Посчитать количество значений в ячейке Excel
Эту статью я хочу посвятить повторам тех значений, которые встречаются в вашей таблице, то есть мы научимся как подсчитывать повторения в Excel. Данная возможность будет полезной при подсчёте одинаковых значений в необходимом диапазоне, это поможет когда с большого массива вам нужно сгруппировать, например, сколько сотрудников сделало чеков, сколько раз работали с тем или иным поставщиком и многое другое.
Для начала рассмотрим, как выглядят столбики с данными, повторения в которых, мы собственно и будем считать. К примеру, возьмём список сотрудников, которые совершают продажи.
- С помощью функции СЧЁТЕСЛИ;
- С помощью функции СЧЁТЕСЛИМН;
- С помощью функции ДЛСТР;
- С помощью функций VBA.
Используя функцию СЧЁТЕСЛИ
В Excel произвести такой подсчёт чрезвычайно просто, достаточно задействовать функцию СЧЁТЕСЛИ и она за несколько секундок всё за вас сделает. В нашем случае формула будет следующего вида:
=СЧЁТЕСЛИ($B$2:$B$11;B15)
Вторым аргументом «критерий» ставим указание на ячейку, по которому будет производиться подсчёт аналогичных данных. Если он один вы можете его прописать вручную как текстовое слово и вместо адреса ячейки «B15» указать «Нагаев А.В.», результат будет аналогичным, но только в одном конкретном случае, возможность автоматизации таблицы тогда значительно сократится.
Дополнительная информация! Кроме прямого указания поиска данных, функция СЧЁТЕСЛИ умеет работать с символами подстановки. Таких знаков используют двух видов «?» и «*„, применять их возможно только при работе с текстовыми символами. Знак “*» позволяет заменить абсолютно любое количество значений, а Знак «?» производит замену только одного символа.
Для работы с числовыми значениями необходимо применять знаки операторов сравнения: «>», «<», «<>» и «=». К примеру, для подсчёта числовых значений больше «нуля» прописывайте «>0», а для подсчёта непустых ячеек нужен указать «<>».
Используем функцию СЧЁТЕСЛИМН
Когда у вас возникает необходимость подсчитывать повторения в Excel, но уже по нескольким критериям, тогда нужно работать с функцией СЧЁТЕСЛИМН, которая легко и просто сможет это выполнить.
В моём примере я добавлю категорию продаж по городам и используя формулы соберу необходимые мне повторы:
=СЧЁТЕСЛИМН($B$2:$B$11;B14;$C$2:$C$11;C14)
Работаем с функцией ДЛСТР
Теперь давайте рассмотрим ситуацию, когда не всё так просто и упорядоченно, когда информация сбита в одну ячейку, к примеру, «Нагаев Гаврош Каропачёв Козубенко Нагаев Гаврош Козубенко Нагаев Нагаев». В этом случае статистические функции нам не помогут, необходимо считать символы и сверять повторы значений с указанным эталоном. Для этих целей есть много иных полезных функций, используя которые это можно провернуть достаточно просто:
=(ДЛСТР($B$2) -ДЛСТР(ПОДСТАВИТЬ($B$2;B5;«")))/ДЛСТР(B5)
С помощью функций VBA
Последний рассматриваемый вариант, это подсчёт количества повторений с помощью, созданной в VBA функции. Функции я не писал, а просто предлагаю вам найденный ранее вариант, чтобы упростить вашу работу.
Для начала вам нужно запустить редактор макросов VBA и вставить новый модуль с помощью команд «Insert» — «Module».
После всего этого вызываете «Диспетчер функций» в панели управления или с помощью горячих клавиш Ctrl+F3 и в категории «Определённые пользователем» у вас появляется новая необходимая функция.
=GetRepeat ($B$2;B8), где:
