Как посчитать количество ячеек с определенным значением в excel
Перейти к содержимому

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

  • автор:

Способы подсчета количества ячеек в диапазоне с данными

В Excel есть несколько функций, позволяющих подсчитать число пустых ячеек или ячеек с данными определенного типа в диапазоне.

  1. Щелкните ячейку, в которой должен выводиться результат.
  2. На вкладке Формулы щелкните Другие функции, наведите указатель мыши на пункт Статистические и выберите одну из следующих функции:
  3. СЧЁТЗ: подсчитывает количество непустых ячеек.
  4. СЧЁТ: подсчитывает количество ячеек, содержащих числа.
  5. СЧИТАТЬПУСТОТЫ: подсчитывает количество пустых ячеек.
  6. СЧЁТЕСЛИ: подсчитывает ячейки, отвечающие заданным условиям.

Совет: Чтобы ввести нескольких условий, используйте вместо этого функцию СЧЁТЕСЛИМН.

Excel: Функции СЧЕТЕСЛИ и СЧЕТЕСЛИМН

В случаях, когда при работе в Excel требуется подсчитать количество ячеек, удовлетворяющих заданному условию, можно использовать функцию Счётесли .

Функция Счётесли

Счётесли (диапазон; критерий)

Функция СЧЁТЕСЛИ

Диапазон – группа ячеек, для которых нужно выполнить подсчет.

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

Критерий – число, выражение, ссылка на ячейку или текстовая строка, которая определяет, какие ячейки нужно подсчитать.
Обязательный аргумент.

Критерий проверки необходимо заключать в кавычки.

Критерий не чувствителен к регистру. К примеру, функция не увидит разницы между словами «налог» и «НАЛОГ».

Примеры использования функции Счётесли.

  1. Подсчет количества ячеек, содержащих отрицательные значения Счётесли(А1:С2;» <0") Диапазон - А1:С2 , критерий - "<0"

Функция СЧЁТЕСЛИ

Функция СЧЁТЕСЛИ

  1. Подсчет количества ячеек, значение которых больше содержимого ячейки А4: Счётесли(А1:С2;»>»&A4) Диапазон — А1:С2 , критерий — «>»&A4

Функция СЧЁТЕСЛИ

  1. Подсчет количества ячеек со словом «текст» (регистр не имеет значения). Счётесли(А1:С2;»текст») Диапазон — А1:С2 , критерий — «текст»

Функция СЧЁТЕСЛИ

  1. Для текстовых значений в критерии можно использовать подстановочные символы * и ? . Вопросительный знак соответствует одному любому символу,
    звездочка— любому количеству произвольных символов. Если требуется найти непосредственно вопросительный знак (или звездочку), необходимо поставить перед ним знак ~ . Например, чтобы подсчитать количество ячеек, содержащих текст, который начинается с буквы Т (без учета регистра), можно воспользоваться следующей формулой: Счётесли(А1:С2;»Т * «) Диапазон — А1:С2 , критерий — «Т * «

Функция СЧЁТЕСЛИ

Если необходимо подсчитать количество ячеек, которые содержат ровно четыре символа, можно использовать формулу:

Счётесли(А1:С2;». «) Диапазон — А1:С2 , критерий — «. «

Функция СЧЁТЕСЛИ

В функции Счётесли используется только один критерий.

Чтобы провести подсчет по нескольким условиям, необходимо воспользоваться функцией Счётеслимн.

Функция Счётеслимн

Счётеслимн (диапазон1; условие1; [диапазон2]; [условие2]; …).

Функция Счётеслимн

Функция аналогична функции Счётеслимн, за исключением того, что может содержать до 127 диапазонов и условий, где первое является обязательным, а последующие – нет.

Каждый дополнительный диапазон должен состоять из такого же количества строк и столбцов, что и диапазон условия 1. Эти диапазоны могут не находиться рядом друг с другом.

Пример использования:

  1. Подсчет количества ячеек, в которых находятся даты из определенного периода (например, после 15 января и до 1 марта 2015г.).

Диапазон один — C1:C8 , условия — «>15.01.2015″ и »

СЧЁТЕСЛИ (функция СЧЁТЕСЛИ)

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

Самая простая функция СЧЁТЕСЛИ означает следующее:

  • =СЧЁТЕСЛИ(где нужно искать;что нужно найти)
  • =СЧЁТЕСЛИ(A2:A5;»Лондон»)
  • =СЧЁТЕСЛИ(A2:A5;A4)

Ваш браузер не поддерживает видео. Установите Microsoft Silverlight, Adobe Flash Player или Internet Explorer 9.

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

Имя аргумента

диапазон (обязательный)

Группа ячеек, для которых нужно выполнить подсчет. Диапазон может содержать числа, массивы, именованный диапазон или ссылки на числа. Пустые и текстовые значения игнорируются.

критерий (обязательный)

Число, выражение, ссылка на ячейку или текстовая строка, которая определяет, какие ячейки нужно подсчитать.

Например, критерий может быть выражен как 32, «>32», В4, «яблоки» или «32».

В функции СЧЁТЕСЛИ используется только один критерий. Чтобы провести подсчет по нескольким условиям, воспользуйтесь функцией СЧЁТЕСЛИМН.

Примеры

Чтобы использовать эти примеры в Excel, скопируйте данные из приведенной ниже таблицы и вставьте их на новый лист в ячейку A1.

Количество ячеек, содержащих текст «яблоки» в ячейках А2–А5. Результат — 2.

Количество ячеек, содержащих текст «персики» (значение ячейки A4) в ячейках А2–А5. Результат — 1.

Количество ячеек, содержащих текст «яблоки» (значение ячейки A2) и «апельсины» (значение ячейки A3) в ячейках А2–А5. Результат — 3. В этой формуле для указания нескольких критериев, по одному критерию на выражение, функция СЧЁТЕСЛИ используется дважды. Также можно использовать функцию СЧЁТЕСЛИМН.

Количество ячеек со значением больше 55 в ячейках В2–В5. Результат — 2.

Количество ячеек со значением, большим или равным 32 и меньшим или равным 85, в ячейках В2–В5. Результат — 1.

Количество ячеек, содержащих любой текст, в ячейках А2–А5. Подстановочный знак «*» обозначает любое количество любых символов. Результат — 4.

Количество ячеек, строка в которых содержит ровно 7 знаков и заканчивается буквами «ки», в диапазоне A2–A5. Подставочный знак «?» обозначает отдельный символ. Результат — 2.

Распространенные неполадки

Возможная причина

Для длинных строк возвращается неправильное значение.

Функция СЧЁТЕСЛИ возвращает неправильные результаты, если она используется для сопоставления строк длиннее 255 символов.

Для работы с такими строками используйте функцию СЦЕПИТЬ или оператор сцепления &. Пример: =СЧЁТЕСЛИ(A2:A5;»длинная строка»&»еще одна длинная строка»).

Функция должна вернуть значение, но ничего не возвращает.

Аргумент критерий должен быть заключен в кавычки.

Формула СЧЁТЕСЛИ получает #VALUE! ошибка при ссылке на другой лист.

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

Рекомендации

Помните о том, что функция СЧЁТЕСЛИ не учитывает регистр символов в текстовых строках.

Критерий не чувствителен к регистру. Например, строкам «яблоки» и «ЯБЛОКИ» будут соответствовать одни и те же ячейки.

Использование подстановочных знаков

В критериях можно использовать подстановочные знаки — вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому отдельно взятому символу. Звездочка — любой последовательности символов. Если требуется найти именно вопросительный знак или звездочку, следует ввести значок тильды (~) перед искомым символом.

Например, =СЧЁТЕСЛИ(A2:A5;»яблок?») возвращает все вхождения слова «яблок» с любой буквой в конце.

Убедитесь, что данные не содержат ошибочных символов.

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

Для удобства используйте именованные диапазоны.

ФУНКЦИЯ СЧЁТЕСЛИ поддерживает именованные диапазоны в формуле (например, =COUNTIF(fruit;»>=32″)-COUNTIF(fruit;»>85″). Именованный диапазон может располагаться на текущем листе, другом листе этой же книги или листе другой книги. Чтобы одна книга могла ссылаться на другую, они обе должны быть открыты.

Примечание: С помощью функции СЧЁТЕСЛИ нельзя подсчитать количество ячеек с определенным фоном или цветом шрифта. Однако Excel поддерживает пользовательские функции, в которых используются операции VBA (Visual Basic для приложений) над ячейками, выполняемые в зависимости от фона или цвета шрифта. Вот пример подсчета количества ячеек определенного цвета с использованием VBA.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

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

Подскажите, пожалуйста, как можно подсчитать ячейки с определенным значением, которые при этом еще должны соответствовать определенным данным в других ячейках?

Т.е. нужно сделать сводную таблицу, где считается, сколько раз одному значению соотвествовало другое значение.

Пользователь
Сообщений: 47199 Регистрация: 15.09.2012
11.03.2011 19:53:08

. как можно подсчитать ячейки с определенным значением.
Запросто. Формулой. Макросом.
Пример покажете или нам самим табличку рисовать?

Пользователь
Сообщений: 88 Регистрация: 01.01.1970
11.03.2011 20:50:31

Подскажите, пожалуйста, как можно подсчитать ячейки с определенным значением, которые при этом еще должны соответствовать определенным данным в других ячейках?

Т.е. нужно сделать сводную таблицу, где считается, сколько раз одному значению соотвествовало другое значение.

пишите так
for j=nacholo intervala_1i tablichki to konec intevala 1i tablichki
for i=nacholo intervala_2i_tablichki to konec_intervala_2i tablichki
if list1.cells(i,nomer_stolbca) = list2.cells(i,nomer_stolbca)//предположим что они в одном и томже месте тольоко на разных листах then
сумируете 1 куда вам надо и так далше,
next
next

что полезно выше написаного? алгоритм с 2мя фор что пробежит 2ю таблицу дял каждого значения с 1й и остановится(плюсует 1) там где они совпали

14.03.2011 07:53:15

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

Желательно сделать это формулой в ячейке.

Прикрепленные файлы

  • post_207907.xls (19 КБ)

Пользователь
Сообщений: 47199 Регистрация: 15.09.2012
14.03.2011 09:10:11
=СУММПРОИЗВ(—(R1C3:R30C3=R[-7]C10);—(R1C4:R30C4=R2C))
Пользователь
Сообщений: 47199 Регистрация: 15.09.2012
14.03.2011 09:14:48
В «нормальном» стиле:
=СУММПРОИЗВ(—($C$1:$C$30=$J3);—($D$1:$D$30=L$2))
14.03.2011 10:24:29

Насколько я понимаю, СУММПРОИЗВ использует числовые значения, прочие оно воспринимает как 0, результат получается соответствующий. Что делать, если значения в ячейках все-таки не числовые и считать надо количество ячеек с определенным значением?

Пользователь
Сообщений: 47199 Регистрация: 15.09.2012
14.03.2011 10:39:34

В данном случае функция работает с условиями, ей все равно, 1=2 или Вася=Петя.
Показанная формула со стилем R1C1 «сдвинута», проверялась в стороне от таблицы, оттуда и скопировани. Для первой ячейки (R3C12):
=СУММПРОИЗВ(—(R1C3:R30C3=RC10);—(R1C4:R30C4=R2C))
Или в меню Сервис-Параметры-Общие-Параметры меняйте Стиль_ссылок и применяйте вторую формулу (со стилем А1).

14.03.2011 10:55:29

В данном случае функция работает с условиями, ей все равно, 1=2 или Вася=Петя.
Показанная формула со стилем R1C1 «сдвинута», проверялась в стороне от таблицы, оттуда и скопировани. Для первой ячейки (R3C12):
=СУММПРОИЗВ(—(R1C3:R30C3=RC10);—(R1C4:R30C4=R2C))
Или в меню Сервис-Параметры-Общие-Параметры меняйте Стиль_ссылок и применяйте вторую формулу (со стилем А1).

Понятно. Спасибо.
А есть возможность указать в качестве массива весь столбец, чтобы каждый раз не переписывать формулу, если количество строк будет увеличиваться?

Пользователь
Сообщений: 47199 Регистрация: 15.09.2012
14.03.2011 11:06:59
С именованными динамическими диапазонами:
=СУММПРОИЗВ(—(оц=$J3);—(пер=L$2))
Прикрепленные файлы

  • post_207963.xls (18.5 КБ)

14.03.2011 14:54:57

Спасибо.
Использовал именованные динамические диапазоны, но с предложенной формулой возникает ошибка #ЗНАЧ! при добавлении новых строк только в один столбец.

Пользователь
Сообщений: 47199 Регистрация: 15.09.2012
14.03.2011 14:58:59
И как Вам подсказать, где не получается?
То пример с Вас выжимать, то ошибку.
14.03.2011 15:14:25

В том же файле, что прикреплен к вашему сообщению: post_207963.xls
Там достаточно добавить в R30C3 значение, чтобы в отчетной таблице возникла ошибка.

Пользователь
Сообщений: 47199 Регистрация: 15.09.2012
14.03.2011 15:21:16

Понял. Ошибка получается из-за разного размера диапазонов (СУММПРОИЗВ этого не любит). Замените формулу имени «пер»:
=Лист1!R2C4:ИНДЕКС(Лист1!R2C4:R1000C4;ПОИСКПОЗ(«яя»;Лист1!R2C3:R1000C3))
Теперь размер второго диапазона определяется длиной первого.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *