Функция ПРОМЕЖУТОЧНЫЕ. ИТОГИ
Возвращает промежуточный итог в список или базу данных. Обычно проще создать список с промежуточными итогами, используя команду Итоги в меню Данные. Но если список с промежуточными итогами уже создан, его можно модифицировать, редактируя формулу с функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ.
ПРОМЕЖУТОЧНЫЕ. ИТОГИ(номер_функции; ссылка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, как минимизировать ошибки и Ваш профессионализм достигнет небывалых высот.
ПРОМЕЖУТОЧНЫЕ.ИТОГИ (функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ)
В этой статье описаны синтаксис формулы и использование функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ в 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
Особенность функции состоит в том, что она предназначена для использования совместно с другими средствами 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 .
Функция ПРОМЕЖУТОЧНЫЕ ИТОГИ в Excel
Здравствуйте, друзья. Сегодня расскажу отличный способ, как посчитать промежуточные итоги в таблице Excel, получив приятное преимущество. Изложенный здесь материал обязателен для продвинутых пользователей, поэтому, читаем до конца!
Вот пример таблицы. Здесь есть продажи менеджеров по месяцам. Для каждого менеджера указан регион, в котором находятся его клиенты.
В строках 18-19 посчитаны суммарные и средние продажи в каждом месяце. Для этого использованы функции СУММ и СРЗНАЧ соответственно. Так сделает каждый, кто неплохо знает программу. А мы поступим иначе, используем функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Синтаксис ее таков:
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(функция ; ссылка 1 ; [ссылка 2] ; … ; [ссылка n])
Её аргументы:
- Функция – математическая операция, которая будет в итоговой строке, например, сумма, среднее, максимальное и т.п. Полный перечень функций я опишу ниже
- Ссылка – диапазон, для которого рассчитывается функция. Обязательна только первая ссылка
Предусмотрены такие функции:
Функция Excel
Код, когда скрытые строки учитываются
Код, когда скрытые строки НЕ учитываются
Описание
Количество ячеек, содержащих числа
Количество непустых ячеек
Максимальное в массиве
Минимальное в массиве
Произведение всех элементов
Стандартное отклонение по выборке
Стандартное отклонение по генеральной совокупности
Сумма всех элементов
Дисперсия по выборке
Дисперсия по генеральной совокупности
Тогда формулу суммирования можно записать так:
Первым аргументом указана девятка – код функции суммирования. Вторым – все продажи в месяце.
Так в чём преимущество, если функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ выглядит сложнее? Я приведу несколько:
- Для интерактивных таблиц, где клиент сам указывает, какой показатель вывести на экран, эта функция позволяет легко организовать изменение показателя в пределах ячейки.
- Используется лишь одна функция, в которой достаточно изменить один код, чтобы пересчитать все итоги
- Самое важное: функцию можно настроить на отображение итогов лишь для видимых ячеек!
На третьем пункте списка остановлюсь отдельно, т.к. это мощное преимущество функции. В таблице кодов я привел две колонки: «Код, когда скрытые строки учитываются» и «Код, когда скрытые строки не учитываются». Названия говорят сами за себя. Давайте посмотрим, как это работает. В таблице примером посчитаем среднее значение продаж в каждом месяце:
В январе эта величина составила 485 шт.
Обратите внимание, код функции – 1, т.е. «среднее значение, скрытые ячейки учитываются». Теперь скроем строки 5-10 и убедимся, что среднее значение в январе осталось таким же, 485.
Теперь изменим код функции с 1 на 101 – «среднее значение, скрытые ячейки НЕ учитываются». Среднее будет вычислено только для видимых на экране строк! То есть, вы можете просто скрыть ненужные ячейки, и они не будут участвовать в расчете итогов!
Думаю, вы оценили эту возможность, т.к. большинство функций Эксель так не умеют, а функционал действительно полезный. Следует оговориться еще о нескольких особенностях и повторить уже названные:
- Коды 1-11 используются для итогов, включая скрытые строки
- Коды 101-111 применяют для получения результатов без учета скрытых
- Функция ВСЕГДА исключает из расчета строки, скрытые автофильтром
- Ф-я ПРОМЕЖУТОЧНЫЕ.ИТОГИ исключает из расчета ячейки, которые тоже содержат эту функцию
- Когда вы используете функцию для горизонтальных массивов, скрытые столбцы никогда не исключаются!
Это всё, что я планировал рассказать. Делитесь статьёй с друзьями, если она вам понравилась, а если возникли сложности – пишите комментарии!