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

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

#014 Колко дена има в даден месец

В Analysis ToolPak add-in-са има функция ЕОMONTH, но съм виждал прекалено много инсталации с не инсталирани (не активирани)  add-in модули за това ще дам един по-универсален пример. =Day(Date(Година,Месец+1,0)). Ако искаме самата дата а не броя на дните просто се маха заграждащия Day. Годината е необходима за месец февруари. В останалите случай може просто да се сложи произволна година. Трикът е че взема нулевия ден на СЛЕДВАЩИЯ месец:)

Пример 1: Да се намери броя на дните (брой не дата!) в текущия месец.
Отговор : =DAY(DATE(YEAR(NOW()),MONTH(NOW())+1,0))

 Пример 2: Да се намери последната ден в месеца (като дата) за дата в клетката A1
Отговор: =DATE(YEAR(A1),MONTH(A1)+1,0)

Пример 3: Колко дена има в 2008 година:)
Отговор: =SUMPRODUCT(DAY(DATE(2008,ROW(INDIRECT("1:12"))+1,0)))
Това е мноооого ма много триково решение с цел да ви припомня как се прави вътрешен цикъл във формула:))) И не ви съветвам да се увличате по тях.
Иначе ... =DATE(2008,12,31)-DATE(2008,1,1)+1 :) Явно съм пропуснал да кажа, че датите могат да се вадят като числа. И да припомня едно правило от първи клас. Брой=Крайната_стойност-началната_стойност+1!! :):) Може и =DATEDIF("1.1.2008","31.12.2008","d")+1 :)

Дано вече усещате мощността на Excel (а още не съм започнал с макросите):)

Enjoy:)

#008 Сортиране при въвеждане на данните

Тук ще покажа как може да се направи така, че данните да се сортират динамично при въвеждане на данни в дадена област.
Ето примерния лист:

Сортировка в "движение"

При промяна  (въвеждане, изтриване или редактиране) на данните в областта A:B те автоматично се появяват е зоната F:G сортирани по факултетен номер.

1. Подготовка. За по-лесно създаване на формулите ще дефинираме две динамични имена (описани в тема #001).
Име FN (факултетен номер) сочещо към =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)
Име Danni (факултетен номер и име на студента) сочещо към =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,2)
NB! В случая се вижда че се вади единица от броя на редовете заради заглавията на колонките!
NB! Разликата между двете имена е, че втората област се състои от две колонки (последния параметър)!

2. Формула в клетка F2 - =IFERROR(SMALL(FN,ROW(A1)),"")
Ето анализът на тази формула:

• Променлив брояч. Row(A1). За разлика от примера с ЕГН-то тук искаме във формулата да имаме  стойност която да се се променя от едно до N в зависимост от броя на редовете в които се размножава формулата. (Ще обясня по-късно защо). Има по обикновен вариант поставяйки допълнителна колонка и в нея да се въведе стойността на брояча. Всъщност от израза Row(A1) ни интересува върната стойност 1 (реда на клетката А1) , която ще стане 2,3,4 и т.н. когато формулата се размножи надолу (А1 ще стане А2,А3 и т.н. и съответно и резултатът на Row ще се промени)...NB! Със същия успех вместо A1 може да използваме B1, X1 или която и да е клетка от първи ред.
• Намиране на N-тата по големина стойност... SMALL(FN,ROW(A1)). За първата клетка ще върне най-малката стойност, за втората клетка втората по големина и т.н. Именно за тази цел използвахме брояча. Hint! Ако искате да се подреждат в обратен ред се използва функцията Large!
• "Пакетираме" в IFError защото в даден момент функцията Small ще върне грешка когато в зоната няма вече стойности. (грешка #Num).

3. Формула в клетката G1. =IFERROR(VLOOKUP(F2,Danni,2,FALSE),"")
Запълването на имената става, чрез точно търсене (параметър False) с Vlookup в зоната за въвеждане. Връща се съдържането на втората колонка (в случая името). Пакетирането в IFError вече не го коментирам:)

NB! Имейте предвид, че този прмер ще работи при УНИКАЛНИ стойности в колонката която сортираме (в нашия случай Факултетен номер)! В случай на дублажи Vlookup няма правилно да определи съдържанието на втората колона.

#007 Контролно число на ЕГН с една формула

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

Проблем: Да се изчисли контролната цифра на ЕГН в клетката A1.
Отговор: =IF(MOD(SUMPRODUCT(VALUE(MID(A1,ROW(INDIRECT("1:9")),1))*({2;4;8;5;10;9;7;3;6})),11)=10, 0, MOD(SUMPRODUCT(VALUE(MID(A1, ROW( INDIRECT("1:9")) ,1))*({2;4;8;5;10;9;7;3;6})),11))

Nice А?:):) Ето анализът на формулата:

Контролно число: Последната цифра на ЕГН-то се нарича контролна и се изчислява по следния алгоритъм:
1. Всяка от първите девет цифри се умножава по съответно тегло. Теглата са: 2,4,8,5,10,9,7,3,6
2. Намира се сумата на тези произведения
3. Намира се остатъкът от делението на 11 на намерената в точка две сума
4. Ако остатъкът е по-малък от 10 това е контролното число, ако е равен на 10 контролното число е 0!

1. Извличане на първите девет цифрите от ЕГН-то  една по една: VALUE(MID(A1,ROW(INDIRECT("1:9")),1))

 Тук се симулира цикъл във формула. Няколко думи за неговото осъществяване:

• Ако стойностите не са много може просто да изброят заградени в {} и разделени със "," (хоризонтален масив)! В нашия случай трябваше да се напише {1,2,3,4,5,6,7,8,9}, което като се замисля не изглежда прекалено дълго и зле:)
• Когато стойностите са повече се използва конструкцията Row(начало:край). Например Row(1:9) или Row(1:999).... Тази конструкция има един недостатък. При местене на формулата тя се "настройва" за новото място. Не ни спасява и "трикът" с използването на абсолютни адреси например Row($1:$9). В този случай формулата не се променя  при преместване, но става каша ако изтриваме или вмъкваме редове в района на първи и девети ред. За това използвайте този метод предпазливо.
• Най-стабилния е метода използван в примера ROW(Indirect("начало:край"). В този случай формулата не зависи от "околната среда" и нейното местоположение:) Ще използвам този начин за правене на вътрешен цикъл и  в други примери.

Функцията Value преобразува извлечение символ в цифра. В интерес на истината съм подходил доста "консервативно" към проблемът, но и без това формулата си е сложна да правя други трикове в нея:) Като резултат се получава масив от първите девет цифри.

2. Сума на произведенията на цифрите и теглата:

SUMPRODUCT(VALUE(MID(A1,ROW(INDIRECT("1:9")),1))*({2;4;8;5;10;9;7;3;6}))

Тук трябва да се обърне внимание на масивът с теглата. Той е "вертикален"! Ха сега:) Пак ви намерих занимание за четене:) Ето тук (на англйски): http://office.microsoft.com/en-us/excel-help/more-arrays-introducing-array-constants-in-excel-HA001087291.aspx Тази "подробност" ми коства около час когато правих формулата де;) Т.е. е важно, че разделителят на елементите е ";" а не ","! (тук възниква въпросът как се отбелязват вертикалните и хоризонталните масиви при друг вид езикови настройки (друг десетичен знак и друг знак за разделител на списъци) за което ще пиша в коментар под темата когато го тествам!)

Няма да се спирам защо се използва SumProduct а не Sum (четете по-старите теми)!

3. Намиране остатък от делението на 11.Нищо сложно при използването на функцията Mod.

MOD(SUMPRODUCT(VALUE(MID(A1,ROW(INDIRECT("1:9")),1))*({2;4;8;5;10;9;7;3;6})) ,11)

4. Поставяне в If конструкция =IF(остатък=10,0,остатък).

Ми това е:)