Посчитать количество уникальных значений в столбце Excel 365 и 2021 можно формулой =СЧЁТЗ(УНИК(A2:A7)), а сколько раз встречается каждое значение — =СЧЁТЕСЛИ(A2:A7;C2) рядом со списком =УНИК(A2:A7). В старых версиях число уникальных значений даёт =СУММПРОИЗВ((A2:A7<>"")/СЧЁТЕСЛИ(A2:A7;A2:A7&"")). Свести одинаковые ячейки с подсчётом и суммированием соседних столбцов в один клик можно «Быстрой сводной таблицей» !SEMTools.
Сводные таблицы Excel - отличный инструмент с широчайшими возможностями по анализу данных. Он позволяет в реальном времени смотреть на данные в разных разрезах, фильтровать данные и создавать новые вычисляемые поля. Сводным таблицам посвящены множество курсов и руководств.
Однако, тем, кто знаком с функциональностью сводных таблиц, известны их проблемы:
- сводные таблицы - это обновляемый запрос, в связи с чем при работе с большими объемами данных они строятся довольно долго(зато потом быстро пересчитываются при изменении исходных данных).
- к данным сводной таблицы нельзя обратиться, выделяя ячейки обычным кликом - нужно указывать диапазон, прописывая его адрес вручную
- Создание сводной таблицы не предусматривает создание строк и столбцов, поэтому чтобы построить финальную сводку, понадобится сделать еще несколько дополнительных действий.
А что, если стоит простая задача - посчитать уникальные ячейки диапазона и вывести списком? Или сделать простую сводку по одному-двум столбцам? Просто, чтобы в моменте взглянуть на данные и посмотреть, что получится, для принятия быстрых решений?
В таких случаях все эти возможности выглядят как излишество, но ими приходится пользоваться.
Поэтому в !SEMTools родился инструмент "Быстрая сводная таблица". Он делает лишь одно действие - создание сводной таблицы с суммированием, но делает ее максимально быстро, не задавая лишних вопросов.
Посчитать количество уникальных значений формулой
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Значение | Уникальные | Сколько раз | |
| 2 | A | A=УНИК(A2:A7) | 3=СЧЁТЕСЛИ($A$2:$A$7;C2) | |
| 3 | B | B | 2=СЧЁТЕСЛИ($A$2:$A$7;C3) | |
| 4 | A | C | 1=СЧЁТЕСЛИ($A$2:$A$7;C4) | |
| 5 | C | |||
| 6 | B | Всего уникальных: | 3=СЧЁТЗ(УНИК(A2:A7)) | |
| 7 | A |
УНИК в C2 сам заполнил C3:C4. СЧЁТЗ(УНИК(…)) сразу даёт число уникальных значений — 3. Формула СУММПРОИЗВ для старых версий вернёт то же число; &"" в ней нужно, чтобы пустые ячейки не давали деления на ноль.
В английском Excel: =COUNTA(UNIQUE(A2:A7))
Уникальные значения в сводной таблице
Штатная сводная таблица тоже умеет считать уникальные значения: при её создании поставьте галочку «Добавить эти данные в модель данных», затем в параметрах поля значений выберите операцию «Число различных элементов». Без модели данных этой операции в списке нет.
Как посчитать количество ячеек с одинаковым значением в Excel
Подсчет количества ячеек с одинаковым текстом с помощью !SEMTools. В примере в ячейках по 1 букве, но в реальности могут быть какие угодно повторяющиеся данные. Макрос объединяет и одновременно суммирует одинаковые ячейки, выводя на отдельный лист их количество.
Объединение одинаковых ячеек в excel с суммированием остальных столбцов
Когда нужно быстро построить сводку с суммами определенных метрик. Столбцов метрик может быть сколько угодно. Пустые и текстовые значения среди чисел будут автоматически пропущены - оставим качество на совести создателя таблицы.
Выделяем нужные столбцы и жмем тот же самый макрос, что и в примере выше! Он посчитает все сам.
Важно: макрос берет крайний левый столбец в выделенном диапазоне и производит склейку по нему, а суммирует все остальные, находящиеся справа. Поэтому для получения желаемого результата может потребоваться подготовить для него данные соответствующим образом. Также видно, что макрос не сохраняет форматирование исходных данных - это сделано в угоду быстродействия на больших объемах.
Почему уникальные значения считаются неверно
- Одинаковые на вид значения считаются разными. В ячейках лишние пробелы — удалите их. Регистр УНИК и СЧЁТЕСЛИ не различают.
- Формула СУММПРОИЗВ выдаёт #ДЕЛ/0!. В диапазоне пустые ячейки, а в формуле нет приёма &"".
- СЧЁТЗ(УНИК(…)) на 1 больше ожидаемого. В диапазон попала пустая ячейка — УНИК возвращает её как отдельное значение 0. Используйте УНИК(ФИЛЬТР(A2:A100;A2:A100<>"")).
- Нет операции «Число различных элементов». Сводная создана без добавления данных в модель данных.
Уникальные значения — частые вопросы
Как посчитать количество уникальных значений в столбце?
В Excel 365 и 2021 — =СЧЁТЗ(УНИК(A2:A100)), в старых версиях — =СУММПРОИЗВ((A2:A100<>"")/СЧЁТЕСЛИ(A2:A100;A2:A100&"")).
Как посчитать, сколько раз встречается каждое значение?
Выведите список уникальных значений функцией УНИК и рядом посчитайте каждое через СЧЁТЕСЛИ — или постройте «Быструю сводную таблицу» !SEMTools.
Как объединить одинаковые ячейки с суммированием?
Сводной таблицей (значения — в «Строки», числа — в «Значения» с операцией «Сумма») или процедурой «Быстрая сводная таблица» !SEMTools.
Как получить список без повторов?
Формулой =УНИК(A2:A100) или командой «Данные → Удалить дубликаты».
Посчитать повторы, свести одинаковые ячейки и просуммировать соседние столбцы можно в один клик «Быстрой сводной таблицей» !SEMTools — без настройки полей и обновления сводной.
!SEMTools решает эту и сотни других задач в пару кликов!
Итоги: как посчитать уникальные значения в Excel
| Задача | Способ |
|---|---|
| Число уникальных (Excel 365, 2021) | =СЧЁТЗ(УНИК(A2:A100)) |
| Число уникальных (старые версии) | =СУММПРОИЗВ((A2:A100<>"")/СЧЁТЕСЛИ(A2:A100;A2:A100&"")) |
| Сколько раз каждое значение | УНИК + СЧЁТЕСЛИ или сводная таблица |
| Сводка с суммами в 1 клик | Быстрая сводная таблица !SEMTools |
Надеюсь, на этой странице вы нашли то, что искали. Вас наверняка заинтересуют и похожие операции: удалить дубликаты и статистические метрики.