Вычисление промежуточных итогов

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

Примечание

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Примечание

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

В начало

Полезное

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *