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

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

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

#045 Data Validation без Validation!

Ето ви едно малко  по-сложно изпълнение базирано на Data Validation.

Задача: Да се реализира "Pick from Drop-down List" функционалност.

Ха сега... Няколко уводни бележки относно тази функционалност. Тези които знаят за какво иде реч да прескачат абзаца:) Когато се въвеждат стойности и сте натиснали десен бутон на мишката може ви сте видели командата Pick from Drop-down List, която показва списък на въведените до този момент стойности в колоната!

Командата в контекстното меню

Вид на списъка за избор

Това улеснение ни дава бърз начин за избиране от вече въвежданите стойности.

Hint! Вместо да се мотате из контекстното меню същия ефект се постига с натискането на ALT+Стрелка надолу!!!

След като го има защо ни трябва да го правим отново!?  Винаги съм се смятал за мързел без капка мазохистични наклонности:)

Проблем 1: Това не действа за цифрови стойности! Просто не ги показва в списъка.
Проблем 2: Ето едно писмо което получих преди време:

"..... Има едно положение в Ексел 2003, което ме затрудни. Става дума за следния казус:

Имаме таблица, в която трябва да се заключат определени области - колони, в които да се въвежда само след парола. Дотук добре - дефинираме областите в Tools/Protecton/Allow Users to Edit Ranges и слагаме пароли. След това заключваме Sheet-а от Tools/Protection/Protect Sheet. Междувременно използвам Аuto Filter във всяка колона. Това ми позволява да използвам десен бутон и Pick From Drop-down List.

И тук идва проблемът. Докато Sheet-а не беше заключен, това меню съществуваше, в момента в който я заключих, то стана неактивно. ...."

Наистина не работеше и реших да го симулирам:)

Списък в колонка C

Колонките D и E са работни може да ги сложите по-далече (може и на друг лист)! Може даже да ги скриете:)

Стъпка 1: Формула в D2-> =IF(COUNTIF($C$2:C2;C2)=1;ROW(C2);"")

Това е лесно за разбиране. Просто там където за първи път се появява дадена стойност "маркира" реда слагайки номера на реда. Размножавате формулата надолу.

Стъпка 2: Формула в E2 ->  =IFERROR(INDEX(C:C;SMALL(D:D;ROW(A1)));" ")
"Пакетиране" на уникалните стойности. Този номер ни е познат от друг цирк:):)

Стъпка 3: Декларираме име (Formulas/Define Name)  values1 с формула за Refers To:
=OFFSET(Sheet1!$E$1;0;0;50-COUNTIF(Sheet1!$E$1:$E$50;" ");1)

Това се прави за да се извлекат само клетките с конкретна стойност. Обърнете внимание, че ако горната формула върне грешка в клетката се връща ИНТЕРВАЛ! За това не минава номера с CountA.  Произволно е решено, че уникалните стойности са по-малко от 50! Ако прецените може да увеличите тази константа.

Дефиниране на име

 

Стъпка 3: Контрол на колонката C. Избираме клетките и изпълняваме Data/Data Validation

Стъпка 3.1 Избор типа на контрол:

Контрол чрез списък

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

Стъпка 3.2: Изключваме контрола при въвеждане!!!!!!

Премахване на контрола

Най-накрая си дойдохме на думата защо съм сложил това "тъпо" заглавие на темата:):):):) Всъщност Data Validation не прави никакъв контрол! Позволява да въвеждаме стойности които ги няма в списъка! Проверка няма:) Само използваме възможността да показва списък в дадената клетка! Та както виждате (може би за първи път) Data Validation без validation!:) 

Готовооо... Всяко добавено нещо се появява в списъка.... Даже числата......:)

успех

#044 Въвеждане на уникални стойности

Един мини трик за използване на Data validation.

Проблем: Да се въвеждат в дадена зона стойности без повторение.

Предупреждение за дублиране

Решение: Избират се клетките и се изпълнява Data/Data Validation

Контрол чрез формула

Настройка на съобщението за грешка

И готово:) Лесен "трик" за все още неизтрезнелите от Нова Година:)

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

Успех:)

#042 Мързеливо въвеждане на дроби

Беше ми зададен въпрос как при въвеждане потребителя да не се налага да въвежда дробен знак. Т.е. автоматично когато въвежда цели числа те да се преобразуват в дробни. Примерно с 4 знака след десетичната точка. Примерно при въвеждане на 10 да се появи 0.0010 при въвеждане на 12345 -> 1.2345 при 123456789  -> 12345.6789 и т.н.

Вариант 1: Без "фокуси" :):) Excel си има такава настройка. Влизате в менюто File/Options (или Офис бутон/Options за Excel 2007) и там в секцията Advanced намирате това което ви трябва:

Автоматично преобразуване на цели числа в дробни

За да ви подскаже, че сте включили този режим, Excel  ви показва в статус лентата "Fixed Decimal" (т.е. ако някой иска да се бъзика с вас ако видите този надпис да знаете от къде е активиран:))))

Надпис на статус лентата

И готово:)

Плюсове: Няма код, няма допълнителна колонка.

Минуси: Проблемът е, че тази настройка е валидна за Excel а не за конкретната работна книга! Т.е. ако изпратите вашата работна книга на ваш познат при който тази настройка не е включена "магията" ще изчезне:( Друго досадно нещо е, че тези настройки важат за всички цели числа които въвеждате (независимо от работна книга, работен лист или зона от клетки).... Т.е. ако искате наистина да въведете 123 трябва да напишете 123. (точка след 123!) . При дробните числа няма проблем. Ако вие въведете 123.4 това число ще бъде въведено в клетката без промяна! Върши работа но е прекалено глобално като действие и не е обвързано с конкретна работна книга.

Вариант 2: "Бабешки" начин:) Въвеждаме помощна колонка:) Примерно ако потребителя въвежда данните в колонка А правим колонка B съдържаща следната формула:

  =A1/10^4  (В интерес на истината варианта =A1/10000 ще е по-бърз, но нали трябва да ви припомням и странични неща. Примерно, че ^ е степенуване ):):)

Плюсове: Локално за работна книга. Което означава, че когато я изпратите на ваш познат ще си работи и при него. Няма код.

Минус: Ми от всякъде погледнато тази допълнителна колонка е грозна. Т.е. не тя ами колонката в която потребителя въвежда данните. Това може да се избегне по два  налична. Или колонката B е скрита (или изобщо е няма и във всяка формула съдържанието на A се дели на 10000). Или колонка А се слага на лист примерно "Въвеждане", а на друг лист се вижда в целия блясък коригираните стойности (т.е. "работната" колонка и останалите колонки които са производни от нея")!

Вариант 3: С "фокус" (разбирайте код):):) Натискате десен бутон на мишката върху името на листа изпълнявате командата View Code и в редактора слагате следния код:

' ------------- Начало на кода -------

Private Sub Worksheet_Change(ByVal Target As Range)
Const div = 10000
If Target.Count > 1 Then Exit Sub
If Not (Intersect(Target, Range("A1:A22")) Is Nothing) Then
   If IsNumeric(Target.Value) Then
   Application.Calculation = xlCalculationManual
   Application.EnableEvents = False
   Target.Value = Target.Value / div
   pplication.Calculation = xlCalculationAutomatic
   Application.EnableEvents = True
   End If
End If
End Sub
' ----- Край на кода ------

В различен цвят съм дал нещата които подлежат на настройка. Както виждате съм изнесъл като константа 10000 за да може лесно да пипате и променяте числото на което делите. Променяте и зоната в зависимост от вашите нужди и сте готови!:) Сега при промяна на клетките от посочената от вас зона ще действа "магията" за останалите клетки и работни листи няма да действа. За да е по-разбираем примера е направен само за промяна на единични клетки в посочената зона! За повече информация вижте другите теми за Target  (#037 и #036)!

Важна особеност е блокирането на събитията при промяна на стойността на клетката от самия макрос (target.value=.........). Защото може да изпаднете във вечен цикъл (в интерес на истината просто Excel при мен "гръмна" когато бях забравил този момент:):) За това е важна двойката оператори:

Application.EnableEvents = False и Application.EnableEvents = True "заграждащи" самото действие. Така ние временно "приспиваме" Excel да не отчита промяната на стойността на клетката от макроса!

Плюсове: Пълен контрол. Свързано е с работната книга и конкретни клетки от нея.  Книгата ще си "носи" със себе си тази функционалност. Няма излишни колонки. Промяна на данните в мястото на въвеждане.

Минус: Наличието на код. Т.е. потребителя трябва да разреши макросите. Което си е досадно. Ако трябва да се ползват новите формати на офис 2007/20010 книгата трябва да бъде записана в XLSM а не XLSX!

Успех:)