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

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

history 25 мая 2014 г.

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

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

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

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

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

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

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

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

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

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

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

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

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

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() и средство 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 не составляет труда. Поэтому многие пользователи интересуются, какими способами возможно подведение промежуточных в рамках одной таблицы. Конечно можно все делать вручную, но поскольку Excel – это программа для автоматизации обработки данных, давайте более подробно рассмотрим методы, с помощью которых можно поручить эту задачу компьютеру.

Требования к таблицам для подведения промежуточных итогов

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

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

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

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

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

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

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

После этого непосредственно начинаем работу:

После выполнения всех операций таблица обретет такой вид.

4

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

5

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

6

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

7

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

8

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

9

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

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

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

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

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

10

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

Давайте на реальном примере разберем, как правильно применять эту функцию.

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

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

11

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

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

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

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

12

Давайте используем функцию «Автофильтр», чтобы отображать лишь ряды, описывающие товар номер 1. И давайте тогда применим эту функцию, чтобы узнать сумму товаров, отмеченных номером 1. Соответственно, нам требуется функция 9 или 109.

13

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

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

Предположим, у нас есть такая же таблица, к которой не был применен автофильтр. Ряды, озаглавленные «Товар 2» мы попробуем просто скрыть стандартным способом. То есть, воспользоваться или контекстным меню, или перейти по пути Главная – Ячейки – Формат – Скрыть.

14

Здесь пользователю необходимо убедиться, что используется тот код, подходящий под скрытые строки. Если говорить проще, то для поиска промежуточной суммы необходимо ввести код 109, а не 9. Последний вариант не чувствителен к скрытым строкам. Поэтому при ручной фильтрации данных нужно использовать коды, начиная 101 и заканчивая 111. Обозначения соответствующие описанным выше, просто нужно к ним добавить 100.

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

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

15

Теперь давайте отфильтруем таблицу, оставив только Товар1 в ней.

16

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

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

17

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

Сама формула будет такой:

18

Как видим, мы используем сочетание функций СТРОКА и ДВССЫЛ в том месте, где расположен второй аргумент функции. Так можно добиться того, чтобы второму аргументу передавалось несколько диапазонов. Главное – убедиться в том, чтобы не было скрытой строки, потому что тогда помимо стоимости будет отображаться значение «Пустой текст», а оно функцией СРГЕОМ() не учитывается.

Формула «Промежуточные итоги» в Эксель (примеры)

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

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

Наш диапазон выглядит следующим образом.

19

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

20

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

Написание формулы промежуточных итогов вручную

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

Любая формула начинается со знака =, потом пишется непосредственно название функции, открывается скобка, перечисляются все аргументы через точку с запятой), а потом закрывается скобка.

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

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

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

Источник

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник

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

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

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

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

Применение функции промежуточных итогов

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

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

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

Написание формулы промежуточных итогов вручную

Есть еще один способ посчитать промежуточные итоги – с помощью специальной функции.

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

= ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер обработки данных;координаты ячеек)

Далее жмем клавишу Enter и получаем желаемый результат в заданной ячейке.

Заключение

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

Источник