Как подсчитать данные в Excel, игнорируя скрытые ячейки, строки или столбцы?
Обычно для подсчёта количества ячеек в отфильтрованном списке удобно использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTAL). Однако иногда необходимо подсчитать ячейки, игнорируя те, что скрыты вручную — будь то отдельные ячейки, строки или столбцы. В этой статье описаны различные способы подсчёта с исключением таких скрытых элементов в Excel.
- Подсчёт с игнорированием скрытых ячеек или строк с помощью функций Excel
- Подсчёт с игнорированием скрытых строк и столбцов с помощью Kutools для Excel
Подсчёт с игнорированием скрытых ячеек и строк с помощью функций Excel
В этом методе представлены функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTAL) и АГРЕГАТ (AGGREGATE), которые позволяют подсчитывать данные, игнорируя вручную скрытые ячейки или строки в Excel.
Например, я вручную скрыл несколько строк в таблице, как показано на снимке экрана ниже. Далее я подробно покажу, как подсчитать количество ячеек, исключая все скрытые строки и ячейки.
Выберите пустую ячейку для размещения результата подсчёта, введите формулу =SUBTOTAL(102,C2:C22) (C2:C22 — диапазон, в котором требуется выполнить подсчёт с игнорированием вручную скрытых ячеек и строк), а затем нажмите клавишу Enter.
Теперь вы получите количество ячеек в диапазоне «Ограниченный диапазон», за исключением всех вручную скрытых ячеек и строк.
Примечания:
(1) Чтобы подсчитать количество ячеек любого типа, игнорируя вручную скрытые ячейки и строки, используйте формулу =SUBTOTAL(103,A2:A22).
(2) В Excel 2010 или более поздней версии можно также использовать формулу =AGGREGATE(2,3,C2:C22) для подсчёта числовых ячеек с игнорированием вручную скрытых ячеек и строк. См. снимок экрана ниже; а также применяйте формулу =AGGREGATE(3,3,A2:A22), чтобы подсчитать количество ячеек любого типа, игнорируя вручную скрытые ячейки и строки.
(3) Все приведённые выше формулы учитывают ячейки в скрытых столбцах.
Подсчёт с игнорированием скрытых строк и столбцов с помощью Kutools для Excel
Например, я вручную скрыл 3 столбца и 4 строки в таблице, как показано на снимке экрана ниже, и сейчас подробно покажу, как подсчитать количество ячеек любого типа, игнорируя все скрытые строки и столбцы, с помощью функции COUNTVISIBLE из Kutools для Excel.
Выберите пустую ячейку для размещения результата подсчёта и введите формулу =COUNTVISIBLE(B2:H11) (B2:H11 — диапазон, в котором нужно подсчитать ячейки, игнорируя скрытые ячейки, строки и столбцы), а затем нажмите клавишу Enter.
Теперь вы получите общее количество ячеек любого типа в ограниченном диапазоне, исключая все скрытые строки и столбцы. См. снимок экрана:
Примечание: Вы также можете воспользоваться функцией COUNTVISIBLE следующим образом: 1. Выберите пустую ячейку и щёлкните Kutools > Функции > Статистические и математические > COUNTVISIBLE.
2. В открывшемся диалоговом окне «Аргументы функции» укажите в поле Ссылка диапазон, в котором нужно подсчитать ячейки, игнорируя все вручную скрытые строки и столбцы, затем нажмите кнопку ОК. См. снимок экрана выше:
Kutools для Excel— раскройте весь потенциал Excel с помощью более чем 300 незаменимых инструментов, которые ускоряют и упрощают вашу работу, а также воспользуйтесь возможностями ИИ для более разумной обработки данных и повышения продуктивности.Получить сейчас
Демонстрация: подсчёт с игнорированием скрытых ячеек/строк/столбцов в Excel
Связанные статьи:
Суммирование только отфильтрованных или видимых ячеек в Excel
Получение максимального/минимального значения только среди видимых ячеек в Excel
Лучшие инструменты повышения продуктивности в 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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек