Капанът на COUNTIF и SUMIF

Функциите COUNTIF и SUMIF са сред най-полезните и често използвани функции на Ексел. С тяхна помощ броим или събираме стойности в зависимост от определени критерии.

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

Наскоро обаче се сблъсках с един казус, който ми припомни какъв е големият недостатък на COUNTIF и SUMIF. Мой колега се оплака, че при изготвянето на една справка губи по 2-3 часа само, за да се копират някакви формули. Звучи абсурдно, нали? Но беше факт. Лично седнах до него и го помолих да повтори стъпка по стъпка действията за изготвяне на справката. Няма шега – отваря два файла. Единият съдържа данни, другият – 7-8 колони с формули. Копира формулите от първия ред, пейства ги до  до долу и … чака два часа. Разбира се не чакахме толкова, а след две три минути затворихме Ексел (SHIFT+CTRL+ESC, за да отворим Windows Task Manager, след това намираме  Excel.exe, маркираме го и даваме End Process – затваря всеки файл на секундата).

След това направихме анализ на ситуацията. Оказа се следното:

 * Изходните данни в първия файл са около 190 000 (словом сто и деветдесет хиляди) реда

 * Повечето формули във втория файл са прости – събиране и умножение. Но в една от тях имаше VLOOKUP, която търсеше кои стойности в колона А от изходните данни се срещат в справката от предния месец

* Имаше и една колона, която изброяваше с COUNTIF колко пъти се срещат дадени стойности в колона В от изходния файл

* Целта на справката беше да се провери кои стойности от колона А ги няма (VLOOKUP връща #N/A) и от тях кои се срещат повече от един път (COUNTIF връща стойност по-голяма от 2). Общият брой на редовете, които остават в справката не надвишава 2 хиляди.

И ето тук беше ключът от палатката. COUNTIF е чудесна функция, но за 190 хиляди реда отнема 2-3 часа време, освен ако не работите на някакъв супер мощен компютър с параметрите на сървър. Просто така е направена да работи.

Въпросът е как да решим този проблем?

Ето няколко насоки за действие, които можете да имате предвид в подобни ситуации

  1. Формулите и действията да се изпълняват последователно

Това беше и решението в нашия случай. Просто първо копирахме VLOOKUP, което отне 2-3 минути (VLOOKUP също работи бавно на такъв брой редове, но не чак толкова). След това филтрирахме редовете, които не са #N/A и ги изтрихме. Така останаха под 5 хиляди реда. Накрая копирахме COUNTIF на тези 5 хиляди реда и както може би се досещате операцията отне не повече от минута. Ето как успяхме да свалим времето за изготвяте на справката от 3 часа на 5 минути!

  1. Копиране формулите на порции

Понякога горният подход би бил невъзможен. Например нямаме VLOOKUP, а просто трябва да изброим 190 хиляди реда с COUNTIF. Тогава вместо да копираме формулата на всички редове, правим го на по-малки порции от по 20-30 хиляди реда. Звучи странно, но  да се копира формула 6 пъти по 30 хиляди реда е много по-бързо от веднъж на 190 хиляди. Изглежда има някаква оптимизация за по-малък брой редове.

4. Ограничаване на областта на формулите

Често срещам формули, зададени по този начин: = COUNTIF(B:B;“круши“).

Това означава, че областта за търсене е цялата колона В. Прави се защото така се обхваща цялата колона и подсигуряваме, че ще обхванем всички данни колкото и много да са те. Но така принуждаваме COUNTIF да търси в над 1 млн. реда, което значително забавя неговото действие. Фактът, че по-голямата част от тях са празни не ни помага кой знае колко.

Затова винаги е по-добре да се ограничи обхватът на формулата. Например: = COUNTIF(B1:B1000;“круши“)

4. Използване на пивот таблица

Пивот таблиците по същество са точно обобщения на данни на базата на някакви критерии. Но са проектирани да работят много по-бързо от COUNTIF и SUMIF, дори и за големи обеми от данни. Затова (почти) винаги е възможно да се замени използването на формули с пивот таблица.

Като обобщение: COUNTIF и SUMIF са много полезни функции, но използвани за десетки хиляди редове са изключително бавни. Затова потърсете начин да намалите областта на тези функции или да ги замените с пивот таблица (моят личен избор). Разбира се, казаното по-горе важи за всички формули от този вид – AVERAGEIF, COUNTIFS, SUMIFS и др.

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