SUBTOTAL в Excel (формула, примери) - Използване на SUBTOTAL функция

Съдържание:

Anonim

Excel SUBTOTAL Функция (Съдържание)

  • SUBTOTAL в Excel
  • SUBTOTAL Формула в Excel
  • Как да използвате SUBTOTAL функция в Excel?

SUBTOTAL в Excel

  • Субтотална функция е вградена функция в excel, която е категоризирана по математика / тригова функция
  • Subtotal не просто ще сумира числата в определен диапазон от клетки, той ще помогне, като извършва различни аритметични операции
  • SUBTOTAL е универсален. Можете да го използвате, за да изчислите средно, да обобщите, преброите куп клетки и други изчисления

дефиниция

Връща субтотал в база данни или списък, т.е. За да разберете субтота на даден диапазон от клетки. Той извършва определено изчисление за даден набор от стойности.

SUBTOTAL Формула в Excel

Формулата за функцията SUBTOTAL е следната:

SUBTOTAL (function_num, ref1, (ref2), …) Къде

Function_num или операционен код: Посочва коя функция трябва да се използва за изчисленията на междинните данни в даден списък или е тип математическа операция, която искате да извършите

Аргументът function_num се прилага въз основа на споменатите по-долу два критерия или метод

  1. Включете скрити стойности Когато аргументът function_num е между 1-11, функцията SUBTOTAL ще включва скритите стойности.
  2. Игнорира скритите стойности Когато аргументът function_num е между 101-111, функцията SUBTOTAL ще изключи или игнорира скритите стойности. Той ще намира стойности само невидими редове игнорира скритите стойности в изчислението.
Fun_num
Включва скрити стойности
Fun_num
Изключва скритите стойности
функция описание
1 101 СРЕДНО АРИТМЕТИЧНО Той изчислява Средната стойност на определен диапазон.
2 102 БРОЯ Той ще преброи броя на клетките, които съдържат числови стойности.
3 103 COUNTA Той ще брои непразните клетки в определения диапазон.
4 104 MAX Той ще открие най-голямата стойност в определения диапазон.
5 105 MIN Той ще открие най-малката стойност в посочения диапазон.
6 106 КАТАЛОГ Той изчислява произведението на клетките в определения диапазон.
7 107 СтОткл Той оценява стандартното отклонение в определения диапазон.
8 108 STDEVP Той изчислява стандартното отклонение въз основа на цялото население.
9 109 SUM Той изчислява сумата на посочения диапазон.
10 110 VAR Той оценява дисперсията в определения диапазон.
11 111 VARP Той оценява дисперсията въз основа на цялото население.

ref1, (ref2): Една или повече препратки към клетки, съдържащи стойностите, които изчислението трябва да се извърши за субтотални.

Как да използвате SUBTOTAL функция в Excel?

Нека да разгледаме как работи функцията SUBTOTAL в Excel.

Можете да изтеглите този шаблон за функция SUBTOTAL Function Excel тук - Шаблон за функция SUBTOTAL Excel

Пример №1 - SUBTOTAL Формула за стойности, скрити с помощта на Excel Autofilter или редове, скрити от филтър

В посочения по-долу пример таблицата съдържа тримесечни данни за продажбите, където отделните тримесечия в колона 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 -

  1. ТЪРСЕНЕ Функция в Excel
  2. НАМЕРЕТЕ Функцията в Excel
  3. НЕ в Excel
  4. ТРАНСПОЗИЦИЯ в Excel