Возвращает промежуточный итог в список или базу данных. Обычно проще создать список с промежуточными итогами, используя команду Итоги в меню Данные . Но если список с промежуточными итогами уже создан, его можно модифицировать, редактируя формулу с функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ.
Синтаксис
Номер_функции - это число от 1 до 11, которое указывает, какую функцию использовать при вычислении итогов внутри списка.
| Нр. ф-и | Функция | Описание функции |
| 1 | СРЗНАЧ | Возвращает среднее (арифметическое) своих аргументов. |
| 2 | СЧЁТ | Подсчитывает количество чисел в списке аргументов. Функция СЧЁТ используется для получения количества числовых ячеек в интервалах или массивах ячеек. |
| 3 | СЧЁТЗ | Подсчитывает количество непустых значений в списке аргументов. Функция СЧЁТЗ используется для подсчета количества ячеек с данными в интервале или массиве. |
| 4 | МАКС | Возвращает наибольшее значение из набора значений. |
| 5 | МИН | Возвращает наименьшее значение в списке аргументов. |
| 6 | ПРОИЗВЕД | Перемножает числа, заданные в качестве аргументов и возвращает их произведение. |
| 7 | СТАНД ОТКЛОН | Оценивает стандартное отклонение по выборке. Стандартное отклонение - это мера того, насколько широко разбросаны точки данных относительно их среднего. |
| 8 | СТАНД ОТКЛОНП | Вычисляет стандартное отклонение по генеральной совокупности. Стандартное отклонение - это мера того, насколько широко разбросаны точки данных относительно их среднего. |
| 9 | СУММ | Суммирует все числа в интервале ячеек. |
| 10 | ДИСП | Оценивает дисперсию по выборке |
| 11 | ДИСПР | Вычисляет дисперсию для генеральной совокупности |
Внимание!
Если уже имеются формулы подведения итогов внутри аргументов ссылка1;ссылка2;... (вложенные итоги), то эти вложенные итоги игнорируются, чтобы избежать двойного суммирования.
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ игнорирует все скрытые строки, которые получаются в результате фильтрации списка. Это важно в том случае, когда нужно подвести итоги только для видимых данных, которые получаются в результате фильтрации списка.
Трехмерные ссылки используются при необходимости анализа данных из одной и той же ячейки или диапазона ячеек на нескольких листах одной книги. Трехмерная ссылка включает в себя ссылку на ячейку или диапазон, перед которой ставятся имена листов. Microsoft Excel использует все листы, хранящиеся между начальным и конечным именами, указанными в ссылке. Например, формула =СУММ(Лист2:Лист13!B5) суммирует все значения, содержащиеся в ячейке B5 на всех листах в диапазоне от Лист2 до Лист13 включительно.
Пример
Свойство функции игнорировать скрытые строки оказалось очень удобным для оперативного анализа данных с использованием фильтра.
Допустим, имеем какой то учет арсенала для борьбы с "захватчиками". Создаем таблицу. Если таблица большая, для удобства, чтобы каждый раз не лазить в конец таблицы, итоговую строку можно расположить над шапкой, а саму шапку закрепить как область.
Такой оперативный анализ очень полезен техническим исполнителя. Представьте, Вы спокойно сидите перед своим персональным компьютером, на своем рабочем месте и мирно занимаетесь своими личными делами. И вдруг, звонок начальника: "а скажи-ка мне Тяпкин-Ляпкин, скока у нас того-то и того-то"? Пока начальник выговаривает, что ему надо, Вы быстренько запускаете файл с базой данных и по фильтру отбираете то, что интересует руководство. Ответ готов, начальник доволен, товарищи по работе в шоке, а Вы опять спокойно занимаетесь своими делами.
Понятно, что для такого результата нужна эта самая база данных, а ее создание и поддержание в актуальном состоянии зависит от Вашего профессионализма. Поверьте мне, никто и никогда не будет Вам рассказывать, как достигнуть результата, всех интересует только результат, поэтому постоянно думайте над тем как облегчить и автоматизировать свою работу при помощи Excel, как минимизировать ошибки и Ваш профессионализм достигнет небывалых высот.
Доброго времени суток друзья!
Эту статью я посвящаю такой возможности, как создать промежуточные итоги в Excel. Если вы даже слышали или знаете, что это такое, всё же будет полезно прочитать или послушать мою информацию. По большому счёту, промежуточные итоги – это автоматическое подведение итогов по заданному критерию и их для удобства и хорошей визуализации данных.
Это достаточно полезный и удобный инструмент работы в электронных таблицах, который вам пригодится, а для большего удобства я советую использовать эти итоги в тандеме с группированием ячеек, это вообще супер сила. Также я прошу обратить ваше внимание, что перед работой с итогами, необходимо в обязательном порядке произвести правильную ваших данных.
Итак, давайте же более детально рассмотрим, что это за итоги, как их создать, как с ними работать, ну и собственно, как их убрать, если нужда в этой возможности отпадёт. И начнём все наши исследования сначала, а точнее с того, как можно создать промежуточный итог.
Как сделать промежуточные итоги в Excel
А теперь давайте на созданном примере посмотрим поэтапно, как это осуществить:
Работа со сгруппированными данными
После проведенных манипуляций, работа с данными значительно упростится. Данные можно удобно визуализировать, используя группировку данных и создавать столько уровней, сколько вам будет удобно, но, увы, не более 8 вложенных, с опыта могу сказать, что этого хватит за глаза.
Нажимая на иконки группирования данных, вы скрываете и отображаете ту структуру данных, которая вам наиболее актуальна на данный момент. 
К примеру, можно отобразить только общий итог, отобразить итоги по группам данных и полный разворот данных в деталях. 
Также, всё это можно проделать, используя пиктограммы «Скрыть детали»
или «Показать»
. 
Убрать промежуточные итоги
В случае, когда промежуточные итоги в Excel станут, не нужны, ну или потребуется перегруппировка по-новому ваших данных в таблице, в этом случае итоги можно удалить без потери качества исходных данных. Для этого нужно:
- Открыть вкладку на панели управления «Данные» и в блоке «Структура» нажимаете на пиктограмму «Промежуточный итог» .
- В появившемся диалоговом окне «Промежуточные итоги» нажимаем кнопку «Убрать всё» .
- После нажатия все ваши данные разгруппируются, а промежуточные итоги будут удалены.
Если же вам всё-таки промежуточные итогинужно оставить, а вот группирование не нужно, вы можете в панели задач на вкладке на вкладке «Данные»
в кнопки «Разгруппировать»
выбрать пункт «Удалить структуру»
, структура группировки будет удалена, оставив вам на прощание промежуточные итоги.
А на этом у меня всё! Я очень надеюсь, что всё вышеизложенное вам понятно. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!
Пока богатство еще не приобретено, стремление к нему изнуряет, будучи же приобретено, оно изводит заботами, когда же оно утрачено, мучает тоска по нем. Демокрит
В этой статье описаны синтаксис формулы и использование функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Microsoft Excel.
Описание
Возвращает промежуточный итог в список или базу данных. Обычно проще создать список с промежуточными итогами, используя в настольном приложении Excel команду Промежуточные итоги в группе Структура на вкладке Данные . Но если такой список уже создан, его можно модифицировать, изменив формулу с функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ.
Синтаксис
Аргументы функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ описаны ниже.
Номер_функции - обязательный аргумент. Число от 1 до 11 или от 101 до 111, которое обозначает функцию, используемую для расчета промежуточных итогов. Функции с 1 по 11 учитывают строки, скрытые вручную, в то время как функции с 101 по 111 пропускают такие строки; отфильтрованные ячейки всегда исключаются.
Номер_функции
| Номер_функции
| Функция |
|---|---|---|
|
ПРОИЗВЕД |
||
|
СТАНДОТКЛОН |
||
|
СТАНДОТКЛОНП |
||
Примечания
Если уже имеются формулы подведения итогов внутри аргументов "ссылка1;ссылка2;..." (вложенные итоги), эти вложенные итоги игнорируются, чтобы избежать двойного суммирования.
Для констант "номер_функции" от 1 до 11 функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ учитывает значения строк, скрытых с помощью команды Скрыть строки (меню Формат , подменю Скрыть или отобразить ) в группе Ячейки на вкладке Главная в настольном приложении Excel. Эти константы используются для получения промежуточных итогов с учетом скрытых и нескрытых чисел списка. Для констант "номер_функции" от 101 до 111 функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ исключает значения строк, скрытых с помощью команды Скрыть строки . Эти константы используются для получения промежуточных итогов с учетом только нескрытых чисел списка.
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ исключает все строки, не включенные в результат фильтра, независимо от используемого значения константы "номер_функции".
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ применяется к столбцам данных или вертикальным наборам данных. Она не предназначена для строк данных или горизонтальных наборов данных. Так, при определении промежуточных итогов горизонтального набора данных с помощью значения константы "номер_функции" от 101 и выше (например, ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;B2:G2)), скрытие столбца не повлияет на результат. Однако на него повлияет скрытие строки при подведении промежуточного итога для вертикального набора данных.
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем - клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Данные |
||
|---|---|---|
|
Формула |
Описание |
Результат |
|
ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;A2:A5) |
Значение промежуточного итога диапазона ячеек A2:A5, полученное с использованием числа 9 в качестве первого аргумента. |
|
|
ПРОМЕЖУТОЧНЫЕ.ИТОГИ(1;A2:A5) |
Среднее значение промежуточного итога диапазона ячеек A2:A5, полученное с использованием числа 1 в качестве первого аргумента. |
|
|
Примечания |
||
|
В качестве первого аргумента функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ необходимо использовать числовое значение (1–11, 101–111). Этот числовой аргумент используется для промежуточного итога значений (диапазонов ячеек, именованных диапазонов), указанных в качестве следующих аргументов. |
||
Если у вас есть таблица данных, то посчитать сумму по столбцу можно обычной функцией =СУММ(). Вы, наверное, замечали, что в случае отбора определенных значений такая функция работает неверно. Как посчитать сумму только отобранных значений? Для этого есть возможность создать промежуточные итоги в Excel.
1. Номер_функции. Это функция, которой нужно воспользоваться для расчета итогов.
Пример. Посчитайте сумму по столбцу Сумма для всех месяцев, где продано больше 20 единиц.
Как вы видите на первой картинке, мы отобрали фильтром все значение по столбцу «Продано единиц» большие 20. В ячейке С15 записываем формулу вида
ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;C2:C13)
Значение функции будет меняться, если изменить условия фильтра.
Прошу не забывать, что это же действие можно сделать функцией
Промежуточные итоги в Excel, сумма только видимых ячеек
Интересно, что в списке номеров функций для формулы промежуточных итогов, как на картинке выше, нет интересной функции. Чтобы подсчитать сумму всех видимых ячеек необходимо воспользоваться кодом 109 .
Промежуточные итоги в Excel. Создать таблицу на отдельном листе
т.е. с промежуточными итогами по каждой группе строк, например, по полугодиям в нашем примере.
Что нужно сделать:
1. Убедитесь, что данные отсортированы, хотя бы по одному столбцу
2. Выберите диапазон
3. Зайдите в ленте инструментов в меню Данные, раздел Структура и выберите Промежуточные итоги
В открывшемся меню делаем настройки, выбираем по какому столбцу сортироваться (Полугодие) и что делать (Операция), а так же в каком столбце проводить расчет:
Должно получиться как на рисунке выше.
Очень удобно, когда таблица большая и нужно отсортировать по номенклатуре, кварталу, счету и т.д.
Важно отметить:
— если используете возможность Таблица (Вставка -Таблица), в эту интерактивную таблицу при расчете суммы автоматом добавляется «Строка итогов», которая во многом заменяет промежуточные итоги. В открывшейся вкладке Работа с таблицами – Конструктор вы можете настроить интерактивную таблицу почти под любые условия. Я пока почти не использую эту возможность, т.к. такая таблица даже на 10 тысяч строк может значительно грузить систему. Хотя в Excel я работаю до 500 тыс. строк без проблем, даже без PowerPivot.
Поделитесь нашей статьей в ваших соцсетях:Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Excel используется для расчета промежуточных итогов в таблицах (в том числе и базах данных) и возвращает искомое числовое значение (в зависимости от номера требуемой операции, указанного в качестве первого аргумента данной функции, например, 1 – среднее арифметическое диапазона значений, 9 – суммарное значение и т. д.). Чаще всего рассматриваемую функцию применяют для модификации списков с промежуточными итогами, созданных с использованием специальной встроенной команды в Excel.
Примеры использования функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Excel
Функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ целесообразно использовать в случаях, когда таблица содержит большое количество данных, которые могут быть отфильтрованы по одному или нескольким критериям. При этом в результате применения фильтров будет отображена только часть таблицы, данные в которой соответствуют установленному критерию. Однако операции с использованием обычных функций, таких как СУММ, СРЗНАЧ и др. будут производиться над всей изначальной таблицей (то есть с учетом скрытых строк). Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ работает только с отфильтрованными данными.
Суммирование только видимых ячеек в фильтре Excel
В таблице содержатся данные о продажах в магазине музыкальных инструментов электрогитар трех марок различных моделей на протяжении трех дней. Рассчитать промежуточные итоги по продажам гитары марки Ibanez.
Вид исходной таблицы данных:
Используем фильтр для отбора данных, которые относятся к гитарам марки Ibanez. Для этого выделим всю таблицу или просто перейдите курсором на любую ячейку таблицы и воспользуйтесь инструментом «ДАННЫЕ»-«Фильтр». Теперь исходная таблица имеет следующий вид:
Нажмем на раскрывающийся список в столбце B («Марка товара») и установим флажок только напротив названия «Ibanez»:
После нажатия на кнопку «ОК» таблица примет следующий вид:
Как видно, некоторые строки теперь являются скрытыми. Если применить обычную функцию СУММ, будет произведен расчет для всех строк исходной таблицы:
Вместо этого в ячейке C24 будем использовать следующую функцию:
Описание аргументов:
- 9 – числовое значение, соответствующее использованию функции СУММ для получения промежуточных итогов;
- C4:C20 – диапазон ячеек, содержащих данные о стоимости гитар (при этом все другие гитары, кроме марки Ibanez, в расчете не учитываются).
Аналогично выполним расчет для количества проданных гитар и общей выручки («Сумма). В результате получим:
Для сравнения приведем результаты, полученные с использованием обычной функцией СУММ:
Несмотря на то, что часть строк скрыта благодаря использованию фильтра, функция СУММ учитывает все строки в расчете.
Выборочное суммирование ячеек таблицы в Excel
Для таблицы, используемой в первом примере, получить промежуточные итоги для каждой марки товара по дате продаж.Отключите автофильтр и выделите исходную таблицу данных из первого примера. Теперь воспользуемся инструментом «Промежуточный итог» во вкладке «Данные» на панели инструментов:
В открывшемся диалоговом окне выберем наименование столбца «Дата» в качестве критерия «При каждом изменении в:». Следующей опцией является операция, которая будет проводиться над данными. Выберем «Сумма» для суммирования значений. Критерий «Добавить итоги по:» позволяет выбрать столбцы, для которых будет выполняться операция суммирования. Установим флажки также напротив «Заменить текущие итоги» и «Итоги под данными»:
После нажатия на кнопку «ОК» исходная таблица примет следующий вид:
Полученная таблица имеет инструменты, позволяющие скрывать/отображать части данных и отобразить при необходимости только общий итог. Если выделить любую ячейку, в которой отображаются промежуточные итоги, можно увидеть, что они были рассчитаны с использованием функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ.
Каждая такая функция может быть модифицирована на усмотрение пользователя. Например, так автоматически определена средняя стоимость гитар, проданных за 13.08.2018:
Примеры формул для расчетов промежуточных итогов в таблице Excel
В таблице Excel представлены данные о продаже джинсов и брюк на протяжении нескольких дней с указанием размера и стоимости покупки. Определить значение моды (наиболее популярного размера) для джинсов на 23.08.2018, а также значение среднего отклонения средней цены покупки от средней стоимости проданных джинсов.Вид исходной таблицы данных:
Отфильтруем данные с использованием критериев «джинсы» и указанная в условии дата:
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ не содержит встроенных функций для расчета моды и среднего отклонения. Для расчета моды используем следующую формулу (формула массива 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 (такие ссылки называются трехмерными), функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ вернет код ошибки #ЗНАЧ!.