Одной из часто решаемых с помощью Microsoft Excel задач является подсчет количества определенных значений в некотором диапазоне. Для решения подобных задач можно задействовать функции СЧЁТЕСЛИ и СЧЁТЕСЛИМН.
Первая функция позволяет считать значения, соответствующие только одному критерию. Вторая - если критериев несколько. В функции СЧЁТЕСЛИМН можно задействовать до 127 условий.
Давайте рассмотрим применение этих функций на примерах.
Простое использование функции СЧЁТЕСЛИ
Есть таблица с заказами за определенный период и необходимо посчитать, сколько заказов было оформлено определенным покупателем.
Задействуем функцию СЧЁТЕСЛИ. Она имеет два аргумента. Первый аргумент - диапазон, в котором происходит поиск, второй аргумент - значение, которое нужно найти в этом диапазоне.
Выделяем весь столбец «Покупатель», а в качестве искомого значения указываем «Магнит». Так как значение является текстом, то не забываем поместить его в двойные кавычки.
Само собой, значение не обязательно указывать явно, а можно сослаться на ячейку, в которой это значение находится. Для примера давайте сделаем простейшую сводную таблицу, в которой посчитаем количество заказов каждого покупателя.
Указываем диапазон поиска, а в качестве критерия сошлемся на значение из заголовка строки таблицы. Так как эту формулу мы будем протягивать по всему столбцу, то не забываем зафиксировать ссылки на диапазон с помощью клавиши F4.
Протягиваем формулу по всему диапазону значений:
Как показывает этот пример, функцию СЧЁТЕСЛИ можно использовать для создания сводных таблиц. Это может быть полезным в ситуациях, когда стандартные сводные таблицы по каким-то причинам задействовать не получается, например, когда данные необходимо подставить в некоторую стандартизированную форму.
Сводные таблицы с помощью СЧЁТЕСЛИМН
Вот реальная упрощенная задача, которую я получил от подписчика.
Есть некоторый перечень, в котором указаны документы разных типов и причины их возврата в соответствии с некоторой классификацией.
Необходимо составить сводную таблицу, в которой подсчитать количество возвратов в разрезе их причин. Такая задача является классической для стандартных сводных таблиц, но давайте решим ее с помощью формул.
Так как условия подсчета будет два - это фамилия сотрудника и код причины, то задействуем расширенную функцию - СЧЁТЕСЛИМН.
Ее аргументами являются пара - диапазон поиска и соответствующее ему условие. Сначала указываем диапазон с фамилиями (A2:A21)и ссылаемся на соответствующую ячейку сводной таблицы (E3). Затем создаем второе условие, в котором выделяем диапазон с причинами (C2:C21) и соответствующую ячейку в строке с заголовками сводной (F2). Так как формулу будем протягивать по диапазону, то не забываем фиксировать ссылки на ячейки и диапазоны. В ссылке в столбце с фамилиями у нас может изменяться строка, поэтому с помощью клавиши F4 фиксируем столбец ($E3). В ссылке на причину возврата фиксируем строку (F$2). Более детально об абсолютных и относительных ссылках я также рассказал в очень подробном видео.
Протягиваем формулу по диапазону и получаем результат.
Но все же в таких задачах я бы рекомендовал задействовать классические сводные таблицы Excel, так как они позволят избежать возможных ошибок, которые могут быть связаны с неверно указанными заголовками строк и столбцов. Также классические сводные будут значительно эффективнее при обработке больших таблиц, так как в таких ситуациях подсчет значений с помощью формул потребует значительных вычислительных ресурсов.
Подсчет значений в диапазоне
Еще одной довольно распространенной задачей является нахождение общего количества значений в рамках заданного диапазона. Границами диапазона могут быть не только обычные числа, но и даты.
Рассмотрим простейший пример. Есть диапазон значений и нужно получить количество значений, которые больше 20, но меньше 40.
Так как условия два, то задействуем функцию СЧЁТЕСЛИМН.
Сначала указываем диапазон для поиска (А2:А23), затем задаем первое условие - искомое значение должно быть больше значения ячейки E2. Знак «больше» (>) необходимо поместить в двойные кавычки, так как для Excel в формуле он будет являться обычным символом (текстом). Далее с помощью символа амперсанд (&) «сцепим» знак «больше» со ссылкой на ячейку.
Второе условие создается аналогично - снова выбираем тот же диапазон (А2:А23), так как поиск второго критерия будем также производить в нем. И по аналогии указываем второе условие - "<"&E3.
Поиск дубликатов
И еще одна задача, при решении которой функция СЧЁТЕСЛИ может быть полезной - это поиск повторяющихся значений.
В Excel есть несколько способов найти дубликаты в диапазоне, но в ряде ситуаций именно использование функции СЧЁТЕСЛИ будет максимально удобным вариантом. Продемонстрирую его на том же примере.
В столбце рядом (в ячейке B2) создадим формулу с функцией СЧЁТЕСЛИ.
Выбираем диапазон значений (A2:A23)и сразу его фиксируем с помощью клавиши F4, так как формулу будем протягивать вниз. Далее указываем ячейку из первого столбца (А2), ведь именно значение этой ячейки мы будем искать в диапазоне. Растягиваем формулу на весь диапазон и получаем столбец с цифрами.
Цифра в новом столбце указывает на то, сколько раз соответствующее значение встречается в диапазоне. Можно для диапазона с помощью сочетания клавиш Ctrl + Shift +L включить фильтры и через них отключить для столбца показ строк с единицей.
В результате мы получим только список с дублированными значениями и сможем вручную их откорректировать или удалить не нужные.
Ссылки на мои ресурсы по Excel
★ YouTube-канал по Excel и Word
★ Телеграм
