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

Как посчитать количество да в excel

  • автор:

Подсчет чисел или дат на основе условия

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

Пример

Примечание: Вам потребуется настроить приведенные здесь ссылки на формулы ячеек в зависимости от места и способа копирования этих примеров на лист Excel.

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

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

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

  • =СЧЁТЕСЛИ(где нужно искать;что нужно найти)
  • =СЧЁТЕСЛИ(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: Функции СЧЕТЕСЛИ и СЧЕТЕСЛИМН

В случаях, когда при работе в 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″ и »

Как посчитать количество да в excel

Я имею таблицу с датами, мне нужно подсчитать количество ячеек с датами, которые (например) больше определённой даты.
Для этого попытался воспользоваться функцией «СЧЁТЕСЛИ» (формула: =СЧЁТЕСЛИ(диапазон ячеек с датами;»>ячейка с датой, с которой нужно сравнить»)).
Но ничего не получается почему-то.

Можно было бы посчитать в отдельном столбце разность каждой даты в диапазоне со сравниваемой датой, а потом, воспользовавшись той же функцией «СЧЁТЕСЛИ» (формула: =СЧЁТЕСЛИ(диапазон ячеек с разностями дат;»>0″)), выяснить искомое количество ячеек.
Но и тут засада: многие даты в столбце стоят в объединённых ячейках или в столбце имеются пустые ячейки.

Вероятно, я где-то ошибся. Прошу помочь советом.
Заранее спасибо!

P.S. Пример прилагаю.

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

  • пример.xlsx (8.54 КБ)

Сообщений: 16625 Регистрация: 10.01.2013
06.11.2016 23:31:36

=СЧЁТЕСЛИ(A2:A19;">"&I1)

Согласие есть продукт при полном непротивлении сторон
Пользователь
Сообщений: 8065 Регистрация: 21.12.2012
06.11.2016 23:43:12
И не путайте I с J
Пользователь
Сообщений: 6 Регистрация: 06.11.2016
07.11.2016 14:51:01
Благодарю, а то я уж совсем замучился:)
Пользователь
Сообщений: 23 Регистрация: 21.11.2017
02.02.2018 19:10:22

Всем привет, подскажите мне тот же вопрос пожалуйста, вроде бы формулу проверила, все равно не считает((

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

  • 1 ex.xls (53.5 КБ)

Пользователь
Сообщений: 1252 Регистрация: 07.05.2015
02.02.2018 19:37:54

=СЧЁТЕСЛИ(TDSheet!A:A;" < forum-post-lastedit">Изменено: StepanWolkoff - 02.02.2018 19:39:50
MortyMurr
Пользователь
Сообщений: 23Регистрация: 21.11.2017
#7
05.02.2018 18:36:06
спасибо огромное! выручили))
AlexeyKV1
Пользователь
Сообщений: 4Регистрация: 25.12.2019
#8
25.12.2019 13:27:11
Пожалуйста, объясните, почему для данной задачи надо использовать именно такое написание формулы, с амперсандом?
Юрий М
Модератор
Сообщений: 61044Регистрация: 14.09.2012
Контакты см. в профиле
#9
25.12.2019 13:42:14
А если без него, то какой критерий?
AlexeyKV1
Пользователь
Сообщений: 4Регистрация: 25.12.2019
#10
25.12.2019 14:29:01
Почему не проходит такой вариант =СЧЁТЕСЛИ(Лист1!D:D;"<=B1"), но при этом проходит вариант =СЧЁТЕСЛИ(Лист1!D:D;"<=20.12.2019"). хотя в ячейке B1 формат даты?
Почему нужно использовать именно формат "
Dark1589
Пользователь
Сообщений: 487Регистрация: 22.11.2017
#11
25.12.2019 14:42:19
AlexeyKV1, потому что B1 в кавычках распознаётся как текст, а не как ссылка на ячейку
Ігор Гончаренко
Пользователь
Сообщений: 14576Регистрация: 01.01.1970
#12
25.12.2019 14:49:26
потому что вы решили, что в этой записи =СЧЁТЕСЛИ(Лист1!D:D;" <=B1") что-то сподвигнет Excel отнестись к В1 как к ссылке на ячейку В1, а текстовая константа она и в Африке текстовая константа
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
msi2102
Пользователь
Сообщений: 3782Регистрация: 31.03.2014
#13
25.12.2019 15:04:53
AlexeyKV1,
Цитата
AlexeyKV1 но при этом проходит вариант =СЧЁТЕСЛИ(Лист1!D:D;"<=20.12.2019"). хотя в ячейке B1 формат даты
В ячейке B1 формат даты, а в ячейках D:D какой формат, я подозреваю, что текстовый. Иначе не должен сравнивать, что бы сравнивалась дата с датой Ваша формула должна выглядеть вот так
=СЧЁТЕСЛИ(Лист1!D:D;"")
или как писали выше "
Изменено: msi2102 - 25.12.2019 15:12:30
AlexeyKV1
Пользователь
Сообщений: 4Регистрация: 25.12.2019
#14
25.12.2019 15:37:32
с форматами ячеек все нормально. Я даже не сообразил, комент выше верный. По сути запись B1 воспринимает как текстовую константу. По этому и формула вниз не тянулась.
msi2102
Пользователь
Сообщений: 3782Регистрация: 31.03.2014
#15
25.12.2019 15:52:08
AlexeyKV1, проверьте на всякий случай формат в Столбце D. Дело в том, что иногда бывает, что стоит формат "дата", записано в формате "дата", а Excel понимает запись как текст. Поэтому попробуйте проверить, в любой ячейке столбца С или Е установите формат дата и только этого запишите значение как в столбце D (не копированием, а вручную) и простая формула =D5=C5 и если ИСТИНА то все в порядке. Просто формула =СЧЁТЕСЛИ(Лист1!D:D;" <=20.12.2019") сравнивает текст, а не дату. Excel не сравнивает даты как таковые он сравнивает значения этих дат.
Изменено: msi2102 - 25.12.2019 15:52:39
AlexeyKV1
Пользователь
Сообщений: 4Регистрация: 25.12.2019
#16
25.12.2019 16:34:28
msi2102,там с форматом все ок. Я с данными работал. Это даты, считается разность дат, и тд. Проблема выше сказана. Что при вводе формулы =СЧЁТЕСЛИ(Лист1!D:D;" <=B1") воспринимается текст B1, а не значение ячейки B1. Хотя это несколько странно. Ведь формулу я забиваю через развернутую форму. Почему так - "<=B1" переводит excel, для меня загадка. ведь я набиваю <=, а потом просто нажимаю на ячейку. По идее он сам должен был на мои действия в формулу вбить "
Павел Стасюк
Пользователь
Сообщений: 5Регистрация: 27.01.2021
#17
27.01.2021 13:25:39
Всем привет!
По описанной выше проблеме все понятно.
А если условие такое: нужно подсчитать количество ячеек с датами, которые (например) больше определённой даты, но меньше другой даты?
Например, есть дата 02.01.2021.
Она меньше, чем 03.01.2021, но больше, чем 31.12.2020.
Формула, описанная выше, поможет сделать подсчет в 1 месяце, но уже при переключении на другой - будет считать неправильно, т.к. прошедший месяц не должен учитываться. Тут-то и требуется второй параметр.

Имеется переключение месяца в ячейке K7 (для примера).
Интересует 2 строка после переключения K7. Если установлен месяц Февраль 2020, значит из диапазона A11:A15 считаются только февральские даты, т.е. ячейки A14 и A15. Если Март 2020, значит все даты по нулям.
  • Пример.xlsx(31.26 КБ)
vikttur
Пользователь
Сообщений: 47199Регистрация: 15.09.2012
#18
27.01.2021 13:28:22
=СЧЁТЕСЛИМН
Павел Стасюк
Пользователь
Сообщений: 5Регистрация: 27.01.2021
#19
27.01.2021 13:34:59
Благодарю за подсказку, я знал о этой функции, но, видимо, заработался.
Все теперь отлично!
Прекрасного дня!
yanpriest
Пользователь
Сообщений: 9Регистрация: 14.11.2019
#20
02.11.2021 14:47:23
Здравствуйте.

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

Получилось сделать как в подсказках - подсчет дат больше нужной.
Подскажите, как сделать двойное условие: чтобы подсчет ячеек вёлся в диапазоне не меньше одной даты, но не больше другой.
vikttur
Пользователь
Сообщений: 47199Регистрация: 15.09.2012
#21
02.11.2021 14:49:26
Нужно обращать внимание на все ответы. В сообщении №17 вопрос, аналогичный Вашему. В сообщении №18 показана функция.
Сергей Евдокимов
Пользователь
Сообщений: 457Регистрация: 17.10.2013
(Win'11-Excel'21/365)
#22
02.11.2021 16:57:31
Цитата
AlexeyKV1 написал: почему . такое написание формулы, с амперсандом?
Дежа вю
Компьютер никогда не заменит человека (©️ Hannibal Lecter)
БМВ
Модератор
Сообщений: 23431Регистрация: 28.12.2016
Excel 2013, 2016
#23
02.11.2021 17:00:48
Сергей Евдокимов, Вы обращайте внимание на даты постов, а то неудобно может получится.
По вопросам из тем форума, личку не читаю.
Сергей Евдокимов
Пользователь
Сообщений: 457Регистрация: 17.10.2013
(Win'11-Excel'21/365)
#24
02.11.2021 17:45:32
БМВ, а что с датами не так? И почему вдруг неудобно? Пусть прошло несколько дней, я в курсе. Что сие меняет?
Ссылка отправляет на компетентное мнение г-на Mershik по ровно тому же вопросу. Желающие прочтут, иные пройдут мимо.
По моему всё нормально ��
Компьютер никогда не заменит человека (©️ Hannibal Lecter)
БМВ
Модератор
Сообщений: 23431Регистрация: 28.12.2016
Excel 2013, 2016
#25
02.11.2021 18:01:58
Цитата
Сергей Евдокимов написал:
Пусть прошло несколько дней, я в курсе. Что сие меняет?
ну да особенно от того что цитатке прошло несколько дней, ну ведь и правда в Русском языке несколько это и 678 в том числе.
По вопросам из тем форума, личку не читаю.
Jack Famous
Пользователь
Сообщений: 12102Регистрация: 07.11.2014
OS: Win 10 Корп. x64 | Excel 2016 x64: | Browser: Chrome
#26
02.11.2021 18:06:08
Цитата
Сергей Евдокимов: несколько дней, я в курсе
ЛЕТ
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Ігор Гончаренко
Пользователь
Сообщений: 14576Регистрация: 01.01.1970
#27
02.11.2021 20:39:07
Цитата
Как подсчитать количество ячеек с датами, которые больше/меньше определённой даты
все ячейки не равные этой дате больше или меньше ее
т.е. отнимите от общего количества ячеек с датами количество ячеек равных этой определенной дате, это и будет ответ на ваш вопрос
Пы.Сы.
мы с Сергеем не обращаем внимания на даты постов, пишем просто от души
Изменено: Ігор Гончаренко - 02.11.2021 20:40:56
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
velesren84
Пользователь
Сообщений: 12Регистрация: 14.09.2023
#28
14.09.2023 16:08:50
Доброго времени суток! Помогите, пожалуйста, с расчётом формул СЧЕТЕСЛИ

есть столбец с данными в формате 12.09.2023 10:48:09 'Сделки'!A2:A1500
Найти в диапазоне дат количество ячеек в листе СВОД D2

При этом формат дат в 'Сделки'!A2:A1500 менять нельзя, выгружается в гугл шит автоматически ссылка на Файл
Заранее буду благодарен
velesren84
Пользователь
Сообщений: 12Регистрация: 14.09.2023
#29
14.09.2023 16:10:13
Заранее спасибо
Изменено: velesren84 - 14.09.2023 16:10:59
Максим В.
Пользователь
Сообщений: 7918Регистрация: 15.02.2016
#30
14.09.2023 16:30:24
Для MS Excel
Код
=СЧЁТЕСЛИМН(Сделки!$A$2:$A$932;">="&B2;Сделки!$A$2:$A$932;"<"&C2+1)

Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл

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

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