В тази статия ще научим как да използваме функцията SUBTOTAL в Excel.
Получавайте резултати за филтрирани редове
Най -големият проблем с SUM, AVERAGE, COUNT и други NUMBER функции е, че те включват редове, които са скрити от филтър. Те не работят правилно, когато искаме да сумираме, преброяваме или изпълняваме друга цифрова задача само на видима клетка. Ето стъпки във функцията SUBTOTAL на Excel.
СУБТОТАЛЕН Синтаксис на функцията:
| = СУБТОТАЛ(номер на функция, ref1, [ref2] …) |
Функция_номер: Междинната сума поддържа различни цифрови функции. За лесен достъп до тях се използва номер на функция. По -долу е дадена таблицата с номера на функциите със съответните функции.
| Номер на функцията До Включете ръчно скрити редове | Номер на функцията До Игнорирайте ръчно скритите редове | Функция |
| 1 | 101 | СРЕДНО АРИТМЕТИЧНО |
| 2 | 102 | БРОЯ |
| 3 | 103 | COUNTA |
| 4 | 104 | МАКС |
| 5 | 105 | МИН |
| 6 | 106 | ПРОДУКТ |
| 7 | 107 | СТДЕВ |
| 8 | 108 | STDEVP |
| 9 | 109 | SUM |
| 10 | 110 | VAR |
| 11 | 111 | VARP |
Ref1: Именованият диапазон или препратка към числа.
[ref2]: по избор именован диапазон или референтен диапазон към числа.
Пример:
Всичко това може да е объркващо за разбиране. Нека разберем как да използваме функцията, използвайки пример. Тук имаме тази таблица с недвижими имоти. Сега искам да добавя общ ред, но той трябва да ми покаже сумата от видими клетки само когато прилагам филтър.
Напишете тази формула в края на таблицата или в горната част. Твой избор…
| = ПОДБОТА (9, C2: C16) |
Сега, когато прилагате филтър, ще се показват само видимите клетки.
Както можете да видите от горното изображение, сумата от общата площ на филтрираните редове (1250 + 530 + 590) излиза 2370. Не е ли това впечатляващо.
COUNT стойности във филтрирани редове
За тази статия ще трябва да използваме функцията SUBTOTAL. Сега ще направим формула от функцията. Тук са ни дадени някои стойности в диапазон и конкретна текстова стойност като критерии. Трябва да преброим стойностите, където формулата включва всички стойности, които завършват с дадения текст или модел
Обща формула:-
| = SUBTOTAL (fun_num, range) |
fun_num: номер, номер, съответстващ на операцията
range: диапазон, където трябва да се приложи операцията.
Пример:
Всичко това може да е объркващо за разбиране. Така че, нека тестваме тази формула, като я изпълним в примера, показан по -долу.
Тук имаме данни за импортиране на поръчки. И ние сме приложили критерии към региона, само източният регион е филтриран.
Тук можете да намерите броя на филтрираните стойности. Изберете правилния аргумент като fun_num. Fun_num е операцията, която искате да приложите. Тук, за да преброим клетките, които използваме COUNTA операция, num като 3.
Използвайте формулата:
| = СУБТОТАЛ (3, B2: B14) |
Както можете да видите, общите редове, които са видими, се оказват 11. Сега ще използваме още един пример, за да извлечем средната стойност на количеството, което е видимо или филтрирано.
Използвайте формулата:
| = СУБТОТАЛ (1, C2: C14) |
Както можете да видите, средното количество поръчки, получено от източния регион, е приблизително 60. Можете да използвате различни операции като средно, броене, макс., Мин., Продукт, стандартно отклонение, сума или промяна според необходимите резултати по филтрирани данни .
Ето някои наблюдателни бележки, показани по -долу.
Бележки:
- Изберете правилния fun_num, за да получите желаните резултати.
- Формулата работи само с числа и текст
- Оператори като равен на ( = ), по -малко от равно на ( <= ), по-голяма от ( > ) или не е равно на () може да се изпълнява в рамките на функции, прилагани само към числа.
Надявам се, че тази статия за това как да използвате функцията SUBTOTAL в Excel е обяснима. Намерете още статии за изчисляване на стойности и свързани формули на Excel тук. Ако ви харесаха нашите блогове, споделете го с приятелите си във Facebook. Можете също така да ни следвате в Twitter и Facebook. Ще се радваме да чуем от вас, уведомете ни как можем да подобрим, допълним или обновим работата си и да я подобрим. Пишете ни на имейл сайта.
Как да използвате функцията SUMPRODUCT в Excel: Връща SUM след умножаване на стойности в множество масиви в Excel.
SUM, ако датата е между : Връща сумата на стойностите между датите или периода в Excel.
Сума, ако датата е по -голяма от датата: Връща сумата на стойностите след дадената дата или период в Excel.
2 начина за сумиране по месеци в Excel: Връща сумата на стойностите в рамките на даден конкретен месец в Excel.
Как да сумирате няколко колони с условие: Връща сумата на стойностите в множество колони, имащи условие в excel
Как да използвате заместващи символи в Excel : Пребройте клетки, съвпадащи с фрази, използвайки заместващи знаци в Excel.
COUNTIFS с динамичен диапазон от критерии : Брой клетки в зависимост от други стойности на клетки в Excel.
КОНФИЦИИ Съответствие по два критерия : Брой клетки, отговарящи на два различни критерия в списъка в Excel.
ИЗЧИСЛЕНИЯ С ИЛИ За множество критерии : Преброяване на клетки с множество съвпадащи критерии с помощта на функцията ИЛИ.
Функцията COUNTIFS в Excel : Преброяване на клетки в зависимост от други стойности на клетките.
Как да използвате Countif във VBA в Microsoft Excel : Пребройте клетките, като използвате кода на Visual Basic за приложения.
Как да използвате заместващи символи в Excel : Пребройте клетки, съвпадащи с фрази, използвайки заместващи знаци в Excel
Популярни статии:
Как да използвате функцията IF в Excel : Изразът IF в Excel проверява условието и връща конкретна стойност, ако условието е TRUE или връща друга конкретна стойност, ако FALSE.
Как да използвате функцията VLOOKUP в Excel : Това е една от най -използваните и популярни функции на excel, която се използва за търсене на стойност от различни диапазони и листове.
Как да използвате функцията SUMIF в Excel : Това е друга основна функция на таблото. Това ви помага да обобщите стойностите при конкретни условия.
Как да използвате функцията COUNTIF в Excel : Пребройте стойностите с условия, използвайки тази невероятна функция. Не е необходимо да филтрирате данните си, за да броите конкретни стойности. Функцията Countif е от съществено значение за подготовката на вашето табло.
