Проналажење више поља података са Екцел ВЛООКУП

Комбиновањем Екцелове ВЛООКУП функције са функцијом ЦОЛУМН можемо направити формулу за претраживање која вам омогућава да вратите више вредности из једног реда базе података или таблице података.

У примеру приказаном на слици изнад, формула за претраживање омогућава лако вратити све вриједности - као што су цијена, број парцеле и добављач - који се односе на различите дијелове хардвера.

01 од 10

Враћање више вриједности са Екцел ВЛООКУП

Следеци кораци наведени доле стварају формулу за претрагу која се види на слици изнад која це вратити висе вредности из једног јединственог записа података.

Формула за тражење захтева да се ЦОЛУМН функција угнезди унутар ВЛООКУП-а.

Нестинг функција подразумева улазак у другу функцију као један од аргумената за прву функцију.

У овом туториалу, ЦОЛУМН функција ће се унети као аргумент броја индекса колона за ВЛООКУП.

Последњи корак у туторијалу подразумева копирање формуларе за претраживање у додатне колоне да бисте добили додатне вредности за изабрани део.

Садржај приручника

• Унос података о туторијалу
• Креирање именованог опсега за таблицу података
• Започињање функције ВЛООКУП
• Уношење аргумената по Лоокуп Валуе-у коришћењем референци Абсолуте Целл
• Унос Аргумент Арраи табеле
• Улазак у Уграђену функцију ЦОЛУМН
• Завршетак функције ВЛООКУП
• Копирање Формуле за тражење помоћу ручице за попуњавање
• Преузимање података са Формулом за претраживање

02 од 10

Унесите туторски подаци

Први корак у туторијалу је унос података у Екцел радни лист .

Да бисте пратили кораке у туторијалу, унесите податке приказане на слици изнад у следеће ћелије .

• Унесите највише опсега података у ћелије Д1 до Г1
• Унесите други опсег у ћелије Д4 до Г10

Критеријуми претраживања и формула за проналажење настала током овог туторијала биће унета у ред 2 радног листа.

Овај туторијал не укључује обликовање које се види на слици, али то неће утицати на то како функционира формула за претраживање.

Информације о опцијама за обликовање сличне онима које смо видели изнад доступни су у овом Басиц Екцел формату .

Корак по корак

1. Унесите податке као што видите на слици изнад у ћелије Д1 до Г10

03 од 10

Креирање именованог опсега за таблицу података

Именски распон је једноставан начин за упућивање на низ података у формули. Уместо да уносите референце ћелија за податке, можете уписати само име опсега.

Друга предност за кориштење назначеног опсега је то што се референце ћелија за овај опсег никада не мењају чак и када се формула копира у друге ћелије на радном листу.

Дакле, имена опсега су алтернатива коришћењу апсолутних референци ћелија како би се спречиле грешке приликом копирања формулара.

Напомена: Назив подручја не укључује називе и називе поља за податке (ред 4), већ само податке.

Корак по корак

1. Истакните ћелије Д5 до Г10 на радном листу како бисте их изабрали
2. Кликните на поље за избор имена изнад ступца А
3. Тип "Табела" (без цитата) у поље за називе
4. Притисните тастер ЕНТЕР на тастатури
5. Ћелије Д5 до Г10 сада имају назив распона "Табела". Користићемо име за аргумент ВЛАОКУП табле арраи касније у туторијалу

04 од 10

Отварање дијалога ВЛООКУП

Иако је могуће само уписати нашу формулу за претраживање директно у ћелију на радном листу, многим људима је тешко држати синтаксу равномерно - посебно за комплексну формулу као што је она коју користимо у овом приручнику.

Алтернатива, у овом случају, је коришћење дијалога ВЛООКУП. Скоро све функције Екцел- а имају дијалошки оквир који вам омогућава да уносите сваки аргумент функције на посебну линију.

Корак по корак

1. Кликните на ћелију Е2 радног листа - локацију на којој ће се приказати резултати дводимензионалне формула за претраживање
2. Кликните на картицу Формуле на траци
3. Кликните на опцију Лоокуп & Референце у траци да бисте отворили листу падајућих функција
4. Кликните на ВЛООКУП на листи да отворите дијалог прозора функције

05 од 10

Уношење аргумената по Лоокуп Валуе-у коришћењем референци Абсолуте Целл

Нормално, вредност за тражење одговара пољу података у првој колони табеле података.

У нашем примеру, вриједност прегледа се односи на име хардверског дела о којем желимо пронаћи информације.

Дозвољени типови података за тражњу су:

• текстуални подаци
• логичка вредност (само ТРУЕ или ФАЛСЕ)
• Број
• референца ћелије на вриједност на радном листу

У овом примеру ћемо унети референцу ћелије на место где се налази назив ћелије - ћелија Д2.

Апсолутне ћелијске референце

У каснијом кораку у туторијалу копирамо формулу за тражење у ћелију Е2 у ћелије Ф2 и Г2.

Обично, када се формуле копирају у Екцел-у, референце ћелија се мењају да одражавају њихову нову локацију.

Уколико се ово деси, Д2 - референца ћелије за вредност за претрагу - ће се променити с копирањем формуле, стварајући грешке у ћелијама Ф2 и Г2.

Да бисмо спречили грешке претворићемо референце Д2 ћелије у апсолутну референтну ћелију .

Апсолутне референце ћелија се не мењају када се копирају формуле.

Апсолутне референце ћелија се стварају притиском на тастер Ф4 на тастатури. На тај начин се додају знаци долара око референце ћелије, као што је $ Д $ 2

Корак по корак

1. Кликните на линију лоокуп_валуе у дијалошком оквиру
2. Кликните на ћелију Д2 да додате ову референцу ћелију на линију лоокуп_валуе . Ово је ћелија у којој ћемо унети име дела о којем тражимо информације
3. Без померања тачке убацивања, притисните тастер Ф4 на тастатури да бисте претворили Д2 у апсолутну референтну ћелију $ Д $ 2
4. Оставите дијалошки оквир функције ВЛООКУП отворен за следећи корак у туторијалу

06 од 10

Унос Аргумент Арраи табеле

Низ таблица је табела података коју формула за претраживање тражи да пронађе информације које желимо.

Низ таблица мора да садржи најмање две колоне података .

• прва колона садржи аргумент за тражење вредности (претходни корак у туторијалу)
• други и сви додатни ступци ће се претражити помоћу формула за проналажење информација које ћемо навести.

Арраи табле арраи се мора унети као опсег који садржи референце ћелија за таблицу података или као назив распона .

За овај примјер користићемо име опсега креираног у кораку 3 туториала.

Корак по корак

1. Кликните на линију табле_арраи у дијалошком оквиру
2. Укуцајте "Табела" (без цитата) да бисте унели назив распона за овај аргумент
3. Оставите дијалошки оквир функције ВЛООКУП отворен за следећи корак у туторијалу

07 од 10

Вређање функције ЦОЛУМН

Обично ВЛООКУП враћа само податке из једне колоне табеле података, а ова колона је постављена аргументом бројева индексног броја .

У овом примеру, међутим, имамо три колоне за које желимо вратити податке, тако да нам је потребан начин да лако променимо индексни број ступца без измјене наше формуле за претраживање.

Овдје долази функција ЦОЛУМН. Уношењем је као аргумент броја индекса ступца , она ће се променити како се формула за претрагу копира из ћелије Д2 у ћелије Е2 и Ф2 касније у туториалу.

Нестинг Фунцтионс

Функција ЦОЛУМН, дакле, делује као ВЛООКУП-ов аргумент броја бројева колона .

Ово се постиже гнежењем функције ЦОЛУМН унутар ВЛООКУП-а у Цол_индек_нум линији дијалог бок-а.

Ручно уношење функције ЦОЛУМН

Када унесете функције, Екцел нам не дозвољава да отворимо дијалошки оквир друге функције да уносимо његове аргументе.

Функција ЦОЛУМН, према томе, мора се унети ручно у Цол_индек_нум линију.

Функција ЦОЛУМН има само један аргумент - референтни аргумент који је референца ћелије.

Избор референтног аргумента функције ЦОЛУМН функције

Задатак функције ЦОЛУМН је вратити број колоне датог као Референтни аргумент.

Другим речима, претвара се слово колоне у број са колоном А која је прва колона, колона Б друга и тако даље.

Пошто прво поље података које желимо вратити је цена ставке - која је у колони два из табеле података - можемо одабрати референцу ћелије за било коју ћелију у колони Б као референтни аргумент да би добили број 2 за аргумент Цол_индек_нум .

Корак по корак

1. У дијалогу функције ВЛООКУП кликните на линију Цол_индек_нум
2. Укуцајте колону назива функције коју прати отворени округли носач " ( "
3. Кликните на ћелију Б1 на радном листу да бисте унели референцу ћелије као референтни аргумент
4. Укуцајте држач за затварање " ) " да бисте довршили функцију ЦОЛУМН
5. Оставите дијалошки оквир функције ВЛООКУП отворен за следећи корак у туторијалу

08 од 10

Улазак у ВЛООКУП Ранге Аргумент

ВЛООКУП-ов аргумент Ранге_лоокуп је логична вредност (само ТРУЕ или ФАЛСЕ) која указује на то да ли желите да ВЛООКУП пронађе тачну или приближну подударност са Лоокуп_валуе.

• Ако је ТРУЕ или ако је овај аргумент изостављен, ВЛООКУП враћа или тачну утакмицу на Лоокуп_валуе, или ако није пронађено тачно усклађивање, ВЛООКУП враћа следећу највећу вриједност. За формулу која то ради, подаци у првој колони Табле_арраи морају бити сортирани у растућем редоследу .
• Ако је ФАЛСЕ, ВЛООКУП ће користити само тачан резултат на Лоокуп_валуе. Ако у првој колони Табле_арраи постоје двије или више вриједности које се подударају са вриједностима тражења, користи се прва вриједност. Ако се не пронађе тачно усклађивање, грешка # Н / А се враћа.

У овом водичу, пошто тражимо специфичне информације о одређеном хардверском ставку, поставићемо Ранге_лоокуп једнако Фалсе .

Корак по корак

1. Кликните на Ранге_лоокуп линију у дијалог бок-у
2. У ову линију упишите реч Фалсе да би желели да ВЛООКУП врати тачан подудар за податке које тражим
3. Кликните на ОК да бисте довршили формулу за претрагу и затворили дијалошки оквир
4. Пошто још нисмо уписали критеријуме за претраживање у ћелију Д2, грешка # Н / А ће бити присутна у ћелији Е2
5. Ова грешка ће бити исправљена када ћемо додати критеријуме за претрагу у последњем кораку туториала

09 од 10

Копирање Формуле за тражење помоћу ручице за попуњавање

Формула за проналажење има за циљ да преузме податке из више колона таблице података у исто време.

Да би то учинили, формула за тражење мора да се налази у свим пољима из којих желимо информацију.

У овом водичу желимо да добије податке из колона 2, 3 и 4 табеле података - то је цена, број дела и име добављача када унесемо назив дела као Лоокуп_валуе.

Пошто су подаци постављени у редовном образцу на радном листу , можемо копирати формулу за претраживање у ћелији Е2 у ћелије Ф2 и Г2.

Како је формула копирана, Екцел ће ажурирати релативну референтну ћелију у функцији ЦОЛУМН (Б1) како би одразила нову локацију формуле.

Такође, Екцел не мења апсолутну референтну ћелију $ Д $ 2 и названу таблицу распона јер се формула копира.

Постоји више начина за копирање података у Екцелу, али највероватније најлакши начин је кориштење ручице за попуњавање .

Корак по корак

1. Кликните на ћелију Е2 - где се налази формула за претрагу - како би она постала активна ћелија
2. Поставите показивач миша преко црног квадрата у доњем десном углу. Показивач ће се променити на плус знак " + " - ово је ручица за попуњавање
3. Кликните на леви тастер миша и превуците ручицу за пуњење преко ћелије Г2
4. Отпуштајте дугме миша и ћелија Ф3 треба да садржи дводимензионалну формулу за претраживање
5. Ако је исправно извршено, ћелије Ф2 и Г2 сада треба да садрже и # Н / А грешку која је присутна у ћелији Е2

10 од 10

Унос критеријума за тражење

Када формула за претрагу буде копирана у жељене ћелије , може се користити за преузимање информација из табеле података.

Да бисте то урадили, упишите назив ставке коју желите да преузмете у Целл Лоокуп_валуе (Д2) и притисните тастер ЕНТЕР на тастатури.

Када једном завршите, свака ћелија која садржи форму за претраживање треба да садржи различите податке о хардверу који тражите.

Корак по корак

1. Кликните на ћелију Д2 на радном листу
2. Укуцајте Видгет у ћелију Д2 и притисните тастер ЕНТЕР на тастатури
3. Следеће информације требају бити приказане у ћелијама Е2 до Г2:
   • Е2 - 14,76 долара - цена видгет-а
   • Ф2 - ПН-98769 - број дела за виџет
   • Г2 - Видгетс Инц. - име добављача за виџете
4. Тестирајте формулу ВЛООКУП низом тако што ћете у поље Д2 укуцати име других делова и посматрати резултате у ћелијама Е2 до Г2

Ако се порука о грешци, као што је # РЕФ! појављује се у ћелијама Е2, Ф2 или Г2, ова листа ВЛООКУП порука о грешкама може вам помоћи да одредите гдје се налази проблем.