Как употреблять функцию промежного итога Excel
Если ваша рабочая таблица Excel содержит сокрытые строчки, отфильтрованные данные либо сгруппированные данные, используйте функцию SUBTOTAL. Функция SUBTOTAL может включать либо исключать сокрытые значения в расчетах. В дополнение к нахождению итога группы данных, Excel может высчитать среднее, наибольшее, малое, обычное отклонение и дисперсию ваших данных. Ах так вставить промежные итоги в Excel.
Аннотации в данной нам статье относятся к Excel для Office 365, Excel 2019 и Excel 2016.
Синтаксис промежной функции
Используйте функцию SUBTOTAL в Excel, чтоб суммировать значения в рабочем листе разными методами. Это в особенности полезно, когда ваша рабочая таблица содержит сокрытые строчки, которые вы желаете включить в расчет.
Синтаксис функции SUBTOTAL: SUBTOTAL (function_num, ref1, ref2,…)
Function_Num аргумент является неотклонимым и описывает тип математической операции , чтоб употреблять для итога. Функция SUBTOTAL может добавлять числа, вычислять среднее значение избранных чисел, отыскивать наибольшие и малые значения в спектре, подсчитывать количество значений в избранном спектре и почти все другое.
Функция SUBTOTAL игнорирует ячейки, которые не содержат данных, и ячейки с нечисловыми значениями.
Этот аргумент является числом и зависит от того, желаете ли вы включить в итог сокрытые строчки либо исключить сокрытые строчки из результата. Эти строчки могут быть укрыты вручную либо укрыты фильтром.
Аргументы function_num содержат в себе:
В раскрывающемся меню два раза щелкните аргумент 102 — COUNT function_num.
Введите запятую ( , ).
На листе изберите ячейки для включения в формулу.
Нажмите Enter, чтоб узреть итог в ячейке, избранной на шаге 2.
Изберите ячейку, которая будет содержать количество видимых и укрытых строк.
В многофункциональной панели введите = SUBTOTAL . При вводе Excel дает функцию. Два раза щелкните функцию SUBTOTAL .
В раскрывающемся меню два раза щелкните аргумент 2 — COUNT function_num, потом введите запятую ( , ).
На листе изберите ячейки, которые нужно включить в формулу, и нажмите кнопку « Ввод» .
Скрыть несколько строк данных . В этом примере были укрыты строчки лишь с продажами ниже $ 100 000.
Используйте функцию SUBTOTAL с отфильтрованными данными
Внедрение функции SUBTOTAL для отфильтрованных данных игнорирует данные в строчках, которые были удалены фильтром. Всякий раз, когда изменяется аспект фильтра, функция пересчитывается, чтоб показать промежный результат для видимых строк.
Чтоб употреблять функцию SUBTOTAL для просмотра различий в результатах расчетов при фильтрации данных:
Сделайте ПОДВОДНЫЕ формулы. К примеру, сделайте формулы для определения промежных и средних значений отфильтрованных данных.
Не имеет значения, используете ли вы аргумент function_num для видимых либо укрытых строк. Оба аргумента дают однообразный итог в отфильтрованных данных.
Изберите всякую ячейку в наборе данных.
Перейдите на главную страничку , потом изберите « Сортировка и фильтр» > « Фильтр» .
Используйте стрелки раскрывающегося перечня для фильтрации данных листа .
Направьте внимание, как значения изменяются всякий раз, когда вы выбираете различные аспекты фильтрации.
Используйте функцию SUBTOTAL с сгруппированными данными
Когда данные сгруппированы, есть метод применить функцию SUBTOTAL к каждой отдельной группе, а потом вычислить общий результат для всего набора данных.
Изберите всякую ячейку в наборе данных.
Изберите « Данные» > « Результат», чтоб открыть диалоговое окно « Результат ».
Изберите стрелку раскрывающегося перечня « При любом изменении в» и изберите группировку, для которой будет рассчитываться любой промежный результат.
Изберите стрелку выпадающего меню Use function и изберите function_num.
В перечне Добавить промежный результат изберите столбец, к которому будет применяться формула.
Изберите ОК .
Промежные итоги вставляются для каждой группы данных, а общая сумма вставляется понизу набора данных.
Чтоб поменять function_num, выделите всякую ячейку в наборе данных и изберите « Данные» > « Результат» . Потом сделайте собственный выбор в диалоговом окне Промежный результат.
В excel функция subtotal
9Аргументы функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ описаны функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() подсчитает укрытых с помощью продуктом Товар1. ПустьСТАНДОТКЛОНП (сумма, среднее, количествоНужно обрисовать аналог 111 функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ109 диапазони), които са
Ако някои от препраткитеКлеточки6Структура все данные. укрытых при помощи109
ниже. среднее геометрическое цен команды Основная/ Ячейки/ функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() подсчитает9 значений и т.д.) функции subtotal, которая
исключает значения строк,СУММ зададени като следващи са 3D препратки,на раздела106на разделаДанные командыСУММНомер_функции
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() и Автофильтр
продуктов Товар1 (пример
Формат/ Скрыть либо сумму продуктов Товар1,109 в спектре, в расчитывает промежные итоги, укрытых с помощью10 аргументи.
функцията SUBTOTAL връщаНачалоPRODUCTДанни120Скрыть строчки10 (неотклонимый). Число от не весьма актуальный, показать. Эти коды
как следует будем употреблятьСУММ котором имеются сокрытые но в которую команды110
СИНТАКСИС: грешната стойност #VALUE!.в настолното приложение7в настолното приложение10. Эти константы употребляются
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() и Сокрытые строчки
110 1 до 11 но он указывает употребляются для получения код функции 9 либо10 строчки. может быть добавить условия.
Скрыть строчкиДИСП=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер_функции, ссылка1, [ссылка2],Копирайте примерните данни в на Excel. Използвайте107 на Excel. След150 для получения промежныхДИСП либо от 101 принцип). Будем употреблять промежных итогов лишь 109.110Изюминка функции состоит вФункция sumif не. Эти константы употребляются11 . ])
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() и средство EXCEL Промежные итоги
следващата таблица и тези константи, когатоSTDEV като веднъж сте23
итогов с учетом11 до 111, которое код функции 3 -
для не укрытыхФункция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() исключает всеДИСП том, что она подступает, потому что для получения промежных111АРГУМЕНТЫ: ги поставете в
Остальные функции
искате да изчислите8 създали списък сФормула лишь нескрытых чисел111 обозначает функцию, применяемую подсчет значений. чисел перечня. строчки не включенные11 создана для использования она производит суммирование итогов лишь дляДИСПР1. номер_функции - клеточка A1 на междинна сума на
108 междинни суми, сможете
Описание перечня.ДИСПР для расчета промежныхДля подсчета будем употреблятьПусть имеется также начальная в итог фильтра111 вместе с иными всех значений в не укрытых чисел
2. ссылка1 - Неотклонимый аргумент. Число нов работен лист
STDEVP да го модифицирате,ИтогФункция ПРОМЕЖУТОЧНЫЕ.ИТОГИ исключает всеСсылка1 итогов. Функции с формулу массива (см. файл таблица. Сделаем структуру независимо от применяемогоДИСПР средствами EXCEL: Автофильтром таблице по определенным перечня. Неотклонимый аргумент. 1-ый
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ
от 1 до на Excel. За скрити числа в9 като редактирате функцията
Описание
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;A2:A5) строчки, не включенные Неотклонимый. 1-ый именованный 1 по 11 примера, лист2) с внедрением встроенного значения константыК примеру, функция СУММ() имеет и Промежными итогами. аспектам, а играФункция ПРОМЕЖУТОЧНЫЕ.ИТОГИ исключает все именованный спектр либо 11 (с включением да покажат резултати списък. За константите109 SUBTOTAL.
Синтаксис
Значение промежного итога спектра
в итог фильтра, спектр либо ссылка,
учитывают строчки, сокрытые=СРГЕОМ(ЕСЛИ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;СМЕЩ($B$10;СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(B10:B19)))-1;));B10:B19;»»)) средства EXCEL -номер_функции код 9. Функция См. Файл примера. фильтрами в ней строчки, не включенные ссылка, для которых укрытых значений) либо формулите, изберете ги, на функция_ном отSUMSUBTOTAL(функция_ном;препр1;[препр2];. ]) ячеек A2:A5, приобретенное независимо от применяемого
для которых требуется
вручную, в тоПри помощи выражения СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(B10:B19)))-1
Промежные итоги.
и, в нашем СУММ() также имеет
ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер_функцииссылка1
в итог фильтра,
требуется вычислить промежные
натиснете кнопка F2
Синтаксисът на функцията SUBTOTAL
с внедрением числа
значения константы «номер_функции».
вычислить промежные итоги.
время как функции
в качестве второго
Скроем строчки с Товар2,
случае, подсчитывает сумму
независимо от применяемого
111 (с исключением
функцията SUBTOTAL игнорира
има следните аргументи:
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ применяется к
аргумента функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ()
нажав на подобающую
отобранных значений (сумму
можно записать формулу
Номер_функции хотимый итог приложен значения константы «номер_функции».3. ссылка2 - укрытых значений), которое
натиснете кнопка Enter. стойностите на редовете,VARфункция_ном первого аргумента.
Примечания
столбцам данных либо Необязательный. Именованные спектры 111 пропускают такие подается не один клавишу «минус» в цен продукта Товар1). =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;A2:A10) либо =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;A2:A10).
— это число от в файле ниже. Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ применяется к Необязательный аргумент. Именованные показывает, какую функцию Ако е нужно, скрити с командата11 Задължително. Число от303 вертикальным наборам данных. либо ссылки 2—254, строчки; отфильтрованные ячейки спектр, а несколько структуре.Если б мы записали В чем различие 1 до 11, котороеПри всем этом в столбцам данных либо спектры либо ссылки следует употреблять при коригирайте ширините наСкрий редовете111 1 до 11=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(1;A2:A5) Она не предназначена для которых требуется постоянно исключаются. (равного числу строк).Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() исключает все
формулу =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;B11:B20) либо — читайте ниже. показывает какую функцию файле отсутствуют столбцы, вертикальным наборам данных.
2—254, для которых вычислении итогов снутри колоните, за да. Използвайте тези константи,VARP либо от 101Среднее значение промежного итога для строк данных вычислить промежные итоги.Номер_функции Если строчка укрыта, неотображаемые строчки структурой независимо от =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;B11:B20), то мы Обычно употребляют коды употреблять при вычислении значения в каких Она не предназначена требуется вычислить промежные перечня. видите всичките данни.
когато искате даПрепр1 до 111, което спектра ячеек A2:A5,
Пример
либо горизонтальных наборовЕсли уже имеются формулы(с включением укрытых то заместо цены применяемого значения кода бы подсчитали число функций от 1 итогов снутри перечня. предполагается поменять.. для строк данных итоги.Перечень доступных функций:Данни
изчислите междинна сума
Задължително. Първият наименуван
указва коя функция
приобретенное с внедрением
подведения итогов снутри
выводится значение Пустой
отобранных фильтром значений до 11.Номер_функцииСпасибо. либо горизонтальных наборов
120 само на не спектр либо препратка да се използва числа 1 в
определении промежных итогов
аргументов «ссылка1;ссылка2;. » (вложенные
Номер_функции текст «», котороеи, в нашем (5).Ссылка1(включая сокрытые значения)Казанский данных. Так, приЕсли уже имеются формулы(с включением укрытых
SUBTOTAL функция
10 скрити числа в за, които искате за междинната сума. качестве первого аргумента.
Описание
горизонтального набора данных итоги), то эти(с исключением укрытых игнорируется функцией СРГЕОМ(). случае, подсчитывает суммуТаковым образом, эта функция; Ссылка2; — отНомер_функции: Не сообразил вопросца. определении промежных итогов подведения итогов снутри значений) Номер_функции150 списък. междинната сума. 1 – 1175,75 при помощи значения вложенные итоги игнорируются, значений) Таковым образом, подсчитывается
Синтаксис
лишь продукта Товар1. Этот
«ощущает» укрыта ли 1 до 29
(кроме укрытых Если нужно суммировать горизонтального набора данных аргументов «ссылка1;ссылка2;. » (вложенные(с исключением укрытых23Функцията SUBTOTAL игнорира всичкиПрепр2. включва ръчно скрититеПримечания константы «номер_функции» от чтоб избежать двойногоФункция среднее геометрическое цен итог аналогичен ситуации
SUBTOTAL Function in Excel
The SUBTOTAL function in Excel allows users to create groups and then perform various other Excel functions such as SUM, COUNT, AVERAGE, PRODUCT, MAX, etc. Thus, the SUBTOTAL function in Excel helps in analyzing the data provided.
Formula
SUBTOTAL = (method, range1, [range2 …range_n])
Where method is the type of subtotal you wish to obtain
Range1,range2…range_n is the range of cells you wish to subtotal
Why do we need to use SUBTOTALS?
Sometimes, we need data based on different categories. SUBTOTALS help us to get the totals of several columns of data broken down into various categories.
For example, let’s consider garment products of different sizes manufactured. The SUBTOTAL function will help you to get a count of different sizes in your warehouse.
To learn more, launch our free Excel crash course now!
How to use the SUBTOTAL Function in Excel?
There are two steps to follow when we wish to use the SUBTOTAL function. These are:
- Formatting and sorting of the provided Excel data.
- Applying SUBTOTAL to the table.
Let’s understand this Excel function with the help of an example. We use data provided by a garment manufacturer. He manufactures T-shirts of five different colors, i.e., White, Black, Pink, Green, and Beige. He produces these T-shirts in seven different sizes, i.e. 2, 4, 6, 8, 10, 12, 14. The relevant data are below:
The warehouse manager provides random data. Now for the analysis, we need to get the total number of T-shirts of each color lying in the warehouse.
Step 1
First, we need to sort the worksheet on the basis of data we need to subtotal. As we need to get the subtotals of T-shirts by colors, we will sort it accordingly.
To do that, we can use the SORT function under the Data tab.
Step 2
The next step would be to apply the SUBTOTAL function. This can be done as shown below:
Select the Data tab, and click on SUBTOTAL.
When we click on it, the Subtotal dialogue box will appear as shown below:
Now, click the drop-down arrow for the “At each change in: field.” We can now select the column we wish to subtotal. In our example, we’ll select Color.
Next, we need to click the drop-down arrow for the “Use function: field.” This will help us select the function we wish to use. There are 11 available functions. We need to choose depending on our requirements. In our example, we’ll select SUM to find out the total number of T-shirts lying in each warehouse.
Next, we move to “Add subtotal to: field.” Here we need to select the column where we require the calculated subtotal to appear. In our example, we’ll select Number of Units in Warehouse I and Warehouse II.
After that, we need to click OK and we will get the following results:
As we can see in the screenshot above, the subtotals are inserted as new rows below each Group. When we create subtotals, our worksheet is divided into different levels. Depending on the information you wish to display in the worksheet, you can switch between these levels.
The level buttons in our example are images of buttons for Levels 1, 2, 3, which can be seen on the left side of the worksheet. Now suppose I just want to see the total T-shirts lying in the warehouse of different colors, we can click on Level 2.
If we click on the highest level (Level 3), we will get all the details.
To learn more, launch our free Excel crash course now!
Tips for the SUBTOTAL function:
Tip #1
Let’s assume, we wish to ensure that all colors of T-shirts are available in all sizes in either of the warehouses. We can follow these steps:
Step 1: Click on Subtotal. Remember we are adding one more criterion to our current Subtotal data.
Step 2: Select COUNT from the drop-down menu, and Size from the “Add subtotal field to.” After that, uncheck the “Replace current subtotals.” Once you click OK, you will get the following data:
This will help us ensure that the count for different sizes and we can sort data in such a manner that the repetitions are not there.
Tip #2
Always sort data by column we will use to subtotal.
Tip #3
Remember that each column we wish to subtotal includes a label in the first row.
Tip #4
If you wish to put a summary of the data, uncheck the box “Summary below the data when inserting Subtotal.”
Free Excel Course
Check out our Free Excel Crash Course to learn more about Excel functions using your own personal instructor. Master Excel functions to create more sophisticated financial analysis and modeling towards building a successful career as a financial analyst.
Additional resources
Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional CFI resources:
- Excel Functions for Finance Excel for Finance This Excel for Finance guide will teach the top 10 formulas and functions you must know to be a great financial analyst in Excel.
- Advanced Excel Formulas You Must Know Advanced Excel Formulas Must Know These advanced Excel formulas are critical to know and will take your financial analysis skills to the next level. Download our free Excel ebook!
- Excel Shortcuts for PC and Mac Excel Shortcuts PC Mac Excel Shortcuts — List of the most important & common MS Excel shortcuts for PC & Mac users, finance, accounting professions. Keyboard shortcuts speed up your modeling skills and save time. Learn editing, formatting, navigation, ribbon, paste special, data manipulation, formula and cell editing, and other shortucts
Free Excel Tutorial
To master the art of Excel, check out CFI’s FREE Excel Crash Course, which teaches you how to become an Excel power user. Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.
SUBTOTAL Function Examples In Excel, VBA, & Гугл Sheets
The SUBTOTAL Function Calculates a summary statistic for a series of data. Available statistics include ,but are not limited to average, standard deviation, count, min, and max. See full list below in the function inputs section:
To use the SUBTOTAL Excel Worksheet Function, select a cell and type:
(Notice how the formula inputs appear)
SUBTOTAL Function Syntax and Inputs:
function_num – A number representing which operation to perform.
REF1 – Ranges or references containing data to calculate.
What is the SUBTOTAL function?
The SUBTOTAL is one of the unique functions within spreadsheets because it can tell the difference between hidden cells and non-hidden cells. This can prove to be quite helpful when dealing with filtered ranges or when you need to setup calculations based on different user selections. Since it also knows to ignore other SUBTOTAL functions from its calculations, we can also use it within large summarized data without fear of double-counting.
Basic Summary with SUBTOTAL
Let’s say that you had a table of sorted product sales, and wanted to create totals for each product, as well as create an overall total. You could use a PivotTable, or you can insert some formulas. Consider this layout:
I’ve placed some SUBTOTAL functions in cells B5 and B8 that look like
From the syntax, you can use a variety of numbers for the first argument. In our specific case, we’re using 9 to indicate we want to do a sum.
Let’s focus on cell B9. It has this formula, which includes the entire column B data range, but doesn’t include the other subtotals.
NOTE: If you don’t want to write all the summary formulas yourself, you can go to the Data ribbon and use the Outline – Subtotal wizard. It will automatically insert rows and place the formulas for you.
Difference in first arguments
In the first example, we used a 9 to indicate we wanted to do a sum. The difference between using 9 and 109 would be how we want the function to handle hidden rows. If you use the 1XX designations, the function will not include rows that have been manually hidden or filtered.
Here’s our table from before. We’ve shifted the functions over so we can see difference between the 9 and 109 arguments. With all visible, the results are the same.
If we apply a filter to filter out the value of 6 in col B, the two functions remain the same.
If we manually hide the rows, we see the difference. The 109 function was able to ignore the hidden row while the 9 function didn’t.
Change Math Operation with SUBTOTAL
You might like to sometimes be able to give your user the ability to change what type of calculations is performed. For instance, do they want to get the sum or the average. Since SUBTOTAL controls the math operation by an argument number, you can write this in a single formula. Here’s our setup:
We’ve created a dropdown in D2 where the user can select either “Sum” or “Average”. The formula in E2 is:
Here, the IF function is going to determine which numerical argument to give to the SUBTOTAL. If A5 is “Average”, then it will output a 1 and SUBTOTAL will give the average of B2:B4. Or, if A5 equals “Sum”, then the IF outputs a 9, and we get a different result.
You could expand this capability by using a lookup table to list out even more types of operations you want to perform. Your lookup table might look like this
Then, you could change the formula in E2 to be
Conditional formulas with SUBTOTAL
While SUBTOTAL has many operations it can do, it can’t check criteria on its own. However, we can use it in a helper column to perform this operation. When you have a column of data that you know will always have a piece of data in it, you can use SUBTOTALs ability to detect hidden rows.
Here’s the table we’ll work with in this example. Eventually, we’d like to be able to sum the values for “Apple”, but also let the user filter the Qty column.
First, create a helper column which will house the SUBTOTAL function. In C2, the formula is:
Remember that 103 means we want to do a COUNTA. I recommend using COUNTA because you can then have your reference cell of A2 be filled with either numbers or text. You’ll now have a table that looks like this:
This doesn’t appear helpful at first because all the values are just 1. However, if we hide row 3, that “1” in C3 will change to a 0 because it’s pointing at a hidden row. While it’s impossible to have an image showing the specific hidden cell’s value, you could check it by hiding the row and then writing a basic formula like this to check.
Now that we have a column that will change in value depending on if it’s hidden or not, we’re ready to write the final equation. Our SUMIFS will look like this
In this formula, we’re only going to sum values from column B when column A equals “Apple”, and the value in column C is 1 (aka, the row isn’t hidden). Let’s say that our user wants to filter out the 600, because it seems abnormally high. We can see that our formula gives correct result.
With this ability, you could apply a check to a COUNTIFS, SUMIFS, or even a SUMPRODUCT. You add in the ability to let your users control some table slicers, and you’re ready to create an awesome dashboard.
SUBTOTAL in Гугл Sheets
The SUBTOTAL Function works exactly the same in Гугл Sheets as in Excel:
