#032 Функция SubTotal и защо е по-добра от Sum

Да погледнем #030 в реда за обобщение. Там намираме не Sum и Average ами функцията SubTotal. Да се замислим защо Excel използва нея а не "класическите" функции. SubTotal не се преподава, но е доста по-мощна и гъвкава от Sum и т.н. Та в общи линии първия параметър е число показващо каква е използваната функция а втория е самата зона. Ето стойностите на първия параметър:

Функция_ном (включва скрити стойности)	Функция_ном (игнорира скрити стойности)	Функция
1	101	AVERAGE
2	102	COUNT
3	103	COUNTA
4	104	MAX
5	105	MIN
6	106	PRODUCT
7	107	STDEV
8	108	STDEVP
9	109	SUM
10	110	VAR
11	111	VARP

Вижда се, че имаме две възможности за всяка функция. СЪС и БЕЗ да се включват скритите стойности! Тук се крие силата на тази функция!  По време на правенето или използването  на една таблица "скриваме" редове или колонки както с помощта да Hide или с помощта на филтрите. Сега е моментът да се замислите че SUM смята ВСИЧКО което му е подадено като параметър без да го е грижа какво се вижда и какво не! Т.е. "това което виждате може да НЕ е това което се сумира"!:) За разлика от SUM/Average и т.н. SubTotal ви дава право на избор!

=SubTotal(9,A1:A100) е пълен аналог на =SUM(A1:A100) сумирайки независимо дали са видими, докато =SubTotal(109,A1:A100) ще зависи кои клетки от зоната са видими! Този "малък" на пръв поглед нюанс може да ви създаде главоболия:) Забелязал съм, че рядко се набляга на този "дефект" на класическите функции и сякаш никога няма да мине през акъла на някой да скрива или филтрира:):)

Както казах обаче при таблиците в реда за обобщаване Excel "мъдро" слага правилните функции (т.е. SubTotal с отчитане на скритите редове). Ако не искате това ще се наложи да подмените предложените от Excel функции с "вашите" любими такива:) Чара на SubTotal е че може да работи и като класическа функция, за това просто сменяте първия параметър и сте ОК:)

NB! Да бъдем коректни не винаги е възможно използването на SubTotal! Тя не възприема така наречените 3D зони (зони които са между няколко работни листа). Например =SUM(Sheet1:Sheet4!A1) няма как да я подмените с SubTotal! За това не бързайте да погребвате Sum, Average и т.н. :):)

Като бонус ето как се прави табличка за демонстрация възможностите на SubTotal:)

Табличка за SubTotal

Условие: Когато потребителя пипа C1 и C2 да се вижда правилния резултат.

0. Подготовка. Колонките D, E и F са помощни и спокойно може да са на друг лист да не загрозяват пейзажа:) Или просто ги скрийте когато приключите с настройките и видите, че всичко е ОК

1. Дефиниране на имена. Понеже все забравям да направя тема за именуване сега ще пиша много:( Трябват ни три имена "Функции", "Всички", "Видими" за данните в помощните колонки. Най-бързия начин е да изберете клетките от D1 до F12 (данните с имената!) и да изпълните командата Formulas/Define Names/From Selection и да посочите че използвате и заглавния ред и първата колона. Така ще получим освен трите имена на колонките и имената "Average" за първите две стойности, "Count" за вторите и т.н.

Създаване на имена

Резултат от именуването (проверка чрез Formulas/NameManager)

Тук има един малък проблем. Името "Функции" сочи към данните а ние искаме да сочи към имената на функциите. За това трябва да оправим този проблем като редактираме името от Name Manager.

Редактиране на името "Функции"

2. Контрол на C1 и C2. За да може потребителя да избира стойности за контролиране на C1 използваме списъка "Функции",  а за C2 зоната E1:F1. (Вижте #002).

Контрол на C2

3. Формула в C3. Ще дам два варианта да е по-весело:) И да имате теми за размисъл и четене:)

Вариант 1: =SUBTOTAL(INDEX(INDIRECT(C2),MATCH(C1,Функции,0)),A1:A24)

Тук извличаме номера на функцията чрез Index:

INDIRECT(C2) - "Обръща" съдържанието на клетка C2 в зона. (Всички/Видими). Така получаваме зоната от която ще извличаме числото.

MATCH(C1,Функции,0) -  Тук намираме редът от който извличаме търсейки съдържанието на C1 в зоната Функции (за това и се наложи корекция!)

Вариант 2: =SUBTOTAL(INDIRECT(C1) INDIRECT(C2),A1:A28) 

 

Извличането на стойността е в израза:

INDIRECT(C1) INDIRECT(C2) - Това е странен израз. Две зони разделени с интервал. Тук хитростта е че се намира "сечението" на двете зони. Зоната C1 (това е хоризонталната зона на съответната функция) определя реда, Зоната C2 пък представяла колоната. Например ако сме избрали Max и Видими  се търси сечението между E5:F5 (зона Max) и F2:F12 (зона Видими). Сечението (общото между двете зони) е клетка F5. Която и съдържа това което ни трябва!

Явно и тема за зоните трябва да понапиша:)

Успех със SubTotal:)