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

Функция вычисления промежуточных итогов в Excel – это простой и удобный способ анализа данных определенного столбца или таблицы.

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

Кстати, чтобы эффективнее работать с таблицами можете ознакомиться с нашим материалом Горячие клавиши Excel — Самые необходимые варианты.

Содержание: 

Листы документа Excel содержат большое количество информации. Часто просматривать и искать данные неудобно.

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

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

Обобщить несколько групп листа можно с помощью функции «Промежуточные итоги».

Рассмотрим детальнее принцип работы этой функции и её реализацию во всех версиях табличного процессора Excel.

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

В программе Excel опция отображается в виде ПРОМЕЖУТОЧНЫЕ.ИТОГИ (№; ссылка 1; ссылка 2; ссылка3;…;ссылкаN), где номер – обозначение функции, ссылка – столбец, по которому подводится итог.

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

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

Синтаксис опции итогов указан в таблице:

Синтаксис

Действие

1-СРЗНАЧ

Определение среднего арифметического двух и более значений.

2-СЧЁТ

Расчет количества чисел, которые представлены в списке аргументов.

3-СЧЁТЗ

Подсчёт всех непустых аргументов.

4-МАКС

Показ максимального значения из определяемого набора чисел.

5-МИН

Показ минимального значения из определяемого набора чисел.

6-ПРОИЗВЕД

Перемножение указанных значений и возврат результата.

7-СТАНД ОТКЛОН

Анализ стандартного отклонения каждой выборки.

8-СТАД ОТКЛОН П

Анализ отклонения по общей совокупности данных.

9-СУММ

Возвращает сумму выбранных чисел.

10-ДИСП

Анализ выборочной дисперсии.

11-ДИСПР

Анализ общей дисперсии.

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

Пример реализации промежуточных итогов

Выполнение промежуточных итогов включает в себя такие операции:

  • Группировка данных;
  • Создание итогов;
  • Создание уровней для групп.

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

Группировка таблиц

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

Следуйте инструкции, чтобы сгруппировать строки. В нашем случае, мы связываем столбцы А, В, С:

1 С помощью мышки выделите столбцы А, В, С, как показано на рисунке ниже. 
Рис.2 – выделение нескольких столбцов

Рис.2 – выделение нескольких столбцов

 

2 Теперь, сохраняя выделение, откройте на панели инструментов программы поле «Данные». Затем в правой части окна опций найдите иконку «Структура» и нажмите на неё; 

3В выпадающем списке нажмите на «Группировать». Если вы ошиблись на этапе выделения нужных столбиков таблицы, нажмите на «Разгруппировать» и повторите операцию заново. 
Рис.3 – группировка информации

Рис.3 – группировка информации

4 В новом окне выберите пункт «Строки» и нажмите на «ОК»;
Рис. 4 – группировка строк

Рис. 4 – группировка строк

5 Теперь снова выделите столбцы А, В, С и проведите группировку, но уже по столбцам: 
Рис.5 – создание группы столбцов

Рис.5 – создание группы столбцов

В результате выполнения действия, все выбранные ранее строки образуют новую группу элементов. Возле столбцов и строк появится панель управления группами. С её помощью вы сможете скрыть детали или же просмотреть скрытые элементы. Для этого нажимайте на знаки «-» и «+» слева от строчек или сверху от столбиков. 
Рис.6 – панель управления группами

Рис.6 – панель управления группами

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

Опция промежуточных итогов, благодаря использованию групп и стандартных одиннадцати функции (таблица 1), позволяет быстро проводить итоги по заполненным данным таблицы.

Рассмотрим практическое использование этой функции.

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

Результатом выполнения действия будет появление групп, которые отсортированы по каждому размеру вещи. Следуйте инструкции:

  • Определите, для каких данных нужно проводит итоговые вычисления. В нашем случае это столбец «Размер». Его содержимое нужно отсортировать. Сортировка проводится от большего элемента к меньшему. Выделяем столбец «Размер»;

  • Далее необходимо перейди во вкладку «Данные» на панели инструментов;

  • Найдите поле «Сортировка» и нажмите на него;

  • В появившемся окне выберите пункт «Сортировать в диапазоне указных значений» и нажмите на клавишу «ОК».

Рис.7 – сортировка содержимого столбца

Рис.7 – сортировка содержимого столбца

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

Рис.8 – результат сортировки

Рис.8 – результат сортировки

Теперь перейдем к реализации функции «Промежуточный итог»:

  • На панели инструментов программы откройте поле «Данные»;
  • Выберите плитку «Структура»;
  • Нажмите на «Промежуточные итоги»;

Рис.9 – расположение опции на панели инструментов

Рис.9 – расположение опции на панели инструментов

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

  • В поле «Операция» выберите тип функции, которая применяется ко всем элементам выбранного в первом поле столбца. Для просчета размеров необходимо использовать подсчет количества;

  • В писке «Добавить итоги по» вы выбираете тот столбец, в котором будут отображаться промежуточные итоги. Выбираем пункт «Размер»;

  • После настройки всех параметров еще раз перепроверьте введённые значения и нажмите на ОК.

Рис.10 – обработка результата

Рис.10 – обработка результата

В результате, на листе отобразится таблица в сгруппированном по размерам виде. Теперь очень легко посмотреть все заказанные вещи размера Small, Extra Large и так далее.

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

Рис.11 – результат выполнения функции

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

Итог для одежды с размером Small – 5 штук, для одежды с размером Extra Large – 2 штуки. Под таблицей показывается и общее количество элементов.

к содержанию ↑

Уровни групп

Просматривать группы можно еще и по уровням.

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

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

Рис.12 – просмотр второго уровня

Рис.12 – просмотр второго уровня

 Первый уровень отображает только общее количество вещей, второй – количество каждого размера и итоговое число, третий – разворот всех данных листа. 
к содержанию ↑

Удаление итогов

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

Старые промежуточные итоги и тип их расчета будет неактуален, поэтому их нужно удалить.

Чтобы удалить итог, выберите вкладку «Данные»«Структура»«Промежуточные итоги».

В открывшемся окне нажмите на клавишу «Убрать все» и подтвердите действие кнопкой «ОК».

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

Рис.13 – удаление данных

Тематические видеоролики:

Развитие цифровых технологий требует непрестанного получения новых знаний и расширения кругозора. Останавливаться на достигнутом нельзя ни на миг. https://immkgh.livejournal.com/2819.html https://www.last.fm/ru/user/immkgh/ http://www.hdclub.ua/member/immkgh

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

Такой e-mail уже зарегистрирован. Воспользуйтесь формой входа или введите другой.

Вы ввели некорректные логин или пароль

Извините, для комментирования необходимо войти.
Geek-Nose