Как подсчитать пустые или непустые ячейки в диапазоне фильтрации Excel?
При работе с большими наборами данных в Excel фильтры нередко применяются для отображения только нужных записей. После фильтрации часто возникает необходимость дополнительно проанализировать видимые (отфильтрованные) данные — например, подсчитать количество пустых или непустых ячеек. Хотя Excel предоставляет базовые инструменты для подсчёта видимых ячеек, быстро получить именно число пустых или именно непустых ячеек в отфильтрованном списке бывает непросто без подходящего метода. Точный подсчёт таких значений особенно важен при очистке данных, обобщении ответов на опросы или выявлении незаполненных записей в отфильтрованных отчётах. В этой статье описаны несколько эффективных решений — от формул до методов на основе VBA, — которые помогут справиться с этой задачей в различных практических ситуациях. Вы также найдёте рекомендации по устранению типичных проблем и советы по адаптации каждого подхода под конкретные сценарии.
Подсчёт пустых ячеек в Диапазон фильтрации с помощью формулы
Подсчёт непустых ячеек в Диапазон фильтрации с помощью формулы
Подсчёт пустых или непустых ячеек в Диапазон фильтрации с помощью кода VBA
Подсчёт пустых ячеек в Диапазон фильтрации с помощью формулы
Чтобы подсчитать только пустые ячейки в диапазоне фильтрации, можно использовать комбинацию функции SUBTOTALи вспомогательного столбца. Этот метод идеально подходит для списков с отфильтрованными данными, где необходимо игнорировать скрытые строки и точно подсчитать количество видимых пустых записей в указанном столбце.
Введите эту формулу в ячейку, где должен отобразиться результат подсчёта пустых ячеек:
=SUBTOTAL(3,A2:A20)-SUBTOTAL(3,B2:B20)
После ввода формулы нажмите клавишу Enter. Результат покажет количество видимых пустых ячеек в вашем отфильтрованном списке. Пример — на скриншоте ниже:
Пояснение и советы по использованию:
- В этой формуле
A2:A20должен быть вспомогательным столбцом, который гарантированно не содержит пустых ячеек (например, столбец с последовательными номерами или уникальными идентификаторами строк). B2:B20— это диапазон, в котором нужно подсчитать количество пустых ячеек.- Функция SUBTOTAL(3, range) возвращает количество непустых видимых ячеек в ограниченном диапазоне. В данном случае вычитание количества непустых ячеек в диапазоне
B2:B20из общего количества во вспомогательном столбце даёт число пустых ячеек в отфильтрованных (видимых) данных. - Этот подход учитывает только ячейки, остающиеся видимыми после применения фильтров, поэтому он не подсчитывает пустые ячейки в строках, скрытых фильтрами.
- Обязательно скорректируйте диапазоны ()
A2:A20иB2:B20), чтобы они соответствовали вашим реальным данным. Вспомогательный столбец (A) должен содержать значения в каждой строке — если во вспомогательном столбце есть пустые ячейки, результаты могут оказаться неточными.
Типичные проблемы и способы их устранения:
- Если вспомогательный столбец содержит скрытые или пустые значения, подсчёт пустых ячеек окажется некорректным. Убедитесь, что вспомогательный столбец заполнен полностью.
- Если вы добавляете или удаляете строки, обязательно скорректируйте диапазон в формуле — иначе данные в начале или конце могут быть проигнорированы.
- Убедитесь, что фильтр действительно применён; в противном случае функция промежуточного итога учтёт все строки.
Подсчёт непустых ячеек в Диапазон фильтрации с помощью формулы
Чтобы подсчитать количество видимых непустых ячеек в отфильтрованном столбце, Excel предлагает простое решение с помощью функции SUBTOTAL. Этот метод идеально подходит, если вам нужно быстро получить число непустых записей, оставшихся после применения фильтра, игнорируя скрытые строки.
Чтобы воспользоваться этим решением, введите следующую формулу в пустую ячейку, где вы хотите отобразить результат:
=SUBTOTAL(102,B2:B20)
Затем нажмите клавишу Enter. Excel немедленно отобразит количество видимых непустых ячеек в ограниченном диапазоне. Пример см. на скриншоте ниже:
Пояснение и советы по использованию:
- Здесь
B2:B20представляет анализируемый столбец. Настройте этот диапазон в соответствии со своими данными. - Аргумент
102в функции SUBTOTAL гарантирует подсчёт только видимых ячеек и игнорирует как строки, скрытые фильтрами, так и пустые ячейки. - Это решение идеально подходит для стандартных отфильтрованных списков в одном столбце.
Меры предосторожности:
- Этот метод не учитывает ячейки, которые кажутся «пустыми», но содержат формулы, возвращающие пустую строку («»), так как Excel считает их заполненными.
- Если вы работаете с объединённым или нестандартным диапазоном, обязательно проверьте точность результата формулы.
- Не забудьте обновить диапазон при добавлении новых строк или перемещении данных.
Подсчёт пустых или непустых ячеек в Диапазон фильтрации с помощью кода VBA
Для более сложных сценариев, когда ваш Диапазон фильтрации может включать несмежные диапазоны, несколько столбцов или требует гибкого подхода, макрос VBA эффективно перебирает только видимые ячейки и обеспечивает точный подсчёт пустых или непустых ячеек. Этот метод особенно подходит, если необходима гибкость, например, подсчёт пустых/непустых ячеек сразу в нескольких столбцах или в сложных структурах данных, которые трудно обработать с помощью формул.
Преимущества и применимые сценарии:
- Может работать с несколькими столбцами и несмежными диапазонами за одну операцию
- Легко адаптируется к фильтрам — учитывает только видимые ячейки
- Позволяет выбрать, подсчитывать ли пустые или непустые ячейки за одну операцию
- Наиболее подходит для опытных пользователей, знакомых с макросами, когда стандартные формулы недостаточны
Ограничения:
- Требует доступа к редактору VBA и включённых разрешений на выполнение макросов
- Логика подсчёта рассматривает ячейки, содержащие результат формулы «», как пустые
1. На вкладке Разработчик нажмите Visual Basic, чтобы открыть редактор Microsoft Visual Basic для приложений. В окне VBA выберите Вставка > Модуль, чтобы создать новый модуль. Затем скопируйте и вставьте приведённый ниже код в окно модуля:
Sub CountVisibleBlanksOrNonBlanks()
Dim rng As Range
Dim cell As Range
Dim countBlanks As Long
Dim countNonBlanks As Long
Dim resp As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.Selection
Set rng = Application.InputBox("Select the filtered range to analyze", xTitleId, rng.Address, Type:=8)
If rng Is Nothing Then Exit Sub
resp = MsgBox("Do you want to count BLANK cells? (Click No to count non-blank cells)", vbYesNo + vbQuestion, xTitleId)
countBlanks = 0
countNonBlanks = 0
For Each cell In rng.SpecialCells(xlCellTypeVisible)
If cell.Value = "" Then
countBlanks = countBlanks + 1
Else
countNonBlanks = countNonBlanks + 1
End If
Next cell
If resp = vbYes Then
MsgBox "Number of visible blank cells: " & countBlanks, vbInformation, xTitleId
Else
MsgBox "Number of visible non-blank cells: " & countNonBlanks, vbInformation, xTitleId
End If
End Sub2. Нажмите F5, чтобы запустить код.
- Появится запрос, предлагающий выбрать или подтвердить целевой диапазон.
- Макрос спросит, хотите ли вы подсчитать пустые ячейки — нажмите «Да». Если же вы предпочитаете подсчитать непустые ячейки, нажмите «Нет».
- Результат отобразится в диалоговом окне с указанием количества пустых или непустых видимых ячеек.
Советы по работе и обработка ошибок:
- Если ваш диапазон включает объединённые ячейки, макрос всё равно выполнит подсчёт корректно — однако следите за возможными перекрытиями или несоответствиями в отфильтрованных данных.
- Если вы попытаетесь запустить макрос без выделенного диапазона, система предложит выбрать допустимый.
- При работе с большими наборами данных макрос может выполняться несколько секунд. Дождитесь появления сообщения с результатом.
- Если появляется ошибка или диалоговое окно с надписью «Ячейки не найдены», убедитесь, что выделение включает хотя бы одну видимую строку и что фильтр активен.
Рекомендации по итогам: Для повседневного использования формулы Excel просты, быстры и не требуют дополнительной настройки. Однако если возникают задачи с несколькими столбцами, несмежными данными или необходимостью большей гибкости, решение на основе VBA оказывается гораздо более адаптивным. Всегда сохраняйте свою работу перед запуском макросов и убедитесь, что параметры безопасности позволяют выполнять макросы.
Лучшие инструменты повышения продуктивности в 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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек