Vba excel. методы count, counta и countblank

Using COUNTBLANKS

The COUNTBLANKS function will only count the Blank Cells in the Range of cells – ie cells that have no data in them at all.

In the example below, column B has no blank cells while column C has one blank cell.

Using the COUNTIF Function

Another worksheet function that can be used is the  function.

The procedure above will only count the cells with values in them if the criteria is matched – greater than 0, greater than 100, greater than 1000 and greater than 10000.  You have to put the criteria within quotation marks for the formula to work correctly.

Disadvantages of WorksheetFunction

When you use the WorksheetFunction to count the values in a range in your worksheet, a static value is returned, not a flexible formula. This means that when your figures in Excel change, the value that has been returned by the WorksheetFunction will not change.

In the example above, the procedure TestCount has counted up the cells in column H where a value is present.  As you can see in the formula bar, this result is a figure and not a formula.

If any of the values change therefore in the Range(H2:H12), the results in H14 will NOT change.

Instead of using the WorksheetFunction.Count, you can use VBA to apply a Count Function to a cell using the Formula or FormulaR1C1 methods.

The formula method allows you to point specifically to a range of cells eg: H2:H12 as shown below.

Using the FormulaR1C1 Method

The FromulaR1C1 method is more flexible in that it does not restrict you to a set range of cells. The example below will give us the same answer as the one above.

However, to make the formula more flexible, we could amend the code to look like this:

Wherever you are in your worksheet, the formula will then count the values in the 12 cells directly above it and place the answer into your ActiveCell. The Range inside the COUNT function has to be referred to using the Row (R) and Column (C) syntax.

Both these methods enable you to use Dynamic Excel formulas within VBA.

Метод 4: оператор СЧЕТЕСЛИ

Наконец, мы подошли к функции, которая способна сделать подсчет по определенным условиям. Оператор СЧЕТЕСЛИ создан именно для этой задачи. Все ячейки без данных и те, которые не отвечают заданным пользователем параметрам, данная функция игнорирует.

Синтаксис СЧЕТЕСЛИ типичен для всех операторов, работающих с условиями:

=СЧЕТЕСЛИ(диапазон;критерий)

Диапазон – область ячеек, внутри которой необходимо найти количество совпадений по заданному условию.

Критерий – конкретное условие, совпадение по которому ищет функция. Условие указывается в кавычках, может быть задано как в виде точного совпадения с введенным числом или текстом, или же как математическое сравнение, заданное знаками «не равно» («»), «больше» («>») и «меньше» (« /= ») перед словом «бег».

После нажатия кнопки OK мы получаем число, которое сообщает нам, сколько в выбранном диапазоне (столбце) ячеек, не содержащих слово «бег». На этот раз количество равно 17.

Напоследок, можно разобрать работу с числовыми условиями, содержащими знаки «больше» («>») или «меньше» (« 350” в поле “Критерий” и жмем OK.

В заранее выбранной ячейке получим итог – 10 ячеек содержат значения больше числа 350.

Как посчитать, содержит ли ячейка текст или часть текста в Excel?

Предположим, у вас есть данные, указанные ниже, и вы хотите подсчитать количество ячеек, содержащих текст «Apple», количество ячеек, содержащих текст»Оранжевая»и ячейки содержат»персик»отдельно, как это сделать? В этом руководстве мы объясняем COUNTIF Подробная функция, которая поможет вам быстро подсчитать количество ячеек, если они содержат текст или часть определенного текста в Excel. Кроме того, мы представляем замечательную функцию, позволяющую легко достичь ее с помощью только щелчков мышью.

Подсчитайте, если ячейка содержит текст или часть текста, с помощью функции СЧЁТЕСЛИ

Функция СЧЁТЕСЛИ может помочь подсчитать ячейки, содержащие часть текста в диапазоне ячеек в Excel. Пожалуйста, сделайте следующее.

1. Выберите пустую ячейку (например, E5), скопируйте в нее приведенную ниже формулу и нажмите Enter ключ. Затем перетащите маркер заполнения вниз, чтобы получить все результаты.

=COUNTIF(B5:B10,»*»&D5&»*»)

Синтаксис

=COUNTIF (range, criteria)

аргументы

• Диапазон (обязательно): диапазон ячеек, которые вы хотите подсчитать.
• Критерии (обязательно): число, выражение, ссылка на ячейку или текстовая строка, определяющая, какие ячейки будут учитываться.

Базовые ноты:

• В формуле B5: B10 — это диапазон ячеек, который нужно подсчитать. D5 — это ссылка на ячейку, содержащую то, что вы хотите найти. Вы можете изменить ссылочную ячейку и критерии в формуле по своему усмотрению.
• Если вы хотите напрямую вводить текст в формуле для подсчета, примените следующую формулу:=COUNTIF(B5:B10,»*Apple*»)
• В этой формуле регистр не учитывается.

Самый большой Выбрать определенные ячейки полезности Kutools for Excel может помочь вам быстро подсчитать количество ячеек в диапазоне, если они содержат определенный текст или часть текста. После получения результата во всплывающем диалоговом окне все совпавшие ячейки будут выбраны автоматически. .Загрузите Kutools for Excel прямо сейчас! (30-дневная бесплатная трасса)

Счетные ячейки содержат текст с функцией СЧЁТЕСЛИ

Как показано на скриншоте ниже, если вы хотите подсчитать количество ячеек в определенном диапазоне, которые содержат только текст, метод в этом разделе может вам помочь.

1. Выберите пустую ячейку для вывода результата, скопируйте в нее приведенную ниже формулу и нажмите Enter ключ.

=COUNTIF(B5:B10,»*»)

Подсчитайте, если ячейка содержит текст или часть текста с помощью Kutools for Excel

Чаевые: Помимо приведенной выше формулы, здесь представлена ​​замечательная функция, позволяющая легко решить эту проблему. С Выбрать определенные ячейки полезности Kutools for Excel, вы можете быстро подсчитать, содержит ли ячейка текст или часть текста, щелкнув мышью. С помощью этой функции вы даже можете подсчитать с помощью OR или AND, если вам нужно. Пожалуйста, сделайте следующее.

Перед использованием Kutools for Excel, вам нужно потратить несколько минут, чтобы в первую очередь.

1. Выберите диапазон, в котором вы хотите подсчитать количество ячеек, содержащих определенный текст.

2. Нажмите Kutools > Выберите > Выбрать определенные ячейки.

3. в Выбрать определенные ячейки диалоговое окно, вам необходимо:

• Выберите Ячейка вариант в Тип выбора раздел;
• В Конкретный тип раздел, выберите Комплект в раскрывающемся списке введите Apple в текстовом поле;
• Нажмите OK кнопку.
• Затем появляется окно подсказки, в котором указано, сколько ячеек соответствует условию. Щелкните значок OK кнопка и все соответствующие ячейки выбираются одновременно.

 Наконечник. Если вы хотите получить бесплатную (60-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.

Используйте countif с несколькими критериями в Excel В Excel функция СЧЁТЕСЛИ может помочь нам вычислить количество определенного значения в списке. Но иногда нам нужно использовать несколько критериев для подсчета, это будет сложнее. Из этого туториала Вы узнаете, как этого добиться.Нажмите, чтобы узнать больше …

Подсчитайте, начинаются ли ячейки или заканчиваются определенным текстом в Excel Предположим, у вас есть диапазон данных, и вы хотите подсчитать количество ячеек, которые начинаются с «kte» или заканчиваются «kte» на листе. Эта статья знакомит вас с некоторыми хитростями вместо ручного подсчета.Нажмите, чтобы узнать больше …

Подсчитайте конкретное значение на нескольких листах Как можно подсчитать конкретные значения на нескольких листах? Например, получить количество вхождений определенного значения «Excel» из нескольких листов. В этом руководстве представлены методы, позволяющие это сделать.Нажмите, чтобы узнать больше …

Примеры работы функций СЧЁТ, СЧИТАТЬПУСТОТЫ и СЧЁТЕСЛИ в Excel

​В​ 3.​Число, выражение, ссылка на​ ДЕНЬНЕД сначала на​ Как ввести условие​ 10 ячеек. Нужно​

Где используется и как работает функция СЧЁТ?

​ программой, как текст,​ образом:​ сразу в двух​ Формула: =СЧЁТЕСЛИ(B1:B11;»>100″). Диапазон​СЧИТАТЬПУСТОТЫ​ один произвольный символ.​ в списке аргументов,​3​ – то дублей​

​15.05.2017​

• ​ «Майкрософт». Для чего​критерии​=СЧЁТЕСЛИ(B2:B5;»>=32″)-СЧЁТЕСЛИ(B2:B5;»>85″)​
• ​ ячейку или текстовая​ 1, потом, на​
• ​ для выходного дня​

​ определить, какое количество​ если оно находится​

​=СЧЁТ(аргумент1; аргумент2; …)​

​ столбцах. Если диапазоны​ – В1:В11. Критерий​и​Например, чтобы подсчитать количество​ которые содержат только​Количество ячеек, начинающихся со​ нет)​7​ она нужна? Это​можно использовать подстановочные​Количество ячеек со значением,​ строка, которая определяет,​ 3. В любом​ — по цвету​

1. ​ студентов получили отличную​ в ячейке, и​Все версии Excel, начиная​
2. ​ несмежные, то применяется​ подсчета – «>100».​
3. ​СЧЕТЕСЛИМН​ ячеек, содержащих текст,​ числовые значения. Например,​ слова «Яблоки» и​=СЧЁТЕСЛИ(D2:D9; СЕГОДНЯ())​Огурец китайский​ понятно уже из​ знаки: вопросительный знак​ большим или равным​

​ какие ячейки нужно​ случае — И​ ячейки или с​

Пример использования функции СЧЁТ в Excel

​ как число, если​ с 2007, могут​ функция СЧЕТЕСЛИМН.​ Результат:​. Возвращайтесь к нам​ который начинается с​ на рисунке ниже​ любым окончанием​2​64​

​ ее названия. Она​ (?) и звездочку​ 32 и меньшим​ подсчитать.​

​ каким образом эти​ учетом дня недели?​ пятибалльной системе оценивания),​ оно прописано в​ учитывать до 255​Когда в качестве критерия​Если условие подсчета внести​ почаще. Всего Вам​

​ буквы​ мы подсчитали количество​=СЧЁТЕСЛИ(А2:А9;»*й*»)​Количество ячеек с товарами,​17​ поможет любому пользователю,​ (*). Вопросительный знак​

​ или равным 85,​Например, критерий может быть​ вычисления привязаны к​Yurii_74​ а какое 4,​

​ числе аргументов функции.​ аргументов при расчёте​

​ указывается ссылка на​ в отдельную ячейку,​ доброго и успехов​Н​ ячеек в диапазоне,​3​ поступившими на склад​07.05.2017​ задавшемуся вопросом «Как​

Пример использования функций СЧЁТЗ и СЧИТАТЬПУСТОТЫ в Excel

​ соответствует одному любому​ в ячейках В2–В5.​ выражен как 32,​ месяцу октябрю?​: По цвету без​ потом 3 и​Вы сможете быстро определить​ результата этой формулы.​ диапазон ячеек с​ можно в качестве​

​ в изучении Excel.​(без учета регистра),​ который полностью состоит​Количество ячеек, содержащих букву​ сегодня (используется системная​8​

​ посчитать ячейки в​ символу, а звездочка —​ Результат — 3.​ «>32», В4, «яблоки»​Добавлено через пять​ использования макросов не​

​ 2.​ количество заполненных или​ Обязательным является только​ условиями, функция возвращает​ критерия использовать ссылку:​

​ «й» в любой​ дата компьютера)​Огурец опыляемый​ Excel по заданному​

Пример использования функции СЧЁТЕСЛИ с условием

​ любой последовательности знаков.​=СЧЁТЕСЛИ(A2:A5;»*»)​ или «32».​ минут……​ получится. Но если​Для определения количества отличников​ пустых клеток в​ первый из них.​ массив. Для ввода​

1. ​Посчитаем текстовые значения в​Функция СЧЕТЕСЛИ входит в​ формулой:​
2. ​В следующем примере в​ части текста. Часто​

​=СЧЁТЕСЛИ(С2:С9; «»)​

​110,6​ критерию?», получить нужные​ Если требуется найти​Количество ячеек, содержащих любой​В функции СЧЁТЕСЛИ используется​Правда, формула правильно​ сверху фигурируют даты​ нужно провести анализ​ заданной области таблицы.​

​ При этом есть​ формулы нужно выделить​ одном диапазоне. Условие​ группу статистических функций.​

​Если необходимо подсчитать количество​ двух ячейках диапазона​ используется для поиска​0​28​ цифры.​ непосредственно вопросительный знак​ текст, в ячейках​ только один критерий.​ работает, если​ — то вполне​ содержимого ячеек второго​ Для выполнения этих​

​ разница в том,​ такое количество ячеек,​ поиска – один​ Позволяет найти число​ ячеек, которые содержат​ содержится текст. Как​ и дальнейшей замены​Подсчитывает количество незаполненных ячеек​

​16.05.2017​Данная функция принимает два​ (или звездочку), необходимо​

• ​ А2–А5. Подстановочный знак​
• ​ Чтобы провести подсчет​=СУММПРОИЗВ(—(F3:L3=»я»)—(F3:L3=»8-12″);—(ДЕНЬНЕД(F$1:L$1;2)=7)), т.е. считать​
• ​ реально (с использованием​
• ​ столбика. В отдельной​

• ​ операций используются функции​ как учитывается значение,​ как в диапазоне​ критерий.​

  exceltable.com>

Суммирование с множеством условий.

Имеются данные о заказах и продаже шоколада. Подсчитаем итог совершённых продаж по молочному шоколаду. То есть, у нас два требования: должно совпадать наименование товара и в колонке «Выполнен» должно быть указано «Да».

Первым аргументом мы указываем диапазон суммирования E2:E21, а затем попарно – диапазон условия и само условие для него.

В C2:C21 будем искать слово «молочный» с любым его вхождением. То есть, до и после него могут быть еще любые другие символы.

В F2:F21 ищем «Да», то есть отметку о том, что заказ выполнен.

Если ОБА эти требования выполняются, то такой заказ нам подходит, и его стоимость мы учтём.

Как видите, у нас найдено 2 совпадения, в которых был продан молочный шоколад.

COUNTIF and COUNTIFS Warnings

There are a few things to be careful with, when using the COUNTIF and COUNTIFS functions.

1. Count might include
2. Cannot count text characters
3. Similar text might be counted, instead of just specific text — see the example here
4. Specific numbers within other numbers are not counted —

1) False Duplicates With COUNTIF

Be careful when using COUNTIF, because it matches numbers, with text
that looks like numbers, and that could result in false counts.

For example, if you have a text entry — «00123» — it would
be counted as a duplicate for the number — 123.

=COUNTIF($B$2:$B$10,B2))>1

If your data could contain entries like that, use SUMPRODUCT, instead
of COUNTIF.

=SUMPRODUCT(—($B$2:$B$10=B2))>1

Функция СЧЁТЕСЛИМН в Excel

​ от проданной продукции​ 2-м критериям (Условие​) =5, которое удовлетворяет​

Синтаксис

​В качестве исходной таблицы​

​=БСЧЁТА(A1:B13;A1;D14:F15) или БСЧЁТ(A1:B13;B1;D14:F15), которые требуют наличия​ критериями.​

• ​формула массива =СУММ((B2:B13<>=D2))​​ ячейку или текста,​ и Климов превысили​В условии можно использовать​Функция​

• ​ еженедельно и даже​​Прописываем аргументы.​ зарплаты.​Критерий – определяет, какие​ на пустую ячейку​ клавиш Ctrl+Shift+Enter:​ составляет техника фирмы​ И) можно без​ критерию >=5, поэтому​ возьмем таблицу с​ отдельной таблички с​

• ​Рассмотрим задачу, когда 1​​формула массива =СЧЁТ(ЕСЛИ((B2:B13>=D2)*(B2:B13​ которые определяют, какие​ квоту для периодов​ подстановочные знаки: вопросительный​СЧЁТЕСЛИМН​

​ чаще. Пользуйтесь поиском​​диапазон суммирования – ячейки​Аналогично можно подсчитать зарплаты​ ячейки из диапазона​ или пустая строка​Выражение 1/СЧЁТЕСЛИ(A3:A20;A3:A20) возвращает массив​​ Samsung.​​ применения формул с​ второе значение в​ двумя столбцами: текстовым​

Замечания

• ​ критериями.​ текстовый критерий с​=БСЧЁТА(A1:B13;A1;D14:E15) или =БСЧЁТ(A1:B13;B1;D14:E15), которые требуют​ ячейки требуется учитывать.​ Q1, Q2 и​ знак (?) и​применяет критерии к​ по форуму.​ с зарплатой;​ всех менеджеров, продавцов-кассиров​ будут выбраны (записывается​ «», результат вычисления​ дробных чисел 1/количество_вхождений,​Вид исходной таблицы данных:​ помощью стандартного Автофильтра.​

• ​ массиве =1 и​ «Фрукты» и числовым​О подсчете с множественными​ подстановочным знаком применяются​​ наличия отдельной таблички​​ Например, условие может​

• ​ Q3 (только в​ звездочку (*). Вопросительный​ ячейкам в нескольких​r_kot​диапазон условия 1 –​ и охранников. Когда​ в кавычках).​ для любого диапазона​ например, для пользователя​Для расчета используем формулу:​Установите автофильтр к столбцу​ т.д.​​ «Количество на складе»​​ критериями можно почитать​

Пример 1

​ к значениям текстового​ с критериями.​ быть выражено следующим​ Q2).​ знак соответствует любому одиночному​ диапазонах и вычисляет​: Поправил формулу. Вместо​ ячейки с указанием​ табличка небольшая, кажется,​Диапазон суммирования – фактические​ ячеек будет числовое​ с ником sam​=СЧЁТЕСЛИ(C3:C17;»Samsung»)/A17​

​ Количество ящиков на​	​Далее, функция попарно перемножает​	​ (См. файл примера).​	​ в этом разделе.​
​ столбца, а другой​	​Рассмотрим задачу, когда 2​	​ образом: 32, «>32»,​	​1​
​ символу; звездочка —​	​ количество соответствий всем​	​ enter жми ctrl+shift+enter​	​ должности сотрудника;​
​ что все можно​	​ ячейки, которые необходимо​	​ значение 0 (нуль).​	​ это значение равно​
​Описание аргументов:​	​ складе, выделив заголовок​	​ элементы массивов и​	​Рассмотрим задачу, когда критерии​
​В этом разделе перечислены​	​ (числовой) — к значениям​	​ критерия заданы в​
​ B4 или «яблоки»;​	​Данные​ любой последовательности символов.​ критериям.​ и наступит результат!​условия 1 – продавец;​	​ сосчитать и вручную,​
​ просуммировать, если они​	​Функция может быть использована​ 0,25 (4 вхождения).​C3:C17 – диапазон ячеек,​ столбца и нажав​ суммирует их. Получаем​	​ применяются к значениям​
​ распространенные причины ошибки​	​ столбца с числами (см. файл​ форме дат и​Диапазон_условия2, условие2… ​1​ Если нужно найти​Это видео — часть учебного​	​r_kot​

Пример 2

​диапазон условия 2 –​
​ но при работе​	​ удовлетворяют критерию.​
​ в качестве формулы​	​ Общая сумма таких​
​ содержащих названия фирм​	​CTRL+SHIFT+L. ​
​ – 2.​	​ из разных столбцов.​
​ #ЗНАЧ! в функциях​	​ примера, лист 1текст (с​
​ применяются к значениям​	​   Необязательные аргументы. Дополнительные диапазоны​
​01.05.2011​	​ сам вопросительный знак​	​ курса Усложненные функции​
​: Поправил формулу. Вместо​	​ ячейки с указанием​ со списками, в​Получается, что у функции​ массива, если требуется​ значений, вычисляемая функцией​	​ проданной техники;​
​Выберите числовой фильтр Между.​	​3. Другим вариантом использования​Найдем число партий товара​ СЧЁТЕСЛИ и СЧЁТЕСЛИМН.​ подстанов) 1числовой).​ одного из столбцов.​ и условия для​	​2​
​ или звездочку, поставьте​	​ ЕСЛИ.​ enter жми ctrl+shift+enter​ пола сотрудника;​ которых по несколько​ всего 3 аргумента.​ выполнить расчет числа​	​ СУММ, соответствует количеству​

support.office.com>

Использование функции СУММЕСЛИМН

Последний пример – функция СУММЕСЛИМН, которая похожа на предыдущую, но позволяет работать со множеством аргументов. Возьмем таблицу, где указано наименование продукции с некоторыми одинаковыми названиями, есть разная цена и количество. 

Посчитаем количество груш, но только тех, чья цена будет выше 10 за единицу. Соответственно, это только пример, а вы можете использовать функцию для совершенно разных задач как при работе с текстовыми значениями, так и неравенствами.

1. Объявите функцию СУММЕСЛИМН и сначала запишите тот диапазон, который будете считать. В моем случае это количество груш.

2. Далее нужно вычленить из списка продукции только груши, для чего используйте критерий текста точно так же, как это уже было показано в предыдущем примере.

3. Второй критерий – цена, которая должна превышать 10 за единицу. Соответственно, впишите блок с неравенством A1:A10;»>10″, где A1:A10 – диапазон ячеек, а >10 – критерий.

4. Нажмите клавишу Enter и ознакомьтесь с результатом. На следующем скриншоте вы видите, что груш с ценой больше 10 довольно много, все их количество суммировалось и отображается в отдельной ячейке.

При первой записи у вас могут возникнуть трудности с правильным написанием функции, ведь она содержит много условий. Я оставлю вам ее отдельно, чтобы вы могли скопировать ее и вставить, подставив вместо текущих диапазонов ячеек свои: =СУММЕСЛИМН(B2:B25;A2:A25;»Груши»;C2:C25;»>10″). Не забывайте о том, что первый диапазон – то, что вы считаете, далее идет первый критерий – диапазон с названием столбца, потом второй – диапазон с неравенством.

Это лишь несколько примеров использования функций СУММЕСЛИ и СУММЕСЛИМН в Excel. Полученные знания вы можете использовать в своих целях, выполняя необходимые расчеты и упрощая процесс взаимодействия с электронной таблицей.

Как в Excel посчитать количество ячеек с определенным значением?

​ а только те,​Для вывода результата на​ имеются значения. В​ сможете указать 126​В появившемся окне из​ количество заполненных ячеек​ обрисовать в общем​

​К примеру, следующая формула​​ подсчитать количество пустых​​ пустые ячейки, то​ столбец и выполняешь​ оказались рядом.​ Знак амперсанда (&)​​ быть ссылка, число,​ столбца с текстовыми​

​ форматированием с правилом =И($A2=$D$2;$B2>=$E$2)​​Как видим, не отмеченные​ которые отвечают определенному​ экран достаточно будет​​ этом случае, если​ условий. Приводить подробную​ списка выберите функцию​​ в строке, столбце​ сами способы. Во-первых,​

​ подсчитывает ячейки, значения​​ ячеек в Excel,​​ придется немного усовершенствовать​ макрос. ​​Первый аргумент формулы «ПРОМЕЖУТОЧНЫЕ.ИТОГИ»​ объединяет данный оператор​ текстовая строка, выражение.​ значениями, т.к. БСЧЁТА()​​Подсчет можно реализовать множеством​ цветом ячейки после​ заданному условию. В​​ нажать кнопку​​ мы выделим только​ инструкцию, как использовать​​ СЧЕТ и нажмите​ или целом диапазоне.​ вы можете воспользоваться​​ которых больше нуля,​​ Вы сможете, применив​ формулу, добавив проверку​​Во втором столбце​ — «Номер функции».​​ и значение «стулья».​​ Функция СЧЕТЕСЛИ работает​​ подсчитывает текстовые значения.​​ формул, приведем несколько:​ данных действий были​ этом случае на​Enter​ один столбец, то​​ функцию СЧЕТЕСЛИМН нет​ кнопку «ОК».​Вы также можете посчитать​​ специальным счетчиком, расположенным​ но меньше 50:​ функцию​ на пустые ячейки​ excel тебе покажет​

​ Это числа от​​При применении ссылки формула​ только с одним​ В случае использования БСЧЁТ() нужно​​1. =СЧЁТЕСЛИМН(A2:A13;D2;B2:B13;»>=»&E2) Это решение является​ спрятаны. Просто выделяем​ помощь придет условное​

​.​​ те элементы, у​ смысла, так как​Перед вами, как и​ количество заполненных ячеек​ на строке состояния,​

​Функция​​СЧИТАТЬПУСТОТЫ​ (иначе получим ошибку​ сколько каких значений​ 1 до 11,​ будет выглядеть так:​​ условием (по умолчанию).​