Мы используем cookie, чтобы сайтом было удобно пользоваться
Считаем уникальные значения в Excel разными способами: старые и новые формулы, Power Query и Power Pivot
Считаем уникальные значения: новые, модные и прогрессивные функции
У нас есть список сделок. Мы хотим понять, сколько у нас вообще клиентов (уникальных значений в столбце «Клиент») и сколько товаров покупал каждый клиент (не число сделок, то есть строк; не количество штук; а количество уникальных наименований).
Как решать эту задачу? Начнем с простого. Но простое доступно, увы, только в Excel 2021 / 2024 / 365.
Список уникальных значений (или строк в общем случае) можно получить функцией УНИК / UNIQUE.
А потом посчитать, сколько в списке уникальных значений — старой доброй функцией СЧЁТЗ / COUNTA.
=СЧЁТЗ (УНИК (диапазон))
А если нужно количество уникальных товаров, купленных каждым клиентом?
Тогда сначала фильтруем одноименной функцией товары только по нужному клиенту, а потом уже считаем, сколько в этом списке уникальных значений:
=СЧЁТЗ (УНИК (ФИЛЬТР (диапазон с товарами;диапазон с условием=условие)))
P. S. А в Google Таблицах все можно сделать одной функцией COUNTUNIQUEIFS, считающей уникальные значения с одним или несколькими условиями.
Считаем уникальные значения: старая школа
Как быть в старой версии Excel вплоть до 2019?
Воспользоваться формулами массива, суровыми — с фигурными скобками. Вводить такие нужно, напомню, сочетанием Ctrl+Shift+Enter (руками ввести фигурные скобки не получится).
Вот вариант формулы от Николая Павлова из его мощной книги «Мастер формул»:
{=СУММ (1/СЧЁТЕСЛИ (тот же диапазон;тот же диапазон))}
Что тут происходит? Мы для каждого значения в диапазоне считаем, сколько раз оно встречается (функция СЧЁТЕСЛИ / COUNTIF). Допустим, некий клиент встречается 5 раз.
Потом делим единицу на эти числа. Для этого клиента в результате получим пять чисел 0,2. В сумме они всегда дадут единицу, то есть каждому клиенту (уникальному значению) будет соответствовать единица. И нам останется только просуммировать эти единицы, получив нужное число.
А подсчет уникальных с условием? Можно добавить вспомогательный столбец с такой формулой (по мотивам книги Майка Гирвина с очень говорящим названием Ctrl + Shift + Enter):
=СУММ (И (СЧЁТЕСЛИМН ($B$ 2:B2;[@Товар];$A$ 2:A2;$H$ 6)=1;[@Клиент]=$H$ 6))
Ссылка вида $B$ 2:B2 означает, что мы в каждой строке ссылаемся на диапазон, начинающийся в B2 и заканчивающийся в текущей строке. То есть считаем от начала таблицы до строки, в которой находится формула.
Она будет возвращать единицу только для товаров выбранного клиента (это условие, клиент выбирается в списке в ячейке H6) — но только один раз, когда товар впервые встречается в списке. Для последующих вхождений она будет возвращать нули.
Для других клиентов нули будут априори (не будет выполнено второе условие внутри СЧЁТЕСЛИМН / COUNTIFS. Нам останется просуммировать единицы во вспомогательном столбце.
Вы можете скачать книгу Excel со всеми формулами по ссылке — изучайте на здоровье!
Воспользоваться формулами массива, суровыми — с фигурными скобками. Вводить такие нужно, напомню, сочетанием Ctrl+Shift+Enter (руками ввести фигурные скобки не получится).
Вот вариант формулы от Николая Павлова из его мощной книги «Мастер формул»:
{=СУММ (1/СЧЁТЕСЛИ (тот же диапазон;тот же диапазон))}
Что тут происходит? Мы для каждого значения в диапазоне считаем, сколько раз оно встречается (функция СЧЁТЕСЛИ / COUNTIF). Допустим, некий клиент встречается 5 раз.
Потом делим единицу на эти числа. Для этого клиента в результате получим пять чисел 0,2. В сумме они всегда дадут единицу, то есть каждому клиенту (уникальному значению) будет соответствовать единица. И нам останется только просуммировать эти единицы, получив нужное число.
А подсчет уникальных с условием? Можно добавить вспомогательный столбец с такой формулой (по мотивам книги Майка Гирвина с очень говорящим названием Ctrl + Shift + Enter):
=СУММ (И (СЧЁТЕСЛИМН ($B$ 2:B2;[@Товар];$A$ 2:A2;$H$ 6)=1;[@Клиент]=$H$ 6))
Ссылка вида $B$ 2:B2 означает, что мы в каждой строке ссылаемся на диапазон, начинающийся в B2 и заканчивающийся в текущей строке. То есть считаем от начала таблицы до строки, в которой находится формула.
Она будет возвращать единицу только для товаров выбранного клиента (это условие, клиент выбирается в списке в ячейке H6) — но только один раз, когда товар впервые встречается в списке. Для последующих вхождений она будет возвращать нули.
Для других клиентов нули будут априори (не будет выполнено второе условие внутри СЧЁТЕСЛИМН / COUNTIFS. Нам останется просуммировать единицы во вспомогательном столбце.
Вы можете скачать книгу Excel со всеми формулами по ссылке — изучайте на здоровье!
Считаем уникальные значения: сводная таблица
А почему бы не вывести в сводной таблице список клиентов, отправить в область значений товары да посмотреть, сколько уникальных?
Но проблема в том, что по умолчанию сводная будет считать просто число значений. То есть это будет количество строк (=покупок), а не уникальных товаров.
Но если в вашей версии Excel есть Power Pivot, то достаточно просто поставить галочку «Добавить эти данные в модель данных» при вставке сводной (а если PP нет, то и флажка этого не будет) — и случится магия. В списке вычислений появится «Число разных элементов», это и будут уникальные. Как все это сделать — в очень коротком видео без звука.
Залезать в сам Power Pivot, связывать там данные — все это не нужно. Собственно, связывать нечего, в данном примере мы строим сводную на основе одной таблицы. Но тот факт, что она будет добавлена в модель данных этой книги Excel (в Power Pivot), активирует эту опцию.
Ну, а в Google Таблицах в сводных и так есть COUNTUNIQUE среди операций🔥
Но проблема в том, что по умолчанию сводная будет считать просто число значений. То есть это будет количество строк (=покупок), а не уникальных товаров.
Но если в вашей версии Excel есть Power Pivot, то достаточно просто поставить галочку «Добавить эти данные в модель данных» при вставке сводной (а если PP нет, то и флажка этого не будет) — и случится магия. В списке вычислений появится «Число разных элементов», это и будут уникальные. Как все это сделать — в очень коротком видео без звука.
Залезать в сам Power Pivot, связывать там данные — все это не нужно. Собственно, связывать нечего, в данном примере мы строим сводную на основе одной таблицы. Но тот факт, что она будет добавлена в модель данных этой книги Excel (в Power Pivot), активирует эту опцию.
Ну, а в Google Таблицах в сводных и так есть COUNTUNIQUE среди операций🔥
Считаем уникальные значения: Power Query
Наконец, четвертый вариант — если у вас есть Power Query, а это куда больше версий, чем в случае с новыми функциями.
Загружаем данные в Power Query:
Данные — Получить данные — Из таблицы / диапазона
Удаляем все, кроме двух столбцов — по одному будем группировать (у нас это «Клиент»), по другому считать уникальные значения (у нас это «Товар»)
Преобразование — Группировать по — выбираем нужные столбцы и операцию «Количество уникальных строк«
Закрыть и загрузить в — выбираем «Таблица» на новый или существующий лист. Готово!
Загружаем данные в Power Query:
Данные — Получить данные — Из таблицы / диапазона
Удаляем все, кроме двух столбцов — по одному будем группировать (у нас это «Клиент»), по другому считать уникальные значения (у нас это «Товар»)
Преобразование — Группировать по — выбираем нужные столбцы и операцию «Количество уникальных строк«
Закрыть и загрузить в — выбираем «Таблица» на новый или существующий лист. Готово!