Excel vba посчитать количество ячеек со значением
В MS Excel есть различные функции для подсчета значений, будь то строка или числа. Подсчет может производиться по некоторым критериям. Функции включают COUNT, COUNTA COUNTA Функция COUNTA — это встроенная статистическая функция Excel, которая подсчитывает количество непустых ячеек (не пустых) в диапазоне ячеек или в ссылке на ячейку. Например, ячейки A1 и A3 содержат значения, а ячейка A2 пуста. Формула «= COUNTA (A1, A2, A3)» возвращает 2.
читать далее , СЧИТАТЬПУСТОТЫ, СЧЁТЕСЛИ СЧЁТЕСЛИ Функция СЧЁТЕСЛИ в Excel подсчитывает количество ячеек в диапазоне на основе заранее определенных критериев. Он используется для подсчета ячеек, содержащих даты, числа или текст. Например, СЧЁТЕСЛИ (A1: A10, «Козырный») подсчитает количество ячеек в диапазоне A1: A10, содержащих текст «Козырный».
читать далее , и СЧЁТЕСЛИМН в excel СЧЁТЕСЛИМН в Excel Функция СЧЁТЕСЛИМН в excel подсчитывает значения предоставленного диапазона на основе одного или нескольких критериев (условий). Поставляемый диапазон может быть одним или несколькими, смежными или несмежными. Являясь статистической функцией Excel, СЧЁТЕСЛИМН поддерживает использование операторов сравнения и подстановочных знаков.
читать далее . Однако эти функции не могут выполнять некоторые задачи, такие как подсчет ячеек на основе их цвета, подсчет только жирных значений и т. Д. Вот почему мы создадим счетчик в VBA, чтобы мы могли рассчитывать для этих типов задач в Excel.
Давайте создадим какой-нибудь счетчик в Excel VBA.
Примеры счетчика Excel VBA
Ниже приведены примеры счетчика в VBA.
Предположим, у нас есть данные, как указано выше, для 32 строк. Мы создадим счетчик VBA, который будет подсчитывать значения, превышающие 50, и еще один счетчик для подсчета значений, которые меньше 50. Мы будем создать код VBA Создать код VBA Код VBA относится к набору инструкций, написанных пользователем на языке программирования приложений Visual Basic в редакторе Visual Basic (VBE) для выполнения определенной задачи. читать далее таким образом, чтобы пользователь мог иметь данные для неограниченного количества строк в Excel.
Чтобы сделать то же самое, шаги будут следующими:
Убедитесь, что Разработчик вкладка Excel видна. Чтобы сделать вкладку видимой (если нет), выполните следующие действия:
Нажать на ‘Файл’ вкладку на ленте и выберите ‘Вариант’ из списка.
Выбирать ‘Настроить ленту ‘ из списка установите флажок для «Разработчик» и нажмите на В ПОРЯДКЕ.
Теперь Вкладка «Разработчик» видно.
Вставьте командную кнопку, используя ‘Вставлять’ команда доступна в Группа Controls в Вкладка «Разработчик».
Нажимая на ALT key создайте командную кнопку с помощью мыши. Если мы будем продолжать нажимать Клавиша ALT, то края кнопки управления автоматически совпадают с границей ячеек.
Щелкните правой кнопкой мыши командную кнопку, чтобы открыть контекстное меню (убедитесь, что «Режим дизайна» активируется; иначе мы не сможем открыть контекстное меню).
Выбирать ‘Характеристики’ из меню.
Измените свойства командной кнопки, т. Е. Имя, заголовок, шрифт и т. Д.
Щелкните правой кнопкой мыши еще раз и выберите ‘Просмотреть код’ из контекстного меню.
Редактор Visual Basic открывается сейчас, и по умолчанию для командной кнопки уже создана подпрограмма.
Теперь напишем код. Мы объявим 3 переменные. Один для цели цикла, один для подсчета и один для хранения значения для последней строки.
Мы будем использовать код, чтобы выбрать ячейку A1, а затем текущую область ячейки A1, а затем перейти к последней заполненной строке, чтобы получить номер последней заполненной строки.
Мы проведем ‘за’ цикл в VBA для проверки значений, записанных в ячейке A2, до последней заполненной ячейки в столбце A. Мы увеличим стоимость ‘прилавок’ на 1, если значение больше 50 и цвет шрифта ячейки изменится на ‘Синий,’ и если значение меньше 50, тогда цвет шрифта ячейки будет ‘Красный.’
Код:
Деактивировать «Режим дизайна» и нажмите на «Командная кнопка». Результат будет следующим.
Предположим, мы хотим создать счетчик времени с помощью excel VBA следующим образом:
Если мы нажмем на ‘Начинать’ кнопка, таймер запускается, и если мы нажмем на ‘Останавливаться’ кнопку, таймер останавливается.
Чтобы сделать то же самое, шаги будут следующими:
Создайте такой формат на листе Excel.
Измените формат ячейки A2 как ‘чч: мм: сс.’
Объедините ячейки C3 в G7, используя Слияние и центрирование Excel Слияние и центрирование Excel Кнопка слияния и центральная кнопка используется для объединения двух или более разных ячеек. Когда данные вставляются в какие-либо объединенные ячейки, они находятся в центральном положении, отсюда и название «слияние и центр». читать далее команда в Группа «Выравнивание» в Вкладка «Главная».
Дайте ссылку на ячейку A2 для только что объединенной ячейки, а затем выполните форматирование, например, сделайте стиль шрифта для «Баскервиль» размер шрифта до 60 и т. д.
Создайте две командные кнопки, ‘Начинать’ и ‘Останавливаться’ с помощью Команда «Вставить» доступны в Группа Controls в Вкладка «Разработчик».
С помощью Команда ‘Свойства’ доступны в Группа Controls в Вкладка «Разработчик», измените свойства.
Выберите кнопки команд одну за другой и выберите ‘Просмотреть код’ команда из ‘Controls’ группа в ‘Разработчик’ tab, чтобы написать код следующим образом.
В раскрывающемся списке выберите соответствующую командную кнопку.
Вставьте модуль в ‘ ThisWorkbook ThisWorkbook VBA ThisWorkbook относится к книге, в которой пользователи в настоящее время пишут код для выполнения всех задач в текущей книге. При этом не имеет значения, какая книга активна, и требуется только ссылка на книгу, в которой пользователи пишут код. читать далее ‘ щелкнув правой кнопкой мыши на ‘Thisworkbook’ а затем выберите ‘Вставлять’ а потом «Модуль».
Напишите в модуле следующий код.
Код:
Мы использовали ‘вовремя‘метод Применение объект, который используется для запуска процедуры в запланированное время. Процедура, которую мы запланировали запустить, такова: «Следующий_ момент».
Сохраните код. Напишите время в ячейке A2 и нажмите на ‘Начинать’ кнопку, чтобы запустить счетчик времени.
Предположим, у нас есть список учеников с выставленными ими оценками. Мы хотим подсчитать количество студентов, сдавших и не сдавших экзамен.
Чтобы сделать то же самое, мы напишем код VBA.
Откройте редактор Visual Basic, нажав ярлык в Excel Ярлык в Excel Ярлык Excel — это способ более быстрого выполнения ручной задачи. читать далее Alt + F11 и дважды щелкните на ‘Sheet3 (Подсчет количества студентов)’ для вставки подпрограммы на основе события в Sheet3.
Выбирать ‘Рабочий лист’ из раскрывающегося списка.
Как мы выбираем ‘Рабочий лист’ Из списка мы видим, что в соседнем раскрывающемся списке есть различные события. Нам нужно выбрать ‘SelectionChange’ из списка.
Мы будем объявить переменную VBA Объявить переменную VBA Объявление переменной необходимо в VBA для определения переменной для определенного типа данных, чтобы она могла содержать значения; любая переменная, не определенная в VBA, не может содержать значения. читать далее ‘lastrow’ для сохранения номера последней строки в списке для студентов может увеличиваться, ‘проходят’ для хранения количества сдавших студентов, и ‘потерпеть поражение’ для хранения количества студентов, которые потерпели неудачу.
Мы сохраним значение номера последней строки в ‘lastrow.’
Мы создадим цикл for для подсчета по условию.
Мы поставили условие: если общая оценка больше 99, то добавляем значение 1 к ‘проходят’ переменной и добавьте одно значение к ‘потерпеть поражение’ переменная, если условие не выполняется.
Последнее утверждение делает заголовок ‘Резюме’ смелый.
Чтобы распечатать значения на листе, используйте следующий код:
Код:
Теперь всякий раз, когда есть изменение в выборе, значения будут вычисляться снова, как показано ниже:
Определение метода CountIf объекта WorksheetFunction в VBA Excel:
WorksheetFunction.CountIf — это метод, который подсчитывает в указанном диапазоне количество ячеек, соответствующих одному заданному критерию (условию), и возвращает значение типа Double.
Синтаксис
Синтаксис метода CountIf объекта WorksheetFunction:
Параметры
Параметры метода CountIf объекта WorksheetFunction:
Параметр | Описание |
---|---|
Arg1 | Диапазон, в котором необходимо подсчитать количество ячеек, соответствующих заданному критерию. Тип данных — Range. |
Arg2 | Критерий в виде числа, текста, выражения или ссылки на ячейку, определяющий, какие ячейки будут засчитываться. Тип данных — Variant. |
Примечания
- Метод WorksheetFunction.CountIf позволяет получить количество ячеек, соответствующих заданному критерию, в указанном диапазоне.
- Примеры критериев (условий): 25 , "25" , ">50" , "
- В критериях можно использовать знаки подстановки (спецсимволы): знак вопроса (?) и звездочку (*). Знак вопроса заменяет один любой символ, а звездочка соответствует любой последовательности символов. Чтобы знак вопроса (?) и звездочка (*) обозначали сами себя, перед ними указывается тильда (~).
Metod WorksheetFunction.CountIfs
Должен отметить, что у меня в VBA Excel 2016 метод WorksheetFunction.CountIfs не работает. При попытке применить данный метод, генерируется ошибка:
Run-time error '1004':
Невозможно получить свойство CountIfs класса WorksheetFunction
Очевидно, метод WorksheetFunction.CountIfs предусмотрен для более новых версий VBA Excel. Статья на сайте разработчиков датирована 2021 годом.
Определение
Определение метода CountIfs объекта WorksheetFunction в VBA Excel:
WorksheetFunction.CountIfs — это метод, который подсчитывает в указанном диапазоне количество ячеек, соответствующих одному или нескольким заданным критериям (условиям), и возвращает значение типа Double.
Определение метода Count объекта WorksheetFunction в VBA Excel:
Метод WorksheetFunction.Count подсчитывает в заданном диапазоне (массиве) количество ячеек (элементов массива), содержащих числа, и возвращает значение типа Double.
Синтаксис
Синтаксис метода Count объекта WorksheetFunction:
Параметры
Параметры метода Count объекта WorksheetFunction:
Параметр | Описание |
---|---|
Arg1-Arg30 | От 1 до 30 аргументов, которые могут содержать различные типы данных или ссылаться на них. |
Примечания
- Метод WorksheetFunction.Count позволяет получить количество числовых значений в диапазоне ячеек или в массиве.
- При подсчете учитываются аргументы, которые являются числами, датами или текстовым представлением чисел.
- Логические значения учитываются при подсчете только в том случае, если они введены непосредственно в список аргументов.
Метод WorksheetFunction.CountA
Определение
Определение метода CountA объекта WorksheetFunction в VBA Excel:
WorksheetFunction.CountA — это метод, который подсчитывает в заданном диапазоне количество непустых ячеек, и возвращает значение типа Double.
Синтаксис
Синтаксис метода CountA объекта WorksheetFunction:
Параметры
Параметры метода CountA объекта WorksheetFunction:
Параметр | Описание |
---|---|
Arg1-Arg30 | От 1 до 30 аргументов, которые могут содержать различные типы данных или ссылаться на них. |
Примечания
- Метод WorksheetFunction.CountA позволяет получить количество непустых ячеек в заданном диапазоне.
- Непустыми являются ячейки, которые содержат любые данные, включая значения ошибок и пустые строки ( "" ).
- Тесты показывают, что метод WorksheetFunction.CountA в массиве, созданном путем присвоения ему значений диапазона, содержащего пустые ячейки, все равно считает все элементы массива, как содержащие значения.
Метод WorksheetFunction.CountBlank
Определение
Определение метода CountBlank объекта WorksheetFunction в VBA Excel:
WorksheetFunction.CountBlank — это метод, который подсчитывает в заданном диапазоне количество пустых ячеек, и возвращает значение типа Double.
Подсчитывают количество ячеек в диапазоне, отвечаемом нескольким критериям.
Синтаксис
выражение Переменная, представляюная объект WorksheetFunction .
Параметры
Имя | Обязательный или необязательный | Тип данных | Описание |
---|---|---|---|
Arg1 | Обязательный | Range | Один или несколько диапазонов для оценки связанных критериев. |
Arg2 – Arg30 | Обязательный | Variant | Один или несколько критериев в виде числа, выражения, ссылки на ячейки или текста, определяя, какие ячейки будут засчитываться. Например, критерии могут быть выражены как 32, "32", ">32", "яблоки" или B4. |
Возвращаемое значение
64-разрядное число с плавающей запятой двойной точности.
Примечания
Каждая ячейка в диапазоне учитывается только в том случае, если все указанные соответствующие критерии являются true для этой ячейки.
Если ячейка в любом аргументе является пустой ячейкой, CountIfs рассматривает ее как значение 0.
В критериях используйте символы подпольной карты, знак вопросов (?) и звездочки (*). Знак вопроса совпадает с любым одним персонажем; звездочка соответствует любой последовательности символов. Если вы хотите найти фактический знак вопроса или звездочки, введите тильду (~) перед персонажем.
См. также
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Функции, основанные на критериях, являются лидером в расчетах. В начале изучения Excel мы должны были изучить процесс COUTNIF в Excel. В наших предыдущих статьях мы показали вам, как работать с функцией СЧЁТЕСЛИ в Excel VBA.
Обратитесь к нашей статье о Формула СЧЁТЕСЛИ в Excel Формула СЧЁТЕСЛИ в Excel Функция СЧЁТЕСЛИ в Excel подсчитывает количество ячеек в диапазоне на основе заранее определенных критериев. Он используется для подсчета ячеек, содержащих даты, числа или текст. Например, СЧЁТЕСЛИ (A1: A10, «Козырный») подсчитает количество ячеек в диапазоне A1: A10, содержащих текст «Козырный».
читать далее чтобы узнать основы функции СЧЁТЕСЛИ в Excel VBA. В этой статье мы покажем вам, как использовать ту же функцию в кодировании VBA. Теперь мы увидим ту же формулу в VBA. Прежде всего, СЧЁТЕСЛИ не является функцией VBA; вместо этого это функция рабочего листа, к которой можно получить доступ из класса функции рабочего листа.
Пример функции счетчика Excel VBA
Хорошо, давайте посмотрим на простой пример.
Посмотрите на приведенный ниже пример подсчета значений из лота.
На изображении выше у нас есть названия городов от ячейки A1 до A10. В ячейке C3 нам нужно подсчитать, сколько раз название города «Бангалор» встречается в диапазоне от A1 до A10.
Хорошо, выполните следующие шаги, чтобы написать код для применения функции СЧЁТЕСЛИ.
Шаг 1: Запустите процедуру Sub.
Код:
Шаг 2: Поскольку нам нужно сохранить результат в ячейке C3, запустите Range («C3»). Value.
Код:
Шаг 3: В ячейке C3, применяя функцию excel VBA COUNTIF, мы пытаемся получить результат. Итак, чтобы получить доступ к функции, нам нужно сначала использовать класс Worksheet Function.
Код:
Шаг 4: Из утерянного выберите функцию Excel VBA COUNTIF.
Код:
Шаг 5: Если вы посмотрите на параметры функции VBA COUNTIF, мы не увидим параметр, как мы видим на рабочем листе.
Как мы видим на приведенном выше изображении на листе, у нас есть точный синтаксис, но в VBA мы можем видеть только Arg 1 и Arg 2.
Arg 1 — это диапазон, поэтому выберите диапазон от A1 до A10.
Код:
Шаг 6: Arg 2 — это значение, которое нам нужно посчитать в диапазоне от A1 до A10. В этом примере нам нужно вычислить «Бангалор».
Код:
Хорошо, мы закончили.
Запустите код, чтобы увидеть результат в ячейке C3.
Мы получили результат 4. Так как название города «Бангалор» появляется в ячейках A1, A4, A7 и A10, функция СЧЁТЕСЛИ VBA вернула результат как 4.
Если вы видите, что код VBA вернул только результат формулы, мы не узнаем о процедуре в строке формул.
Чтобы прийти к формуле, нам нужно написать код немного иначе. Ниже приведен код, позволяющий применить к ячейке саму формулу.
Код:
Это применит формулу к ячейке C3.
Получите результат с переменными
Переменные — неотъемлемая часть любого языка программирования. Нам нужно объявить переменные для эффективной работы с Код VBA Код VBA Код VBA относится к набору инструкций, написанных пользователем на языке программирования приложений Visual Basic в редакторе Visual Basic (VBE) для выполнения определенной задачи. читать далее . Например, посмотрите на приведенный ниже код.
Код:
Позвольте мне расшифровать код, чтобы вы лучше поняли.
Сначала я объявил две переменные как Range.
Тусклый ValuesRange В виде Диапазон: это ссылка на список значений.
Тусклый ResultCell В виде Диапазон: это ссылка на ячейку результата.
Затем я установил диапазон ссылок на обе переменные.
Набор ValuesRange = Range («A1: A10»): это диапазон, в котором присутствуют все названия городов.
Набор ResultCell = Range («C3»): в этой ячейке мы будем хранить результат функции СЧЁТЕСЛИ.
А пока я объявил еще одну переменную для хранения значения критерия.
Тусклый CriteriaValue В виде Нить
Итак, теперь переменная CriteteriaValue содержит значение Bangalore.
В следующей строке я, как обычно, применил функцию СЧЁТЕСЛИ.
ResultCell = WorksheetFunction.CountIf (ValuesRange, CriteriaValue)
Таким образом, мы можем применить функцию СЧЁТЕСЛИ в Excel VBA в соответствии с нашими потребностями.
Читайте также: