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