Excel промежуточные итоги: Промежуточные итоги в Excel с примерами функций

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

Надстройка
VBA-EXCEL

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

Подробнее…

Возможности Excel » Диапазоны » Промежуточные итоги

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

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

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

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

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

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

Итоги размещаются в автоматически добавляемых новых строках с добавлением функций =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(Номер_функции; Ссылка) [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].

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

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

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

Рекомендуем к прочтению

Фильтрация данных в Excel

Сортировка данных в Excel

Комментарии:

Please enable JavaScript to view the comments powered by Disqus.comments powered by Disqus

Применение функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Excel.

Количество просмотров: 498

Промежуточные функции в Excel и функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ.

Особенности. Назначение промежуточных итогов.

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

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

Использование функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ.

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

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

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

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

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

Номера операций в функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ.

Как, наверное, читатели догадались, результат расчета данной функции зависит от применяемого номера операций, так как непосредственно диапазоны к вариантам расчёта отношения явно не имеют. Что же представляют номера операций? Номер операции – это число, согласно которому для вычисления связанная с ним функция. Всего номеров 22 – от 1 до 11 и до 101 до 111. С каждым из них связана своя функция. Проще всего разобраться с ними, посмотрев на таблицу

Как видно номера операций до 100 и аналогичные им номера выше 100 запускают для расчета одинаковые функции. Различаются они тем, что номера операций выше 100 при расчете не учитывают данные в строках, скрытых не с помощью фильтра, а командой «Скрыть». Для примера найдем минимальную цену на листе «Филиал_Павлодар», предварительно скрыв строки с типом товара «Продукты». Обратите внимание на номера строк в изображении слева.

Как видите, несмотря на отсутствие фильтра, результаты вычисления разные. Во втором случае скрытые строки не учитываются. Еще раз обращаю внимание, что это не относится к строкам, скрытым с помощью фильтра. Такие строки не учитывают оба варианта. В большинстве же случаев скрытие строк вручную не применяется, поэтому на практике вы можете применять любой из возможных вариантов. Например, для расчета суммы указать номер операции 9 или 109, для расчета максимального значения 4 или 104, чтобы найти количество заполненных ячеек указать 103 или 3. Повторяю, в большинстве случаев разницы между этими вариантами не будет. Она появится только тогда, когда вы отмените фильтр и включите скрытие строк вручную через соответствующее выпадающее меню. Если же вы примените команду «Скрыть» для строк, когда фильтр включен, то скрытые строки автоматически добавятся к фильтру, поэтому номера операций до 100 и выше 100 снова дадут одинаковый результат.

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Excel, ее назначение и особенности.

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

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

Подведем итоги.

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

Кроме этого, мы приглашаем вас посетить наши занятия по интересующей вас тематике, где вы получите гораздо больше интересных, а главное – необходимых и полезных навыков по обработке и анализу данных в Excel и работе в других программах, в частности PowerPoint и Project.

Ссылка на учебный материал.

131510cookie-checkПрименение функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Excel.no

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

Excel для Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Дополнительно… Меньше

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

Subtotal .

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

Когда вы вставляете промежуточные итоги:

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

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

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

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

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

2. Выберите ячейку в диапазоне.

3. Выполните одно из следующих действий:

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

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

   1. При каждом изменении в столбце Спорт…

   2. …промежуточный итог столбца «Продажи».

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

      Данные в группе Сортировка и фильтрация щелкните Сортировка от А до Я или Сортировка от Я до А .

   2. На вкладке Данные в группе Структура щелкните Промежуточный итог .

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

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

   4. В поле Использовать функцию щелкните итоговую функцию, которую вы хотите использовать для расчета промежуточных итогов. Например, используя приведенный выше пример, вы должны выбрать Сумма .

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

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

      Разрыв страницы между группами .

   7. Чтобы указать строку сводки над строкой сведений, снимите флажок Сводка под данными . Чтобы указать строку сводки под строкой сведений, выберите Сводка под данными 9.0006 флажок. Например, используя приведенный выше пример, вы должны снять флажок.

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

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

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

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

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

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

   2. Вставьте внешние промежуточные итоги.

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

      1. На вкладке Данные в группе Структура щелкните Промежуточный итог .

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

      2. В При каждом изменении в щелкните столбец для внешних промежуточных итогов. Например, используя приведенный выше пример, вы должны щелкнуть Region .

      3. В поле Использовать функцию щелкните итоговую функцию, которую вы хотите использовать для расчета промежуточных итогов. Например, используя приведенный выше пример, вы должны выбрать Сумма .

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

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

      6. Чтобы указать строку сводки над строкой сведений, снимите флажок Сводка под данными . Чтобы указать строку сводки под строкой сведений, установите флажок Сводка ниже данных . Например, используя приведенный выше пример, вы должны снять флажок.

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

   3. org/ListItem»>

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

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

      1. На вкладке Data в группе Outline щелкните Итого .

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

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

      3. org/ListItem»>

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

         Выберите любые другие параметры, которые вы хотите.

      4. Снимите флажок Заменить текущие промежуточные итоги .

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

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

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

2. На вкладке Данные в группе Структура щелкните Промежуточный итог .

3. В диалоговом окне Промежуточный итог нажмите Удалить все .

Верх страницы

Нужна дополнительная помощь?

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

как вставлять, использовать и удалять

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

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

Что такое промежуточный итог в Excel?

Вообще говоря, промежуточный итог — это сумма набора чисел, которая затем добавляется к другому набору (наборам) чисел для получения общей суммы.

В Microsoft Excel функция «Промежуточный итог» не ограничивается только суммированием подмножеств значений в наборе данных. Это позволяет вам группировать и суммировать ваши данные, используя СУММ, СЧЁТ, СРЕДНЕЕ, МИН., МАКС. и другие функции. Кроме того, он создает иерархию групп, известную как схема, которая позволяет отображать или скрывать сведения о каждом промежуточном итоге или просматривать только сводку промежуточных и общих итогов.

Например, промежуточные итоги Excel могут выглядеть так:

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

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

1. Организуйте исходные данные

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

Итак, прежде чем добавлять промежуточные итоги, обязательно отсортируйте столбец, по которому вы хотите сгруппировать данные. Самый простой способ сделать это — нажать кнопку Filter на Data 9.0344, затем щелкните стрелку фильтра и выберите сортировку от А до Я или от Я до А:

Чтобы удалить пустые ячейки, не испортив данные, следуйте этим рекомендациям: Как удалить все пустые строки в Excel.

2. Добавьте промежуточные итоги

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

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

3. Задайте параметры промежуточного итога

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

• В поле При каждом изменении поле выберите столбец, содержащий данные, по которым вы хотите сгруппировать.
• В поле Use function выберите одну из следующих функций:
  • Сумма — сложите числа.
  • Подсчет — подсчет непустых ячеек (при этом будут вставлены формулы промежуточных итогов с помощью функции СЧЁТЗ).
  • Среднее — вычислить среднее число.
  • Max — вернуть наибольшее значение.
  • Min — вернуть наименьшее значение.
  • Произведение — вычислить произведение ячеек.
  • Подсчет чисел — подсчет ячеек, содержащих числа (при этом будут вставлены формулы промежуточных итогов с помощью функции СЧЁТ).
  • StdDev — расчет стандартного отклонения совокупности на основе выборки чисел.
  • StdDevp — возвращает стандартное отклонение на основе всей совокупности чисел.
  • Var — оценка дисперсии генеральной совокупности на основе выборки чисел.
  • Varp — оценка дисперсии совокупности на основе всей совокупности чисел.
• В разделе Добавить промежуточный итог к установите флажок для каждого столбца, для которого вы хотите получить промежуточный итог.

В этом примере мы группируем данные по столбцу Регион и используем функцию СУММ для суммирования чисел в 9Столбцы 0343 Продажи и Прибыль .

Кроме того, вы можете выбрать любую из следующих опций:

• Чтобы вставить автоматический разрыв страницы после каждой промежуточной суммы, установите флажок Разрыв страницы между группами .
• Чтобы отобразить строку сводки над строкой сведений, снимите флажок Сводка под данными . Чтобы отобразить строку сводки под строкой сведений, установите этот флажок (обычно установлен по умолчанию).
• Чтобы перезаписать любые существующие промежуточные итоги, оставьте Заменить выбранное поле текущих промежуточных итогов , в противном случае очистить это поле.

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

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

Совет. Если промежуточные итоги и общий итог не пересчитываются, обязательно настройте рабочую книгу на автоматический расчет формул ( Файл > Параметры > Формулы > Параметры расчета > Расчет рабочей книги > Автоматически ).

3 вещи, которые вы должны знать о функции промежуточных итогов Excel

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

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

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

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

• 1–11 игнорировать отфильтрованные ячейки, но включать скрытые вручную строки.
• 101 — 111 игнорировать все скрытые строки (отфильтрованы и скрыты вручную).

Функция промежуточного итога Excel вставляет формулы с номерами функций 1–11.

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

Если отфильтровать, скажем, Лимоны и Апельсины , они будут автоматически удалены из промежуточных итогов. Однако если вы скроете эти строки вручную, они будут включены в промежуточные итоги. Изображение ниже иллюстрирует разницу:

Чтобы исключить вручную скрытые строки , чтобы рассчитывались только видимые ячейки, измените формулу промежуточного итога, заменив номер функции 1-11 на соответствующий номер 101-111.

В нашем примере, чтобы просуммировать только видимые ячейки, исключая скрытые вручную строки, измените ПРОМЕЖУТОЧНЫЕ ИТОГОВ( 9 ,C2:C5) в ПРОМЕЖУТОЧНЫЙ ИТОГ( 109 ,C2:C5):

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

2. Общие итоги рассчитываются на основе исходных данных

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

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

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

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

Как добавить несколько промежуточных итогов в Excel (вложенные промежуточные итоги)

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

1. Сортировка данных по нескольким столбцам

При вставке вложенных промежуточных итогов в Excel важно сортировать данные во всех столбцах, по которым вы хотите сгруппировать промежуточные итоги. Для этого перейдите на 9Вкладка 0343 Данные > группа Сортировка и фильтрация , нажмите кнопку Сортировка , и добавьте два или более уровня сортировки:

Подробные инструкции см. в разделе Как сортировать по нескольким столбцам.

В результате значения в первых двух столбцах отсортированы в алфавитном порядке:

2. Вставьте первый уровень промежуточных итогов

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

3. Вставьте вложенные уровни субтотал

с подтолатами 9034. Снова 9034.

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

В поле Использовать функцию выберите нужную сводную функцию.

до 9 лет0343 Добавьте промежуточные итоги к , выберите столбцы, для которых вы хотите рассчитать промежуточные итоги. Это могут быть те же столбцы, что и во внешних промежуточных итогах, или разные столбцы.

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

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

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

В результате Excel рассчитает итоги по каждому элементу в каждом регионе, как показано на снимке экрана ниже:

Для удобства группа Восточный регион расширяется для отображения вложенных промежуточных итогов Item , а 3 другие группы регионов свернуты (в следующем разделе объясняется, как это сделать: Отображение или скрытие промежуточных итогов).

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

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

Например, в нашей таблице-примере, в дополнение к итоговым значениям региона, мы можем отобразить среднее значение для столбцов Продажи и Прибыль :

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

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

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

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

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

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

В нашем образце рабочего листа щелкните цифру 2, чтобы отобразить первую группу по Региону :

Или щелкните цифру 3, чтобы отобразить вложенные промежуточные итоги по Item :

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

Или нажмите кнопки Показать подробности и Скрыть подробности на вкладке Данные в группе Структура .

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

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

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

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

1. Отобразите только те строки промежуточных итогов, которые вы хотите скопировать, используя нумерацию или символы плюс и минус.
2. Выберите любую ячейку промежуточного итога, а затем нажмите Ctrl+A, чтобы выбрать все ячейки.
3. Выбрав промежуточные итоги, перейдите на вкладку Главная > Редактирование группы и нажмите Найти и выбрать > Перейти к специальному…
   
4. В В диалоговом окне «Перейти к специальному » выберите « Только видимые ячейки » и нажмите «ОК».
   

   Наконечник. Вместо использования функции Перейти к специальному вы можете нажать Alt + ; чтобы выбрать только видимые ячейки.

5. На текущем рабочем листе нажмите Ctrl+C, чтобы скопировать выбранные ячейки промежуточных итогов.
6. Откройте другой лист или книгу и нажмите Ctrl+V, чтобы вставить промежуточные итоги.

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

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

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

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

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

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

Как удалить промежуточные итоги в Excel

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

1. Выберите любую ячейку в диапазоне промежуточных итогов.
2. Перейдите на вкладку Данные > Выделите группу и щелкните Итого .
3. В диалоговом окне Промежуточный итог нажмите кнопку Удалить все .

Это разгруппирует ваши данные и удалит все существующие промежуточные итоги.

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