Како користити ВЛООКУП у Екцелу

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

ВЛООКУП је функција Екцел .Овај чланак претпоставља да читач већ има кратко разумевање функција Екцел и може користити основне функције као што су СУМ, АВЕРАГЕ и ДОДАЈ.У својој најчешћи употреби, ВЛООКУП је база функције, што значи да ради са табелама базе података - или једноставно, наводи ствари у Екцеловом радном листу.Какве ствари?Па, било каква врста ствари.Можда имате радни лист који садржи листу запослених, производа или купаца или ЦД-а у вашој колекцији ЦД-а или звездама на ноћном небу.Није стварно битно.

Ево примера листе или базе података.У овом случају то је листа производа које наша фиктивна компанија продаје:

Обично овакве листе имају неку врсту јединственог идентификатора за сваку ставку на листи.У овом случају, јединствени идентификатор је у ступцу "Ставка код".Напомена: Да би функција ВЛООКУП радила са базом података / листом, та листа

мора имати колону која садржи јединствени идентификатор( или "кључ" или "ИД") и та колона мора бити први ступац у табели.Наша база података узорка задовољава овај критеријум.

Најтежи део коришћења ВЛООКУП-а је разумевање тачно за шта је то.Дакле, да видимо да ли можемо прво да будемо јасни:

ВЛООКУП преузима информације из базе података / листе на основу приложене инстанце јединственог идентификатора.

У горе наведеном примеру, ви бисте ставили ВЛООКУП функцију у другу табелу с кодом ставке и вама ће вам се вратити опис одговарајуће ставке, његова цијена или његова доступност( његова количина "у залихама") како је описано у вашеморигинална листа.Који од ових информација ће вас вратити назад?Па, одлучите ово када креирате формулу.

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

Направимо један пример овога: Образац за фактуре који можемо поново и више користити у нашој фиктивној компанији.

Прво покрећемо Екцел и креирамо себи празну фактуру:

Овако ће функционирати: Лице које користи шаблон фактуре попуњава серију ставки ставки у колони "А", а систем ће преузети све ставкеопис и цијену из наше базе производа.Те информације ће се користити за израчунавање укупне линије за сваку ставку( под претпоставком да унесемо важећу количину).

У сврху чувања овог примера једноставно ћемо пронаћи базу производа на посебном листу у истој радној свесци:

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

Стога смо креирали нашу базу производа која изгледа овако:

Да би тестирали формулу ВЛООКУП коју ћемо написати, прво уносимо важећи код ставке у ћелију А11 нашег празног рачуна:

Затим, ми смопреместите активну ћелију у ћелију у којој желимо да се ВЛООКУП подаци чувају из базе података.Интересантно, ово је корак у коме се већина људи погрешно схвата.Да бисмо објаснили даље: Направићемо формулу ВЛООКУП која ће добити опис који одговара коду ставке у ћелији А11.Где желимо да овај опис стави када га добијемо?У ћелији Б11, наравно.Дакле, ту пишемо формулу ВЛООКУП: у ћелији Б11.Изаберите ћелију Б11 сада.

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

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

Да бисте пронашли онај који тражимо, могли бисмо откуцати појам за претрагу попут "лоокуп"( јер је функција која нас занима функција за претраживање ).Систем ће нам вратити листу свих функција које се односе на претрагу у програму Екцел. ВЛООКУП је други на листи.Изаберите један клик ОК .

Појављује се Аргументи функционалности , који нас траже за све аргументе ( или параметре ) потребне да би се комплетирала ВЛООКУП функција.Можете замислити ово поље као функција која нам поставља следећа питања:

1. Који јединствени идентификатор тражите у бази података?
2. Гдје је база података?
3. Које податке из базе података, повезане са јединственим идентификатором, желите да их преузмете?

Прве три аргументе су приказане у болду , што указује да су они обавезни аргументи ( функција ВЛООКУП је непотпуна без њих и неће вратити важећу вриједност).Четврти аргумент није болд, што значи да је опционо:

Документи ћемо завршити редом, одозго према доље.

Први аргумент који требамо довршити је Лоокуп_валуе аргумент.Функција нам треба да кажемо где се налази јединствени идентификатор( код артикла у овом случају) да би требало да враћа опис.Морамо изабрати ставку кода коју смо унели раније( у А11).

Кликните на икону селектора десно од првог аргумента:

Затим кликните једном на ћелију која садржи ознаку предмета( А11) и притисните Унесите :

Вредност "А11" се убаци у први аргумент.

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

Сада лоцирајте базу података / листу и изаберите читаву листу - који не укључује линију заглавља .У нашем примеру, база података се налази на одвојеном радном листу, тако да прво кликнемо на картицу радног листа:

Затим изаберите читаву базу података, не укључујући линију заглавља: ​​

. .. и притисните Унесите .Опсег ћелија који представљају базу података( у овом случају "Продуктна база података!! А2: Д7") аутоматски се уноси у други аргумент.

Сада морамо унети трећи аргумент, Цол_индек_нум .Користимо овај аргумент да наведемо ВЛООКУП који део информација из базе података, који се повезује са нашим кодом ставке у А11, желимо да нам се вратимо.У овом конкретном примеру желимо да нам се врати опис артикла .Ако погледате радни лист базе података, приметићете да је колона "Опис" друга колона у бази података.То значи да морамо унети вредност "2" у поље Цол_индек_нум :

Важно је напоменути да овде не улазимо "2" јер је колона "Опис" у колони Б натај радни лист.Ако је база података започела у колони К на радном листу, ипак ћемо унети "2" у овом пољу јер је колона "Опис" друга колона у скупу ћелија које смо одабрали приликом спецификације "Табле_арраи".

Коначно, морамо да одлучимо да ли да унесемо вредност у коначни ВЛООКУП аргумент, Ранге_лоокуп .Овај аргумент захтева или труе или вредност фалсе , или треба оставити празном.При коришћењу ВЛООКУП-а са базама података( што је тачно 90% времена), начин одлучивања о томе шта треба ставити у овај аргумент може се сматрати слиједећим:

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

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

Као први ступац наше базе података је није сортиран,уносимо фалсе у овај аргумент:

То је то!Унели смо све информације потребне за ВЛООКУП да вратимо вредност која нам је потребна.Кликните на дугме ОК и приметите да је опис који одговара коду ставке "Р99245" исправно унет у ћелију Б11:

Формула која је створена за нас изгледа овако:

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

Можемо извести сличан скуп корака да бисте добили цену елемента вратили у ћелију Е11.Имајте на уму да нова формула мора бити креирана у ћелији Е11.Резултат ће изгледати овако:

. .. и формула ће изгледати овако:

Имајте на уму да је једина разлика између двије формуле трећи аргумент( Цол_индек_нум ) промењен са "2" у "3"( јержелимо податке извући из 3. колоне у бази података).

Ако смо одлучили да купимо 2 од ових ставки, у ћелију Д11 уносићемо "2".Затим ћемо унети једноставну формулу у ћелију Ф11 како би добили линију укупно:

= Д11 * Е11

. .. који изгледа овако. ..

Завршавање шаблона рачуна

До сада смо сазнали пуно о ВЛООКУП-у.У ствари, научили смо све што ћемо научити у овом чланку.Вредно је напоменути да се ВЛООКУП може користити у другим околностима поред база података.Ово је мање уобичајено и може бити покривено у будућим Хов-То Геек чланцима.

Наш шаблон фактуре још није завршен.Да бисмо то довршили, урадили би следеће:

1. Ми ћемо уклонити шифру предмета од ћелије А11 и "2" из ћелије Д11.Ово ће узроковати наше ново креиране ВЛООКУП формуле за приказ порука о грешци:
   
   То можемо ријешити разумном употребом Екцелових ИФ() и ИСБЛАНК() функција.Ми мијењамо нашу формулу из овог. .. = ВЛООКУП( А11, 'Продуцт Датабасе'! А2: Д7,2, ФАЛСЕ) . .. у овај. .. = ИФ( ИСБЛАНК( А11), "", ВЛООКУП( А11,'! А2: Д7,2, ФАЛСЕ))
   
2. Ми ћемо копирати формуле у ћелијама Б11, Е11 и Ф11 до остатка редова ставке фактуре.Имајте на уму да, ако ово урадимо, резултујуће формуле више неће исправно реферирати таблици базе података.То можемо поправити промјеном референци ћелије за базу података на апсолутне референце ћелија.Алтернативно - и још боље - могли бисмо да креирамо назив називног аспекта за целу производну базу података( као што су "Производи"), и користимо ово име опсега умјесто референци ћелије.Формула би се променила из овог. .. = ИФ( ИС11), ВЛООКУП( А11, 'База података производа'! А2: Д7,2, ФАЛСЕ)) . .. у овај. .. = ИФ( ИСБЛАНК( А11), "", ВЛООКУП( А11, Производи, 2, ФАЛСЕ)) . .. и а затим копирајте формуле до остатка редова ставке фактуре.
3. Вероватно ћемо "закључати" ћелије које садрже наше формуле( или боље откључати ћелије друге ћелије), а затим заштитити радни лист како би осигурали да нас пажљиво конструисане формуле случајно не преписују када неко дођеу фактури.
4. Ми ћемо сачувати датотеку као шаблон , тако да би га могли поново користити сви у нашој компанији

. Ако смо осећали стварно паметно, ми ћемо створити базу података свих наших купаца на другом радном листу, а затим користитиИД клијента унета у ћелију Ф5 да аутоматски попуни име и адресу купца у ћелијама Б6, Б7 и Б8.

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