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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Вот решение:

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

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

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

=СТРОКИ(УНИКАЛЬНЫЕ(A2:C10))

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

Чтобы подсчитать уникальные значения в 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)

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