Функція Excel SUBTOTAL (Зміст)
- SUBTOTAL в Excel
- SUBTOTAL Формула в Excel
- Як використовувати функцію SUBTOTAL в Excel?
SUBTOTAL в Excel
- Підсумкова функція - це вбудована функція в excel, яка класифікується за функцією math / trig
- Підсумковий номер не просто підсумовує числа у визначеному діапазоні комірок, він допоможе виконувати різні арифметичні операції
- SUBTOTAL універсальний. Ви можете використовувати його для середнього, підсумовування, підрахунку купу комірок та інших розрахунків
Визначення
Повертає підсумкові дані в базі даних або списку, тобто для пошуку підсумкових даних певного діапазону комірок. Він виконує заданий розрахунок для заданого набору значень.
SUBTOTAL Формула в Excel
Формула функції SUBTOTAL така:
SUBTOTAL (function_num, ref1, (ref2), …) Де
Function_num або код операції: вказує, яку функцію слід використовувати для обчислень підсумкових даних у заданому списку, або це тип математичної операції, який ви хочете виконувати
Аргумент function_num застосовується на основі наведених нижче двох критеріїв або методу
- Включити приховані значення Коли аргумент function_num знаходиться між 1-11, функція SUBTOTAL буде включати приховані значення.
- Ігнорувати приховані значення Коли аргумент function_num знаходиться між 101-111, функція SUBTOTAL буде виключати або ігнорувати приховані значення. Він знайде значення лише невидимих рядків ігнорує приховані значення в обчисленні.
| Fun_num Включає приховані значення | Fun_num Виключає приховані значення | Функція | Опис | |||
| 1 | 101 | СЕРЕДНЕ | Він обчислює Середнє значення визначеного діапазону. | |||
| 2 | 102 | РАХУВАТИ | Він підрахує кількість комірок, що містять числові значення. | |||
| 3 | 103 | КОНТА | Він підраховує не порожні комірки у вказаному діапазоні. | |||
| 4 | 104 | МАКС | З'ясується найбільше значення у вказаному діапазоні. | |||
| 5 | 105 | ХВ | З'ясується найменше значення у вказаному діапазоні. | |||
| 6 | 106 | ПРОДУКТ | Він обчислює добуток комірок у заданому діапазоні. | |||
| 7 | 107 | СТДЕВ | Він оцінює стандартне відхилення у визначеному діапазоні. | |||
| 8 | 108 | STDEVP | Він обчислює стандартне відхилення на основі всього населення. | |||
| 9 | 109 | СУМ | Він обчислює суму вказаного діапазону. | |||
| 10 | 110 | VAR | Він оцінює дисперсію у вказаному діапазоні. | |||
| 11 | 111 | VARP | Він оцінює дисперсію на основі всієї сукупності. |
ref1, (ref2): одне або більше посилань на комірки, що містять значення, які слід обчислити для підсумкових даних.
Як використовувати функцію SUBTOTAL в Excel?
Давайте розберемося, як функція SUBTOTAL працює в Excel.
Ви можете завантажити цей шаблон програми SUBTOTAL Function Excel тут - Шаблон програми Excel SUBTOTAL FunctionПриклад №1 - SUBTOTAL Формула значень, прихованих за допомогою автоматичного фільтра Excel або рядків, прихованих фільтром
У наведеному нижче прикладі таблиця містить квартальні дані продажів, де окремі чверті у колонці E та дані про продажі у колонці F.
Функція суми застосовується у комірці “F16”, тобто = SUM (F4: F15).
Він повертає або призводить до значення 1184 .
Функція SUM повертає те саме значення. тобто 1184, а не 194 для даних Q4, якщо рядки приховані фільтром (Нижче згаданий скріншот).
Якщо я бажаю лише правильного значення SUM для Q4, тоді мені потрібно використовувати функцію SUBTOTAL.
Перш ніж застосувати функцію SUBTOTAL, на панелі інструментів даних застосуйте фільтр для рядка заголовка.
Тепер давайте підрахуємо конкретно Q1 або Q2 або Q3 або Q4 дані про продаж окремо для відфільтрованих даних.
Використовуйте 109 як аргумент function_num у функції SUBTOTAL для підбиття підсумків даних. тобто = SUBTOTAL (109, C17: C28)
Функція SUBTOTAL ігнорує рядки, приховані фільтром, і обчислює правильний результат.
Тут субтотальне значення динамічно змінюється відповідно до фільтра, воно знайде суму значень лише у видимих рядках; приховані рядки нехтують функцією SUBTOTAL. (Нижче згадується наприклад)
Q4
Q3
Q2
Q1
Приклад №2 - SUBTOTAL Функція для прихованих рядків вручну
На аркуші excel рядки 15, 16, 19 та 20 були приховані за допомогою параметра форматування рядків, виділяючи ці рядки, клацаючи правою кнопкою миші та вибираючи пункт Приховати.
Підсумкова функція використовується в комірці C23 електронної таблиці для обчислення суми видимих комірок.
Ми повинні використовувати 109 як аргумент function_num у функції SUBTOTAL для підбиття підсумків даних.
тобто = SUBTOTAL (109, C14: C21)
Тут функція SUBTOTAL ігнорує приховані вручну рядки та обчислює правильний результат.
Він знайде суму значень лише у видимих рядках; приховані рядки нехтують функцією SUBTOTAL
Приклад №3 - SUBTOTAL Функція для нормального значення
Оскільки всі комірки в діапазоні квартальних цифр продажів видно, жодна з них не фільтрується або рядки не приховуються, вона включає всі значення.
У наведеному нижче прикладі застосовано кілька субтотальних функцій.
У комірці застосовується функція C14, C15 і C16 SUBTOTAL.
Для обчислення значення SUM функція № 9 або 109 використовується в підсумковій функції, де вона обчислює суму заданого діапазону, тобто = SUBTOTAL (9, C4: C11)
Це дасть нам суму всіх значень у C4: C11, за умови, що жодне не відфільтровано, а рядки приховані у вказаному діапазоні.
Для обчислення значення MIN використовується функція номер 5 або 105 в підсумковій функції, де вона виявить найменше значення у вказаному діапазоні. У комірці C15 застосовується функція SUBTOTAL, тобто = SUBTOTAL (5, C4: C11)
Він знайде найменше значення у вказаному діапазоні, тобто C4: C11
Для обчислення значення MAX функція 4 або 104 використовується в підсумковій функції, де вона виявить найбільше значення у вказаному діапазоні. У комірці C16 застосовується функція SUBTOTAL, тобто = SUBTOTAL (4, C4: C11)
З'ясується найбільше значення у вказаному діапазоні, тобто C4: C11.
Що слід пам’ятати про функцію SUBTOTAL
- Для пошуку суми відфільтрованих значень можна застосувати функцію SUBTOTAL.
- Функція SUBTOTAL використовується для ігнорування значень у прихованих рядках.
- Функція _ число аргументу значення не повинно бути більше 11 або менше 1, аналогічно не повинно бути більше 111 або менше 101, інакше воно дасть #VALUE! помилка.
- Функція SUBTOTAL застосовна лише для числового значення.
- Якщо в діапазоні SUBTOTAL є якісь підсумкові величини, вони будуть знехтувані, тобто якщо якісь уже існуючі формули SUBTOTAL є у вхідному діапазоні іншої формули SUBTOTAL, тоді ці значення знехтуються субтотальною функцією під час обчислення, щоб уникнути подвійного підрахунку & це запобігає помилці подвійного підрахунку.
- Для динамічного підбиття даних можна використовувати функцію SUBTOTAL.
- Пусті клітини та комірки, що містять нечислові значення, ігноруються субтотальною функцією під час обчислення.
Рекомендовані статті
Це керівництво по SUBTOTAL. Тут ми обговорюємо формулу SUBTOTAL та як використовувати функцію SUBTOTAL разом із прикладом excel та шаблонами Excel, які можна завантажити. Ви також можете переглянути ці корисні функції у відмінність -
- ПОШУК Функція в Excel
- ЗНАЙТИ Функцію в Excel
- НЕ в Excel
- ТРАНСПОЗИЦІЯ в Excel