Съдържание
- Търсете в Excel по множество критерии
- Ние извличаме 2-ри, 3-ти и т.н. стойности с помощта на VLOOKUP
- Извлечете всички повторения на желаната стойност
- XNUMXD търсене по известен ред и колона
- Използване на множество VLOOKUPs в една формула
- Динамично заместване на данни от различни таблици с помощта на VLOOKUP и INDIRECT
Във втората част на нашия урок за функцията ВПР (VLOOKUP) в Excel, ще анализираме няколко примера, които ще ви помогнат да насочите цялата мощност ВПР за решаване на най-амбициозните задачи на Excel. Примерите предполагат, че вече имате основни познания за това как работи тази функция. Ако не, може да ви е интересно да започнете с първата част на този урок, който обяснява синтаксиса и основната употреба. ВПР. Е, да започваме.
Търсете в Excel по множество критерии
функция ВПР в Excel е наистина мощен инструмент за извършване на търсене на определена стойност в база данни. Има обаче значително ограничение – неговият синтаксис ви позволява да търсите само една стойност. Ами ако искате да търсите по множество условия? Ще намерите решението по-долу.
Пример 1: Търсене по 2 различни критерия
Да предположим, че имаме списък с поръчки и искаме да намерим Количество стоки (Кол.), въз основа на два критерия – Име на клиента (Клиент) и Название продукт (Продукт). Въпросът се усложнява от факта, че всеки от купувачите е поръчал няколко вида стоки, както се вижда от таблицата по-долу:
редовна функция ВПР няма да работи в този сценарий, защото ще върне първата намерена стойност, която съответства на дадената стойност за търсене. Например, ако искате да знаете количеството на даден артикул Захарни изделия'по поръчка на купувача Джеръми Хил, напишете следната формула:
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)
=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)
– тази формула ще върне резултата 15съответстващ на продукта ябълки, защото това е първата стойност, която съвпада.
Има просто решение – създайте допълнителна колона, в която да комбинирате всички желани критерии. В нашия пример това са колоните Име на клиента (Клиент) и Название продукт (Продукт). Не забравяйте, че обединената колона трябва винаги да е най-лявата колона в диапазона за търсене, тъй като това е лявата колона, в която функцията ВПР гледа нагоре, когато търси стойност.
И така, добавяте спомагателна колона към таблицата и копирате следната формула върху всички нейни клетки: =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 – Аргумент col_index_num (column_number), т.е. номера на колоната, съдържаща данните за извличане.
Пример 2: VLOOKUP по два критерия с таблица, която се разглежда на друг лист
Ако трябва да актуализирате основната таблица (Main table) чрез добавяне на данни от втората таблица (Lookup table), която се намира на друг лист или в друга работна книга на Excel, тогава можете да съберете желаната стойност директно във формулата, която вмъквате в главната маса.
Както в предишния пример, ще ви е необходима спомагателна колона в справочната таблица с комбинираните стойности. Тази колона трябва да е най-лявата колона в диапазона за търсене.
Така че формулата с ВПР може да бъде така:
=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).
Помня! функция ВПР ограничен до 255 знака, той не може да търси стойност с повече от 255 знака. Имайте това предвид и се уверете, че дължината на желаната стойност не надвишава това ограничение.
Съгласен съм, че добавянето на спомагателна колона не е най-елегантното и не винаги приемливо решение. Можете да направите същото без помощната колона, но това ще изисква много по-сложна формула с комбинация от функции INDEX (ИНДЕКС) и MATCH (ПО-ИЗЛОЖЕН).
Ние извличаме 2-ри, 3-ти и т.н. стойности с помощта на VLOOKUP
Вие вече знаете това ВПР може да върне само една съответстваща стойност, по-точно първата намерена. Но какво ще стане, ако тази стойност се повтаря няколко пъти в разглеждания масив и искате да извлечете 2-ри или 3-ти от тях? Ами ако всички стойности? Проблемът изглежда сложен, но решението съществува!
Да предположим, че една колона на таблицата съдържа имената на клиентите (Име на клиента), а другата колона съдържа продуктите (Продукт), които са закупили. Нека се опитаме да намерим 2-ри, 3-ти и 4-ти артикул, закупени от даден клиент.
Най-лесният начин е да добавите помощна колона преди колоната Потребителско име и го попълнете с имена на клиенти с номера на повторение на всяко име, например, Джон Доу1, Джон Доу2 и т.н. Ще направим номера с номерирането с помощта на функцията COUNTIF (COUNTIF), като се има предвид, че имената на клиентите са в колона B:
=B2&COUNTIF($B$2:B2,B2)
=B2&СЧЁТЕСЛИ($B$2:B2;B2)
След това можете да използвате нормалната функция ВПРза намиране на необходимия ред. Например:
- Какво 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-е повторение, можете да го направите без помощната колона, като създадете по-сложна формула:
=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;ИСТИНА);"")
В тази формула:
- $F$2 – клетка, съдържаща името на купувача (то е непроменено, имайте предвид – връзката е абсолютна);
- $ B $ - колона Потребителско име;
- Table4 – Вашата маса (това място може да бъде и обикновен асортимент);
- $ C16 – крайната клетка на вашата таблица или диапазон.
Тази формула намира само втората съответстваща стойност. Ако трябва да извлечете оставащите повторения, използвайте предишното решение.
Ако имате нужда от списък с всички съвпадения – функцията ВПР това не е помощник, тъй като връща само една стойност наведнъж – точка. Но Excel има функция INDEX (ИНДЕКС), който лесно може да се справи с тази задача. Как ще изглежда такава формула, ще научите в следния пример.
Извлечете всички повторения на желаната стойност
Както е споменато по-горе ВПР не може да извлече всички дублиращи се стойности от сканирания диапазон. За да направите това, имате нужда от малко по-сложна формула, съставена от няколко функции на Excel, като напр INDEX (ИНДЕКС), МАЛКИ (МАЛКО) и РЕД (ЛИНИЯ)
Например формулата по-долу намира всички повторения на стойността от клетка F2 в диапазона B2:B16 и връща резултата от същите редове в колона C.
{=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));"")}
Въведете тази формула за масив в множество съседни клетки, като клетките F4: F8както е показано на фигурата по-долу. Броят на клетките трябва да бъде равен или по-голям от максималния възможен брой повторения на търсената стойност. Не забравяйте да щракнете 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. Ако се намери съвпадение, тогава изразът НИГА(C2:C16)-1 връща номера на съответния ред (стойност -1 ви позволява да не включвате заглавния ред). Ако няма съвпадения, функцията IF (IF) връща празен низ.
Резултат от функцията IF (АКО) ще има такъв хоризонтален масив: {1,"",3,"",5,"","","","","","",12,"","",""}
Част 2:
ROW()-3
СТРОКА()-3
Ето функцията РЕД (LINE) действа като допълнителен брояч. Тъй като формулата се копира в клетки 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 (INDEX) просто връща стойността на конкретна клетка в масив С2: С16. За клетка F4 функция ИНДЕКС($C$2:$C$16) Ще се върне ябълкиЗа F5 функция ИНДЕКС($C$2:$C$16) Ще се върне Захарни изделия' и така нататък.
Част 5:
IFERROR()
ЕСЛИОШИБКА()
Накрая поставяме формулата във функцията АКО ГРЕШКА (IFERROR), защото е малко вероятно да сте доволни от съобщението за грешка #AT (#N/A), ако броят на клетките, в които се копира формулата, е по-малък от броя на дублиращите се стойности в преглеждания диапазон.
XNUMXD търсене по известен ред и колона
Извършването на XNUMXD търсене в Excel включва търсене на стойност по известен номер на ред и колона. С други думи, вие извличате стойността на клетката в пресечната точка на определен ред и колона.
И така, нека се обърнем към нашата таблица и да напишем формула с функция ВПР, който ще намери информация за себестойността на продадените лимони през март.
Има няколко начина за извършване на XNUMXD търсене. Разгледайте опциите и изберете този, който ви подхожда най-добре.
Функции VLOOKUP и MATCH
Можете да използвате куп функции ВПР (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);ЛОЖЬ)
Формулата по-горе е нормална функция ВПР, който търси точно съвпадение на стойността „Лимони“ в клетки от A2 до A9. Но тъй като не знаете в коя колона са разпродажбите през март, няма да можете да зададете номера на колоната за третия аргумент на функцията. ВПР. Вместо това се използва функцията ПО-ИЗЛОЖЕНза да дефинирате тази колона.
MATCH("Mar",$A$1:$I$1,0)
ПОИСКПОЗ("Mar";$A$1:$I$1;0)
Преведена на човешки език, тази формула означава:
- Търсим знаците „Мар“ – аргумент търсена_стойност (търсеща_стойност);
- Търсене в клетки от A1 до I1 – аргумент търсене_масив (търсен_масив);
- Връща точно съвпадение – аргумент съвпадение_тип (тип_съвпадение).
Използването на 0 в третия аргумент казвате функции ПО-ИЗЛОЖЕН потърсете първата стойност, която точно съвпада със стойността, която търсите. Това е еквивалентно на стойността FALSE (FALSE) за четвъртия аргумент ВПР.
Ето как можете да създадете двупосочна формула за търсене в Excel, известна още като двуизмерно търсене или двупосочно търсене.
Функция SUMPRODUCT
функция 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 ще покаже подсказка със списък от съвпадащи имена, точно както когато въвеждате формула.
- Натискане Въведете и проверете резултата
Като цяло, който и от горните методи да изберете, резултатът от двуизмерното търсене ще бъде същият:
Използване на множество VLOOKUPs в една формула
Случва се основната таблица и справочната таблица да нямат нито една обща колона и това ви пречи да използвате обичайната функция ВПР. Има обаче друга таблица, която не съдържа информацията, която ни интересува, но има обща колона с основната таблица и справочната таблица.
Нека да разгледаме следния пример. Имаме Основна таблица с колона SKU (нов), където искате да добавите колона със съответните цени от друга таблица. Освен това имаме 2 справочни таблици. Първата (Таблица за справка 1) съдържа актуализирани числа SKU (нов) и имена на продукти, а втората (Таблица за справка 2) – имена на продукти и стари номера SKU (стар).
За да добавите цени от втората справочна таблица към основната таблица, трябва да извършите действие, известно като двойно ВПР или вложени ВПР.
- Напишете функция ВПР, който намира името на продукта в таблицата Справочна таблица 1използвайки Код, като желаната стойност:
=VLOOKUP(A2,New_SKU,2,FALSE)=ВПР(A2;New_SKU;2;ЛОЖЬ)Тук New_SKU – наименуван диапазон $A:$B на масата Справочна таблица 1, 2 – това е колона B, която съдържа имената на стоките (вижте снимката по-горе)
- Напишете формула за вмъкване на цени от таблица Справочна таблица 2 въз основа на добре познати имена на продукти. За да направите това, поставете формулата, която сте създали по-рано, като стойност за търсене за новата функция ВПР:
=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)=ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)Тук Цена – наименуван диапазон $A:$C на масата Справочна таблица 2, 3 е колона C, съдържаща цени.
Фигурата по-долу показва резултата, върнат от формулата, която създадохме:
Динамично заместване на данни от различни таблици с помощта на VLOOKUP и INDIRECT
Първо, нека изясним какво имаме предвид под израза „Динамично заместване на данни от различни таблици“, за да сме сигурни, че се разбираме правилно.
Има ситуации, когато има няколко листа с данни от един и същи формат и е необходимо да се извлече необходимата информация от определен лист, в зависимост от стойността, която е въведена в дадена клетка. Мисля, че е по-лесно да обясня това с пример.
Представете си, че имате отчети за продажби за няколко региона с едни и същи продукти и в същия формат. Искате да намерите данни за продажбите за определен регион:
Ако имате само два такива отчета, тогава можете да използвате позорно проста формула с функции ВПР и IF (АКО), за да изберете желания отчет за търсене:
=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)
=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)
Където:
- $D$2 е клетка, съдържаща името на продукта. Обърнете внимание, че тук използваме абсолютни препратки, за да избегнем промяна на стойността за търсене при копиране на формулата в други клетки.
- $D3 е клетка с името на региона. Използваме абсолютна препратка към колона и относителна препратка към ред, защото планираме да копираме формулата в други клетки в същата колона.
- FL_Салes и CA_Продажби – имената на таблиците (или наименуваните диапазони), които съдържат съответните отчети за продажбите. Можете, разбира се, да използвате обичайните имена на листове и препратки към диапазони от клетки, например „FL лист“!$A$3:$B$10, но именуваните диапазони са много по-удобни.
Когато обаче има много такива таблици, функцията IF не е най-доброто решение. Вместо това можете да използвате функцията НЕПРЯК (INDIRECT), за да върнете желания диапазон на търсене.
Както вероятно знаете, функцията НЕПРЯК се използва за връщане на връзка, дадена от текстов низ, което е точно това, от което се нуждаем сега. Така че, смело заменете в горната формула израза с функцията IF за свързване с функция НЕПРЯК. Ето една комбинация ВПР и НЕПРЯК работи чудесно с:
=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)
Където:
- $D$2 – това е клетка с името на продукта, не се променя поради абсолютния линк.
- $D3 е клетката, съдържаща първата част от името на региона. В нашия пример това FL.
- _Продажби – общата част от името на всички именувани диапазони или таблици. Когато се комбинира със стойността в клетка D3, тя формира пълното име на необходимия диапазон. По-долу са някои подробности за тези, които са нови за функцията НЕПРЯК.
Как работят INDIRECT и VLOOKUP
Първо, нека ви напомня синтаксиса на функцията НЕПРЯК (НЕПРЯК):
INDIRECT(ref_text,[a1])
ДВССЫЛ(ссылка_на_текст;[a1])
Първият аргумент може да бъде препратка към клетка (стил A1 или R1C1), име на диапазон или текстов низ. Вторият аргумент определя какъв стил на връзката се съдържа в първия аргумент:
- A1ако аргументът е ИСТИНСКИ КОД (ВЯРНО) или не е посочено;
- R1C1, Ако FКАТО Е (НЕВЯРНО).
В нашия случай връзката има стил A1, така че можете да оставите втория аргумент и да се съсредоточите върху първия.
И така, нека се върнем към нашите отчети за продажбите. Ако си спомняте, тогава всеки отчет е отделна таблица, разположена на отделен лист. За да работи формулата правилно, трябва да дадете име на вашите таблици (или диапазони) и всички имена трябва да имат обща част. Например така: CA_Продажби, FL_Продажби, TX_Продажби и така нататък. Както можете да видите, „_Sales“ присъства във всички имена.
функция НЕПРЯК свързва стойността в колона D и текстовия низ „_Sales“, като по този начин казва ВПР в коя таблица да търсите. Ако клетка D3 съдържа стойността „FL“, формулата ще търси в таблицата FL_Продажби, ако е “CA” – в таблицата CA_Продажби и така нататък.
Резултатът от функциите ВПР и НЕПРЯК ще бъде следното:
Ако данните се намират в различни книги на Excel, тогава трябва да добавите името на книгата преди наименования диапазон, например:
=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)
Ако функцията НЕПРЯК препраща към друга работна книга, тази работна книга трябва да е отворена. Ако е затворена, функцията ще отчете грешка. #REF! (#SSYL!).