VLOOKUP функциясын пайдаланып, Excel бағдарламасының сол жақтағы формуласы

03 01

Деректерді солға табыңыз

Excel бағдарламасының солға қарай қарап шығу формуласы

Excel бағдарламасының VLOOKUP функциясы таңдалған іздеу мәніне негізделген деректер кестесінен ақпаратты табу және қайтару үшін пайдаланылады.

Әдетте, VLOOKUP іздеу мәнін деректер кестесінің ең сол жақ бағанында болуын талап етеді және функция осы мәннің сол жағында орналасқан деректердің басқа өрісін қайтарады.

VLOOKUP функциясын CHOOSE функциясымен біріктіру арқылы; алайда, сол іздеу формуласы жасалуы мүмкін:

• деректер кестесіндегі кез келген бағаннан іздеу мәнін таңдауға мүмкіндік береді
• кез келген бағанда орналасқан деректерді іздеу мәнінің сол жағына қайтарады

Мысалы: Left Lookup формуласында VLOOKUP және CHOOSE функцияларын пайдалану

Төменде келтірілген қадамдар жоғарыдағы суретте көрсетілген сол іздеу формуласын жасайды.

Формула

= VLOOKUP ($ D $ 2, CHOOSE ({1,2}, $ F: $ F, $ D: $ D), 2, FALSE)

деректер кестесінің 3-бағанында көрсетілген әртүрлі компаниялар ұсынатын бөлікті табуға мүмкіндік береді.

Формуладағы CHOOSE функциясының тапсырмасы VLOOKUP-ті 3-бағанды ​​шынымен 1-бағанға сенуге түрту болып табылады. Нәтижесінде, Компанияның атауы әрбір компания ұсынатын бөліктің атауын табу үшін іздеу мән ретінде пайдалануға болады.

Оқулықтың қадамдары - Оқулық деректерін енгізу

1. Төмендегі тақырыптарды көрсетілген ұяшықтарға енгізіңіз: D1 - Жеткізуші E1 - Бөлім
2. Жоғарыдағы суретте көрсетілген деректер кестесіне D4-ден F9 ұяшығына енгізіңіз
3. 2 және 3 жолдары осы оқу құралында жасалған іздеу критерийлерін және сол іздеу формуласын орналастыру үшін бос қалды

Сол жақтағы іздеу формуласын іске қосу - VLOOKUP тілқатысу терезесін ашу

Жоғарыда көрсетілген формуланы тікелей жұмыс парағында F1 ұяшығына тікелей жазуға болады, бірақ көптеген адамдар формуланың синтаксисіне қиындық туғызады.

Балама, бұл жағдайда VLOOKUP тілқатысу терезесін пайдалану . Excel бағдарламасының дерлік барлық функцияларында функцияның әрқайсысын жеке жолға енгізуге мүмкіндік беретін диалогтық терезесі бар.

Тәжірибелік қадамдар

1. Жұмыс парағының E2 ұяшығына басыңыз - сол іздеу формуласының нәтижелері көрсетілетін орын
2. Таспаның Формулалары қойындысын нұқыңыз
3. Функция ашылмалы тізімін ашу үшін таспаның Іздеу және анықтама параметрін басыңыз
4. Функция диалогтық терезесін келтіру үшін тізімде VLOOKUP түймесін басыңыз

03 02

Аргументтерді VLOOKUP тілқатысу терезесіне енгізу - Үлкен суретті көру үшін нұқыңыз

VLOOKUP-ның дәлелдері

Функцияның дәлелдері - нәтижені есептеу үшін функция пайдаланатын мәндер.

Функция тілқатысу терезесінде әр аргументінің аты жеке жолға орналастырылады, содан кейін мән енгізу үшін өріс.

Жоғарыдағы суретте көрсетілгендей, VLOOKUP аргументтерінің әрқайсысы үшін төмендегі мәндерді диалогтық терезенің дұрыс жолына енгізіңіз.

Қарап шығу мәні

Іздеу мәні - кесте жиымын іздеу үшін пайдаланылатын ақпарат өрісі. VLOOKUP деректердің басқа өрісін іздеу мәнімен бірдей жолдан қайтарады.

Бұл мысалда компания аты жұмыс парағына енгізілетін орынға ұяшық сілтемесін пайдаланады. Оның артықшылығы, формуланы редакциялаусыз компания атауын өзгертуді жеңілдетеді.

Тәжірибелік қадамдар

1. Диалогтық терезеде lookup_value жолын басыңыз
2. Бұл ұяшық сілтемесін lookup_value жолына қосу үшін D2 ұяшығын басыңыз
3. Пернетақтадағы F4 пернесін ұялы сілтеме абсолютті жасау үшін басыңыз - $ D $ 2

Ескерту: Іздеу формуласы жұмыс парағындағы басқа ұяшықтарға көшірілсе қателерді болдырмау үшін іздеу мәніне және кесте жиым дәлелдеріне арналған абсолюттік ұяшық сілтемелері қолданылады.

Кесте массиві: CHOOSE функциясын енгізу

Кесте жиымының аргументі - нақты ақпарат алынатын жалғасқан деректер блогы.

Әдетте, VLOOKUP тек кесте жиегіндегі деректерді табу үшін іздеу мәнінің дәлелінің оң жағынан ғана көрінеді. Сол жаққа қарап шығу үшін, VLOOKUP функциясын CHOOSE функциясын пайдаланып, кесте жиегіндегі бағандарды қайта реттеу арқылы алдау керек.

Осы формулада CHOOSE функциясы екі тапсырманы орындайды:

1. ол D және F бағандарында тек екі бағаннан тұратын кесте жиымын жасайды
2. ол бағанның массивіндегі бағандардың сол жақ тәртібін өзгертеді, сонда F бағаны бірінші, ал D бағаны екінші болады

CHOOSE функциясының осы тапсырмаларды қалай орындағаны туралы мәліметтерді оқу құралының 3-бетінен табуға болады.

Тәжірибелік қадамдар

Ескерту: Функцияларды қолмен енгізу кезінде, функцияның аргументтерінің әрқайсысы «,» үтірмен бөлінуі керек.

1. VLOOKUP функциясының тілқатысу терезесінде Table_array жолын нұқыңыз
2. Келесі CHOOSE функциясын енгізіңіз
3. CHOOSE ({1,2}, $ F: $ F, $ D: $ D)

Баған индексінің нөмірі

Әдетте, бағанның индексінің нөмірі кестелік массивтің қай бағандағы кейінгі деректерді қамтитынын көрсетеді. Бұл формулада; алайда, ол CHOOSE функциясы арқылы орнатылған бағандардың тәртібін білдіреді.

CHOOSE функциясы кесте жиымын жасайды, яғни баған F алдымен бағанмен бағанамен жасалады. Ақпарат сұралғандықтан - бөлік атауы - D бағанында баған индексінің дәлелінің мәні 2 мәніне тең болуы керек.

Тәжірибелік қадамдар

1. Тілқатысу терезесіндегі Col_index_num жолын нұқыңыз
2. Осы жолда 2- ні теріңіз

Ауқымды іздеу

VLOOKUP-ның Range_lookup аргументі - VLOOKUP-тың іздеу мәніне нақты немесе жақындаған сәйкестікті табуды қалайтыныңызды көрсететін логикалық мән (тек TRUE немесе FALSE).

• TRUE немесе осы аргумент жоқ болса, VLOOKUP Lookup_value-ге дәл сәйкестікті қайтарады, немесе дәл сәйкестік табылмаса, VLOOKUP келесі ең үлкен мәнді қайтарады. Формула жасау үшін Table_array бірінші бағанындағы деректер өсу тәртібімен сұрыпталуы керек.
• FALSE болса, VLOOKUP тек Lookup_value-ге дәл сәйкес келеді. Егер іздеу мәніне сәйкес келетін Table_array бірінші бағанында екі немесе одан да көп мән болса, табылған бірінші мән пайдаланылады. Егер дәл сәйкестік табылмаса, # N / A қатесі қайтарылады.

Бұл оқулықта белгілі бір бөлік атауын іздейтіндіктен, Range_lookup форманың орнына дәл сәйкестік қайтарылады.

Тәжірибелік қадамдар

1. Диалогтық терезеде Range_lookup жолын басыңыз
2. VLOOKUP-ті біз іздеген деректер үшін дәл сәйкестікті қайтаруды қалайтынымызды көрсету үшін осы жолдағы жалған сөзді теріңіз
3. Сол іздеу формуласын аяқтау және тілқатысу терезесін жабу үшін OK түймешігін басыңыз
4. Компания атауы D2 ұяшығына әлі кірмегендіктен, E2 ұяшығында # N / A қатесі болуы керек

03 03

Сол іздеу формуласын тексеру

Деректерді Сол Іздеу Формуласымен қайтару

Қандай компаниялардың қандай бөліктерін жеткізетінін табу үшін компания атауын D2 ұяшығына теріңіз және пернетақтадағы ENTER пернесін басыңыз.

Бөліктің атауы E2 ұяшығында көрсетіледі.

Тәжірибелік қадамдар

1. Жұмыс парағыңыздағы D2 ұяшығын басыңыз
2. Гаджеттер Плюсін D2 ұяшығына енгізіңіз және пернетақтадағы ENTER пернесін басыңыз
3. Gadgets Plus компаниясы ұсынған Gadgets мәтіні - E2 ұяшығында көрсетілуі керек
4. Басқа компания атауын D2 ұяшығына теру арқылы іздеу формуласын әрі қарай тексеріңіз және тиісті бөлік атауы E2 ұяшығында пайда болуы керек

VLOOKUP қате туралы хабарлар

E2 ұяшығында # N / A сияқты қате туралы хабар шықса, алдымен D2 ұяшығындағы емле қателерін тексеріңіз.

Егер емле проблема болмаса, VLOOKUP қате туралы хабарлардың бұл тізімі мәселенің қай жерде екенін анықтауға көмектесуі мүмкін.

CHOOSE функциясының жұмысын бұзу

Жоғарыда аталып өткендей, осы формулада CHOOSE функциясы екі тапсырманы орындайды:

• ол D және F бағандарында тек екі бағаннан тұратын кесте жиымын жасайды
• ол бағанның массивіндегі бағандардың сол жақ тәртібін өзгертеді, сонда F бағаны бірінші, ал D бағаны екінші болады

Екі бағанды ​​кесте жиынын жасау

CHOOSE функциясының синтаксисі :

= CHOOSE (индексі_саны, мән1, мән2, ... мән254)

CHOOSE функциясы әдетте енгізілген индекс санына негізделген мәндер тізімінен (Value1-ден Value254) бір мәнді қайтарады.

Егер индекс нөмірі 1 болса, функция тізімнен Value1 мәнін қайтарады; егер индекс нөмірі 2 болса, функция тізімнен Value2 мәнін қайтарады және т.с.с.

Бірнеше индекс сандарын енгізу арқылы; алайда, функция кез келген тәртіпте бірнеше мәндерді қайтарады. Бірнеше мәндерді қайтару үшін таңдау CHOOSE массив жасау арқылы жасалады.

Массивті енгізу бұдырлы жақшалармен немесе жақшалармен енгізілген сандарды қоршап қояды. Индекс нөмірі үшін екі сан енгізілді: {1,2} .

CHOOSE екі баған кестесін жасаумен шектелмегенін атап өту керек. Массивте қосымша нөмірді қосу арқылы - {1,2,3} сияқты - және аргумент мәніндегі қосымша ауқым үш баған кестесін жасай алады.

Қосымша бағандар VLOOKUP баған индексінің индексінің дәлелін қалаған ақпаратты қамтитын баған нөміріне өзгерту арқылы сол ақпаратты іздеу формуласымен әртүрлі ақпаратты қайтаруға мүмкіндік береді.

CHOOSE функциясы бар бағандардың орнын өзгерту

Осы формулада пайдаланылатын CHOOSE функциясында: CHOOSE ({1,2}, $ F: $ F, $ D: $ D) , F бағаны үшін диапазон Д бағанында көрсетілген.

CHOOSE функциясы VLOOKUP кестесінің массивін тағайындағандықтан, осы функция үшін деректер көзі - CHOOSE функциясындағы бағандардың орнын ауыстыру VLOOKUP жолына өтеді.

Енді VLOOKUP сұранысы бойынша, кесте жиымы оң жақ бағанында F және оң жақ бағанында D бағанымен екі баған ғана. F бағанында біз іздейтін компанияның атауы бар және D бағанында бөліктердің атаулары бар болғандықтан, VLOOKUP іздеу мәнінің сол жағында орналасқан деректерді іздеуде қалыпты іздеу бойынша міндеттерін орындай алады.

Нәтижесінде, VLOOKUP компаниясы ұсынған бөлігін табу үшін компания атауын пайдалана алады.