Функция COUNTIF в Excel

Те, кто использует Excel только для создания таблиц вручную, упускают множество преимуществ этого инструмента Microsoft. Разнообразие функций облегчает работу — как дома, так и в офисе. Однако, чтобы воспользоваться всеми преимуществами Excel, необходимо разобраться в отдельных функциях. Одним из многих полезных инструментов является функция COUNTIF. Узнайте больше о том, как правильно использовать эту функцию и для чего она может понадобиться.

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

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

Функция COUNTIF в Excel представляет собой комбинацию функции IF и функции COUNT или COUNTA (статистическая функция). Эта комбинация гарантирует, что Excel подсчитает ячейку только при соблюдении определенных критериев.

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

При работе с функциями Excel очень важно использовать правильный синтаксис:

                =COUNTIF(Диапазон;Критерий поиска).

Термин диапазон означает ячейки таблицы, содержащие данные, которые вы хотите подсчитать. Термин критерий поиска означает соответствующее значение, которое необходимо учитывать. Диапазон должен включать начальную и конечную точки, разделенные двоеточием. Другими словами, ‘B3:B14’ включает две упомянутые ячейки, а также все ячейки, находящиеся между ними. Также можно создать диапазон, перетащив мышь и выделив соответствующий диапазон ячеек. Также можно выделить целый столбец: просто введите столбец в качестве диапазона и никаких строк — ‘B:B’. Эта техника также работает для строк.

Критерий поиска можно задать либо в виде значения, которое затем заключить в кавычки (например, «обувь»), либо ссылкой на ячейку, содержащую это значение (например, B3). Оба элемента (диапазон и критерий поиска) разделяются точкой с запятой. В синтаксисе пробелы не используются.

                =COUNTIF(B3:B14; «Обувь»)

                =COUNTIF(B3:B14;F3)

Excel также предлагает полезное автозавершение: просто перетащите функцию (в правом нижнем углу активированной ячейки) на одну строку дальше. Указанные ячейки под диапазоном и критерием поиска будут автоматически адаптированы, и функция теперь будет ссылаться на ячейки, расположенные на один ряд ниже. Если вы не желаете такого автоматического завершения и хотите, чтобы область поиска оставалась неизменной, вы можете ввести эти характеристики как абсолютные значения. Для этого вставьте знак доллара:

                =COUNTIF($B$3:$B$14;F3)

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

=COUNTIF(B3:B14;$G4;C3:C14;>1)

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

                =COUNTIF(B3:B17;»10969″)

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

                =COUNTIF(B3:B17;»<>10969″)

Если вы не вводите значение непосредственно в функцию, а хотите ввести его в определенную ячейку, вам необходимо соединить отрицание («<>») с ячейкой с помощью оператора конкатенации (&):

                =COUNTIF(B3:B17;»<>»&B3)

Если вы спрашивали своих клиентов об их возрасте, то эти цифры были отнесены к соответствующим почтовым индексам в дополнительном столбце. Если вы считаете, что ваше предложение привлекает в первую очередь людей старше 50 лет, вы можете проверить это предположение с помощью функции COUNT IF. Поскольку вы хотите включить не только всех покупателей старше 50, но и тех, кому точно 50, функция должна содержать знаки «больше» и «равно»:

                =COUNTIF(C3:C17;»>=50″)

Конечно, вас будет интересовать и отрицательный результат, т.е. сколько людей моложе 50. Для этого вам не понадобится отрицание: знака less-than вполне достаточно:

                =COUNTIF(C3:C17;»<50″)

Если кто-то не ввел никакой информации о своем возрасте, это значение будет просто вычтено. Вы заметите, что удаление почтового индекса, который не является 10969, никак не повлияет на результат. Для этого вы можете расширить формулу, подсчитав все пустые ячейки и вычтя это число из первого результата. Чтобы указать Excel, что вы хотите использовать пустые ячейки в качестве критериев поиска, просто оставьте область между кавычками пустой и напишите два символа непосредственно рядом друг с другом:

                =COUNTIF(B3:B17;»<>10969″)-COUNTIF(B3:B17;»»)

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

                =COUNTIF(B3:B17;»<>10969″;B3:B17;»<>»)

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

                =COUNTIFS(B3:B17;»10969″;B3:B17;»<>»;C3:C17;»>=50″)

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

                =COUNTIF(D3:D17; «*Обувь»).