Как использовать функцию ПРОМЕЖУТОЧНЫЙ ИТОГ в Microsoft Excel

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

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

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

СВЯЗАННЫЕ С: Как использовать функцию COUNT в Microsoft Excel

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

Используйте функцию ПРОМЕЖУТОЧНЫЙ ИТОГ Excel

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

Синтаксис формулы: SUBTOTAL(function_number, reference 1, reference 2,...) где требуются первые два аргумента. При необходимости вы можете использовать дополнительные ссылки на ячейки или именованные диапазоны для остальных аргументов.

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

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

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

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

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

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

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

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

СВЯЗАННЫЕ С: Как суммировать столбец в Microsoft Excel

В другом примере мы будем использовать функцию среднего, чтобы включить скрытые строки 3 и 4 в эту формулу:

 =ПРОМЕЖУТОЧНЫЙ ИТОГ(1,C2:C6) 

А затем мы исключим скрытые строки с помощью этой формулы:

 =ПРОМЕЖУТОЧНЫЙ ИТОГ(101,C2:C6) 

Примечания к функции ПРОМЕЖУТОЧНЫЙ ИТОГ

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

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

Используйте функцию промежуточного итога

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

СВЯЗАННЫЕ С: Как рассчитать рабочие дни с помощью функции в Microsoft Excel

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

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

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

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

При желании установите флажки внизу для дополнительных элементов, которые вам нравятся. Нажмите «ОК».

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

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

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

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

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

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

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

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

СВЯЗАННЫЕ С : Как преобразовать таблицу в диапазон и наоборот в Microsoft Excel

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