Эксель промежуточные итоги формула: Промежуточные итоги в Excel с примерами функций

Содержание

Группы и промежуточные итоги в Excel

Листы Excel, содержащие большой объем информации, иногда могут выглядеть перегруженными и даже трудночитаемыми. Excel позволяет группировать данные, чтобы с легкостью скрывать и показывать различные разделы листа. К тому же Вы можете обобщить различные группы при помощи команды Промежуточный итог и придать структуру рабочему листу Excel. В этом уроке мы рассмотрим оба этих инструмента максимально подробно и на наглядных примерах.

Группировка строк и столбцов в Excel

Выделите строки или столбцы, которые необходимо сгруппировать. В следующем примере мы выделим столбцы A, B и C.
Откройте вкладку Данные на Ленте, затем нажмите команду Группировать.
Выделенные строки или столбцы будут сгруппированы. В нашем примере это столбцы A, B и C.

Чтобы разгруппировать данные в Excel, выделите сгруппированные строки или столбцы, а затем щелкните команду Разгруппировать.

Как скрыть и показать группы

Чтобы скрыть группу в Excel, нажмите иконку Скрыть детали (минус).
Группа будет скрыта. Чтобы показать скрытую группу, нажмите иконку Показать детали (плюс).

Подведение итогов в Excel

Команда Промежуточный итог позволяет автоматически создавать группы и использовать базовые функции, такие как СУММ, СЧЁТ и СРЗНАЧ, чтобы упростить подведение итогов. Например, команда Промежуточный итог способна вычислить стоимость канцтоваров по группам в большом заказе. Команда создаст иерархию групп, также называемую структурой, чтобы упорядочить информацию на листе.

Ваши данные должны быть правильно отсортированы перед использованием команды Промежуточный итог, Вы можете изучить серию уроков Сортировка данных в Excel, для получения дополнительной информации.

Создание промежуточного итога

В следующем примере мы воспользуемся командой

Промежуточный итог, чтобы определить сколько заказано футболок каждого размера (S, M, L и XL).

В результате рабочий лист Excel обретет структуру в виде групп по каждому размеру футболок, а затем будет подсчитано общее количество футболок в каждой группе.

Прежде всего отсортируйте данные, для которых требуется подвести итог. В этом примере мы подводим промежуточный итог для каждого размера футболок, поэтому информация на листе Excel должна быть отсортирована по столбцу Размер от меньшего к большему.
Откройте вкладку Данные, затем нажмите команду Промежуточный итог.
Откроется диалоговое окно Промежуточные итоги. Из раскрывающегося списка в поле При каждом изменении в, выберите столбец, который необходимо подытожить. В нашем случае это столбец Размер.
Нажмите на кнопку со стрелкой в поле Операция, чтобы выбрать тип используемой функции. Мы выберем Количество, чтобы подсчитать количество футболок, заказанных для каждого размера.
В поле Добавить итоги по выберите столбец, в который необходимо вывести итог. В нашем примере это столбец Размер.
Если все параметры заданы правильно, нажмите ОК.
Информация на листе будет сгруппирована, а под каждой группой появятся промежуточные итоги. В нашем случае данные сгруппированы по размеру футболок, а количество заказанных футболок для каждого размера указано под соответствующей группой.

Просмотр групп по уровням

При подведении промежуточных итогов в Excel рабочий лист разбивается на различные уровни. Вы можете переключаться между этими уровнями, чтобы иметь возможность регулировать количество отображаемой информации, используя иконки структуры 1, 2, 3 в левой части листа. В следующем примере мы переключимся между всеми тремя уровнями структуры.

Хоть в этом примере представлено всего три уровня, Excel позволяет создавать до 8 уровней вложенности.

Щелкните нижний уровень, чтобы отобразить минимальное количество информации. Мы выберем уровень 1, который содержит только общее количество заказанных футболок.
Щелкните следующий уровень, чтобы отобразить более подробную информацию. В нашем примере мы выберем уровень 2, который содержит все строки с итогами, но скрывает остальные данные на листе.
Щелкните наивысший уровень, чтобы развернуть все данные на листе. В нашем случае это уровень 3.

Вы также можете воспользоваться иконками Показать или Скрыть детали, чтобы скрыть или отобразить группы.

Удаление промежуточных итогов в Excel

Со временем необходимость в промежуточных итогах пропадает, особенно, когда требуется иначе перегруппировать данные на листе Excel. Если Вы более не хотите видеть промежуточные итоги, их можно удалить.

Откройте вкладку Данные, затем нажмите команду Промежуточный итог.
Откроется диалоговое окно Промежуточные итоги. Нажмите Убрать все.
Все данные будут разгруппированы, а итоги удалены.

Чтобы удалить только группы, оставив промежуточные итоги, воспользуйтесь пунктом Удалить структуру из выпадающего меню команды Разгруппировать.

Оцените качество статьи. Нам важно ваше мнение:

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ

Возвращает промежуточный итог в список или базу данных. Обычно проще создать список с промежуточными итогами, используя команду

Итоги в меню Данные. Но если список с промежуточными итогами уже создан, его можно модифицировать, редактируя формулу с функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ.

Синтаксис

ПРОМЕЖУТОЧНЫЕ. ИТОГИ(номер_функции; ссылка1; ссылка2;…)

Номер_функции — это число от 1 до 11, которое указывает, какую функцию использовать при вычислении итогов внутри списка.

Нр. ф-и	Функция	Описание функции
1	СРЗНАЧ	Возвращает среднее (арифметическое) своих аргументов.
2	СЧЁТ	Подсчитывает количество чисел в списке аргументов. Функция СЧЁТ используется для получения количества числовых ячеек в интервалах или массивах ячеек.
3	СЧЁТЗ	Подсчитывает количество непустых значений в списке аргументов. Функция СЧЁТЗ используется для подсчета количества ячеек с данными в интервале или массиве.
4	МАКС	Возвращает наибольшее значение из набора значений.
5	МИН	Возвращает наименьшее значение в списке аргументов.
6	ПРОИЗВЕД	Перемножает числа, заданные в качестве аргументов и возвращает их произведение.
7	СТАНД ОТКЛОН	Оценивает стандартное отклонение по выборке. Стандартное отклонение — это мера того, насколько широко разбросаны точки данных относительно их среднего.
8	СТАНД ОТКЛОНП	Вычисляет стандартное отклонение по генеральной совокупности. Стандартное отклонение — это мера того, насколько широко разбросаны точки данных относительно их среднего.
9	СУММ	Суммирует все числа в интервале ячеек.
10	ДИСП	Оценивает дисперсию по выборке
11	ДИСПР	Вычисляет дисперсию для генеральной совокупности

Ссылка1; Ссылка2; — от 1 до 29 интервалов или ссылок, для которых подводятся итоги.

Внимание!

Если уже имеются формулы подведения итогов внутри аргументов ссылка1;ссылка2;… (вложенные итоги), то эти вложенные итоги игнорируются, чтобы избежать двойного суммирования.

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ игнорирует все скрытые строки, которые получаются в результате фильтрации списка. Это важно в том случае, когда нужно подвести итоги только для видимых данных, которые получаются в результате фильтрации списка.

Если среди ссылок есть трехмерные ссылки, функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ возвращает значение ошибки #ЗНАЧ!.

Трехмерные ссылки используются при необходимости анализа данных из одной и той же ячейки или диапазона ячеек на нескольких листах одной книги. Трехмерная ссылка включает в себя ссылку на ячейку или диапазон, перед которой ставятся имена листов. Microsoft Excel использует все листы, хранящиеся между начальным и конечным именами, указанными в ссылке. Например, формула =СУММ(Лист2:Лист13!B5) суммирует все значения, содержащиеся в ячейке B5 на всех листах в диапазоне от Лист2 до Лист13 включительно.

Пример

Свойство функции игнорировать скрытые строки оказалось очень удобным для оперативного анализа данных с использованием фильтра.

Допустим, имеем какой то учет арсенала для борьбы с «захватчиками». Создаем таблицу. Если таблица большая, для удобства, чтобы каждый раз не лазить в конец таблицы, итоговую строку можно расположить над шапкой, а саму шапку закрепить как область.

Теперь, чтобы посчитать сколько у нас в запасе тех или иных наименований, достаточно их отобрать по фильтру.

Такой оперативный анализ очень полезен техническим исполнителя. Представьте, Вы спокойно сидите перед своим персональным компьютером, на своем рабочем месте и мирно занимаетесь своими личными делами. И вдруг, звонок начальника: «а скажи-ка мне Тяпкин-Ляпкин, скока у нас того-то и того-то»? Пока начальник выговаривает, что ему надо, Вы быстренько запускаете файл с базой данных и по фильтру отбираете то, что интересует руководство. Ответ готов, начальник доволен, товарищи по работе в шоке, а Вы опять спокойно занимаетесь своими делами.

Понятно, что для такого результата нужна эта самая база данных, а ее создание и поддержание в актуальном состоянии зависит от Вашего профессионализма. Поверьте мне, никто и никогда не будет Вам рассказывать, как достигнуть результата, всех интересует только результат, поэтому постоянно думайте над тем как облегчить и автоматизировать свою работу при помощи Excel, как минимизировать ошибки и Ваш профессионализм достигнет небывалых высот.

Формула промежуточные итоги в Excel — Группы и промежуточные итоги в Excel

Команда Данные→Структура →Промежуточный итог представляет собой удобный механизм автоматической вставки формул в базу данных рабочего листа. В ней используется функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Чтобы реализовать этот механизм, нужно сортировать записи каждый раз при изменении значений в определенных столбцах.

Примечание

Когда выделена таблица, команда Данные→Структура→Промежуточный итог недоступна. Таким образом, этот раздел применим только при работе с базами данных рабочего листа. Если данные находятся в таблице и нужно автоматически вставить промежуточные итоги, преобразуйте таблицу в диапазон, воспользовавшись командой Работа с таблицами→Конструктор→Сервис→Преобразовать в диапазон. После вставки промежуточных итогов можно снова преобразовать диапазон в таблицу с помощью команды Вставка→Таблицы→Таблица.

На рисунке показан пример диапазона, подходящего для вычисления промежуточных итогов. Эта база данных отсортирована по столбцам Месяц и Регион.

Чтобы вставить на рабочий лист формулы, подсчитывающие промежуточные итоги, установите курсор ячейки в любом месте базы данных и выберите команду Данные→Структура→Промежуточный итог. Откроется диалоговое окно, показанное на рисунке.

Диалоговое окно Промежуточные итоги содержит следующие элементы.

• При каждом изменении в. В этом раскрывающемся списке отображаются все столбцы, имеющиеся в базе данных. Весь список должен быть отсортирован по выбранному полю.

• Операция. Раскрывающийся список предлагает выбрать одну из 11 доступных функций (по умолчанию Excel предлагает функцию СУММ).

• Добавить итоги по. В этом списке перечислены все существующие столбцы. Установите флажок рядом со столбцами, для которых должны вычисляться промежуточные итоги.

• Заменить текущие итоги. Если этот флажок установлен, Excel будет удалять любые существующие формулы промежуточных итогов, заменяя их новыми.

• Конец страницы между группами. Если этот флажок установлен, после каждого промежуточного итога Excel будет вставлять разрыв страницы.

• Итоги под данными. Если этот флажок установлен, Excel по умолчанию помещает промежуточные итоги под самими данными. В противном случае формулы промежуточных итогов размещаются выше данных.

• Убрать все. Данная кнопка позволяет удалить из списка все формулы промежуточных итогов.

Щелкните на ОК, и Excel начнет анализировать записи, вставляя определенные формулы и создавая уникальную схему промежуточных итогов. На рисунке показан рабочий лист после добавления двух наборов промежуточных итогов, один из которых суммирует значения по месяцам, другой — по регионам. Естественно, функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ можно использовать в формулах, создаваемых вручную, однако применение команды Данные→Структура→Промежуточный итог существенно упрощает задачу.

Предупреждение

После добавления промежуточных итогов к отфильтрованному списку и удаления фильтра итоги перестают быть правильными.

При создании дополнительных промежуточных итогов не устанавливайте в диалоговом окне флажок Заменить текущие итоги.

Во всех формулах на этом рабочем листе используется функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Например, формула подсчета продаж в январе выглядит следующим образом:

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;Е2:Е4)

Если формула обращается к двум ячейкам, которые тоже содержат формулу ПРОМЕЖУТОЧНЫЕ.ИТОГИ, во избежание двойного подсчета эти ячейки в расчет не включаются.

Чтобы установить уровень детализации отображаемых значений, воспользуйтесь элементами управления схемой итогов. Например, на рисунке ниже показаны только строки с суммами, которые содержат функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ.

Примечание

В большинстве случаев для обобщения данных лучше применять не функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ, а сводные таблицы. Они более гибкие и не требуют разработки формул. Функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ рекомендуется применять только для решения простых задач.

В начало

Полезное

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ — ONLYOFFICE

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ — это одна из математических и тригонометрических функций. Возвращает промежуточный итог в список или базу данных.

Синтаксис функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ:

ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер_функции;список_аргументов)

где

номер_функции — числовое значение, которое обозначает функцию, используемую для расчета промежуточных итогов. Допустимые значения приведены в таблице ниже. При использовании аргументов номер_функции от 1 до 11 функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ учитывает значения строк, которые были скрыты вручную. При использовании аргументов номер_функции от 101 до 111 функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ не учитывает значения строк, которые были скрыты вручную. Значения, скрытые фильтром, исключаются всегда.

список_аргументов — ссылка на диапазон ячеек, содержащих значения, для которых требуется вычислить промежуточные итоги.

Чтобы применить функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ,

выделите ячейку, в которой требуется отобразить результат,
щелкните по значку Вставить функцию , расположенному на верхней панели инструментов,
или щелкните правой кнопкой мыши по выделенной ячейке и выберите в меню команду Вставить функцию,
или щелкните по значку перед строкой формул,
выберите из списка группу функций Математические,
щелкните по функции ПРОМЕЖУТОЧНЫЕ. ИТОГИ,
введите требуемые аргументы через точку с запятой,
нажмите клавишу Enter.

Результат будет отображен в выбранной ячейке.

На следующем изображении показан результат, возвращаемый функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ, если несколько строк скрыто.

Вернуться на предыдущую страницу

Промежуточные итоги в Excel — Excel works!

Исправляем ошибки в Excel при расчетах Формула массива Excel. Как пользоваться?

Если у вас есть таблица данных, то посчитать сумму по столбцу можно обычной функцией =СУММ(). Вы, наверное, замечали, что в случае отбора фильтром определенных значений такая функция работает неверно. Как посчитать сумму только отобранных значений? Для этого есть возможность создать промежуточные итоги в Excel.

Функция промежуточные итоги в Excel имеет 2 аргумента:

1. Номер_функции. Это функция, которой нужно воспользоваться для расчета итогов.

2. Ссылка. Это тот диапазон, по которому нужно найти итог.

Пример. Посчитайте сумму по столбцу Сумма для всех месяцев, где продано больше 20 единиц.

Как вы видите на первой картинке, мы отобрали фильтром все значение по столбцу «Продано единиц» большие 20. В ячейке С15 записываем формулу вида

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;C2:C13)

Значение функции будет меняться, если изменить условия фильтра.

Пример

Прошу не забывать, что это же действие можно сделать функцией =СУММЕСЛИ()

Промежуточные итоги в Excel, сумма только видимых ячеек

Как посчитать только те ячейки, которые вы не делали скрытыми?

Интересно, что в списке номеров функций для формулы промежуточных итогов, как на картинке выше, нет интересной функции. Чтобы подсчитать сумму всех видимых ячеек необходимо воспользоваться кодом 109.

Промежуточные итоги в Excel.

Создать таблицу на отдельном листе

т.е. с промежуточными итогами по каждой группе строк, например, по полугодиям в нашем примере.

Что нужно сделать:

1. Убедитесь, что данные отсортированы, хотя бы по одному столбцу

2. Выберите диапазон

3. Зайдите в ленте инструментов в меню Данные, раздел Структура и выберите Промежуточные итоги

В открывшемся меню делаем настройки, выбираем по какому столбцу сортироваться (Полугодие) и что делать (Операция), а так же в каком столбце проводить расчет:

Должно получиться как на рисунке выше.

Пример2

Очень удобно, когда таблица большая и нужно отсортировать по номенклатуре, кварталу, счету и т.д.

Важно отметить:

— при таком способе расчета итогов Excel группирует данные (плюсы слева на панели), т.е. вы можете скрывать лишние данные. Подробнее здесь

— если используете возможность Таблица (Вставка -Таблица), в эту интерактивную таблицу при расчете суммы автоматом добавляется «Строка итогов», которая во многом заменяет промежуточные итоги. В открывшейся вкладке Работа с таблицами – Конструктор вы можете настроить интерактивную таблицу почти под любые условия. Я пока почти не использую эту возможность, т.к. такая таблица даже на 10 тысяч строк может значительно грузить систему. Хотя в Excel я работаю до 500 тыс. строк без проблем, даже без PowerPivot.

Поделитесь нашей статьей в ваших соцсетях:

Функция эксель промежуточные итоги

Особенность функции состоит в том, что она предназначена для использования совместно с другими средствами EXCEL: Автофильтром и Промежуточными итогами. См. Файл примера .

Синтаксис функции

ПРОМЕЖУТОЧНЫЕ.ИТОГИ( номер_функции; ссылка1;ссылка2;. ))

Номер_функции — это число от 1 до 11, которое указывает какую функцию использовать при вычислении итогов внутри списка.

Номер_функции (включая скрытые значения)	Номер_функции (за исключением скрытых значений)	Функция
1	101	СРЗНАЧ
2	102	СЧЁТ
3	103	СЧЁТЗ
4	104	МАКС
5	105	МИН
6	106	ПРОИЗВЕД
7	107	СТАНДОТКЛОН
8	108	СТАНДОТКЛОНП
9	109	СУММ
10	110	ДИСП
11	111	ДИСПР

Например, функция СУММ() имеет код 9. Функция СУММ() также имеет код 109, т.е. можно записать формулу = ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;A2:A10) или = ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;A2:A10). В чем различие – читайте ниже. Обычно используют коды функций от 1 до 11.

Ссылка1; Ссылка2; — от 1 до 29 ссылок на диапазон, для которых подводятся итоги (обычно используется один диапазон).

Если уже имеются формулы подведения итогов внутри аргументов ссылка1;ссылка2;. (вложенные итоги), то эти вложенные итоги игнорируются, чтобы избежать двойного суммирования.

Важно: Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() разработана для столбцов данных или вертикальных наборов данных. Она не предназначена для строк данных или горизонтальных наборов данных (ее использование в этом случае может приводить к непредсказуемым результатам).

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() и Автофильтр

Пусть имеется исходная таблица.

Применим Автофильтр и отберем только строки с товаром Товар1. Пусть функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() подсчитает сумму товаров Товар1, следовательно будем использовать код функции 9 или 109.

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() исключает все строки не включенные в результат фильтра независимо от используемого значения константы номер_функции и, в нашем случае, подсчитывает сумму отобранных значений (сумму цен товара Товар1).

Если бы мы записали формулу = ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;B11:B20) или = ПРОМЕЖУТОЧНЫЕ.ИТОГИ( 103;B11:B20), то мы бы подсчитали число отобранных фильтром значений (5).

Таким образом, эта функция «чувствует» скрыта ли строка автофильтром или нет. Это свойство используется в статье Автоматическая перенумерация строк при применении фильтра.

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() и Скрытые строки

Пусть имеется та же исходная таблица. Скроем строки с товаром Товар2 через меню Главная/ Ячейки/ Формат/ Скрыть или отобразить или через контекстное меню.

В этом случае имеется разница между использованием кода функции СУММ() : 9 и 109. Функция с кодом 109 «чувствует» скрыта строка или нет. Другими словами для диапазона кодов номер_функции от 101 до 111 функция ПРОМЕЖУТОЧНЫЕ. ИТОГИ() исключает значения строк скрытых при помощи команды Главная/ Ячейки/ Формат/ Скрыть или отобразить . Эти коды используются для получения промежуточных итогов только для не скрытых чисел списка.

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() и средство EXCEL Промежуточные итоги

Пусть имеется также исходная таблица. Создадим структуру с использованием встроенного средства EXCEL – Промежуточные итоги.

Скроем строки с Товар2, нажав на соответствующую кнопку «минус» в структуре.

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() исключает все неотображаемые строки структурой независимо от используемого значения кода номер_функции и, в нашем случае, подсчитывает сумму только товара Товар1. Этот результат аналогичен ситуации с автофильтром.

Другие функции

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() может подсчитать сумму, количество и среднее отобранных значений, а также включает еще 8 других функций (см. синтаксис). Как правило, этик функций вполне достаточно, но иногда требуется расширить возможности функции ПРОМЕЖУТОЧНЫЕ. ИТОГИ() . Рассмотрим пример вычисления среднего геометрического для отобранных автофильтром значений. Функция СРГЕОМ() отсутствует среди списка функций доступных через соответствующие коды, но выход есть.

Воспользуемся той же исходной таблицей.

Применим Автофильтр и отберем только строки с товаром Товар1. Пусть функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() подсчитает среднее геометрическое цен товаров Товар1 (пример не очень жизненный, но он показывает принцип). Будем использовать код функции 3 – подсчет значений.

Для подсчета будем использовать формулу массива (см. файл примера , лист2)

С помощью выражения СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(B10:B19)))-1 в качестве второго аргумента функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ() подается не один диапазон, а несколько (равного числу строк). Если строка скрыта, то вместо цены выводится значение Пустой текст «», которое игнорируется функцией СРГЕОМ() . Таким образом, подсчитывается среднее геометрическое цен товара Товар1.

В этой статье описаны синтаксис формулы и использование функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Microsoft Excel.

Описание

Возвращает промежуточный итог в список или базу данных. Обычно проще создать список с промежуточными итогами, используя в настольном приложении Excel команду Промежуточные итоги в группе Структура на вкладке Данные. Но если такой список уже создан, его можно модифицировать, изменив формулу с функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ.

Синтаксис

Аргументы функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ описаны ниже.

Номер_функции — обязательный аргумент. Число от 1 до 11 или от 101 до 111, которое обозначает функцию, используемую для расчета промежуточных итогов. Функции с 1 по 11 учитывают строки, скрытые вручную, в то время как функции с 101 по 111 пропускают такие строки; отфильтрованные ячейки всегда исключаются.

Номер_функции
(с включением скрытых значений)

Номер_функции
(с исключением скрытых значений)

Ссылка1 Обязательный. Первый именованный диапазон или ссылка, для которых требуется вычислить промежуточные итоги.

Ссылка2;. Необязательный. Именованные диапазоны или ссылки 2—254, для которых требуется вычислить промежуточные итоги.

Примечания

Если уже имеются формулы подведения итогов внутри аргументов «ссылка1;ссылка2;. » (вложенные итоги), эти вложенные итоги игнорируются, чтобы избежать двойного суммирования.

Для констант «номер_функции» от 1 до 11 функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ учитывает значения строк, скрытых с помощью команды Скрыть строки (меню Формат, подменю Скрыть или отобразить) в группе Ячейки на вкладке Главная в настольном приложении Excel. Эти константы используются для получения промежуточных итогов с учетом скрытых и нескрытых чисел списка. Для констант «номер_функции» от 101 до 111 функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ исключает значения строк, скрытых с помощью команды Скрыть строки. Эти константы используются для получения промежуточных итогов с учетом только нескрытых чисел списка.

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ исключает все строки, не включенные в результат фильтра, независимо от используемого значения константы «номер_функции».

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ применяется к столбцам данных или вертикальным наборам данных. Она не предназначена для строк данных или горизонтальных наборов данных. Так, при определении промежуточных итогов горизонтального набора данных с помощью значения константы «номер_функции» от 101 и выше (например, ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;B2:G2)), скрытие столбца не повлияет на результат. Однако на него повлияет скрытие строки при подведении промежуточного итога для вертикального набора данных.

Если среди ссылок есть трехмерные ссылки, функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ возвращает значение ошибки #ЗНАЧ!.

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Excel используется для расчета промежуточных итогов в таблицах (в том числе и базах данных) и возвращает искомое числовое значение (в зависимости от номера требуемой операции, указанного в качестве первого аргумента данной функции, например, 1 – среднее арифметическое диапазона значений, 9 – суммарное значение и т. д.). Чаще всего рассматриваемую функцию применяют для модификации списков с промежуточными итогами, созданных с использованием специальной встроенной команды в Excel.

Примеры использования функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Excel

Функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ целесообразно использовать в случаях, когда таблица содержит большое количество данных, которые могут быть отфильтрованы по одному или нескольким критериям. При этом в результате применения фильтров будет отображена только часть таблицы, данные в которой соответствуют установленному критерию. Однако операции с использованием обычных функций, таких как СУММ, СРЗНАЧ и др. будут производиться над всей изначальной таблицей (то есть с учетом скрытых строк). Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ работает только с отфильтрованными данными.

Суммирование только видимых ячеек в фильтре Excel

Пример 1. В таблице содержатся данные о продажах в магазине музыкальных инструментов электрогитар трех марок различных моделей на протяжении трех дней. Рассчитать промежуточные итоги по продажам гитары марки Ibanez.

Вид исходной таблицы данных:

Используем фильтр для отбора данных, которые относятся к гитарам марки Ibanez. Для этого выделим всю таблицу или просто перейдите курсором на любую ячейку таблицы и воспользуйтесь инструментом «ДАННЫЕ»-«Фильтр». Теперь исходная таблица имеет следующий вид:

Нажмем на раскрывающийся список в столбце B («Марка товара») и установим флажок только напротив названия «Ibanez»:

После нажатия на кнопку «ОК» таблица примет следующий вид:

Как видно, некоторые строки теперь являются скрытыми. Если применить обычную функцию СУММ, будет произведен расчет для всех строк исходной таблицы:

Вместо этого в ячейке C24 будем использовать следующую функцию:

9 – числовое значение, соответствующее использованию функции СУММ для получения промежуточных итогов;
C4:C20 – диапазон ячеек, содержащих данные о стоимости гитар (при этом все другие гитары, кроме марки Ibanez, в расчете не учитываются).

Аналогично выполним расчет для количества проданных гитар и общей выручки («Сумма). В результате получим:

Для сравнения приведем результаты, полученные с использованием обычной функцией СУММ:

Несмотря на то, что часть строк скрыта благодаря использованию фильтра, функция СУММ учитывает все строки в расчете.

Выборочное суммирование ячеек таблицы в Excel

Отключите автофильтр и выделите исходную таблицу данных из первого примера. Теперь воспользуемся инструментом «Промежуточный итог» во вкладке «Данные» на панели инструментов:

В открывшемся диалоговом окне выберем наименование столбца «Дата» в качестве критерия «При каждом изменении в:». Следующей опцией является операция, которая будет проводиться над данными. Выберем «Сумма» для суммирования значений. Критерий «Добавить итоги по:» позволяет выбрать столбцы, для которых будет выполняться операция суммирования. Установим флажки также напротив «Заменить текущие итоги» и «Итоги под данными»:

После нажатия на кнопку «ОК» исходная таблица примет следующий вид:

Полученная таблица имеет инструменты, позволяющие скрывать/отображать части данных и отобразить при необходимости только общий итог. Если выделить любую ячейку, в которой отображаются промежуточные итоги, можно увидеть, что они были рассчитаны с использованием функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ.

Каждая такая функция может быть модифицирована на усмотрение пользователя. Например, так автоматически определена средняя стоимость гитар, проданных за 13.08.2018:

Примеры формул для расчетов промежуточных итогов в таблице Excel

Вид исходной таблицы данных:

Отфильтруем данные с использованием критериев «джинсы» и указанная в условии дата:

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ не содержит встроенных функций для расчета моды и среднего отклонения. Для расчета моды используем следующую формулу (формула массива CTRL+SHIFT+ENTER):

В данном случае функция ПРОМЕЖУТОЧНЫЕ.ИТОГЫ возвращает ссылку на диапазон ячеек, из которого исключены строки, которые не отображаются в связи с использованием фильтров. Функция ЕСЛИ возвращает массив, содержащий числовые значения для отображаемых строк и пустые значения «» для строк, которые не отображены. Функция МОДА игнорирует их при расчете. В результате выполнения формулы получим:

Для расчета среднего отклонения используем похожую формулу:

Как правило, функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ используют для несложных вычислений. 11 функций, предложенных в рамках ее синтаксиса, как правило вполне достаточно для составления отчетов с промежуточными итогами.

Особенности использования функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Excel

Рассматриваемая функция имеет следующую синтаксическую запись:

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ( номер_функции;ссылка1; [ссылка2];…])

номер_функции – обязательный для заполнения аргумент, принимающий числовые значения из диапазонов от 1 до 11 и от 101 до 111, характеризующие номер используемой функции для расчета промежуточных итогов: СРЗНАЧ, СЧЁТ, СЧЁТЗ, МАКС, МИН, ПРОИЗВЕД, СТАНДОТКЛОН, СТАНДОТКЛОНП, СУММ, ДИСП и ДИСПР соответственно. При этом функции, обозначенные числами от 1 до 11, используются тогда, когда в расчет требуется также включить строки, которые были скрыты вручную. Функции, обозначенные числами от 101 до 111, игнорируют скрытые вручную строки при расчетах. Строки, которые были скрыты в связи с применением фильтров, в расчетах не учитываются в любом случае;
ссылка1 – обязательный аргумент, принимающий ссылку на диапазон ячеек с числовыми данными, для которых требуется выполнить расчет промежуточных итогов;
[ссылка2];…] – вторая и последующие ссылки на диапазоны ячеек, для значений которых выполняется расчет промежуточных итогов. Максимальное количество аргументов – 254.

Если в качестве аргументов ссылка1, [ссылка2];…] были переданы диапазоны ячеек, в которые включены ячейки, содержащие промежуточные итоги, полученные с помощью функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ, они учтены не будут чтобы не повлиять на итоговый результат.
В отфильтрованной таблице отображаются только те строки, содержащиеся значения в которых удовлетворяют поставленным условиям (используемым фильтрам). Некоторые строки могут быть скрыты вручную с использованием опции «Скрыть строки». Такие строки также могут быть исключены из результата, возвращаемого функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ, если в качестве ее первого аргумента было указано число из диапазона от 101 до 111.
Основное свойство рассматриваемой функции (выполнение операций только над отфильтрованными данными) применимо только для таблиц, данные в которых фильтруются по строкам, а не по столбцам. Например, при расчете промежуточных итогов в горизонтальной таблице, в которой в результате применения фильтра были скрыты несколько столбцов, функция =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(1;A1:F1) вернет среднее значение для всех величин, находящихся в диапазоне A1:F1, несмотря на то, что некоторые столбцы являются скрытыми.
Если в качестве аргументов ссылка1, [ссылка2];…] были переданы ссылки на диапазоны ячеек, находящиеся на другом листе или в другой книге Excel (такие ссылки называются трехмерными), функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ вернет код ошибки #ЗНАЧ!.

Функция Промежуточные итоги в Excel. Формула, требования к таблице

Промежуточные результаты, которые необходимо получить при составлении отчетов, можно с легкостью рассчитать в программе Excel. Для этого есть достаточно удобная опция, которую мы подробно рассмотрим в данной статье.

Требования, которые предъявляют к таблицам, чтобы получить промежуточные результаты

Функция промежуточных подсчетов в Excel подходит исключительно для определенных типов таблиц. Далее в этом разделе вы узнаете, какие условия должны быть соблюдены, чтобы воспользоваться данной опцией.

В табличке не должно быть пустых ячеек, каждая из них должна обязательно содержать какую-либо информацию.
Шапка должна состоять из одной строчки. Кроме того, ее расположение должно быть корректным: без перескоков и совмещенных ячеек.
Оформление шапки должно быть выполнено строго в верхней строке, в противном случае функция не сработает.
Сама таблица должна быть представлена обычным количеством ячеек, без дополнительных ветвей. Получается, что конструкция таблицы должна состоять строго из прямоугольника.

Если отклониться хоть от одного заявленного требования при попытке воспользоваться функцией «Промежуточные результаты», в ячейке, которая была выбрана для расчетов, появятся ошибки.

Как применяется функция промежуточных итогов

Чтобы найти необходимые значения, нужно воспользоваться соответствующей функцией, которая располагается в верхней части листа Microsoft Excel на верхней панели.

Открываем таблицу, соответствующую требованиям, указанным выше. Далее кликаем по табличной ячейке, из которой и будем находить промежуточный результат. Затем переходим во вкладку «Данные», в разделе «Структура» кликаем на «Промежуточный итог».

В открывшемся окне нам необходимо выбрать один параметр, который и даст промежуточный результат. Для этого в поле «При каждом изменении» необходимо определить цену за единицу товара. Соответственно, значение проставляем «Цена». Затем жмем кнопку «ОК». Обратите внимание, что в поле «Операция» необходимо установить «Сумма», чтобы корректно просчитать промежуточные значения.

После нажатия на кнопку «ОК» в табличке для каждого значения выведется промежуточный итог, показанный на скриншоте ниже.

Если вы попробуете свернуть все строчки с помощью инструмента, установленного слева от таблички, то увидите, что все промежуточные результаты остаются. Именно их вы и находили с помощью приложенной выше инструкции.

Промежуточные итоги в виде формулы

Чтобы не искать необходимый инструмент функции во вкладках панели управления, необходимо воспользоваться опцией «Вставить функцию». Рассмотрим этот способ подробнее.

Открывает табличку, в которой нужно отыскать промежуточные значения. Выбираем ячейку, где и будут выведены промежуточные значения.

Затем нажимаем на кнопку «Вставить функцию». В открывшемся окошке выбираем необходимый инструмент. Для этого в поле «Категория» ищем раздел «Полный алфавитный перечень». Затем в окошке «Выберите функцию» кликаем на «ПРОМЕЖУТОЧНЫЕ.ИТОГИ», жмем на кнопку «ОК».

В следующем окошке «Аргументы функции» выбираем «Номер функции». Прописываем цифру 9, соответствующую нужному нам варианту обработки информации – расчету суммы.

В следующем поле данных «Ссылка», выбираем количество ячеек, в которых следует найти промежуточные итоги. Чтобы не вводить данные вручную, можно просто с помощью курсора выделить диапазон необходимых ячеек, а следом в окошке нажать кнопку «ОК».

В результате в выбранной ячейке мы получаем промежуточный результат, который равен сумме выбранных нами ячеек с прописанными числовыми данными. Использовать функцию можно и без применения «Мастера функций», для этого следует вручную ввести формулу: =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер обработки данных;координаты ячеек).

Применение функции и обработка ячеек вручную

Данный метод предполагает применение функции несколько иным способом. Его использование представлено в алгоритме ниже:

Запустите Эксель и убедитесь в корректности отображения таблицы на листе. Затем выберите ячейку, в которой хотите получить промежуточное значение конкретного значения в табличке. Затем нажмите на кнопку под панелью управления «Вставить функцию».

В появившемся окне выберем категорию «10 недавно использовавшихся функций» и ищем среди них «Промежуточные итоги». Если таковая функция отсутствует, соответственно необходимо прописать другую категорию — «Полный алфавитный перечень».

После появления дополнительного всплывающего окна, где необходимо прописать «Аргументы функций», вносим туда все данные, которые использовались в предыдущем способе. В подобном случае результат операции «Промежуточные итоги» будет выполняться аналогично.

В некоторых случаях, чтобы скрыть все данные, кроме промежуточных по отношению к одному типу значений в ячейке, допускается использовать средство скрытия данных. Однако нужно быть уверенными в том, что код формулы прописан правильно.

Подведем итоги

Выполнение расчетов с нахождением промежуточных итогов с помощью алгоритмов таблицы Excel можно выполнять исключительно с применением определенной функции, однако доступ к ней возможен с помощью различных способов. Главные условия — выполнить все действия по порядку, чтобы не допустить ошибки, и проверить соответствует ли выбранная таблица необходимым требованиям.

Сообщение Функция Промежуточные итоги в Excel. Формула, требования к таблице появились сначала на Информационные технологии.

Как использовать функцию ПРОМЕЖУТОЧНЫЙ ИТОГ Excel

Используйте функцию ПРОМЕЖУТОЧНЫЙ ИТОГ, чтобы получить промежуточный итог в списке или базе данных. Несмотря на название, SUBTOTAL может выполнять множество математических функций, включая AVERAGE, COUNT, MAX и многие другие (полный список см. В таблице ниже). По умолчанию SUBTOTAL исключает значений в строках, скрытых фильтром, что делает SUBTOTAL очень полезным в таблицах Excel.

Функция ПРОМЕЖУТОЧНЫЙ ИТОГ автоматически игнорирует другие формулы ПРОМЕЖУТОЧНЫЙ ИТОГ, существующие в ссылках, чтобы предотвратить двойной подсчет.

Примеры

Ниже приведены несколько примеров SUBTOTAL, сконфигурированных для SUM, COUNT и AVERAGE значений в диапазоне. Обратите внимание, единственное отличие — это значение, используемое для аргумента function_num :

Доступные расчеты

Поведение

SUBTOTAL управляется аргументом function_num , который предоставляется как числовое значение. Доступно 11 функций, каждая с двумя вариантами, как показано в таблице ниже. Обратите внимание, что значения являются «парными» (например,грамм. 1-101, 2-102, 3-103 и т. Д.). Это связано с тем, как SUBTOTAL обрабатывает скрытые вручную строки. Когда function_num находится между 1-11, SUBTOTAL включает ячеек, которые были скрыты вручную. Когда function_num находится между 101-111, SUBTOTAL исключает значений в строках, которые были скрыты вручную.

Функция	Включить скрытые	Игнорировать скрыто
СРЕДНЕЕ	1	101
СЧЕТ	2	102
COUNTA	3	103
МАКС	4	104
МИН	5	105
ПРОДУКТ	6	106
СТАНДОТКЛОН	7	107
STDEVP	8	108
СУММ	9	109
VAR	10	110
VARP	11	111

Примечание. ПРОМЕЖУТОЧНЫЙ ИТОГ всегда игнорирует значения в ячейках, которые скрыты с помощью фильтра.Значения в строках, которые были «отфильтрованы», никогда не включаются, независимо от function_num .

ПРОМЕЖУТОЧНЫЙ ИТОГ в таблицах Excel

Функция ПРОМЕЖУТОЧНЫЙ ИТОГ используется при отображении строки итогов в таблице Excel. Excel автоматически вставляет функцию ПРОМЕЖУТОЧНЫЙ ИТОГ, и вы можете использовать раскрывающееся меню для переключения поведения и отображения максимального, минимального, среднего и т. Д. Причина, по которой Excel использует ПРОМЕЖУТОЧНЫЙ ИТОГ для вычислений в строке Итого таблицы Excel, состоит в том, что ПРОМЕЖУТОЧНЫЙ ИТОГ автоматически исключает строки, скрытые элементами управления фильтром в верхней части таблицы.То есть, когда вы фильтруете строки в таблице со строкой Итого, вы увидите, что вычисления обновляются автоматически с учетом фильтра.

ИТОГО с очертаниями

Excel есть функция промежуточных итогов, которая автоматически вставляет формулы ПРОМЕЖУТОЧНЫЙ ИТОГ в отсортированные данные. Вы можете найти эту функцию в Data> Outline> Subtotal. В формулах ПРОМЕЖУТОЧНОГО ИТОГА, вставленных таким образом, используются стандартные номера функций 1-11. Это позволяет отображать промежуточные итоги, даже если строки скрыты и отображаются при свертывании и раскрытии структуры.

Примечание: хотя функция Outline — это «простой» способ вставки промежуточных итогов в набор данных, сводная таблица — лучший и более гибкий способ анализа данных. Кроме того, сводная таблица будет отделять данные от представления данных, что является наилучшей практикой.

Банкноты

Когда function_num находится между 1-11, SUBTOTAL включает значения, которые скрыты
Когда function_num находится между 101-111, SUBTOTAL исключает значения, которые скрыты
В отфильтрованных списках SUBTOTAL всегда игнорирует значения в скрытых строках, независимо от function_num .
ПРОМЕЖУТОЧНЫЙ ИТОГ игнорирует другие формулы ПРОМЕЖУТОЧНОГО ИТОГА, существующие в ссылках, чтобы предотвратить двойной подсчет.
SUBTOTAL предназначен для работы с вертикальными значениями данных, расположенными вертикально. В горизонтальные диапазоны всегда включаются значения в скрытых столбцах.

Excel ПРОМЕЖУТОЧНЫЙ ИТОГ Примеры функций

Используйте функцию ПРОМЕЖУТОЧНЫЙ ИТОГ для суммирования сумм и исключения отфильтрованных или скрытые строки. Для Excel 2010 и более поздних версий также доступна функция АГРЕГАТ, которая имеет больше параметров и функций.

ПРОМЕЖУТОЧНЫЙ ИТОГ Функция

Используйте функцию ПРОМЕЖУТОЧНЫЙ ИТОГ, чтобы исключить отфильтрованные или скрытые строки при вычислении итога. Вы можете выбрать любую из 11 функций, которые может вычислить SUBTOTAL, например Sum, Average, Count или Max.

В этом видео показано, как использовать ПРОМЕЖУТОЧНЫЙ ИТОГ или более новую функцию АГРЕГАТ для работы с отфильтрованными данными.

Использовать ПРОМЕЖУТОЧНЫЙ ИТОГ с отфильтрованными списками

Чтобы показать разницу между SUBTOTAL и COUNT, на снимке экрана ниже есть список заказов с итогами над списком.Список был отфильтрован, чтобы показать продажи только в Западном регионе.

Над списком общее количество:

В ячейке E1 используется функция СЧЁТ, и хотя видно только 6 заказов, количество заказов отображается как 37.
В ячейке E2 используется функция ПРОМЕЖУТОЧНЫЙ ИТОГ, которая показывает количество только видимых строк.

Итак, если вы работаете со списками, которые будут отфильтрованы, используйте ПРОМЕЖУТОЧНЫЙ ИТОГО вместо основных функций, таких как СУММ, СЧЁТ или СРЕДНЕЕ.В приведенных ниже инструкциях показано, как использовать функцию ПРОМЕЖУТОЧНЫЙ ИТОГ.

Создать быстрый ПРОМЕЖУТОЧНЫЙ ИТОГ

Во-первых, вот быстрый способ создать формулу ПРОМЕЖУТОЧНОГО ИТОГА для суммирования сумм в списке:

Примените фильтр к списку. В этом примере столбец «Регион» отфильтрован по «Западу».
В строке непосредственно под списком выберите ячейку, в которой вы хотите получить общую сумму.
На вкладке «Главная страница» Excel нажмите кнопку «Автосумма»
- ИЛИ на клавиатуре нажмите клавишу Alt и коснитесь клавиши со знаком равенства (Alt + =).
- ИЛИ щелкните стрелку рядом с командой Автосумма и выберите одно из имен других функций, например, СЧЁТ ЧИСЛОВ

Поскольку список отфильтрован, Excel вставляет формулу ПРОМЕЖУТОЧНЫЙ ИТОГ. В начале у него есть 109 , и это говорит Excel, что нужно СУММИРОВАТЬ числа.

= ПРОМЕЖУТОЧНЫЙ ИТОГ (109, tblProducts [удельная стоимость])

ПРИМЕЧАНИЕ : Если список представляет собой отформатированную таблицу Excel, он может автоматически развернуться и поместить формулу в строку «Итого». Чтобы отменить этот шаг, сразу нажмите Ctrl + Z.

ПРОМЕЖУТОЧНЫЙ ИТОГ Аргументов

В скобках для функции ПРОМЕЖУТОЧНЫЙ ИТОГ есть два аргумента, разделенных запятой (или точкой с запятой, в зависимости от ваших региональных настроек). Вот формула, созданная приведенным выше примером автосуммы:

= ПРОМЕЖУТОЧНЫЙ ИТОГ (109, tblProducts [удельная стоимость])

Первый аргумент — это число, которое сообщает Excel , какую итоговую функцию использовать в промежуточной сумме. 109 в этом примере указывает Excel использовать функцию СУММ. Подробнее об этих
См. Полный список сводной функции числа в следующем разделе
Второй аргумент — ссылка на диапазон ячеек , который должен быть вычислен. В этом примере он суммирует числа в столбце «Стоимость единицы» таблицы с именем tblProducts ..

Итого номеров функций

Первый аргумент функции ПРОМЕЖУТОЧНЫЙ ИТОГ — это номер функции, которая указывает, как должна быть рассчитана сумма. Когда вы вводите открывающую скобку для функции ПРОМЕЖУТОЧНЫЙ ИТОГ, появляется раскрывающийся список из 11 функций, перечисленных в алфавитном порядке. См. Полный список в следующем разделе.

Чтобы выбрать функцию из списка, дважды щелкните по ней.

11 функций для промежуточного итога

11 функций перечислены в алфавитном порядке, каждая функция имеет два номера:

один в диапазоне от 1 до 11
и еще в диапазоне от 101 до 111

Функция	Номера
СРЕДНЕЕ	1 или 101
СЧЕТ	2 или 102
COUNTA	3 или 103
МАКС	4 или 104
МИН	5 или 105
ПРОДУКТ	6 или 106
STDV	7 или 107
STDEVP	8 или 108
СУММ	9 или 109
VAR	10 или 110
VARP	11 или 111

Диапазон номеров функций

Существует одно ключевое различие между двумя наборами чисел.

Если строки скрыты фильтром
- функции в обоих диапазонах чисел исключают отфильтрованные ячейки
Если строки скрыты вручную :
- функции в нижнем диапазоне чисел (1-11) будут включать эти ячейки
- в верхнем диапазоне чисел (101-111) будут исключить эти ячейки

Примечание : строки, которые вы форматируете с нулевой высотой , НЕ БУДУТ включены ни в один из типов промежуточных итогов.

Гибкий ПРОМЕЖУТОЧНЫЙ ИТОГ Формула

Вместо ввода номера функции в формулу ПРОМЕЖУТОЧНЫЙ ИТОГ в этом примере показано, как выбрать имя функции из раскрывающегося списка.

Это позволяет быстро увидеть различные результаты, такие как Среднее, Сумма, Мин. Или Макс., Не пытаясь запомнить все номера функций.

Посмотрите это видео, чтобы увидеть, как работает этот метод, и загрузите образец файла (ниже), чтобы попробовать его самостоятельно.

Письменные инструкции находятся в моем блоге Contextures: Изменить функцию Excel с помощью SUBTOTAL

Скачать образец файла

Чтобы увидеть примеры функции ПРОМЕЖУТОЧНЫЙ ИТОГ, загрузите образец книги. Заархивированный файл имеет формат xlsx и не содержит макросов.
Чтобы увидеть пример, в котором вы выбираете функцию из раскрывающегося списка, загрузите книгу Гибкая функция промежуточных итогов. Заархивированный файл имеет формат xlsx и не содержит макросов.

Дополнительные руководства

Перейдите на следующие страницы, чтобы увидеть другие ПРОМЕЖУТОЧНЫЕ примеры:

Для Excel 2010 и более поздних версий также доступна функция АГРЕГАТ. Он похож на ПРОМЕЖУТОЧНЫЙ ИТОГ, но имеет больше опций и функций.

Excel также имеет функцию промежуточных итогов, которая группирует элементы в списке и добавляет одну или несколько строк промежуточных итогов.

Как использовать функцию промежуточных итогов и промежуточных итогов в Excel

Итог: Узнайте, как работает функция ПРОМЕЖУТОЧНЫЙ ИТОГ и как автоматически создавать строки промежуточных итогов в наборе данных с помощью функции «Промежуточные итоги» в Excel.

Уровень квалификации: Средний

Видеоурок

Смотрите на Youtube

Загрузите файл Excel

Вот копия файла, который я использую в видео.

Функция «Промежуточный итог» и «Промежуточный итог» .xlsx (24,9 КБ)

Функция ПРОМЕЖУТОЧНЫЙ ИТОГ

В Excel есть функция ПРОМЕЖУТОЧНЫЙ ИТОГ и функция Промежуточный итог , и это разные вещи. Оба они полезны, и я объясню разницу.Начнем с функции ПРОМЕЖУТОЧНЫЙ ИТОГ.

Одним из наиболее эффективных способов использования функции ПРОМЕЖУТОЧНЫЙ ИТОГ является строка итогов таблицы Excel. Я говорю об этом в этом посте: Объяснение функции ПРОМЕЖУТОЧНЫЙ ИТОГ для итоговой строки таблиц Excel. Но в сегодняшнем посте я хотел бы взглянуть на то, как функция ПРОМЕЖУТОЧНЫЙ ИТОГ используется в наборах данных, которые имеют больше настроек схемы . Эти наборы данных обычно содержат строки, сгруппированные вместе с промежуточными итогами и общей суммой внизу. Вот пример:

Функция ПРОМЕЖУТОЧНЫЙ ИТОГ имеет два аргумента .

Первый аргумент определяет, какой тип вычисления вы ищете. Это может быть среднее значение, сумма, количество, стандартное отклонение и т. Д. Каждому из этих типов вычислений присвоено число в Excel. 1–11 вычисляют на основе все ячейки в заданном диапазоне данных, тогда как 101–111 вычисляют только ячейки , которые видны в диапазоне данных. Для нашего формата структуры наилучшим выбором является 9, что является обозначением суммы всех ячеек в указанном диапазоне, независимо от того, видимы они или нет.

Второй аргумент — это ссылка . Это просто диапазон данных, из которого вы хотите рассчитать промежуточный итог. Вы можете включить более одной ссылки, если хотите, чтобы промежуточный итог включал данные из несмежных диапазонов.

Некоторые преимущества функции ПРОМЕЖУТОЧНЫЙ ИТОГ

Функция ПРОМЕЖУТОЧНОГО ИТОГА не учитывает дважды

Одной из приятных особенностей функции SUBTOTAL является то, что она распознает другие промежуточные итоги в пределах указанного диапазона и исключает их из своего вычисления .Это хорошо, потому что вы не будете дважды считать данные . Если вы использовали функцию СУММ для сложения всех чисел в диапазоне, который включает промежуточный итог, ваш результат будет неверным.

Функция ПРОМЕЖУТОЧНЫЙ ИТОГ экономит время

Если вы привыкли часто использовать функцию СУММ , у вас может возникнуть соблазн написать формулу СУММ, которая просто складывает все промежуточные итоги . Хотя это может быть простой альтернативой, если у вас всего несколько промежуточных итогов в вашем наборе данных, все, что больше, будет стоить вам больше времени , чем просто использование SUBTOTAL.Это особенно верно, если у вас есть десятки или сотни записей промежуточных итогов.

Функция ПРОМЕЖУТОЧНЫЙ ИТОГ автоматически суммирует правильные данные , а также имеет то преимущество, что ее можно легко изменить, если вы хотите увидеть другое вычисление, такое как среднее значение или счет , при этом избегая дублирования из других промежуточных итогов в диапазон данных.

Характеристика промежуточных итогов

Отдельно от функции ПРОМЕЖУТОЧНЫЙ ИТОГ находится функция Промежуточный итог в Excel. Эта функция проста в использовании и идеально подходит для данных, содержащих похожих записей или сгруппированных элементов.

Подготовить данные

Данные должны быть отсортированы по столбцам, для которых вы хотите создать группы и промежуточные итоги. Функция промежуточных итогов НЕ делает этого за вас. Спасибо Элиоту Пауэллу за указание на это в комментариях к видео на YouTube.

Создание групп структуры промежуточных итогов

Чтобы использовать функцию промежуточных итогов, поместите курсор в любое место в наборе данных.Затем щелкните Промежуточный итог в разделе Outline , который находится на вкладке Data . Кнопка Промежуточный итог выглядит так:

Нажмите, чтобы увеличить

Это вызывает окно промежуточных итогов с опциями и вариантами для настройки промежуточных итогов . Эти варианты включают:

Указывает, где должны отображаться промежуточные итоги.
Замена существующих промежуточных итогов.
Добавление разрывов страниц между разделами с промежуточными итогами.
Размещение промежуточных итогов в нескольких столбцах.
И добавление строки итога или итога внизу.

После того, как вы нажмете ОК , будут добавлены строки промежуточных и общих итогов. В этих строках используется функция ПРОМЕЖУТОЧНЫЙ ИТОГ в формулах для вычисления суммы.

Развернуть и свернуть группы строк

Вы заметите, что слева от номеров строк есть некоторые отметки, которые указывают диапазон групп промежуточных итогов , а также возможность свернуть и развернуть эти разделы. Цифровые кнопки выше, которые указывают уровни группировок промежуточных итогов, чтобы вы могли развернуть или уменьшить масштаб, чтобы рассмотреть более или менее подробные сведения.

Бесплатный веб-семинар по обучению работе с электроинструментами

Прямо сейчас я провожу бесплатный обучающий веб-семинар по всем Power Tools в Excel. Сюда входят Power Query, Power Pivot, Power BI, сводные таблицы, макросы и VBA и многое другое.

Он называется The Modern Excel Blueprint . Во время вебинара я объясняю, что это за инструменты и как они могут вписаться в ваш рабочий процесс.

Вы также узнаете, , как стать героем Excel в своей организации. , этой девушкой или парнем, на которого все полагаются при помощи Excel и увлекательных проектах.

Вебинар проводится несколько дней и раз в несколько дней. Пожалуйста, нажмите на ссылку ниже, чтобы зарегистрироваться и сохранить свое место.

Нажмите здесь, чтобы зарегистрироваться на бесплатный вебинар

Заключение

Вы можете подумать, что сводные таблицы имеют во многом те же функциональные возможности , что и эти схемы с промежуточными итогами, и вы будете правы.Я предпочитаю использовать сводные таблицы поверх подобных контуров, но все же приятно знать, как работают эти функции и что можно сделать с помощью функций ПРОМЕЖУТОЧНЫЙ ИТОГ и Промежуточный итог. Надеюсь, этот пост помог в этом.

Сообщите мне, какие вопросы или комментарии у вас есть, оставив запись в разделе комментариев. Я хотел бы получить известие от вас.

Функция промежуточного итога

в Excel — как использовать

Функция промежуточного итога

в Excel — замечательная формула, которую можно использовать для выполнения определенных арифметических и логических операций с определенным диапазоном ячеек.

Microsoft Excel определяет функцию промежуточного итога как «Возвращает промежуточный итог в списке или базе данных».

Формула промежуточного итога принимает два аргумента:
1. Код операции
2. Диапазон ячеек.

В следующем разделе мы увидим синтаксис Subtotal.

Синтаксис функции промежуточного итога:

Функция промежуточного итога может быть записана как:

= промежуточный итог (Operation_Code, Range1)

Здесь ‘ Operation_Code ‘ указывает тип математической операции, которую вы хотите выполнить в указанном диапазоне. ячеек.

Код операции	Название операции	Описание
1	СРЕДНЕЕ	Вычисляет среднее значение указанного диапазона.
2	COUNT	Подсчитывает количество ячеек в указанном диапазоне.
3	COUNTA	Подсчитывает количество непустых ячеек в указанном диапазоне.
4	MAX	Находит наибольшее значение в указанном диапазоне.
5	MIN	Находит наименьшее значение в указанном диапазоне.
6	ПРОДУКТ	Вычисляет произведение ячеек в указанном диапазоне.
7	STDEV	Оценивает стандартное отклонение в указанном диапазоне.
8	STDEVP	Вычисляет стандартное отклонение для генеральной совокупности.
9	SUM	Вычисляет сумму указанного диапазона.
10	VAR	Оценивает отклонение в указанном диапазоне.
11	VARP	Оценивает дисперсию для всей генеральной совокупности.

« Диапазон1 » означает массив ячеек.

Пример функции подытога в Excel:

В приведенном ниже примере я использовал несколько функций подытога.

Формула = ПРОМЕЖУТОЧНЫЙ ИТОГ (9, B2: B11) приводит к сумме (код операции = 9) всех элементов, присутствующих в диапазоне B2: B11.

И = ПРОМЕЖУТОЧНЫЙ ИТОГ (1, B2: B11) приводит к усреднению (код операции = 1) всех элементов, присутствующих в диапазоне B2: B11

Таким же образом = ПРОМЕЖУТОЧНЫЙ ИТОГ (7, B2: B11 ) приводит к стандартному отклонению (код операции = 7) указанного диапазона.

Почему вам следует использовать функцию промежуточных итогов?

Я знаю, что после прочтения функции промежуточных итогов у вас в голове возникнет вопрос, почему я должен использовать функцию промежуточных итогов для выполнения каких-либо математических операций, если у меня уже есть отдельная функция для этого.

Например: вы знаете, что с промежуточным итогом вы можете суммировать указанный диапазон, но Excel уже предоставил вам функцию суммирования для этого. Так почему вы должны использовать промежуточный итог?

Ответ на поставленный выше вопрос прост. Промежуточный итог дает вам две важные возможности по сравнению с традиционными функциями.

1. Он может генерировать результаты динамически: Если вы попытаетесь использовать функцию excel Subtotal для определенного диапазона, вы увидите, что ее результаты генерируются динамически.Вы можете ясно понять это, посмотрев на пример ниже.

В приведенном выше примере промежуточное значение изменяется динамически в соответствии с фильтром.

2. Функция, которая игнорирует скрытые строки: Вы должны знать, что в Excel есть функция «Скрыть строки». Часто, когда в ваших данных есть какие-то нерелевантные записи, вы их скрываете. Но если вы примените к этому диапазону традиционную функцию суммирования, вы не сможете исключить эти скрытые записи. Но функция промежуточных итогов дает вам эту возможность i.е. вы можете подсчитывать или суммировать только видимые записи в диапазоне.

Но для этого необходимо использовать специальные операционные коды в функции промежуточных итогов. И вы получите эти специальные операционные коды, добавив 100 к исходным. Например, средний рабочий код равен 1, поэтому 101 будет операционным кодом для средней функции, исключающей скрытые строки.

Пример:

= промежуточный итог (9, B2: B5) даст вам сумму всех значений (включая скрытые) между B2 и B5.

И = промежуточный итог (109, B2: B5) даст вам сумму всех видимых значений (кроме скрытых) между B2 и B5.

Использование функции промежуточного итога на ленте Excel:

Вы также можете использовать функцию промежуточного итога на ленте Excel, вкладка «Данные»> параметр «Промежуточный итог». Использовать формулу из этого варианта довольно просто. Использование формулы промежуточного итога из этого параметра создает параметры свертывания и развертывания (+/-) в левой части строк. Это упрощает просмотр данных без прокрутки.

Чтобы использовать эту опцию «Промежуточный итог», просто выполните следующие шаги:

1. Выберите свою таблицу и перейдите к «Данные»> «Промежуточный итог».

2. Откроется окно промежуточных итогов. В опции «При каждом изменении в» выберите столбец, на основе которого вы хотите консолидировать результат (в моем примере я использовал столбец «Регион»). Затем выберите соответствующую функцию, которая будет использоваться, и, наконец, отметьте столбец, к которому вы хотите применить формулу.

3.Нажмите кнопку «ОК», и формула будет применена.

Итак, все дело в функции промежуточных итогов в Excel. Не стесняйтесь делиться своими идеями об этой красивой функции.

Рекомендуемая литература

Функция ПРОМЕЖУТОЧНЫЙ ИТОГ в Excel — формула, советы, как использовать

Что такое ПРОМЕЖУТОЧНЫЙ ИТОГ в Excel?

Функция ПРОМЕЖУТОЧНЫЙ ИТОГ в Excel позволяет пользователям создавать группы, а затем выполнять различные другие функции Excel, такие как СУММ, СЧЁТ, СРЕДНИЙ, ПРОИЗВОД, МАКС и т. Д.Таким образом, функция ПРОМЕЖУТОЧНЫЙ ИТОГ в Excel помогает анализировать предоставленные данные.

Формула

ПРОМЕЖУТОЧНЫЙ ИТОГ = (метод, диапазон1, [диапазон2… диапазон_n])

Где метод — это тип промежуточного итога, который вы хотите получить

Диапазон1, диапазон2… диапазон_n — это диапазон ячеек, который вы хотите промежуточный итог

Почему нам нужно использовать ПРОМЕЖУТОЧНЫЙ ИТОГ?

Иногда нам нужны данные, основанные на разных категориях. ПРОМЕЖУТОЧНЫЕ ИТОГИ помогают нам получить итоги нескольких столбцов данных, разбитых на различные категории.

Для примера рассмотрим выпускаемые швейные изделия разных размеров. Функция ПРОМЕЖУТОЧНЫЙ ИТОГ поможет вам получить количество товаров разных размеров на вашем складе.

Чтобы узнать больше, запустите наш бесплатный ускоренный курс по Excel прямо сейчас!

Как использовать функцию ПРОМЕЖУТОЧНЫЙ ИТОГ в Excel?

Если мы хотим использовать функцию ПРОМЕЖУТОЧНЫЙ ИТОГ, необходимо выполнить два шага. Это:

Форматирование и сортировка предоставленных данных Excel.
Применение ПРОМЕЖУТОЧНОГО ИТОГА к таблице.

Давайте разберемся с этой функцией Excel на примере. Мы используем данные, предоставленные производителем одежды. Он производит футболки пяти разных цветов: белого, черного, розового, зеленого и бежевого. Он производит эти футболки семи различных размеров, то есть 2, 4, 6, 8, 10, 12, 14. Соответствующие данные приведены ниже:

Управляющий складом предоставляет случайные данные. Теперь для анализа нам нужно получить общее количество футболок каждого цвета, лежащее на складе.

Шаг 1

Во-первых, нам нужно отсортировать рабочий лист на основе данных, которые нам нужно вычислить. Поскольку нам нужно получить промежуточные итоги футболок по цветам, мы отсортируем их соответственно.

Для этого мы можем использовать функцию СОРТИРОВКИ на вкладке «Данные».

Шаг 2

Следующим шагом будет применение функции ПРОМЕЖУТОЧНЫЙ ИТОГ. Это можно сделать, как показано ниже:

Выберите вкладку «Данные» и нажмите ПРОМЕЖУТОЧНЫЙ ИТОГ.

Когда мы щелкнем по нему, появится диалоговое окно «Промежуточный итог», как показано ниже:

Теперь щелкните стрелку раскрывающегося списка для поля «При каждом изменении:». ” Теперь мы можем выбрать столбец, который хотим подвести итоги. В нашем примере мы выберем Цвет.

Затем нам нужно щелкнуть стрелку раскрывающегося списка для поля «Использовать функцию:». Это поможет нам выбрать функцию, которую мы хотим использовать.Доступно 11 функций. Выбирать нужно в зависимости от наших требований. В нашем примере мы выберем СУММ, чтобы узнать общее количество футболок на каждом складе.

Затем мы переходим к «Добавить промежуточный итог в: поле». Здесь нам нужно выбрать столбец, в котором мы хотим, чтобы вычисленный промежуточный итог отображался. В нашем примере мы выберем Количество единиц на складе I и на складе II.

После этого нам нужно нажать OK, и мы получим следующие результаты:

Как видно на скриншоте выше, промежуточные итоги вставляются в новые строки под каждым Группа.Когда мы создаем промежуточные итоги, наш рабочий лист делится на разные уровни. В зависимости от информации, которую вы хотите отобразить на листе, вы можете переключаться между этими уровнями.

Кнопки уровней в нашем примере — это изображения кнопок для уровней 1, 2, 3, которые можно увидеть в левой части рабочего листа. Теперь предположим, что я просто хочу увидеть все футболки разных цветов, лежащих на складе, мы можем нажать на Уровень 2.

Если мы нажмем на самый высокий уровень (Уровень 3), мы получим все подробности.

Чтобы узнать больше, запустите наш бесплатный ускоренный курс по Excel прямо сейчас!

Советы для функции ПРОМЕЖУТОЧНЫЙ ИТОГ:

Совет № 1

Предположим, мы хотим обеспечить наличие футболок всех цветов всех размеров на любом из складов. Мы можем выполнить следующие шаги:

Шаг 1: Нажмите «Промежуточный итог». Помните, что мы добавляем еще один критерий к нашим текущим данным промежуточных итогов.

Сейчас,

Шаг 2: Выберите СЧЕТЧИК в раскрывающемся меню и Размер в поле «Добавить промежуточный итог в.»После этого снимите флажок« Заменить текущие промежуточные итоги ». После того, как вы нажмете ОК, вы получите следующие данные:

Это поможет нам обеспечить подсчет для разных размеров, и мы сможем отсортировать данные таким образом, чтобы не было повторов.

Совет № 2

Всегда сортируйте данные по столбцам, которые мы будем использовать для промежуточных итогов.

Совет № 3

Помните, что каждый столбец, который мы хотим вычислить, включает метку в первой строке.

Совет № 4

Если вы хотите поместить сводку данных, снимите флажок «Сводка под данными при вставке промежуточного итога».

Бесплатный курс Excel

Посетите наш бесплатный ускоренный курс Excel, чтобы узнать больше о функциях Excel с помощью личного инструктора. Освойте функции Excel, чтобы создавать более сложный финансовый анализ и моделирование для построения успешной карьеры финансового аналитика.

Дополнительные ресурсы

Спасибо, что прочитали руководство CFI по важным функциям Excel! Потратив время на изучение и освоение этих функций, вы значительно ускорите свой финансовый анализ.Чтобы узнать больше, ознакомьтесь с этими дополнительными ресурсами CFI:

Функции Excel для FinanceExcel for Finance Это руководство по Excel для финансов научит 10 основных формул и функций, которые вы должны знать, чтобы стать отличным финансовым аналитиком в Excel. В этом руководстве есть примеры, скриншоты и пошаговые инструкции. В конце загрузите бесплатный шаблон Excel, который включает в себя все финансовые функции, описанные в учебнике.
Расширенный курс формул Excel
Расширенные формулы Excel, которые вы должны знать Расширенные формулы Excel, которые необходимо знать Эти расширенные формулы Excel очень важно знать и будут использовать ваш финансовый анализ навыки на новый уровень.Расширенные функции Excel
Ярлыки Excel для ПК и MacExcel Ярлыки ПК MacExcel Ярлыки — Список наиболее важных и распространенных ярлыков MS Excel для пользователей ПК и Mac, специалистов в области финансов и бухгалтерского учета. Сочетания клавиш ускоряют ваши навыки моделирования и экономят время. Изучите редактирование, форматирование, навигацию, ленту, специальную вставку, манипулирование данными, редактирование формул и ячеек и другие краткие сведения

Excel ПРОМЕЖУТОЧНЫЙ ИТОГ с примерами формул

В руководстве объясняются особенности функции ПРОМЕЖУТОЧНЫЙ ИТОГ в Excel и показано, как использовать формулы промежуточных итогов для суммирования данных в видимых ячейках.

В предыдущей статье мы обсудили автоматический способ вставки промежуточных итогов в Excel с помощью функции «Промежуточные итоги». Сегодня вы узнаете, как самостоятельно писать формулы промежуточных итогов и какие преимущества это дает вам.

Функция промежуточных итогов Excel — синтаксис и использование

Microsoft определяет Excel SUBTOTAL как функцию, которая возвращает промежуточный итог в списке или базе данных. В этом контексте «промежуточный итог» — это не просто суммирование чисел в определенном диапазоне ячеек. В отличие от других функций Excel, которые предназначены только для одной конкретной задачи, SUBTOTAL удивительно универсален — он может выполнять различные арифметические и логические операции, такие как подсчет ячеек, вычисление среднего, поиск минимального или максимального значения и многое другое.

Функция ПРОМЕЖУТОЧНЫЙ ИТОГ доступна во всех версиях Excel 2016, Excel 2013, Excel 2010, Excel 2007 и более ранних.

Синтаксис функции Excel ПРОМЕЖУТОЧНЫЙ ИТОГ следующий:

ПРОМЕЖУТОЧНЫЙ ИТОГ (номер_функции, ссылка1, [ссылка2],…)

Где:

Номер_функции — число, указывающее, какую функцию использовать для промежуточного итога.
Ref1, Ref2,… — одна или несколько ячеек или диапазонов для промежуточного итога. Первый аргумент ref является обязательным, остальные (до 254) необязательны.

Аргумент function_num может принадлежать к одному из следующих наборов:

1–11 игнорировать отфильтрованные ячейки, но включать вручную скрытые строки.
101 — 111 игнорировать все скрытые ячейки — отфильтрованы и скрыты вручную.

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

На самом деле нет необходимости запоминать все номера функций. Как только вы начнете вводить формулу промежуточного итога в ячейку или в строку формул, Microsoft Excel отобразит для вас список доступных номеров функций.

Например, вот как вы можете создать формулу промежуточного итога 9 для суммирования значений в ячейках от C2 до C8:

Чтобы добавить номер функции в формулу, дважды щелкните его, затем введите запятую, укажите диапазон, введите закрывающую скобку и нажмите Enter.Заполненная формула будет выглядеть так:

= ПРОМЕЖУТОЧНЫЙ ИТОГ (9, C2: C8)

Аналогичным образом вы можете написать формулу Промежуточного итога 1 для получения среднего значения, Промежуточного итога 2 для подсчета ячеек с числами, Промежуточного итога 3 для подсчета непустых значений и т. Д. На следующем снимке экрана показаны несколько других формул в действии:

Примечание. Когда вы используете формулу промежуточных итогов с функцией суммирования, например SUM или AVERAGE, она вычисляет только ячейки с числами, игнорируя пробелы, и ячейки, содержащие нечисловые значения.

Теперь, когда вы знаете, как создать формулу промежуточных итогов в Excel, главный вопрос — зачем вообще тратить силы на ее изучение? Почему бы просто не использовать обычные функции, такие как SUM, COUNT, MAX и т. Д.? Вы найдете ответ прямо ниже.

3 основных причины использовать ПРОМЕЖУТОЧНЫЙ ИТОГ в Excel

По сравнению с традиционными функциями Excel, ПРОМЕЖУТОЧНЫЙ ИТОГО дает следующие важные преимущества.

1. Вычислить значения в отфильтрованных строках

Поскольку функция Excel ПРОМЕЖУТОЧНЫЙ ИТОГ игнорирует значения в отфильтрованных строках, вы можете использовать ее для создания динамической сводки данных, в которой промежуточные итоги пересчитываются автоматически в соответствии с фильтром.

Например, если мы отфильтруем таблицу, чтобы отобразить продажи только для Восточного региона, формула промежуточного итога автоматически изменится так, что все остальные регионы будут удалены из итога:

Примечание. Поскольку оба набора номеров функций (1-11 и 101-111) игнорируют отфильтрованные ячейки, в этом случае вы можете использовать формулу ether Subtotal 9 или Subtotal 109.

2. Вычислить только видимые ячейки

Как вы помните, формулы промежуточных итогов с номером функции от 101 до 111 игнорируют все скрытые ячейки — отфильтрованные и скрытые вручную.Итак, когда вы используете функцию «Скрыть» в Excel для удаления из представления нерелевантных данных, используйте функцию номер 101-111, чтобы исключить значения в скрытых строках из промежуточных итогов.

Следующий пример поможет вам лучше понять, как это работает: Промежуточный итог 9 против Промежуточного итога 109.

3. Игнорировать значения во вложенных формулах промежуточных итогов

Если диапазон, указанный в формуле промежуточных итогов Excel, содержит любые другие формулы промежуточных итогов, эти вложенные промежуточные итоги будут проигнорированы, поэтому одни и те же числа не будут вычисляться дважды.Классно, не правда ли?

На скриншоте ниже формула общего среднего SUBTOTAL (1, C2: C10) игнорирует результаты формул промежуточных итогов в ячейках C3 и C10, как если бы вы использовали формулу среднего значения с двумя отдельными диапазонами AVERAGE (C2: C5 , С7: С9) .

Использование промежуточных итогов в Excel — примеры формул

Когда вы впервые сталкиваетесь с ПРОМЕЖУТОЧНЫМ ИТОГОМ, это может показаться сложным, запутанным и даже бессмысленным. Но как только вы перейдете к медным гвоздям, вы поймете, что освоить их не так уж и сложно.Следующие примеры дадут вам несколько полезных советов и вдохновляющих идей.

Пример 1. Промежуточный итог 9 и Промежуточный итог 109

Как вы уже знаете, Excel SUBTOTAL принимает 2 набора номеров функций: 1-11 и 101-111. Оба набора игнорируют отфильтрованные строки, но номера 1-11 включают вручную скрытые строки, а 101-111 исключают их. Чтобы лучше понять разницу, давайте рассмотрим следующий пример.

Чтобы получить отфильтрованных строк , вы можете использовать формулу Промежуточный итог 9 или Промежуточный итог 109, как показано на снимке экрана ниже:

Но если скрыто нерелевантных элементов вручную с помощью команды Hide Rows на вкладке Home > Cells group> Format > Hide & Unhide , или щелкнув строки правой кнопкой мыши и затем нажав Скройте , и теперь вы хотите суммировать значения только в видимых строках, Промежуточный итог 109 — единственный вариант:

Другие номера функций работают таким же образом.Например, для подсчета непустых отфильтрованных ячеек подойдет формула Промежуточный итог 3 или Промежуточный итог 103. Но только Промежуточный итог 103 может правильно подсчитывать видимые непробелы, если в диапазоне есть скрытых строк:

Примечание. Функция Excel ПРОМЕЖУТОЧНЫЙ ИТОГ с function_num 101-111 игнорирует значения в скрытых строках, но не в скрытых столбцах . Например, если вы используете формулу типа ПРОМЕЖУТОЧНЫЙ ИТОГ (109, A1: E1) для суммирования чисел в горизонтальном диапазоне, скрытие столбца не повлияет на промежуточный итог.

Пример 2. IF + SUBTOTAL для динамического суммирования данных

Если вы создаете сводный отчет или информационную панель, где вам нужно отображать различные сводные данные, но у вас нет места для всего, следующий подход может быть решением:

В одной ячейке создайте раскрывающийся список, содержащий имена функций, такие как Total, Max, Min и т. Д.
В ячейке рядом с раскрывающимся списком введите вложенную формулу ЕСЛИ со встроенными функциями промежуточных итогов, соответствующими именам функций в раскрывающемся списке.

Например, предположим, что значения для промежуточного итога находятся в ячейках C2: C16, а раскрывающийся список в A17 содержит Всего , Среднее , Макс и Мин элементов, «динамическая» формула промежуточного итога выглядит следующим образом:

= ЕСЛИ (A17 = «всего», ПРОМЕЖУТОЧНЫЙ ИТОГ (9, C2: C16), ЕСЛИ (A17 = «средний», ПРОМЕЖУТОЧНЫЙ ИТОГ (1, C2: C16), ЕСЛИ (A17 = «мин», ПРОМЕЖУТОЧНЫЙ ИТОГ (5, C2) : C16), ЕСЛИ (A17 = "макс", ПРОМЕЖУТОЧНЫЙ ИТОГ (4; C2: C16), ""))))

И теперь, в зависимости от того, какую функцию ваш пользователь выбирает из раскрывающегося списка, соответствующая функция промежуточных итогов будет вычислять значения в отфильтрованных строках:

Наконечник. Если вдруг раскрывающийся список и ячейка с формулой исчезнут с рабочего листа, обязательно выберите их в списке фильтров.

Excel Промежуточный итог не работает — распространенные ошибки

Если формула промежуточного итога возвращает ошибку, вероятно, это связано с одной из следующих причин:

# ЗНАЧЕНИЕ! — аргумент function_num отличается от целого числа от 1 до 11 или от 101 до 111; или любой из аргументов ref содержит трехмерную ссылку.

# DIV / 0! — возникает, если указанная итоговая функция должна выполнить деление на ноль (например,грамм. вычисление среднего или стандартного отклонения для диапазона ячеек, не содержащего единственного числового значения).

# ИМЯ? — неправильно написано имя функции Промежуточный итог — исправить ошибку проще 🙂

Наконечник. Если вы еще не чувствуете себя комфортно с функцией ПРОМЕЖУТОЧНЫЙ ИТОГ, вы можете использовать встроенную функцию ПРОМЕЖУТОЧНЫЙ ИТОГ и автоматически вставлять формулы.

Наконец, вы можете загрузить образец книги Excel «Промежуточные итоги», содержащий все формулы, которые мы обсуждали в этом руководстве.

Вас также может заинтересовать

Excel 2013: группы и промежуточные итоги

Урок 20: Группы и промежуточные итоги

/ ru / excel2013 / filtering-data / content /

Введение

Рабочие листы с большим объемом содержания иногда могут казаться подавляющими и даже трудными для чтения. К счастью, Excel может организовать данные в группах , что позволяет легко показать и скрыть различных разделов вашего рабочего листа. Вы также можете суммировать различные группы с помощью команды Промежуточный итог и создать схему для своего рабочего листа

Необязательно: Загрузите нашу рабочую тетрадь.

Для группировки строк или столбцов:

Выберите строк, или столбцов , которые вы хотите сгруппировать. В этом примере мы выберем столбцы A , B и C . Выбор столбцов для группы
Выберите вкладку Data на ленте , затем щелкните команду Group . Групповая команда
Выбранные строки или столбцы будут сгруппированы . В нашем примере столбцы A , B и C сгруппированы вместе.Сгруппированные столбцы

Чтобы разгруппировать данные , выберите сгруппированные строки или столбцы, затем щелкните команду Разгруппировать .

Щелчок по команде Разгруппировать

Чтобы скрыть и показать группы:

Чтобы скрыть группу, нажмите кнопку Hide Detail . Скрытие группы
Группа будет скрыта . Чтобы отобразить скрытую группу, нажмите кнопку Показать подробности . Нажмите кнопку Показать подробности, чтобы отобразить скрытую группу

Создание промежуточных итогов

Команда Subtotal позволяет автоматически создавать группы и использовать общие функции, такие как SUM, COUNT и AVERAGE, чтобы помочь суммировать ваши данные.Например, команда Промежуточный итог может помочь рассчитать стоимость канцелярских товаров по типу из большого складского заказа. Он создаст иерархию групп, известную как схема , чтобы помочь организовать ваш рабочий лист.

Ваши данные должны быть правильно отсортированы перед использованием команды Промежуточный итог, поэтому вы можете просмотреть наш урок по сортировке данных, чтобы узнать больше.

Для создания промежуточного итога:

В нашем примере мы будем использовать команду «Промежуточный итог» с формой заказа футболок, чтобы определить, сколько футболок было заказано каждого размера (маленький, средний, большой и очень большой).Это создаст схему для нашего рабочего листа с группой для каждого размера футболки, а затем посчитает общее количество рубашек в каждой группе.

Во-первых, отсортируйте свой лист по данным, которые вы хотите вычислить. В этом примере мы создадим промежуточный итог для каждого размера футболки, поэтому наш рабочий лист был отсортирован по размеру футболки от самого маленького до самого большого. Рабочий лист отсортирован по размеру футболки
Выберите вкладку Data , затем щелкните команду Промежуточный итог .При нажатии команды «Промежуточный итог»
появится диалоговое окно «Промежуточный итог ». Щелкните стрелку раскрывающегося списка для поля При каждом изменении: , чтобы выбрать столбец , для которого требуется подытог. В нашем примере мы выберем футболку , размер .
Щелкните стрелку раскрывающегося списка для поля Использовать функцию: , чтобы выбрать функцию , которую вы хотите использовать. В нашем примере мы выберем COUNT , чтобы подсчитать количество заказанных рубашек каждого размера.
В поле Добавить промежуточный итог в: выберите столбец , где вы хотите, чтобы вычисленный промежуточный итог отображался . В нашем примере мы выберем футболку , размер .
Когда вы будете удовлетворены своим выбором, нажмите OK . Создание промежуточного итога
Рабочий лист будет выделен в группы , а промежуточный итог будет указан под каждой группой. В нашем примере данные теперь сгруппированы по размеру футболки, и количество рубашек, заказанных в этом размере, отображается под каждой группой.Обрисованные в общих чертах данные с промежуточными итогами

Для просмотра групп по уровню:

Когда вы создаете промежуточные итоги, ваш рабочий лист делится на разные уровни . Вы можете переключаться между этими уровнями, чтобы быстро контролировать объем информации, отображаемой на листе, нажимая кнопки Уровень слева от листа. В нашем примере мы будем переключаться между всеми тремя уровнями нашей схемы. Хотя этот пример содержит только три уровня, Excel может вместить до восьми.

Щелкните самый низкий уровень , чтобы отобразить наименьшие детали. В нашем примере мы выберем уровень 1 , который содержит только grand count или общее количество заказанных футболок. Просмотр данных на самом низком уровне
Щелкните следующий уровень , чтобы развернуть деталь. В нашем примере мы выберем , уровень 2, , который содержит каждую строку промежуточных итогов, но скрывает все остальные данные на листе. Просмотр данных на следующем уровне
Щелкните верхний уровень , чтобы просмотреть и развернуть весь рабочий лист данные.В нашем примере мы выберем уровень 3 . Просмотр данных на самом высоком уровне

Вы также можете использовать кнопки Показать, и Скрыть Подробности , чтобы отображать и скрывать группы внутри контура.

Отображение и скрытие новых групп внутри контура

Чтобы удалить промежуточные итоги:

Иногда вам может не понадобиться хранить промежуточные итоги на листе, особенно если вы хотите реорганизовать данные по-разному. Если вы больше не хотите использовать промежуточные итоги, вам нужно удалить , и со своего рабочего листа.

Выберите вкладку Data , затем щелкните команду Промежуточный итог . Щелчок по команде Промежуточный итог
Появится диалоговое окно Промежуточный итог . Нажмите Удалить Все . Удаление промежуточных итогов
Все данные рабочего листа будут разгруппированы , а промежуточные итоги будут удалены .

Чтобы удалить все группы без удаления промежуточных итогов, щелкните стрелку раскрывающегося списка команды Разгруппировать , затем выберите Очистить структуру .

Удаление всех групп

Challenge!

Откройте существующую книгу Excel. Если хотите, можете воспользоваться нашим учебным пособием.
Попробуйте сгруппировать диапазон строк или столбцов вместе. Если вы используете этот пример, сгруппируйте столбцы D и E .
Используйте кнопки Показать, и Скрыть Деталь , чтобы скрыть и отобразить группу.
Попробуйте разгруппировать группу. Если вы используете пример, разгруппируйте столбцов D и E .
Обведите свой рабочий лист с помощью команды Промежуточный итог . Если вы используете пример, обведите контуром по футболку размера .
Удалите промежуточные итоги из рабочего листа.

/ ru / excel2013 / tables / content /

Группы и промежуточные итоги в Excel

Группировка строк и столбцов в Excel

Как скрыть и показать группы

Подведение итогов в Excel

Создание промежуточного итога

Просмотр групп по уровням

Удаление промежуточных итогов в Excel

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ

Формула промежуточные итоги в Excel — Группы и промежуточные итоги в Excel

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ — ONLYOFFICE

Промежуточные итоги в Excel — Excel works!

Промежуточные итоги в Excel, сумма только видимых ячеек

Промежуточные итоги в Excel.

Похожие статьи

Функция эксель промежуточные итоги

Синтаксис функции

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() и Автофильтр

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() и Скрытые строки

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() и средство EXCEL Промежуточные итоги

Другие функции

Описание

Синтаксис

Примечания

Пример

Примеры использования функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Excel

Суммирование только видимых ячеек в фильтре Excel

Выборочное суммирование ячеек таблицы в Excel

Примеры формул для расчетов промежуточных итогов в таблице Excel

Особенности использования функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Excel

Функция Промежуточные итоги в Excel. Формула, требования к таблице

Требования, которые предъявляют к таблицам, чтобы получить промежуточные результаты

Как применяется функция промежуточных итогов

Промежуточные итоги в виде формулы

Применение функции и обработка ячеек вручную

Подведем итоги

Как использовать функцию ПРОМЕЖУТОЧНЫЙ ИТОГ Excel

Примеры

Доступные расчеты

ПРОМЕЖУТОЧНЫЙ ИТОГ в таблицах Excel

ИТОГО с очертаниями

Банкноты

Excel ПРОМЕЖУТОЧНЫЙ ИТОГ Примеры функций

ПРОМЕЖУТОЧНЫЙ ИТОГ Функция

Использовать ПРОМЕЖУТОЧНЫЙ ИТОГ с отфильтрованными списками

Создать быстрый ПРОМЕЖУТОЧНЫЙ ИТОГ

ПРОМЕЖУТОЧНЫЙ ИТОГ Аргументов

Итого номеров функций

11 функций для промежуточного итога

Диапазон номеров функций

Гибкий ПРОМЕЖУТОЧНЫЙ ИТОГ Формула

Скачать образец файла

Дополнительные руководства

Как использовать функцию промежуточных итогов и промежуточных итогов в Excel

Видеоурок

Загрузите файл Excel

Функция ПРОМЕЖУТОЧНЫЙ ИТОГ

Некоторые преимущества функции ПРОМЕЖУТОЧНЫЙ ИТОГ

Функция ПРОМЕЖУТОЧНОГО ИТОГА не учитывает дважды

Функция ПРОМЕЖУТОЧНЫЙ ИТОГ экономит время

Характеристика промежуточных итогов

Подготовить данные

Создание групп структуры промежуточных итогов

Развернуть и свернуть группы строк

Бесплатный веб-семинар по обучению работе с электроинструментами

Заключение

в Excel — как использовать

Синтаксис функции промежуточного итога:

Пример функции подытога в Excel:

Почему вам следует использовать функцию промежуточных итогов?

Использование функции промежуточного итога на ленте Excel:

Функция ПРОМЕЖУТОЧНЫЙ ИТОГ в Excel — формула, советы, как использовать

Что такое ПРОМЕЖУТОЧНЫЙ ИТОГ в Excel?

Формула

Почему нам нужно использовать ПРОМЕЖУТОЧНЫЙ ИТОГ?

Как использовать функцию ПРОМЕЖУТОЧНЫЙ ИТОГ в Excel?

Шаг 1

Шаг 2

Советы для функции ПРОМЕЖУТОЧНЫЙ ИТОГ:

Совет № 1

Совет № 2