Формула промежуточные итоги в excel как сделать
Перейти к содержимому

Формула промежуточные итоги в excel как сделать

  • автор:

Функция Excel Промежуточные.итоги()

Функция «Промежуточные итоги()» используется для вычисления промежуточного итога (сумма, среднее, количество значений и т.д.) в диапазоне, в котором имеются скрытые сроки.

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

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

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

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

Для этого можно воспользоваться функцией Excel =Промежуточные.итоги(). Функция =Промежуточные.итоги() делает расчет по отфильтрованным значениям.

В формуле можно задать расчет:

  • Средней – номер 1 или 101 – для выборки
  • Счет значений – номер 3 или 103
  • Сумма — 9 или 109
  • И т.д.

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

  • Если номер функции трехзначный, например, 109, то функция работает как промежуточные итоги, т.е. рассчитывает значения по отфильтрованным параметрам.
  • Если номер однозначный или двузначный 2 или 11, то формула промежуточные.итоги() работает, как стандартная формула по выбранной функции.
Перейдем к нашим мероприятиям:

У нас есть выгрузка из базы по следующим столбцам:

  • Ситуация
  • Действия
  • Кол-во клиентов
  • Объём_Продаж_До
  • Объём_Продаж_План
  • Объём_Продаж_Факт
  • Затраты_План
  • Затраты_Факт

Для оценки ситуации в целом введем стандартную формулу суммирования:

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

Для расчета промежуточных итогов по выбранным параметрам введем =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;R[3]C:R[65533]C), где

  • 109 – функция суммы для расчета промежуточных итогов;
  • R[3]C:R[65533]C – ссылка на диапазон суммирования.

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

Теперь, отфильтровав столбец «Ситуация» — «Ситуация 4» и «Действие» — Действие 2 и 3, мы получим перерасчет промежуточных итогов по каждому из показателей и можем их сравнить с показателями «Итого»:

промежуточные итоги в таблице Excel

В результате: видно, что % прироста продаж по отфильтрованным мероприятиям выше, чем по всем, причем планировали результаты получить ниже, чем по итого, а получили выше. Т.е. сработали лучше, чем планировали и лучше чем по всем мероприятиям.

Формула Excel =промежуточные.итоги() – отличный инструмент для расчета показателей по отфильтрованным параметрам.

Если есть вопросы — обращайтесь!

Присоединяйтесь к нам!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

Novo Forecast - прогноз в Excel - точно, легко и быстро!

  • Novo Forecast Lite — автоматический расчет прогноза в Excel .
  • 4analytics — ABC-XYZ-анализ и анализ выбросов в Excel.
  • Qlik Sense Desktop и QlikView Personal Edition — BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

  • Novo Forecast PRO — прогнозирование в Excel для больших массивов данных.

Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

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

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

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

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

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

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

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

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

  1. Убедитесь, что каждый столбец в диапазон данных, для которого требуется вычислить промежуточные итоги, имеет метку в первой строке, содержит аналогичные факты в каждом столбце и что диапазон не содержит пустых строк или столбцов.
  2. Выделите ячейку в диапазоне.
  3. Выполните одно из указанных ниже действий. Вставка одного уровня промежуточных итогов Можно вставить один уровень промежуточных итогов для группы данных, как показано в следующем примере.
  1. Для сортировки столбца, содержащего данные, по которым требуется выполнить группировку, выделите этот столбец и на вкладке Данные в группе Сортировка и фильтр нажмите кнопку Сортировка от А до Я или Сортировка от Я до А.
  2. На вкладке Данные в группе Структура щелкните Промежуточный итог.

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

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

1. При каждом изменении во внешнем столбце Регион.

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

  1. Для сортировки столбца, содержащего данные, по которым требуется выполнить группировку, выделите этот столбец и на вкладке Данные в группе Сортировка и фильтр нажмите кнопку Сортировка от А до Я или Сортировка от Я до А.
  2. Вставьте внешние промежуточные итоги. Вставка внешних промежуточных итогов
    1. На вкладке Данные в группе Структура щелкните Промежуточный итог.

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

    рядом с номерами строк. Используйте символы

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

    Данные итогов в таблице Excel

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

    Браузер не поддерживает видео. Установите Microsoft Silverlight, Adobe Flash Player или Internet Explorer 9.

    Кнопка

    1. Щелкните любое место таблицы.
    2. Выберите Работа с таблицами >Конструктор и установите флажок Строка итогов.

    Таблица Excel со строкой итогов

    Строка итогов будет вставлена в нижней части таблицы.

    Примечание: Если применить в строке итогов формулы, а затем отключить ее, формулы будут сохранены. В приведенном выше примере мы применили функцию СУММ для строки итогов. При первом использовании строки итогов ячейки будут пустыми.

    Пример выбора формулы для строки итогов в раскрывающемся списке

    Выделите нужный столбец, а затем выберите вариант из раскрывающегося списка. В этом случае мы применили функцию СУММ к каждому столбцу:

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

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

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

    Браузер не поддерживает видео. Установите Microsoft Silverlight, Adobe Flash Player или Internet Explorer 9.

    Кнопка

    1. Щелкните любое место таблицы.
    2. Выберите Таблица >Строка итогов.

    Таблица Excel со строкой итогов

    Строка итогов будет вставлена в нижней части таблицы.

    Примечание: Если применить в строке итогов формулы, а затем отключить ее, формулы будут сохранены. В приведенном выше примере мы применили функцию СУММ для строки итогов. При первом использовании строки итогов ячейки будут пустыми.

    Пример выбора формулы для строки итогов в раскрывающемся списке

    Выделите нужный столбец, а затем выберите вариант из раскрывающегося списка. В этом случае мы применили функцию СУММ к каждому столбцу:

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

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

    Вы можете быстро подвести итоги в таблице Excel, включив параметр Переключить строку итогов.

    1. Щелкните любое место таблицы.
    2. Щелкните вкладку Конструктор таблиц >Параметры стилей >Строка итогов. Строка Итог будет вставлена в нижней части таблицы.

    Настройка агрегатной функции для ячейки строки итогов

    Примечание: Это одна из нескольких бета-функций, и в настоящее время она доступна только для части инсайдеров Office. Мы будем оптимизировать такие функции в течение следующих нескольких месяцев. Когда они будут готовы, мы сделаем их доступными для всех участников программы предварительной оценки Office и подписчиков Microsoft 365.

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

    В раскрывающемся списке

    1. Щелкните ячейку в строке итогов под столбцом, который нужно настроить, а затем выберите раскрывающийся список, отображаемый рядом с ячейкой.
    2. Выберите агрегатную функцию, используемую для столбца. Обратите внимание, что вы можете щелкнуть Другие функции, чтобы просмотреть дополнительные параметры.

    Дополнительные сведения

    Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

    Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() EXCEL

    Особенность функции состоит в том, что она предназначена для использования совместно с другими средствами EXCEL: Автофильтром и Промежуточными итогами . См. Файл примера .

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

    ПРОМЕЖУТОЧНЫЕ.ИТОГИ( номер_функции ; ссылка1 ;ссылка2;. ))

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

    Номер_функции (включая скрытые значения) Номер_функции (за исключением скрытых значений) Функция
    1 101 СРЗНАЧ
    2 102 СЧЁТ
    3 103 СЧЁТЗ
    4 104 МАКС
    5 105 МИН
    6 106 ПРОИЗВЕД
    7 107 СТАНДОТКЛОН
    8 108 СТАНДОТКЛОНП
    9 109 СУММ
    10 110 ДИСП
    11 111 ДИСПР

    Например, функция СУММ() имеет код 9. Функция СУММ() также имеет код 109, т.е. можно записать формулу = ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;A2:A10) или = ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;A2:A10). В чем различие — читайте ниже. Обычно используют коды функций от 1 до 11.

    Ссылка1 ; Ссылка2; — от 1 до 29 ссылок на диапазон, для которых подводятся итоги (обычно используется один диапазон).

    Если уже имеются формулы подведения итогов внутри аргументов ссылка1;ссылка2;. (вложенные итоги), то эти вложенные итоги игнорируются, чтобы избежать двойного суммирования.

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

    Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() и Автофильтр

    Пусть имеется исходная таблица.

    Применим Автофильтр и отберем только строки с товаром Товар1 . Пусть функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() подсчитает сумму товаров Товар1 , следовательно будем использовать код функции 9 или 109.

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

    Если бы мы записали формулу = ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;B11:B20) или = ПРОМЕЖУТОЧНЫЕ.ИТОГИ( 103;B11:B20), то мы бы подсчитали число отобранных фильтром значений (5).

    Таким образом, эта функция «чувствует» скрыта ли строка автофильтром или нет. Это свойство используется в статье Автоматическая перенумерация строк при применении фильтра .

    Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() и Скрытые строки

    Пусть имеется та же исходная таблица. Скроем строки с товаром Товар2 через меню Главная/ Ячейки/ Формат/ Скрыть или отобразить или через контекстное меню.

    В этом случае имеется разница между использованием кода функции СУММ() : 9 и 109. Функция с кодом 109 «чувствует» скрыта строка или нет. Другими словами для диапазона кодов номер_функции от 101 до 111 функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() исключает значения строк скрытых при помощи команды Главная/ Ячейки/ Формат/ Скрыть или отобразить . Эти коды используются для получения промежуточных итогов только для не скрытых чисел списка.

    Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() и средство EXCEL Промежуточные итоги

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

    Скроем строки с Товар2 , нажав на соответствующую кнопку «минус» в структуре.

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

    Другие функции

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

    Воспользуемся той же исходной таблицей.

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

    Для подсчета будем использовать формулу массива (см. файл примера , лист2)

    С помощью выражения СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(B10:B19)))-1 в качестве второго аргумента функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ() подается не один диапазон, а несколько ( равного числу строк ). Если строка скрыта, то вместо цены выводится значение Пустой текст «» , которое игнорируется функцией СРГЕОМ() . Таким образом, подсчитывается среднее геометрическое цен товара Товар1 .

    Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() с условием

    Предположим, что у нас есть таблица продаж товаров (см. файл примера, лист3, диапазон А9:С19).

    Автофильтром мы планируем отбирать определенный вид товаров, например, Товар1. Также дополнительно, нам нужно посчитать сумму Продаж отобранных Товаров1, Продажи которых более 50 млн. руб.

    Это сделать легко стандартными средствами. Сначала по столбцу Товар выбираем Товар1.

    Затем настраиваем фильтр по столбцу Продажи.

    И, через 4 клика мышкой имеем:

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

    Очистим фильтр у второго столбца.

    В желтой ячейке Е7 над таблицей введем значение критерия = 50. В другой ячейке запишем довольно длинную формулу:

    В результате в ячейке В7 будет получен тот же результат, что и при применении Автофильтра (92).

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

    Функция СУММПРОИЗВ() имеет аргумент, состоящий из 3х умножаемых.

    1. ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;СМЕЩ($B$10;СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(B10:B19)))-1;))
    2. B10:B19
    3. —((B10:B19)>$E$7)

    Первое умножаемое возвращает массив 0 и 1 вида: . Если строка скрыта фильтром, то возвращается 0, если нет, то 1. Обычно ПРОМЕЖУТОЧНЫЕ.ИТОГИ() возвращает только одно число, а не массив чисел. Чтобы заставить эту функцию вернуть массив понадобилось выражение СМЕЩ($B$10;СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(B10:B19)))-1;), которое возвращает массив чисел из диапазона B10:B19. Если бы мы указали ссылку на этот же диапазон, т.е. ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;B10:B19), то функция вернула бы просто число и мы не получили бы нужный результат. Т.е. передавая в качестве аргумента массив чисел (а не ссылку на диапазон, содержащий этот же массив) мы заставили функцию вернуть массив.

    Второе умножаемое — это ссылка на столбец с продажами. Если в строке формул выделить B10:B19 и нажать F9 , то получим массив

    Третье — возвращает массив . Если в строке значение Продаж больше 50, то возвращается 1, если нет, то 0.

    Функция СУММПРОИЗВ() построчно перемножает 3 полученных массива. В результате получим 92 (т.к. только в 4й строке у нас не скрытая строка и Продажи более 50).

    Теперь, изменив в желтой ячейке критерий, мы сразу же получим нужный результат (4 товара1 удовлетворяют условиям).

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

    Все примеры можно найти в файле (кнопка вверху и внизу статьи).

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

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