Сумма по фильтру в Эксель (Excel) — как найти?
Как найти сумму ячеек, отфильтрованных в Excel?
Например, если в столбце таблицы 100 ячеек, а после применения фильтра осталось 10, как можно легко и быстро вычислить сумму этих 10 ячеек?
Сумма отфильтрованных ячеек в Excel
При работе в Excel иногда необходимо не только отфильтровать данные в таблице, но и найти сумму одного или нескольких столбцов, к которым был применен фильтр.
Я расскажу, как это можно сделать, на конкретном примере.
Пусть исходная таблица будет выглядеть так:
Вам просто нужно бросить фрукты и рассчитать их общую стоимость.
I. Сначала установите фильтр: на панели инструментов Excel «Главная» щелкните «Сортировка и фильтр» -> «Фильтр», затем щелкните треугольник, который появляется в столбце «Фрукты / овощи».
Снимите флажок «Овощи» и нажмите «ОК».
Останутся только фрукты.
II. Как правило, сумма фильтра отображается в строке состояния Excel — просто выберите нужный столбец.
То есть все фрукты в сумме стоят 430 рублей.
Но если это число очень велико (например, миллионы или миллиарды) и вам нужно использовать его где-то еще, опция со строкой состояния не будет работать — вам придется вручную вводить эти числа позже.
Следовательно, вы можете это сделать:
1) Поместите курсор в ячейку ниже и на панели инструментов «Главная» нажмите «Автосумма».
2) Появится формула с функцией ПРОМЕЖУТОЧНАЯ
3) Нажимаем клавишу Enter и получаем нужную нам сумму.
Это значение можно использовать в формулах и копировать куда угодно.
Если вы установите другое условие в фильтре, сумма будет пересчитана автоматически.
В частности, в этом примере вы можете изменить условие с «Фрукты» на «Овощи»:
Также обратите внимание, что в некоторых случаях для вычисления суммы отдельных ячеек вам может не потребоваться фильтровать данные и не находить сумму с помощью фильтра, а использовать функцию СУММЕСЛИ.
Эта функция имеет 3 аргумента:
1) Диапазон — это диапазон ячеек для проверки (соответствуют ли они каким-либо условиям).
В нашем случае это столбец «Фрукты / овощи» (B).
2) Критерий — указано необходимое условие.
В нашем случае это «Фрукты» (в самом мастере написано без кавычек).
3) Sum_Range — столбец со значениями, которые необходимо добавить.
Сумма видимых строк. Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ
Задача: функция СУММ суммирует все ячейки в диапазоне, независимо от того, скрыты они или нет. Вы просто хотите просуммировать видимые линии.
Решение: вы можете использовать функцию ПРОМЕЖУТОЧНЫЙ.ИТОГО вместо СУММ. Формула будет немного отличаться в зависимости от того, как вы спрятали струны. Если вы выбрали строки, щелкнули правой кнопкой мыши и выбрали скрыть в контекстном меню, вы можете использовать: = INTERMEDIATE.TOTALS (109; диапазон) (рис. 1). Очень необычно использовать для этой цели ПРОМЕЖУТОЧНЫЙ ИТОГ. Как правило, эта функция требуется, чтобы Excel игнорировал другие промежуточные итоги в пределах диапазона.
Рис. 1. Серия 100 в первом аргументе функции INTERMEDIATE.TOTAL используется для обработки видимых строк
INTERMEDIATE.TOTALS может выполнять 11 операций. Первый аргумент функции указывает на следующие операции: (1) СРЕДНИЙ, (2) СЧЁТ, (3) СЧЁТ, (4) МАКС, (5) МИН, (6) ПРОИЗВОД, (7) СТАНДАРТ, (8) СТАНДАРТ , (9) СУММА, (10) ВАР, (11) ВАР. При добавлении сотен выполняются те же операции, но только с видимыми ячейками. Например, 104 найдет максимум видимых ячеек. «Видимый» означает, что он не отображается на экране (например, 120 строк не помещаются на экране) и не скрывается командой «Скрыть.
В ячейке E566 (см. Рисунок 1) используется формула = ПРОМЕЖУТОЧНЫЙ.ИТОГО (109; E2: E564). Excel возвращает сумму только видимых (не скрытых) ячеек в диапазоне, то есть — E2; E30; E72; E78; E564.
Функция INTERMEDIATE.TOTALS применяется к вертикальным наборам данных. Он не предназначен для горизонтальных наборов данных. Следовательно, при определении промежуточных итогов горизонтального набора данных с использованием значения константы function_number 101 и выше (например, INTERMEDIATE.TOTALS (109; C2: F2) рис. 2), скрытие столбца не повлияет на результат.
Рис. 2. Формула не игнорирует ячейки в скрытых столбцах
Дополнительная информация: существует необычное исключение в поведении функции INTERMEDIATE.TOTALS. Если строки были скрыты одной из команд фильтра (Расширенный фильтр, Автофильтр или Фильтр), Excel суммирует только видимые строки, даже в параметре INTERMEDIATE.TOTAL (9; диапазон). Версия 109 не обязательна (рис. 3). Здесь фильтр используется для поиска записей Chevron.
Рис. 3. Аргумента 9 достаточно, если строки скрыты в результате применения фильтра
Почему я говорю об этой странности? Потому что есть малоизвестное сочетание клавиш для суммирования видимых строк, полученных с помощью фильтрации. Попробуйте следующие шаги:
- Выберите ячейку в наборе данных.
- Перейдите в меню ДАННЫЕ -> Фильтр (или нажмите Alt + S, затем, не отпуская Alt, нажмите F; или нажмите Ctrl + Shift + L). Excel добавляет фильтр (раскрывающееся меню) для всех заголовков столбцов.
- Откройте одно из раскрывающихся меню, например Клиент. Снимите флажок «Выбрать все» и выберите клиента. В нашем примере Chevron.
- Выделите ячейки непосредственно под отфильтрованными данными. В нашем примере ячейки E565: H565.
- Нажмите Alt + = или щелкните значок Автосумма (меню ГЛАВНОЕ). Вместо использования SUM Excel будет использовать функцию INTERMEDIATE.TOTALS (9; range), которая суммирует только строки, выбранные фильтром (см. Рисунок 3).
В Excel 2010 появилась еще одна похожая функция: АГРЕГАЦИЯ (подробнее см. Сравнение массивов и выборок на основе одного или нескольких условий; раздел Функция АГРЕГАЦИЯ). В его «репертуаре» больше функций и больше вариантов, какие строки исключить, а какие обработать. Его главное преимущество — обработка неверных значений (например, # DIV / 0!). К сожалению, эта функция не применяется даже к сумме видимых столбцов.
Сводка: вы можете использовать функцию INTERMEDIATE.TOTAL, чтобы игнорировать скрытые строки.
Суммирование только видимых ячеек
Если у нас есть таблица, по которой нужно подсчитывать итоги, то важную роль играет то, какая функция они рассчитываются, так как таблица может содержать:
- Фильтры включены
- Некоторые строки скрыты
- Сжатые сгруппированные строки
- Промежуточные итоги в таблице
- Ошибки в формулах
Некоторые из приведенных ниже методов чувствительны к этим факторам, другие — нет. Это необходимо учитывать при проведении расчетов:
СУММ — тупо суммирует все в выбранном диапазоне без разбора, например, даже скрытые строки. Если хотя бы одна ячейка содержит ошибки, она прекращает счет и также выдает ошибку.
ПРОМЕЖУТОЧНЫЕ ИТОГИ с кодом 9 в первом аргументе: суммировать все видимые ячейки после фильтра. Игнорируйте другие подобные функции, которые могут считывать внутренние промежуточные итоги в исходном диапазоне.
ПРОМЕЖУТОЧНЫЕ ИТОГИ с кодом 109 в первом аргументе: суммировать все видимые ячейки после фильтрации и группировки (или скрытые). Игнорируйте другие подобные функции, которые могут считывать внутренние промежуточные итоги в исходном диапазоне.
Если складывать не нужно, можно использовать другие значения кода математической операции:
AGGREGATE — самая мощная функция, представленная в Office 2010. Помимо INTERMEDIATE.TOTALS, она может не только складывать, но и вычислять среднее, количество, минимум, максимум и т.д.: Код операции указывается первым аргументом. Кроме того, у него есть много параметров подсчета, которые можно указать в качестве второго аргумента:
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Excel для промежуточных расчетов
Функция INTERMEDIATE.TOTALS в Excel используется для вычисления промежуточных итогов в таблицах (включая базы данных) и возвращает желаемое числовое значение (в зависимости от требуемого номера операции, указанного в качестве первого аргумента этой функции, например, 1 — это среднее арифметическое диапазона значений, 9 — общее значение и т д.). Чаще всего рассматриваемая функция используется для редактирования списков с промежуточными итогами, созданными с помощью специальной команды, встроенной в Excel.
Примеры использования функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Excel
Функция INTERMEDIATE.TOTAL полезна в случаях, когда таблица содержит большой объем данных, которые можно отфильтровать по одному или нескольким критериям. В этом случае после применения фильтров будет отображаться только та часть таблицы, данные которой соответствуют заданным критериям. Однако операции с использованием общих функций, таких как СУММ, СРЕДНЕЕ и т.д., будут выполняться для всей исходной таблицы (т.е с учетом скрытых строк). Функция ИТОГО ПРОМЕЖУТОЧНАЯ работает только с отфильтрованными данными.
Суммирование только видимых ячеек в фильтре Excel
Пример 1. В таблице приведены данные о продажах в магазине музыкальных инструментов трех марок различных моделей электрогитар за три дня. Подсчитайте промежуточные итоги продаж гитар марки Ibanez.
Просмотр исходной таблицы данных:
Мы используем фильтр для отбора данных, относящихся к гитарам марки Ibanez. Для этого выделите всю таблицу или просто переместите курсор в любую ячейку таблицы и воспользуйтесь инструментом «ДАННЫЕ» — «Фильтр». Исходная таблица теперь выглядит так:
Щелкните раскрывающийся список в столбце B («Марка продукта») и установите флажок рядом с названием «Ibanez»:
После нажатия кнопки «ОК» таблица будет выглядеть так:
Как видите, некоторые линии теперь скрыты. Если применить обычную функцию СУММ, расчет будет производиться для всех строк исходной таблицы:
Вместо этого мы будем использовать следующую функцию в ячейке C24:
- 9 — числовое значение, соответствующее использованию функции СУММ для получения промежуточных итогов;
- C4: C20 — диапазон ячеек, содержащий данные о стоимости гитар (при этом все остальные гитары, за исключением марки Ibanez, при расчете не учитываются).
Аналогичным образом произведем расчет количества проданных гитар и общей выручки («Сумма»). В результате мы получим:
Для сравнения приведем результаты, полученные с помощью обычной функции СУММ:
Несмотря на то, что некоторые строки скрыты из-за использования фильтра, функция СУММ учитывает все строки.
Выборочное суммирование ячеек таблицы в Excel
Отключите автоматический фильтр и выделите исходную таблицу данных из первого примера. Теперь воспользуемся инструментом Промежуточный итог на вкладке «Данные» на панели инструментов:
В открывшемся диалоговом окне выберите имя столбца «Дата» в качестве критерия «Для каждого изменения:». Следующий вариант — это операция, которую нужно выполнить с данными. Мы выбираем «Сумма», чтобы суммировать значения. Критерий «Добавить итоги по:» позволяет выбрать столбцы, для которых будет выполняться операция суммирования. Установите флажки рядом с «Заменить текущие итоги» и «Итоги под данными»:
После нажатия кнопки «ОК» исходная таблица будет выглядеть так:
В итоговой таблице есть инструменты для скрытия / отображения частей данных и отображения общей суммы только при необходимости. Если вы выберете ячейку, в которой отображаются промежуточные итоги, вы увидите, что промежуточные итоги были рассчитаны с использованием функции ПРОМЕЖУТОЧНЫЕ ИТОГИ.
Каждая из этих функций может быть изменена по усмотрению пользователя. Например, вот как автоматически определяется средняя стоимость проданных гитар на 13.08.2018:
Примеры формул для расчетов промежуточных итогов в таблице Excel
Просмотр исходной таблицы данных:
Мы фильтруем данные по критерию «джинсы» и дате, указанной в условии:
Функция ОБЩАЯ ПРОМЕЖУТОЧНОСТЬ не имеет встроенных функций для расчета режима и среднего отклонения. Для расчета режима используйте следующую формулу (матричная формула CTRL + SHIFT + ENTER):
В этом случае функция INTERMEDIATE.TOTAL возвращает ссылку на диапазон ячеек, из которого исключаются строки, не отображаемые из-за фильтров. Функция ЕСЛИ возвращает массив, содержащий числовые значения для отображаемых строк и «» пустые значения для строк, которые не отображаются. Функция РЕЖИМ игнорирует их во время расчета. В результате формулы получаем:
Для расчета среднего отклонения используем аналогичную формулу:
Обычно функция INTERMEDIATE.TOTAL используется для простых вычислений. 11 функций, предлагаемых в его синтаксисе, обычно достаточно для создания отчетов с промежуточными итогами.
Особенности использования функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Excel
Рассматриваемая функция имеет следующий синтаксис:
= INTERMEDIATE.TOTAL (число_функция; ссылка1; [ссылка2];…)
- function_number — обязательный аргумент, который принимает числовые значения из диапазонов от 1 до 11 и от 101 до 111, которые характеризуют номер функции, используемой для вычисления промежуточных итогов: AVERAGE, COUNT, COUNT, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, DISP и VARP соответственно. В этом случае используются функции, обозначенные номерами от 1 до 11, когда в расчет также должны входить скрытые вручную строки. Функции с номерами от 101 до 111 игнорируют вручную скрытые строки в вычислениях. Строки, которые были скрыты из-за применения фильтров, по-прежнему не учитываются в расчетах;
- ссылка1 — обязательный аргумент, принимающий ссылку на диапазон ячеек с числовыми данными, для которого вы хотите вычислить промежуточные итоги;
- [ref2];[…] — вторая и последующие ссылки на диапазоны ячеек, для значений которых рассчитываются промежуточные итоги. Максимальное количество аргументов — 254.
- Если ссылка1, [ссылка2];… были переданы в качестве аргументов диапазонам ячеек, которые включают ячейки, содержащие промежуточные итоги, полученные с помощью функции ПРОМЕЖУТОЧНЫЙ ИТОГ, они не будут приняты во внимание, чтобы не повлиять на окончательный результат.
- В отфильтрованной таблице отображаются только строки, содержащие значения, которые соответствуют указанным условиям (используемым фильтрам). Некоторые линии можно скрыть вручную с помощью параметра «Скрыть линии». Такие строки также можно исключить из результата, возвращаемого функцией INTERMEDIATE.TOTAL, если в качестве первого аргумента указано число от 101 до 111.
- Основное свойство рассматриваемой функции (выполнять операции только с отфильтрованными данными) применимо только к таблицам, где данные фильтруются по строкам, а не по столбцам. Например, если вы вычисляете промежуточные итоги в горизонтальной таблице, где несколько столбцов были скрыты из-за фильтра, = INTERMEDIATE.TOTALS (1; A1: F1) вернет среднее значение всех значений в диапазоне A1: F1, даже если некоторые столбцы скрыты.
- Если в ссылке1, [ссылка2];[…] были переданы ссылки на диапазоны ячеек, которые находятся на другом листе или в другой книге Excel (такие ссылки называются 3D), функция ПРОМЕЖУТОЧНЫЙ.ИТОГО вернет код ошибки # ЗНАЧ!.
