#61 Frequеncy и други магии. В търсене на Немо, Дори и най-дългата зона:)

Здравейте!

Бях в режим на мълчание, понеже не ми давате теми за писане, а и нямам време да си ги измислям:)

Скоро ми зададоха задачка да намеря най-дългия последователен брой работни дни. Реших да поровя за различни екзотики и попаднах на един фен на Frequency :) Както има фенове на Sumproduct така има и фенове на различни функции и с тях се опитват да решат всичко. Нещо като Golden Hammer (за повече информация- Law of the instrument (Wikipedia)):)

Та реших да споделя с вас този трик. Може да ви е полезен. В чест и на новата учебна година в Свищов. Да си пожелаем повече бъдещи нинджи:)

Задачата е: Да се изведе най-големия брой от последователно запълнени клетки.

Примерни данни

Формулата е: {=MAX(FREQUENCY(IF(A3:A20<>"";ROW(A3:A20));IF(A3:A20="";ROW(A3:A20))))}
Резултат:4
Найс, а?:) Следва дисекция.

0. Функция Frequency. (Ако сте запознати с нейния начин на работа прескочете точката.)

Връзка към официалната помощна страница. Хелпа на функцията е некадърно преведен (сякаш правен с машинен превод) и объркващ. За да стане кашата пълна;) ето и моето обяснение:

• Функцията връща масив от стойности (array function).
• Има два параметъра, който са масиви или зона от клетки.
• Първият параметър представлява данните, които ще бъдат анализирани.
• Вторият параметър представлява стойностите за групиране.
• Резултатът е масив с дължина по-голяма от големината на втория параметър с едно.
• Резултата представлява масив, съдържащ БРОЯ на елементите от първия масив по-малки или равни от границата.
• Последната стойност в резултатния масив е БРОЯ на елементите по-големи от последната стойност за групиране.

Пример: 

{=FREQUENCY({1,2,3,4,5,6,7};{3,6})}

Резултат: {3;3;1}. Където:

• 3-> броя на числата по-малки или равни на 3 (1,2,3)
• 3-> броя на числата по-малки или равни на 6 (4,5,6)
• 1 -> броя на числата по-големи от 6 (7)

1. Дисекция на формулата

{=MAX(FREQUENCY(IF(A3:A20<>"";ROW(A3:A20));IF(A3:A20="";ROW(A3:A20))))}

• Формулата е CSE (въвежда се с Ctrl+Shift+Enter).

• IF(A3:A20<>"";ROW(A3:A20)). За всички клетки които отговарят на условието (да са пълни) се записва номера на реда. За останалите се запълва false (функцията If умишилено е оставане без трети параметър!). За примерните данни се получава: 

FALSE, FALSE, FALSE, FALSE,FALSE, 8,9, FALSE, 11, 12, 13, 14, FALSE, FALSE, 17,18, 19,20.
Това са данните: 8, 9, 11, 12, 13, 14, 17, 18, 19, 20

• IF(A3:A20="";ROW(A3:A20)). За всички клетки които НЕ отговарят на условието също се записва номера на реда. По този начин се получава огледален масив за групиране:

3,4,5,6,7,FALSE,FALSE,10,FALSE,FALSE,FALSE,FALSE,15,16,FALSE,FALSE,FALSE,FALSE
Това отговаря на: 3, 4, 5, 6, 7, 10, 15, 16

• FREQUENCY({8,9,11,12,13,14,17,18,19,20},{3,4,5,6,7,10,15,16}) . Брои стойностите по-малки или равни на стойностите за групиране. Резултат: 0, 0, 0, 0, 0, 2, 4, 0, 4 (последната стойност в резултата е броя на данните по-големи от 16!)
• Max({0,0,0,0,0,2,4,0,4}) Намира най-голямото число. Резултат 4.

Ми това е :) Просто като президент на щатите:) *упсс... Сори за политическото изказване:)

2. Варианти на формулата

2.1 Да се намери най-дългата поредица от ПРАЗНИ клетки:

{=MAX(FREQUENCY(IF(A3:A20="";ROW(A3:A20));IF(A3:A20<>"";ROW(A3:A20))))}

Резултат: 5

Коментар: Ми нищо различно. Просто обръщаме условията.

2.2. Да се намери най-дългата поредица съдържаща "C".

{=MAX(FREQUENCY(IF(A3:A20="C";ROW(A3:A20));IF(A3:A20<>"C";ROW(A3:A20))))}

Резултат:2

Без коментар;)

2.3 Да се намери най-дългата поредица съдържаща "C" ИЛИ "D".

Тук може да стане голяма беля! Има два подводни камъка, на които лесно може да се натресете. 

Обръщане (отрицание) на логическа операция. Не забравяйте, че освен обръщане на = в <> трябва да обърнете и логическата операция. (Булева алгебра, правила на Де Морган и други неща дето не се учат много много):) Законите на Де Морган.

Та трябва да се усетите, че формулата трябва да е нещо такова:

{=MAX(FREQUENCY(IF(OR(A3:A20="C";A3:A20="D");ROW(A3:A20));IF(AND(A3:A20<>"C";A3:A20<>"D");ROW(A3:A20))))}

Да ама не. Нацелвате втория камък. В CSE формули Or/And малко не бачкат както трябва:) Да не кажа хич;) Трябва да търсите техни заместители. Някъде из другите постове ви обърнах внимание, че Or е "събиране", а And е умножение. И Еврика! Ето ви формулата:

=MAX(FREQUENCY(IF((A3:A20="C")+(A3:A20="D");ROW(A3:A20));IF((A3:A20<>"C")*(A3:A20<>"D");ROW(A3:A20))))

Резултат: 3 

Красиво:)

2.4 Данните са разположени хоризонтално.

{=MAX(FREQUENCY(IF(A29:M29<>"";COLUMN(A29:M29));IF(A29:M29="";COLUMN(A29:M29))))}

Комантар: Вместо функцията Row() се използва Column().

Това е.

3. Заготовки.

Та заготовките, които трябва да запомните са:

• За данни в колона: {Max(Frequency(If (УСЛОВИЕ;Row());If(NOT УСЛОВИЕ;Row()))}
• За данни в ред: {Max(Frequency(If (УСЛОВИЕ;Column());If(NOT УСЛОВИЕ;Column())}
• Формулите се въвеждат с Ctrl+Shift+Enter!

Ми успех в броенето;):)

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

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

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