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

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

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

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

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

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

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

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

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

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

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

И это можно сделать всего несколькими щелчками мыши.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• В поле «Каждый раз, когда я перехожу на» выберите столбец, по которому вы хотите сгруппировать данные. В нашем случае мы выберем столбец с именами покупателей.
• В списке Использовать функцию выберите один из следующих вариантов:
  • Сумма.
  • Счетчик: подсчет непустых ячеек (при этом будут вставлены формулы промежуточных итогов с функцией СЧЁТ).
  • Среднее: вычислить среднее.
  • Максимум — вернуть наибольшее число.
  • Минимум — получить наименьшее число.
  • Продукт — рассчитайте продукт по столбцу.
  • Подсчет чисел: подсчет ячеек, содержащих числа.
  • Стандартное отклонение — вычисляет стандартное отклонение генеральной совокупности на основе выборки чисел.
  • Беспристрастное отклонение: возвращает стандартное отклонение, основанное на генеральной совокупности чисел.
  • Дисперсия — оценка дисперсии генеральной совокупности на основе выборки чисел.
  • Несмещенная дисперсия — оценка дисперсии генеральной совокупности на основе всей совокупности чисел.
• В разделе «Добавить итоги по» установите флажок для каждого столбца, по которому вы хотите вычислить промежуточные итоги.

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

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

• Чтобы вставить автоматический разрыв страницы после каждого промежуточного итога, установите флажок Разрыв страницы между группами. В результате каждая группа будет распечатана на отдельном листе. Но в большинстве случаев в этом нет необходимости, поэтому этот параметр обычно отключен.
• Чтобы отобразить строку итогов над данными, снимите флажок «Итоги под данными». Этот элемент обычно включен по умолчанию, так как мы еще больше привыкли, когда сначала идут данные, а под ними — итоги.
• Чтобы перезаписать любые уже существующие промежуточные итоги, установите флажок «Перезаписать промежуточные итоги». Если вы изменили данные, старые итоги вам не понадобятся. Но если вы работаете не со всей таблицей, а только с ее частью (о такой возможности мы говорили выше), возможно, нет необходимости удалять то, что уже было подсчитано. Кроме того, если вы не установите этот флажок, вы добавите еще один уровень итогов. Например, вы найдете общий объем продаж для каждой группы и можете добавить несколько продаж или средний размер заказа. Другими словами, для каждой группы могут быть рассчитаны разные итоги.

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

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

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

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

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

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

В результате мы получаем следующую картину:

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

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

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

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

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

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

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

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

В приведенном выше примере промежуточный итог с функцией SUM создает следующую формулу: INTERMEDIATE.TOTALS (9, G2: G91). В нем число 9 представляет функцию СУММ, а G2: G91 — это область ячеек для вычисления промежуточных итогов.

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

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

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

= ПРОМЕЖУТОЧНЫЙ.ИТОГО (9; G2: G6)

 на  

= ПРОМЕЖУТОЧНЫЙ.ИТОГО (109; G2: G6):

 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

А по регионам итоги таковы: 9 продаж на сумму 6714.

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

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

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

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

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

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

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

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

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

Или щелкните номер 3, чтобы просмотреть частичные промежуточные итоги по клиенту:

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

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

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

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

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

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

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

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

Совет. Вместо использования меню на шаге 4 вы можете нажать комбинацию Alt +; для выбора только видимых ячеек..

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

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

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

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

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

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

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

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

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

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

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

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