ПОДСКАЗКА в Excel (формула, примеры) - Использование СУБТОТАЛЬНОЙ функции

Содержание:

Anonim

СУБТОТАЛЬНАЯ функция Excel (Содержание)

  • ПОДСКАЗКА в Excel
  • Подводная формула в Excel
  • Как использовать SUBTOTAL функцию в Excel?

ПОДСКАЗКА в Excel

  • Функция промежуточных итогов - это встроенная функция в Excel, которая классифицируется как математическая функция
  • Промежуточный итог будет не просто суммировать числа в определенном диапазоне ячеек, он будет помогать, выполняя различные арифметические операции
  • СУБТОТАЛЬ универсален. Вы можете использовать его для усреднения, суммирования, подсчета количества ячеек и других расчетов.

Определение

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

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

Формула для функции SUBTOTAL выглядит следующим образом:

SUBTOTAL (function_num, ref1, (ref2), …) где

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

Аргумент function_num применяется на основе упомянутых ниже двух критериев или метода

  1. Включить скрытые значения Когда аргумент function_num находится в диапазоне 1-11, функция SUBTOTAL будет включать скрытые значения.
  2. Игнорировать скрытые значения Когда аргумент function_num находится между 101-111, функция SUBTOTAL исключит или проигнорирует скрытые значения. Он будет искать значения только невидимых строк, игнорирует скрытые значения в расчете.
Fun_num
Включает скрытые значения		 Fun_num
Исключает скрытые значения		 функция Описание
1 101 СРЕДНИЙ Он рассчитывает среднее значение указанного диапазона.
2 102 COUNT Он будет считать количество ячеек, которые содержат числовые значения.
3 103 COUNTA Он будет считать непустые ячейки в указанном диапазоне.
4 104 МАКСИМУМ Он найдет наибольшее значение в указанном диапазоне.
5 105 MIN Он найдет наименьшее значение в указанном диапазоне.
6 106 ПРОДУКТ Он рассчитывает произведение клеток в указанном диапазоне.
7 107 STDEV Он оценивает стандартное отклонение в указанном диапазоне.
8 108 STDEVP Он рассчитывает стандартное отклонение на основе всей совокупности.
9 109 SUM Он рассчитывает сумму указанного диапазона.
10 110 VAR Оценивает дисперсию в указанном диапазоне.
11 111 VARP Он оценивает дисперсию на основе всего населения.

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

Как использовать SUBTOTAL функцию в Excel?

Давайте посмотрим, как работает функция SUBTOTAL в Excel.

Вы можете скачать этот шаблон SUBTOTAL Function Excel здесь - Шаблон SUBTOTAL Function Excel

Пример # 1 - СУБТОТАЛЬНАЯ формула для значений, скрытых с помощью автофильтра Excel или строк, скрытых фильтром

В приведенном ниже примере таблица содержит квартальные данные о продажах, где отдельные кварталы в столбце E и данные о продажах в столбце F.

Функция суммы применяется в ячейке «F16», т. Е. = SUM (F4: F15).

Возвращает или приводит к значению 1184 .

Функция SUM возвращает то же значение. т.е. 1184, вместо 194 для данных Q4, если строки скрыты фильтром (ниже упомянутый скриншот).

Если мне нужно только правильное значение SUM для данных Q4, мне нужно использовать функцию SUBTOTAL.

Перед применением функции SUBTOTAL под панелью инструментов данных примените фильтр для строки заголовка.

Теперь давайте рассчитаем данные продаж по Q1, Q2, Q3 или Q4 по отдельности для отфильтрованных данных.

Используйте 109 в качестве аргумента function_num в SUBTOTAL функции для суммирования данных. то есть = СУБТОТАЛЬНО (109, C17: C28)

Функция SUBTOTAL игнорирует строки, скрытые фильтром, и вычисляет правильный результат.

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

Q4

Q3

Q2

Q1

Пример №2 - СУБТОТАЛЬНАЯ функция для скрытых строк вручную

В листе Excel строки 15, 16, 19 и 20 были скрыты с помощью параметра форматирования строки, выделив эти строки, щелкнув правой кнопкой мыши и выбрав Скрыть.

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

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

то есть = СУБТОТАЛЬНО (109, C14: C21)

Здесь функция SUBTOTAL игнорирует скрытые строки вручную и вычисляет правильный результат.

Он найдет сумму значений только в видимых строках; скрытые строки игнорируются функцией SUBTOTAL

Пример № 3 - СУБТОТАЛЬНАЯ функция для нормального значения

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

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

В ячейке применяется СУБТОТАЛЬНАЯ функция С14, С15 и С16.

Для вычисления значения SUM используется функция № 9 или 109 в функции промежуточного итога, где она вычисляет сумму указанного диапазона, т.е. = SUBTOTAL (9, C4: C11)

Это даст нам сумму всех значений в C4: C11, при условии, что ни одно из них не будет отфильтровано и строки будут скрыты в указанном диапазоне.

Чтобы вычислить значение MIN, функция номер 5 или 105 используется в функции промежуточного итога, где она найдет наименьшее значение в указанном диапазоне. В ячейке C15 применяется функция SUBTOTAL, т.е. = SUBTOTAL (5, C4: C11)

Он найдет наименьшее значение в указанном диапазоне, то есть C4: C11

Для расчета значения MAX используется функция № 4 или 104 в функции промежуточного итога, в которой она находит наибольшее значение в указанном диапазоне. В ячейке C16 применяется функция SUBTOTAL, т.е. = SUBTOTAL (4, C4: C11)

Он обнаружит самое большое значение в указанном диапазоне, то есть C4: C11.

Что нужно помнить о СУБТОТАЛЬНОЙ функции

  • СУБТОТАЛЬНАЯ функция может быть применена, чтобы найти сумму отфильтрованных значений.
  • SUBTOTAL функция используется для игнорирования значений в скрытых строках.
  • Значение аргумента функции _ num не должно быть больше 11 или меньше 1, аналогично не должно быть больше 111 или меньше 101, в противном случае будет получено #VALUE! ошибка.
  • SUBTOTAL функция применима только для числового значения.
  • Если в диапазоне SUBTOTAL присутствуют какие-либо промежуточные итоги, они будут игнорироваться, т. Е. Если какие-либо уже существующие формулы SUBTOTAL присутствуют во входном диапазоне другой формулы SUBTOTAL, то во время вычисления эти значения игнорируются функцией промежуточных итогов, поэтому двойной счет не учитывается. это предотвращает ошибку двойного счета.
  • СУБТОТАЛЬНАЯ функция может использоваться для динамического суммирования данных.
  • Пустые ячейки и ячейки, содержащие нечисловые значения, игнорируются функцией промежуточных итогов при вычислении.

Рекомендуемые статьи

Это было руководство по подведению итогов. Здесь мы обсуждаем SUBTOTAL Formula и как использовать SUBTOTAL Function вместе с примером Excel и загружаемыми шаблонами Excel. Вы также можете посмотреть на эти полезные функции в Excel -

  1. Функция поиска в Excel
  2. НАЙТИ функцию в Excel
  3. НЕ в Excel
  4. ТРАНСПОРТИРОВАТЬ в Excel