Комбинацијом Екцел-аВЛООКУП функционише саЦОЛУМН функцију можемо направити формулу за претраживање која вам омогућава да вратите више вредности из једног реда базе података или таблице података.
01 од 10Враћање више вриједности са Екцел ВЛООКУП
Следеци кораци наведени доле стварају формулу за претрагу која се види на слици изнад која це вратити висе вредности из једног јединственог записа података.
Формула за тражење захтева да ЦОЛУМН функција је угнежена унутар ВЛООКУП. Нестинг функција подразумева улазак у другу функцију као један од аргумената за прву функцију.
У овом приручнику, ЦОЛУМН функција се уноси као индекс колоне број аргумент за ВЛООКУП. Последњи корак у туторијалу подразумева копирање формуларе за претраживање у додатне колоне да бисте добили додатне вредности за изабрани део.
Унесите туторски подаци
Први корак у туторијалу је унос података у Екцел радни лист. Да бисте пратили кораке у туторијалу, унесите податке приказане на слици изнад у следеће ћелије:
- Унесите највиши опсег података у ћелије Д1 до Г1.
- Уђите у други опсег ћелије Д4 до Г10.
Критеријуми претраживања и формулација за претрагу створени током овог туторијала биће унесени ред 2 радног листа.
Овај туторијал не укључује обликовање које се види на слици, али то неће утицати на то како функционира формула за претраживање. Информације о опцијама за обликовање сличне онима које смо видели изнад доступни су у овом Басиц Екцел формату.
03 од 10Креирање именованог опсега за таблицу података
Именски распон је једноставан начин за упућивање на низ података у формули. Уместо да уносите референце ћелија за податке, можете уписати само име опсега.
Друга предност за кориштење назначеног опсега је то што се референце ћелија за овај опсег никада не мењају чак и када се формула копира у друге ћелије на радном листу. Дакле, имена опсега су алтернатива коришћењу апсолутних референци ћелија како би се спречиле грешке приликом копирања формулара.
Назив распона не укључује називе или називе поља за податке (ред 4), али само сами подаци.
- Означите ћелије Д5 до Г10 на радном листу да их изаберете.
- Кликните на Име Бок налази се изнад колона А.
- Тип Сто Инто тхе Име Бок.
- притисните Ентер на тастатури.
- Ћелије Д5 до Г10 сада имају име опсега Сто. Користићемо име за ВЛООКУП табле арраи аргумент касније у туторијалу.
Отварање дијалога ВЛООКУП
Иако је могуће само уписати нашу формулу за претраживање директно у ћелију на радном листу, многим људима је тешко држати синтаксу равномерно - посебно за комплексну формулу као што је она коју користимо у овом приручнику.
Алтернатива, у овом случају, јесте да се користи ВЛООКУП Дијалог. Скоро све функције Екцел-а имају дијалошки оквир који вам омогућава да уносите сваки аргумент функције на посебну линију.
Корак по корак
- Кликните на ћелија Е2 радног листа - локација на којој ће се приказати резултати дводимензионалне формуле за претрагу.
- Кликните на Формуле таб од трака.
- Кликните на Претрага и референца опција у трака да отворите функцију падајућег менија.
- Кликните на ВЛООКУП на списку да отворите дијалошки оквир функције.
Уношење аргумената за тражење вредности
Обично, потражити вредност одговара пољу података у првој колони таблице података. У нашем примеру, потражити вредност односи се на име хардверског дела о којем желимо да пронађемо информације. Дозвољени типови података за потражити вредност су текстуални подаци, логичке вредности, бројеви и референце ћелија.
Апсолутне ћелијске референце
Обично, када се формуле копирају у Екцел-у, референце ћелија се мењају да одражавају њихову нову локацију. Ако се ово деси, Д2, референца ћелије за потражити вредност, ће се променити како се формула копира, стварајући грешке у ћелије Ф2 и Г2.
Апсолутне референце ћелија се не мењају када се копирају формуле.
Да бисмо спречили грешке, претворићемо референцу ћелије Д2 у апсолутну референтну ћелију. Апсолутне референце ћелија се стварају притиском на Ф4 на тастатури. На тај начин додаје знакове долара око референца ћелије, као што је $ Д $ 2.
- Кликните на лоокуп_валуе линију у дијалогу.
- Кликните на ћелија Д2 да додате ову референцу на ћелију лоокуп_валуе линија - ово је ћелија у којој ћемо унети име дела о којем тражимо информације
- Без померања тачке убацивања, притисните тастер Ф4 кључ на тастатури за конверзију Д2 у апсолутну референтну ћелију $ Д $ 2.
- Оставити ВЛООКУП дијалог за функцију отворен за следећи корак у туторијалу.
Унос Аргумент Арраи табеле
Низ таблица је табела података коју формула за претраживање тражи да пронађе информације које желимо.Низ таблица мора да садржи најмање две колоне података.
Прва колона садржи аргумент за тражење вредности (претходни корак у туторијалу), док ће друга колона претраживати формула за проналажење информација које ћемо навести.
Арраи табле арраи се мора унети као опсег који садржи референце ћелија за таблицу података или као назив распона.
- Кликните на табле_арраи линију у дијалогу.
- Тип Сто да унесете назив распона за овај аргумент.
- Оставити ВЛООКУП дијалог за функцију отворен за следећи корак у туторијалу.
Вређање функције ЦОЛУМН
Нормално ВЛООКУП враћа само податке из једне колоне таблице података и ову колону поставља број индекса колоне расправа.
У овом примеру, међутим, имамо три колоне за које желимо да вратимо податке, тако да нам треба начин да их лако променимо број индекса колоне без уређивања наше формуле за тражење - ово је место где је ЦОЛУМН долази функција.
Уношењем га као број индекса колоне аргумент, то ће се променити како се копира формула за претрагу ћелија Д2 до ћелије Е2 и Ф2 касније у туторијалу.
Нестинг Фунцтионс
Тхе ЦОЛУМН функција, дакле, делује као ВЛООКУП'с број индекса колоне расправа; ово се постиже гнездењем ЦОЛУМН функционира унутар ВЛООКУП у Цол_индек_нум линију дијалог бок-а.
Ручно уношење функције ЦОЛУМН
Када унесете функције, Екцел нам не дозвољава да отворимо дијалошки оквир друге функције да уносимо његове аргументе. Тхе ЦОЛУМН функција, према томе, мора се унети ручно у Цол_индек_нум лине. Тхе ЦОЛУМН функција има само један аргумент - Референца аргумент који је референца ћелије.
Избор референтног аргумента функције ЦОЛУМН функције
Тхе ЦОЛУМН задатак функције је да вратите број колоне дате као Референца расправа. Другим речима, претвара слово колоне у број са колона А будући да је прва колона, колона Б други и тако даље.
Од првог поља података, оно што желимо вратити је цијена артикла, што је у колони два у таблици података. Можемо одабрати референцу ћелије за било коју ћелију колона Б као Референца Аргумент да бисте добили број 2 за Цол_индек_нум расправа.
- У ВЛООКУП дијалог функцију, кликните на Цол_индек_нум лине.
- Укуцајте име функције колона а затим ан отворени округли носач.
- Кликните на ћелија Б1 на радном листу да унесете референцу ћелије као Референца расправа.
- Укуцате затварање округле конзоле да заврши ЦОЛУМН функција
- Оставити ВЛООКУП дијалог за функцију отворен за следећи корак у туторијалу.
Улазак у ВЛООКУП Ранге Аргумент
ВЛООКУП'с Ранге_лоокуп аргумент је логична вредност (ИСТИНА или ФАЛСЕ) који указује да ли желите ВЛООКУП да пронађемо тачну или приближну подударност са Лоокуп_валуе.
- Ако ИСТИНА или ако је овај аргумент изостављен: ВЛООКУП враћа или тачну подударност са Лоокуп_валуеИли, ако није пронађено тачно усклађивање, ВЛООКУП враћа следећу највећу вредност. За формулу која то ради, подаци у првој колони Табле_арраи мора бити сортиран у растућем редоследу.
- Ако ФАЛСЕ: ВЛООКУП користиће само тачну утакмицу са Лоокуп_валуе. Ако постоје две или више вредности у првој колони Табле_арраи који се подударају са тражњом, користи се прва пронађена вриједност. Ако се не пронађе тачно усклађивање, а # Н / А грешка се враћа.
У овом водичу, пошто тражимо специфичне информације о одређеном хардверу, поставићемо Ранге_лоокуп једнако Лажно.
- Кликните на Ранге_лоокуп линију у дијалогу.
- Упишите реч Лажно у овој линији да би показали да желимо ВЛООКУП да вратимо тачан подудар за податке које тражимо.
- Кликните У реду да довршите формулу за претрагу и затворите дијалог.
- Пошто још увек нисмо унели критеријуме за претрагу ћелија Д2 ан # Н / А грешка ће бити присутан у ћелија Е2.
- Ова грешка ће бити исправљена када ћемо додати критеријуме за претрагу у последњем кораку туториала.
Копирање Формуле за тражење
Формула за проналажење има за циљ да преузме податке из више колона таблице података у исто време. Да би то учинили, формула за тражење мора да се налази у свим пољима из којих желимо информацију.
У овом водичу желимо да преузме податке колоне 2, 3, и 4 табеле података - то је цена, број дела и име добављача када унесемо делимично име као Лоокуп_валуе.
Пошто су подаци постављени у редовном образцу на радном листу, можемо копирати формулу за претрагу у ћелија Е2 до ћелије Ф2 и Г2.
Како је формула копирана, Екцел ће ажурирати релативну референцу ћелије у ЦОЛУМН функција (ћелија Б1) да одражава нову локацију формуле.
Такође, Екцел не мења апсолутну референтну ћелију $ Д $ 2 и назначени опсег Сто јер се формула копира.
Постоји више начина за копирање података у програму Екцел, али вероватно најлакши начин је кориштење Попуните ручку.
- Кликните на ћелија Е2, где се налази формула за тражење, како би она постала активна ћелија.
- Поставите показивач миша над црни квадрат у доњем десном углу - показивач ће се променити на плус знак како би означио ручицу за пуњење.
- Кликните на леви тастер миша и превуците ручицу за пуњење преко пута ћелија Г2.
- Издање дугме миша и ћелија Ф3 треба да садржи дводимензионалну формулу за претраживање.
- Ако се исправно уради, ћелије Ф2 и Г2 сад би сад требало да садржи и # Н / А грешка која је присутна ћелија Е2.
Унос критеријума за тражење
Када формула за претрагу буде копирана у жељене ћелије, може се користити за преузимање информација из табеле података. Да бисте то урадили, укуцајте назив предмета желите да преузмете у Лоокуп_валуе ћелија (Д2) и притисните Ентер на тастатури.
Када једном завршите, свака ћелија која садржи форму за претраживање треба да садржи различите податке о хардверу који тражите.
- Кликните на ћелија Д2 на радном листу.
- Тип Видгет у ћелија Д2 и притисните Ентер на тастатури.
- Следеће информације требају бити приказане у ћелије Е2 до Г2.
- Е2: 14,76 долара - цена видгет-а
- Ф2: ПН-98769 - број дела за виџет
- Г2: Видгетс Инц. - име добављача за виџете
- Тестирајте ВЛООКУП низа формулара даље укуцавањем имена других делова у ћелија Д2 и посматрајући резултате ћелије Е2 до Г2
