✅ Подводная формула в Excel - Как использовать SUBTOTAL Formula в Excel?

Подводная формула в Excel

Подводная формула в Excel (Содержание)

Подводная формула в Excel
Как использовать ПОДВОДНУЮ формулу в Excel?

Подводная формула в Excel

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

Определение

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

Синтаксис СУБТОТАЛЬНОЙ Формулы

Аргументы:

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

Ref1, Ref 2: относится к диапазону ячеек.

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

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

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

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

Как использовать СУБТОТАЛЬНУЮ Формулу в Excel?

Подводная формула в Excel очень проста и легка. Давайте разберемся, как использовать СУБТОТАЛЬНУЮ Формулу в Excel с некоторыми примерами.

Вы можете скачать этот СУБТОТАЛЬНЫЙ шаблон Excel для формул здесь - СУБТОТАЛЬНЫЙ шаблон Excel для формул

Пример № 1

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

Если вы соблюдаете приведенную выше таблицу, у нас есть категория продукта, цветовая категория и количество.

Примените СУБТОТАЛЬНУЮ Формулу, как показано ниже, с функцией № 9.

После применения этой формулы результат будет таким, как показано ниже.

Примените ПОДВОДНУЮ формулу, как показано ниже, с номером функции 109.

После применения СУБТОТАЛЬНОЙ Формулы результат показан ниже.

В формуле мы использовали обе функции: 9 и 109 для выполнения SUM в двух разных столбцах. C2: C9 - это диапазон данных, которые мы выполняем вычисления.

Теперь общая сумма составляет 385 для обеих формул. Скройте несколько строк и наблюдайте результаты для обеих формул.

После применения СУБТОТАЛЬНОЙ Формулы результат показан ниже.

Строки от 50 до 60 скрыты, поэтому результаты функции номер 109 изменились на 255, потому что она не учитывает скрытые вручную данные, тогда как итоговая функция номер 9 остается неизменной, что означает, что она будет рассматривать данные, даже если мы скрываем данные вручную,

Примените фильтр к данным и отфильтруйте только один цвет, здесь мы выбрали черный цвет.

После применения СУБТОТАЛЬНОЙ Формулы результат будет таким, как показано ниже.

Примените СУБТОТАЛЬНУЮ формулу, как показано ниже, с номером функции 109.

После применения СУБТОТАЛЬНОЙ Формулы результат будет таким, как показано ниже.

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

После применения СУБТОТАЛЬНОЙ Формулы результат будет таким, как показано ниже.

Примените СУБТОТАЛЬНУЮ формулу, как показано ниже, с номером функции 109.

После применения этой формулы результат будет таким, как показано ниже.

Пример № 2

До сих пор мы видели операцию SUM, теперь мы будем выполнять операцию AVERAGE с тем же диапазоном данных. 1 и 101 - номера функций для выполнения среднего. Просмотрите таблицу выше для подробностей номеров функций.

После применения СУБТОТАЛЬНОЙ Формулы результат будет таким, как показано ниже.

Для следующей ячейки снова примените SUBTOTAL Formula с номером функции 109.

После применения формулы результат будет таким, как показано ниже.

С той же формулой SUBTOTAL мы также можем вычислить среднее значение с единственным изменением номера функции.

После применения СУБТОТАЛЬНОЙ Формулы результат будет таким, как показано ниже.

Для следующей ячейки снова примените СУБТОТАЛЬНУЮ формулу с номером функции 101.

После применения СУБТОТАЛЬНОЙ Формулы результат показан ниже.

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

Пример № 3

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

После применения СУБТОТАЛЬНОЙ Формулы результат будет таким, как показано ниже.

Используйте СУБТОТАЛЬНУЮ Формулу еще раз, чтобы проверить СУММУ B.

После применения СУБТОТАЛЬНОЙ Формулы результат будет таким, как показано ниже.

Используйте СУБТОТАЛЬНУЮ Формулу снова, чтобы проверить СУММУ C.

После применения СУБТОТАЛЬНОЙ Формулы результат будет таким, как показано ниже.

Используйте СУБТОТАЛЬНУЮ Формулу еще раз, чтобы проверить СУММУ D.

После применения СУБТОТАЛЬНОЙ Формулы результат будет таким, как показано ниже.

Используйте СУБТОТАЛЬНУЮ Формулу снова, чтобы перейти к следующей ячейке.

После применения СУБТОТАЛЬНОЙ Формулы результат будет таким, как показано ниже.

Используйте СУБТОТАЛЬНУЮ Формулу снова, чтобы перейти к следующей ячейке.

После применения СУБТОТАЛЬНОЙ Формулы результат показан ниже.

Если мы наблюдаем вышеупомянутые снимки экрана, то есть сумма по побочным продуктам, и при выполнении общей суммы мы включали итоговые суммы по категориям в общую сумму (ячейки от 36 до 48), но не будут учитывать промежуточные итоги (ячейки от 44 до 47). ). Таким образом, мы можем вычислить промежуточные итоги между диапазонами данных, не влияя на общую сумму.

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

Что нужно помнить о СУБТОТАЛЬНОЙ ФОРМУЛЕ в Excel

При указании номера функции не указывайте номер, который находится вне диапазона номеров функций, поскольку он не предопределен в Excel, мы получим сообщение об ошибке. Мы всегда должны указывать число от 1 до 11 и от 101 до 111, иначе будет выдано сообщение об ошибке #value.
При использовании операции деления помните, что ни одно число не должно делиться на ноль. Это означает, что x / 0 - неправильный формат.
Если вы применяете промежуточный итог для горизонтальных данных, которые взяты из A1: F1, и скрытие каких-либо столбцов не повлияет на промежуточный итог.
При применении промежуточного итога, если какие-либо ячейки не имеют данных или нечисловых данных, функция будет игнорировать эти ячейки.
Используйте номера функций, понимая их функциональность, иначе используйте неправильные результаты, как вы ожидаете.

Подводная формула в Excel - Как использовать SUBTOTAL Formula в Excel?