В этой статье описаны синтаксис формулы и использование функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ в 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). Этот числовой аргумент используется для промежуточного итога значений (диапазонов ячеек, именованных диапазонов), указанных в качестве следующих аргументов. |
||
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() , английский вариант SUBTOTAL(), используется для вычисления промежуточного итога (сумма, среднее, количество значений и т.д.) в диапазоне, в котором имеются скрытые строки.
Особенность функции состоит в том, что она предназначена для использования совместно с другими средствами 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;... (вложенные итоги), то эти вложенные итоги игнорируются, чтобы избежать двойного суммирования.
Важно : Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() разработана для столбцов данных или вертикальных наборов данных. Она не предназначена для строк данных или горизонтальных наборов данных (ее использование в этом случае может приводить к непредсказуемым результатам).
Пусть имеется исходная таблица.
Пусть имеется та же исходная таблица. Скроем строки с товаром Товар2 через меню или через контекстное меню.
В этом случае имеется разница между использованием кода функции СУММ() : 9 и 109. Функция с кодом 109 "чувствует" скрыта строка или нет. Другими словами для диапазона кодов номер_функции от 101 до 111 функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() исключает значения строк скрытых при помощи команды Главная/ Ячейки/ Формат/ Скрыть или отобразить . Эти коды используются для получения промежуточных итогов только для не скрытых чисел списка.
Пусть имеется также исходная таблица. Создадим структуру с использованием встроенного средства EXCEL - .
Скроем строки с Товар2 , нажав на соответствующую кнопку "минус" в структуре.
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() исключает все неотображаемые строки структурой независимо от используемого значения кода номер_функции и, в нашем случае, подсчитывает сумму только товара Товар1 . Этот результат аналогичен ситуации с автофильтром.
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() может подсчитать сумму, количество и среднее отобранных значений, а также включает еще 8 других функций (см. синтаксис). Как правило, этик функций вполне достаточно, но иногда требуется расширить возможности функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ() . Рассмотрим пример вычисления среднего геометрического для отобранных автофильтром значений. Функция СРГЕОМ() отсутствует среди списка функций доступных через соответствующие коды, но выход есть.
Задача выборочного копирования в практике бухгалтерского учета встречается очень часто. Применительно к работе с итогами проблема такова. Есть база данных, в которой собраны сведения о движении товаров, денежных средств, — чего угодно. Информация в ней представлена подробно. Например, по датам, документам, по каждому товару и т. д. по этим данным мы собираемся построить аналитический отчет. Однако в нем такая детализация нам не нужна, мы хотим получить краткий и лаконичный документ. Иными словами, мы должны взять исходную базу, обработать ее, посчитать итоговые значения и затем использовать их для формирования результирующего отчета.
Инструмент для получения итогов у нас есть: в данном случае это функция « Промежуточные итоги ». В результате его применения в базе появятся специальные записи, где будут посчитаны итоговые значения по указанным полям (или колонкам). Остается решить одну проблему: как отделить эти итоги от основной базы данных, чтобы затем использовать их в дальнейшей работе.
Для решения этой задачи существуют разные способы — от организации динамических ссылок до логических функций и массивов формул. Но сегодня мы познакомимся с другим, не менее интересным приемом — воспользуемся инструментом « Выделение группы ячеек ».
Итак, есть база данных, фрагмент которой показан на рис. 1. В ней представлены сведения о продажах ТМЦ за некоторый период: дата, наименование товара, цена, объем реализации (количество) и общая сумма. Наша задача — посчитать суммарные объемы продаж за каждую дату и затем скопировать эти итоги на другой рабочий лист. Делаем так.
1. Открываем документ, ставим указатель активной ячейки в область данных.
2. Вызываем меню « Данные », в группе « Сортировка и фильтр » щелкаем на иконке « Сортировка » (рис. 2) — перед подведением итогов базу нужно отсортировать по ключевому полю. Откроется окно, изображенное на рис. 3.
3. В этом окне в списке « Сортировать по » выбираем вариант « Дата », в поле « Порядок » оставляем значение « От старых к новым ».
4. В окне « Сортировка » нажимаем « ОК ».
5. В группе « Структура » щелкаем на иконке « Промежуточный итог » (рис. 2). Откроется окно, как на рис. 4.
6. В нем из списка « При каждом изменении в: » выбираем значение « Дата ». В поле « Операция: » указываем « Сумма ». В группе параметров « Добавить итоги по: » отмечаем галочками значения « Кол. » и « Сумма ».
7. В окне « Промежуточные итоги » нажимаем « ОК ». В базе данных появятся строки с итогами, а в левом верхнем углу рабочего листа — значки с уровнями группировки.
8. Щелкаем левой кнопкой на значке второго уровня, чтобы оставить на экране только промежуточные итоги .
Результат нашей работы показан на рис. 5. На экране видны только итоговые значения. Остальные записи скрыты. И теперь наша задача скопировать эти итоги на другой рабочий лист. Обычным способом сделать это не удастся. В этом нетрудно убедиться: выделяем колонки с базой данных, нажимаем « Ctrl+C », переходим на новый лист и вставляем содержимое из буфера комбинацией « Ctrl+V ». На лист вместе с итогами Excel скопирует все записи исходной базы данных, что нас в данном случае не устраивает. Конечно, можно было бы копировать каждый итог по отдельности. Но это долго и утомительно. Поэтому мы поступим иначе. Делаем так.
1. Удерживая клавишу « Ctrl », последовательно щелкаем на колонках « B », « E » и « F ». Столбцы « D » и « E » не выделяем, так как на втором уровне группировки в них нет значений.
2. Не снимая выделения, нажимаем « Ctrl+G » или клавишу « F5 ». Откроется окно « Переход », как на рис. 6.
3. В этом окне нажимам кнопку « Выделить… ». Откроется окно « Выделение группы ячеек » (рис. 6).
4. В нем включаем параметр « только видимые ячейки » и нажимаем « ОК ».
5. Не снимая выделения, нажимаем « Ctrl+С » (копируем видимые ячейки в буфер обмена).
6. Переходим на новый лист (на рис. 5 он называется « Итоги ») и нажимаем « Ctrl+ V » (вставляем данные из буфера).
Результат копирования показан на рис. 7. На новый лист попали только итоговые значения. Но это не все. Вернемся на лист к базе данных с итогами (рис. 5). Щелкаем на ячейке « А7 » — в ней посчитано суммарное количество проданного товара за « 17/01/2011 ». Смотрим на строку формул — там стоит выражение: « =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;E4:E6) ». Это функция суммирования значений за указанную дату из блока « E4:E6 ».
Переходим на лист « Итоги » и смотрим на содержимое ячейки « B3 ». В ней записано значение « 8 » и это обычное число .
Важно! При копировании фрагментов таблицы с использованием инструмента « Выделить » в результирующий документ Excel переносит только значения.
На сегодня все. Успешной работы! Жду ваших писем, предложений и замечаний на [email protected] , [email protected] или на форуме редакции.
Подсчитаем промежуточные итоги в таблице MS EXCEL. Например, в таблице содержащей сведения о продажах нескольких различных категорий товаров подсчитаем стоимость каждой категории .
Имеем таблицу продаж товаров (товары повторяются). См. Файл примера .
Подсчитаем стоимость каждого товара с помощью средства MS EXCEL Промежуточные итоги (Данные/ Структура/ Промежуточные итоги ).
Для этого необходимо:
Как видно из рисунка выше, после применения инструмента Промежуточные итоги, MS EXCEL создал три уровня организации данных: слева от таблицы возникли элементы управления структурой. Уровень 1: Общий итог (стоимость всех товаров в таблице); Уровень 2: Стоимость товаров в каждой категории; Уровень 3: Все строки таблицы. Нажимая соответствующие кнопки можно представить таблицу в нужном уровне детализации. На рисунках ниже представлены уровни 1 и 2.
Скопировать только строки с промежуточными итогами в другой диапазон не так просто: если даже таблица сгруппирована на 2-м уровне (см. рисунок выше), то выделив ячейки с итогами (на самом деле выделится диапазон А4:D92) и скопировав его в другой диапазон мы получим всю таблицу. Чтобы скопировать только Итоги используем (будем использовать тот факт, что MS EXCEL при создании структуры Промежуточные итоги вставляет строки итогов с добавлением слова Итог или в английской версии - Total ).
Раздел «Структура», помимо группировки и разгруппировки данных, имеет еще одну функцию – «Промежуточные итоги». С ее помощью Вы можете задать автоматическую группировку для данных с подведенным по ним итогом. Чтобы стало понятнее, о чем идет речь, рассмотрим в виде примера таблицу по сотрудникам с результатами их продаж за квартал.
Выделив таблицу и нажав на значок «Промежуточные итоги», Excel выдаст окно:
В поле «При каждом изменении в:» необходимо указать заголовок столбца таблицы по которому будет определяться подведение итогов. В качестве заголовка выступают ячейки верхних строк выделенной таблицы. Надписи в ячейках, указанного столбца, будут служить заголовками строк. Программа будет проверять заголовки на сходство. Если заголовки строк совпадают, то она добавляет его в подсчет итога, если не совпадают, то дополнительной строкой выводится результат по группе, определенных одним заголовком. Поэтому рекомендуем произвести заранее сортировку по данному столбцу.
Во втором поле «Операция:» выбирается какой итог необходимо подвести: Сумма, количество, среднее и т.д.. В последнем поле «Добавить итог по:» выбирается столбец, который содержит необходимые данные для вывода результата.
Ниже располагается 3 чекбокса (флага):
Кнопка «Убрать все» удаляет всю структуру данных.
Так преобразовалась таблица, после применения к ней описываемой функции:
Добавленные новые ячейки содержат в себе математическую функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ() , ознакомиться с которой можно в статье про математические функции - Математические функции Excel .
Механизм подведения промежуточных итогов имеет общие черты с другим более мощным инструментом приложения Excel –
