Эксель промежуточные итоги пример

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

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

Судя по названию, формула подводит итоги (т.е. суммирует) нужные нам показатели. Чтобы было нагляднее давайте представим себе следующую таблицу.

Если мне по каким-либо причинам понадобится скрыть отдельные строки (фильтр, к примеру) обычная формула автосуммы (СУММ), всё равно будет включать даже скрытые значения.

А что делать если я хочу сделать отбор только по контрагенту «Юлмарт» и посчитать итоги только по нему? Вот здесь и пригодится формула промежуточных итогов.

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

Взглянем на формулу — =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;[Сумма]). Нас особенно будут интересовать аргументы в скобках, так как название формулы, думается, пояснять не стоит.

В скобках мы видим загадочную цифру 109 и слово Сумма. Здесь тоже ничего загадочного нет, так как взглянув на справку от компании Майкрософт всё тут же станет ясно.

Цифра 109 обозначает что нужно делать, если включён фильтр (в названии столбца с сотнями так и написано — «с исключением скрытых значений» если же нам нужно «с включением скрытых значений» просто заменим на нужный элемент из таблицы). Если бы мне понадобилось другое значение, к примеру, 104 — максимальное значение в таблице, я просто могу от руки поправить формулу или просто выбрать её из выпадающего списка.

Видите, мне тут же покажет максимальную сумму продажи другому контрагенту — «Топкомпьютер». Аналогично можно попробовать и минимальное значение.

Также можно исправлять всё не вручную, а использовать открывающийся список после нашей ячейки с формулой.

Если же вы всё-таки решили исправлять руками, программа не оставит вас без подсказок.

Вот и всё! Функция ПРОМЕЖУТОЧНЫЕ ИТОГИ поможет вам быстро производить расчёты даже при скрытых данных!

Если что-то осталось непонятным — смотрите наше новое видео! А вот ССЫЛКА на таблицу из статьи! Наша ПРОШЛАЯ СТАТЬЯ про функцию «Заменить»!

Подвести промежуточные итоги в таблице Excel можно с помощью встроенных формул и соответствующей команды в группе «Структура» на вкладке «Данные».

Читайте также:  Pokemon go не работает камера

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Функция «ПРОМЕЖУТОЧНЫЕ.ИТОГИ» возвращает промежуточный итог в список или базу данных. Синтаксис: номер функции, ссылка 1; ссылка 2;… .

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

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

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

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

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

Рассмотрим на примере использование функции:

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

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

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

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

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

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

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

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

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

Использование функции «Промежуточные итоги» в Excel

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

  • Строчки таблицы обязаны быть формата обычной области ячейки;
  • Верхняя часть таблицы обязана быть выполненной только из единственной строки и находиться на первой строчке листа;
  • Таблица ни в коем случае не должна быть пустой.
Читайте также:  Что делать если наушники хрипят

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

Теперь перейдем непосредственно к самому главному — применению функции на деле. Отдел, отвечающий за "Промежуточные итоги", находится в верхнем меню Excel.

Определите с помощью мыши необходимую область, после чего смотрим на раздел "Данные". Находим там блок под названием "Структура" и выбираем в небольшом списке последнюю строчку — "Промежуточные итог".

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

Теперь переходим в поле "Операция" и указываем значение "Сумма" за каждые сутки, которое необходимо нам вывести. Если покопаться, то вы можете так же там найти настройку значений: максимум, произведение, количество, минимум.

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

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

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

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

Кликаем по кнопке "ОК" и завершаем настройку.

После завершения вышеуказанного алгоритма действий вы придете к тому, что в таблице появятся промежуточные итоги ваших продаж. Хотим обратить ваше внимание к тому, что вы пожеланию можете скрыть все строки соединенные промежуточным итогом, всего лишь нажав ПКМ на иконку "-", находящуюся в левой части таблицы оной группы.

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

Оставьте ответ

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