#043 Как се коригират скали и има ли "Тъп 0" като оценка:)

Преди време получих писмо (и чинно му отговорих):) Но реших, че си заслужава да напиша отговора и в тема за да съм полезен и на други. Ето писмото:

Попаднах случайно на блога ти за трикове в Ексел, докато търсех информация за функцията CHOOSE. Изглежда интересен и полезен, но аз съм още начинаещ. Та в този смисъл в последно време се сблъсквам доста често с Ексел. Озадачи ме следното: =CHOOSE(O4-1;"Слаб";"Среден";"Добър";"Мн.добър";"Отличен").

Защо от О4 изваждаме една единица.

Лесно се намира в помощната информация на Еxcel, че Choose служи за преобразуване на числова стойност. Първия параметър е стойността, а останалите са отговорите в зависимост от нейната стойност. Отговорите са подредени по следния начин "Отговор при стойност на първия параметър ЕДНО", "Отговор при стойност на първия параметър ДВЕ" и т.н. Т.е. скалата на отговрите е 1,2,3,4,5.... Да разгледаме скалата от оценките... Тя е 2,3,4,5,6... Т.е. по някаквъ начин ние трябва да изравним двете скали:

Вариант 1: Корекция на отговорите. Създаваме ШЕСТ отговора. Този вариант е по-скоро за илюстрация и по-добре не го помнете:)

=CHOOSE(А1;"";"Слаб";"Среден";"Добър";"Мн.добър";"Отличен") 

 В този случай първия параметър си запазва стойността. Само сме вмъкнали един "фалшив" отговор в първа позиция (обърнете внимание на двете кавички)! Така при оценка две ще се върне втория отговор при три третия и т.н. Този начин е подходящ при  нужда от малко фалшиви отговори.

Вариант 2: Корекция на скалата с оценките . Тук НАМАЛЯВАМЕ оценката да се преобразуват в числови стойности от ЕДНО до ПЕТ! И задаваме ПЕТ отговора.

=CHOOSE(А1-1;"Слаб";"Среден";"Добър";"Мн.добър";"Отличен") (това е примерът от писмото)

Ако оценката е 2 ще стане едно и ще се върне първия отговор ("Слаб"). При три ще стане две и ще се върне втория отговор и т.н... 

Да продължим със следния казус. Един учител по мое време имаше малко по-различна скала за оценяване. Тя беше следната "Тъп 0", "Лош 1", "Слаб 2", "Среден 3", "Добър 4", "Мн. добър 5", "Отличен 6":):)

Тук скалата на оценките (стойността според която ще връщаме резултат) започва от 0!! Тук помага сама втория вариант на решение от предходния пример:

=CHOOSE(А1+1;"Тъп";"Лош";"Слаб";"Среден";"Добър";"Мн.добър";"Отличен")

Тук изравняването се извършва чрез ДОБАВЯНЕ на единица към оценката за да се преобразува в поредица запозвачваща от едно. При оценка 0 ще върне ПЪРВИЯ отговор ("Тъп") и т.н.

Мисля че сте разбрали, че трябва стойностите на израза да се преобразува в поредица 1,2,3,4....

Да видим сега малко "забавления" за напреднали:) Малко решения на същия пример (с "нормални оценки") решена чрез други функции. Идеята е да имате богат арсенал от оръжия за да се по-гъвкави в различни ситуации.

Вариант 1: Vlookup с "вграден" масив.

=VLOOKUP(A2;{2\"Слаб";3\"Среден";4\"Добър";5\"Мн. Добър";6\"Отличен"};2;FALSE)

(вижте #041!). Разбира се може да го направите и по "бабешкия" начин, като направите отделна табличка с оценките и "словом" на отделен лист. И да търсите с Vlookup в нея.

Вариант 2: Комбинация Index/Match.

=INDEX({"Слаб";"Среден";"Добър";"Мн. Добър";"Отличен"};MATCH(A1;{2\3\4\5\6};0))

 (вижте темите за Index и  Match)

Вариант 3: CSE вариант на предходната формула с автоматично генериран масив

от числа. (въвежда се с Ctrl+Shift+Enter! без {} около формулата!)

{=INDEX({"Слаб";"Среден";"Добър";"Мн.Добър";"Отличен"};MATCH(A2;ROW(INDIRECT("2:6"));0))}

 Вариант 4: Любимия на всеки даскал физкултурник преквалифицирал се като информатик:)

(хич не съм му фен на този вариант, но всеки ученик (и студент) го въртят на шиш докато не го научи да го прави по този начин):):)

=IF(A1=2;"Слаб";IF(A1=3;"Среден";IF(A1=4;"Добър";IF(A1=5;"Мн. Добър";"Отличен")))) 

Та опитайте се да ги разберете тези варианти (мога и още да ви дам):) Всеки си има плюсчета и минусчета:) И така никога няма да  получите "Тъп 0":)

Успех:)

#41 Разделители и масиви

В тема #40 на края посочих два "странни" примера. Ето малко разяснения. Всъщност трябва да се извиня, че последно време измених използваните разделители. Това е поради новата ми инсталация на Windows и това, че писах примери за едно помагало. В него трябваше нещата да са с локални настройки на Български. Т.е. десетичен знак ",", а не точка. Това автоматично означава, че параметрите се разделят с ";".

Тук трябва да задълбаем малко в проблемите. За обикновения потребител не представлява проблем да запомни двата разделителя. Но нали все пак тук е блог за трикове!:) В някой от примерите използвам константни масиви (стойности оградени с { }). Ако поразровите темите и прочетете всичко в една от темите аз споменах, че има драма и обещах да разчовъркам. 

При масивите с константи ние имаме два вида разделителя. Разделител на елементите в един ред (така наречения разделител на колони) и разделител на редове. При разделител за десетичен знак ТОЧКА тези разделители са:

разделител за колони -  запетая (",")

разделител за редове - точка и запетая (";")

Това означава че записа {1,2,3;4,5,6} се представя като:

 {1,2,3} като:

{1;2;3} като:

При десетичен разделител ЗАПЕТАЯ разделителите са:

разделител за колони -  обратна наклонена черта -  ("\")

разделител за редове - точка и запетая (";") (същото както при другия вид разделител!)

Горните примери изглеждат така:

• {1\2\3;4\5\6}
• {1\2\3}
• {1;2;3}

Ето табличката за да я имате в компактен вид:

Разделители

Няколко думи за "странните" функции от #40:


=VLOOKUP(A2;{1\1;2\1;3\1;4\2;5\2;6\2;7\3;8\3;9\3;10\4;11\4;12\4};2;FALSE)

Тук търсенето чрез Vlookup не е в зона от клетки (много хора мислят, че това е единствената възможност),  а в константен масив. Ако се разбрали това което писах преди малко този масив изглежда така в "разгърнат" вид:

След което търсим точно и връщаме съдържането на втората колонка. Просто и ясно:)) В другия пример нещата са аналогични, с тази разлика, че Vlookup търси приблизително!

=VLOOKUP(A2;{1\1;4\2;7\3;10\4};2;TRUE)
 
Внимавайте с типовете масиви и разделителите:)

П.П. Длъжен съм да направя уточнението, че това се отнася за таблиците на Excel!  Когато пишете на езика VBA винаги десетичния разделител е ТОЧКА, разделителя на аргументите е ЗАПЕТАЯ!

#40 Месец в тримесечие или логика срещу математика

Който не е обяснявал Excel той не се е сблъсквал с проблема да се обяснят логическите функции. Много студенти смятат, че научвайки функцията IF са големи гении. По някога има и различно решение, но друг е въпросът дали е по-лесно за обяснение.

Задача: Да се преобразува месец в тримесечие.

Месец в тримесечие

Решение 1: Може би най-завъртяния начин е само с IF :)
В клетката B2: =IF(A2=1;1;IF(A2=2;1;IF(A2=3;1;IF(A2=4;2;IF(A2=5;2;IF(A2=6;2;IF(A2=7;3;IF(A2=8;3;IF(A2=9;3;4)))))))))

Тази формула не се нуждае от коментар:) Показва няколко неща. Че студента знае IF и че има здрави нерви да напише такава дълга формула;)

Решение 2: Малко "по-културна" логическа формула:)
=IF(OR(A2=1;A2=2;A2=3);1;IF(OR(A2=4;A2=5;A2=6);2;IF(OR(A2=7;A2=8;A2=9);3;4)))
Малко по-къса и по разбираема:) И студента знае OR:)

 Решение 3: Е все пак освен проверка за равенство може да се използват и други знаци:)
 =IF(A2<4;1;IF(A2<7;2;IF(A2<10;3;4)))



 Решение 4: До тук с логиката:) Сега да седнем и да помислим каква е връзката между номера на тримесечието и номера на месеца. За да илюстрирам това ще направя една помощна табличка...

Месец разделен на 3

Както се вижда в тази таблица съм разделил номера на месеца три (все пак става дума за ТРИмесечие):):) В различен цвят съм дал различните тримесечия. Би трябвало да ви "светне", че по някакъв начин трябва да закръгляме. В по-предна тема дискутирах закръглянето и ви препоръчах да се запознаете с всички възможности в Excel. Ако бяхте послушали съвета ми вече щяхте да сте разбрали че ни трябва закръгляне към СЛЕДВАЩОТО цяло число (когато числото е дробно!) . Това е функцията RoundUP!

Ето решението за примера:  =ROUNDUP(A2/3;0)

Чиста математика:) Без логика:) Тук е момента да се запитаме кое е по-лесно за обяснение (не по-кратко)?! Честно казано не знам!! Забелязал съм че на хората е еднакво трудна (лесна) и математиката и логиката!

:)

П.П Като бонус ще покажа други решения извън дискусията за логиката и математиката:) Може нещо да си харесате:) Ако има нещо неясно, значи не сте чели внимателно предишните теми:)

Решение 5: =CHOOSE(A2;1;1;1;2;2;2;3;3;3;4;4;4)

Решение 6: ={INDEX({1;1;1;2;2;2;3;3;3;4;4;4};MATCH(A2;ROW(INDIRECT("1:12"));0))}

CSE Функция! Въвежда се без {} но с помощта на Ctrl+Shift+Enter!! Ако искаме да не е CSE "пакетираме" Match със Sumproduct!

=INDEX({1;1;1;2;2;2;3;3;3;4;4;4}; SUMPRODUCT(MATCH(A2;ROW(INDIRECT("1:12"));0)))

Решение 7: =INDEX({1;2;3;4};MATCH(A2;{1;4;7;10};1))
Тук използваме друг вид търсене в Match (обърнете внимание на втория параметър в Match!). Красота:)

Решение 8: Горните две решения за любителите на Vlookup:):)


=VLOOKUP(A2;{1\1;2\1;3\1;4\2;5\2;6\2;7\3;8\3;9\3;10\4;11\4;12\4};2;FALSE)

=VLOOKUP(A2;{1\1;4\2;7\3;10\4};2;TRUE) 

Тук имам нещо за казване, но ще го напиша в отделна тема:) Защото тази формула на първо четене може да ви доведе до главоболие:):):) Обърнете внимание, че втората формула има друг начин за търсене!

#39 Функците Small и Large без дублирани стойности

Описанието на функцията Small е дадено в помощната информация на Excel (може да използвате този адрес http://office.microsoft.com/bg-bg/excel-help/HP010062539.aspx).
За съжаление в информацията не е посочен един малък подводен камък, който  може да ви доведе до главоболие в определени случаи. Става дума за това, че функцията брои стойностите не отчитайки дублиращите стойности за всяка позиция. Ето пример:

Данни за Small

Ако въведем формулата: =SMALL(A1:A10;1) ще видим стойността на най-малката стойност (в случая 1).  Ако въведем формулата =SMALL(A1:A10;2), с тайната надежда да видим ВТОРАТА по големина стойност, ни очаква разочарование. Функцията ще върне пак 1!! Това произтича от факта, че в списъка има две единици. Чак когато зададем =SMALL(A1:A10;3) ще получим това което искаме :(

Този "проблем" се решава със следната помощна табличка:

Намиране на N-тата по големина стойност

Стъпка 1: В колонка C се намират числа от 1 до N.Може с AutoFill или формулата =Row(A1) или =Row()-1 и т.н. ( всеки си има стил и различен подход, и както и да ги въведете (дори и на ръка) никой няма да ви разстреля):):):)

Стъпка 2: В клетката D2 въведете  =SMALL($A$1:$A$10;1). Намира първата най-малка стойност. Тук няма интрига;)  

Стъпка 3: В клетка D3 въведете =SMALL($A$1:$A$10;COUNTIF($A$1:$A$10;"<="&D2)+1)

Размножете формулата като я "дръпнете" надолу. И готовото:) Ако искате да не дава грешка #Num просто я "пакетирайте" с IFError за което си има отделна тема;) 

Няколко думи за "магията". За да се намери N-тото по големина число, трябва да е намери колко е броя на N-1 по големина числа преди него! Примерно за да се намери второто по големина число трябва да се намери колко са преди него (в случая колко единици имаме)! След което към намерената бройка да се добави единица и да се намери чрез Small съответната стойност (в нашия случай 3-тото по големина число). За намирането на третото по големина числа броим колко са преди него като бройка (в нашия случай колко са числата по-малки от 2) В нашия случай намираме броя  на единиците и двойките!. Понеже имаме само една двойка сумата е 3! Т.е. със Small трябва да намерим четвъртата(!) по големина стойност.... И т.н. (опитайте се да проиграете и за останалите числа).

За по-лесното използване на тази таблица, я именуваме (примерно SmallBD).След което може да използваме "прост" Vlookup:):)

Именуване на таблицата

Пример: Да се намери петата по големина стойност: =VLOOKUP(5;SmallBD;2;FALSE). Сменяте само червената цифричка и сте готови:):)

Забележка: По някога може създаването на междинна таблица да не е възможно (въпреки че в случая го препоръчвам горещо) и се налага да се сглоби цялата формула в една клетка. В този случай формулата става доста дълга (и доста объркваща за начинаещи)! Аз ще дам само как се намира втората по големина стойност:
=SMALL($A$1:$A$10;COUNTIF($A$1:$A$10;"<=" & SMALL($A$1:$A$10;1))+1).
За по-големи стойности се въоръжете с търпение и здрави нерви и "сглобявайте":):) Но както споменах това го препоръчвам за майсторите които са разбрали предишните обяснения!

Успех:)

П.П. Всичко казано за функцията Small важи и за функцията Large!

#38 Работа с ЕГН

Реших малко да си поиграя и направих една таблица за работа с ЕГН-та... В нея има доста хитрости и различни техники. Няма да описвам детайли. Тези които искат да пишат на посочения е-mail. Успешно човъркане и учене:):)

Връзка към файла (google.docs)

#034 Таблици за търсене

Този пример не бих нарекъл много триков, но от друга страна се случва доста често да се правят подобни неща. Идеята е да се реализира таблица в която търсим в първата колонка и връщаме данни от втората.

Таблица оригинал:

до 80%	2
от 81 до 99%	4
от 100 % до 119 %	6
120% и повече	8

Вариант 1: Първо ще реализираме цялата таблица с помощта само на IF. =IF(A1<=80%,2,IF(A1<100%,4,IF(A1<120%,6,8)))    * ако данните са в А1

 Този вариант е добър в случаите когато числата не се променят често, но при промяна потребителя трябва да се занимава с редактиране на формули и т.н.

Вариант 2: Таблица за търсене

Таблица за търсене

Въвеждаме данните в произволни клетки (може и на друг лист). Избираме клетките и им задаваме име (в случая  TablicaP).  NB! Не забравяйте да натискате Enter след като именувате областта!.  Обърнете внимание как е изградена таблицата спрямо основните данни! Така изградена таблицата позволява търсенето на всякакви стойности по-големи или равни на нула. 

Формулата която търси в тази таблица е :  =VLOOKUP(A1,tablicaP,2,TRUE) . Приблизително търсене чрез VLookup връщайки стойността от втора колонка (за повече  вижте помощната информация в Excel). Както казах тази формула ще работи само с неотрицателни числа! При отрицателни стойности или текст ще се издъни. Най лесно е да се "пакетира" в IFError (виж темата за тази функция). =IFERROR(VLOOKUP(A1,tablicaP,2,TRUE),0)

Вариант 3: Понеже имах цяла тема как да не се използва Vlookup ето и варианта без нея. Но в случая става доста оплетено и за предпочитане е Vlookup. Все пак ето формулата и нейната "дисекция":

=IFERROR(INDEX(tablicaP,MATCH(A1,INDEX(tablicaP,0,1),1),2),0)

INDEX(tablicaP,0,1) - Това връща цялата първа колонка от таблицата за търсене

MATCH(A1,INDEX(tablicaP,0,1),1) - Приблизително търсене на стойността от А1 в първата колонка на таблицата за търсене. Резултатът представлява номерът на намерения ред. (NB! Единицата означава приблизително търсене в подредени във възходящ ред стойности!)

INDEX(tablicaP,MATCH(A1,INDEX(tablicaP,0,1),1),2) - Връща от таблицата за търсене стойностна от намерения ред и втората колонка.

Ако сте разбрали и трите варианта нямате проблеми с търсенето (става дума за търсене в Excel а не търсене на сериозно гадже):):)

#023 Търсене (Първа част?)

Тази тема няма за цел в детайли да ви запознае с функциите за търсене. Искам да обърна внимание на разликите (и приликите) между функциите и да обърнете внимание на техните особености. Няма да се спирам в детайли на техните параметри. За целта си има клавиш 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 и други трикове за нормализация е, че формулите за работа с масиви се редактират по-трудно (има по-стара тема по въпросът). Не съм установил чак толкова огромна разлика във времената.

Толкова по темата за търсене за сега:) Ако се сетя още нещо или имате някакъв конкретен практически пример и въпроси може да има втора серия:)

#003 Функция iferror и съвместимост с по-стари версии

Една от новите функции в Excel 2007 е функцията IfError... Синтаксисът й е IFERROR(Израз,стойност)... Функцията връща стойността на израза, ако е верен, или посочената като втори параметър стойност, ако изразът е грешен... Например =IFError(A1/B1,"") е често срещано приложение, ако има вероятност в B1 да няма стойност и да възникне грешка за деление на нула. В случая при деление на нула клеткaта ще остане празна.... Ето още един пример =iferror(vlookup(a1,b1:c100,2,0),"Липсва търсената стойност"). В случая се обработва грешката #N/A (липсваща стойност) която би върнала функцията VLookup.

Както беше споменато тази функция се появи в Excel 2007. Това може да предизвика проблеми ако се прави приложение за работа с по-стари версии. Ето как се заменя тази функция: IF(ISERROR(израз),стойност,израз). Първият пример се трансформира в IF(ISERROR(A1/B1),"",A1/B1).

В примерите ще използвам IFError, но по посочения начин Вие може да си конвертирате изразите за по-голяма съвместимост.