Тази тема няма за цел в детайли да ви запознае с функциите за търсене. Искам да обърна внимание на разликите (и приликите) между функциите и да обърнете внимание на техните особености. Няма да се спирам в детайли на техните параметри. За целта си има клавиш F1:)
1. Функциите Find и Search служат за търсене вътре в текст. Т.е. може да ги използвате за претърсване вътрешността на клетки. Разликата между двете функции е, че Find прави разлика между малки и главни букви когато търси! Двете функции връщат позицията на намерения текст. Т.е. число! За съжаление вместо нула ако няма успешен резултат от търсенето се връща грешка (#Value) което си е досадно (трябва да се обработи или с ISError или IFError).
2. Функцията Match служи да търси в област от клетки или масив. В този случай разглежда цялата клетка като едно цяло! Връща число показващо номера на първия елемент отговарящ на даденото условие. Връща ПОЗИЦИЯ (брои от едно)!! (NB! Функцията може да търси и "приблизително" но за целта елементите трябва да подредени. За повече вижте помощната информация.) . Бих ви заострил внимание на подразбиращата се стойност на третия параметър (тип на търсенето)! Тя НЕ Е 0(!!) (точно търсене). Ако искате да търсите точно не прескачайте този параметър!
3. Функците Vlookup, Hlookpup, Lookup отново служат за търсене в област от клетки или масив. За разлика от Match те връщат СТОЙНОСТ. Стойността се извлича от областта за търсене или от друга област. Всъщност тези функции са "глезотии" за улеснение на потребителите.:) Могат да бъдат създадени с помощта на други функции. Ще дам примери по-късно.
Трябва да се отбележи един недостатък на Match, Vlookup и другите функции от тази група, че не правят разлика между малки главни букви. Т.е. ако ви трябва наистина точно съвпадение ще се наложи да прибягвате към различни трикове.
Ето примерните данни:
 |
| Примерни данни |
Пример 1. Да се намери стойността на колонка "C" за 1.10.2010 в колонка "A".
Тук се решава с "простия" Vlookup.
=VLOOKUP(DATEVALUE("1.10.2010"),A1:C6,3,FALSE)
Функцията връща стойността на ТРЕТАТА колонка от блока търсейки в първата колона. Да не е прекалено скучно с тази формулка ви напомням, че датите и часовете са ЧИСЛА и ако търсите определена стойност трябва да я преобразувате в дата/час! В случая се налага да преобразуваме текстовата стойност в дата чрез DateValue! Ако датата беше записана като дата в дадена клетка (например e1) формулата щеше да изглежда:
=VLOOKUP(E1,A1:C6,3,FALSE) без преобразуване.
Но да се върнем към нашата задача. Функцията Vlookup често се явява нещо като Еверест и когато някой HR чуе че кандидата я знае направо подскача от радост. За мен това е излишна радост:) Vlookup си е много много базова функция:)
Ще дава още един вариант на решение на тази задача:
=INDEX(A1:C6,MATCH(DATEVALUE("1.10.2010"),A1:A6,0),3)
Хммм... Сега ще ме обвините, че е по-сложно! Да но е по-универсално! За това се опитайте да разберете как действа този пример и сте има няма няколко педи (не повече) над "Еверест-а " Vlookup:) В #010 разгледахме Index. Казах тогава че по-подходяща за показване на единична стойност. При нея се задава блок и се посочват координатите (ПОЗИЦИИТЕ!) на реда и на колоната. Колонката както при Vlookup е третата колона. "Далаверата" е, че контролираме по-добре и частта с реда. В случая използваме Match (зеления сегмент). Не забравяйте да укажете третия параметър (НУЛА за точно търсене)! Обърнете внимание че ние задаваме зоната за търсене в Match (A1:A6) и може да няма нищо общо със зоната за извличане в Index!
Пример 2 Да се намери датата която да отговаря числото 50 в третата колонка.
"Майсторите" на Vlookup-a загинаха:) Vlookup има "дефектът" ВИНАГИ да търси в първата колонка на блока. Този дефект е незабележим в голяма част от ситуациите, защото първата колонка най-често е колонката за търсене. Но не винаги :(
Ето решението (ако вече не сте се справили):
=INDEX(A1:C6,MATCH(50,C1:C6,0),1)
Просто е:) Търсим в третата колонка (C1:C6) и връщаме стойност от първата. Елементарно. Просто го запомнете;)
Пример 3 Да се намери стойността на трети ред за колонката съдържаща числото 20 в първия си ред. Тук се извършва хоризонтално търсене.
Този пример не е много подходящ за данните, но искам да ви покажа как да "ритнем" и Hlookup от нашите примери:) Ето "класическо" търсене в първия ред:
=HLOOKUP(20,A1:C6,3,FALSE)
Ето по-гъвкавия вариант:
=INDEX(A1:C6,3,MATCH(20,A1:C1,0))
Тук редът от който извличаме е константа, а Match определя колонката! Нищо сложно:) Запомнете втория вариант и забравете HLookup:)
Пример 4 Да намерим позицията първата клетка в колонка B която съдържа "А".
Първи опит: =MATCH("A",B1:B6,0)
Независимо, че сме сложи "A" и 0 (точно) търсене. За данните които съм дал функцията връща 2 (!), което е позицията на малкото "a". Както ви обърнах внимание Match (и братовчедите) не разбират от малки и главни букви. За целта ще се наложи малко "гимнастика":)
Втори опит (отговор): {=MATCH(TRUE,EXACT("A",B1:B6),0)}
Тази формула я въвеждате без {}, но със Shift+Ctrl+Enter! Това е функция за работа с масиви.
Тук използваме функцията EXACT която връща истина или лъжа ако имаме ПЪЛНО съвпадение между два текста. В случая резултатът от тази функция ще бъде {FALSE, FALSE,TRUE,FALSE,TRUE,FALSE}.
Остава да намерим първия True с помощта на Match:) Елементарно:) За да проработи не забравяйте Ctrl+Shift+Enter при въвеждането.
Пример 5. Да намерим позицията на първата клетка която вътре в себе си съдържа "А".
За разлика от предходния пример тук търсим дали няма някъде в текстът буквата "А"
Отговор: =MATCH(TRUE,NOT(ISERROR(FIND("A",B1:B6))),0)Пример 6: Да се намери най-голямата дата за която стойността в колонка C е по-голяма от 20.
Отговор 1: {=MAX((C1:C6>20)*(A1:A6))}
Въведете с Ctrl+Shift+Enetr! Форматирайте резултатната клетка като дата!
Тук нещата са леко екзотични:) Извършва се умножение на два масива. Първия е с нули и единици в зависимост от резултата на условието. Втория са датите.
{0,1,1,0,1,1}*{1.1.2010,1.2.2010, 1.10.2010, 1.9.2010, 1.8.2010, 1.7.2010} в резултат се получава
{0,1.2.2010, 1.10.2010, 0, 1.8.2010, 1.7.2010}. Чрез функцията MAX извличаме най-голямата дата.
Отговор 2: =SUMPRODUCT(MAX((C1:C6>20)*(A1:A6)))
Тук използваме една възможност за "пакетиране" на Max с цел избягване на функцията за работа с масиви. Използваме Sumproduct.Тази функция е "нормална" и няма нужда от въвеждането и с Ctrl+Shift+Eneter!
Hint! Ако искате направо да се форматира като дата може да използвате
=TEXT(SUMPRODUCT(MAX((C1:C6>20)*(A1:A6))),"dd.mm.yyyy")
Пример 7: Да се намери най-малката дата за която стойността в колонка C е по-голяма от 20.
Може по инерция да решите, че всичко ви е ясно и да си помислите, че заменяйки MAX с MIN си решавате проблемът. Да ама не:) Ако обърнете внимание на резултатния масив от по-горния пример ще видите, че най-малката стойност е нула:( което автоматично ви реже възможността да използвате Min върху произведението на двата масива. За целта се налага леко да видоизменим по-горната формула:
Отговор: {=MIN(IF(C1:C6>20,A1:A6))}
Формулата е за работа с масиви (Ctrl+Shift+Enter!) и няма как да бъде "пакетирана" и "нормализирана! В случая чрез IF-а се създава масив в който има само датите които ни интересуват. Без нулеви стойности!
NB! Много се дискутира до колко формулите за масиви са по-бавни от нормалните. Из доста от формули се водят доста оживени дискусии. Честно казано единствената причина да използвам SumProduct и други трикове за нормализация е, че формулите за работа с масиви се редактират по-трудно (има по-стара тема по въпросът). Не съм установил чак толкова огромна разлика във времената.
Толкова по темата за търсене за сега:) Ако се сетя още нещо или имате някакъв конкретен практически пример и въпроси може да има втора серия:)
Няма коментари:
Публикуване на коментар