Как посчитать уникальные значения в Excel: с критериями, игнорируя пробелы

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

Пару лет назад мы обсуждали различные способы подсчета уникальных и уникальных значений в Excel. Но, как и любая другая программа, Microsoft Excel постоянно развивается, и почти в каждом выпуске появляются новые функции. Сегодня мы рассмотрим, как можно подсчитывать уникальные значения в Excel с помощью недавно представленных функций динамического массива. Если вы еще не использовали ни одну из этих функций, вы будете поражены, увидев, насколько проще стали формулы с точки зрения построения и удобства использования.

Примечание. Все формулы, обсуждаемые в этом руководстве, основаны на функции UNIQUE, которая доступна только в Excel 365 и Excel 2021. Если вы используете Excel 2019, Excel 2016 или более раннюю версию, ознакомьтесь с решениями в этой статье.

Подсчет уникальных значений в столбце

Самый простой способ подсчитать уникальные значения в столбце — использовать функцию UNIQUE вместе с функцией COUNTA:

СЧЕТЧИК(УНИКАЛЬНЫЙ(диапазон))

Формула работает с этой простой логикой: UNIQUE возвращает массив уникальных записей, а COUNTA подсчитывает все элементы массива.

В качестве примера посчитаем уникальные имена в диапазоне B2:B10:

=СЧЕТЧИК(УНИКАЛЬНЫЙ(B2:B10))

Формула говорит нам, что в списке победителей 5 разных имен:
Формула Excel для подсчета уникальных значений в столбце

Кончик. В этом примере мы подсчитываем уникальные текстовые значения, но вы можете использовать эту формулу и для других типов данных, включая числа, даты, время и т. д.

Подсчет уникальных значений, которые встречаются только один раз

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

Чтобы получить список однократных вхождений, установите для третьего аргумента UNIQUE значение TRUE:

УНИКАЛЬНЫЙ(B2:B10,,ИСТИНА))

Для подсчета уникальных однократных вхождений вложите UNIQUE в функцию ROW:

СТРОКИ(УНИКАЛЬНЫЕ(B2:B10,,ИСТИНА))

Обратите внимание, что COUNTA не будет работать в этом случае, потому что он подсчитывает все непустые ячейки, включая значения ошибок. Таким образом, если результаты не будут найдены, UNIQUE вернет ошибку, а COUNTA посчитает ее за 1, что неверно!

Чтобы обработать возможные ошибки, оберните формулу функцией ЕСЛИОШИБКА и дайте ей указание выводить 0 при возникновении какой-либо ошибки:

=ЕСЛИОШИБКА(СТРОКИ(УНИКАЛЬНЫЕ(B2:B10,,ИСТИНА)), 0)

В результате вы получаете счет, основанный на концепции уникальности базы данных:
Подсчет уникальных значений, которые встречаются только один раз

Подсчет уникальных строк в Excel

Теперь, когда вы знаете, как считать уникальные ячейки в столбце, есть идеи, как найти количество уникальных строк?

Вот решение:

СТРОКИ(УНИКАЛЬНЫЕ(диапазон))

Хитрость заключается в том, чтобы «скормить» весь диапазон UNIQUE, чтобы он нашел уникальные комбинации значений в нескольких столбцах. После этого вы просто заключаете формулу в функцию СТРОКИ, чтобы вычислить количество строк.

Например, чтобы подсчитать уникальные строки в диапазоне A2:C10, мы используем эту формулу:

=СТРОКИ(УНИКАЛЬНЫЕ(A2:C10))
Формула Excel для подсчета уникальных строк

Подсчитывать уникальные записи, игнорируя пустые ячейки

Чтобы подсчитать уникальные значения в Excel, игнорируя пробелы, используйте функцию ФИЛЬТР, чтобы отфильтровать пустые ячейки, а затем преобразовать их в уже знакомую формулу СЧЕТЧИК УНИКАЛЬНЫХ:

СЧЕТЧИК(УНИКАЛЬНЫЙ(ФИЛЬТР(диапазон, диапазон<>«»)))

С исходными данными в B2:B11 формула принимает следующий вид:

=СЧЕТЧИК(УНИКАЛЬНЫЙ(ФИЛЬТР(B2:B11, B2:B11<>«»)))

На скриншоте ниже показан результат:
Подсчет уникальных записей без учета пустых ячеек

Подсчет уникальных значений с критериями

Чтобы извлечь уникальные значения на основе определенных критериев, вы снова используете функции UNIQUE и FILTER вместе, как описано в этом примере. Затем вы используете функцию ROWS для подсчета уникальных записей и IFERROR для перехвата всех видов ошибок и замены их на 0:

ЕСЛИОШИБКА(СТРОКИ(УНИКАЛЬНЫЕ(диапазон, критерии_диапазонзнак равнокритерии))), 0)

Например, чтобы узнать, сколько разных победителей в конкретном виде спорта, используйте следующую формулу:

=ЕСЛИОШИБКА(СТРОКИ(УНИКАЛЬНЫЕ(ФИЛЬТР(A2:A10,B2:B10=E1))), 0)

Где A2:A10 — диапазон для поиска уникальных имен (диапазон), B2:B10 — виды спорта, в которых соревнуются победители (критерии_диапазон), а E1 — интересующий вид спорта (критерии).
Подсчет уникальных значений с критериями

Подсчет уникальных значений с несколькими критериями

Формула для подсчета уникальных значений на основе нескольких критериев очень похожа на приведенный выше пример, хотя критерии построены немного по-другому:

ЕСЛИОШИБКА(СТРОКИ(УНИКАЛЬНЫЕ(диапазон(критерии_диапазон1знак равнокритерии1) * (критерии_диапазон2знак равнокритерии2)))), 0)

Те, кому интересно узнать внутреннюю механику, могут найти объяснение логики формулы здесь: Найдите уникальные значения на основе нескольких критериев.

В этом примере мы собираемся выяснить, сколько разных победителей в конкретном виде спорта в F1 (критерии 1) и несовершеннолетние в F2 (критерии 2). Для этого мы используем эту формулу:

=ЕСЛИОШИБКА(СТРОКИ(УНИКАЛЬНЫЕ(ФИЛЬТР(A2:A10, (B2:B10=F1) * (C2:C10

Где A2:B10 — список имен (диапазон), C2:C10 – спортивные (диапазон_критериев 1) и D2:D10 — возраст (диапазон_критериев 2).
Подсчет уникальных значений с несколькими критериями

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

Практическая рабочая тетрадь для скачивания

Примеры формулы подсчета уникальных значений (файл .xlsx)

Вас также могут заинтересовать

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *