Как суммировать только видимые ячейки в excel

Обновлено: 06.07.2024

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

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

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

Синтаксис

=SUBTOTAL(function_num,ref1,[ref2],…)

аргументы

Funtion_num (Обязательно): число от 1 до 11 или от 101 до 111, которое указывает функцию, используемую для промежуточного итога.
Ref1 (Обязательно): именованный диапазон или ссылка, для которой требуется подытог.
Ref2 (Необязательно): именованный диапазон или ссылка, по которым вы хотите вычислить промежуточные итоги.

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

=SUBTOTAL(9,C2:C13)

Примечание:

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

Подытог только видимые ячейки после фильтрации с помощью удивительного инструмента

Здесь рекомендую СУМВИДИМЫЕ ячейки Функция Kutools for Excel для вас. С помощью этой функции вы можете легко суммировать только видимые ячейки в определенном диапазоне несколькими щелчками мыши.

Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.

1. Выберите пустую ячейку для вывода результата, щелкните Kutools > Kutools Функции > Статистический И математика > НЕВЕРОЯТНО. Смотрите скриншот:

2. в Аргументы функций в диалоговом окне выберите диапазон, в котором будет произведен промежуточный итог, а затем нажмите кнопку OK кнопку.

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

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

Статьи по теме

Удалять только видимые строки или столбцы в Excel
В Excel мы обычно скрываем некоторые важные строки или столбцы информации, иногда нам нужно удалить только видимые строки или столбцы, если вы удалите их напрямую с помощью клавиши Delete, скрытые значения будут удалены одновременно. Как удалить видимые строки или столбцы, не удаляя скрытые строки или столбцы в Excel? В этом руководстве будут представлены способы удаления только видимых строк.

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

Одновременная фильтрация нескольких столбцов в Excel
Когда вы применяете функцию «Фильтр», после фильтрации одного столбца следующие столбцы будут фильтроваться только на основе результата предыдущего отфильтрованного столбца. Это означает, что только критерий И может применяться к более чем одному столбцу. Как в этом случае применить критерии И и ИЛИ для одновременной фильтрации нескольких столбцов на листе Excel? Ваш метод в этой статье.

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

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

Еще одним, относительно экзотическим, но весма мощным инструментом является функция БДСУММ (DSUM) из категории Работа с базой данных (Database) . При внешней простоте, она позволяет гибко фильтровать списки по нескольким сложным и связанным между собой условиям и подсчитывает сумму найденных записей по заданному столбцу. Синтаксис функции таков:

=БДСУММ( Исходные_данные ; Столбец_результата ; Диапазон_условий )

Исходные_данные - диапазон, включающий в себя таблицу с данными, которые мы анализируем и строкой заголовка.
Столбец_результата - название (из шапки таблицы) или порядковый номер столбца, по которому нужно просуммировать данные.
Диапазон_условий - диапазон, содержащий названия столбцов и условия по ним.

Чтобы удобнее было ссылаться эту таблицу в будущем, конвертируем ее в "умную" командой Форматировать как таблицу на вкладке Главная (Home - Format as Table) или сочетанием клавиш Ctrl + T . На появившейся затем вкладке Конструктор (Design) зададим ей имя - например БазаДанных.

Простая сумма по одному условию

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

Обратите внимание на следующие моменты:

Приблизительный и точный текстовый поиск

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

Если нужен поиск точного соответствия, то используем конструкцию '= (апостроф и знак равно).
Если нужен поиск подстроки, т.е. всех ячеек, которые содержат нужное значение, то его надо заключить в звездочки. В нашем случае будут просуммированы все варианты Абакана (с "г.", без "г.", с пробелами перед-после и т.п.)
Если просто ввести значение без равно и звездочек, то будут найдены и просуммированны все строки, где содержимое начинается с указанного значения, т.е. это равноценно звездочке в конце.

Несколько условий со связками "И" - "ИЛИ"

Если нужно просуммировать данные по нескольким условиям, связанным друг с другом логическим оператором И (AND), то ячейки с этими условиями должны быть в одной строке. Например, если нужно просуммировать все продажи Fanta по Абакану (в любом виде его написания), то это будет выглядеть так:

Если же нужно связать несколько условий логическим оператором ИЛИ (OR), то их нужно расположить в разных строчках. Например, если нужно просуммировать деньги по всем вариантам написания "города на Неве", коих великое множество:

И конечно же, можно комбинировать оба подхода, сочетания в одном запросе условия со связками И и ИЛИ одновременно:

В этом случае вычисляется сумма продаж Fanta в Абакане и Burn у Дубинина.

Суммирование по интервалу дат

В диапазоне условий можно легко задать интервал дат для подсчета. Если интервал ограничен с двух сторон, то нужно будет задать два условия, связанных между собой логическим "И" - поэтому придется писать условия в одной строке, т.е. добавить еще один столбец с тем же названием Дата сделки:

В данном случае вычисляется сумма продаж Fanta за 2016 год и Фруктайм до 2016 года.

Условия для чисел

Для отбора по числовым критериям можно смело использовать обычные знаки неравенств >, <, >=, <= как и в обычных формулах Excel. Например, если нам нужно просуммировать все продажи любых видов колы, где сумма сделки была в интервале 500-600:

Исключения "все кроме"

Если нужно при суммировании исключить записи по какому-либо параметру, то можно использовать символы "<>" обозначающие "не равно" в синтаксисе Excel. Допустим, нам нужно просуммировать все данные по Fanta кроме Самары и по Квасу кроме Пензы - это будет выглядеть так:

Заключение

Надеюсь, вы уже поняли, что функция БДСУММ является очень неплохим инструментом и, зачастую, более удобной альтернативой классическим функциям выборочного подсчета типа СУММЕСЛИ (SUMIF) и СУММЕСЛИМН (SUMIFS) . Кроме того, в той же категории Работа с базой данных (Database) можно найти ее "подруг", вычисляющих не только сумму:

Задача: функция СУММ суммирует все ячейки диапазона, являются ли они скрытыми или нет. Вы хотите суммировать только видимые строки.

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

Рис. 1. Серия 100 в первом аргументе функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ используется для обработки видимых строк

ПРОМЕЖУТОЧНЫЕ.ИТОГИ может выполнить 11 операций. Первый аргумент функции указывает ей на следующие операции: (1) СРЗНАЧ, (2) СЧЁТ, (3) СЧЁТЗ, (4) МАКС, (5) МИН, (6) ПРОИЗВЕД, (7) СТАНДОТКЛОН, (8) СТАНДОТКЛОНП, (9) СУММ, (10) ДИСП, (11) ДИСПР. При добавлении сотни выполняются те же операции, но только над видимыми ячейкам. Например, 104 найдет максимум среди видимых ячеек. Под видимыми имеется ввиду, не видимые на экране (например, 120 строк не уместятся на экране), а не скрытые, командой Скрыть.

В ячейке Е566 (см. рис. 1) используется формула =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;E2:E564). Excel возвращает сумму только видимых (не скрытых) ячеек в диапазоне, а именно – Е2;Е30;Е72;Е78;Е564.

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ применяется к вертикальным наборам данных. Она не предназначена для горизонтальных наборов данных. Так, при определении промежуточных итогов горизонтального набора данных с помощью значения константы номер_функции от 101 и выше (например, ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;С2:F2) рис. 2), скрытие столбца не повлияет на результат.

Рис. 2. Формула не игнорирует ячейки в скрытых столбцах

Дополнительные сведения: существует необычное исключение в поведении функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Когда строки были скрыты по какой-либо из команд фильтра (расширенный фильтр, автофильтр или фильтр), Excel суммирует только видимые строки даже в варианте ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;диапазон). Нет необходимости использовать версию 109 (рис. 3). Здесь фильтр используется для поиска записей Chevron.

Рис. 3. Достаточно аргумента 9 если строки скрыты в результате применения фильтра

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

Выбрать любую ячейку в вашем наборе данных.
Пройдите по меню ДАННЫЕ –> Фильтр (или нажмите Alt + Ы, а затем не отпуская Alt, нажмите Ф; или нажмите Ctrl+Shift+L). Excel добавляет фильтр (выпадающее меню) для всех заголовков столбцов.
Откройте одно из выпадающих меню, например, Customer. Снимите флажок Выделить все, а затем выберите одного клиента. В нашем примере – Chevron.
Выберите ячейки непосредственно под отфильтрованными данными. В нашем примере –ячейки Е565:H565.
Нажмите клавиши Alt+= или щелкните значок Автосумма (меню ГЛАВНАЯ). Вместо того, чтобы использовать СУММ, Excel применит функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;диапазон), которая просуммирует только строки, выбранные фильтром (см. рис. 3).

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

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

Подсчет / суммирование ячеек на основе фильтра с формулами

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

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

Чтобы суммировать значения ячеек на основе отфильтрованных данных, примените эту формулу: = ПРОМЕЖУТОЧНЫЙ ИТОГ (9; C6: C19) (C6: C19 это диапазон данных, который отфильтрован, вы хотите суммировать), а затем нажмите Enter ключ. Смотрите скриншот:

Подсчет / сумма ячеек на основе фильтра с помощью Kutools for Excel

Если у вас есть Kutools for Excel, Countvisible и Sumvisible функции также могут помочь вам сразу подсчитать и суммировать отфильтрованные ячейки.

После установки Kutools for Excel, введите следующие формулы для подсчета или суммирования отфильтрованных ячеек:

Подсчитайте отфильтрованные ячейки: = СОВМЕСТНО (C6: C19)

Суммируйте отфильтрованные ячейки: = СУММВИДИМЫЕ (C6: C19)

Советы: Вы также можете применить эти функции, нажав Kutools > Kutools Функции > Статистические и математические > СРЕДНЕВИДИМЫЙ / СОВМЕСТНО / СУМВИДИМЫЙ как вам нужно. Смотрите скриншот:

Подсчет / суммирование ячеек на основе фильтра с определенными критериями с использованием формул

Иногда в отфильтрованных данных вы хотите подсчитать или суммировать на основе критериев. Например, у меня есть следующие отфильтрованные данные. Теперь мне нужно подсчитать и суммировать заказы с именем «Нелли». Здесь я представлю несколько формул для ее решения.

Подсчет ячеек на основе данных фильтра с определенными критериями:

Пожалуйста, введите эту формулу: =SUMPRODUCT(SUBTOTAL(3,OFFSET(B6:B19,ROW(B6:B19)-MIN(ROW(B6:B19)),,1)), --( B6:B19="Nelly")) (B6: B19 это отфильтрованные данные, которые вы хотите использовать, а текст Нелли является критерием, по которому вы хотите вести подсчет), а затем нажмите Enter ключ для получения результата:

Суммируйте ячейки на основе данных фильтра с определенными критериями:

Чтобы суммировать отфильтрованные значения в столбце C на основе критериев, введите следующую формулу: =SUMPRODUCT(SUBTOTAL(3,OFFSET(B6:B19,ROW(B6:B19)-MIN(ROW(B6:B19)),,1)),( B6:B19="Nelly")*(C6:C19)) (B6: B19 содержит критерии, которые вы хотите использовать, текст Нелли это критерии, и C6: C19 - значения ячеек, которые вы хотите суммировать), а затем нажмите Enter чтобы вернуть результат, как показано на следующем снимке экрана:

Читайте также: