Vba excel посчитать количество непустых ячеек в столбце
Обновлено: 05.07.2024
Возможно, вы часто использовали функции COUNT и COUNTA в Microsoft Excel. При этом COUNT захватывает все числовые значения в ячейке, COUNTA захватывает все непустые ячейки (включая строки). Можно ли каким-либо образом использовать функцию COUNTA через VBA? Абсолютно да! Вы можете использовать функцию COUNTA через VBA в качестве функции рабочего листа (так как она является частью семейства WorksheetFunction внутри VBA) для подсчета непустых ячеек через заданный диапазон рабочего листа. Преимущество функции COUNTA над COUNT заключается в том, что она может считать все (числа, строки, специальные символы, значения ошибок и т. Д.), Кроме пустых ячеек в данном диапазоне, тогда как COUNT может считать только количество ячеек, состоящих из числовых значений.,
Синтаксис VBA COUNTA:
Синтаксис функции VBA COUNTA показан ниже:
Эта функция может принимать до 30 аргументов за один вызов. Аргумент может состоять из диапазона ячеек или одного значения, которое вводится вручную.
Как использовать функцию COUNTA в Excel VBA?
Ниже приведены различные примеры использования функции COUNTA в Excel с использованием кода VBA.
Вы можете скачать этот шаблон VBA COUNTA Excel здесь - Шаблон VBA COUNTA Excel
Пример № 1 - VBA COUNTA с ручными аргументами в качестве входных данных
Мы увидим, как работает функция COUNTA, когда мы предоставим ей ручные аргументы в VBA. Выполните следующие шаги:
Шаг 1. Вставьте новый модуль в редактор Visual Basic (VBE). Нажмите на вкладку Вставка > выберите модуль.
Шаг 2: Определите новую подпроцедуру во вновь вставленном модуле, которая может содержать ваш макрос.
Код:
Шаг 3: Решите, где мы хотим сохранить выходные данные для функции COUNTA. Я хочу сохранить его в ячейке A2 листа с именем « Пример 1 ». Для этого нам нужно использовать метод Sheets.Range в VBA. Смотрите следующий скриншот для справки:
Код:
Этот фрагмент кода выбирает ячейку A2 в качестве диапазона из листа с именем Пример 1.
Шаг 4: Используйте оператор присваивания, чтобы мы могли присвоить значение ячейке, к которой будет осуществляться доступ, с помощью кода выше. Добавьте объект с именем WorksheetFunction, чтобы мы могли получить доступ к функции COUNTA под ним.
Код:
Шаг 5: Поставьте точку (.) После указанного объекта, и вы увидите список функций, доступных для использования под ним. Выберите COUNTA из списка нескольких доступных функций, который позволяет подсчитывать непустые ячейки или значения.
Код:
Шаг 7: Запустите этот фрагмент кода, нажав F5 или кнопку Run, и просмотрите результат под ячейкой A2 листа « Пример 1 » в активной рабочей книге.
В этом коде мы хотим, чтобы система посчитала входные аргументы и сохранила счет под ячейкой A2 в примере 1.
Пример № 2 - VBA COUNTA для подсчета непустых ячеек в заданном диапазоне
Предположим, у меня есть данные по всему столбцу A, и мне нужно посчитать, что является непустыми строками во всем столбце. Поскольку весь столбец состоит из более чем 10 строк Lac, для меня является идеальным трудоемким переходом и подсчетом каждой непустой ячейки. Смотрите частичный скриншот данных ниже.
Выполните следующие шаги:
Шаг 1: Определите новую подпроцедуру, которая может содержать ваш макрос.
Код:
Шаг 2: Определите две новые переменные в этой подпроцедуре как Range, используя Dim . Одна переменная будет полезна для хранения диапазона столбца входного аргумента, а другие переменные будут полезны для хранения ячейки, в которой мы хотим получить результат в виде подсчитанного числа.
Код:
Здесь rng_1 сохранит диапазон ввода для функции COUNTA. Вывод COUNTA будет сохранен в переменной op_cell .
Шаг 3: Теперь установите диапазон для обеих переменных, используя свойство VBA Set. Это должно быть сделано, потому что мы не можем напрямую присвоить значение переменной, определенной как объект диапазона.
Код:
Здесь переменная rng_1 была установлена в диапазон всего столбца с именем A. op_cell установлена в ячейку B1, поскольку это будет ячейка, которая содержит выходные данные COUNTA.
Шаг 4: Теперь используйте общий оператор присваивания для переменной op_cell, чтобы мы могли сохранить выходные данные в ячейке, для которой установлена эта переменная. Это может рассматриваться как выходная инициализация.
Код:
Шаг 5: Используйте объект WorksheetFunction в правой части оператора присваивания (это будет выражение, которое будет оценено, и значение будет сохранено в ячейке с переменной op_cell), чтобы инициализировать класс, к которому мы можем обращаться, а также использовать Функция COUNTA.
Код:
Шаг 6: Как только вы нажмете точку (.) После объекта WorksheetFunction, вы сможете получить доступ ко всем функциям, доступным в этом классе. Перейдите к функции COUNTA и дважды щелкните по ней, чтобы выбрать.
Шаг 7: Используйте переменную rng_1 в качестве входного аргумента в COUNTA. Следовательно, эта функция в классе объектов функций рабочего листа может подсчитывать непустые ячейки, присутствующие во всем столбце A.
Код:
Шаг 8: Запустите этот код, нажав F5 или кнопку Run, и вы увидите результат, как показано ниже, в ячейке B1 активного листа из рабочей книги.
В ячейке B1 мы можем видеть число как 17. Это означает, что у нас есть 17 непустых ячеек в столбце A рабочего листа. Вы можете вообще не видеть 17 на этом скриншоте, так как он является частичным. Вы могли бы лучше видеть рабочий лист и перемещаться по столбцу А.
То, что нужно запомнить
- Вы можете использовать функцию VBA COUNTA, когда вам нужно посчитать количество непустых ячеек, присутствующих в данном диапазоне.
- COUNTA рассматривает все значения, такие как числа, строки, значения ошибок, логические значения, пустой текст («»). Однако он не учитывает пустую ячейку.
- Пустые ячейки не будут учитываться с помощью функции COUNTA и будут игнорироваться.
- Вы можете использовать аргументы вручную в функции VBA COUNTA, и она все еще работает.
Рекомендуемые статьи
Это руководство к функции VBA COUNTA. Здесь мы обсудим, как использовать функцию COUNTA в Excel VBA вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи -
В Excel мы используем функцию count для подсчета количества ячеек, которые содержат числа. То же самое можно сделать и в VBA. В VBA мы можем использовать ту же функцию Count, чтобы узнать, сколько номеров ячеек содержат числа. Считает только ячейку с числами. Значения, отличные от чисел, не могут быть подсчитаны.
Синтаксис Count в Excel VBA
Синтаксис для функции VBA Count в Excel выглядит следующим образом:
Как использовать VBA Count в Excel?
Мы узнаем, как использовать функцию подсчета VBA, с несколькими примерами в Excel.
Вы можете скачать этот шаблон VBA Count Excel здесь - Шаблон VBA Count Excel
Пример № 1 - количество VBA
Для реализации этого у нас есть список некоторых данных в столбце А. Этот список содержит цифры и тексты, как показано ниже. Теперь мы с помощью функции Count в VBA увидим, сколько ячеек имеют числа. Для этого мы определили ячейку в позиции A8, где мы увидим вывод функции Count через VBA.
Шаг 1: Для этого нам нужен модуль. Перейдите на вкладку меню «Вставка» и выберите «Модуль», как показано ниже из списка.
Шаг 2: После этого мы получим пустое окно модуля. Теперь в это напишите подкатегорию VBA Count. Или выберите любое другое имя согласно вашему выбору.
Код:
Шаг 3: Выберите диапазон ячейки, в которой мы хотим применить функцию Count. Здесь нашей выходной ячейкой является A8, как определено выше. Таким образом, мы выбрали его в качестве нашего диапазона .
Код:
Шаг 4: Теперь получите команду Value, и это позволит нам добавить значение в нее.
Код:
Шаг 5: Теперь с помощью функции подсчета выберите диапазон ячеек, из которого мы хотим получить количество ячеек, которое содержит только числа. Здесь мы выбрали диапазон ячеек от А1 до А6.
Код:
Ste 6: После этого скомпилируйте код и запустите, нажав кнопку воспроизведения. Как мы видим ниже, количество ячеек, содержащих числа, равно 3. Что означает, что функция Count в VBA дала счетчик ячеек с номерами от A1 до A3.
Пример № 2 - Количество VBA
Аналогичным образом, у нас есть другой набор данных. Но эти данные имеют некоторые даты, число с текстом вместе с цифрами и текст, как показано ниже. Мы исправили ячейку C12, где мы увидим вывод функции Count через VBA.
Теперь мы применим функцию Count и посмотрим, может ли она считать даты и числовые текстовые ячейки или нет. Мы можем снова написать новый код или ссылаться на тот же код, который мы видели в примере 1, и просто изменить ячейки ссылки.
Шаг 1: Перейдите на вкладку меню «Вставка» и выберите пункт «Модуль», как показано ниже в списке.
Код:
Шаг 2: Выберите диапазон ячеек, в котором мы хотим увидеть вывод. Вот эта ячейка C12.
Код:
Шаг 3: Теперь используйте функцию подсчета в кавычках для выбора диапазона тех ячеек, которые нам нужно посчитать. Здесь этот диапазон от ячейки C1 до C10.
Код:
Шаг 4: Теперь запустите приведенный выше код.
Мы увидим, что функция Count вернула счетчик 6, как показано ниже. Это означает, что функция count может также считать ячейки с датой. Здесь значения, выделенные жирным шрифтом, являются значениями, которые только что были подсчитаны с помощью функции Count в VBA.
Пример № 3 - Количество VBA
Есть еще один способ использовать функцию подсчета в VBA. Этот метод предполагает использование активных ячеек листа. Здесь мы будем использовать те же данные, которые мы видели в примере-1.
Шаг 1: Откройте новый модуль и создайте подкатегорию с именем VBA Count, как показано ниже.
Код:
Шаг 2. Сначала вставьте функцию ActiveCell в VBA. Это поможет в выборе диапазона ячеек.
Код:
Шаг 3: Теперь с помощью функции Formula выберите номер строки и номер столбца, которые мы хотим вставить в функцию Count. Здесь наша ссылочная строка начинается с 1, а столбец также равен 1.
Код:
Шаг 4: Теперь вставьте функцию Count под кавычками, как показано ниже.
Код:
Шаг 5: Выберите диапазон ячеек от точки, где мы применяем функцию Count. По мере того, как мы поднимаемся от A8 к A1, количество строк будет равно « -7 », а столбец будет первым, но ничто не будет упомянуто в числе строк «-2» от начальной точки, которая является ячейкой A8 .
Код:
Шаг 6: Теперь выберите диапазон ячеек, в котором мы хотим увидеть результат. Здесь, в этой ячейке диапазона A8, мы также увидим курсор.
Код:
Шаг 7: Теперь запустите код. Мы увидим, что функция count вернула то же число, что и 3, которое мы получили в примере-1.
Плюсы VBA Count
- Это так же просто, как применять функцию подсчета в Excel.
- Это одна из самых простых функций, которые можно автоматизировать с помощью VBA.
- Если процесс Count повторяется несколько раз, то его автоматизация с помощью функции Count в VBA - это экономия времени и усилий.
То, что нужно запомнить
- При применении функции подсчета в VBA всегда указывайте имя функции в кавычках.
- Поскольку мы используем Count в Excel, то же самое можно увидеть и при применении Count Function в VBA.
- Процесс применения VBA Count также может быть выполнен путем перекодирования макроса.
- Всегда сохраняйте написанный код в VBA в Macro, включите формат файла Excel, чтобы избежать потери кода.
Рекомендуемые статьи
Это руководство к графу VBA. Здесь мы обсудим, как использовать функцию подсчета Excel VBA вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи -
На практике часто возникает необходимость быстро найти значение последней (крайней) непустой ячейки в строке или столбце таблицы. Предположим, для примера, что у нас есть вот такая таблица с данными продаж по нескольким филиалам:
Задача: найти значение продаж в последнем месяце по каждому филиалу, т.е. для Москвы это будет 78, для Питера - 41 и т.д.
Если бы в нашей таблице не было пустых ячеек, то путь к решению был бы очевиден - можно было бы посчитать количество заполненных ячеек в каждой строке и брать потом ячейку с этим номером. Но филиалы работают неравномерно: Москва простаивала в марте и августе, филиал в Тюмени открылся только с апреля и т.д., поэтому такой способ не подойдет.
Универсальным решением будет использование функции ПРОСМОТР (LOOKUP) :
У этой функции хитрая логика:
- Она по очереди (слева-направо) перебирает непустые ячейки в диапазоне (B2:M2) и сравнивает каждую из них с искомым значением (9999999).
- Если значение очередной проверяемой ячейки совпало с искомым, то функция останавливает просмотр и выводит содержимое ячейки.
- Если точного совпадения нет и очередное значение меньше искомого, то функция переходит к следующей ячейке в строке.
Легко сообразить, что если в качестве искомого значения задать достаточно большое число, то функция пройдет по всей строке и, в итоге, выдаст содержимое последней проверенной ячейки. Для компактности, можно указать искомое число в экспоненциальном формате, например 1E+11 (1*10 11 или сто миллиардов).
Если в таблице не числа, а текст, то идея остается той же, но "очень большое число" нужно заменить на "очень большой текст":
Применительно к тексту, понятие "большой" означает код символа. В любом шрифте символы идут в следующем порядке возрастания кодов:
- латиница прописные (A-Z)
- латиница строчные (a-z)
- кириллица прописные (А-Я)
- кириллица строчные (а-я)
Поэтому строчная "я" оказывается буквой с наибольшим кодом и слово из нескольких подряд "яяяяя" будет, условно, "очень большим словом" - заведомо "большим", чем любое текстовое значение из нашей таблицы.
Вот так. Не совсем очевидное, но красивое и компактное решение. Для поиска последней непустой ячейки в столбцах работает тоже "на ура".
Когда есть несколько ячеек без данных, заполненных в диапазоне в Excel, и вы просто хотите знать, сколько непустых ячеек или пустых ячеек находится в этом диапазоне, как вы можете это сделать? Теперь я могу представить вам два способа подсчета пустых или непустых ячеек в диапазоне в Excel.
Подсчет пустых или непустых ячеек с помощью формулы
В Excel есть формулы, которые помогут быстро подсчитать только пустые или непустые ячейки в диапазоне.
Например, вам нужно подсчитать пустые ячейки только в этом диапазоне, как показано ниже, и выбрать пустую ячейку, чтобы ввести эту формулу. = СЧЁТЕСЛИ (A1: G11; "") (диапазон A1: G11 указывает диапазон, из которого вы хотите подсчитать пустые ячейки, вы можете изменить его по своему усмотрению) и нажмите кнопку Enter на клавиатуре, вы посчитаете пустые ячейки. Смотрите скриншот:
Совет: вы можете использовать эту формулу: = СЧИТАТЬПУСТОТЫ (A1: G11) для подсчета только пустых ячеек. Если вы хотите подсчитывать только непустые ячейки, вы можете использовать эту формулу = СЧЁТ (A1: G11) (диапазон A1: G11 указывает диапазон, из которого вы хотите подсчитать непустые ячейки, вы можете изменить его по своему усмотрению), или вы также можете использовать эту формулу = СЧЁТЕСЛИ (A1: G11; «<>») .
Легко подсчитайте и выберите все непустые ячейки в диапазоне в Excel:
Kutools for Excel's Выбрать непустые ячейки Утилита поможет вам легко подсчитать и выбрать все непустые ячейки в выбранном диапазоне в Excel, как показано на скриншоте ниже.
Скачайте и попробуйте прямо сейчас! (60-дневная бесплатная трасса)
Подсчет пустых или непустых ячеек с помощью VBA
Выполните следующие шаги, чтобы использовать код VBA для преобразования подсчета только пустых или непустых ячеек.
1. Держать ALT и нажмите F11 на клавиатуре, чтобы открыть Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модуль, и скопируйте VBA в модуль.
VBA: подсчитывать только пустые ячейки
3. Нажмите Запустите для запуска VBA и KutoolsforExcel появится диалоговое окно для выбора рабочего диапазона, см. снимок экрана:
4. Затем нажмите OK в диалоговом окне, и отображается другое диалоговое окно, чтобы сообщить вам, сколько пустых ячеек находится в этом выбранном диапазоне.
Совет: если вы хотите подсчитать непустые ячейки, вы можете сделать, как указано выше, и использовать следующий VBA:
VBA: подсчитывать только непустые ячейки
Легко подсчитывать непустые ячейки с помощью Kutools for Excel
Освободи Себя Выбрать непустые ячейки полезности Kutools for Excel помогает выбрать все непустые ячейки в выбранном диапазоне одним щелчком мыши, а затем легко подсчитать количество выбранных ячеек.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1. Выберите диапазон с непустыми ячейками, которые вы хотите подсчитать, затем щелкните Kutools > Выберите > Выберите непустые ячейки. Смотрите скриншот:
2. Затем появится диалоговое окно, чтобы сообщить вам, сколько непустых ячеек в выбранном диапазоне, нажмите кнопку ОК, и эти непустые ячейки будут выбраны немедленно. Смотрите скриншот:
Если вы хотите получить 30-дневную бесплатную пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Читайте также: