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

Постановка задачи

Есть диапазон с данными, в котором некоторые значения повторяются больше одного раза:

count-unique1.png

Задача - подсчитать количество уникальных (неповторяющихся) значений в диапазоне. В приведенном выше примере, как легко заметить, на самом деле упоминаются всего четыре варианта.

Рассмотрим несколько способов ее решения.

Способ 1. Если нет пустых ячеек

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

count-unique2.png

Не забудьте ввести ее как формулу массива, т.е. нажать после ввода формулы не Enter, а сочетание Ctrl+Shift+Enter.

Технически, эта формула пробегает по всем ячейкам массива и вычисляет для каждого элемента количество его вхождений в диапазон с помощью функции СЧЕТЕСЛИ (COUNTIF). Если представить это в виде дополнительного столбца, то выглядело бы оно так:

count-unique4.png

Потом вычисляются дроби 1/Число вхождений для каждого элемента и все они суммируются, что и даст нам количество уникальных элементов:

count-unique5.png

Способ 2. Если есть пустые ячейки

Если в диапазоне встречаются пустые ячейки, то придется немного усовершенствовать формулу, добавив проверку на пустые ячейки (иначе получим ошибку деления на 0 в дроби):

count-unique3.png

Вот и все дела.

Ссылки по теме

 



09.10.2013 12:54:52
Вариант 2, для 2-го способа, немассивно:
=СУММПРОИЗВ(СЧЁТЕСЛИ(A2:A10;A2:A10)^(2*ЕПУСТО(A2:A10)-1))
09.10.2013 13:41:15
Класс! Отличный способ!
24.06.2015 10:13:35
Доброго времени суток, могли бы вы, для новичка, разобрать данную формулу, начиная со знака крышки.
=СУММПРОИЗВ(СЧЁТЕСЛИ(A2:A10;A2:A10)^(2*ЕПУСТО(A2:A10)-1))
11.10.2013 20:12:03
Для полного диапазона:

=СУММПРОИЗВ(1/СЧЁТЕСЛИ(A2:A10;A2:A10))

Для диапазона с пустыми ячейками:

=СУММПРОИЗВ(1/СЧЁТЕСЛИ(A2:A10;A2:A10&""))-1
Не задан ID пользователя.

30.06.2014 12:35:06
Неудобство в том, что диапазон должен обязательно содержать пустые значения, чтобы "-1" было в тему... Всё-таки метод от MCH наиболее универсален, не говоря уже о красоте.
14.01.2014 14:25:30
Всем доброго времени суток! Можно ли усовершенствовать формулу так, чтобы считать количество уникальных элементов в отфильтрованных строках (при использовании автофильтра)? Что-то вроде ПромежуточныхИтогов, где будут считаться количество уникальных.
16.08.2014 10:17:20
Не уверен, что это можно реализовать формулой. Я бы писал макрофункцию на VBA в таком случае.
Не задан ID пользователя.

01.10.2014 18:33:03
Я сделал так.
1) В отдельной колонке вывел результат 1/Число вхождений, как на последней картинке в Способ 1
2) На эту колонку наложил функцию =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;колонка)
В результате, когда применяю фильтр, получаю суммарное количество уникальных значений.

Недостаток один - если фильтр наложить по другим полям, получим неверное отображение количества уникальных значений.
Не задан ID пользователя.

01.07.2016 16:33:18
Огромное спасибо. Ваш способ именно то, что мне пригодилось!
Не задан ID пользователя.

13.08.2014 22:30:35
Как посчитать уникальных значений из списка деталей по длине и ширине,когда длина и ширина в разных ячейках.
16.08.2014 10:16:07
Склеить длину и ширину в один столбец с помощью функции СЦЕПИТЬ (CONCATENATE) и дальше подсчитать уникальные, как описано в статье выше.
Не задан ID пользователя.

24.09.2014 22:52:00
А мне еще нужно посчитать кол-во  уникальных значений не просто в одном столбце, а ещё по двум критериям в таблице. Это реально? Можно ли как-то соединить с СЧЁТЕСЛИМН?
27.09.2014 09:37:25
См. выше. Я бы склеил все критерии в отдельный столбец с помощью функции СЦЕПИТЬ и искал уникальные потом по этой колонке. Хотя не видя примера детально ответить сложно.
Не задан ID пользователя.

07.11.2014 13:06:35
Буквально вчера бился с этой задачей, но вроде бы победил :) Допустим, что в указанном примере в столбце "B" стоит требуемый нам дополнительный параметр, например "цвет". Тогда формула будет выглядеть следующим образом:

=СУММ(ЕСЛИОШИБКА(1/СЧЁТЕСЛИМН(A2:A10;A2:A10;B2:B10;"Красный");0))
подсчет по двум критериям в таблице можно посмотреть в последнем файле этой темы: http://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=8&TID=30488&MID=265827#message265827
Не задан ID пользователя.

08.10.2014 12:38:37
А мне еще нужно посчитать кол-во уникальных значений не просто в одном столбце, а ещё по двум критериям в таблице. Это реально? Можно ли как-то соединить с СЧЁТЕСЛИМН?
 

Тот же вопрос, например мне нужно посчитать кол-во уникальных значений, при том в определенном диапазоне дат.

Например Таблица содержит наименования продуктов и даты их выпуска )) Подскажите.. )8)
Не задан ID пользователя.

27.10.2014 20:27:24
Добрый день. А есть ли возможность вставить подсчет уникальных данных в сводную таблицу? Например в вычисляемое поле?
08.11.2014 16:11:26
Вычисляемые поля в сводных таблицах весьма ограничены по возможностям - формулу массива или сложную функцию туда точно не вставить. В новом Excel 2013 подсчет уникальных значений добавлен в стандартный набор вычислений по полю, кстати.
Не задан ID пользователя.

13.02.2015 13:10:38
Здравствуйте! Просьба помочь с решением такой задачи: нужно по каждой дате подсчитать количество машин (по гос. номеру). №№ ТТН пренебречь.
Дат в 2014 году было много, машин - тоже.....
Заранее спасибо!
Гос.№ авто№ ТТНДата
Т 240РВ403826/1201.03.2014
Т 240РВ403826/1201.03.2014
Т 240РВ403826/1201.03.2014
С 215ВН403825/1404.03.2014
С 215ВН403825/1404.03.2014
С 215ВН403825/1404.03.2014
С 215ВН403825/1404.03.2014
С 215ВН403825/1404.03.2014
С 215ВН403825/1404.03.2014
С 215ВН403825/1404.03.2014
С 215ВН403825/1404.03.2014
С 215ВН403825/1404.03.2014
С 215ВН403825/1404.03.2014
Т 118ЕЕ14/2-206.03.2014
Т 118ЕЕ14/2-206.03.2014
14.02.2015 11:30:28
Андрей, если у вас Excel 2013, то можно использовать сводную таблицу - она в 2013 версии умеет подсчитывать количество уникальных элементов.
Если версия более древняя, то прогнать список сначала через удаление дубликатов (Данные - Удалить дубликаты) с галочками Гос.№ и Дата, а потом построить сводную (Даты закинуть в строки, а Гос№ в значения).
Не задан ID пользователя.

16.02.2015 12:05:54
Николай, спасибо за ответ!
Остался момент: в сводной таблице не получается найти кнопку "Уникальные значения". У меня 2013 эксель.
16.02.2015 14:26:45
Андрей, когда строите сводную, то обязательно установите флажок Добавить в модель данных.
Потом в сводной щелкаете правой по полю значений - Параметры поля - Операции - Число различных элементов.
Не задан ID пользователя.

16.02.2015 16:06:37
Флажок ставлю. А "Кол-ва уникальных" не вижу в настройках поля данных....
16.02.2015 19:08:42
Оно там на вкладке Операции - правильно называется Число различных элементов (Distinct Values)
Не задан ID пользователя.

17.02.2015 09:25:36
Гос. № машины заменил на число - и заработало! а смешанный формат Гос.№  не дает появиться пункту "Число различных элементов". Есть рецепт как решить эту проблему? В смысле - без выборки 3 цифр гос. номера. В случае их уникальности...
Не задан ID пользователя.

18.02.2015 09:25:05
либо....формула?

Гос.№ автоДатаКол-во датКол-во машин
Т 240РВ01.03.20141
Т 240РВ01.03.2014
Т 240РВ01.03.2014
Т 240РВ01.03.20141
С 215ВН04.03.20142
С 215ВН04.03.2014
С 215ВН04.03.2014
С 215ВН04.03.2014
С 2155ВН04.03.2014
С 215ВН04.03.2014
С 215ВН04.03.2014
С 215ВН04.03.2014
С 215ВН04.03.2014
С 215ВН04.03.20141
Т 118ЕЕ06.03.20143
Т 118ЕЕ06.03.2014
Т 118ЕЕ06.03.2014
О 754ТМ06.03.2014
О 754ТМ06.03.2014
О 754ТМ06.03.2014
О 754ТМ06.03.20141
В 093ЕЕ07.03.20144
В 093ЕЕ07.03.2014
В 093ЕЕ07.03.2014
В 093ЕЕ07.03.2014
В 093ЕЕ07.03.2014
В 093ЕЕ07.03.2014
В 093ЕЕ07.03.2014
В 093ЕЕ07.03.2014
В 093ЕЕ07.03.2014
В 093ЕЕ07.03.20141
{=ЕСЛИ(G4<>"";СЧЁТЕСЛИ(ИНДЕКС(A1:A$2;ПОИСКПОЗ("ххх";G1:G$2)):A1;"<>"&A1:A$2);"";)}
Кол-во дат считает верно, а кол-во машин в день - неверно...


{=ЕСЛИ(G4<>"";СЧЁТЕСЛИ(ИНДЕКС(A1:A$2;ПОИСКПОЗ("ххх";G1:G$2)):A1;"<>"&A1:A$2);"")}
Прошу помощи!!!!!!!!!!!!!!!!!!!!!!!!
Не задан ID пользователя.

04.03.2015 14:01:41
Камрады! Хелп!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Не задан ID пользователя.

17.03.2015 08:55:00
Андрей, я бы сделала так: делаем из этой таблицы сводную. В строки ставим поле "Дата" - получаем список всех возможных дат, копируем его на отдельный лист. Добавляем в сводной в строки поле "Гос.№ авто" , обязательно в "макете отчета" - "Повторять все подписи элементов" (работает с 2010-го). И с помощью СЧЁТЕСЛИ подтягиваем к списку дат количество машин. Это вариант без дополнительных условий. Если нужно учесть еще критерии (например, количество машин с отгрузкой не менее определенной суммы) - СЧЁТЕСЛИМН меня всегда выручает.
Не задан ID пользователя.

31.03.2015 14:55:00
Спасибо!!!
Не задан ID пользователя.

29.11.2015 16:02:11
Здравствуйте.

Подскажите пожалуйста, как решить задачку. У меня к сожалению не получается самостоятельно.
Есть несколько строк из 10 чисел.
Нужно посчитать сколько раз одновременно в строку входят 2 или 3 других числа.

Заранее спасибо.
Не задан ID пользователя.

03.12.2015 19:05:29
Здравствуйте, подскажите, есть ли какие-либо ограничения по кол-ву строк или версии Офиса?
У меня на 35 тыс. строк (Офис 2013) не  работает, выдает "0". При этом, ограничив в этом же массиве до 1500 строк, считает корректно :(
Не задан ID пользователя.

30.06.2016 15:14:01
Ребят, а как просчитать значения которые повторяются, чтобы указывалось не общее количество, а порядочное, нп:

Имя          Подсчет
Груша             1
Груша             2
Груша             3
Груша             4

Вместо такого, что выдает формула "счетесли"

Имя          Подсчет
Груша             4
Груша             4
Груша             4
Груша             4
Не задан ID пользователя.

15.07.2016 21:55:31
Нужно у аргумета "диапазон" функции СЧЁТЕСЛИ строку нижней границу указать без доллара. И будет тебе счастье…
Не задан ID пользователя.

15.07.2016 22:06:36
Есть ещё один замечательный способ, как можно посчитать уникальные значения. С помощью функции ЧАСТОТА:

{=СУММ(--(ЧАСТОТА(ПОИСКПОЗ(A2:A10;A2:A10;0);СТРОКА(A2:A10)-СТРОКА(A2)+1)>0))}

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


{=СУММ(--(ЧАСТОТА(ЕСЛИ(B2:B10=E1;ПОИСКПОЗ(A2:A10;A2:A10;0));СТРОКА(A2:A10)-СТРОКА(B2)+1)>0))}
(в ячейке E1 указываем условие с названием магазина)
Не задан ID пользователя.

19.07.2016 19:59:16
В продолжение темы, ещё один вариант подсчёта уникальных значений.

Сначала сортируем диапазон A1:A10 по возрастанию (или по убыванию, не важно). Потом вводим такую формулу массива:
{=СУММ(ЕСЛИ(A2:A10<>A1:A9;1;0))}

В этом варианте преимущество в том, что расчёт происходит очень быстро, даже если в исходнике много тысяч строк.
Не задан ID пользователя.

19.07.2016 20:49:02
С пропуском пустых ячеек и в немассивной форме:
=СУММПРОИЗВ((A2:A10<>A1:A9)*(A2:A10<>"")
Не задан ID пользователя.

29.10.2016 19:54:17
Хотел задать вопрос, но сам разобрался. Всем удачи!