Vvmebel.com

Новости с мира ПК
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

Функция ПРОМЕЖУТОЧНЫЕ ИТОГИ в Excel

Здравствуйте, друзья. Сегодня расскажу отличный способ, как посчитать промежуточные итоги в таблице Excel, получив приятное преимущество. Изложенный здесь материал обязателен для продвинутых пользователей, поэтому, читаем до конца!

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

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

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(функция ; ссылка 1 ; [ссылка 2] ; … ; [ссылка n])

Её аргументы:

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

Предусмотрены такие функции:

Функция Excel

Код, когда скрытые строки учитываются

Код, когда скрытые строки НЕ учитываются

Описание

Количество ячеек, содержащих числа

Количество непустых ячеек

Максимальное в массиве

Минимальное в массиве

Произведение всех элементов

Стандартное отклонение по выборке

Стандартное отклонение по генеральной совокупности

Сумма всех элементов

Дисперсия по выборке

Дисперсия по генеральной совокупности

Тогда формулу суммирования можно записать так:

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

Так в чём преимущество, если функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ выглядит сложнее? Я приведу несколько:

  1. Для интерактивных таблиц, где клиент сам указывает, какой показатель вывести на экран, эта функция позволяет легко организовать изменение показателя в пределах ячейки.
  2. Используется лишь одна функция, в которой достаточно изменить один код, чтобы пересчитать все итоги
  3. Самое важное: функцию можно настроить на отображение итогов лишь для видимых ячеек!

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

В январе эта величина составила 485 шт.

Обратите внимание, код функции – 1, т.е. «среднее значение, скрытые ячейки учитываются». Теперь скроем строки 5-10 и убедимся, что среднее значение в январе осталось таким же, 485.

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

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

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

Это всё, что я планировал рассказать. Делитесь статьёй с друзьями, если она вам понравилась, а если возникли сложности – пишите комментарии!

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

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

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

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

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

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

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

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

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

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

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

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

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

Читать еще:  Функция если несколько условий в excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Функция «Промежуточные итоги» в Microsoft Excel

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

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

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

  • Таблица должна иметь формат обычной области ячеек;
  • Шапка таблицы должна состоять из одной строки и размещаться на первой строке листа;
  • В таблице не должно быть строк с незаполненными данными.

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

Переходим к самому процессу. За использование этого инструмент отвечает отдельный раздел, вынесенный на верхнюю панель программы.

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

  • Откроется окно, в котором нужно настроить выведение промежуточных итогов. В нашем примере нам надо просмотреть сумму общей выручки по всем товарам за каждый день. Значение даты расположено в одноименной колонке. Поэтому в поле «При каждом изменении в» выбираем столбец «Дата».
  • В поле «Операция» выбираем значение «Сумма», так как нам требуется подбить именно сумму за день. Кроме суммы доступны многие другие операции, среди которых можно выделить: количество, максимум, минимум, произведение.
  • Так как значения выручки выводятся в столбец «Сумма выручки, руб.», то в поле «Добавить итоги по», выбираем именно его из списка столбцов таблицы.
  • Кроме того, надо установить галочку, если ее нет, около параметра «Заменить текущие итоги». Это позволит при пересчете таблицы, если вы проделываете с ней процедуру подсчетов промежуточных итогов не в первый раз, не дублировать многократно запись одних и тех же итогов.
  • Если поставить галочку в пункте «Конец страницы между группами», при печати каждый блок таблицы с промежуточными итогами будет распечатываться на отдельной странице.
  • При добавлении галочки напротив значения «Итоги под данными» промежуточные итоги будут устанавливаться под блоком строк, сумма которых в них подбивается. Если же снять галочку, тогда они будут показываться над строками. Для большинства удобнее размещение под строками, но сам выбор сугубо индивидуален.
  • По завершении жмем на «OK».

    Читать еще:  Расчет промежуточных итогов в excel

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

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

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

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

    Откроется «Мастер функций», где среди списка функций ищем пункт «ПРОМЕЖУТОЧНЫЕ.ИТОГИ». Выделяем его и кликаем «OK».

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

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

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

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

  • Синтаксис самой функции выглядит следующим образом: ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер_функции;адреса_массивов_ячеек) . В нашей ситуации формула будет выглядеть так: «ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;C2:C6)». Эту функцию, используя этот синтаксис, можно вводить в ячейки и вручную, без вызова «Мастера функций». Только важно не забывать перед формулой в ячейке ставить знак «=».
  • Итак, существует два основных способа формирования промежуточных итогов: через кнопку на ленте и через специальную формулу. Кроме того, пользователь должен определить, какое именно значение будет выводиться в качестве итога: сумма, минимальное, среднее, максимальное значение и т.д.

    Отблагодарите автора, поделитесь статьей в социальных сетях.

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

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

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

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

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

    1. В появившемся диалоговом окне в выпадающем списке При каждом изменении в: [At each change in] отметить столбец, по значениям которого будут добавлены промежуточные итоги.
    2. В выпадающем списке Операция [Use function] выбрать функцию для расчета промежуточных итогов.
    3. В списке Добавить итоги по: [Add subtotal to] отметить все поля, для которых нужно подвести итоги.
    4. ОК.

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

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

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

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

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

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

    1. Если нужно вычислить другие промежуточные итоги , то следует убрать флажок в строке Заменить текущие итоги [Replace current subtotals] и повторить описанные выше действия по расчету одного промежуточного итога.
    2. Если нужно к имеющимся добавить еще один промежуточный итог, следует убрать флажок в строке Заменить текущие итоги [Replace current subtotals] и повторить описанные выше действия по расчету промежуточного итога.
    3. Если нужно удалить все итоги, достаточно щелкнуть по кнопке Убрать все [Remove All].
    Читать еще:  Как чертить таблицы в excel

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

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

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

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

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

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

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

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

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

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

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

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

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

    Ссылка на основную публикацию
    Adblock
    detector