Как составить формулу в excel с условием счет если

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

Синтаксис и особенности функции

Сначала рассмотрим аргументы функции:

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

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

В качестве критерия может быть ссылка, число, текстовая строка, выражение. Функция СЧЕТЕСЛИ работает только с одним условием (по умолчанию). Но можно ее «заставить» проанализировать 2 критерия одновременно.

Рекомендации для правильной работы функции:

• Если функция СЧЕТЕСЛИ ссылается на диапазон в другой книге, то необходимо, чтобы эта книга была открыта.
• Аргумент «Критерий» нужно заключать в кавычки (кроме ссылок).
• Функция не учитывает регистр текстовых значений.
• При формулировании условия подсчета можно использовать подстановочные знаки. «?» — любой символ. «*» — любая последовательность символов. Чтобы формула искала непосредственно эти знаки, ставим перед ними знак тильды (~).
• Для нормального функционирования формулы в ячейках с текстовыми значениями не должно пробелов или непечатаемых знаков.



Функция СЧЕТЕСЛИ в Excel: примеры

Посчитаем числовые значения в одном диапазоне. Условие подсчета – один критерий.

У нас есть такая таблица:

Посчитаем количество ячеек с числами больше 100. Формула: =СЧЁТЕСЛИ(B1:B11;»>100″). Диапазон – В1:В11. Критерий подсчета – «>100». Результат:

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

Посчитаем текстовые значения в одном диапазоне. Условие поиска – один критерий.

Формула: =СЧЁТЕСЛИ(A1:A11;»табуреты»). Или:

Во втором случае в качестве критерия использовали ссылку на ячейку.

Формула с применением знака подстановки: =СЧЁТЕСЛИ(A1:A11;»таб*»).

Для расчета количества значений, оканчивающихся на «и», в которых содержится любое число знаков: =СЧЁТЕСЛИ(A1:A11;»*и»). Получаем:

Формула посчитала «кровати» и «банкетки».

Используем в функции СЧЕТЕСЛИ условие поиска «не равно».

Формула: =СЧЁТЕСЛИ(A1:A11;»<>»&»стулья»). Оператор «<>» означает «не равно». Знак амперсанда (&) объединяет данный оператор и значение «стулья».

При применении ссылки формула будет выглядеть так:

Часто требуется выполнять функцию СЧЕТЕСЛИ в Excel по двум критериям. Таким способом можно существенно расширить ее возможности. Рассмотрим специальные случаи применения СЧЕТЕСЛИ в Excel и примеры с двумя условиями.

1. Посчитаем, сколько ячеек содержат текст «столы» и «стулья». Формула: =СЧЁТЕСЛИ(A1:A11;»столы»)+СЧЁТЕСЛИ(A1:A11;»стулья»). Для указания нескольких условий используется несколько выражений СЧЕТЕСЛИ. Они объединены между собой оператором «+».



2. Условия – ссылки на ячейки. Формула: =СЧЁТЕСЛИ(A1:A11;A1)+СЧЁТЕСЛИ(A1:A11;A2). Текст «столы» функция ищет в ячейке А1. Текст «стулья» — на базе критерия в ячейке А2.



3. Посчитаем число ячеек в диапазоне В1:В11 со значением большим или равным 100 и меньшим или равным 200. Формула: =СЧЁТЕСЛИ(B1:B11;»>=100″)-СЧЁТЕСЛИ(B1:B11;»>200″).



4. Применим в формуле СЧЕТЕСЛИ несколько диапазонов. Это возможно, если диапазоны являются смежными. Формула: =СЧЁТЕСЛИ(A1:B11;»>=100″)-СЧЁТЕСЛИ(A1:B11;»>200″). Ищет значения по двум критериям сразу в двух столбцах. Если диапазоны несмежные, то применяется функция СЧЕТЕСЛИМН.



5. Когда в качестве критерия указывается ссылка на диапазон ячеек с условиями, функция возвращает массив. Для ввода формулы нужно выделить такое количество ячеек, как в диапазоне с критериями. После введения аргументов нажать одновременно сочетание клавиш Shift + Ctrl + Enter. Excel распознает формулу массива.

СЧЕТЕСЛИ с двумя условиями в Excel очень часто используется для автоматизированной и эффективной работы с данными. Поэтому продвинутому пользователю настоятельно рекомендуется внимательно изучить все приведенные выше примеры.

ПРОМЕЖУТОЧНЫЕ.ИТОГИ и СЧЕТЕСЛИ

Посчитаем количество реализованных товаров по группам.

1. Сначала отсортируем таблицу так, чтобы одинаковые значения оказались рядом.



2. Первый аргумент формулы «ПРОМЕЖУТОЧНЫЕ.ИТОГИ» — «Номер функции». Это числа от 1 до 11, указывающие статистическую функцию для расчета промежуточного результата. Подсчет количества ячеек осуществляется под цифрой «2» (функция «СЧЕТ»).

Скачать примеры функции СЧЕТЕСЛИ в Excel

Формула нашла количество значений для группы «Стулья». При большом числе строк (больше тысячи) подобное сочетание функций может оказаться полезным.

СЧЁТЕСЛИ в Excel — примеры функций с одним и несколькими условиями

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

Они в основном одинаковы во всех версиях, поэтому вы можете использовать примеры в MS Excel 2016, 2013, 2010 и 2007.

Функция Excel COUNTIF используется для подсчета количества ячеек в указанном диапазоне, которые соответствуют заданному условию.

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

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

Как видите, здесь всего 2 аргумента, оба обязательные:

• диапазон: указывает одну или несколько ячеек для подсчета. Вы вставляете диапазон в формулу как обычно, например, A1:A20.
• критерий: определяет условие, определяющее, что именно следует учитывать. Это может быть число, текстовая строка, ссылка или выражение. Например, вы можете использовать следующие критерии: «10», A2, «>= 10», «какой-то текст».

Что там помнить?

• В аргументе условия условие всегда должно быть заключено в кавычки, за исключением случаев использования хука или функции.
• Любой аргумент относится к диапазону в другой книге Excel, эта книга должна быть открыта.
• Буквы не чувствительны к регистру.
• Вы также можете использовать подстановочные знаки * и ? (о них позже).
• Во избежание ошибок текст не должен содержать непечатаемых символов.

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

Примеры работы функции СЧЕТЕСЛИ.

Для подсчета текста.

Давайте разберемся, как это работает. На рисунке ниже вы можете увидеть список выполненных менеджерами заказов. Выражение =СЧЁТЕСЛИ(B2:B22;»Никитенко») подсчитывает, сколько раз этот работник присутствует в списке:

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

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

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

=СЧЁТЕСЛИ(A2:A22;»*Коро*»)

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

Также не совсем рационально указывать условие непосредственно в формуле, так как если нужно вычислить какое-то другое значение, то придется его корректировать. А это не очень удобно.

Рекомендуется написать условие в какой-нибудь ячейке и потом ссылаться на него. Так мы сделали это в H9. Вы также можете использовать подстановочные знаки со ссылками, используя оператор конкатенации (&). Например, вместо того, чтобы вводить «*Коро*» прямо в формулу, вы можете ввести его где-нибудь и использовать следующую конструкцию для подсчета ячеек, содержащих «Коро»:

=СЧЁТЕСЛИ(A2:A22,»*»&H8&»*»)

Подсчет ячеек, начинающихся или заканчивающихся определенными символами

Вы можете использовать звездочку (*) или вопросительный знак (?) в качестве подстановочного знака, в зависимости от точного результата, которого вы хотите достичь.

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

=СЧЁТЕСЛИ(A2:A22;»K*») — считать значения, начинающиеся с «K» .

=СЧЁТЕСЛИ(A2:A22,»*p») — подсчитываются те, которые заканчиваются на букву «p».

Если вы ищете количество ячеек, которые начинаются или заканчиваются определенными буквами и содержат точное количество символов, поставьте знак вопроса (?):

=СЧЁТЕСЛИ(С2:С22;»????d») — находит число с буквой «d» на конце и текст, состоящий из 5 букв, включая пробелы.

=СЧЁТЕСЛИ(С2:С22, «??») — подсчитывает количество 2-х символов, включая пробелы.

Примечание. Чтобы узнать количество ячеек, содержащих знак вопроса или звездочку в тексте, введите тильду (~) перед ? или *.

Например, =СЧЁТЕСЛИ(C2:C22;»*~?*») подсчитает все позиции, содержащие вопросительный знак в диапазоне C2:C22.

Подсчет чисел по условию.

Что касается чисел, то редко бывает так, что нужно посчитать их количество равным какому-то определенному числу. Тем не менее, мы рекомендуем вам написать что-то вроде этого:

= СЧЁТЕСЛИ(D2:D22,10000)

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

Чтобы подсчитать значения, которые больше, меньше или равны указанному вами числу, просто добавьте соответствующие критерии, как показано в таблице ниже.

Обратите внимание, что математический оператор вместе с числом всегда заключен в кавычки .

В нашем примере

=СЧЁТЕСЛИ(D2:D22;»>10000″)

Мы подсчитываем количество крупных заказов, которые превышают 10 000. Обратите внимание, что здесь мы записываем условие подсчета в виде текстовой строки и поэтому заключаем его в двойные кавычки.

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

Комментарий. При использовании ссылки вы должны заключить математический оператор в кавычки и добавить перед ним амперсанд (&). Например, чтобы подсчитать числа в диапазоне D2:D9 больше, чем D3, используйте =СЧЁТЕСЛИ(D2:D9,»>»&D3)

Если вы хотите подсчитывать записи, содержащие в своем содержании математический оператор, то есть символ «>», «

Например, =СЧЁТЕСЛИ(D2:D9,»*>5*») будут подсчитаны все товары в диапазоне D2:D9 с таким содержанием, как «Отправка >5 дней» или «>5 единиц на складе».

Примеры с датами.

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

Приведу несколько примеров:

В дополнение к этим стандартным методам вы можете использовать функцию СЧЁТЕСЛИ в сочетании с функциями даты и времени, такими как СЕГОДНЯ(), для подсчета ячеек на основе текущей даты.

Как посчитать количество пустых и непустых ячеек?

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

Не пустой.

В некоторых учебниках COUNTIF вы можете найти предложения для подсчета непустых ячеек, например:

СЧЁТЕСЛИ(диапазон;»*»)

Но дело в том, что приведенное выше выражение подсчитывает только ячейки, содержащие текстовые значения. И это означает, что те, которые содержат даты и числа, будут рассматриваться как пустые (игнорируемые) и не будут включены в общую сумму!

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

СЧЁТЕСЛИ(диапазон;»» & «»)

Это правильно работает со всеми типами значений — текстом, датами и числами — как вы можете видеть на изображении ниже.

Также можно подсчитать непустые ячейки в диапазоне:

=СЧЁТЧАСТЬ(E2:E22).

Пустой.

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

Считаем ячейки, не содержащие текста:

СЧЁТЕСЛИ(диапазон; «» и; «» & «*»)

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

Чтобы подсчитать пустые ячейки (все типы значений):

=СЧЁТЕСЛИ(E2:E22;»»)

Конечно, для таких случаев есть специальная функция

=СЧИТАТЬПУСТО(E2:E22)

Но не все знают о его существовании. Но теперь ты знаешь …

Нулевые строки.

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

Что это за пустые строки? Также они часто возникают при импорте данных из других программ (например, 1С). Внешне в них ничего нет, но на самом деле это не так. При попытке найти такие «заготовки» (F5 -Выбрать — Пустые ячейки) они не обнаруживаются. Но фильтр данных видит его как пустой и отфильтровывает как пустой.

Дело в том, что существует «строка нулевой длины» (или «нулевая строка»). Нулевая строка возникает, когда программе нужно вставить какое-то значение, но вставлять нечего.

Проблемы начинаются, когда вы пытаетесь произвести с ним какие-то математические вычисления (вычитание, деление, умножение и т д.). Вы получаете сообщение об ошибке #ЗНАЧ!. При этом функции СУММ и СЧЁТ их игнорируют, как если бы там был текст. Внешне его нет.

И самое интересное, что если навести на него мышкой и нажать Удалить (или вкладку Главная — Правка — Очистить содержимое), то он действительно становится пустым, и формулы и другие функции Excel начинают с ним работать без ошибок.

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

=СТРОКИ(E2:E22)*СТОЛБЦ(E2:E22)-СЧЁТЕСЛИ(E2:E22;»»&»»)

Где в ячейках могут появляться нулевые строки? Тут может быть несколько вариантов:

1. Он есть изначально, потому что так вы настраиваете загрузку и создание файлов в сторонней программе (типа 1С). В некоторых случаях такие нагрузки настраиваются таким образом, что пустых ячеек как таковых нет; они просто заполняются строкой нулевой длины.
2. Была создана формула, результатом которой стал текст нулевой длины. Самый простой случай:

=ЕСЛИ(Е1=1,10;»»)

В результате, если в E1 будет записано что-то отличное от 1, программа вернет строку нулевой длины. А если мы потом заменим формулу значением (Специальная вставка — Значения), то получим нашу псевдопустую позицию.

Если вы проверяете какие-то условия с помощью функции ЕСЛИ и планируете в дальнейшем производить над результатами математические операции, то вместо « » лучше поставить 0 . Тогда проблем не будет. Нули всегда можно заменить или скрыть: Файл -Параметры -Дополнительно — Показать нули в позициях, содержащих нулевые значения.

СЧЕТЕСЛИ с несколькими условиями.

На самом деле функция СЧЁТЕСЛИ в Excel не предназначена для подсчёта количества ячеек по различным условиям. В большинстве случаев я рекомендую использовать ее множественный аналог — функцию СЧЁТЕСЛИМН. Он просто предназначен для подсчета количества ячеек, соответствующих двум или более условиям (логическое И). Однако некоторые проблемы можно решить, объединив две или более функций СЧЁТЕСЛИ в одно выражение.

Количество чисел в диапазоне

Одним из наиболее распространенных применений функции СЧЁТЕСЛИ с двумя критериями является определение количества чисел в определённом диапазоне, то есть меньше X, но больше Y.

Например, вы можете использовать для вычисления ячеек в диапазоне B2:B9, где значение больше 5 и меньше или равно 15:

=СЧЁТЕСЛИ(B2:B11;»>5″)-СЧЁТЕСЛИ(B2:B11;»>15″)

Количество ячеек с несколькими условиями ИЛИ.

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

Давай сделаем это:

=СЧЁТЕСЛИ(A4:A13;»лимонад»)+СЧЁТЕСЛИ(A2:A11;»*сок»)

Обратите внимание, что мы включили подстановочный знак (*) во второй критерий. Он используется для расчета количества всех видов сока в списке.

Как вы понимаете, здесь можно добавить больше условий.

Использование СЧЕТЕСЛИ для подсчета дубликатов.

Другое возможное использование функции СЧЁТЕСЛИ в Excel — поиск дубликатов в столбце, между двумя столбцами или в строке.

1. Ищем дубликаты в одном столбце

Это простое выражение COUNTIF($A$2:$A$24,A2)>1 найдет все одинаковые записи в A2:A24.

И еще одна формула СЧЁТЕСЛИ(B2:B24,ИСТИНА) сообщит вам, сколько существует дубликатов:

Для более наглядного представления найденных совпадений я использовал условное форматирование значения ИСТИНА.

2. Сколько совпадений между двумя столбцами?

Сравните список2 со списком1. В столбце E мы последовательно берем каждое значение из списка2 и подсчитываем, сколько раз оно встречается в списке1. Если совпадений нет, то это уникальное значение. На рисунке они выделены цветом с использованием условного форматирования.

Выражение =СЧЁТЕСЛИ($A$2:$A$24,C2) копируется в столбец E.

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

Чтобы просто определить количество дубликатов, вы можете использовать комбинацию функций СУММПРОИЗВ и СЧЁТЕСЛИ.

=СУММПРОИЗВ((СЧЁТЕСЛИ(A2:A24;C2:C24)>0)*(C2:C24″»))

Подсчитаем количество уникальных значений в list2:

=СУММПРОИЗВ((СЧЁТЕСЛИ(A2:A24;C2:C24)=0)*(C2:C24″»))

Получаем 7 уникальных и 16 дублирующих записей, что видно на рисунке.

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

3. Сколько дубликатов и уникальных значений в строке?

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

Подсчет количества дубликатов:

=СУММПРОИЗВ((СЧЁТЕСЛИ(A2:K2,A2:K2)>1)*(A2:K2″»))

Мы видим, что 13 выпало 2 раза.

Подсчет уникальных значений:

=СУММПРОИЗВ((СЧЁТЕСЛИ(A2:K2,A2:K2)=1)*(A2:K2″»))

Часто задаваемые вопросы и проблемы.

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

1. Можно ли считать в диапазоне несмежных ячеек?

Вопрос: Как я могу использовать СЧЁТЕСЛИ для диапазона или несмежных ячеек?

Ответ: Не работает с несмежными диапазонами, синтаксис не позволяет указывать несколько отдельных ячеек в качестве первого параметра. Вместо этого вы можете использовать комбинацию нескольких функций COUNTIF:

Неправильно: =СЧЁТЕСЛИ(A2,B3,C4,»>0″)

Правильно: = СЧЁТЕСЛИ(A2;»>0″) + СЧЁТЕСЛИ(B3;»>0″) + СЧЁТЕСЛИ(C4;»>0″)

Альтернативный способ — использовать функцию ДВССЫЛ для создания массива несмежных ячеек. Например, два варианта ниже дают тот же результат, что и на изображении:

=СУММ(СЧЁТЕСЛИ(ДВССЫЛ({«B2:B11″,»D2:D11″}),»=0»))

ИЛИ

=СЧЁТЕСЛИ($B2:$B11,0) + СЧЁТЕСЛИ($D2:$D11,0)

1. Амперсанд и кавычки в формулах СЧЁТЕСЛИ

Вопрос: Когда мне нужно использовать амперсанд?

Ответ: Это, пожалуй, самая сложная часть функции СЧЁТЕСЛИ, которая лично меня тоже смущает. Хотя, если подумать, вы увидите, что амперсанд и кавычки необходимы для построения строки аргумента.

Итак, вы можете следовать этим правилам:

• Если вы используете номер или ссылку на ячейку в точном соответствии, вам не нужны амперсанд или кавычки. Например:

= СЧЁТЕСЛИ(A1:A10,10) или = СЧЁТЕСЛИ(A1:A10,C1)

• Если ваши условия содержат текст, подстановочный знак или логический оператор с числом, заключите его в кавычки. Например:

= СЧЁТЕСЛИ(A2:A10,»яблоко») или = СЧЁТЕСЛИ(A2:A10,»*») или = СЧЁТЕСЛИ(A2:A10,»>5″)

• Если вашим критерием является выражение со ссылкой или какая-либо другая функция Excel, вы должны использовать кавычки («»), чтобы начать текстовую строку, и амперсанд (&), чтобы конкатенировать (объединить) и закончить строку. Например:

= СЧЁТЕСЛИ(A2:A10;»>»&D2) или = СЧЁТЕСЛИ(A2:A10;»

Если вы не уверены, нужен вам амперсанд или нет, попробуйте оба способа. В большинстве случаев амперсанд работает нормально.

Например, =СЧЁТЕСЛИ(C2: C8;»

1. Как считать клетки по цвету?

Вопрос: Как считать ячейки по цвету заливки или шрифту, а не по значениям?

Ответ: К сожалению, синтаксис функции не позволяет использовать форматы в качестве условия. Единственный возможный способ суммировать ячейки на основе их цвета — использовать макрос или, точнее, определяемую пользователем функцию Excel VBA.

1. Ошибка #ИМЯ?

Проблема: я продолжаю получать #NAME? Как я могу это исправить?

Ответ: Скорее всего вы указали неверный диапазон. См пункт 1 выше.

1. Формула не работает

Проблема: Моя формула не работает! Что я сделал не так?

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

Будьте очень осторожны с пробелами. Создавая одну из формул для этой статьи, я был готов рвать на себе волосы, потому что правильный дизайн (я знал, что он правильный!) не сработал. Оказывается, проблема была там… Например, посмотрите на это: =СЧЁТ.ЕСЛИ(A4:A13;»Лимонад»). На первый взгляд ничего страшного, кроме лишнего пробела после открывающей цитаты. Программа проглотит все без каких-либо сообщений об ошибках, предупреждений или каких-либо других указаний. Но если вы действительно хотите посчитать предметы, содержащие слово «лимонад» и начальный пробел, вы будете очень разочарованы….

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

И это все на сегодня. В следующей статье мы рассмотрим несколько способов подсчета ячеек в Excel с несколькими условиями.

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

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

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

Критерий — критерий в форме числа, выражения, ссылки на ячейку или текста, определяющий, какие ячейки следует подсчитывать. Например, критерий может быть выражен следующим образом: 32, «32», «> 32», «яблоки» или B4.

Подсчет числовых значений с одним критерием

Данные будут взяты из диапазона A15: A25.

Подсчитывает количество ячеек, содержащих числа, равные или превышающие 10. Критерий указывается в формуле

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

Подсчитывает количество ячеек, содержащих числа, равные или превышающие 11. Критерий указывается с помощью ссылки и параметра

Примечание. Чтобы подсчитать значения, соответствующие нескольким критериям, см. Подсчет значений с несколькими критериями. Чтобы подсчитать числа с более чем 15 значащими цифрами, прочитайте статью Подсчет значений ТЕКСТА с одним критерием в MS EXCEL.

Подсчет Текстовых значений с одним критерием

Функция СЧЁТЕСЛИ () также подходит для подсчета текстовых значений.

Подсчет дат с одним критерием

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

Если вам нужно подсчитать количество дат, принадлежащих определенному месяцу, вам нужно создать дополнительный столбец для расчета месяца, а затем написать формулу = СЧЁТЕСЛИ (B20: B30; 2)

Подсчет с несколькими условиями

Обычно в качестве аргумента функции СЧЁТЕСЛИ () указывается только одно значение. Например, = СЧЁТЕСЛИ (H2: H11; I2). Если вы укажете ссылку на весь диапазон ячеек с критериями в качестве критерия, функция вернет массив. В файле примера формула = СЧЁТЕСЛИ (A16: A25; C16: C18) возвращает массив .

Чтобы вставить формулу, выберите диапазон ячеек того же размера, что и диапазон, содержащий критерии. В строке формул введите формулу и нажмите CTRL + SHIFT + ENTER, то есть введите ее как формулу массива.

Также прочтите: При включенной отладке jit любые необработанные исключения

Это свойство функции СЧЁТЕСЛИ () используется в статье Выбор уникальных значений.

Специальные случаи использования функции

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

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

Выражение COUNTIF (A6: A14; A6: A14) возвращает массив чисел , указывая, что значение 1 из списка в диапазоне A6: A15 — единственный, даже в диапазоне 4 значений, одно значение 3, три значения 4. Это позволяет подсчитывать количество неповторяющихся значений по формуле = СУММПРОИЗВ (- (СЧЁТЕСЛИ (LA6: LA14; LA6: LA14) = 1)) .

Формула = СЧЁТЕСЛИ (A6: A14; «вычисляет ранг в порядке убывания для каждого числа из диапазона A6: A15. Вы можете проверить это, выделив формулу в строке формул и нажав F9. Значения будут соответствовать вычисленному рангу в столбце B (с помощью функции RANK ()) Этот подход применяется в статьях «Динамическая сортировка таблицы в MS EXCEL» и «Выбор уникальных значений с сортировкой в ​​MS EXCEL.

Функция COUNT подсчитывает количество ячеек, содержащих числа, и количество чисел в списке аргументов. Функция COUNT используется для определения количества числовых ячеек в диапазонах и массивах чисел. Например, чтобы вычислить количество чисел в диапазоне A1: A20, вы можете ввести следующую формулу: = COUNT (A1: A20). Если в этом примере пять ячеек в диапазоне содержат числа, результатом будет 5.

Синтаксис

Аргументы функции COUNT перечислены ниже.

Значение1 Обязательное. Первый элемент, ссылка на ячейку или диапазон, для которого вы хотите подсчитать количество чисел.

Value2;… — необязательный аргумент. До 255 дополнительных элементов, ссылок на ячейки или диапазонов, в которых вы хотите подсчитать количество чисел.

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

Замечания

Подсчитываются аргументы, которые являются числами, датами или текстовыми представлениями чисел (например, число, заключенное в кавычки, например «1″).

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

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

Если аргумент является массивом или ссылкой, учитываются только числа. Пустые ячейки, логические значения, текст и значения ошибок в массиве или ссылке игнорируются.

Смотрите также: Смарт-устройство на телевизоре Samsung

Если вам нужно подсчитать логические значения, текстовые элементы или значения ошибок, используйте функцию COUNT.

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

Пример

Скопируйте образец данных из приведенной ниже таблицы и вставьте его в ячейку A1 нового листа Excel. Чтобы просмотреть результаты формул, выберите их и нажмите F2, затем нажмите Enter. При необходимости измените ширину столбцов, чтобы увидеть все данные.

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

Синтаксис и особенности функции

Давайте сначала рассмотрим аргументы функции:

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

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

В качестве критерия могут использоваться ссылка, число, текстовая строка, выражение. Функция СЧЁТЕСЛИ работает только с одним условием (по умолчанию). Но вы можете «заставить» его анализировать 2 критерия одновременно.

Рекомендации по правильному функционированию функции:

• Если функция СЧЁТЕСЛИ ссылается на диапазон в другой книге, эта книга должна быть открыта.
• Аргумент критерия должен быть заключен в кавычки (кроме ссылок).
• Функция не чувствительна к регистру для текстовых значений.
• вы можете использовать подстановочные знаки при формулировании условия подсчета. «?» — любой персонаж. «*» — любая последовательность символов. Чтобы формула искала эти знаки напрямую, мы ставим перед ними тильду ().
• Чтобы формула работала правильно, ячейки с текстовыми значениями не должны содержать пробелов или непечатаемых символов.

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

У нас есть такая таблица:

Считаем количество ячеек с числами больше 100. Формула: = СЧЁТЕСЛИ (B1: B11; «> 100»). Диапазон — B1: B11. Критерий подсчета: «> 100». Результат:

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

Считаем текстовые значения в диапазоне. Поисковый запрос является критерием.

Формула: = СЧЁТЕСЛИ (A1: A11; «табуреты»). ИЛИ:

Во втором случае в качестве критерия использовалась ссылка на ячейку.

Читайте также: Как привязать боковые кнопки на мышке

Формула с подстановочным знаком: = СЧЁТЕСЛИ (A1: A11; «табуляция*»).

Чтобы вычислить количество значений, оканчивающихся на «e», содержащих любое количество символов: = СЧЁТЕСЛИ (A1: A11; «* e»). У нас есть:

В формуле подсчитывались «кровати» и «скамейки».

Мы используем поисковый запрос «не равно» в функции СЧЁТЕСЛИ».

Формула: = СЧЁТЕСЛИ (A1: A11; «» & «стулья»). Оператор означает не равно. Символ амперсанда (&) объединяет данный оператор и значение слова «стулья».

При применении ссылки формула будет выглядеть так:

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

1. Посчитаем, сколько ячеек содержит текст «столы» и «стулья». Формула: = СЧЁТЕСЛИ (A1: A11; «столы») + СЧЁТЕСЛИ (A1: A11; «стулья»). Несколько выражений COUNTIF используются для указания нескольких условий. К ним присоединяется оператор «+».
2. Условия — это ссылки на ячейки. Формула: = СЧЁТЕСЛИ (A1: A11; A1) + СЧЁТЕСЛИ (A1: A11; A2). Функция ищет текстовые «таблицы» в ячейке A1. Текст «стулья» основан на критерии в ячейке A2.
3. Мы подсчитываем количество ячеек в диапазоне B1: B11 со значением больше или равным 100 и меньше или равным 200. Формула: = СЧЁТЕСЛИ (B1: B11; «> = 100») — СЧЁТЕСЛИ (B1: B11 ; «> 200»).
4. Мы применяем разные диапазоны в формуле СЧЁТЕСЛИ. Это возможно, если интервалы смежные. Формула: = СЧЁТЕСЛИ (A1: B11; «> = 100») — СЧЁТЕСЛИ (A1: B11, «> 200»). Ищите значения на основе двух критериев в двух столбцах одновременно. Если диапазоны не являются смежными, используется функция СЧЁТЕСЛИ.
5. Если критерием является ссылка на диапазон ячеек с условиями, функция возвращает массив. Чтобы ввести формулу, вам нужно выбрать столько ячеек, сколько есть в диапазоне с критериями. После ввода аргументов одновременно нажмите комбинацию клавиш Shift + Ctrl + Enter. Excel распознает формулу массива.

СЧЁТЕСЛИ с двумя условиями в Excel очень часто используется для автоматизированной и эффективной работы с данными. Поэтому опытному пользователю настоятельно рекомендуется внимательно изучить все приведенные выше примеры.

ПРОМЕЖУТОЧНЫЕ.ИТОГИ и СЧЕТЕСЛИ

Считаем количество проданных товаров по группам.

1. Сначала отсортируем таблицу так, чтобы одинаковые значения располагались рядом друг с другом.
2. Первым аргументом формулы «INTERMEDIATE.TOTAL» является «Номер функции». Это числа от 1 до 11, которые обозначают статистическую функцию для вычисления промежуточного результата. Подсчет количества ячеек производится под числом «2» (функция «СЧЁТ»).

Формула нашла количество значений для группы «Стулья». При большом количестве строк (более тысячи) такое сочетание функций может пригодиться.