MAXIF, MAXIFS и Кашчей Безсмъртни

Наскоро, за да разрешим един бизнес казус, трябваше да използваме функция MAXIF в Ексел. Такава, както и MAXIFS, стандартно няма до версия 2016 за Офис 365, но алтернативите не са сложни за прилагане и отдавна добре описани. И целта на тази статия е да ви запозная с тях. Дори и да сте потребител на Офис 365 с Ексел 2016 или 2019, препоръчвам да прочетете статията докрай, за да видите една интересна формула, която търси дали дадена клетка съдържа която и да е дума от някакъв списък. А такава със сигурност все още не съществува в стандартния пакет на Ексел.

Казусът

Да предположим, че сте счетоводител във фирма продаваща на едро хранителни стоки. Идва данъчен служител на проверка и иска да му предоставите информация за приходите по дни и видове стоки в една такава таблица:

След като му я предоставите, той я поглежда и казва „Аааа, чудесно, чудесно. Дайте ми, моля, пълната документация за стоките от този списък.“

„Само че не искам да се занимавам с всички, нека да бъде само за най-голямата сума, която сте реализирали през периода.“ Разбира се, данъчният служител не се подиграва с вас, а просто желае да си свърши работата по-ефективно като се съсредоточи върху големите числа. А на вас ви трябва функция

MAXIF

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

Да  се конструира такава формула не е много сложно. Ето как:

{=MAX(IF($C$4:$C$28=F4;$D$4:$D$28))}

Как работи:

С IF проверяваме дали стойностите в областта С4:С28 са равни на F4, т.е. проверява дали стоката е равна на „круши“. Ако е равна, връща стойност TRUE, ако не е равна – стойност FALSE. може да го проверите като кликнете върху IF и натиснете бутона за функция (или използвате клавишната комбинация SHIFT+F3).

След това идва ред на MAX, която взема само тези редове от колона С, за които IF е върнал TRUE и проверява на кой от тях в колона D е максималната стойност.

Тази формула не е обикновена формула, защото сравняваме една клетка с цяла област ($C$4:$C$28=F4). Такива формули се наричат array формули. И за да работят правилно, след като ги въведем трябва да натиснем не просто Enter а CTRL+SHIFT+ENTER. и когато го направим, формулата се огражда автоматично с къдрави скоби ({}). Ексел сам си ги слага, не е необходимо да го правите вие.

Дотук добре – измислихме вариант за MAXIF и бързо и точно предоставяме исканата от данъчния служител информация. Но той решава да оптимизира още малко работата си и да поиска данните не просто за най-голямата сделка, а за най-голямата сделка след определена дата (например 31.08.2018).

Сега вече ви трябва

 MAXIFS

 Няма проблем:

{=MAX(IF(($C$4:$C$28=F4)*($B$4:$B$28>$H$2);$D$4:$D$28))}

Тук принципът на действие е малко по-друг.

Имаме две проверки ($C$4:$C$28=F4) проверява дали стоката в колона С е „картофи“, а ($B$4:$B$28>$H$2) – дали датата е по-голяма от 31.08.2018. Всяка една от тези проверки връща TRUE за вярно и FALSE за невярно. А след това ги умножаваме. За ексел FALSE=0 и TRUE =1. Така че когато умножим FALSE с каквото и да е число, резултатът е 0, а ако умножим TRUE*TRUE – резулататът е 1. Така получаваме една поредица от нули и единици, което може да проверим по познатия начин.

И понеже нулата е FALSE, а единицата е TRUE, то IF подава на MAX за оценка само тези стойности от колона D, които съответстват на 1. в случая – 4200 и 3000. Това са приходите от продажба на картофи след 31 Август. След това МАХ си свършва работата и избира по-голямата стойност, т.е. 4200.

Кашчей Безсмъртни

За съжаление в реалността ние рядко продаваме с една фактура само дини, или само картофи, или само ябълки. Обикновено продаваме по няколко стоки.

Нека се доближим до реалността и приемем, че от счетоводната система данните излизат в такъв вид:

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

Как да изнамерим тези редове, на които са продадени стоки от списъка на данъчния служител и то след 31 Август, че и да намерим максималната сума?

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

На Ексел език казано искаме да проверим в областта С4:С28 къде се срещат стрингове от областта Н4:Н7 и ако намерим такива, да ги изведе като резултат

И отново ще използваме array формула:

=IFERROR(INDEX($H$4:$H$7;MATCH(TRUE;ISNUMBER(SEARCH($H$4:$H$7;C4));0));““)}

Тази формула не е сложна, а по-скоро напомня на приказките за Кашчей Безсмъртни – смъртта му е в игла, сложена в яйце, което се намира в патица, патицата в заек, а заекът в сандък, заключен с вериги и окачен на тристагодишен дъб.

В случая ролята на иглата играе функцията SEARCH. Тя проверява дали даден текст присъства в друг текст и връща номера на позицията, от която започва. Ако не открие, връща грешка (#VALUE!).

В нашия пример SEARCH проверява дали стойноста в клетка Н4 („картофи“)  се съдържа в текста в С5 („картофи, ябълки;сирене“)  ще върне 1, защото текстът „картофи“ започва от 1 символ в С5. След това, понеже формулата е array, отива в Н5 („диня“) и проверява дали и тя случайно не се намира в С5. Но не я открива и връща грешка. И така докато обходи целия списък в областта Н4:Н7.

Важно е да се уточни, че SEARCH не е case sensitive. Ако ви трябва case sensitive функция – използвайте FIND

Аналогът на яйцето е ISNUMBER. Една много хубава функция, която връща TRUE, ако дадена стойност е число и съответно FALSE, ако не е. Това е една концепция, която вече съм представял в друга статия.

Но тук комбинацията от ISNUMBER и SEARCH (т.е. нашето яйце с игла в него) създава една виртуална поредица от TRUE и FALSE.

 Най-добре го разбираме като кликнем върху патицата във формулата, т.е. MATCH.

MATCH е функция, която търси някаква стойност в списък и връща номера в този списък, на който открива за първи път търсената стойност. В случая сме инструктирали MATCH да търси TRUE в списък от TRUE и FALSE. И понеже намира TRUE на първо място, връща стойност 1.

И тази стойност се използва от заека (INDEX), за да върне първата стойност от областта Н4:Н7.

А самият заек е в сандък (IFERROR), защото ако MATCH не открие стойност от списъка, както например в клетка С8, ще върне грешка #N/A, което ще обърка работата на МАХ на следващия етап. Затова указваме на ексел, ако има грешка, да върне празно (два пъти кавички едно след друго („“) са знакът за празно).

И накрая, за да окачим сандъка на тристагодишния дъб удряме CTRL+SHIFT+ENTER, защото това е array формула.

След като разкрихме тайната на Кашчей Безсмъртни, можем просто да си приложим MAXIF или MAXIFS, но не като използваме за търсене колоната „Стока“, а помощната колона „SEARCH“

Като обобщение:

* Ексел не предлага стандартно функции MAXIF и MAXIFS

* Алтернатива на MAXIF:

{MAX(IF($4:;$D$4:$D$28))}

* Алтернатива на MAXIFS:

{MAX(IF(($C$4:$C$28=F4)*($B$4:$B$28>$H$2);$D$4:$D$28))}

* бонус формула за проверка дали в дадена клетка се среща някоя стойност от някакъв списък (формула на Кашчей Безсмъртни):

=IFERROR(INDEX($H$4:$H$7;MATCH(TRUE;ISNUMBER(SEARCH($H$4:$H$7;C4));0));““)}

* Не забравяйте: CTRL+SHIFT+ENTER

Можете да свалите файла с формулите от тук

П.П. От само себе си се разбира, че абсолютно същата логика може да се приложи и за MINIF, AVERAGEIF, а ако ви харесва повече – дори и за SUMIF и COUNTIF. За съжаление тези алтернативи, няма да ви избавят от коварния капан на бавното изчисление

Публикувано в Формули с етикети , , . Постоянна връзка.