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

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

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

Необходимо рассчитать доход от реализации отдельных групп товаров. При использовании фильтра можно получить записи одного типа по заданному критерию отбора. Но значения должны быть рассчитаны вручную. Поэтому воспользуемся другим инструментом Microsoft Excel — командой «Промежуточные итоги».

Чтобы функция возвращала правильный результат, проверьте диапазон на следующие условия:

• Таблица выполнена в виде простого списка или базы данных.
• Первая строка — это имена столбцов.
• Столбцы содержат значения одного типа.
• В таблице нет пустых строк или столбцов.

Начнем…

1. Отсортируем диапазон по значению первого столбца — данные одного типа должны быть рядом.
2. Выберите любую ячейку в таблице. Выберите вкладку Данные на ленте. Группа «Структура» — команда «Средние результаты».
3. Заполните диалоговое окно Промежуточные итоги. В поле «При каждом изменении» выберите условие отбора данных (в примере — «Значение»). В поле «Операция» назначьте функцию («Сумма»). В поле «Добавить по» следует отметить столбцы значений, по которым будет использоваться функция.
4. Закройте диалоговое окно, нажав кнопку OK. Исходная таблица имеет следующий вид:

Если скрыть строки в подгруппах (нажать на «минусы» слева от номеров строк), то получим таблицу только из промежуточных итогов:

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

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

Для отображения промежуточных данных НАД группой снимите условие «Итоги по данным».

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

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

Формула «Промежуточные итоги» в Excel: примеры

Функция SUBTOTAL возвращает промежуточный итог в список или базу данных. Синтаксис: номер функции, ссылка 1; ссылка 2;… .

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

1. – СРЗНАЧ (среднее арифметическое);
2. – COUNT (количество ячеек);
3. – COUNT (количество непустых ячеек);
4. – MAX (максимальное значение в диапазоне);
5. – МИН (минимальное значение);
6. – ПРОИЗВЕДЕНИЕ (произведение чисел);
7. – СТАНДОТКЛОН (стандартное отклонение выборки);
8. – STDEV (стандартное отклонение генеральной совокупности);
9. – СУММА;
10. – VASP (отклонение выборки);
11. – VASP (разброс населения).

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

Особенности «работы» функции:

• выдает результат для явных и скрытых строк;
• исключает строки, не включенные в фильтр;
• считает только в столбцах, не подходит для строк.

Давайте используем функцию в качестве примера:

1. Мы создаем дополнительную строку для отображения промежуточных итогов. Например, «сумма выбранных значений».
2. Включим фильтр. Оставим только данные о стоимости «Обеденная группа «Амадис» в таблице»».
3. В ячейку B2 введите формулу: .

Формула среднего значения частичной суммы площади (для времени «Ретро»): .

Формула максимального значения (для спален): .

Читайте также: Как напечатать римские цифры в Экселе

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

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

1. Когда вы создаете сводный отчет, функция автоматического суммирования уже включена для расчета итогов.
2. Для использования другой функции находим группу «Активное поле» в разделе «Работа со сводными таблицами» вкладки «Параметры». Курсор должен находиться в ячейке столбца, к которому должна применяться функция. Нажмите кнопку «Параметры поля». В открывшемся меню выберите «другое». Назначьте желаемую функцию для промежуточных итогов.
3. Чтобы отобразить итоги для отдельных значений, используйте кнопку фильтра в правом углу имени столбца.

В меню «Параметры сводной таблицы» («Параметры» — «Сводная таблица») доступна вкладка «Итоги и фильтры».

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

Условия для использования функции

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

• В поле «Операция» вы часто выбираете случаи,​
• исключает строки, не входящие в подгруппы (нажмите — значения упорядочены — «Сумма», п.​
• Итоги в строке «Когда о товарах, услугах​

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

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

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

1. Отмечаем любую ячейку в таблице, переходим на вкладку «Данные», находим раздел «Структура», нажимаем на него и в открывшемся списке нажимаем «Промежуточный итог”.
2. В результате появится окно, где мы произведем дополнительные настройки согласно нашей задаче.
3. Итак, нам нужно рассчитать ежедневные продажи всех товарных позиций. Информация о дате продажи размещена в одноименной колонке. Исходя из этого, заполните необходимые поля настроек.
   • разверните список для строки «При каждом изменении» и остановите выбор на «Дата”.
   • мы хотим рассчитать общую сумму продаж за день, поэтому для параметра «Операция» выбираем функцию «Сумма”.
   • если бы перед нами стояла другая задача, мы могли бы выбрать другую функцию из четырех, предложенных программой: произведение (умножение), минимум, максимум, количество.
   • кроме того, необходимо указать место вывода для полученных данных. У нас в таблице есть столбец «Продано, в рублях». Его мы укажем для параметра «Добавить итоги после».
   • также следует обратить внимание на пункт «Заменить текущие итоги». Если рядом нет галочки, поставьте ее. В противном случае возникнут проблемы с любыми изменениями и перерасчетом сумм.
   • давайте перейдем к надписи «Конец страницы между группами» и выясним, стоит ли ставить перед ней галочку. Если этот флажок установлен, это повлияет на внешний вид документа при его отправке на принтер. Все блоки в таблице с суммированными промежуточными итогами будут напечатаны на отдельных листах каждый.
   • и, наконец, параметр «Итоги по данным» определяет положение результата по отношению к строкам. Если снять этот пункт, результат будет отображаться над строками. Оба варианта приемлемы, но все же расположение результатов ниже данных привычнее и визуально нагляднее.
   • когда вы закончите с настройками, подтвердите действие, нажав ОК.
4. В результате выполненных действий в таблице отобразятся промежуточные итоги по группам (по датам). На противоположной стороне каждой группы вы можете увидеть значок минуса. При нажатии на него строки в нем схлопываются.
5. При желании можно убрать ненужные данные из поля видимости, чтобы остались только итоги и промежуточные итоги. Нажав кнопку плюс, можно развернуть строки в группах назад.

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

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

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

1. Для начала отмечаем ячейку, в которой должна отображаться сумма счета. Затем щелкните значок «Вставить функцию» (fx) рядом с панелью формул в левой части.
2. Откроется мастер функций. Выбираем вкладку «Полный алфавитный список», находим в предложенном списке функцию «ПРОМЕЖУТОЧНЫЕ ИТОГЫ», ставим на нее курсор и нажимаем ОК.
3. Теперь нужно задать настройки функции. В поле «Номер_функции» введите номер, соответствующий желаемому варианту обработки информации. Всего есть одиннадцать вариантов:
   • цифра 1 — вычисление среднего арифметического
   • цифра 2 — считает количество ячеек
   • цифра 3 — подсчитывает количество заполненных ячеек
   • цифра 4 — определение максимального значения в выбранной матрице данных
   • цифра 5 — определение минимального значения в выбранной матрице данных
   • цифра 6 — умножение данных в ячейках
   • цифра 7 — обозначает стандартное отклонение выборки
   • число 8 — определить стандартное отклонение генеральной совокупности
   • цифра 9 — расчет суммы (видно в нашем варианте по заданию)
   • число 10 — найти дисперсию в выборке
   • число 11 — найти дисперсию генеральной совокупности
4. В поле «Опорная 1» введите координаты области, для которой вы хотите рассчитать итоги. Всего можно указать до 255 областей. После ввода координат первой ссылки появляется строка для добавления следующей. Вводить координаты вручную не очень удобно, к тому же велика вероятность ошибиться. Поэтому просто поместите курсор в поле для ввода информации, а затем щелкните левой кнопкой мыши в нужной области данных. Кроме того, вы можете добавить следующие ссылки, если это необходимо. Когда закончите, подтвердите настройки, нажав кнопку OK.
5. В результате в ячейке с формулой отобразится результат подсчета промежуточного итога.

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

= ПРОМЕЖУТОЧНЫЕ ИТОГОВ(количество обработок, координаты ячейки)

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

Порядок создания промежуточных итогов

Выполните следующие действия, чтобы создать промежуточные итоги:

1. Предварительно отсортируйте таблицу по столбцу, по которому должны суммироваться результаты.
2. Выберите любую ячейку в таблице.
3. Выберите вкладку «Данные» Данные, затем найдите группу кнопок «Структура» и нажмите команду «Промежуточный итог Промежуточный итог.

1. В появившемся диалоговом окне в раскрывающемся списке При каждом изменении: При каждом изменении выберите столбец, значения которого будут использоваться для сложения промежуточных итогов.
2. В раскрывающемся списке Использовать функцию операции выберите функцию для расчета промежуточных итогов.
3. В списке Добавить промежуточный итог по: Добавить промежуточный итог в выберите все поля, для которых вы хотите выполнить суммирование.
4. ХОРОШО.

Итоги размещаются в автоматически добавляемых новых строках с добавлением функций =ПРОМЕЖУТОЧНЫЙ ИТОГ(Номер функции; Ссылка) [ПРОМЕЖУТОЧНЫЙ ИТОГ(Номер функции; Ссылка)]. Эта функция имеет два параметра:

• [Номер_функции] — номер в фиксированном списке, который Excel предлагает для подсчета итогов (11 функций).
• [Ссылка] — интервал исходных данных для расчета итогов.

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

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

1. Кнопки уровня, отмеченные цифрами
   , находится в верхнем ряду. Они указывают организационный уровень в таблице. Нажав на кнопку уровня, вы можете скрыть все итоги для этого уровня.
2. Кнопки [Скрыть] детали
   стоять рядом с линиями промежуточных итогов. Они отмечены знаком «-». При нажатии такой кнопки скрывается группа исходных записей, по которым суммировались результаты, и отображается только итоговая строка.
3. Кнопки [Просмотрет]ь подробности
   стоять рядом с линиями промежуточных итогов. Они отмечены знаком «+». При нажатии такой кнопки над панелью сводки отображается группа исходных записей.

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

1. Если вам нужно рассчитать другие промежуточные итоги, вам следует снять флажок Заменить текущие промежуточные итоги [Replace current промежуточные итоги] и повторить шаги, описанные выше, для расчета одного промежуточного итога.
2. Если вам нужно добавить промежуточный итог к существующим, снимите флажок Заменить текущие промежуточные итоги и повторите шаги, описанные выше, для расчета промежуточного итога.
3. Если вы хотите очистить все итоги, просто нажмите кнопку «Очистить все.

Многоуровневые итоги

Если вы хотите просуммировать сразу по нескольким полям (например, по столбцу с названием товара, а затем внутри каждого товара по поставщикам), необходимо:

1. Выполнять многоуровневую сортировку (например, сначала по наименованию товаров, а затем уже внутри товаров по поставщикам).
2. Выполните суммирование, как описано выше, для поля, задающего самый высокий уровень сортировки (например, для поля «Название продукта).
3. Снимите флажок «Заменить текущие промежуточные итоги.
4. Выберите параметры для суммирования следующего уровня (например, по полю «Поставщик).
5. Повторите шаг 4 для всех уровней.

Как сделать несколько результатов

Для этого вам необходимо выполнить следующие шаги.

1. Допустим, у вас уже есть какое-то подведение итогов. Мы будем использовать наш пример. Сделайте любую ячейку активной. Затем нажмите на инструмент структуры».
2. После этого снова выбираем известный нам элемент.
3. Затем выберите другую операцию для расчета. Обязательно снимите флажок «Заменить текущие итоги».
4. Только после этого можно нажать на кнопку «ОК».
5. Благодаря этому вы увидите, что в таблице теперь больше промежуточных значений.

Как убрать промежуточные итоги

Процесс почти такой же, как и при создании.

1. Выделите весь диапазон ячеек. Нажмите на пункт меню «Структура».
2. Выберите указанный инструмент
3. Нажмите кнопку «Удалить все».
4. В результате редактор Excel примет свой прежний вид.

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

Расчёты при помощи функции

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

Для этого достаточно сделать несколько простых шагов.

1. Переместитесь в любую ячейку. Нажмите на значок «Фекс». В окне «Вставить функцию» выберите вкладку «Полный алфавитный список». Найдите там нужный товар. Нажмите на кнопку «ОК».
2. После этого появится окно с просьбой ввести аргументы функции.

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

Обратите внимание, что здесь используется формула расчета обычной суммы. Функция СУММЕСЛИ отсутствует в этом списке.

1. Возьмем в качестве примера цифру 9.
2. Затем нужно кликнуть по второму полю, иначе выделенный диапазон ячеек будет заменен в первой строке и это вызовет ошибку.
3. Затем выделите некоторые значения. В нашем случае мы рассчитываем продажи первого продукта. В конце нажмите «ОК».
4. Результат следующий.

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