содржина
- Пребарувајте во Excel по повеќе критериуми
- Ги извлекуваме вредностите 2, 3, итн. со помош на VLOOKUP
- Вратете ги сите повторувања на саканата вредност
- XNUMXD пребарување по познат ред и колона
- Користење на повеќе VLOOKUP во една формула
- Динамична замена на податоци од различни табели користејќи VLOOKUP и INDIRECT
Во вториот дел од нашиот туторијал за функцијата VPR (VLOOKUP) во Excel, ќе анализираме неколку примери кои ќе ви помогнат да ја насочите целата моќ VPR за решавање на најамбициозните задачи на Excel. Примерите претпоставуваат дека веќе имате основно познавање за тоа како функционира оваа функција. Ако не, можеби ќе ве интересира да започнете со првиот дел од ова упатство, кој ја објаснува синтаксата и основната употреба. VPR. Па, ајде да започнеме.
Пребарувајте во Excel по повеќе критериуми
функција VPR во Excel е навистина моќна алатка за извршување на пребарувања за одредена вредност во базата на податоци. Сепак, постои значително ограничување - неговата синтакса ви овозможува да пребарувате само една вредност. Што ако сакате да пребарувате според повеќе услови? Решението ќе го најдете подолу.
Пример 1: Пребарај по 2 различни критериуми
Да претпоставиме дека имаме список со нарачки и сакаме да најдеме Количина на стоки (Количина), врз основа на два критериуми - Име на клиент (Клиент) и Название продукта (производ). Работата е комплицирана од фактот што секој од купувачите нарачал неколку видови стоки, како што може да се види од табелата подолу:
редовна функција VPR нема да работи во ова сценарио бидејќи ќе ја врати првата вредност што ќе ја најде што одговара на дадената вредност за пребарување. На пример, ако сакате да ја знаете количината на ставката Слаткинарачана од купувачот Remереми Хил, напишете ја следната формула:
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)
=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)
– оваа формула ќе го врати резултатот 15што одговара на производот јаболка, бидејќи тоа е првата вредност што се совпаѓа.
Постои едноставна заобиколница - креирајте дополнителна колона во која ќе ги комбинирате сите посакувани критериуми. Во нашиот пример, ова се колоните Име на клиент (Клиент) и Название продукта (производ). Не заборавајте дека споената колона мора секогаш да биде најлевата колона во опсегот за пребарување, бидејќи тоа е левата колона што функцијата VPR гледа нагоре кога бара вредност.
Значи, додавате помошна колона на табелата и ја копирате следната формула над сите нејзини ќелии: =B2&C2. Ако сакате низата да биде почитлива, можете да ги одделите комбинираните вредности со празно место: =B2&» «&C2. После тоа, можете да ја користите следнава формула:
=VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)
=ВПР("Jeremy Hill Sweets";$A$7:$D$18;4;ЛОЖЬ)
or
=VLOOKUP(B1,$A$7:$D$18,4,FALSE)
=ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)
Каде е ќелијата B1 ја содржи поврзаната вредност на аргументот вредност на пребарување (барање_вредност) и 4 – Аргумент коло_индекс_број (колона_број), односно бројот на колоната што ги содржи податоците што треба да се преземат.
Пример 2: VLOOKUP според два критериуми со табела која се гледа на друг лист
Ако треба да ја ажурирате главната табела (Главна табела) со додавање податоци од втората табела (табела за пребарување), која се наоѓа на друг лист или во друга работна книга на Excel, тогаш можете да ја соберете саканата вредност директно во формулата што ја вметнувате во главната маса.
Како и во претходниот пример, ќе ви треба помошна колона во табелата за пребарување со комбинираните вредности. Оваа колона мора да биде најлевата колона во опсегот за пребарување.
Значи формулата со VPR може да биде вака:
=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)
=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)
Овде, колоните B и C ги содржат имињата на клиентите и имињата на производите, соодветно, и врската Нарачки!$A&$2:$D$2 дефинира табела за пребарување во друг лист.
За да ја направите формулата почитлива, можете да му дадете име на опсегот на прегледи, а потоа формулата ќе изгледа многу поедноставна:
=VLOOKUP(B2&" "&C2,Orders,4,FALSE)
=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)
За да функционира формулата, вредностите во најлевата колона од табелата што ја гледате мора да се комбинираат на ист начин како и во критериумите за пребарување. На сликата погоре, ги комбиниравме вредностите u2bu2band ставивме празно место меѓу нив, на ист начин што треба да го направите во првиот аргумент на функцијата (BXNUMX& “” & CXNUMX).
Запомнете! функција VPR ограничен на 255 знаци, не може да бара вредност долга повеќе од 255 знаци. Имајте го ова на ум и проверете дали должината на саканата вредност не ја надминува оваа граница.
Се согласувам дека додавањето помошна колона не е најелегантно и не секогаш прифатливо решение. Можете да го направите истото без помошната колона, но за тоа би била потребна многу покомплексна формула со комбинација на функции Индекс (ИНДЕКС) и Состаноците (ПОЕ ЕКСПОЗИРАНО).
Ги извлекуваме вредностите 2, 3, итн. со помош на VLOOKUP
Веќе го знаете тоа VPR може да врати само една вредност што одговара, поточно првата пронајдена. Но, што ако оваа вредност се повтори неколку пати во прегледаната низа и сакате да ја извлечете втората или третата од нив? Што ако сите вредности? Проблемот изгледа комплициран, но решението постои!
Да претпоставиме дека една колона од табелата ги содржи имињата на клиентите (Име на клиентот), а другата колона ги содржи производите (Производ) што тие ги купиле. Ајде да се обидеме да ги најдеме 2-ри, 3-ти и 4-ти артикли купени од даден клиент.
Најлесен начин е да додадете помошна колона пред колоната име на клиентот и пополнете го со имиња на клиенти со бројот на повторување на секое име, на пример, Џон До 1, Џон До 2 итн. Ќе го направиме трикот со нумерирање користејќи ја функцијата COUNTIF (COUNTIF), со оглед на тоа што имињата на клиентите се во колоната Б:
=B2&COUNTIF($B$2:B2,B2)
=B2&СЧЁТЕСЛИ($B$2:B2;B2)
После тоа, можете да ја користите нормалната функција VPRза да ја пронајдете потребната нарачка. На пример:
- Најди 2-ви ставка нарачана од клиентот Ден Браун:
=VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ) - Најди 3-ви ставка нарачана од клиентот Ден Браун:
=VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)
Всушност, можете да внесете референца на ќелија како вредност за пребарување наместо текст, како што е прикажано на следната слика:
Ако барате само 2-e повторување, можете да го направите без помошната колона со создавање покомплексна формула:
=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4[Customer Name],0)+2)&":$C16"),2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ("$B$"&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&":$C16");2;ИСТИНА);"")
Во оваа формула:
- $2 F $ – ќелија која го содржи името на купувачот (тоа е непроменето, забележете – врската е апсолутна);
- $ B $ – колона име на клиентот;
- Table4 – Вашата маса (ова место може да биде и редовен опсег);
- $ C16 – крајната ќелија на вашата табела или опсег.
Оваа формула ја наоѓа само втората вредност што се совпаѓа. Ако треба да ги извлечете преостанатите повторувања, користете го претходното решение.
Ако ви треба список со сите совпаѓања - функцијата VPR ова не е помошник, бидејќи враќа само една вредност во исто време – период. Но, Excel има функција Индекс (INDEX), кој лесно може да се справи со оваа задача. Како ќе изгледа таква формула, ќе дознаете во следниот пример.
Вратете ги сите повторувања на саканата вредност
Како што споменавме погоре VPR не може да ги извлече сите дупликати вредности од скенираниот опсег. За да го направите ова, потребна ви е малку посложена формула, составена од неколку функции на Excel, како на пр Индекс (ИНДЕКС), МАЛИ (МАЛО) и ред (ЛИНИЈА)
На пример, формулата подолу ги наоѓа сите повторувања на вредноста од ќелијата F2 во опсегот B2:B16 и го враќа резултатот од истите редови во колоната В.
{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"")}
{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3));"")}
Внесете ја оваа формула за низа во повеќе соседни ќелии, како што се ќелиите Ф4: Ф8како што е прикажано на сликата подолу. Бројот на ќелии мора да биде еднаков или поголем од максималниот можен број на повторувања на бараната вредност. Не заборавајте да кликнете Ctrl + Shift + Enterза правилно внесување на формулата на низата.
Ако сте заинтересирани да разберете како функционира, ајде малку да се нурнеме во деталите на формулата:
Дел 1:
IF($F$2=B2:B16,ROW(C2:C16)-1,"")
ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")
$F$2=B2:B16 - споредете ја вредноста во ќелијата F2 со секоја од вредностите во опсегот B2:B16. Ако се најде совпаѓање, тогаш изразот STRING(C2:C16)-1 го враќа бројот на соодветната линија (вредност -1 ви овозможува да не ја вклучите линијата за заглавие). Ако нема совпаѓања, функцијата IF (IF) враќа празна низа.
Резултат на функцијата IF (АКО) ќе има таква хоризонтална низа: {1,"",3,"",5,"","","","","","",12,"","",""}
Дел 2:
ROW()-3
СТРОКА()-3
Еве ја функцијата ред (ЛИНИЈА) делува како дополнителен бројач. Бидејќи формулата е копирана во ќелиите F4:F9, го одземаме бројот 3 од функцијата резултат да се добие вредност 1 во ќелијата F4 (линија 4, одземете 3) за да добиете 2 во ќелијата F5 (алинеја 5, одземете 3) и така натаму.
Дел 3:
SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
функција МАЛИ (МАЛО) се враќа не-ох најмалата вредност во низата со податоци. Во нашиот случај, која позиција (од најмалата) да се врати е одредена од функцијата ред (ЛИНИЈА) (види Дел 2). Значи, за ќелија F4 функција МАЛА ({ низа}, 1) Се враќа 1-ви (најмал) елемент од низата, т.е 1. За ќелија F5 Се враќа 2-ви најмалиот елемент во низата, т.е 3, Итн
Дел 4:
INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
функција Индекс (INDEX) едноставно ја враќа вредноста на одредена ќелија во низа C2: C16. За ќелија F4 функција ИНДЕКС ($C$2:$C$16) ќе се врати јаболкана F5 функција ИНДЕКС ($C$2:$C$16) ќе се врати Слатки и така натаму.
Дел 5:
IFERROR()
ЕСЛИОШИБКА()
Конечно, ја ставаме формулата во функцијата ИФЕРОР (IFERROR), бидејќи веројатно нема да бидете задоволни од пораката за грешка #AT (# N/A) ако бројот на ќелии во кои е копирана формулата е помал од бројот на дупликати вредности во опсегот што се гледа.
XNUMXD пребарување по познат ред и колона
Вршењето XNUMXD пребарување во Excel вклучува пребарување на вредност по познат број на ред и колона. Со други зборови, ја извлекувате вредноста на ќелијата на пресекот на одреден ред и колона.
Значи, да се свртиме кон нашата табела и да напишеме формула со функција VPR, кој ќе најде информации за цената на продадените лимони во март.
Постојат неколку начини да се изврши XNUMXD пребарување. Проверете ги опциите и изберете ја онаа што најмногу ви одговара.
Функции VLOOKUP и MATCH
Можете да користите еден куп функции VPR (VLOOKUP) и ПОИЗЛОЖЕНИ (MATCH) за да ја пронајдете вредноста на пресекот на полињата Название продукта (низа) и месец (колона) од предметната низа:
=VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)
=ВПР("Lemons";$A$2:$I$9;ПОИСКПОЗ("Mar";$A$1:$I$1;0);ЛОЖЬ)
Формулата погоре е редовна функција VPR, што бара точно совпаѓање на вредноста „Лимони“ во ќелиите А2 до А9. Но, бидејќи не знаете во која колона е продажбата во март, нема да можете да го поставите бројот на колоната за аргументот на третата функција. VPR. Наместо тоа, се користи функцијата ПОИЗЛОЖЕНИза да се дефинира оваа колона.
MATCH("Mar",$A$1:$I$1,0)
ПОИСКПОЗ("Mar";$A$1:$I$1;0)
Преведена на човечки јазик, оваа формула значи:
- Ги бараме ликовите „Мар“ – аргумент вредност на пребарување (барање_вредност);
- Гледање во ќелиите од A1 до I1 - аргумент пребарување_ низа (преглед_низа);
- Враќање на точното совпаѓање - аргумент совпаѓање_тип (тип_совпаѓање).
Користење 0 во третиот аргумент велиш функции ПОИЗЛОЖЕНИ побарајте ја првата вредност што точно се совпаѓа со вредноста што ја барате. Ова е еквивалентно на вредноста НЕТОЧНО (НЕТОЧНО) за четвртиот аргумент VPR.
Така можете да креирате двонасочна формула за пребарување во Excel, позната и како дводимензионално пребарување или двонасочно пребарување.
Функција SUMPRODUCT
функција СУМПРОДУКТ (SUMPRODUCT) го враќа збирот на производите од избраните низи:
=SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar"),$A$2:$I$9)
=СУММПРОИЗВ(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar");$A$2:$I$9)
Функции INDEX и MATCH
Во следната статија детално ќе ги објаснам овие функции, па засега можете само да ја копирате оваа формула:
=INDEX($A$2:$I$9,MATCH("Lemons",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))
=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ("Lemons";$A$2:$A$9;0);ПОИСКПОЗ("Mar";$A$1:$I$1;0))
Именувани опсези и оператор на пресек
Ако не сте во сите тие сложени формули на Excel, можеби ќе ви се допадне овој визуелен и незаборавен начин:
- Изберете ја табелата, отворете го јазичето Формула (Формули) и кликнете Креирај од избор (Креирај од изборот).
- Проверете ги полињата Горниот ред (на линијата погоре) и Лева колона (во колоната лево). Microsoft Excel ќе додели имиња на опсезите од вредностите во горниот ред и левата колона на вашата табела. Сега можете да пребарувате користејќи ги овие имиња директно без да креирате формули.
- Во која било празна ќелија, напишете =ред_име колона_име, на пример вака:
=Лимони Март
…или обратно:
= Мар Лимони
Запомнете дека имињата на редовите и колоните мора да бидат одделени со празно место, кое во овој случај функционира како операторот на пресекот.
Кога внесувате име, Microsoft Excel ќе прикаже совет за алатка со листа на соодветни имиња, исто како кога внесувате формула.
- прес Внесете и проверете го резултатот
Во принцип, кој од горенаведените методи ќе го изберете, резултатот од дводимензионалното пребарување ќе биде ист:
Користење на повеќе VLOOKUP во една формула
Се случува главната табела и табелата за пребарување да немаат една заедничка колона и тоа ве спречува да ја користите вообичаената функција VPR. Сепак, постои уште една табела која не ги содржи информациите за кои не интересира, туку има заедничка колона со главната табела и табелата за пребарување.
Да го погледнеме следниот пример. Имаме Главна табела со колона SKU (нов), каде што сакате да додадете колона со соодветните цени од друга табела. Дополнително, имаме 2 табели за пребарување. Првиот (табела за пребарување 1) содржи ажурирани броеви SKU (нов) и имиња на производи, а вториот (табела за пребарување 2) – имиња на производи и стари броеви SKU (стар).
За да додадете цени од втората табела за пребарување на главната табела, мора да извршите дејство познато како двојно VPR или вгнездени VPR.
- Напишете функција VPR, кој го наоѓа името на производот во табелата Табела за пребарување 1користење SKU, како саканата вредност:
=VLOOKUP(A2,New_SKU,2,FALSE)=ВПР(A2;New_SKU;2;ЛОЖЬ)тука Ново_SKU – именуван опсег $A:$B во табелата Табела за пребарување 1, 2 – ова е колона Б, која ги содржи имињата на стоката (видете ја сликата погоре)
- Напишете формула за вметнување цени од табела Табела за пребарување 2 врз основа на добро познати имиња на производи. За да го направите ова, залепете ја формулата што ја создадовте претходно како вредност за пребарување за новата функција VPR:
=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)=ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)тука Цена – именуван опсег $A: $C во табелата Табела за пребарување 2, 3 е колоната В која содржи цени.
Сликата подолу го покажува резултатот вратен со формулата што ја создадовме:
Динамична замена на податоци од различни табели користејќи VLOOKUP и INDIRECT
Прво, да разјасниме што мислиме со изразот „Динамичка замена на податоци од различни табели“ за да се увериме дека правилно се разбираме.
Има ситуации кога има неколку листови со податоци од ист формат, а потребно е да се извлечат потребните информации од одреден лист, во зависност од вредноста што се внесува во дадена ќелија. Мислам дека е полесно да се објасни ова со пример.
Замислете дека имате извештаи за продажба за неколку региони со исти производи и во ист формат. Сакате да најдете бројки за продажба за одреден регион:
Ако имате само два такви извештаи, тогаш можете да користите срамно едноставна формула со функции VPR и IF (IF) за да го изберете саканиот извештај за пребарување:
=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)
=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)
каде што:
- $ 2 $ XNUMX е ќелија која го содржи името на производот. Забележете дека овде користиме апсолутни референци за да избегнеме менување на вредноста за пребарување при копирање на формулата во други ќелии.
- $ D3 е ќелија со името на регионот. Користиме апсолутна референца за колона и референтна референца на ред бидејќи планираме да ја копираме формулата во други ќелии во истата колона.
- FL_Sales и CA_Sales – имињата на табелите (или именуваните опсези) кои ги содржат соодветните извештаи за продажба. Можете, се разбира, да ги користите вообичаените имиња на листови и референци за опсег на ќелии, на пример 'FL Sheet'!$A$3:$B$10, но именуваните опсези се многу попогодни.
Меѓутоа, кога има многу такви табели, функцијата IF не е најдоброто решение. Наместо тоа, можете да ја користите функцијата ИНДИРЕКТЕН (INDIRECT) за да го вратите саканиот опсег на пребарување.
Како што веројатно знаете, функцијата ИНДИРЕКТЕН се користи за враќање на врската дадена со текстуална низа, што е токму она што ни треба сега. Значи, смело заменете го во горната формула изразот со функцијата IF да се поврзе со функцијата ИНДИРЕКТЕН. Еве една комбинација VPR и ИНДИРЕКТЕН работи одлично со:
=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)
каде што:
- $ 2 $ XNUMX – ова е ќелија со името на производот, таа е непроменета поради апсолутната врска.
- $ D3 е ќелијата што го содржи првиот дел од името на регионот. Во нашиот пример, ова FL.
- _Продажба – заедничкиот дел од името на сите именувани опсези или табели. Кога се комбинира со вредноста во ќелијата D3, таа го формира целосно квалификуваното име на потребниот опсег. Подолу се дадени неколку детали за оние кои се нови во функцијата ИНДИРЕКТЕН.
Како функционираат INDIRECT и VLOOKUP
Прво, дозволете ми да ве потсетам на синтаксата на функцијата ИНДИРЕКТЕН (ИНДИРЕКТНО):
INDIRECT(ref_text,[a1])
ДВССЫЛ(ссылка_на_текст;[a1])
Првиот аргумент може да биде референца на ќелија (стил A1 или R1C1), име на опсег или текстуална низа. Вториот аргумент одредува каков стил на врска е содржан во првиот аргумент:
- A1ако аргументот е ВИСТИНСКИ КОД (ТОЧНО) или не е наведено;
- R1C1, Ако FКАКО Е (НЕТОЧНО).
Во нашиот случај, врската го има стилот A1, за да можете да го изоставите вториот аргумент и да се фокусирате на првиот.
Значи, да се вратиме на нашите продажни извештаи. Ако се сеќавате, тогаш секој извештај е посебна табела сместена на посебен лист. За формулата да работи правилно, мора да ги именувате вашите табели (или опсези), а сите имиња мора да имаат заеднички дел. На пример, вака: CA_Sales, FL_Sales, TX_Sales и така натаму. Како што можете да видите, „_Sales“ е присутна во сите имиња.
функција ИНДИРЕКТЕН ја поврзува вредноста во колоната D и текстуалната низа „_Sales“, со што кажува VPR во која табела да се бара. Ако ќелијата D3 ја содржи вредноста „FL“, формулата ќе ја пребарува табелата FL_Sales, ако „CA“ – во табелата CA_Sales и така натаму.
Резултатот од функциите VPR и ИНДИРЕКТЕН ќе биде следново:
Ако податоците се наоѓаат во различни книги на Excel, тогаш треба да го додадете името на книгата пред именуваниот опсег, на пример:
=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)
Доколку функцијата ИНДИРЕКТЕН се однесува на друга работна книга, таа работна книга мора да биде отворена. Ако е затворена, функцијата ќе пријави грешка. # РЕФ! (#SSYL!).