Съдържание
Този малък урок обяснява как да създадете функция ВПР (VLOOKUP) чувствителен към малки и главни букви, показва няколко други формули, които Excel може да търси по чувствителен към малки и главни букви начин, и посочва силните и слабите страни на всяка функция.
Предполагам, че всеки потребител на Excel знае коя функция извършва вертикално търсене. Точно така, това е функция ВПР. Малцина обаче знаят това ВПР не е чувствителен към малки и големи букви, т.е. малките и главните букви са идентични за него.
Ето един бърз пример, демонстриращ неспособността ВПР разпознават регистър. Да предположим в клетка A1 съдържа стойността „сметка“ и клетката A2 – „Бил“, формула:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
… ще спре търсенето си на „сметка“, тъй като тази стойност е първа в списъка, и ще извлече стойността от клетката B1.
По-късно в тази статия ще ви покажа как да направите ВПР различаващ главни от малки букви. Освен това ще научим още няколко функции, които могат да извършват търсене с малки и големи букви в Excel.
Ще започнем с най-простото – ВИЖТЕ (ТЪРСЕНЕ) и SUMPRODUCT (SUMPRODUCT), които за съжаление имат няколко съществени ограничения. След това ще разгледаме по-отблизо малко по-сложната формула ИНДЕКС+МАЧ (INDEX+MATCH), който работи безупречно във всяка ситуация и с всякакъв набор от данни.
Функцията VLOOKUP е чувствителна към главни и малки букви
Както вече знаете, обичайната функция ВПР не е чувствителен към главни и малки букви. Има обаче начин да го направите чувствителен към главни и малки букви. За да направите това, трябва да добавите помощна колона към таблицата, както е показано в следващия пример.
Да предположим, че в колона B има продуктови идентификатори (артикул) и искате да извлечете цената на продукта и съответния коментар от колоните C и D. Проблемът е, че идентификаторите съдържат както малки, така и главни букви. Например стойности на клетки B4 (001Tvci3u) и B5 (001Tvci3U) се различават само в случая на последния знак, u и U съответно.
Както можете да си представите, обичайната формула за търсене
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
Ще се върне $ 90, тъй като стойността 001Твци3у е в диапазона на търсене по-рано от 001Твци3У. Но това не е това, от което се нуждаем, нали?
За търсене с функция ВПР в Excel, чувствителен към главни и малки букви, ще трябва да добавите помощна колона и да попълните нейните клетки със следната формула (където B е колоната за справка):
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & CODE(MID(B2,4,1)) & CODE(MID(B2,5,1)) & CODE(MID(B2,6,1)) & CODE(MID(B2,7,1)) & CODE(MID(B2,8,1)) & IFERROR(CODE(MID(B2,9,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & КОДСИМВ(ПСТР(B2;4;1)) & КОДСИМВ(ПСТР(B2;5;1)) & КОДСИМВ(ПСТР(B2;6;1)) & КОДСИМВ(ПСТР(B2;7;1)) & КОДСИМВ(ПСТР(B2;8;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;9;1));"")
Тази формула разделя желаната стойност на отделни знаци, заменя всеки знак с неговия код (например вместо A на 65, вместо това a код 97) и след това комбинира тези кодове в уникален низ от числа.
След това използваме проста функция ВПР за чувствително търсене при търсене:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Правилна работа на функцията ВПР чувствителното към малки и малки букви зависи от два фактора:
- Помощната колона трябва да е най-лявата колона във видимия диапазон.
- Стойността, която търсите, трябва да съдържа код на символа вместо реалната стойност.
Как да използвате функцията CODE правилно
Формулата, вмъкната в клетките на спомагателната колона, предполага, че всичките ви стойности за търсене имат еднакъв брой знаци. Ако не, тогава трябва да знаете най-малките и най-големите числа и да добавите толкова функции АКО ГРЕШКА (IFERROR) колко знака е разликата между най-кратката и най-дългата търсена стойност.
Например, ако най-кратката стойност за търсене е 3 знака, а най-дългата е 5 знака, използвайте тази формула:
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & IFERROR(CODE(MID(B2,3,1)),"") & IFERROR(CODE(MID(B2,4,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;3;1));"") & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;4;1));"")
За функция PSTR (MID) Предоставяте следните аргументи:
- 1-ви аргумент – текст (текст) е текстът или препратката към клетка, съдържаща знаците за извличане (в нашия случай това е B2)
- 2-ви аргумент – начален_номер (начална_позиция) е позицията на първия от тези знаци, които ще бъдат извлечени. влизаш 1 в първата функция PSTR, 2 – във втората функция PSTR и т.н.
- 3-ви аргумент – брой_символи (number_of_characters) – Указва броя знаци за извличане от текста. Тъй като имаме нужда само от 1 символ през цялото време, във всички функции, които пишем 1.
ОГРАНИЧЕНИЯ: функция ВПР не е най-доброто решение за чувствителни към главни и малки букви търсения в Excel. Първо, необходимо е добавянето на спомагателна колона. Второ, формулата върши добра работа само ако данните са хомогенни или е известен точният брой знаци в търсените стойности. Ако това не е вашият случай, по-добре е да използвате едно от решенията, които показваме по-долу.
Функция LOOKUP за търсене, чувствително към главни и малки букви
функция ВИЖТЕ (LOOKUP) свързани ВПР, но неговият синтаксис позволява търсене, чувствително към главни и малки букви, без добавяне на спомагателна колона. За да направите това, използвайте ВИЖТЕ съчетано с функцията ТОЧНО (ТОЧНО).
Ако вземем данните от предишния пример (без спомагателна колона), следната формула ще се справи със задачата:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Формула търси в обхват A2: A7 точно съвпадение със стойността на клетката F2 чувствителен към главни и малки букви и връща стойността от колона B на същия ред.
като ВПРфункция ВИЖТЕ работи еднакво с текстови и числови стойности, както можете да видите на екранната снимка по-долу:
Важно! За да може функцията ВИЖТЕ работи правилно, стойностите в колоната за търсене трябва да бъдат сортирани във възходящ ред, т.е. от най-малката към най-голямата.
Нека накратко обясня как работи функцията ТОЧНО във формулата, показана по-горе, тъй като това е ключовият момент.
функция ТОЧНО сравнява двете текстови стойности в първия и втория аргумент и връща TRUE, ако са напълно еднакви, или FALSE, ако не са. За нас е важно функцията ТОЧНО различаващ главни от малки букви.
Нека видим как работи нашата формула ОГЛЕД+ТОЧЕН:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- функция ТОЧНО сравнява стойността на клетката F2 с всички елементи в колона A (A2:A7). Връща TRUE, ако бъде намерено точно съвпадение, в противен случай FALSE.
- Тъй като давате първия аргумент на функцията ВИЖТЕ стойност TRUE, той извлича съответната стойност от указаната колона (в нашия случай, колона B) само ако бъде намерено точно съвпадение, чувствително към главни и малки букви.
Надявам се, че това обяснение беше ясно и сега разбирате основната идея. Ако е така, тогава няма да имате никакви затруднения с други функции, които ще анализираме допълнително, защото. всички работят на един принцип.
ОГРАНИЧЕНИЯ: Данните в колоната за търсене трябва да бъдат сортирани във възходящ ред.
SUMPRODUCT – намира текстови стойности, чувствителни към главни и малки букви, но връща само числа
Както вече разбрахте от заглавието, SUMPRODUCT (SUMPRODUCT) е друга функция на Excel, която ще ви помогне да извършите търсене с малки и големи букви, но ще върне само числови стойности. Ако тази опция не ви подхожда, можете веднага да продължите към пакета ИНДЕКС+МАЧ, което дава решение за всеки случай и за всякакви типове данни.
Първо, позволете ми да обясня накратко синтаксиса на тази функция, това ще ви помогне да разберете по-добре следната формула, чувствителна към главни и малки букви.
функция SUMPRODUCT умножава елементите на дадените масиви и връща сумата от резултатите. Синтаксисът изглежда така:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Тъй като се нуждаем от търсене, чувствително към главни и малки букви, използваме функцията ТОЧНО (ТОЧНО) от предишния пример като един от множителите:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Както си спомняте, ТОЧНО сравнява стойността на клетката F2 с всички елементи в колона A. Връща TRUE, ако бъде намерено точно съвпадение, в противен случай FALSE. При математическите операции Excel приема TRUE като 1и FALSE за 0още SUMPRODUCT умножава тези числа и сумира резултатите.
Нулите не се броят, защото при умножаване винаги дават 0. Нека да разгледаме по-подробно какво се случва при точно съвпадение в колона A намерени и върнати 1… Функция SUMPRODUCT умножава числото в колоната B on 1 и връща резултата – точно същото число! Това е така, защото резултатите от другите продукти са нула и те не влияят на резултантната сума.
За съжаление функцията SUMPRODUCT не може да работи с текстови стойности и дати, тъй като те не могат да бъдат умножени. В този случай ще получите съобщение за грешка #VALUE! (#VALUE!) като в клетка F4 на снимката по-долу:
ОГРАНИЧЕНИЯ: Връща само числови стойности.
INDEX + MATCH – търсене с малки и големи букви за всеки тип данни
И накрая, близо сме до неограничена и чувствителна към малки и главни букви формула за търсене, която работи с всеки набор от данни.
Този пример е последен, не защото най-доброто е оставено за десерт, а защото знанията, получени от предишните примери, ще ви помогнат да разберете по-добре и по-бързо формулата, чувствителна към главни и малки букви. ИНДЕКС+МАЧ (ИНДЕКС+МАЧ).
Както вероятно се досещате, комбинацията от функции ПО-ИЗЛОЖЕН и INDEX използвани в Excel като по-гъвкава и мощна алтернатива за ВПР. Статията Използване на INDEX и MATCH вместо VLOOKUP ще обясни идеално как тези функции работят заедно.
Само ще повторя основните моменти:
- функция ПО-ИЗЛОЖЕН (MATCH) търси стойност в даден диапазон и връща нейната относителна позиция, тоест номера на реда и/или колоната;
- След това функцията INDEX (INDEX) връща стойност от определена колона и/или ред.
За формулиране ИНДЕКС+МАЧ може да търси чувствително към главни и малки букви, трябва само да добавите една функция към него. Не е трудно да се досетите отново какво е ТОЧНО (ТОЧНО):
=INDEX($B$2:$B$7,MATCH(TRUE,EXACT($A$2:$A$7,$F$2),0))
=ИНДЕКС($B$2:$B$7;ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);0))
В тази формула ТОЧНО работи по същия начин, както във връзка с функцията ВИЖТЕ, и дава същия резултат:
Имайте предвид, че формулата ИНДЕКС+МАЧ оградена във фигурни скоби е формула за масив и трябва да я завършите с натискане Ctrl + Shift + Enter.
Защо INDEX+MATCH е най-доброто решение за търсене с малки и големи букви?
Основните предимства на пакета INDEX и ПО-ИЗЛОЖЕН:
- Не изисква добавяне на спомагателна колона, за разлика от ВПР.
- Не изисква колоната за търсене да бъде сортирана, за разлика от ВИЖТЕ.
- Работи с всички видове данни – числа, текст и дати.
Тази формула изглежда перфектна, нали? Всъщност не е така. И ето защо.
Да приемем, че клетката в колоната за върната стойност, свързана с търсената стойност, е празна. Какъв резултат ще върне формулата? Не? Нека да видим какво всъщност връща формулата:
Ами сега, формулата връща нула! Това може да не е голям проблем, ако работите с чисто текстови стойности. Ако обаче таблицата съдържа числа, включително „реални“ нули, това се превръща в проблем.
Всъщност всички други формули за търсене (VLOOKUP, LOOKUP и SUMPRODUCT), които обсъдихме по-рано, се държат по същия начин. Но вие искате перфектната формула, нали?
За да направите формула чувствителна към главни и малки букви ИНДЕКС+МАЧ перфектно, поставете го във функция IF (IF), който ще тества клетка с върната стойност и ще върне празен резултат, ако е празна:
=IF(INDIRECT("B"&(1+MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)))<>"",INDEX($B$2:$B$7, MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)),"")
=ЕСЛИ(ДВССЫЛ("B"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0)))<>"";ИНДЕКС($B$2:$B$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0));"")
В тази формула:
- B е колона с върнати стойности
- 1+ е число, което обръща относителната позиция на клетката, върната от функцията ПО-ИЗЛОЖЕН, до реалния адрес на клетката. Например в нашата функция ПО-ИЗЛОЖЕН даден масив за търсене A2: A7, тоест относителната позиция на клетката A2 ще 1, защото е първият в масива. Но действителното положение на клетката A2 в колоната е 2, така че добавяме 1да компенсира разликата и да има функцията НЕПРЯК (INDIRECT) извлече стойността от желаната клетка.
Снимките по-долу показват коригираната формула, чувствителна към главни и малки букви ИНДЕКС+МАЧ В действие. Той връща празен резултат, ако върнатата клетка е празна.
Пренаписах формулата в колони Б:Дза да пасне на лентата с формули на екранната снимка.
Формулата се връща 0ако върнатата клетка съдържа нула.
Ако искате връзката INDEX и ПО-ИЗЛОЖЕН показва някакво съобщение, когато върнатата стойност е празна, можете да го запишете в последните кавички (“”) на формулата, например, така:
=IF(INDIRECT("D"&(1+MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)))<>"",INDEX($D$2:$D$7, MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)),"There is nothing to return, sorry.")
=ЕСЛИ(ДВССЫЛ("D"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0)))<>"";ИНДЕКС($D$2:$D$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0));"There is nothing to return, sorry.")
