Завантажте зразок робочої книги
Цей підручник демонструє, як користуватися ПІДСУМКОВА функція Excel в Excel для розрахунку зведеної статистики.
ОГОЛОШЕННЯ функцій Огляд
Функція SUBTOTAL Обчислює підсумкову статистику для ряду даних. Доступні статистичні дані включають, але не обмежуються середнім, стандартне відхилення, кількість, хв і макс. Дивіться повний список нижче в розділі введення функцій:
Щоб використати функцію SUBTOTAL Excel Worksheet, виберіть клітинку та введіть:
(Зверніть увагу, як виглядають вхідні дані формул)
ПІДСУМКОВИЙ Синтаксис функції та входи:
| 1 | = SUBTOTAL (номер_функції, REF1) |
function_num - Номер, що позначає, яку операцію виконувати.
REF1 - Діапазони або посилання, що містять дані для розрахунку.
Що таке функція SUBTOTAL?
SUBTOTAL-це одна з унікальних функцій електронних таблиць, оскільки вона може визначити різницю між прихованими клітинками та невидимими. Це може виявитися дуже корисним при роботі з відфільтрованими діапазонами або коли вам потрібно налаштувати обчислення на основі різних вибору користувачів. Оскільки він також знає, що ігнорує інші функції SUBTOTAL у своїх розрахунках, ми також можемо використовувати його у великих узагальнених даних, не боячись подвійного підрахунку.
Основне резюме з SUBTOTAL
Скажімо, у вас була таблиця відсортованих продажів продукції, і ви хотіли створити підсумки для кожного продукту, а також створити загальну загальну суму. Ви можете скористатись зведеною таблицею або вставити деякі формули. Розглянемо такий макет:
Я помістив деякі ПІДСУМКОВІ функції у клітинки В5 і В8, які виглядають так
| 1 | = ПІДСУМКИ (9, В2: В4) |
З синтаксису ви можете використовувати різні числа для першого аргументу. У нашому конкретному випадку ми використовуємо 9, щоб вказати, що хочемо зробити суму.
Зупинимось на клітині В9. Вона має цю формулу, яка включає весь діапазон даних стовпця В, але не включає інші проміжні підсумки.
| 1 | = ПІДСУМКИ (9, В2: В8) |
ПРИМІТКА. Якщо ви не хочете самостійно писати всі підсумкові формули, перейдіть на стрічку Дані та скористайтесь майстром Контур - проміжний підсумок. Він автоматично вставить рядки та розмістить формули замість вас.
Різниця в перших аргументах
У першому прикладі ми використовували 9, щоб вказати, що хочемо зробити суму. Різниця між використанням 9 і 109 полягає в тому, як ми хочемо, щоб функція обробляла приховані рядки. Якщо ви використовуєте позначення 1XX, функція не включатиме рядки, які були приховані або відфільтровані вручну.
Ось наша таблиця від раніше. Ми перенесли функції, щоб побачити різницю між аргументами 9 і 109. З усіма видимими результатами однакові.
Якщо ми застосуємо фільтр, щоб відфільтрувати значення 6 у стовпці В, дві функції залишаться незмінними.
Якщо ми приховуємо рядки вручну, ми бачимо різницю. Функція 109 змогла ігнорувати прихований рядок, тоді як функція 9 - ні.
Змінити математичну операцію за допомогою SUBTOTAL
Можливо, вам хотілося б іноді дати можливість користувачеві змінити тип обчислень. Наприклад, чи хочуть вони отримати суму або середнє значення. Оскільки SUBTOTAL контролює математичну операцію за номером аргументу, ви можете записати це в єдиній формулі. Ось наша установка:
Ми створили спадне меню в D2, де користувач може вибрати "Сума" або "Середній". Формула в Е2 така:
| 1 | = ПІДСУМКИ (IF (D2 = "Середнє значення", 1, IF (D2 = "Сума", 9)), B2: B4) |
Тут функція IF збирається визначити, який числовий аргумент надати SUBTOTAL. Якщо A5 - “Середнє”, то воно видасть 1, а SUBTOTAL дасть середнє значення B2: B4. Або, якщо A5 дорівнює «Сумі», то IF виводить 9, і ми отримуємо інший результат.
Ви можете розширити цю можливість, використовуючи таблицю пошуку, щоб перелічити ще більше типів операцій, які ви хочете виконати. Ваша таблиця пошуку може виглядати так
Тоді ви можете змінити формулу в E2 на
| 1 | = ПІДСУМКОВИЙ (VLOOKUP (A5, LookupTable, 2, 0), B2: B4) |
Умовні формули з SUBTOTAL
Хоча SUBTOTAL має багато операцій, які він може виконувати, він не може самостійно перевірити критерії. Однак ми можемо використовувати його у допоміжному стовпці для виконання цієї операції. Коли у вас є стовпець даних, який, як ви знаєте, буде завжди Якщо у вас є частина даних, ви можете використовувати SUBTOTALs для виявлення прихованих рядків.
Ось таблиця, з якою ми будемо працювати у цьому прикладі. Зрештою, ми хотіли б мати змогу підсумувати значення для «Apple», але також дозволити користувачеві відфільтрувати стовпець «Кількість».
Спочатку створіть допоміжний стовпець, у якому буде розміщена функція 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 Таблицях, як і в Excel:
ПІДСУМКОВІ Приклади у VBA
Ви також можете використовувати функцію SUBTOTAL у VBA. Тип:application.worksheetfunction.subtotal (номер_функції, reh1)
Виконання наступних операторів VBA
| 1234567891011121314151617 | Діапазон ("C7") = Application.WorksheetFunction.Subtotal (1, Діапазон ("C2: C5"))Діапазон ("C8") = Application.WorksheetFunction.Subtotal (2, Діапазон ("C2: C5"))Діапазон ("C9") = Application.WorksheetFunction.Subtotal (4, Діапазон ("C2: C5"))Діапазон ("C10") = Application.WorksheetFunction.Subtotal (5, Діапазон ("C2: C5"))Діапазон ("C11") = Application.WorksheetFunction.Subtotal (9, Діапазон ("C2: CE5"))Діапазон ("D7") = Application.WorksheetFunction.Subtotal (1, Діапазон ("D2: D5"))Діапазон ("D8") = Application.WorksheetFunction.Subtotal (2, Діапазон ("D2: D5"))Діапазон ("D9") = Application.WorksheetFunction.Subtotal (4, Діапазон ("D2: D5"))Діапазон ("D10") = Application.WorksheetFunction.Subtotal (5, Діапазон ("D2: D5"))Діапазон ("D11") = Application.WorksheetFunction.Subtotal (9, Діапазон ("D2: D5"))Діапазон ("E7") = Application.WorksheetFunction.Subtotal (1, Діапазон ("E2: E5"))Діапазон ("E8") = Application.WorksheetFunction.Subtotal (2, Діапазон ("E2: E5"))Діапазон ("E9") = Application.WorksheetFunction.Subtotal (4, Діапазон ("E2: E5"))Діапазон ("E10") = Application.WorksheetFunction.Subtotal (5, Діапазон ("E2: E5"))Діапазон ("E11") = Application.WorksheetFunction.Subtotal (9, Діапазон ("E2: E5")) |
дасть такі результати
Для аргументів функції (число_функції тощо) ви можете або ввести їх безпосередньо у функцію, або визначити змінні для використання замість них.
Повернення до списку всіх функцій Excel
