Посчитать, сколько раз значение встречается в столбце Excel, можно функцией =СЧЁТЕСЛИ($A$2:$A$100;A2): протяните её вниз, и напротив каждой ячейки появится число её повторений. Список уникальных значений с количеством в Excel 365 дают =УНИК(A2:A100) и СЧЁТЕСЛИ, в любой версии — сводная таблица. Количество ячеек, содержащих слово, считает =СЧЁТЕСЛИ(A:A;"*чай*"), а подсчёт повторений для всего списка сразу делает процедура !SEMTools в один клик.
Фактически, это один из способов найти дублирующиеся ячейки, ведь, если количество повторений больше единицы, это означает, что ячейки дублируются.
Разница лишь в том, что, помимо выявления самого факта повторений, мы узнаём и то, сколько раз значение повторяется в списке. В этом отличие от метода выявления повторов условным форматированием.
Посчитать количество повторений функцией СЧЁТЕСЛИ
Функция СЧЁТЕСЛИ считает, сколько ячеек диапазона равны критерию. Если критерий — сама ячейка строки, получаем число её повторений в столбце. Диапазон закрепляем знаками $, чтобы он не сдвигался при протягивании:
| A | B | |
|---|---|---|
| 1 | Товар | Сколько раз |
| 2 | Чай | 3=СЧЁТЕСЛИ($A$2:$A$7;A2) |
| 3 | Кофе | 2=СЧЁТЕСЛИ($A$2:$A$7;A3) |
| 4 | Чай | 3=СЧЁТЕСЛИ($A$2:$A$7;A4) |
| 5 | Сок | 1=СЧЁТЕСЛИ($A$2:$A$7;A5) |
| 6 | Кофе | 2=СЧЁТЕСЛИ($A$2:$A$7;A6) |
| 7 | Чай | 3=СЧЁТЕСЛИ($A$2:$A$7;A7) |
«Чай» встречается трижды, поэтому 3 стоит напротив каждого его вхождения. Чтобы посчитать одно конкретное значение, укажите его вместо ссылки: =СЧЁТЕСЛИ(A2:A7;"Чай").
В английском Excel: =COUNTIF($A$2:$A$7,A2)
Учтите, что СЧЁТЕСЛИ не различает регистр («Чай» и «чай» — одно значение) и не видит разницы, если в конце значения есть лишний пробел — такие значения посчитаются как разные. Подробнее о возможностях функции — в моём справочнике функций Excel.
Список уникальных значений с количеством повторений
Если нужно не число напротив каждой строки, а сводный список «значение — сколько раз», в Excel 365 его строят две формулы: =УНИК(A2:A7) выводит уникальные значения, а =СЧЁТЕСЛИ(A2:A7;D2#) считает каждое из них (знак # ссылается на весь «разлившийся» диапазон):
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Товар | Уникальные | Количество | ||
| 2 | Чай | Чай=УНИК(A2:A7) | 3=СЧЁТЕСЛИ(A2:A7;D2#) | ||
| 3 | Кофе | Кофе | 2 | ||
| 4 | Чай | Сок | 1 | ||
| 5 | Сок | ||||
| 6 | Кофе | ||||
| 7 | Чай |
Формулы стоят только в D2 и E2, остальные ячейки заполняются автоматически. Чтобы отсортировать список по частоте, оберните в СОРТПО (SORTBY).
В английском Excel: =COUNTIF(A2:A7,D2#)
Посчитать количество повторений с помощью сводной таблицы
Другой удобный инструмент — сводные таблицы. Вы можете поместить столбец значений в область строк сводной таблицы, а затем добавить его же в область значений с операцией «Количество». Сводная работает в любой версии Excel и сама обновляется кнопкой «Обновить». В результате вы получите список всех уникальных значений и количество их повторений.
Посчитать, сколько раз встречается слово
Если слово стоит не отдельной ячейкой, а внутри текста, есть два разных вопроса: в скольких ячейках оно есть и сколько раз оно встречается всего. Первый решает СЧЁТЕСЛИ со звёздочками, второй — разница длин текста с ним и без него:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Текст | Ячеек со словом | Вхождений всего | |
| 2 | чай чёрный, чай зелёный | 3=СЧЁТЕСЛИ(A2:A5;"*чай*") | 4=СУММПРОИЗВ((ДЛСТР(A2:A5)-ДЛСТР(ПОДСТАВИТЬ(A2:A5;"чай";"")))/ДЛСТР("чай")) | |
| 3 | кофе и чай | |||
| 4 | сок | |||
| 5 | чай |
Слово «чай» есть в трёх ячейках, но в первой оно встречается дважды — всего 4 раза. ПОДСТАВИТЬ различает регистр, поэтому для подсчёта «Чай» и «чай» вместе оберните диапазон в СТРОЧН.
В английском Excel: =SUMPRODUCT((LEN(A2:A5)-LEN(SUBSTITUTE(A2:A5,"чай","")))/LEN("чай"))
Посчитать повторения в 1 клик с !SEMTools
Моя надстройка !SEMTools позволяет выполнять подсчёты ещё проще. Процедура меню "Извлечь" - "Посчитать (статистические метрики)" быстро посчитает количество вхождений каждого элемента в диапазоне.
Быстрая сводная
Если не хочется добавлять столбцы в исходной таблице и нужно просто быстро узнать, сколько раз какие ячейки повторяются, в !SEMTools есть супербыстрое решение: сводная таблица, которая выводится на новый лист и не требует множества манипуляций со значениями. Подробнее в статье про инструмент: Быстрая сводная таблица в Excel.
N-Gram анализ
Иногда важно подсчитать не только повторы значений целиком, но и отдельные слова внутри текста. В этом случае помогает N-Gram анализ, доступный в !SEMTools. Он позволяет получить список уникальных слов и фраз из столбца Excel с указанием количества их вхождений. Это незаменимо при работе с ключевыми словами, SEO-данными и большими текстами.
Почему СЧЁТЕСЛИ считает повторы неправильно
- Одинаковые на вид значения считаются как разные. В данных лишние пробелы или неразрывные пробелы. Удалите лишние пробелы перед подсчётом.
- Количество съезжает при протягивании. Диапазон не закреплён: вместо $A$2:$A$7 в нижних строках оказывается A5:A10. Закрепите его клавишей F4.
- Значения со звёздочкой или вопросительным знаком считаются неверно. Для СЧЁТЕСЛИ это подстановочные знаки. Используйте =СУММПРОИЗВ(--($A$2:$A$7=A2)) — точное сравнение без подстановки.
- Текст длиннее 255 символов даёт #ЗНАЧ!. СЧЁТЕСЛИ не принимает такие длинные критерии — подойдёт та же формула со СУММПРОИЗВ.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Город | Способ | Сколько «Москва» | |
| 2 | Москва | СЧЁТЕСЛИ | 3=СЧЁТЕСЛИ(A2:A6;"Москва") | |
| 3 | Казань | СУММПРОИЗВ | 3=СУММПРОИЗВ(--(A2:A6="Москва")) | |
| 4 | москва | С учётом регистра | 2=СУММПРОИЗВ(--СОВПАД(A2:A6;"Москва")) | |
| 5 | Москва | |||
| 6 | Тула |
СЧЁТЕСЛИ и обычное сравнение не различают регистр, поэтому «москва» тоже посчитана. СОВПАД сравнивает с учётом регистра. СУММПРОИЗВ работает во всех версиях без Ctrl+Shift+Enter.
В английском Excel: =COUNTIF(A2:A6,"Москва")
Посчитать количество повторений — частые вопросы
Как посчитать количество одинаковых значений в столбце?
Введите рядом со столбцом =СЧЁТЕСЛИ($A$2:$A$100;A2) и протяните вниз — напротив каждой ячейки будет число её повторений.
Как узнать, сколько раз встречается конкретное значение?
Укажите его критерием: =СЧЁТЕСЛИ(A:A;"Москва").
Как посчитать, сколько раз слово встречается в тексте ячеек?
Количество ячеек со словом — =СЧЁТЕСЛИ(A:A;"*слово*"), общее число вхождений — разница длин с ПОДСТАВИТЬ, как в примере выше.
Как получить список уникальных значений с количеством?
В Excel 365 — =УНИК(A2:A100) и СЧЁТЕСЛИ со ссылкой на результат, в любой версии — сводная таблица или быстрая сводная !SEMTools.
Посчитать, сколько раз повторяется каждое значение столбца, можно в один клик с !SEMTools — без СЧЁТЕСЛИ и закрепления диапазонов, а быстрая сводная выведет список уникальных значений с количеством на новый лист.
!SEMTools решает эту и сотни других задач в пару кликов!
Итоги: как посчитать количество повторений в Excel
Надеюсь, на этой странице вы нашли то, что искали, и смогли посчитать повторы в своих данных. Число повторений напротив каждой строки даёт СЧЁТЕСЛИ, сводный список — УНИК со СЧЁТЕСЛИ или сводная таблица, слово внутри текста — СЧЁТЕСЛИ со звёздочками или разница длин. Вас наверняка заинтересуют и похожие операции: