Vba excel подсчитывает количество заполненных ячеек в excel
Обновлено: 20.11.2024
Мы знаем, что на вкладке «Формулы Microsoft Excel» в категории «Дополнительные функции» > «Статистические» есть функция СЧЁТЕСЛИ, которая подсчитывает количество ячеек в диапазоне, удовлетворяющих заданному условию. Критерии для этой функции ограничены текстом или числами. Однако с помощью VBA мы можем создать функцию для подсчета количества ячеек с другими критериями, такими как цвет ячейки.
Подробнее
С помощью VBA можно создать определяемую пользователем функцию (UDF) и сохранить ее в файле надстройки, чтобы ее можно было использовать в других книгах и перенести на другие компьютеры.
Как создать пользовательскую функцию
Вот шаги по созданию пользовательской функции для подсчета цвета ячейки:
Откройте Microsoft Excel, затем нажмите клавиши ALT+F11, чтобы отобразить окно редактора Visual Basic (VBE).
В меню "Вставка" выберите "Модуль", чтобы создать модуль. Затем введите следующий скрипт:
Закройте окно VBE, чтобы вернуться в Excel.
Чтобы протестировать UDF, создайте пример данных, который содержит столбец ячеек разного цвета.
В ячейке D3 напишите функцию:
В аргументе "range_data" выберите ячейку от C2 до C51.
В аргументе "критерии" выберите ячейку F1.
Нажмите Enter. Результат в ячейке F2 равен 6. Это означает, что количество ячеек, заштрихованных синим цветом, равно 6.
Вы можете проверить, используя другие цвета. Измените цвет в ячейке F1 на любой цвет, который вы хотите из данных, используя Главная > Цвет заливки.
Вы также можете заархивировать UDF, чтобы эту функцию можно было использовать в другой книге или на другом компьютере. Для этого выполните следующие действия:
Шаг 1. Сохраните книгу
а. Выберите «Файл», а затем «Сохранить как». (При необходимости выберите Обзор.)
б. Выберите надстройку Excel (.xlam) в качестве формата и дайте файлу имя, например CountCcolor.
Вы можете сохранить файл надстройки в любом месте. Но для того, чтобы он был указан как надстройка в вашей программе Excel, сохраните его в местоположении по умолчанию. В Windows 7 расположение по умолчанию для любой версии Microsoft Excel: C:\Users\RADDINI\AppData\Roaming\Microsoft\AddIns
Шаг 2. Установите надстройку
а. Откройте Microsoft Excel на компьютере, где вы хотите установить надстройку.
б. Откройте диалоговое окно «Надстройки», выбрав «Надстройки Excel для Excel 2013 и более поздних версий» на вкладке «Разработчик». (надстройки в Excel 2010.)
<р>в. В диалоговом окне "Надстройки" выберите "Обзор".д. Перейдите в папку, в которой сохранен файл надстройки (например, на USB-накопителе или в облачной папке). Выберите файл, а затем выберите Открыть.
<р>т.е. В диалоговом окне надстройки убедитесь, что флажок надстройки не установлен. Затем выберите ОК.Пользовательская функция Count Cell Color UDF установлена и готова к использованию. Вы сможете получить доступ к этой функции в любое время, поместив курсор в любую ячейку на листе и набрав:
Отказ от ответственности за содержание решений сообщества
КОРПОРАЦИЯ MICROSOFT И/ИЛИ ЕЕ СООТВЕТСТВУЮЩИЕ ПОСТАВЩИКИ НЕ ДАЮТ ЗАЯВЛЕНИЙ В ОТНОШЕНИИ ПРИГОДНОСТИ, НАДЕЖНОСТИ ИЛИ ТОЧНОСТИ ИНФОРМАЦИИ И СООТВЕТСТВУЮЩИХ ИЗОБРАЖЕНИЙ, СОДЕРЖАЩИХСЯ ЗДЕСЬ. ВСЯ ТАКАЯ ИНФОРМАЦИЯ И СООТВЕТСТВУЮЩИЕ ИЗОБРАЖЕНИЯ ПРЕДОСТАВЛЯЮТСЯ «КАК ЕСТЬ» БЕЗ КАКИХ-ЛИБО ГАРАНТИЙ. МАЙКРОСОФТ И/ИЛИ ЕЕ СООТВЕТСТВУЮЩИЕ ПОСТАВЩИКИ НАСТОЯЩИМ ОТКАЗЫВАЮТСЯ ОТ ВСЕХ ГАРАНТИЙ И УСЛОВИЙ В ОТНОШЕНИИ ЭТОЙ ИНФОРМАЦИИ И СООТВЕТСТВУЮЩИХ ИЗОБРАЖЕНИЙ, ВКЛЮЧАЯ ВСЕ ПОДРАЗУМЕВАЕМЫЕ ГАРАНТИИ И УСЛОВИЯ КОММЕРЧЕСКОЙ ПРИГОДНОСТИ, ПРИГОДНОСТИ ДЛЯ ОПРЕДЕЛЕННОЙ ЦЕЛИ, РАБОЧИХ УСИЛИЙ, НАЗВАНИЯ И НЕНАРУШЕНИЯ ПРАВ. ВЫ ОСОБЕННО СОГЛАШАЕТЕСЬ С ТЕМ, ЧТО КОМПАНИЯ MICROSOFT И/ИЛИ ЕЕ ПОСТАВЩИКИ НИ ПРИ КАКИХ ОБСТОЯТЕЛЬСТВАХ НЕ НЕСУТ ОТВЕТСТВЕННОСТИ ЗА ЛЮБЫЕ ПРЯМЫЕ, КОСВЕННЫЕ, ШТРАФНЫЕ, СЛУЧАЙНЫЕ, ОСОБЫЕ, КОСВЕННЫЕ УБЫТКИ ИЛИ ЛЮБЫЕ УБЫТКИ, ВКЛЮЧАЯ, ПОМИМО ПРОЧЕГО, УБЫТКИ ОТ ПОТЕРИ ИСПОЛЬЗОВАНИЯ, ДАННЫХ ИЛИ ПРИБЫЛИ, ВОЗНИКАЮЩИЕ ИЛИ КАКИМ-ЛИБО СВЯЗАННЫМ С ИСПОЛЬЗОВАНИЕМ ИЛИ НЕВОЗМОЖНОСТЬЮ ИСПОЛЬЗОВАНИЯ ИНФОРМАЦИИ И СООТВЕТСТВУЮЩИХ ИЗОБРАЖЕНИЙ, СОДЕРЖАЩИХСЯ ЗДЕСЬ, НА ОСНОВЕ ДОГОВОРА, ПРАВОВОГО ДЕЛА, НЕБРЕЖНОСТИ, СТРОГОй ОТВЕТСТВЕННОСТИ ИЛИ ИНЫМ ОБРАЗОМ, ДАЖЕ ЕСЛИ МАЙКРОСОФТ ИЛИ ЕЕ ПОСТАВЩИКИ БЫЛИ УВЕДОМЛЕНЫ О ВОЗМОЖНОСТЬ ПОВРЕЖДЕНИЯ.
Мой первый столбец — это индекс с монотонно возрастающими числами, который заканчивается в непредсказуемой точке. Теперь я хочу узнать, сколько записей в этом столбце. Есть ли лучший способ, чем повторять это и следить за пустой ячейкой?
5 ответов 5
Если вы хотите найти последнюю заполненную ячейку в определенном столбце, лучший способ:
В этом коде используется самая последняя ячейка во всем столбце (65536 для Excel 2003, 1048576 в более поздних версиях), а затем выполняется поиск первой заполненной ячейки над ней. Это позволяет игнорировать «разрывы» в ваших данных и находить истинную последнюю строку.
Один из способов: (Предполагается, что столбец индекса начинается с A1)
Который ищет 1-ю незанятую ячейку вниз от A1 и показывает вам ее порядковый номер строки.
Вы можете выбрать следующую пустую ячейку с помощью:
Если вам нужен конец набора данных (включая пробелы), попробуйте: Range("A:A").SpecialCells(xlLastCell).Row
Лучше сделать так, как показано ниже на примере MrKowz. Если по какой-то причине есть пустая строка, вы можете пропустить много данных.
В некоторых случаях, однако, в этом вопросе конкретно указано, что это касается заполненных ячеек, и неправильный подсчет из-за несмежных ячеек может быть столь же плохим
Вы также можете использовать
чтобы предоставить вам диапазон, представляющий границы ваших данных на текущем активном листе
Msdn говорит по теме
Возвращает объект Range, представляющий текущий регион. Текущая область — это диапазон, ограниченный любой комбинацией пустых строк и пустых столбцов. Только для чтения.
Затем вы можете определить количество столбцов с помощью
и количество строк через
Существует так много разных способов сделать это, и некоторые из них категорически поддерживаются как правильные, но это единственное, что сработало для меня. Мои данные генерируются программным обеспечением, которое, по-видимому, помещает нули во многие ячейки после последней строки данных, поэтому «UsedRange» не удалось. Это решение намного проще, чем любые другие.
При использовании Excel нам нужно подсчитывать заполненные ячейки для различных вычислений. В этой статье я собираюсь показать, как подсчитать заполненные ячейки в Excel с помощью 5 быстрых методов. Просто взгляните на следующие методы должным образом, и вы найдете их полезными для применения.
Скачать книгу с практиками
Загрузите книгу Excel, которую мы использовали для подготовки этой статьи.
5 быстрых способов подсчета заполненных ячеек в Excel
Способ 1. Использование функции СЧЕТЧИК для подсчета заполненных ячеек в Excel
Давайте сначала познакомимся с нашим набором данных. Здесь я использовал 3 столбца и 7 строк, чтобы показать продажи некоторых продавцов в разных штатах. Вы заметите, что некоторые ячейки пусты. Теперь мы подсчитаем заполненные ячейки столбца C, используя функцию COUNTA. Функция COUNTA используется для подсчета непустых ячеек.
Шаги:
➽ Активировать ячейку D13
➽ Введите приведенную ниже формулу:
➽ Затем нажмите кнопку Enter.
И у нас есть количество заполненных ячеек в столбце C равно 4
Способ 2. Вставка функции СЧЁТЕСЛИМН в Excel для подсчета заполненных ячеек
Теперь подсчитаем заполненные ячейки с помощью функции СЧЁТЕСЛИМН. Эта функция используется для подсчета количества ячеек, соответствующих нескольким критериям в одном или разных диапазонах. Здесь я посчитаю ячейки штата Аризона со значениями продаж.
Шаги:
➽ Введите формулу в ячейку G5, приведенную ниже:
➽ Теперь просто нажмите кнопку Enter, и вы сразу получите результат.
Способ 3. Применение инструмента Excel «Найти и заменить» для подсчета заполненных ячеек
В этом методе мы будем использовать инструмент «Найти и заменить» для подсчета заполненных ячеек. Давайте посмотрим, как его использовать.
Шаг 1:
➽ Выберите диапазон ячеек: от B5 до D11.
➽ Нажмите Ctrl+F. Появится диалоговое окно инструмента «Найти и заменить».
➽ Введите «*» в поле «Найти».
➽ Выберите «Формулы» в раскрывающемся списке «Искать в».
➽ Наконец, нажмите «Найти все».
См. изображение ниже, панель расширения показывает общее количество найденных заполненных ячеек.
Шаг 2:
➽ Затем выберите расположение всех ячеек в диалоговом окне, и заполненные ячейки будут выделены в наборе данных.
Метод 4. Объединение функций СУММПРОИЗВ и ДЛСТР для подсчета заполненных ячеек
Теперь мы воспользуемся комбинацией функций СУММПРОИЗВ и ДЛСТР для подсчета заполненных ячеек. Функция СУММПРОИЗВ возвращает сумму произведений соответствующих диапазонов или массивов, а функция ДЛСТР используется для возврата длины заданной текстовой строки. Мы будем использовать их комбинацию, чтобы найти все заполненные ячейки во всем диапазоне данных.
Шаги:
➽ Введите формулу в ячейку D13, приведенную ниже:
➽ Нажмите кнопку "Ввод"
👇 Разбивка формулы:
➥ ДЛСТР(B5:D11)>0
Он проверит ячейки, есть ли в них хотя бы один символ или нет. И он вернется как-
➥ –(ДЛСТР(B5:D11)>0)
Эта формула покажет предыдущий результат в двоичном виде, как показано ниже:
➥ СУММПРОИЗВ(–(ДЛСТР(B5:D11)>0))
Наконец, функция СУММПРОИЗВ покажет количество найденных заполненных ячеек, которые будут возвращены как-
Способ 5. Введите специальную формулу Excel для подсчета всех заполненных ячеек в Excel
В этом последнем методе я буду использовать специальную формулу для подсчета всех заполненных ячеек. На самом деле это комбинация функций COLUMNS, ROWS и COUNTBLANK. Функция COLUMNS используется для подсчета номеров столбцов в диапазоне. Функция ROWS используется для подсчета номеров строк в диапазоне. А функция СЧИТАТЬПУСТОТЫ подсчитывает пустые ячейки.
Шаги:
➽ Введите формулу в ячейку G5, приведенную ниже:
➽ Затем просто нажмите кнопку Enter, чтобы получить результат.
👇 Разбивка формулы:
➥ СЧИТАТЬПУСТОТЫ(B5:D11)
Эта формула будет подсчитывать пустые ячейки в диапазоне (B5:D11). Он вернется как-
➥ РЯДЫ(B5:D11)
Он подсчитает количество строк в диапазоне (B5:D11) и вернет как-
➥ КОЛОННЫ(B5:D11)
Он подсчитает количество столбцов в диапазоне (B5:D11) и вернет как-
➥ СТОЛБЦЫ(B5:D11)*СТРОКИ(B5:D11)-СЧЕТПУСТЫЕ(B5:D11)
Наконец, он будет вычитать количество пустых ячеек из произведения умножения количества строк и столбцов. Затем результат вернется как-
Заключение
Я надеюсь, что все описанные выше методы будут достаточно эффективны для подсчета заполненных ячеек в Excel. Не стесняйтесь задавать любые вопросы в разделе комментариев и, пожалуйста, дайте мне отзыв
Как подсчитать пустые или непустые ячейки в диапазоне в Excel?
Если в диапазоне Excel есть несколько ячеек без данных, и вы просто хотите узнать, сколько непустых ячеек или пустых ячеек находится в этом диапазоне, как вы можете это сделать? Теперь я могу представить вам два способа подсчета пустых и непустых ячеек в диапазоне в Excel.
Подсчет пустых или непустых ячеек с формулой
В Excel есть формулы, которые помогут вам быстро подсчитать только пустые или непустые ячейки в диапазоне.
Например, вам нужно подсчитать пустые ячейки только в этом диапазоне, как показано ниже, и выбрать пустую ячейку, чтобы ввести эту формулу =СЧЁТЕСЛИ(A1:G11,"") ( диапазон A1: G11 указывает диапазон, из которого вы хотите подсчитать пустые ячейки, вы можете изменить его по мере необходимости) и нажмите кнопку «Ввод» на клавиатуре, вы подсчитаете пустые ячейки. Смотрите скриншот:
Совет. Вы также можете использовать эту формулу: =СЧИТАТЬПУСТОТЫ(A1:G11) для подсчета только пустых ячеек. Если вы хотите подсчитывать только непустые ячейки, вы можете использовать эту формулу =COUNTA(A1:G11) (диапазон A1:G11 указывает диапазон, из которого вы хотите подсчитать непустые ячейки, вы можете изменить его как вам нужно), или вы также можете использовать эту формулу =COUNTIF(A1:G11,"<>") .
Легкий подсчет и выбор всех непустых ячеек в диапазоне в Excel:
Утилита Kutools for Excel «Выбрать непустые ячейки» поможет вам легко подсчитать и выбрать все непустые ячейки в выбранном диапазоне в Excel, как показано на снимке экрана ниже.
Скачай и попробуй прямо сейчас! (60-дневная бесплатная пробная версия)
Подсчет пустых или непустых ячеек с помощью VBA
Выполните следующие шаги, чтобы использовать код VBA для преобразования счетчика только пустых или непустых ячеек.
<р>1. Удерживая нажатой клавишу ALT, нажмите F11 на клавиатуре, чтобы открыть окно Microsoft Visual Basic for Application. <р>2. Нажмите «Вставить» > «Модуль» и скопируйте VBA в модуль.VBA: считать только пустые ячейки
<р>3.Нажмите «Выполнить», чтобы запустить VBA, и появится диалоговое окно KutoolsforExcel, в котором вы можете выбрать рабочий диапазон. См. снимок экрана:<р>4. Затем нажмите OK в диалоговом окне, и появится другое диалоговое окно, в котором будет указано, сколько пустых ячеек находится в выбранном диапазоне.
Совет. Если вы хотите подсчитать непустые ячейки, вы можете сделать, как указано выше, и использовать следующий VBA:
VBA: подсчитывать только непустые ячейки
Легко подсчитывать непустые ячейки с помощью Kutools for Excel
Утилита «Выбрать непустые ячейки» в Kutools for Excel поможет вам выбрать все непустые ячейки в выбранном диапазоне одним щелчком мыши, а затем легко подсчитать количество выбранных ячеек.
Перед применением Kutools for Excel сначала загрузите и установите его.
<р>1. Выберите диапазон с непустыми ячейками, которые вы хотите подсчитать, затем нажмите Kutools > Выбрать > Выбрать непустые ячейки. Смотрите скриншот:<р>2. Затем появится диалоговое окно, чтобы сообщить вам, сколько непустых ячеек в выбранном диапазоне, нажмите кнопку «ОК», и эти непустые ячейки будут выбраны немедленно. Смотрите скриншот:
Если вы хотите получить бесплатную пробную версию (30 дней) этой утилиты, нажмите, чтобы загрузить ее, а затем перейдите к выполнению операции в соответствии с указанными выше шагами.
Читайте также: