Когато вашият работен лист в Excel съдържа скрити редове, филтрирани данни или групирани данни, използвайте функцията SUBTOTAL на Excel. Функцията SUBTOTAL може да включва или изключва скрити стойности в изчисленията. В допълнение към намирането на общата сума на група данни, Excel може да изчисли средното, максималното, минималното, стандартното отклонение и дисперсията на вашите данни. Ето как да вмъкнете междинни суми в Excel.
Инструкциите в тази статия се отнасят за Excel за Microsoft 365, Excel 2019 и Excel 2016.
Синтаксис на функцията SUBTOTAL
Използвайте функцията SUBTOTAL в Excel, за да обобщите стойностите в работен лист по различни начини. Особено полезно е, когато вашият работен лист съдържа скрити редове, които искате да включите в изчислението.
Синтаксисът на функцията SUBTOTAL е: SUBTOTAL(функция_номер, ref1, ref2, …)
Аргументът function_num е задължителен и указва вида на математическата операция, която да се използва за междинната сума. Функцията SUBTOTAL може да събира числа, да изчислява средната стойност на избраните числа, да намира максималните и минималните стойности в диапазон, да брои броя на стойностите в избран диапазон и др.
Функцията SUBTOTAL игнорира клетки, които не съдържат данни и клетки с нечислови стойности.
Този аргумент е число и зависи от това дали искате да включите скрити редове в резултата или да изключите скрити редове от резултата. Тези редове могат да бъдат ръчно скрити или скрити от филтър.
Аргументите номер_на функция включват:
| Функционална задача | функция_номер | функция_номер |
|---|---|---|
| (включва скрити стойности) | (изключва скритите стойности) | |
| СРЕДНО | 1 | 101 |
| COUNT | 2 | 102 |
| COUNTA | 3 | 103 |
| MAX | 4 | 104 |
| MIN | 5 | 105 |
| ПРОДУКТ | 6 | 106 |
| STDEV | 7 | 107 |
| STDEVP | 8 | 108 |
| SUM | 9 | 109 |
| VAR | 10 | 110 |
| VARP | 11 | 111 |
Референтните аргументи номер_на функция от 1 до 11 включват само стойности в скрити редове, когато използвате командата Скриване за скриване на редове. Когато използвате командата Filter, изчисленията SUBTOTAL не включват скрити резултати от филтъра.
Аргументът ref1 е задължителен. Това са клетките, използвани за изчисляване на резултатите от избрания аргумент номер_на функция. Този аргумент може да бъде стойност, една клетка или диапазон от клетки.
Аргументите ref2, … не са задължителни. Това са допълнителни клетки, които са включени в изчислението.
Използване на функцията SUBTOTAL със скрити редове
Функциите на Excel могат да се въвеждат ръчно или с помощта на диалоговия прозорец Аргументи на функцията. За да илюстрирате как да въведете функцията ръчно с помощта на лентата с формули, следващият пример използва аргумента COUNT function_num, за да преброи броя на стойностите във видимите редове и както във видимите, така и в скритите редове.
За да използвате функцията SUBTOTAL за преброяване на броя на редовете в работен лист:
- Започнете с работен лист, който съдържа множество редове с данни.
-
Изберете клетката, която ще съдържа броя на видимите редове.
-
В лентата с функции въведете =МЕЖДИННА СУМА. Докато пишете, Excel предлага функция. Щракнете двукратно върху функцията SUBTOTAL.
За да използвате диалоговия прозорец с аргументи на функцията, за да въведете функцията SUBTOTAL, отидете на Formulas и изберете Math & Trig >МЕЖДУОБЩО.
Image -
В падащото меню, което се показва, щракнете двукратно върху аргумента 102 – COUNT номер на функция.
Image -
Въведете запетая (,).
Image -
В работния лист изберете клетките, които да включите във формулата.
Image -
Натиснете Enter, за да видите резултата в клетката, която сте избрали в стъпка 2.
Image - Изберете клетката, която ще съдържа броя на видимите и скритите редове.
- В лентата с функции въведете =МЕЖДИННА СУМА. Докато пишете, Excel предлага функция. Щракнете двукратно върху функцията SUBTOTAL.
- В падащото меню, което се появява, щракнете двукратно върху аргумента 2 – COUNT номер на функцията, след което въведете запетая (,).
-
В работния лист изберете клетките, които да включите във формулата, след което натиснете Enter.
Image -
Скриване на няколко реда с данни. В този пример редовете само с продажби под $100 000 бяха скрити.
Image
Използване на функцията SUBTOTAL с филтрирани данни
Използването на функцията SUBTOTAL върху филтрирани данни игнорира данните в редове, които са премахнати от филтъра. Всеки път, когато критериите за филтър се променят, функцията преизчислява, за да покаже междинната сума за видимите редове.
За да използвате функцията SUBTOTAL, за да видите разликите в резултатите от изчисленията, докато филтрирате данните:
-
Създаване на формули за МЕЖДИННИ СУММА. Например, създайте формули за определяне на междинната сума и средните стойности на филтрираните данни.
Няма значение дали използвате аргумента function_num за видими или скрити редове. И двата аргумента дават един и същ резултат във филтрирани данни.
Image - Изберете която и да е клетка в набора от данни.
-
Отидете на Начало, след което изберете Сортиране и филтриране > Филтър.йени
Image -
Използвайте стрелките за падащо меню, за да филтрирате данните в работния лист.
Image -
Забележете как стойностите се променят всеки път, когато изберете различни критерии за филтриране.
Image
Използване на функцията SUBTOTAL с групирани данни
Когато данните са групирани, има начин да приложите функцията SUBTOTAL към всяка отделна група и след това да изчислите общата сума за целия набор от данни.
- Изберете която и да е клетка в набора от данни.
-
Изберете Данни > Междинна сума, за да отворите диалоговия прозорец Междинна сума.
Image - Изберете падащата стрелка При всяка промяна в и изберете групирането, към което ще бъде изчислена всяка междинна сума.
- Изберете падащата стрелка Използване на функция и изберете номер_на функция.
- В списъка Добавяне на междинна сума към изберете колоната, към която ще се приложи формулата.
-
Изберете OK.
Image -
Междинните суми се вмъкват за всяка група данни, а общата сума се вмъква в долната част на набора от данни.
Image - За да промените номер_на функцията, маркирайте произволна клетка в набора от данни и изберете Данни > Междинна сума. След това направете своя избор в диалоговия прозорец Междинна сума.
