Команда Данные→Структура →Промежуточный итог представляет собой удобный механизм автоматической вставки формул в базу данных рабочего листа. В ней используется функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Чтобы реализовать этот механизм, нужно сортировать записи каждый раз при изменении значений в определенных столбцах.
Примечание
Когда выделена таблица, команда Данные→Структура→Промежуточный итог недоступна. Таким образом, этот раздел применим только при работе с базами данных рабочего листа. Если данные находятся в таблице и нужно автоматически вставить промежуточные итоги, преобразуйте таблицу в диапазон, воспользовавшись командой Работа с таблицами→Конструктор→Сервис→Преобразовать в диапазон. После вставки промежуточных итогов можно снова преобразовать диапазон в таблицу с помощью команды Вставка→Таблицы→Таблица.
На рисунке показан пример диапазона, подходящего для вычисления промежуточных итогов. Эта база данных отсортирована по столбцам Месяц и Регион.
Чтобы вставить на рабочий лист формулы, подсчитывающие промежуточные итоги, установите курсор ячейки в любом месте базы данных и выберите команду Данные→Структура→Промежуточный итог. Откроется диалоговое окно, показанное на рисунке.
Диалоговое окно Промежуточные итоги содержит следующие элементы.
• При каждом изменении в. В этом раскрывающемся списке отображаются все столбцы, имеющиеся в базе данных. Весь список должен быть отсортирован по выбранному полю.
• Операция. Раскрывающийся список предлагает выбрать одну из 11 доступных функций (по умолчанию Excel предлагает функцию СУММ).
• Добавить итоги по. В этом списке перечислены все существующие столбцы. Установите флажок рядом со столбцами, для которых должны вычисляться промежуточные итоги.
• Заменить текущие итоги. Если этот флажок установлен, Excel будет удалять любые существующие формулы промежуточных итогов, заменяя их новыми.
• Конец страницы между группами. Если этот флажок установлен, после каждого промежуточного итога Excel будет вставлять разрыв страницы.
• Итоги под данными. Если этот флажок установлен, Excel по умолчанию помещает промежуточные итоги под самими данными. В противном случае формулы промежуточных итогов размещаются выше данных.
• Убрать все. Данная кнопка позволяет удалить из списка все формулы промежуточных итогов.
Щелкните на ОК, и Excel начнет анализировать записи, вставляя определенные формулы и создавая уникальную схему промежуточных итогов. На рисунке показан рабочий лист после добавления двух наборов промежуточных итогов, один из которых суммирует значения по месяцам, другой — по регионам. Естественно, функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ можно использовать в формулах, создаваемых вручную, однако применение команды Данные→Структура→Промежуточный итог существенно упрощает задачу.
Предупреждение
После добавления промежуточных итогов к отфильтрованному списку и удаления фильтра итоги перестают быть правильными.
При создании дополнительных промежуточных итогов не устанавливайте в диалоговом окне флажок Заменить текущие итоги.
Во всех формулах на этом рабочем листе используется функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Например, формула подсчета продаж в январе выглядит следующим образом:
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;Е2:Е4)
Если формула обращается к двум ячейкам, которые тоже содержат формулу ПРОМЕЖУТОЧНЫЕ.ИТОГИ, во избежание двойного подсчета эти ячейки в расчет не включаются.
Чтобы установить уровень детализации отображаемых значений, воспользуйтесь элементами управления схемой итогов. Например, на рисунке ниже показаны только строки с суммами, которые содержат функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ.
Примечание
В большинстве случаев для обобщения данных лучше применять не функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ, а сводные таблицы. Они более гибкие и не требуют разработки формул. Функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ рекомендуется применять только для решения простых задач.
В начало
Полезное
