Как подсчитать или просуммировать ячейки в Excel на основе фильтра с заданными критериями?
В повседневной работе с анализом данных в Excel часто возникает необходимость получать итоговые значения или подсчёты только для отфильтрованных строк — особенно при работе с длинными списками и отчётами, где важно сосредоточиться на конкретных сегментах данных. Стандартные функции Excel, такие как COUNTA и SUM, отлично подходят для неотфильтрованных диапазонов, но при применении фильтров (например, скрытии определённых строк или сужении представления по заданным критериям) они продолжают учитывать скрытые строки, что приводит к неточным результатам. Чтобы надёжно рассчитывать итоги и количества, корректно учитывающие ваши фильтры — включая условия и критерии, — в этом руководстве представлены практические решения для различных сценариев и уровней владения Excel.
Подсчёт / суммирование ячеек на основе фильтра с помощью формул
Подсчёт / суммирование ячеек на основе фильтра с помощью Kutools для Excel
Подсчёт / суммирование ячеек на основе фильтра с определёнными критериями с использованием формул
Подсчёт / суммирование ячеек на основе фильтра с помощью формул
Excel предлагает функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTAL) для работы с отфильтрованными данными, которая позволяет точно подсчитывать или суммировать только видимые ячейки, игнорируя скрытые. Это особенно ценно при анализе наборов данных, отфильтрованных по определённым значениям или условиям. Благодаря использованию ПРОМЕЖУТОЧНЫХ.ИТОГОВ вычисления автоматически адаптируются при изменении условий фильтрации, обеспечивая безупречную точность анализа.
Чтобы подсчитать ячейки в Диапазон фильтрации, используйте следующую формулу в ячейке, где должен появиться результат (например, D1):
=SUBTOTAL(3, C6:C19)Здесь C6:C19 — это отфильтрованный диапазон данных, который необходимо подсчитать. После ввода формулы нажмите Enter, и она вернёт количество только видимых (отфильтрованных) ячеек в этом диапазоне.
Чтобы просуммировать значения в Диапазон фильтрации, введите следующую формулу (например, в D2):
=SUBTOTAL(9, C6:C19)Эта формула суммирует только видимые ячейки после фильтрации. Нажмите Enter, чтобы увидеть итог.
Советы: Первое число в функции SUBTOTAL — это параметр function_num, определяющий тип вычисления: 3 соответствует функции COUNTA (подсчёт непустых значений), а 9 — функции СУММ. Всегда убедитесь, что фильтр активен и настроен правильно, прежде чем доверять полученным результатам. Если диапазон данных изменится, скорректируйте ссылки на ячейки соответствующим образом. Формула автоматически пересчитывается при изменении или отмене фильтра.
Подсчёт / суммирование ячеек на основе фильтра с помощью Kutools для Excel
С помощью Kutools для Excel пользователи могут использовать специализированные функции — COUNTVISIBLE и SUMVISIBLE — чтобы мгновенно получать результаты подсчёта и суммирования только по видимым ячейкам (то есть отфильтрованным и не скрытым), обходя ограничения стандартных формул Excel. Это особенно удобно при частом анализе отфильтрованных данных: экономит время и снижает риск ошибок при ручном вводе.
После установки Kutools для Excelвведите следующие формулы на листе для расчёта результатов по отфильтрованным ячейкам (например, в D1 или D2):
Чтобы подсчитать отфильтрованные ячейки, используйте:
=COUNTVISIBLE(C6:C19)Чтобы просуммировать видимые отфильтрованные ячейки, используйте:
=SUMVISIBLE(C6:C19)
Совет: Эти функции работают как со строками, скрытыми вручную, так и с отфильтрованными строками, обеспечивая соответствие расчётов тому, что отображается на экране. Вы также можете получить к ним доступ через меню Kutools: щёлкните Kutools > Расширенные функции > Статистика и математика > AVERAGEVISIBLE / COUNTVISIBLE / SUMVISIBLE. Так вы получите быстрый доступ к мощным сводным функциям для отфильтрованных наборов данных.
Примечания: Если вы измените фильтр или скроете строки, расширенные функции обновятся автоматически. Эти формулы доступны только после установки Kutools и отсутствуют в стандартных версиях Excel.
Kutools для Excel— раскройте весь потенциал Excel с помощью более чем 300 незаменимых инструментов, которые ускоряют и упрощают вашу работу, а также воспользуйтесь возможностями ИИ для более разумной обработки данных и повышения продуктивности.Получить сейчас
Подсчёт / суммирование ячеек на основе фильтра с определёнными критериями с использованием формул
На практике может потребоваться подсчитать или просуммировать отфильтрованные данные на основе Дополнительное условие — например, подсчитать только строки, где указано определённое имя. Хотя фильтрация визуально сужает данные, использование формул позволяет выполнять такие расчёты на лету без постоянной настройки фильтров. Ниже приведены полезные формулы для подобных сценариев.
Подсчёт ячеек на основе отфильтрованных данных с определёнными критериями:
Чтобы подсчитать видимые (отфильтрованные) ячейки, соответствующие определённому условию — например, содержащие имя «Nelly» — введите следующую формулу в ячейку (например, D1):
=SUMPRODUCT(SUBTOTAL(3,OFFSET(B6:B19,ROW(B6:B19)-MIN(ROW(B6:B19)),,1)), --(B6:B19="Nelly"))Здесь B6:B19 — это диапазон данных, а «Nelly» — ваш критерий. Формула подсчитает только видимые строки, соответствующие заданному условию после фильтрации. Нажмите Enter, и результат отобразится в ячейке.
Суммирование ячеек на основе отфильтрованных данных с определёнными критериями:
Если вам также нужно просуммировать элементы с теми же критериями, используйте следующую расширенную формулу (например, введите в D2):
=SUMPRODUCT(SUBTOTAL(3,OFFSET(B6:B19,ROW(B6:B19)-MIN(ROW(B6:B19)),,1)),(B6:B19="Nelly")*(C6:C19))В этой формуле B6:B19 — это столбец с критериями, C6:C19 — столбец с суммируемыми значениями, а «Nelly» остаётся критерием. Формула возвращает сумму соответствующих значений из C6:C19, где выполняется условие и строки видимы. Нажмите Enter, чтобы подтвердить и отобразить сумму.
Совет: При вводе этих формул убедитесь, что ваш диапазон и критерии соответствуют отфильтрованным данным. Формулы динамически учитывают изменения фильтров, автоматически обновляя итоги или подсчёты. Чтобы использовать другие критерии, просто замените «Nelly» на нужное значение.
Код VBA — автоматический подсчёт или суммирование только видимых ячеек на основе фильтра и критериев с помощью пользовательского макроса
Для пользователей, знакомых с макросами, VBA предоставляет гибкий способ подсчёта или суммирования только видимых ячеек с возможностью задания критериев — это особенно полезно, если ваши фильтры часто меняются или требуется автоматизация таких расчётов. В отличие от формул, макросы быстро обрабатывают большие объёмы данных, и их поведение можно настроить под конкретные задачи.
Применимые сценарии: Идеально подходит тем, кто работает с большими отфильтрованными таблицами и нуждается в пользовательских расчётах, недоступных в стандартных формулах. Преимущества — автоматизация и универсальность, недостатки — необходимость первоначальной настройки и включение поддержки макросов.
Меры предосторожности: Всегда сохраняйте свою работу перед запуском скриптов VBA. Макросы доступны только в настольных версиях Excel и недоступны в веб- или мобильных версиях.
1. Щёлкните Инструменты разработчика > Visual Basic. В открывшемся окне Microsoft Visual Basic для приложений щёлкните Вставка > Модуль и вставьте следующий код в панель модуля:
Sub SumOrCountVisibleCellsWithCriteria()
Dim CriteriaCol As Range
Dim DataCol As Range
Dim Criteria As String
Dim Total As Double
Dim Count As Long
Dim i As Integer
Dim LastRow As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set CriteriaCol = Application.InputBox("Select criteria column (e.g. B6:B19)", xTitleId, Type:=8)
Set DataCol = Application.InputBox("Select data/sum column (for sum, e.g. C6:C19; same as criteria for count)", xTitleId, Type:=8)
Criteria = Application.InputBox("Enter criteria (e.g. Nelly)", xTitleId, "", Type:=2)
Total = 0
Count = 0
LastRow = CriteriaCol.Rows.Count
For i = 1 To LastRow
If Not CriteriaCol.Rows(i).EntireRow.Hidden Then
If CriteriaCol.Cells(i, 1).Value = Criteria Then
Total = Total + DataCol.Cells(i, 1).Value
Count = Count + 1
End If
End If
Next i
MsgBox "Sum: " & Total & vbCrLf & "Count: " & Count, vbInformation, xTitleId
End Sub2. Нажмите кнопку 
, чтобы запустить макрос. Появится диалоговое окно, в котором нужно выбрать столбец с критериями и столбец для суммирования или подсчёта, а затем указать нужный критерий (например, имя). После этого макрос покажет сумму и количество видимых ячеек, соответствующих вашим условиям.
Советы: Этот макрос объединяет подсчёт и суммирование только для видимых строк отфильтрованных данных. При изменении параметров CriteriaCol и DataCol вы можете адаптировать анализ под разные задачи. Всегда убедитесь, что выбранные столбцы соответствуют вашей настройке фильтра. Если вам нужно только посчитать количество (без суммирования), укажите один и тот же диапазон для обоих входных параметров.
Устранение неполадок: Если возникает ошибка времени выполнения, убедитесь, что вы выбрали диапазон того же размера и что ваши критерии точно совпадают с текстом в ячейках. При работе с большими наборами данных производительность может варьироваться; рекомендуем предварительно отфильтровать данные до нужных строк перед запуском макроса.
Сводная таблица — используйте Сводная таблица для сводного анализа (подсчёта/суммирования) отфильтрованных данных, в том числе по критериям, с возможностями интерактивной фильтрации
Сводная таблица — это универсальный и интерактивный инструмент Excel для анализа больших объёмов данных, включая отфильтрованные результаты. С её помощью вы легко группируете, подсчитываете и суммируете данные по нужным критериям — например, по имени или категории, — а встроенные фильтры позволяют мгновенно изменять отображаемую информацию.
Применимые сценарии: Идеально подходит для ситуаций, когда нужны динамические сводки, гибкая агрегация по разным полям или интерактивный анализ результатов за счёт быстрого изменения критериев. Преимущества — простота в использовании и мгновенный пересчёт благодаря функции перетаскивания.
Инструкции по использованию:
1. Выделите отфильтрованный диапазон данных, убедившись, что он включает все столбцы для анализа (в том числе заголовки).
2. Перейдите в меню Вставка > Сводная таблица. В диалоговом окне убедитесь, что таблица или диапазон указаны верно, и выберите место размещения сводной таблицы — новый лист или существующий лист.
3. В списке полей Сводной таблицы перетащите поле критерия (например, «Имя») в область Строки. Затем перетащите целевое поле (например, «Сумма заказа») в область Значения. По умолчанию данные суммируются. Если нужно, щёлкните по полю и измените тип сводки на «Количество» или другой вариант.
4. Используйте встроенные раскрывающиеся фильтры в сводной таблице, чтобы отображать только нужные элементы (например, отфильтруйте по «Нелли») или задайте сразу несколько критериев, чтобы сосредоточиться на соответствующих данных.
5. Ваша сводная таблица немедленно обновится, отображая сумму и/или количество для видимых критериев. Вы можете изменить расположение полей, добавить дополнительные фильтры или отформатировать сводную таблицу для лучшей читаемости.
Советы: Сводные таблицы не реагируют напрямую на фильтры листа, а используют собственные элементы управления фильтрацией, которые могут быть значительно мощнее и гибче. Для углублённого анализа используйте срезы или дополнительные вычисляемые поля. Обновляйте сводную таблицу, если вы изменили исходные данные.
Устранение неполадок: Если результаты не соответствуют ожиданиям, проверьте выбранные поля и убедитесь, что исходный диапазон включает все необходимые соответствующие данные. Если ваши данные не содержат чётких заголовков, добавьте их перед созданием сводной таблицы.
Рекомендации по выбору метода сводки: Каждое решение в этом руководстве решает конкретную задачу — от простых формул до автоматизации и интерактивного анализа. Используйте функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ или расширенные функции для простых итогов и расчётов по отфильтрованным данным, сложные формулы — когда нужны результаты по заданным критериям, макросы — для автоматизации, а сводные таблицы — для максимально гибкой сводки и глубокого анализа данных. Всегда проверяйте ссылки на ячейки и корректность критериев, чтобы избежать ошибок в результатах. Для повышения эффективности храните данные в упорядоченном виде с чёткими заголовками и единообразным форматированием.
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек