agwebrunner.com

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

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

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

Овакве листе обично имају неку врсту јединственог идентификатора за сваку ставку на листи. У овом случају, јединствени идентификатор налази се у колони „Шифра артикла“. Напомена: Да би функција ВЛООКУП функционисала са базом података / листом, та листа мора да има колону која садржи јединствени идентификатор (или „кључ“ или „ИД“) и та колона мора бити прва колона у табели. Наша горња база података са узорцима задовољава овај критеријум.

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

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

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

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

Направимо пример за ово: Ан Предложак фактуре које можемо поново и поново користити у нашој фиктивној компанији.

Прво покренемо Екцел и креирамо празан рачун:

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

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

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

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

Да бисмо тестирали ВЛООКУП формулу коју ћемо написати, прво уносимо важећи код ставке у ћелију А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 од ових предмета, унијели бисмо „2“ у ћелију Д11. Затим бисмо унели једноставну формулу у ћелију Ф11 да бисмо добили укупну линију:

= Д11 * Е1

... што изгледа овако ...

Попуњавање шаблона фактуре

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

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

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.

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