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

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

В этой статье описаны синтаксис формулы и использование функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Microsoft Excel.

Описание

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

Синтаксис

Аргументы функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ описаны ниже.

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

Function_num
(включая скрытые строки)

Function_num
(игнорирует скрытые строки)

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

Ссылка2;. Необязательный. Именованные диапазоны или ссылки 2—254, для которых требуется вычислить промежуточные итоги.

Примечания

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

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

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

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

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

Пример

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

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

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

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

Синтаксис и использование.

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

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

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ (на английском — SUBTOTAL) доступна во всех версиях Excel 2019, 2016 и ниже.

Синтаксис ее следующий:

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

Аргумент номер_функции может принимать значения:

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

Например, вот как вы можете составить формулу с номером 9 для суммирования значений в ячейках с D2 по D10:

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

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

Особенности формулы ПРОМЕЖУТОЧНЫЕ.ИТОГИ:

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

Теперь, когда вы знаете, как создать формулу промежуточных итогов в Excel, главный вопрос: зачем вообще тратить силы на ее изучение? Почему бы просто не использовать обычные функции, такие как СУММ, СЧЁТ, МАКС и т.д.? Вы найдете ответ ниже.

3 основных причины использовать ПРОМЕЖУТОЧНЫЙ ИТОГ в Excel

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

1. Вычисляет значения с учетом применяемого фильтра.

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

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

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

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

Примечание. Поскольку оба набора номеров функций (1-11 и 101-111) игнорируют отфильтрованные ячейки, в этом случае вы можете использовать формулу с функцией номер 9 или 109.

2. Вычисление только видимых ячеек.

Как вы помните, формулы промежуточных итогов с номером функции от 101 до 111 игнорируют все скрытые ячейки – отфильтрованные и скрытые вручную. Итак, когда вы используете функцию «Скрыть» в Excel для удаления из представления нерелевантных данных, используйте функцию номер 101-111, чтобы исключить значения в скрытых строках из подсчета итогов.

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

Следующий пример поможет вам лучше понять, как это работает: Промежуточный итог 9 против 109.

3. Игнорируются значения во вложенных формулах промежуточных итогов.

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

На снимке экрана ниже формула

игнорирует результаты формул промежуточных итогов в ячейках G7 и G120, как если бы вы использовали функцию СУММ с двумя отдельными диапазонами: СУММ(G2:C6, G8:G11).

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

Пример 1. Промежуточный итог с функцией 9 или 109?

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

Чтобы суммировать отфильтрованные строки, вы можете использовать формулу Промежуточный итог с функцией 9 или с 109, как показано на рисунке ниже:

Но если вы скрыли отдельные элементы вручную,

и теперь хотите суммировать значения только в видимых строках, использовать функцию 109 – это единственный вариант:

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

Примечание. Функция Excel ПРОМЕЖУТОЧНЫЕ.ИТОГИ с номерами 101-111 игнорирует значения в скрытых строках, но не в скрытых столбцах. Например, если вы используете формулу =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;D2:H10) для суммирования чисел в горизонтальном диапазоне (по строке), то скрытие столбца F не повлияет на результат.

Пример 2. Промежуточные итоги с условием.

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

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

=ЕСЛИ(A17=»Сумма»;ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;C2:C16); ЕСЛИ(A17=»Среднее»;ПРОМЕЖУТОЧНЫЕ.ИТОГИ(1;C2:C16);ЕСЛИ(A17=»Минимум»;ПРОМЕЖУТОЧНЫЕ.ИТОГИ(5;C2:C16); ЕСЛИ(A17=»Максимум»;ПРОМЕЖУТОЧНЫЕ.ИТОГИ(4;C2:C16);»»))))

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

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

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

Обратите внимание также на то, что если бы мы использовали для расчёта суммы продаж функцию СУММ, то в этой таблице мы не смогли бы подсчитать продажи по какому-то определённому покупателю или группе покупателей. Пришлось бы для каждого из них использовать функцию СУММЕСЛИ.

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

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

Как видите, при помощи СУММ это сделать невозможно. А использовать СУММЕСЛИ — это каждый раз корректировать условие в формуле либо делать несколько формул «на все случаи жизни». А здесь мы просто отбираем нужное при помощи фильтра. Согласитесь, это гораздо проще и удобнее, нежели писать и затем корректировать громоздкие формулы.

Почему промежуточные итоги не работают? Распространенные ошибки.

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

#ЗНАЧЕН!— аргумент номер_функции не является целым числом от 1 до 11 или от 101 до 111; или любой из аргументов ref содержит трехмерную ссылку сразу на несколько листов вашей рабочей книги.

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

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Excel для промежуточных расчетов

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

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

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

Суммирование только видимых ячеек в фильтре Excel

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

Вид исходной таблицы данных:

Используем фильтр для отбора данных, которые относятся к гитарам марки Ibanez. Для этого выделим всю таблицу или просто перейдите курсором на любую ячейку таблицы и воспользуйтесь инструментом «ДАННЫЕ»-«Фильтр». Теперь исходная таблица имеет следующий вид:

Нажмем на раскрывающийся список в столбце B («Марка товара») и установим флажок только напротив названия «Ibanez»:

После нажатия на кнопку «ОК» таблица примет следующий вид:

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

Вместо этого в ячейке C24 будем использовать следующую функцию:

Аналогично выполним расчет для количества проданных гитар и общей выручки («Сумма). В результате получим:

Для сравнения приведем результаты, полученные с использованием обычной функцией СУММ:

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

Выборочное суммирование ячеек таблицы в Excel

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

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

После нажатия на кнопку «ОК» исходная таблица примет следующий вид:

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

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

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

Вид исходной таблицы данных:

Отфильтруем данные с использованием критериев «джинсы» и указанная в условии дата:

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ не содержит встроенных функций для расчета моды и среднего отклонения. Для расчета моды используем следующую формулу (формула массива CTRL+SHIFT+ENTER):

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

Для расчета среднего отклонения используем похожую формулу:

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

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

Рассматриваемая функция имеет следующую синтаксическую запись:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Программа Microsoft Excel: промежуточные итоги

​Смотрите также​выдает результат по явным​ вид:​ «Данные».​В строке «Добавить​Второй пример​Заполняем это диалоговое​ знают английского языка,​, флажок​ скрыть текущие промежуточные​Число значений данных. Подведения​Var​.​При удалении промежуточных итогов​ промежуточные значения. Допускается​ все группы строк,​ «Дата».​При работе с таблицами,​ и скрытым строкам;​Если свернуть строки в​Важное условие применения средств​ итоги по» поставили​.​

​ окно так.​ ознакомиться с материалами​

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

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

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

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

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

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

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

​ за день. Кроме​ в таблице реализации​ подходит.​ из промежуточных итогов:​ в одной группе.​Нажимаем «ОК». Получилась​ по столбцам «Дата»​ и выбираем из​

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

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

​Среднее​ совокупности по выборке​Отобразить промежуточные итоги для​ в список при​Так как вводить диапазон​Таким образом, можно свернуть​ другие операции, среди​ в которой каждая​ функции:​ «Название» пересчитывается промежуточный​

​ отчета промежуточные итоги​Здесь посчитаны итоги по​ ч. нам нужно​списка) название столбца,​ и грамматические ошибки.​

​Параметры​Можно отобразить или скрыть​Среднее чисел.​ данных.​ внешнего поля строки​

Формула «ПРОМЕЖУТОЧНЫЕ.ИТОГИ»

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

​Max​Примечание:​ и поля столбца.​Выделите ячейку в списке,​ всех случаях удобно,​

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

​Чтобы за каждым промежуточным​ итогов в Excel​ разворачивая разные отделы​ и по датам.​

​ Мы поставили название​– это итоги​нажмите кнопку​Отображение и скрытие общих​Min​ использовать нестандартные функции​Для расчета промежуточных итогов​На вкладке​ по кнопке, расположенной​ общие итоги.​минимум;​ товара за день,​

​ значений».​ итогом следовал разрыв​ возьмем небольшой пример.​ таблицы, можно получить​ Сначала настроим сортировку​ столбца «Отдел», п.​ по разделам, пунктам​

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

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

​Для внешних заголовков строк​ функции суммирования выберите​в группе​ ввода.​ при изменении данных​

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

​Структура​При этом, окно аргументов​ в строчках таблицы,​ выводятся в столбец​ реализации всей продукции,​ по значению «Обеденная​ «Конец страницы между​ продажами определенных товаров:​Третий пример​ таблица.​ по отделам.​Excel​

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

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

​ группа «Амадис»».​ группами».​

​Необходимо подсчитать выручку от​​.​​На закладке «Данные» в​​В строке «Операция»​​настраивает таблицу таким​​Параметры сводной таблицы​​Конструктор​

​Число значений данных, которые​​ структуры можно вывести​​вариант​

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

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

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

В этой статье

​ нужно произвести –​ было быстро выбрать​

​Щелкните вкладку​Макет​ счёт работает так​

​ или ниже их​.​Появится диалоговое окно​ данных. После того,​

Поля строк и столбцов промежуточных итогов

​ вывода промежуточных итогов​ выбираем именно его​ по предприятию. Давайте​

​Формула для среднего значения​​ условие «Итоги под​​ фильтр, то можно​​ так, что после​​ кнопку «Сортировка». Или,​​ сложить (сумма), посчитать​​ и посчитать данные​

​Итоги и фильтры​щелкните стрелку рядом​​ же, как функция​​ элементов либо скрыть​

​Если требуется использовать другую​Промежуточные итоги​

​ как он автоматически​ не через кнопку​ из списка столбцов​​ выясним, как можно​

​ промежуточного итога диапазона​ данными».​ получить однотипные записи​ фильтра, порядковый номер​​ на закладке «Главная»​​ среднее значение, т.д.​​ по определенным разделам,​​, а затем выполните​

​ на ленте, а​ данной таблицы.​ сделать промежуточные итоги​

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

​ по заданному критерию​

​ строк не будет​ в разделе «Редактирование»​ Мы установили –​ пунктам, строкам таблицы,​

​Общие итоги​StDev​ образом:​ более одного типа​Нажмите кнопку​ кликните по кнопке,​ воспользовавшись возможностью вызова​

​Кроме того, нужно установить​

​ в программе Microsoft​

​ использовать одновременно несколько​

​ отбора. Но значения​

​ сбиваться. Смотрите статью​

​ нажимаем на кнопку​

​ провести анализ данных​

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

​ размещенной справа от​ специальной функции через​ галочку, если она​ Excel.​

​Формула для максимального значения​

​ статистических функций. Мы​ придется подсчитывать вручную.​ «Порядковый номер строк​

​ «Сортировка» и выбираем,​

​ нам нужно посчитать​ по разным параметрам.​Данные из источника OLAP​ из следующих команд.​

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

​ неё.​​ кнопку «Вставить функцию».​ не установлена, около​Скачать последнюю версию​

​ где выборка является​​в группе​​и выберите функцию.​​Важно:​​Опять открывается окно аргументов​​ Для этого, предварительно​​ параметра «Заменить текущие​

​ Excel​В сводной таблице можно​

​ «Сумма». Добавим средние​​ инструментом Microsoft Excel​​ фильтра в Excel».​

​ функцию «Настраиваемая сортировка».​​ по каждому отделу.​ по разным разделам,​ следующих действий.​​ столбцов​

​ подмножеством генеральной совокупности.​​Макет​Функции, которые можно использовать​​Данная статья переведена​

​ функции. Если нужно​ кликнув по ячейке,​ итоги». Это позволит​​Но, к сожалению, не​

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

​Включить для строк и​StDevp​щелкните элемент​

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

​ с помощью машинного​

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

​ каждой группе товаров.​

​ итоги».​ в Excel можно​ окно.​ итоги по» устанавливаем​Есть много функций​Промежуточные суммы по отобранным​ столбцов​

​Смещенная оценка стандартного отклонения​

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

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

​ если вы проделываете​

​ наборы данных подходят​

​Снова вызываем меню «Промежуточные​Чтобы функция выдала правильный​ применить для проведения​Здесь настроили сортировку по​ название столбца, данные​

​ фильтром элементам страницы​Включить только для строк​ генеральной совокупности по​.​

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

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

​ разделительных линий в​

​ дате.​ из которого нужно​ такого анализа в​

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

​Описание​​ английский вариант этой​

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

​ «Заменить текущие». В​​ на соответствие следующим​ таблице. Например, нужно,​В этом же​ посчитать. У нас​

​ таблице. Но, функция​​ исключить отфильтрованные элементы​Задание отображения или скрытия​​Var​ действий.​Сумма​ статьи, который находится​ алгоритму, который был​ строки формул.​

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

Отображение и скрытие конечных итогов для отчета целиком

​ общих итогов по​Несмещенная оценка дисперсии для​Выберите вариант​

​Сумма чисел. Эта операция​ здесь, в качестве​​ описан выше. В​

​Открывается Мастер функций. Среди​

​ раз, не дублировать​​ К главным условиям​​ расчета итогов.​​ «Среднее».​​Таблица оформлена в виде​ датам были отчерчены​​ на кнопку «Добавить​​Нажимаем «ОК». Получилась​ это настраивает очень​

​ генеральной совокупности, где​Не показывать промежуточные суммы​

​ используется по умолчанию​

​ обратном случае, кликаем​ списка функций ищем​ многократно запись одних​​ относятся следующие:​

​Чтобы применить другую функцию,​

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

​ ​ выборка является подмножеством​​.​​ для подведения итогов​

​При работе с отчетом​​ по кнопке «OK».​​ пункт «ПРОМЕЖУТОЧНЫЕ.ИТОГИ». Выделяем​

​ и тех же​таблица должна иметь формат​

​ в разделе «Работа​​Функция «ПРОМЕЖУТОЧНЫЕ.ИТОГИ» возвращает промежуточный​

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

​Выберите вариант​​ по числовым полям.​

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

​ написать такую формулу​

Вычисление промежуточных итогов и общих итогов с отфильтрованными элементами или без них

​ линии структуры таблицы.​​ удалить из таблицы.​​Установите или снимите флажок​​На вкладке​​Смещенная дисперсия​​Показывать все промежуточные итоги​​Число​

​ отображать или скрывать​ выделенного диапазона данных,​​ по кнопке «OK».​​Если вы поставите галочку​

​шапка таблицы должна состоять​​ на вкладке «Параметры»​​ или базу данных.​ столбцов.​ в правилах условного​

​ по столбцу «Отдел».​​ Нажимаем на кнопку​У нас такая​

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

​ будут сформированы в​​Открывается окно, в котором​ в пункте «Конец​

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

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

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

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

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

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

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

​– СТАНДОТКЛОНП (стандартное отклонение​ для отбора данных​ только в цветных​ Нажимаем на кнопку​ в Excel​ Мы установили сортировку​

​Примечание:​ столбцов​Установите или снимите флажок​Функции, которые можно использовать​ же, как функция​ элементами или без​ промежуточных итогов: через​дисперсия по выборке;​ как ему удобнее.​ выведение промежуточных итогов.​ вкладка «Итоги и​ по генеральной совокупности);​

​ (в примере –​ ячейках. Какими способами​ «Промежуточные итоги» на​.​

​ Для большинства лиц​

​ строк​, чтобы включить или​Функция​Несмещенная оценка стандартного отклонения​ или столбца в​ формулу. Кроме того,​

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

​ выборке);​ («Сумма»). В поле​ цветные ячейки в​ так.​ Нажимаем на кнопку​ любую ячейку таблицы.​

​. Данная статья была​или оба эти​ исключить новые элементы​Описание​

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

​– ДИСПР (дисперсия по​ «Добавить по» следует​ Excel».​В строке «При каждом​ функции «Промежуточные итоги»​

​ На закладке «Данные»​

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

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

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

​Ссылка 1 – обязательный​ значениям которых применится​ таблице Excel можно​ название столбца «Дата».​

​В появившемся диалоговом​

​ качестве итога: сумма,​ нужно указать ссылку​

​Снимите флажок​ меню «Фильтр».​ для подведения итогов​Смещенная оценка стандартного отклонения​

​Активное поле​ минимальное, среднее, максимальное​

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

​ на тот массив​Как видим, промежуточные итоги​ Поэтому, в поле​ группы «Структура», встроенная​

​ столбцов​ Чтобы быстро отобразить или​Число​ выборке данных.​Параметры поля​

​Автор: Максим Тютюшев​ вы хотите установить​

​ таблице. Кроме того,​ в» выбираем столбец​ таблица.​Особенности «работы» функции:​ таблица приобретает следующий​ «Структура» на вкладке​ сложить данные.​

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

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

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

И вот о чем мы будем говорить:

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

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

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

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

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

Вот тут-то и пригодится инструмент «Промежуточные итоги».

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

И сделать это можно буквально в несколько кликов мышкой.

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

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

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

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

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

Итак, прежде всего обязательно отсортируйте ваши данные по столбцу, по которому вы хотите их сгруппировать. Самый простой способ сделать это – нажать кнопку «Фильтр» на вкладке «Данные», затем щелкнуть стрелку фильтра и выбрать сортировку от А до Я или от Я до А:

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

После этого подготовительную работу можно считать завершенной.

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

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

В этом случае Excel будет обрабатывать все данные в вашей таблице, пока не встретит пустые столбец и строку. То есть, до последней заполненной строки.

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

3. Определите параметры промежуточных итогов.

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

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

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

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

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

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

Примечание. Если этот пересчет не происходит, не забудьте активировать автоматическое вычисление формул ( Файл > Параметры > Формулы> Параметры вычислений > Автоматически).

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

Укажите, какую операцию нужно выполнить. И не забудьте убрать птичку в пункте «Заменить текущие итоги».

В результате получаем вот такую картину:

Как видите, подсчитаны и среднее, и сумма.

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

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

1. В расчётах участвуют только видимые строки.

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

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

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

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

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

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

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

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

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

Как вы видите на скриншоте ниже, итоговая сумма в G13 — это не просто сумма значений начиная с G2 и заканчивая G12. Суммы в G7 и G12 не учитываются.

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

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

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

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

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

1. Добавьте два или более уровней сортировки.

Чтобы сделать это, перейдите в меню Данные и нажмите Сортировка. Затем добавьте уровни сортировки, как это показано на скриншоте ниже. Сначала – по региону. Затем – по коду покупателя.

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

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

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

Теперь нужно добавить второй уровень группировки.

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

Если нужны ещё итоги, снова щелкните Данные > Промежуточные итоги, чтобы добавить ещё один уровень внутренних промежуточных итогов:

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

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

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

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

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

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

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

И по региону итоги выглядят следующим образом: 9 продаж на сумму 6714.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

При вставке подытов:

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

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

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

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

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

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

Выполните одно из указанных ниже действий.

Вставка одного уровня подытогов

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

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

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

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

Отобразилось диалоговое окно «Подытая».

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

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

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

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

Чтобы указать итоговую строку над строкой с подробными данными, разо убедитесь в том, что под данными не указаны данные. Чтобы указать итоговую строку под строкой сведений, выберите поле Итоги под данными. Например, в примере выше этот квадрат можно было бы очистить.

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

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

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

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

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

Вставка внешних подытогов.

Вставка внешних подытогов

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

Отобразилось диалоговое окно «Подытая».

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

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

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

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

Чтобы указать итоговую строку над строкой с подробными данными, разо убедитесь в том, что под данными не указаны данные. Чтобы указать итоговую строку под строкой сведений, выберите поле Итоги под данными. Например, в примере выше этот квадрат можно было бы очистить.

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

Вставка вложенных подытов.

Вставка вложенных подытов

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

Отобразилось диалоговое окно «Подытая».

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

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

Выберите другие нужные параметры.

Чтобы заменить текущие подытки, сберем.

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

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

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

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

В диалоговом окне Subtotal (Подытая) нажмите кнопку Remove All (Удалить все).

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

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

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

history 25 мая 2014 г.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Поля «Итоги» и «Итог» в итоговой

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

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

Выберите элемент строки или поле в столбце сводной таблицы. Убедитесь, что это поле, а не значение.

На вкладке Анализ в группе Активное поле нажмите кнопку Поле Параметры.

Отобразит диалоговое Параметры полей.

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

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

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

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

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

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

Сумма значений. Функция по умолчанию для числовых данных.

Число значений. Действует аналогично функции СЧЁТЗ. Функция по умолчанию для данных, отличных от числовых.

Количество числовых значений. Действует аналогично функции СЧЁТ.

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

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

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

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

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

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

Чтобы быстро отобразить или скрыть текущие итоги, щелкните элемент поля правой кнопкой мыши, а затем установите или снимите флажок Промежуточный итог » «.

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

На вкладке Конструктор в группе Макет нажмите кнопку Промежуточные итоги.

Выполните одно из указанных ниже действий.

Выберите пункт Не показывать промежуточные суммы.

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

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

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

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

Отображение или скрытие общих итогов

Щелкните в любом месте сводной таблицы.

На вкладке Конструктор в группе Макет нажмите кнопку Общие итоги, а затем выберите нужный пункт.

Изменение состояния общих итогов по умолчанию (отображение или скрытие)

Щелкните сводную таблицу.

На вкладке Анализ в группе Таблица нажмите кнопку Параметры.

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

Чтобы отобразить общие итоги, выберите Показать общие итоги для столбцов или Показатьобщие итоги для строк или и то, и другое.

Чтобы скрыть общие итоги, откажите показать общие итоги для столбцов или Показатьобщие итоги для строк или и то, и другое.

Вычисление промежуточных и общих итогов с отфильтрованными элементами и без них

Щелкните в любом месте сводной таблицы.

На вкладке Анализ в группе Таблица нажмите кнопку Параметры.

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

Если вы используете источник данных OLAP, выполните одно из указанных ниже действий.

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

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

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

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

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

Посмотреть видео

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

Выберите элемент строки или поле в столбце сводной таблицы. Убедитесь, что это поле, а не значение.

На вкладке Параметры в группе Активное поле нажмите кнопку Параметры поля.

Отобразит диалоговое Параметры полей.

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

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

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

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

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

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

Сумма значений. Функция по умолчанию для числовых данных.

Число значений. Действует аналогично функции СЧЁТЗ. Функция по умолчанию для данных, отличных от числовых.

Количество числовых значений. Действует аналогично функции СЧЁТ.

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

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

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

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

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

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

Чтобы быстро отобразить или скрыть текущие итоги, щелкните элемент поля правой кнопкой мыши, а затем установите или снимите флажок Промежуточный итог » «.

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

На вкладке Конструктор в группе Макет нажмите кнопку Промежуточные итоги.

Выполните одно из указанных ниже действий.

Выберите пункт Не показывать промежуточные суммы.

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

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

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

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

Отображение или скрытие общих итогов

Щелкните в любом месте сводной таблицы.

На вкладке Конструктор в группе Макет нажмите кнопку Общие итоги, а затем выберите нужный пункт.

Изменение состояния общих итогов по умолчанию (отображение или скрытие)

Щелкните сводную таблицу.

На вкладке Параметры в группе Сводная таблица нажмите кнопку Параметры.

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

Чтобы отобразить общие итоги, выберите Показать общие итоги для столбцов или Показатьобщие итоги для строк или и то, и другое.

Чтобы скрыть общие итоги, откажите показать общие итоги для столбцов или Показатьобщие итоги для строк или и то, и другое.

Вычисление промежуточных и общих итогов с отфильтрованными элементами и без них

Щелкните в любом месте сводной таблицы.

На вкладке Параметры в группе Сводная таблица нажмите кнопку Параметры.

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

Если вы используете источник данных OLAP, выполните одно из указанных ниже действий.

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

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

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

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

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

В Excel в Интернете вы можете просмотреть подытоги и итоги для итогов в книге, в которых они есть. Однако вы не сможете вычитать итоговые или итоговые поля. Для работы с подытвами и итогами необходимо использовать настольную версию. См. в поле «Итоги» и «Итоги» в этой области.

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

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

Группировка строк и столбцов в Excel

Чтобы разгруппировать данные в Excel, выделите сгруппированные строки или столбцы, а затем щелкните команду Разгруппировать.

Как скрыть и показать группы

Подведение итогов в Excel

Команда Промежуточный итог позволяет автоматически создавать группы и использовать базовые функции, такие как СУММ, СЧЁТ и СРЗНАЧ, чтобы упростить подведение итогов. Например, команда Промежуточный итог способна вычислить стоимость канцтоваров по группам в большом заказе. Команда создаст иерархию групп, также называемую структурой, чтобы упорядочить информацию на листе.

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

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

В следующем примере мы воспользуемся командой Промежуточный итог, чтобы определить сколько заказано футболок каждого размера (S, M, L и XL). В результате рабочий лист Excel обретет структуру в виде групп по каждому размеру футболок, а затем будет подсчитано общее количество футболок в каждой группе.

Просмотр групп по уровням

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

Хоть в этом примере представлено всего три уровня, Excel позволяет создавать до 8 уровней вложенности.

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

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

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

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