Excel: Промежуточные итоги для ваших расчетов

Программа электронных таблиц Microsoft позволяет составлять обширные таблицы и производить сложные расчеты. Функции в Excel могут помочь во многих ситуациях и редко зависят от дизайна и внешнего вида ваших таблиц. Хотите скрыть некоторые ячейки для наглядности? Нет проблем: функции по-прежнему подсчитывают отфильтрованные значения. Однако это может понадобиться не всегда. Если вы хотите, чтобы ваши вычисления реагировали на фильтры и скрытые ячейки, функция СУММЕСЛИ будет весьма полезна. Она объединяет несколько операций вычисления и дает вам больше контроля над ячейками, которые включает Excel.

Для чего нужна функция СУММЕСЛИ в Excel?

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

Но то, что в целом является преимуществом, в некоторых ситуациях может стать проблемой. Например, если вы хотите, чтобы функции Excel реагировали на то, что видно в данный момент, и соответствующим образом адаптировали результаты, вы не сможете сделать это с помощью обычных методов расчета. Вам придется создать новую таблицу, в которую не будут включены отфильтрованные значения. Функция СУММЕСЛИ дает вам альтернативу: Вы можете настроить функцию так, чтобы скрытые ячейки не включались в расчет.

SUBTOTAL объединяет не менее одиннадцати различных функций: Пользователи выбирают, какой метод вычисления будет использоваться в функции, а также включать ли скрытые ячейки или нет. Функция не включает в расчеты значения, скрытые фильтром. Соответственно, СУММЕСЛИ идеально подходит в качестве дополнительного результата, дополняющего итоговый результат. Сравнение этих двух значений часто дает дополнительную информацию.

Примечание

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

Синтаксис СУММЕСЛИ

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

=SUBTOTAL(Function;Reference1;[Reference2];…)

Первый параметр передает функции SUBTOTAL свойства другой функции — Excel предоставляет список функций. Вы вводите соответствующее число, а SUBTOTAL выполняет соответствующий расчет. Однако для каждой функции существует два числа. Значения от 1 до 11 настраивают SUBTOTAL на включение скрытых значений. Если вы введете значения от 101 до 111, функция игнорирует скрытые ячейки в указанном диапазоне. Ниже приведен обзор.

Скрытые ячейки остаются частью расчета:

  • 1: СРЕДНЕЕ
  • 2: СЧЕТ
  • 3: СЧЕТ
  • 4: МАКС
  • 5: МИН
  • 6: ПРОДУКТ
  • 7: STDEV
  • 8: STDEVP
  • 9: СУММА
  • 10: VAR
  • 11: VARP

Скрытые ячейки не учитываются в расчетах:

  • 101: СРЕДНЕЕ
  • 102: COUNT
  • 103: COUNTA
  • 104: МАКС
  • 105: МИН
  • 106: ПРОДУКТ
  • 107: STDEV
  • 108: STDEVP
  • 109: СУММА
  • 110: VAR
  • 111: VARP
Примечание

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

SUBTOTAL на практике: Примеры использования функции

Вычисление суммы — лучший пример использования функции СУММЕСЛИ. Помимо конечного результата, включающего все значения, функция также позволяет вычислять только те значения, на которые не влияют фильтры или скрытые значения.

=SUBTOTAL(109;A2:A10;B2:B10)

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

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

=SUBTOTAL(1;A2:A10;B2:B10)

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

Также может быть полезно сочетать СУММЕСЛИ с функцией ЕСЛИ. Можно создать ячейку, в которой указывается необходимый тип вычисления — например, с помощью выпадающего меню. Хотя это делает саму формулу несколько длиннее, но, написав ее однажды, вам никогда не придется редактировать ее в дальнейшем. Например, если мы предположим, что вы хотите регулярно переключаться между тремя вычислениями SUM, COUNT и AVERAGE.

 

=IF(A12="SUM";SUBTOTAL(109;B2:B10);IF(A12="COUNT";SUBTOTAL(102;B2:B10);IF(A12="AVERAGE";SUBTOTAL(101;B2:B10))))

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

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

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

Факт

Этот инструмент, который можно запустить одним нажатием кнопки, использует функцию СУММЕСЛИ в фоновом режиме. Инструмент заполняет формулы за вас и вводит их в нужные ячейки.

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

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

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

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

Сводка

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

Оцените статью
cdelat.ru
Добавить комментарий