SUBTOTAL Формула в Excel (Зміст)
- SUBTOTAL Формула в Excel
- Як використовувати формулу SUBTOTAL в Excel?
SUBTOTAL Формула в Excel
Іноді нам потрібно знайти підсумкову категорію з великого набору даних, що має кілька категорій. У такій ситуації субтотальна функція допоможе нам знайти загальну категорію. Не тільки підсумкові ми можемо обчислити середнє, підрахунок та макс та багато іншого.
Визначення
Функція, яка повертає підсумкові дані зі списку або бази даних, може бути визначена як функція Subtotal. Субтотал - це особлива функція порівняно з іншими функціями excel, оскільки інші функції excel будуть виконувати лише одну конкретну операцію, але особливість субтотальної функції полягає в тому, що вона виконує не тільки підсумковий підрахунок, але також множинна арифметика і логічні операції залежать від числа функції.
Синтаксис формули SUBTOTAL
Аргументи:
Номер функції : Це стосується типу математичної операції, яку ми будемо виконувати на визначеному діапазоні даних.
Ref1, Ref 2: Це стосується діапазону комірок.
Доступно декілька номерів функцій, але без проблем, нам не потрібно запам'ятовувати всі ці функціональні номери, оскільки, використовуючи формулу SUBTOTAL, excel автоматично покаже вам список функціональних номерів, як показано нижче.
Вам, мабуть, цікаво, чому для однієї і тієї ж функції є два функції.
- 1 - 11 функціональних номерів слід використовувати, коли ми хочемо ігнорувати відфільтровані комірки та розглядати приховані вручну комірки.
- 101 - 111 номери функцій повинні використовуватись, коли ми хочемо ігнорувати всі приховані комірки, які відфільтровані та приховані вручну комірки.
Ми побачимо кілька прикладів, щоб зрозуміти, як функція субтота буде працювати для різних номерів функцій.
Як використовувати формулу SUBTOTAL в Excel?
SUBTOTAL Formula в Excel дуже проста і легка. Давайте розберемося, як використовувати формулу SUBTOTAL в Excel з деякими прикладами.
Ви можете завантажити цей шаблон SUBTOTAL Formula Excel тут - Шаблон SUBTOTAL Formula ExcelПриклад №1
Розглянемо невелику таблицю, яка містить дані з різних категорій, як показано нижче.
Якщо ви дотримуєтесь наведеної вище таблиці, у нас є категорія продукту, категорія кольору та кількість.
Застосуйте формулу SUBTOTAL, як показано нижче, за допомогою функції № 9.
Після застосування цієї формули результат є таким, як показано нижче.
Застосуйте формулу SUBTOTAL, як показано нижче, за номером функції 109.
Після застосування формули SUBTOTAL результат показаний нижче.
У формулі ми використовували як функції № 9, так і 109 для виконання SUM у двох різних стовпцях. C2: C9 - це діапазон даних, за якими ми виконуємо розрахунки.
Тепер загальна сума становить 385 для обох формул. Сховати кілька рядків і спостерігати за результатами обох формул.
Після застосування SUBTOTAL Формули результат показаний нижче.
Рядки від 50 до 60 приховані, отже, результати функції № 109 змінилися на 255, оскільки вони не враховують дані, приховані вручну, тоді як функція номер 9 залишається такою ж, це означає, що вона буде враховувати дані, навіть якщо ми приховуємо дані вручну .
Застосовуйте фільтр до даних і фільтруйте лише один колір, тут нам обраний Чорний колір.
Після застосування SUBTOTAL Формули результат такий, як показано нижче.
Застосуйте формулу SUBTOTAL, як показано нижче, за допомогою функції №109.
Після застосування SUBTOTAL Формули результат такий, як показано нижче.
Якщо ми спостерігаємо наведений знімок екрана, обидва функції функції не враховували кількість відфільтрованих даних. Після того як ви відфільтруєте дані та приховати рядки, обидві формули дадуть однакові результати, що обидві формули не вважатимуть невидимі дані.
Після застосування формули SUBTOTAL результат наведений нижче.
Застосуйте формулу SUBTOTAL, як показано нижче, за допомогою функції №109.
Після застосування цієї формули результат є таким, як показано нижче.
Приклад №2
Досі ми бачили операцію SUM, тепер ми будемо виконувати операцію AVERAGE з тим же діапазоном даних. 1 і 101 - номери функцій для середнього виконання. Перегляньте вищевказану таблицю для детальної інформації про функції.
Після застосування SUBTOTAL Формули результат такий, як показано нижче.
Для наступної комірки знову застосуйте формулу SUBTOTAL за допомогою функції 109.
Після застосування формули результат є таким, як показано нижче.
З тією ж формулою SUBTOTAL ми можемо також обчислити середнє значення, змінивши лише лише номер функції.
Після застосування SUBTOTAL Формули результат такий, як показано нижче.
Для наступної комірки знову застосуйте формулу SUBTOTAL за допомогою функції № 101.
Після застосування формули SUBTOTAL, результат показаний нижче.
Сховайте кілька рядів, тепер спостерігайте за змінами в середньому. Середнє значення з функцією №1 залишається таким же, як і досі, воно також враховує приховану величину. Середнє значення з номером функції 101 змінилося, оскільки воно ігнорує дані, заховані вручну. Так, ви можете спробувати решту функціональних номерів і перевірити функціональність кожного, оскільки основна концепція така сама, вони також будуть працювати однаково.
Приклад №3
Підсумковий підсумок має ще одну перевагу, яку він не враховуватиме, чи є в діапазоні даних якісь підсумкові відомості. Ми побачимо один приклад, щоб краще зрозуміти. Формули введення для виконання продукту SUM з розумом, як A, B, C і D. Це не обов'язково, щоб це було продумано, як і для кольорів.
Після застосування формули SUBTOTAL результат є таким, як показано нижче.
Використовуйте формулу SUBTOTAL ще раз, щоб перевірити рівень B.
Після застосування формули SUBTOTAL результат наведений нижче.
Використовуйте формулу SUBTOTAL ще раз, щоб перевірити значення СУ.
Після застосування формули SUBTOTAL результат наведений нижче.
Скористайтеся формулою SUBTOTAL ще раз, щоб перевірити SUM D.
Після застосування формули SUBTOTAL результат наведений нижче.
Використовуйте формулу SUBTOTAL знову до наступної комірки.
Після застосування формули SUBTOTAL результат наведений нижче.
Використовуйте формулу SUBTOTAL знову до наступної комірки.
Після застосування формули SUBTOTAL, результат показаний нижче.
Якщо ми спостерігаємо за наведеними вище скріншотами, існує побічний результат суми, і виконуючи загальну суму, ми включаємо категорію мудрих підсумків у загальну суму (комірки від 36 до 48), але вона не враховуватиме підсумкові дані (комірки від 44 до 47 ). Таким чином, ми можемо обчислити підсумкові дані між діапазоном даних, не впливаючи на загальне значення суми.
Сподіваюсь, ви зрозуміли, як користуватися субтоталом і як його використовувати.
Що слід пам’ятати про формулу SUBTOTAL в Excel
- При наданні номера функції не вказуйте число, яке знаходиться поза діапазоном чисел функції, оскільки воно не визначене excel, ми отримаємо повідомлення про помилку. Ми завжди повинні давати число від 1 до 11 і 101 до 111, інакше воно видасть повідомлення про помилку # значення.
- При використанні операції ділення пам’ятайте, що жодне число не повинно ділитися на нуль. Це означає, що x / 0 - це неправильний формат.
- Якщо застосувати підсумковий підсумок для горизонтальних даних, які є від A1: F1, і приховати будь-які стовпці, це не вплине на підсумковий результат.
- При застосуванні підсумкових даних, якщо жодна комірка не має даних або нечислових даних, функція буде ігнорувати ці комірки.
- Використовуйте номери функцій, розуміючи функціональність, а в іншому випадку ви не отримаєте правильних результатів, як очікували.
Рекомендовані статті
Це керівництво по формулі SUBTOTAL в Excel. Тут ми обговорюємо, як використовувати формулу SUBTOTAL в Excel разом з практичними прикладами та шаблоном Excel, який можна завантажити. Ви також можете ознайомитися з іншими запропонованими нами статтями -
- Як використовувати формулу IRR Excel?
- Залишив TRIM в Excel (формула, приклади)
- Підручник з VLOOKUP в Excel
- Формула ЕКСЕЛЬНОГО РОЗМІРУ