Изтеглете примерната работна книга
Този урок демонстрира как да използвате Функция Excel SUBTOTAL в Excel за изчисляване на обобщена статистика.
ПОДБОТА Общ преглед на функцията
Функцията SUBTOTAL Изчислява обобщена статистика за поредица от данни. Наличните статистически данни включват, но не се ограничават до средно, стандартно отклонение, брой, мин. И макс. Вижте пълния списък по -долу в раздела за входни функции:
За да използвате SUBTOTAL функцията на работния лист на Excel, изберете клетка и въведете:
(Забележете как се появяват входовете на формулата)
СУБТОТАЛЕН Синтаксис на функциите и входове:
| 1 | = SUBTOTAL (номер на функция, REF1) |
function_num - Номер, представляващ коя операция да се извърши.
REF1 - Диапазони или препратки, съдържащи данни за изчисляване.
Какво представлява функцията SUBTOTAL?
SUBTOTAL е една от уникалните функции в електронните таблици, защото може да определи разликата между скрити клетки и не скрити клетки. Това може да се окаже доста полезно при работа с филтрирани диапазони или когато трябва да настроите изчисления въз основа на различни потребителски избори. Тъй като също така знае, че може да игнорира други SUBTOTAL функции от своите изчисления, можем също да го използваме в големи обобщени данни, без да се страхуваме от двойно преброяване.
Основно резюме с SUBTOTAL
Да приемем, че сте имали таблица с сортирани продажби на продукти и сте искали да създадете суми за всеки продукт, както и да създадете обща сума. Можете да използвате обобщена таблица или да вмъкнете някои формули. Помислете за това оформление:
Поставих някои СУБТОТАЛНИ функции в клетки B5 и B8, които изглеждат така
| 1 | = СУБТОТАЛНО (9, B2: B4) |
От синтаксиса можете да използвате различни числа за първия аргумент. В нашия конкретен случай използваме 9, за да посочим, че искаме да направим сума.
Нека се съсредоточим върху клетка В9. Той има тази формула, която включва целия диапазон от данни в колона B, но не включва другите междинни суми.
| 1 | = ПОДБОТА (9, B2: B8) |
ЗАБЕЛЕЖКА: Ако не искате сами да пишете всички обобщени формули, можете да отидете на лентата с данни и да използвате съветника Outline - Subtotal. Той автоматично ще вмъква редове и ще поставя формулите вместо вас.
Разлика в първите аргументи
В първия пример използвахме 9, за да посочим, че искаме да направим сума. Разликата между използването на 9 и 109 би била в това как искаме функцията да обработва скрити редове. Ако използвате обозначенията 1XX, функцията няма да включва редове, които са били ръчно скрити или филтрирани.
Ето нашата таблица от преди. Преместихме функциите, за да видим разлика между 9 и 109 аргумента. При всички видими резултати са едни и същи.
Ако приложим филтър за филтриране на стойността 6 в колона B, двете функции остават същите.
Ако ръчно скрием редовете, виждаме разликата. Функцията 109 успя да игнорира скрития ред, докато функцията 9 не.
Променете математическата операция с SUBTOTAL
Може да искате понякога да можете да дадете на потребителя си възможност да променя какъв тип изчисления се извършват. Например, искат ли да получат сумата или средната стойност. Тъй като SUBTOTAL контролира математическата операция чрез номер на аргумент, можете да напишете това в една формула. Ето нашата настройка:
Създадохме падащо меню в D2, където потребителят може да избере „Сума“ или „Средно“. Формулата в E2 е:
| 1 | = SUBTOTAL (IF (D2 = "Средно", 1, IF (D2 = "Сума", 9)), B2: B4) |
Тук функцията IF ще определи кой числов аргумент да даде на SUBTOTAL. Ако A5 е „Среден“, той ще изведе 1 и SUBTOTAL ще даде средното B2: B4. Или, ако A5 е равно на „Сума“, тогава IF извежда 9 и получаваме различен резултат.
Можете да разширите тази възможност, като използвате справочна таблица, за да изброите още повече видове операции, които искате да извършите. Вашата таблица за търсене може да изглежда така
След това можете да промените формулата в E2 да бъде
| 1 | = SUBTOTAL (VLOOKUP (A5, LookupTable, 2, 0), B2: B4) |
Условни формули с SUBTOTAL
Въпреки че SUBTOTAL има много операции, които може да извърши, той не може да провери критериите сам. Можем обаче да го използваме в помощна колона за извършване на тази операция. Когато имате колона с данни, за която знаете, че ще винаги ако имате част от данните в него, можете да използвате SUBTOTALs за откриване на скрити редове.
Ето таблицата, с която ще работим в този пример. В крайна сметка бихме искали да можем да сумираме стойностите за „Apple“, но също така да позволим на потребителя да филтрира колоната Qty.
Първо, създайте помощна колона, която ще съдържа функцията SUBTOTAL. В C2 формулата е:
| 1 | = СУБТОТАЛНО (103, A2) |
Не забравяйте, че 103 означава, че искаме да направим COUNTA. Препоръчвам да използвате COUNTA, защото след това можете да напълните вашата референтна клетка от A2 или числа или текст. Сега ще имате таблица, която изглежда така:
Това в началото не изглежда полезно, защото всички стойности са само 1. Ако обаче скрием ред 3, това „1“ в C3 ще се промени на 0, защото сочи към скрит ред. Въпреки че е невъзможно да има изображение, показващо стойността на конкретната скрита клетка, можете да го проверите, като скриете реда и след това напишете основна формула като тази за проверка.
| 1 | = С3 |
Сега, когато имаме колона, която ще се променя в зависимост от това дали е скрита или не, ние сме готови да напишем крайното уравнение. Нашите SUMIFS ще изглеждат така
В тази формула ще сумираме стойности само от колона В, когато колона А е равна на „Apple“, и стойността в колона C е 1 (известен още като редът не е скрит). Да кажем, че нашият потребител иска да филтрира 600, защото изглежда необичайно висок. Можем да видим, че нашата формула дава правилен резултат.
С тази възможност можете да приложите проверка към COUNTIFS, SUMIFS или дори SUMPRODUCT. Добавяте възможността да позволите на потребителите си да контролират някои нарязващи маси и сте готови да създадете страхотно табло за управление.
SUBTOTAL в Google Таблици
Функцията SUBTOTAL работи абсолютно същото в Google Sheets като в Excel:
СУБТОТАЛНИ Примери във VBA
Можете също да използвате функцията SUBTOTAL във VBA. Тип:application.worksheetfunction.subtotal (function_num, reh1)
Изпълнение на следните VBA изявления
| 1234567891011121314151617 | Диапазон ("C7") = Application.WorksheetFunction.Subtotal (1, Range ("C2: C5"))Диапазон ("C8") = Application.WorksheetFunction.Subtotal (2, Range ("C2: C5"))Диапазон ("C9") = Application.WorksheetFunction.Subtotal (4, Range ("C2: C5"))Обхват ("C10") = Application.WorksheetFunction.Subtotal (5, Range ("C2: C5"))Диапазон ("C11") = Application.WorksheetFunction.Subtotal (9, Range ("C2: CE5"))Обхват ("D7") = Application.WorksheetFunction.Subtotal (1, Range ("D2: D5"))Диапазон ("D8") = Application.WorksheetFunction.Subtotal (2, Range ("D2: D5"))Диапазон ("D9") = Application.WorksheetFunction.Subtotal (4, Range ("D2: D5"))Диапазон ("D10") = Application.WorksheetFunction.Subtotal (5, Range ("D2: D5"))Диапазон ("D11") = Application.WorksheetFunction.Subtotal (9, Range ("D2: D5"))Диапазон ("E7") = Application.WorksheetFunction.Subtotal (1, Range ("E2: E5"))Диапазон ("E8") = Application.WorksheetFunction.Subtotal (2, Range ("E2: E5"))Диапазон ("E9") = Application.WorksheetFunction.Subtotal (4, Range ("E2: E5"))Диапазон ("E10") = Application.WorksheetFunction.Subtotal (5, Range ("E2: E5"))Диапазон ("E11") = Application.WorksheetFunction.Subtotal (9, Range ("E2: E5")) |
ще даде следните резултати
За аргументите на функцията (номер на функция и т.н.) можете или да ги въведете директно във функцията, или да определите променливи, които да използвате вместо тях.
Връщане към списъка с всички функции в Excel
