Функцията SUBTOTAL в Excel е невероятно полезна в различни сценарии, но малко хора познават всичките ѝ особености. Това им пречи да се възползват от предимствата, които тя предоставя. Затова в тази статия ще проучим синтаксиса и параметрите на функцията SUBTOTAL, ще подчертаем разликата между опциите „9“ и „109“ в първия параметър, ще предоставим случаи на употреба с примери и ще обсъдим някои „подводни камъни“ при нейното използване.

Теория на функцията SUBTOTAL

Предназначение:

Функцията SUBTOTAL в Excel ви позволява да извършвате изчисления върху диапазон от данни, като същевременно изключвате скрити или филтрирани редове.

Синтаксис:

Има следния синтаксис:

SUBTOTAL(номер, реф.1, реф.2, …)

номер: Този параметър указва вида на изчислението, което да се извърши. Може да приема стойности от 1 до 11 или от 101 до 111, всяка от които представлява различен тип изчисление, както се вижда на картинката по-долу.

Заб.: За целите на примерите по-долу аз ще използвам параметри 9 и 109, които отговарят на операцията събиране. Всичко казано важи и за останалите видове изчисления

ref1, [ref2], …: Това са препратките към диапазоните или клетките, които искате да включите в изчислението. Можете да предоставите няколко препратки, разделени със запетаи.

Разлика между „9“ и „109“ в първия параметър на функцията SUBTOTAL:

Първият параметър, „номер“, определя вида на изчислението, което да се извърши от функцията SUBTOTAL. Разликата между „9“ и „109“ се състои в тяхното третиране на скритите редове.

„9“ включва скритите редове в изчислението.

„109“ изключва скритите редове от изчислението.

Нека да видим какво значи това с един пример:

В клетки А1, А2, А3 и А4 са записани съответно числата 1, 2, 3 и 4.

Под тях са въведени формулите:

SUM(A1:A5)

SUBTOTAL(9;A1:A4)

SUBTOTAL(109;A1:A4)

За момента, както виждате, всички връщат като резултат 10, т.е. сумата на числата в клетки А1:А4.

Но когато скрием ред 2, тогава нещата се променят:

Сега първите две фомули отново връщат 10, но третата – 8, защото параметърът 109 указва на функцията SUBTOTAL да не сумира числата от скрити редове. Същото важи и ако редовете не се виждат, защото е приложен филтър. Затова в официалната документация се използва думата „видими“ клетки (visible cells).

Важни уточнения

Повечето ексел експерти и блогъри, както и вездесъщият ChatGPT ще ви кажат, че 9 ще направи изчислението като включи скритите редове и при прилагане на филтър на таблицата. Но това НЕ Е вярно. В официалната документация на Майкрософт за функцията ясно си е казано, че SUBTOTAL се държи по един и същ начин, когато има приложен филтър, без значение дали първият параметър е 9 или 109. И в двата случая функцията ще събере всички клетки, които отговарят на критерия за филтране. Различното поведение на 9 и 109 се проявява само когато имаме скрити редове без приложен филтър. Като няма значение дали ще ги скрием с Hide (CTRL+9) или с групиране.

Друга особеност е, че функцията SUBTOTAL работи само вертикално – за изчисление по редове. На долната картинка имаме няколко числа в колони от B до G. В колона Н е въведена функцията SUBTOTAL с първи параметър 109.

Тук са скрити няколко колони (D, E, F). Но това не променя стойността, която връща SUBTOTAL

Примери за използване на функцията SUBTOTAL

1. Сумиране само на видимите клетки при приложен филтър

Това е най-често срещаната употреба на функцията SUBTOTAL.

На картинката по-долу са представени хипотетични данни за продажбите на различни изделия в един бар на плажа на неназован курорт на Черно Море. Виждате най-отдолу в жълтата клетка е въведена формулата =SUBTOTAL(109;C2:C19).

Така както е в момента, тя просто събира стойностите в областта C2:C19.

Но ето тук е приложен филтър и виждаме само изделията, с думата „мента“ в заглавието. Вижте как в жълтата клетка е изчислен сборът само за тях. Много пъти това е удобно за бърза справка или за последващи калкулации.

2. Събиране като се изключват скритите редове

По-рядко, но все пак се случва да пресмятаме нещо като изключим редовете, които са скрити. Много често скриването става с групиране. На картинката по-долу имаме един такъв пример. Същата таблица, но някои редове са скрити с групиране.

Виждате, че стойностите са различни при използване на 9 и 109

А ето нещо интересно – всички редове от таблицата се виждат, но все пак има разлика в стойностите на формулите с 9 и 109. Как се получава това?

Отговорът е, че редовете са били скрити с бутоните за групиране, но после са показани с Unhide (SHIFT + CTRL +9). В този случай, въпреки че редовете са видими, SUBTOTAL не отчита това. Изглежда функцията очаква показването да стане по същия начин, по който е направено скриването, т.е. с бутоните за групиране/разгрупиране. Това е важен бъг, който трябва да се знае при използването на функцията SUBTOTAL в този специфичен случай.

3. Тотал на субтоталите

На картинката по-долу виждаме обобщена справка за продажбите на нашето заведение. То продава само няколко вида сладолед и коктейли на основата на ментовка. Виждате как има ред „Общо“ за двете групи стоки сладолед и коктейли. Накрая – „ОБЩО ОБЩО“ показва продажбите на всички стоки от двете групи. „Общо сладолед“ и „Общо коктейли“ наричаме субтотали. „ОБЩО ОБЩО“ е тотал.

Обърнете внимание, че функцията SUBTOTAL е използвана един път за сумиране на субтоталите по отделните групи („Общо сладолед“ и „Общо коктейли“) и втори път – за общия тотал („ОБЩО ОБЩО“).

Тази функция е достатъчно умна, за да разпознае своете събратя нагоре по колоната и да ги пропусне в събирането. Това е много удобно, когато се прави справка или финансов модел с много групи и подгрупи. Алтернативата е с използване на SUM и да се въведат члез кликане всички необходими клетки. Това може да доведе до грешки.

4. Динамична смяна на вида изчисление

Това вече е някаква екзотика. Но да речем, че имаме някакви числа

И искаме да въведем формула за обобщение. Само че видът на обобщаващата формула да се променя в зависимост от стойността в друга клетка. Избираме например в някаква клетка с падащо меню „sum” и получаваме сбора на числата. Ако изберем „average“ – виждаме средната стойност. При избор на “min” – получаваме най-малкото число. Не мога да се сетя някога да ми е трябвало такова нещо. Но ако случайно се видите в подобно приключение – ето едно възможно решение:

Как работи?

Най-напред подготвяме нещата като в клетка D2 е направено падащо меню. Стойностите на това меню са в колона F. Срещу всяка от тях е записан съответният параметър от функцията SUBTOTAL. 9 за събиране, 3 за броене и т.н.

Накрая в клетка В9 е въведена формула за субтотал, но първият параметър се получава чрез VLOOKUP. При смяна на стойността в клетка D2, VLOOKUP ще извика съответния параметър и SUBTOTAL ще промени вида на изчислението.

В заключение

В тази статия разгледахме една специфична функция, наречена SUBTOTAL. Тя предлага гъвкавост и удобство при пресмятане на данни, когато имаме филтрирани или скрити редове. Освен това успешно разпознава и пропуска себеподобните си в реда нагоре, което може да ви спести много време и нерви в търсене на грешни формули за събиране. Аз редовно използвам функцията SUBTOTAL за бързи справки и проверка на изчисления. Затова знам, че ще ви е от изключителна полза, ако усвоите нейния синтаксис и познавате тънкостите на нейната употреба.

Ако трябва да съм честен – причината да напиша тази статия беше любопитството да разбера в кои случаи има логика да се използва SUBTOTAL с първи параметър 9 вместо SUM. Защото двете функции привидно се държат по един и същи начин. Е, открих някои разлики и ги представих по-горе. Ако вие намерите други – моля да ми пишете във формата за контакт.

Може да изтеглите файла с всички разгледани примери от тук.