#051 Сортировка с формули - втора част

NB! Преди да продължите, моля прочете първата част от темата #050 Сортировка с формули - първа част

II. Сортиране на данните

В тази част ще извършим сортирането на данните... Ето изходните данни върху които ще илюстрираме.

1. Създаване на помощни колони

Колонките C и D са помощни колонки (ПК звучи почти като "пожарен кран":) Разбира се може да ги сложите където си искате в работния лист и да ги скриете (и сложите по-умни имена)! Използвани са знанията от първата част на темата!

Формулата в C2 е:
=COUNTIF($A$2:$A$8;"<="&A2)-COUNTIF(A2:$A$8;"="&A2)+1
Формулата в D2 е:
=COUNTIF($B$2:$B$8;">="&B2)-COUNTIF(B2:$B$8;"="&B2)+1

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

В резултат на тези формули в двете помощни колонки получаваме ранговете ("нечестни") за имената и заплатите.

2. Създаване на имена

За по-лесното изграждане на формулите съм създавал имена на области (така и не направих тема за именуването:() :

=$A$2:$A$8 - Имена
=$B$2:$B$8 - Заплати
=$C$2:$C$8 - ПК1
=$D$2:$D$8 - ПК2

NB! В общи линии не умна идея да пишете имената кирилица! За да стане още по-гъвкава таблицата може да използвате динамични имена (има тема!) които да се разширяват при добавяне на данни!

3. Сортировка

След тези подготовки самата сортировка е фасулска работа, ако си спомняте темата за Index/Match;))!

Формулата в А2 е:
=INDEX(Имена;MATCH(ROW(A1);ПК1;0))
Формулата в B2 е:
=INDEX(Заплати;MATCH(ROW(A1);ПК1;0))

NB! Обърнете внимание, че и в двете формули използваме помощна колонка ЕДНО!! Сортировката е според ПЪРВАТА колонка (в случая имената) и за това заплатите трябва да съответстват на имената!

Идеята е просто да търсим 1,2,3,.... в помощната колонка и да извличаме съответното име/заплата. "Броенето" се реализира чрез Row(..). Ако имате колонка в която да има 1,2,3 (примерно номерация) можете да използвате нейното съдържание.

Ако искаме да сортираме по заплати....

Формулата в А2 е:
=INDEX(Имена;MATCH(ROW(A1);ПК2;0))
Формулата в B2 е:
=INDEX(Заплати;MATCH(ROW(A1);ПК2;0))

NB! И на двете места използваме ВТОРА помощна колонка!

Ми това е:) Успех:):)

P.S. И едно предизвикателство :):) Да се тествате сами себе си:) Опитайте се сами да направите сортировка по два ключа (две колонки). Например в сегашния вариант ако имаме две еднакви имена по-напред се слага името което е било по-нагоре в изходните данни. Помислете как да се подрежда по-нагоре името с по-голяма заплата! В примера имаме две еднакви имена Мария Петрова и в нашия случа се подреждат "грешно"!

#047 Търсене (трета част) Търсене в съдържанието на клетки

Продължаваме борбата с търсенето:) Отново да напомня, че въпреки "обратния" ред на подреждане на темите, е желателно да четете темите в правилния им ред (т.е. #001, #002 ...)... Често дадена тема използва по-стари "трикове".... За това не четете "новините" първо:):)

Ето задачата.... Дадени са кодове разположени в една клетка... Да се направи Vlookup подобна функция, но да търси във вътрешността на клетките....

Фигура 1: Изходни данни

Трябва да се търси в колонката "Кодове", като всеки код трябва да се третира като самостоятелна стойност!

Предварителна информация:

За търсене на текст във друг текст се използват функциите Find и Search. Разликата е в това че Find прави разлика между малки и главни букви. За повече информация ето синтаксиса им:Функция Find Функция Search.
Проблемът е, че са малко неудобни за ползване. Ако текстът го има връща число показващо къде се намира, ако обаче го няма връща грешка от тип #VALUE! а не НУЛА (както правят подобните функции в "културните" езици за програмиране)! T.e ако имате желание да правите трикове от типа Find(.....)>0 с тайната надежда да получите масив съдържащ True, False сте в грешка... Масивът ще съдържа True,#Value стойности.... По-конкретните примери ще видите как елиминирам този "бъг":):) В примера по-долу ще използвам Find по две причини: защото търся число и няма значение регистъра и защото съм мързелив и Find е по-кратка:):) Ако обаче искате търсене на съвпадение в текст без отчитане на регистъра, ще се наложи да използвате Search!

Подготовка на изходните данни:

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

        Проблем1: Нека да потърсим кодът "23" в изходните данни.... Ще видите че точно този текст го има както в първите пет клетки или като част на кодовете ("123"  ,  "12345" и "123456") или като самостоятелен код! Т.е. ще е груба грешка да търсим чрез =Find("23";A2)! Мисля, че се досетихте,че по-хитро ще е да търсим =Find(",23,";A2). Да търсим стойността заградена със запетайки!

        Проблем2: Когато си мислите, че всичко е цветя и рози може да се усетите, че първия и последния код в списъка не са заградени и от двете страни със запетая! Правилното търсене е =Find(",23,";"," & A2 & ",")!!! Т.е. на нас ни трябва подобрена версия на изходния списък в който нещата да са в по-стандартен вид, при който всеки код да изглежда по един и същи начин! За целта съм създал помощна колонка.

Помощна колонка

В клетката D2 е записана формулата: ="," & SUBSTITUTE(A2;" ";"") & ","

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

Именуване на области:

За по-компактни формули съм именувал областите с данни както следва:

D2:D7 - kodove (НЕ колонка А!!)

B2:B7 - gradove

C2:C7 - sumi

Резултатна таблица

Пример1 : Да се изведе името на града с даден код. Ако има дублажи да се изведе името на първия град с посочения код!

В клетка B2 формулата е: 

{=IFERROR(INDEX(gradove;MATCH(TRUE;FIND("," & A2 & ",";kodove)<>0;0));"---")}

Бележки по формулата:

• Формулата е CSE (въвежда се със Ctrl+Shift+Enter без {})!
• Чрез FIND("," & A2 & ",";kodove)<>0 търсим стойността от A2 (като и нея заградим в ","!!!) в кодове и чрез <>0 получаваме масив съдържащ True, #Value .... Tук не е проблем, че едната стойност е грешка. В случая на нас ни трябва True стойностите (първата True стойност)!
• Чрез MATCH(TRUE;FIND(...)<>0;0) търсим първото появяване на TRUE в този масив... Така  получаваме реда който ни трябва..
• INDEX(gradove;MATCH(...)) ни дава името на града.
• "Пакетираме" с IFError да не се появяват досадните #N/A грешки (на тяхно място се появяват досадните "---":):) =IFERROR(INDEX(...);"---") 

Пример 2 : Да се изведе сумата на града с даден код. Ако има дублажи да се изведе сумата на първия град с посочения код!

 В клетка C2 формулата е:
{=IFERROR(INDEX(sumi;MATCH(TRUE;FIND("," & A2 & ",";kodove)<>0;0));"---")}

Тук няма интрига:) Формулата е аналогична на предходната. Само извличаме данните от колонка Сума.

  

Пример 3 : Да се изведе сумата на града с даден код. Ако има дублажи да се изведе сумата на ВСИЧКИ  градове с посочения код!

В клетка D2 формулата е:

{=SUMPRODUCT(--IFERROR(FIND("," & A2 & ",";kodove)<>0;False);sumi)}

Тук идеята е да "умножим" два масива . Единия масив съдържа  едно или нула  в зависимост дали кодът го има или не, а втория масив съдържа сумите. 

Бележки по формулата:

• Формулата е CSE (въвежда се със Ctrl+Shift+Enter без {})!
• Чрез FIND("," & A2 & ",";kodove) търсим стойността от A2 (като и нея заградим в ","!!!) в кодовете. Резултата ще е число или ГРЕШКА (вижте предварителните бележки!).
• Чрез израза IFERROR(FIND()<>0;False) се прави"трикът" да се получи True/False масив. Ако Find върне стойност изразът Find()<>0 ще върне True, ако е грешка "пакетиращата" функция ще върне False! 
• --IFERROR() "обръща" True/False масива в единици и нули
• SUMPRODUCT(--IFERROR();sumi) Умножава двата масива (т.е. там където има код се взема сумата (т.е се умножава по единица), там където няма код сумата се умножава по нула). След което се  намира сумата на произведенията.

Успех...... :) :)

 

#046 Търсене (втора част) или кога сумирането е търсене и кога не е :)

За по-лесно разбиране на нещата тук ви съветвам на прочетете първо Тема 23 и Тема 24 и цитираните в тях теми!


Бях помолен да се се "боря" със следния казус... Търсене по два критерия...

Проблем 1. При зададена таблица са данните да се върне резултат според две условия за търсене. Данните във входната таблица нямат дублажи!

Таблица в която търсим

Таблица с резултатна колонка

За по-лесно разчитане на таблицата съм именувал зоните в началната таблица (жълтите клетки) както следва:
A2:A8 - "к1"
B2:B8 - "к2"
C2:C8 -  "р"

Вариант 1:  Ще използваме SumifS ! Условна Сума?!? Даже когато данните нямат дублажи?! Понякога ни е трудно да осъзнаем, че когато елемента е един, сумата е равна на този елемент! Т.е. извеждайки условната  сума според даден критерий (или критерии) ние връщаме стойността на този елемент! :) Т.е. имаме "магия" как сумата се явява търсеното число.

В клетка C2 (на резултатната таблица) формулата е:
 =SUMIFS(р;к1;A2;к2;B2)

Вариант 2: Без да повтарям по-горните разсъждения вместо SumifS ще използвам Sumproduct (Sumifs го няма в Excel преди 2007!). За повече информация вижте темите за SumProduct.

В клетка C2 (на резултатната таблица) формулата е: 
=SUMPRODUCT(--(A2=к1);--(B2=к2);р)

Двата варианта имат един "дефект" (може би да е "ефект"?!)!  Ако все пак в началния списък има дублажи, горните две формули ще върнат СУМАТА на всички резултати, а не само първия срещнат елемент!! Ние обаче може да не искаме тази функционалност! Т.е. функциите за сума козината си менят, но нрава не!:):)

Да продължим с разсъжденията.... Ами ако резултатът не е число?!? Тук "магията" търсенето да се трансформира в условно сумиране изобщо не минава дори и да няма дублажи! Ето как се решават тези два казуса.

Проблем 2. При зададена таблица са данните да се върне резултат според две условия за търсене. Данните в резултатната колона може да са текст!

Таблица в която търсим

Таблица с резултатна колонка

За по-лесно разчитане на таблицата съм именувал зоните в началната таблица (жълтите клетки) както следва:

A2:A8 - "кк1"
B2:B8 - "кк2"
C2:C8 -  "рр"

Вариант 1: В клетка C2 (на резултатната таблица) формулата е:

 {=INDEX(рр;MATCH(A2&B2;кк1&кк2;0))} Формулата е CSE! (Въвежда се чрез Ctrl+Shift+Enter без {}!) 

Използваме оператора & за "слепване" (по научно "конкатенация") на два символни низа. Така правим едно общо условие чрез което търсим. По същия начин процедираме и с двете зони в които се намират критериите. За останалото се обърнете към темите които препоръчах в началото и помощната информация за функциите Match и Index (в блога има също теми за тях)!

Вариант 2: Понеже мразя CSE функции ще "пакетирам" Match да проработи със сложни масиви.

=INDEX(рр;SUMPRODUCT(MATCH(A2&B2;кк1&кк2;0)))

Вариант3: Ако искаме да не дава #N/A грешка ако няма съвпадение още едно "пакетиране":)

=IFERROR(INDEX(рр;SUMPRODUCT(MATCH(A2&B2;кк1&кк2;0)));"---")

ще показва "---" там където няма съвпадение (Можете да сложите какъвто искате текст, който да се появява при грешка!)

Когато след много мъки стигнете до тази формула и доволни, че сте я разбрали, не бързайте да  си тръгвате! :) Сега е момента да ви кажа, че тя е доста рискова! В някой ситуации може да се насадите на пачи яйца:)
В голяма беда сте, ако имате следните две ситуации (или подобни на тях):
Критерий1: Склад1 Критерий2: 12
Критерий1: Склад11 Критерий2: 2

В резултат на използването на оператора & ще получите едно и също нещо! Склад112 !

Т.е. ако търсите Склад1, 12 може да се "натресете" на резултата за Склад11,2!!! И ако не си проверявате нещата да се получи ГОЛЯМ проблем.
Внимавайте когато използвате & и преценявайте опасността от този начин на търсене в зависимост от конкретната ситуация!

Вариант4: "Разделяне" на двете проверки.

{=INDEX(рр;MATCH(1;(A2=кк1)*(B2=кк2);0))} (CSE функция!)

Тук хитростта е, че в резултат на операцията сравнение се създават два масива: масив с нули и единици в зависимост къде A2 се намира в първата зона и масив от нули единици в зависимост от това къде B2 се открива във втората зона. Ако в резултат на проверките са се получили примерени масиви {1,0,1,0,0} и {0,1,1,0,0},  то при  тяхното умножение се получава единица там където имаме единици и в двата изходни масива (т.е. там където И двете условия са верни!) В по-горния пример резултатът ще е масива {0,0,1,0,0}. Там чрез Match (0 означава точно търсене)  търсим индекса на първата ЕДИНИЦА (В примера ще върне 3)! (NB! Ако имаме дублажи, може и да имаме повече от една единица в резултатния масив, но Match ще върне индексът на първата намерена!). Чрез функцията Index и полученото число (резултатът от Match  всъщност е номера на първия ред където И двете условия са верни!) се извлича съответния резултат.

Вариант 5: Двойно пакетирана (за избягване на CSE и съобщения за грешки) функция!

=IFERROR(INDEX(рр;SUMPRODUCT(MATCH(1;(A2=кк1)*(B2=кк2);0)));"---")

Красиво:) И работещо както за резултат текст така и за резултат число!  Мисля, че за вас няма да е проблем да напишете такава функция за три или повече условия:)

Успех:)
П.П Както казах примерите могат да работят и без имен, а чрез  директно посочване на адресите на съответните зони!

#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":)

Успех:)

#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) 

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

#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 а не търсене на сериозно гадже):):)

#032 Функция SubTotal и защо е по-добра от Sum

Да погледнем #030 в реда за обобщение. Там намираме не Sum и Average ами функцията SubTotal. Да се замислим защо Excel използва нея а не "класическите" функции. SubTotal не се преподава, но е доста по-мощна и гъвкава от Sum и т.н. Та в общи линии първия параметър е число показващо каква е използваната функция а втория е самата зона. Ето стойностите на първия параметър:

Функция_ном (включва скрити стойности)	Функция_ном (игнорира скрити стойности)	Функция
1	101	AVERAGE
2	102	COUNT
3	103	COUNTA
4	104	MAX
5	105	MIN
6	106	PRODUCT
7	107	STDEV
8	108	STDEVP
9	109	SUM
10	110	VAR
11	111	VARP

Вижда се, че имаме две възможности за всяка функция. СЪС и БЕЗ да се включват скритите стойности! Тук се крие силата на тази функция!  По време на правенето или използването  на една таблица "скриваме" редове или колонки както с помощта да Hide или с помощта на филтрите. Сега е моментът да се замислите че SUM смята ВСИЧКО което му е подадено като параметър без да го е грижа какво се вижда и какво не! Т.е. "това което виждате може да НЕ е това което се сумира"!:) За разлика от SUM/Average и т.н. SubTotal ви дава право на избор!

=SubTotal(9,A1:A100) е пълен аналог на =SUM(A1:A100) сумирайки независимо дали са видими, докато =SubTotal(109,A1:A100) ще зависи кои клетки от зоната са видими! Този "малък" на пръв поглед нюанс може да ви създаде главоболия:) Забелязал съм, че рядко се набляга на този "дефект" на класическите функции и сякаш никога няма да мине през акъла на някой да скрива или филтрира:):)

Както казах обаче при таблиците в реда за обобщаване Excel "мъдро" слага правилните функции (т.е. SubTotal с отчитане на скритите редове). Ако не искате това ще се наложи да подмените предложените от Excel функции с "вашите" любими такива:) Чара на SubTotal е че може да работи и като класическа функция, за това просто сменяте първия параметър и сте ОК:)

NB! Да бъдем коректни не винаги е възможно използването на SubTotal! Тя не възприема така наречените 3D зони (зони които са между няколко работни листа). Например =SUM(Sheet1:Sheet4!A1) няма как да я подмените с SubTotal! За това не бързайте да погребвате Sum, Average и т.н. :):)

Като бонус ето как се прави табличка за демонстрация възможностите на SubTotal:)

Табличка за SubTotal

Условие: Когато потребителя пипа C1 и C2 да се вижда правилния резултат.

0. Подготовка. Колонките D, E и F са помощни и спокойно може да са на друг лист да не загрозяват пейзажа:) Или просто ги скрийте когато приключите с настройките и видите, че всичко е ОК

1. Дефиниране на имена. Понеже все забравям да направя тема за именуване сега ще пиша много:( Трябват ни три имена "Функции", "Всички", "Видими" за данните в помощните колонки. Най-бързия начин е да изберете клетките от D1 до F12 (данните с имената!) и да изпълните командата Formulas/Define Names/From Selection и да посочите че използвате и заглавния ред и първата колона. Така ще получим освен трите имена на колонките и имената "Average" за първите две стойности, "Count" за вторите и т.н.

Създаване на имена

Резултат от именуването (проверка чрез Formulas/NameManager)

Тук има един малък проблем. Името "Функции" сочи към данните а ние искаме да сочи към имената на функциите. За това трябва да оправим този проблем като редактираме името от Name Manager.

Редактиране на името "Функции"

2. Контрол на C1 и C2. За да може потребителя да избира стойности за контролиране на C1 използваме списъка "Функции",  а за C2 зоната E1:F1. (Вижте #002).

Контрол на C2

3. Формула в C3. Ще дам два варианта да е по-весело:) И да имате теми за размисъл и четене:)

Вариант 1: =SUBTOTAL(INDEX(INDIRECT(C2),MATCH(C1,Функции,0)),A1:A24)

Тук извличаме номера на функцията чрез Index:

INDIRECT(C2) - "Обръща" съдържанието на клетка C2 в зона. (Всички/Видими). Така получаваме зоната от която ще извличаме числото.

MATCH(C1,Функции,0) -  Тук намираме редът от който извличаме търсейки съдържанието на C1 в зоната Функции (за това и се наложи корекция!)

Вариант 2: =SUBTOTAL(INDIRECT(C1) INDIRECT(C2),A1:A28) 

 

Извличането на стойността е в израза:

INDIRECT(C1) INDIRECT(C2) - Това е странен израз. Две зони разделени с интервал. Тук хитростта е че се намира "сечението" на двете зони. Зоната C1 (това е хоризонталната зона на съответната функция) определя реда, Зоната C2 пък представяла колоната. Например ако сме избрали Max и Видими  се търси сечението между E5:F5 (зона Max) и F2:F12 (зона Видими). Сечението (общото между двете зони) е клетка F5. Която и съдържа това което ни трябва!

Явно и тема за зоните трябва да понапиша:)

Успех със SubTotal:)

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

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

#022 Динамични колонки

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

Динамична таблица

 При промяна съдържанието на клетките B1 или C1 автоматично се променя заглавието на колонка G, данните в нея и данните изобразявани в диаграмата.

1. Помощни колонки. Цялата магия се състои в добавянето на две допълнителни помощни колонки. Тези колонки може да бъдат на произволно място и спокойно може да ги скрием да не се виждат от потребителя. Тези колонки ще използваме за колонка G и за диаграмата. В примера ще използвам като помощни клетки клетките от H3 до H13 за първата година и клетките от I3 до I13 за втората година.

Помощни колони

• В клетката H3 въвеждаме "=B1" в клетката I3 "=C1". Така си осигуряваме динамичност на заглавията на двете колонки. 

• В клетката H4 въвеждаме :

=INDEX($B$4:$F$13,ROW(A1),MATCH($B$1,$B$3:$F$3,0))

• В клетката I4 въвеждаме:

=INDEX($B$4:$F$13,ROW(A1),MATCH($C$1,$B$3:$F$3,0))

Тук отново се използва "трикът" с променлив брояч вътре във формулата (Row(A1)).Той осигурява извличането на първи, втори, трети и т.н. ред от областта. Match частта осигурява номера на колоната в зависимост от стойността на B1 (C1). Дано вече сте усвоили тези два трика:)

Размножете формулите надолу и имаме две колонки които си променят съдържанието при промяна на B1 и C1!

2. Оформяне на таблицата. Тук трябва да отбележа само няколко неща.

• По потребителски ориентирано е да контролирате въвеждането на данни в B1 и C1. За целта изберете двете клетки. Изпълнете командата Data/Data Validation. И изберете контролиране от списък избирайки зоната B3:F3 в полето Source. Това ще позволи ако се сменят годините да не пипате нищо в областта за контрол.

Контрол на зоната за избор

• В клетката G3 въведете следната формула: 

="Сравнение между " & H3 & " и " & I3
 Така имаме динамично заглавие.

• В клетка G4 въведете формулата:  =H4/I4  . Размножете и оформете като процент.

Останалото го правите както си знаете:)

3. Оформяне на диаграмата. Тук няма нищо специално. Трябва използвате данните от колонки H и I за данни за сериите. Тук няма магия и няма да ви обяснявам как се прави диаграма  за да не ви обидя;) Просто използвайте данните от I и H!! Все пак блога е за трикове...;)

Данни за диаграмата

Е има една "мини магия" за заглавието за да стане и то динамично:) Сложете заглавие. Изберете го.  Щракнете първо върху лентата за формули! След което изберете клетката G3!

Създаване на динамично заглавие на диаграмата

4. Скривате помощните колонки и сте готови:)

#019 Контролни списъци които зависят един от друг (Adv)

Както вече споменах в тази тема ще разгледам по-сложен контрол с помощта на списъци които зависят един от друг. Ако не сте прочели #018 (и свързаните с нея теми) го направете сега. Методът използван в #018 има два недостатъка. 

Първият недостатък в използвания в #018 е във фактът, че ако в първия контролен списък има текст с интервали не е възможно да създадем именувана област съдържаща интервали. Този проблем се решава лесно. Ако в първия списък имаме стойност например "малко дете", създаваме именувана област с име "малкодете" (без интервали!). След което видоизменяме контролиращия списък (в примера #018 клетка B2) като =indirect(substitute(B1," ",""). Просто използваме вече дискутирана функция Substitute за премахване на интервалите. Елементарно;)

Вторият проблем е по-сложен и изисква малко по-сложни действия. Проблемът е, че първия списък е статичен. Потребителя не може да въвежда стойности в него. Когато направим и първия контролен списък динамичен се оказва, че няма как да използваме Indirect по простата причина, че при разработката на приложението ние не знаем какво ще реши да въведе потребителя в първия списък (и респективно да създадем съответната именувана област) :( 

Пример: Да се оценяват студенти в зависимост от различни методи за оценки.

Оценките да зависят от избрания метод (държава)

Оценките на студентите (жълтите клетки) да са съобразени избрания метод за оценяване в клетката B1. Потребителя да може да добавя и редактира начините за оценяване в отделна област.

Да предположим, че областта за видовете оценки започва от клетка F1. (Hint! По-добре е самата област да бъде на друг лист!)

За за въвеждане на видовете оценявания на начало клетка F1

В посочената зона потребителя ще въвежда в първия ред с думи типът на оценяването и вертикално разрешените стойности. Дал съм примери. За повече информация може да надзърнете на адрес http://en.wikipedia.org/wiki/Grade_%28education%29 (Типът оценяване от колонка L няма да го намерите там):):)  Както се вижда различните начини на оценяване имат различен брой разрешени стойности.

1. Създаване на динамична именувана област за видовете оценявания. Тук се използва техниката от #001. За име на областта задайте "GradeType" а за Refers to: =OFFSET(Sheet1!$F$1,0,0,1,COUNTA(Sheet1!$1:$1)-2). Областта е хоризонтална. (NB! това "магическо" оцветено в червено -2 е заради фактът че на първи ред имаме две пълни клетки в повече (А1 и В1)!! Ето за това и препоръчвам зоната да бъде някъде отделно където няма да има такива "смущения"!)

Команда Formulas/Define name

2. Създаване на именувана област за оценяване. Тази област трябва да е динамична вертикално в зависимост от броя на оценките и зависи от стойността на B1.  Задава се име на областта "Grade" и сочи към формулата :

=OFFSET(Sheet1!$F$1,1,MATCH(Sheet1!$B$1,GradeType,0)-1, COUNTA(OFFSET(Sheet1!$F$1,1,MATCH(Sheet1!$B$1,GradeType,0)-1,100,1)))

Не се плашете много:) Вече говорихме за Offset. Сигурно скоро ще има тема и за Match. В случая Match намира колонката на съответния тип оценяване в зависимост от съдържанието на B1.

MATCH(Sheet1!$B$1,GradeType,0)-1 (NB! -1 е защото Offset Брои от НУЛА!).

Часта : COUNTA(OFFSET(Sheet1!$F$1,1,MATCH(Sheet1!$B$1,GradeType,0)-1,100,1)))

намира колко пълни клетки има вертикално.   Тъй като трябва да броим в някаква област предварително задаваме област от 100 (множко е) реда и с CountA намираме реалния брой редове. 

3. Контрол на клетката с видовете оценявания. За клетката B1 се изпълнява Data Validation с контрол по списък =Gradetype след което се избират жълтите клетки и се задава DataValidation по списъка Grade.

Това е:) "Само" три стъпки:)