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

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

Главная » Excel

Автор admin На чтение 4 мин Просмотров 526 Опубликовано 05.04.2018

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

При этом есть обязательные условия:

• наименования должны повторяться больше одного раза;
• записи должны быть расположены в один столбец.

Рассмотрим каждый способ более детально.

Содержание

1. Как вычислить промежуточные итоги в Excel
2. Как сделать несколько результатов
3. Как убрать промежуточные итоги
4. Расчёты при помощи функции
5. Использование сводной таблицы Excel
6. Заключение
7. Видеоинструкция

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

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

1. Создайте таблицу, которая соответствует требованиям, описанным выше.

2. Выделите весь диапазон значений. Используйте иконку сортировки. Выберите инструмент «от А до Я».

3. Благодаря этому все клетки будут отсортированы по наименованию товара.

4. После этого сделайте активной какую-нибудь клетку (необязательно выделять всё целиком). Перейдите на вкладку «Данные». Используйте инструмент «Структура».

5. В появившемся меню выберите указанную кнопку.
6. Далее укажите нужную операцию. Чтобы продолжить, нажмите на «OK».

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

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

Если этот фильтр вам не нравится, вы можете вернуть всё обратно при помощи кнопок с символом «+».

Вложенность имеет несколько уровней. Можно свернуть всю информацию целиком.

Как сделать несколько результатов

Для этой цели потребуется выполнить следующие шаги.

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

2. После этого снова выбираем знакомый нам пункт.

3. Далее выбираем какую-нибудь другую операцию для расчета. Обязательно уберите галочку возле пункта «Заменить текущие итоги».

4. Только после этого можно нажать на кнопку «OK».

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

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

Процесс происходит почти так же, как при создании.

1. Выделите весь диапазон клеток. Нажмите на пункт меню «Структура».

2. Выберите указанный инструмент.

3. Кликните на кнопку «Убрать всё».

4. В результате этого редактор Эксель примет свой прежний вид.

Таким способом вы сможете удалить любое количество промежуточных подсчетов.

Расчёты при помощи функции

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

Для этого достаточно совершить несколько простых действий.

1. Перейдите на любую клетку. Нажмите на иконку «Fx». В окне «Вставка функции» выберите категорию «Полный алфавитный перечень». Найдите там нужный пункт. Кликните на кнопку «OK».

2. После этого появится окно, в котором вас попросят указать аргументы функции.

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

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

3. В качестве примера поставим цифру 9.

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

5. Затем выделите какие-нибудь значения. В нашем случае посчитаем сумму продаж первого товара. В конце кликаем на «OK».

6. Результат будет следующим.

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

Использование сводной таблицы Excel

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

1. Укажите нужный диапазон.
2. Перейдите на вкладку «Вставка».
3. Используйте инструмент «Таблица».
4. Выберите нужный пункт.

5. Подберите что-то самое оптимальное для вас. Количество вариантов зависит от выделенной информации.
6. После этого кликните на «OK».

7. По умолчанию редактор Excel попробует сложить все значения в таблице.

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

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

4. Вследствие этого вы увидите следующее:

Заключение

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

Видеоинструкция

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

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

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

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

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

1. Что такое функция ПРОМЕЖУТОЧНЫЙ ИТОГ и для чего она используется?
2. Как работает функция ПРОМЕЖУТОЧНЫЙ ИТОГ в Microsoft Excel?
3. На что следует обратить внимание перед использованием ПРОМЕЖУТОЧНЫХ ИТОГОВ в Excel
4. Как использовать ПРОМЕЖУТОЧНЫЕ ИТОГОВ в формуле Excel: пошаговое руководство

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

1. Что такое функция ПРОМЕЖУТОЧНЫЙ ИТОГ и для чего она используется?

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

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

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

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

2. Как работает функция ПРОМЕЖУТОЧНЫЕ ИТОГО в Microsoft Excel?

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

Формула ПРОМЕЖУТОЧНЫЙ ИТОГ легко создается с использованием следующей структуры:

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

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

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

• 1 или 101: СРЕДНИЙ
• 2 or 102: COUNT
• 3 or 103: COUNTA
• 4 or 104: MAX
• 5 or 105: MIN
• 6 or 106: PRODUCT
• 7 или 107: Stdev
• 8 или 108: Stdevp
• или 109: SUM
• или 109:
• 70004 или 10000:
• 9000 3 70004 или 10000:
• 9000 3 70004 или 109: 9000 3 или 10000: 9
• 9000 3 или 109: 110003 10000: или . 0004
• 11 или 111: VARP

Различия в числе определяют, включает ли ПРОМЕЖУТОЧНЫЙ ИТОГ или игнорирует скрытые числа. Использование чисел 1-11 позволяет ПРОМЕЖУТОЧНЫЙ ИТОГ включать числа из скрытых строк или столбцов, а использование 101-111 позволяет исключить их.

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

3. На что следует обратить внимание перед использованием ПРОМЕЖУТОЧНЫХ ИТОГОВ в Excel

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

• Как уже упоминалось, использование 1-11 для аргумента номер_функции гарантирует, что ПРОМЕЖУТОЧНЫЙ ИТОГ включает скрытые значения, в то время как 101-111 игнорирует их.
• Использование значения, отличного от 1-11 или 101-111 , приведет к тому, что Excel вернет #ЗНАЧ! Ошибка. Это также произойдет для ссылок на 3D-ячейки (где на одну и ту же ячейку на нескольких листах ссылаются в диапазоне).
• Когда используются горизонтальные диапазоны ячеек, такие как A1:D1 , скрытые значения включаются автоматически (независимо от значения аргумента function_num ). Это ограничение функции ПРОМЕЖУТОЧНЫЕ ИТОГО, и его нельзя преодолеть.
• Использование ПРОМЕЖУТОЧНЫХ ИТОГОВ для отфильтрованных данных гарантирует, что скрытые значения всегда игнорируются, независимо от используемого аргумента номер_функции .
• ПРОМЕЖУТОЧНЫЙ ИТОГ может использоваться как вложенная функция в составе других функций, включая формулы, содержащие функцию ЕСЛИ .
• Если другая формула ПРОМЕЖУТОЧНЫХ ИТОГОВ находится в диапазоне данных ref1 , то ПРОМЕЖУТОЧНЫЕ ИТОГЫ игнорируют ее и исключают из общего расчета.
• Excel допускает не более 254 диапазона ячеек для использования в формуле ПРОМЕЖУТОЧНОГО ИТОГО, хотя для работы формулы требуется только 1 .

4. Как использовать ПРОМЕЖУТОЧНЫЙ ИТОГ в формуле Excel: пошаговое руководство

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

Шаг 1: Выберите пустая ячейка

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

Отсюда вы можете начать создавать новую формулу ПРОМЕЖУТОЧНЫХ ИТОГОВ.

Шаг 2: Вставьте метод function_num

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

Первым компонентом формулы ПРОМЕЖУТОЧНЫЙ ИТОГ является метод номер_функции , который коррелирует с другой математической функцией Excel. Для этого введите число от 9От 0003 1 от до 11 , чтобы создать формулу ПРОМЕЖУТОЧНЫХ ИТОГОВ, которая включает скрытые значения, или от 101 от до 111 , чтобы создать формулу, которая их игнорирует.

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

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

Шаг 3: Выберите диапазоны ПРОМЕЖУТОЧНЫХ ИТОГОВ

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

Например, если вы хотите подсчитать количество ячеек в диапазонах A2:A9 и B2:B9 , используйте формулу =ПРОМЕЖУТОЧНЫЙ ИТОГ(102,A2:A9,B2:B9) , убедившись, что закрыть формулу закрывающей скобкой.

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

Заключительные мысли

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

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

• 10 формул Excel, которые должен знать каждый аналитик данных
• Как использовать функцию СЦЕПИТЬ в Excel
• Памятка по SQL: изучите первые 8 команд в Excel

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

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

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

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

Функция промежуточного итога Excel — синтаксис и использование

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

Функция ПРОМЕЖУТОЧНЫЙ ИТОГ доступна во всех версиях Excel 2016, Excel 2013, Excel 2010, Excel 2007 и более ранних версиях.

Синтаксис функции ПРОМЕЖУТОЧНЫЙ ИТОГ Excel следующий:

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

Где:

• Номер_функции — число, указывающее, какую функцию использовать для промежуточного итога.
• Ref1, Ref2, … — одна или несколько ячеек или диапазонов для промежуточного итога. Первый аргумент ref является обязательным, остальные (до 254) необязательны.

Аргумент номер_функции может принадлежать к одному из следующих наборов:

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

Function_num		Функция	Описание
1	101	СРЕДНЕЕ	Возвращает среднее число.
2	102	СЧЕТ	Подсчитывает ячейки, содержащие числовые значения.
3	103	СЧЁТ	Подсчитывает непустые ячейки.
4	104	МАКС	Возвращает наибольшее значение.
5	105	МИН	Возвращает наименьшее значение.
6	106	ПРОДУКТ	Вычисляет произведение ячеек.
7	107	СТАНДОТКЛОН	Возвращает стандартное отклонение совокупности на основе выборки чисел.
8	108	СТДЕВП	Возвращает стандартное отклонение на основе всей совокупности чисел.
9	109	СУММА	Складывает числа.
10	110	ВАР	Оценивает дисперсию генеральной совокупности на основе выборки чисел.
11	111	ВАРП	Оценивает дисперсию совокупности на основе всей совокупности чисел.

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

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

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

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

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

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

Теперь, когда вы знаете, как создать формулу промежуточного итога в Excel, возникает главный вопрос: зачем утруждать себя ее изучением? Почему бы просто не использовать обычные функции, такие как SUM, COUNT, MAX и т. д.? Ответ вы найдете прямо ниже.

3 главные причины для использования ПРОМЕЖУТОЧНЫХ ИТОГОВ в Excel

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

1. Вычисление значений в отфильтрованных строках

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

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

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

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

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

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

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

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

На приведенном ниже снимке экрана формула общего среднего ПРОМЕЖУТОЧНЫЙ ИТОГ(1, C2:C10) игнорирует результаты формул промежуточного итога в ячейках C3 и C10, как если бы вы использовали формулу среднего с двумя отдельными диапазонами СРЗНАЧ(C2: С5, С7:С9) .

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

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

Пример 1. Промежуточный итог 9 против промежуточного итога 109

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

Для суммирования отфильтрованных строк вы можете использовать формулу Subtotal 9 или Subtotal 109, как показано на снимке экрана ниже:

Вкладка > Группа ячеек > Формат > Скрыть и показать или щелкнув правой кнопкой мыши строки, а затем щелкнув Скрыть , и теперь вы хотите суммировать значения только в видимых строках, промежуточный итог 109единственный вариант:

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

Примечание. Функция ПРОМЕЖУТОЧНЫЕ ИТОГОВ Excel с номером_функции 101-111 игнорирует значения в скрытых строках, но не в скрытых столбцах . Например, если вы используете такую ​​формулу, как SUBTOTAL(109, A1:E1) для суммирования чисел в горизонтальном диапазоне, скрытие столбца не повлияет на промежуточный итог.

Пример 2. ЕСЛИ + ПРОМЕЖУТОЧНЫЙ ИТОГ для динамического суммирования данных

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

• В одной ячейке создайте раскрывающийся список, содержащий имена функций, такие как Total, Max, Min и т. д.
• В ячейку рядом с раскрывающимся списком введите вложенную формулу ЕСЛИ со встроенными функциями промежуточного итога, соответствующими именам функций в раскрывающемся списке.

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

=ЕСЛИ(A17="всего", ПРОМЕЖУТОЧНЫЙ ИТОГ(9,C2:C16), ЕСЛИ(A17="среднее", ПРОМЕЖУТОЧНЫЙ ИТОГ(1,C2:C16), ЕСЛИ(A17="мин", ПРОМЕЖУТОЧНЫЙ ИТОГ(5,C2:C16), ЕСЛИ(A17="max", ПРОМЕЖУТОЧНЫЙ ИТОГ(4,C2:C16),""))))

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

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

Промежуточный итог Excel не работает — распространенные ошибки

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

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

#ДЕЛ/0! — возникает, если указанная функция суммирования должна выполнять деление на ноль (например, вычисление среднего значения или стандартного отклонения для диапазона ячеек, не содержащих ни одного числового значения).

#ИМЯ? — неправильно написано название функции Subtotal — проще ошибку исправить 🙂

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