Subtotal excel как пользоваться
Как подсчитать промежуточные итоги только видимых ячеек после фильтрации в Excel?
Предположим, вы вычислили общее значение из столбца с функцией Sum. При фильтрации данных в столбце функция Sum добавляет как видимые, так и скрытые ячейки. Если вы хотите получить промежуточные итоги только для видимых ячеек после фильтрации, методы из этого руководства вам помогут.
Подытог только видимых ячеек после фильтрации с помощью функции Промежуточный итог
Фактически, функция «Промежуточный итог» может помочь вам суммировать только видимые ячейки после фильтрации в Excel. Пожалуйста, сделайте следующее.
Синтаксис
=SUBTOTAL(function_num,ref1,[ref2],…)
аргументы
- Номер_функции (Обязательно): число от 1 до 11 или от 101 до 111, которое указывает функцию, используемую для промежуточного итога.
- Ref1 (Обязательно): именованный диапазон или ссылка, для которой требуется подытог.
- Ref2 (Необязательно): именованный диапазон или ссылка, по которым вы хотите вычислить промежуточные итоги.
1. Выберите пустую ячейку, скопируйте в нее приведенную ниже формулу и нажмите Enter ключ.
=SUBTOTAL(9,C2:C13)
Примечание:
С этого момента, когда вы фильтруете данные столбца, функция ПРОМЕЖУТОЧНЫЙ ИТОГ суммирует только видимые ячейки, как показано на скриншоте ниже.
Подытог только видимые ячейки после фильтрации с помощью удивительного инструмента
Здесь рекомендую СУМВИДИМЫЕ ячейки Функция Kutools for Excel для вас. С помощью этой функции вы можете легко суммировать только видимые ячейки в определенном диапазоне несколькими щелчками мыши.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1. Выберите пустую ячейку для вывода результата, щелкните Кутулс > Kutools Функции > Статистический И математика > НЕВЕРОЯТНО. Смотрите скриншот:
2. в Аргументы функций в диалоговом окне выберите диапазон, в котором будет произведен промежуточный итог, а затем нажмите кнопку OK кнопку.
Затем вы увидите, что формула создана в выбранной ячейке. Когда вы фильтруете данные столбца, суммируются только видимые ячейки.
Если вы хотите получить бесплатную (30-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Статьи по теме
Удалять только видимые строки или столбцы в Excel
В Excel мы обычно скрываем некоторые важные строки или столбцы информации, иногда нам нужно удалить только видимые строки или столбцы, если вы удалите их напрямую с помощью клавиши Delete, скрытые значения будут удалены одновременно. Как удалить видимые строки или столбцы, не удаляя скрытые строки или столбцы в Excel? В этом руководстве будут представлены способы удаления только видимых строк.
Суммировать значения без промежуточных итогов или исключить их в Excel
Предположим, у вас есть список данных, смешанных с несколькими ячейками промежуточных итогов, когда вам нужно суммировать итоги, все промежуточные итоги включаются в окончательное суммирование. Смотрите скриншот ниже. Что бы вы сделали для суммирования значений без промежуточных итогов в Excel? В этой статье мы покажем вам быстрый способ этого добиться.
Одновременная фильтрация нескольких столбцов в Excel
Когда вы применяете функцию «Фильтр», после фильтрации одного столбца следующие столбцы будут фильтроваться только на основе результата предыдущего отфильтрованного столбца. Это означает, что только критерий И может применяться к более чем одному столбцу. Как в этом случае применить критерии И и ИЛИ для одновременной фильтрации нескольких столбцов на листе Excel? Ваш метод в этой статье.
Отфильтровать или выбрать ячейки по цвету ячеек в Excel
Обычно вы можете заливать ячейки разными цветами для любых целей в Excel. Если у вас есть рабочий лист с использованием разных цветов для обозначения разного типа содержимого, и вы хотите отфильтровать или выбрать эти ячейки по цвету ячеек, вы можете сделать это с помощью методов, описанных в этом руководстве:
Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Excel 2010 Excel 2007 Excel for Mac 2011 Excel Starter 2010 More. Less
This article describes the formula syntax and usage of the SUBTOTAL function in Microsoft Excel.
Description
Returns a subtotal in a list or database. It is generally easier to create a list with subtotals by using the Subtotal command in the Outline group on the Data tab in the Excel desktop application. Once the subtotal list is created, you can modify it by editing the SUBTOTAL function.
Syntax
The SUBTOTAL function syntax has the following arguments:
Function_num Required. The number 1-11 or 101-111 that specifies the function to use for the subtotal. 1-11 includes manually-hidden rows, while 101-111 excludes them; filtered-out cells are always excluded.
Function_num
(includes hidden rows)
Function_num
(ignores hidden rows)
Ref1 Required. The first named range or reference for which you want the subtotal.
Ref2. Optional. Named ranges or references 2 to 254 for which you want the subtotal.
Remarks
If there are other subtotals within ref1, ref2,… (or nested subtotals), these nested subtotals are ignored to avoid double counting.
For the function_num constants from 1 to 11, the SUBTOTAL function includes the values of rows hidden by the Hide Rows command under the Hide & Unhide submenu of the Format command in the Cells group on the Home tab in the Excel desktop application. Use these constants when you want to subtotal hidden and nonhidden numbers in a list. For the function_Num constants from 101 to 111, the SUBTOTAL function ignores values of rows hidden by the Hide Rows command. Use these constants when you want to subtotal only nonhidden numbers in a list.
The SUBTOTAL function ignores any rows that are not included in the result of a filter, no matter which function_num value you use.
The SUBTOTAL function is designed for columns of data, or vertical ranges. It is not designed for rows of data, or horizontal ranges. For example, when you subtotal a horizontal range using a function_num of 101 or greater, such as SUBTOTAL(109,B2:G2), hiding a column does not affect the subtotal. But, hiding a row in a subtotal of a vertical range does affect the subtotal.
Example
Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
В этой статье описаны синтаксис формулы и использование функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Microsoft Excel.
Описание
Возвращает промежуточный итог в список или базу данных. Обычно проще создать список с промежуточными итогами, используя в настольном приложении Excel команду Промежуточные итоги в группе Структура на вкладке Данные. Но если такой список уже создан, его можно модифицировать, изменив формулу с функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ.
Синтаксис
Аргументы функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ описаны ниже.
Номер_функции — обязательный аргумент. Число от 1 до 11 или от 101 до 111, которое обозначает функцию, используемую для расчета промежуточных итогов. Функции с 1 по 11 учитывают строки, скрытые вручную, в то время как функции с 101 по 111 пропускают такие строки; отфильтрованные ячейки всегда исключаются.
Function_num
(включая скрытые строки)
Function_num
(игнорирует скрытые строки)
Ссылка1 Обязательный. Первый именованный диапазон или ссылка, для которых требуется вычислить промежуточные итоги.
Ссылка2;. Необязательный. Именованные диапазоны или ссылки 2—254, для которых требуется вычислить промежуточные итоги.
Примечания
Если уже имеются формулы подведения итогов внутри аргументов "ссылка1;ссылка2;. " (вложенные итоги), эти вложенные итоги игнорируются, чтобы избежать двойного суммирования.
Для констант "номер_функции" от 1 до 11 функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ учитывает значения строк, скрытых с помощью команды Скрыть строки (меню Формат, подменю Скрыть или отобразить) в группе Ячейки на вкладке Главная в настольном приложении Excel. Эти константы используются для получения промежуточных итогов с учетом скрытых и нескрытых чисел списка. Для констант "номер_функции" от 101 до 111 функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ исключает значения строк, скрытых с помощью команды Скрыть строки. Эти константы используются для получения промежуточных итогов с учетом только нескрытых чисел списка.
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ исключает все строки, не включенные в результат фильтра, независимо от используемого значения константы "номер_функции".
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ применяется к столбцам данных или вертикальным наборам данных. Она не предназначена для строк данных или горизонтальных наборов данных. Так, при определении промежуточных итогов горизонтального набора данных с помощью значения константы "номер_функции" от 101 и выше (например, ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;B2:G2)), скрытие столбца не повлияет на результат. Однако на него повлияет скрытие строки при подведении промежуточного итога для вертикального набора данных.
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу Enter. При необходимости измените ширину столбцов, чтобы видеть все данные.
Важно: В Excel не поддерживаются Excel. Если вы работаете с Excel таблицей, команда "Excel", будет неавтоматна. Чтобы добавить в таблицу подытую, необходимо сначала преобразовать таблицу в обычный диапазон данных, а затем добавить подытую. Обратите внимание, что в этом случае будут исключены все функции таблицы, кроме форматирования таблицы. Вы также можете создать сетную.
При вставке подытов:
Промежуточные итоги вычисляются с помощью итоговая функция, например Sum или Average,с помощью функции SUBTOTAL. Для каждого столбца можно отобразить несколько типов итогов.
Общие итоги получаются из детали, а не из значений в сумме. Например, если вы используете суммарную функцию Среднее, в строке итогов отображается среднее значение всех строк с подробными данными в списке, а не среднее значение в строках итогов.
Если в книге настроено автоматическое пересчет формул, при редактировании подробных данных команда Итоги автоматически пересчитывает итоговые и общие значения. Команда "Подытая" также определяет структуру списка, чтобы можно было отобразить и скрыть строки с подробными подробностями для каждого из них.
Примечание: При фильтрации данных, которые содержат подытог, они могут отображаться скрытыми. Чтобы снова отобразить их, очистка всех фильтров Дополнительные сведения о применении фильтров см. в кратком запуске. Фильтрация данных с помощью автофильтра.
Убедитесь, что каждый столбец в диапазон данных, для которого вычисляются подытогов, имеет метку в первой строке, содержит похожие факты в каждом столбце и что в диапазоне нет пустых строк или столбцов.
Выделите ячейку в диапазоне.
Выполните одно из указанных ниже действий.
Вставка одного уровня подытогов
Вы можете вставить один уровень подытогов для группы данных, как показано в примере ниже.
1. При каждом изменении в столбце Спорт.
2. . подытожите столбец "Продажи".
Для сортировки столбца, содержащего данные, по которым требуется выполнить группировку, выделите этот столбец и на вкладке Данные в группе Сортировка и фильтр нажмите кнопку Сортировка от А до Я или Сортировка от Я до А.
На вкладке Данные в группе Структуру нажмите кнопку Подытая.
Отобразилось диалоговое окно "Подытая".
В поле При каждом изменении выберите столбец для подытверха. Например, в примере выше нужно выбрать Sport.
В поле Использовать функцию выберите суммарную функцию, которую вы хотите использовать для вычисления итогов. Например, в примере выше нужно выбрать Sum.
В поле Добавить подытожную сумму в выберите каждый столбец со значениями, которые вы хотите выложить. Например, в примере выше нужно выбрать Продажи.
Если вы хотите, чтобы после каждого из подытов был автоматический разрыв страницы, выберите разрыв страницы между группами.
Чтобы указать итоговую строку над строкой с подробными данными, разо убедитесь в том, что под данными не указаны данные. Чтобы указать итоговую строку под строкой сведений, выберите поле Итоги под данными. Например, в примере выше этот квадрат можно было бы очистить.
При желании вы можете использовать команду Итоги еще раз, повторив шаги с 1 по 7, чтобы добавить дополнительные итоги с различными суммарными функциями. Чтобы не переописывать существующие подытоговые, разместим поле Заменить текущие.
Вставка вложенных уровней подытогов
Вы можете вставить подытогов для внутренних вложенных групп в соответствующие внешние группы, как показано в примере ниже.
1. При каждом изменении внешнего столбца Регион.
2. . вы можете вычитать для каждого изменения внутренний столбец Sport и столбец Sales для этого региона.
Для сортировки столбца, содержащего данные, по которым требуется выполнить группировку, выделите этот столбец и на вкладке Данные в группе Сортировка и фильтр нажмите кнопку Сортировка от А до Я или Сортировка от Я до А.
Вставка внешних подытогов.
Вставка внешних подытогов
На вкладке Данные в группе Структуру нажмите кнопку Подытая.
Отобразилось диалоговое окно "Подытая".
В поле При каждом изменении выберите столбец для внешних подытогов. Например, в примере выше нужно щелкнуть Регион.
В поле Использовать функцию выберите суммарную функцию, которую вы хотите использовать для вычисления итогов. Например, в примере выше нужно выбрать Sum.
В поле Добавить подытожную сумму в выберите каждый столбец со значениями, которые вы хотите выложить. В примере выше нужно выбрать Продажи.
Если вы хотите, чтобы после каждого из подытов был автоматический разрыв страницы, выберите разрыв страницы между группами.
Чтобы указать итоговую строку над строкой с подробными данными, разо убедитесь в том, что под данными не указаны данные. Чтобы указать итоговую строку под строкой сведений, выберите поле Итоги под данными. Например, в примере выше этот квадрат можно было бы очистить.
При желании вы можете использовать команду Итоги еще раз, повторив шаги 1–6, чтобы добавить дополнительные итоги с различными суммарными функциями. Чтобы не переописывать существующие подытоговые, разместим поле Заменить текущие.
Вставка вложенных подытов.
Вставка вложенных подытов
На вкладке Данные в группе Структуру нажмите кнопку Подытая.
Отобразилось диалоговое окно "Подытая".
В поле При каждом изменении в щелкните вложенный столбец подытая. Например, в примере выше нужно выбрать Sport.
В поле Использовать функцию выберите суммарную функцию, которую вы хотите использовать для вычисления итогов. Например, в примере выше нужно выбрать Sum.
Выберите другие нужные параметры.
Чтобы заменить текущие подытки, сберем.
Повторите предыдущий шаг для дополнительных вложенных подытогов, работая с внешними.
Совет: Чтобы отобразить сводку только итогов и общих итогов, щелкните символы рядом с номерами строк. Используйте символы и , чтобы отобразить или скрыть строки с подробными данными для отдельных подытов.
Выберем ячейку в диапазоне с подытоговами.
На вкладке Данные в группе Структуру нажмите кнопку Подытая.
В диалоговом окне Subtotal (Подытая) нажмите кнопку Remove All (Удалить все).
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Если ваша рабочая таблица 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, выделите любую ячейку в наборе данных и выберите « Данные» > « Итог» . Затем сделайте свой выбор в диалоговом окне Промежуточный итог.
Читайте также: