Бях в режим на мълчание, понеже не ми давате теми за писане, а и нямам време да си ги измислям:)
Скоро ми зададоха задачка да намеря най-дългия последователен брой работни дни. Реших да поровя за различни екзотики и попаднах на един фен на 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 умишилено е оставане без трети параметър!). За примерните данни се получава:
Това са данните: 8, 9, 11, 12, 13, 14, 17, 18, 19, 20
- IF(A3:A20="";ROW(A3:A20)). За всички клетки които НЕ отговарят на условието също се записва номера на реда. По този начин се получава огледален масив за групиране:
Това отговаря на: 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!
Ми успех в броенето;):)
