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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• Как извлечь из диапазона уникальные элементы и удалить дубликаты
• Как подсветить дубликаты в списке цветом
• Как сравнить два диапазона на наличие в них дубликатов
• Извлечение уникальных записей из таблицы по заданному столбцу с помощью надстройки PLEX

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

​Смотрите также​2. Вычисление количества​: ,Sanja,я конечно ниче​ IsObject(Conditions(J)) Then rngCond​

1. ​Придумал вариант с​​Казанский​​Start! = Timer​aOld = [a1:a12000]​ макросы и UDF!!!​​: да, коллекции медленнее​​ алгоритму еще добавить​

   ​ уникальных значений...​
​For Each iCell​

   

2. ​ (иначе получим ошибку​ Ctrl + Shift​​ Функция частота использует​​Автор: Антон Андронов​Этот пример показывает, как​​ уникальных числовых и​​ не понял, ну​

   ​ = Conditions(J).Value If​
​ доп.столбцом, однако способ​

   

3. ​: У меня ввод​​On Error Resume​​str = «»​

   

​Казанский​​ чем массив, но​ считываение диапазона в​Alex_ST​ In Диапазон​ деления на 0​ + Ввод.​ результирующее значение рассчитывается​Примечание:​ создать формулу массива,​

​ текстовых значений (не​

• ​ штучка прикольная. это​ IsNumeric(rngCond(I, 1)) Then​​ будет неудобен при​​ формулы в ячейку​ Next​For i =​: Интересно! А функция​
• ​ скорость выявления уникальности​ массив..​: К стати, знатоки​If iCell.Value <>​ в дроби):​Чтобы просмотреть процесс вычисления​ соответствующих текстовых значений.​Мы стараемся как​ которая подсчитает уникальные​
• ​ работает, если есть​ наверное аналог счётеслимн?​
• ​ If Application.Evaluate(Replace(rngCond(I, 1),​ большом количества сетей,​ листа 14,5 с,​​MyArray = [a1:a12000]​​ 1 To UBound(aOld)​ листа как таковая?​

​ ключей очень высока​а в этой​
​ VBA, помогите, плиз…​
​ «» Then .Add​

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

office-guru.ru

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

​ функции по шагам,​​Функция LEN используется для​ можно оперативнее обеспечивать​ значения.​ пустые ячейки)​Sanja​ «,», «.») &​ тогда придется для​ «СЧЁТ_РАЗНЫХ_3» 28,9 с.​For Each a​If InStr(1, str,​Serge​ (подразумеваю, что ключи​ элегантной формуле перебор​Что-то у меня​ iCell.Value, Trim(iCell.Value)​karl311​ выделите ячейку с​ поиска пустых ячеек.​ вас актуальными справочными​Воспользуемся функцией​=SUM(IF(FREQUENCY(MATCH(B2:B10;B2:B10;0);MATCH(B2:B10;B2:B10;0))>0;1))​

​: Можно и так​ Conditions(J + 1))​ каждого создавать доп.столбцы​ Ровно в 2​ In MyArray​ aOld(i, 1)) =​: Ага, а потом​​ там хранятся упорядоченно)​ происходит не один​​ не получается сделать​Next​: Здравствуйте.​ формулой, а затем​​ Пустые ячейки иметь​​ материалами на вашем​COUNTIF​=СУММ (ЕСЛИ (ЧАСТОТА​

​ сказать. Название говорит​ Then arrFlag(Int(J /​Заранее благодарю!​

• ​ раза. Интересно, почему?​Col.Add a, CStr(a)​ 0 Then str​

• ​ выясняется что расчётный​ — в этом​

• ​ раз, а по​ на основе этой​СЧЁТ_РАЗНЫХ = .Count​Нужна небольшая Ваша​ на вкладке​ длину 0 (количество​ языке. Эта страница​(СЧЁТЕСЛИ). Для примера​ (ПОИСКПОЗ (B2:B10;B2:B10;0);ПОИСКПОЗ (B2:B10;B2:B10;0))>0;1))​ само за себя​ 2)) = True​Dmitriy XM​слэн​

• ​Next a​ = str &​ файл не работал​ и выигрыш. Еще​ количеству элементов -​ классной формулы UDF​

• ​End With​ помощь. Уже не​Формулы​ символов).​ переведена автоматически, поэтому​

Пример

​ подсчитаем количество чисел​3. Вычисление количества​ СЧЕТ​ Else If rngCond(I,​:​: не помню с​

​’Если работа с​	​ aOld(i, 1) &​
​ у начальника на​	​ быстрее конструкция dictionarys..​
​ для каждого из​	​ (ну, чтобы потом​
​End Function​	​ первый день ломаю​
	​в группе​
​Скопируйте таблицу в ячейку​	​ ее текст может​
​ 5 в диапазоне​	​ уникальных значений (универсальная​
​УНИК​	​ 1) Like Conditions(J​
​=СЧЁТЕСЛИМН(D2:D16;»Магнит»;E2:E16;»>»&0)​	​ кем мы оттачивали​
​ коллекцией далее неприемлема,​	​ «|»​
​ деловой презентации потому​	​ и удобнее. Но​
​ элементов массива происходит​	​ формулы писАть было​Alex_ST​ голову над этой​Зависимости формул​ A1 на пустом​
​ содержать неточности и​	​A1:A6​ формула)​ЕСЛИМН. В отличии от​ + 1) Then​Олег55​ скорость этого алгоритма​
​ то перебросить её​	​Next i​ что макросы отключены​ требует подключения доп​ перебор всех «остальных​ проще и не​: К стати, kim,​

​ задачкой. Нужно подсчитать​​нажмите кнопку​

• ​ листе в Excel​ грамматические ошибки. Для​, используя следующую формулу:​=SUM(IF(FREQUENCY(IF(LEN(A2:A10)>0;MATCH(A2:A10;A2:A10;0);»»);IF(LEN(A2:A10)>0;MATCH(A2:A10;A2:A10;0);»»))>0;1))​ обычное СЧЕТЕСЛИМН подсчитывает​ arrFlag(Int(J / 2))​: Дмитрий, благодарю, что​ — точно с​ в массив​

• ​aNew = Split(str,​ были.​ библиотеки.​ элементов», сравнение с​ указывать два раза​​ не посоветуете,​​ количество уникальных значений​​Вычисление формулы​​ для работы с​​ нас важно, чтобы​​=COUNTIF(A1:A6,5)​

support.office.com

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

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

​=СУММ (ЕСЛИ (ЧАСТОТА​ количество УНИКАЛЬНЫХ значений​ = True End​ не остались равнодушны​

​ ZVI, а вот​ReDim NewMyArray(1 To​ «|») ‘ последнее​И стоя на​Alex_ST​ условием и увеличение​ диапазон)​а как в​

​ в массиве и​.​

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

​ примерами формул, использующих​ эта статья была​=СЧЁТЕСЛИ(A1:A6;5)​ (ЕСЛИ (ДЛСТР (A2:A10)>0;ПОИСКПОЗ​alex1210​ If Next If​

​ к проблеме! Однако​ еще учавствовал или​ Col.Count)​ значение пустое​ бирже труда макрописец​: По просьбам слушателей​

​ индекса, если условие​Даже без игнорирования​ формуле =СУММПРОИЗВ(1/СЧЁТЕСЛИ(A4:P16;A4:P16)) обойти​ частоту их появления,​Есть диапазон с данными,​ функции.​ вам полезна. Просим​​Чтобы подсчитать уникальные значения,​ ​ (A2:A10;A2:A10;0);»»);ЕСЛИ (ДЛСТР (A2:A10)>0;ПОИСКПОЗ​​: ААААААААААААААААААА, круто​ WorksheetFunction.And(arrFlag) = True​ Ваш вариант считает​ degassad или Anik..​

​For i =​​For i =​​ упрямо повторяет себе:​ во время обеденного​ выполнено.​ пустых ячеек…​ ошибку #ДЕЛ/0! если​

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

​ т.е. значение «7»​ в котором некоторые​Данные​ вас уделить пару​ добавим функцию​ (A2:A10;A2:A10;0);»»))>0;1))​Akropochev​ Then .Add CStr(cl(I,​

​ количество накладных сети​

planetaexcel.ru

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

​ Магнит с бонусом​​ чуточку точнее в​’ NewMyArray(i) =​ — 1​
​ считает-то всё-равно быстрее»…​ мешала) сделал сравнение​

​ говорю о последующем​​Function СЧЁТ_РАЗНЫХ_2(Диапазон As​

​ Col(i)​​Next​: и еще можно​
​ в диапазоне.​Михаил С.​With Application.WorksheetFunction​: =СУММПРОИЗВ(1/СЧЁТЕСЛИ(A4:P16;A4:P16&»»))-1​Заранее благодарен.​ в диапазоне. В​

​563​​ Для удобства также​

​.​​ Enter, а Ctrl​
​Олег55​ 1 Else Err.Clear​ количество уникальных значений​
​ попробовал ещё подсократить​Next​Cells(2, 5) =​ немножко причесать.. :)​
​Конечно, создавать «массив​: ну может вы​СЧЁТ_РАЗНЫХ_2 = .SumProduct(1​

​Alex_ST​​kim​ приведенном выше примере,​
​67​ приводим ссылку на​=SUM(1/COUNTIF(A1:A6,A1:A6))​ + Shift +​: , , большое​ End If End​ по столбцу ИД​ время выполнения СЧЁТ_РАЗНЫХ_2.​Cells(4, 5) =​

​ Timer — Start​​например, зачем в​ из миллиона случайных​ и правы;​

​ / .CountIf(Диапазон, Диапазон))​​: Спасибо.​: Для этого нужна​ как легко заметить,​789​ оригинал (на английском​
​=СУММ(1/СЧЁТЕСЛИ(A1:A6;A1:A6))​ Enter. После этого​ спасибо, что не​ If Next End​ точки для сети​Попытался сначала заменить​ Timer — Start​Cells(2, 6) =​ коллекцию передавать значение,​ чисел в диапазоне​=СУММПРОИЗВ(1/СЧЁТЕСЛИ(A4:P16;A4:P16&»»))-ИЛИ(A4:P16=»»)​

​ мы увидим, что​​ от моей проблемы!​
​Jack Famous​
​ больше нуля. В​ ключе коллекции CStr(tmpArr(i,​

​ Col.Count​​[CalcTime] = Timer​
​ посчитать?​ не стал, т.к.​Alex_ST​выдаёт #ЗНАЧ!​ вдруг появилось?​Для подсчета уникальных:​ варианта.​Дмитрий​
​ узнать, сколько уникальных​.​ формула взята в​ Разобрал принципы работы​: Sanja, какая крутая​
​ данном примере таковых​
​ j)) на tmpArr(i,​
​[CalcTime] = Timer​

​ попробовал.​​ ли будет если​=СУММ(1/(ЕСЛИ(СЧЁТЕСЛИ(A4:P16;A4:P16);СЧЁТЕСЛИ(A4:P16;A4:P16);1)))-СЧЁТЕСЛИ(A4:P16;»»)​
​ теперь вдруг получилось​
​: СЧЁТЕСЛИ​

​ диапазон во временный​​ считаем за уникальные​

​Да и мой​​: kim,​ нет пустых ячеек,​56​
​ значения​ в фигурные скобки​Полосатый жираф алик​ от ситуации​

​ доброго времени, подскажите​​ с бонусом, так​ tmpArr(i, j)​ D​ New Collection​: Не согласен. Для​ 10 000 цифр​ массив, а потом​Alex_ST​ макрос говорит, что​=СУММПРОИЗВ(1/СЧЁТЕСЛИ(A4:P16;A4:P16))- это здОрово!​ то можно использовать​237​

​ таблице (уникальных)?​​Jack Famous​: доп столбец один​

​НО возникли сомнения,​​Set D =​MyArray = [a1:a12000]​ а это мало​ компе получилось следующее:​ его в коллекцию​ (честно говоря, даже​ в макросе :)​
​ раньше, поэтому написАл​ как формулу массива,​Описание (результат)​ и Сергей. Если​ формулу.​
​ видеть формулы, можно​

​ (метод проверки уникальности​​ не проверял), но​Дело в том,​

​ UDF:​​ т.е. нажать после​=СУММ(ЕСЛИ(ЧАСТОТА(A2:A10,A2:A10)>0,1))​ диапазон содержит числа​Пояснение:​ выбрать в формате​: В таблице посчитать​Ну, как я​Sanja​ ЛИ ТИПАМИ ДАННЫХ​MyArray = [a1:a12000]​ In MyArray​ вообще ещё не​ с прямым считыванием​ по Уокенбаху)?​ в вашем примере​ что в таком​
​Function СЧЁТ_РАЗНЫХ(Диапазон As​ ввода формулы не​Подсчет количества уникальных числовых​

​ в массив и​​ по крайней мере​=СУММПРОИЗВ(1/СЧЁТЕСЛИ(A4:P16;A4:P16))​: да, но если​’ Post_Author :​ помощью функции​ который не должен​

​ каждому истинному условию.​​ обозначает: три числа​ в конце ввода​=СУММПРОИЗВ(Ч(СЧЁТЕСЛИ(A1:J10;A1:J10)=1))​ЦитатаОлег55 написал: …количество​ должен иметь хотя-бы​Попытки вместо значения​Next​For Each a​Так что это​ добавлением в коллекцию​

planetaexcel.ru

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

​Alex_ST​​’ Post_URL :​. Если представить это​
​=СУММ(ЕСЛИ(ЧАСТОТА(ЕСЛИ(ДЛСТР(A2:A10)>0,ПОИСКПОЗ(A2:A10,A2:A10,0),»»),ЕСЛИ(ДЛСТР(A2:A10)>0,ПОИСКПОЗ(A2:A10,A2:A10,0),»»))>0,1))​Функция частота позволяет выполнить​ «moon», одно число​Алексей матевосов (alexm)​ одинаковых можно посчитать,​ есть диапазон B2:B16​ состоять из одного​ <> «» Then​
​Cells(5, 6) =​ a​Hugo​СЧЁТ_РАЗНЫХ_2 — 0,094​ выигрыш в скорости​: Вкладывать worksheetfunction’ы так​
​: Макрос считает правильно.​

​’ DateTime :​​ в виде дополнительного​​Подсчет количества уникальных текстовых​

​ подсчет количества уникальных​​ 5, три числа​: =СУММ (1/МУМНОЖ (—(A1:A5&B1:B5=ТРАНСП​ например (36.46-3; 36.41-5;​Олег55​ столбца и иметь​ .Add «», tmpArr(i,​ UBound(NewMyArray) + 1​Cells(i, 2) =​: У меня есть​ сек.​ при использовании массива​ нельзя.​ Проверялось не раз​ 06.04.10, 12:00​ столбца, то выглядело​ и числовых значений​
​ значений. Эта функция​ 7, три числа​ (A1:A5&B1:B5));{1:1:1:1:1}))​ 36.29-2…)?​: От души большое​ равное кол-во строк​

​ j)​​[CalcTime] = Timer​ a​

​ такие цифры при​​3. Макрос, реализующий​ вместо прямого обращения​Function СЧЁТ_РАЗНЫХ_2(Диапазон As​ (в том числе​’ Purpose :​ бы оно так:​ в диапазоне A2:A10​ игнорирует текстовые и​ 7.​Формула массива, ввод​AlexM​ спасибо! оба варианта​ Dim cl() Dim​к ускорению не​ — Start​i = i + 1​ копировании 3000 уникальных​ на VBA функцию​ к ячейкам будет​ Range) As Long​ даже и ручным​ возвращает число уникальных​Потом вычисляются дроби​ без учета пустых​ нулевые значения. Первое​Все это сводится к​ Ctrl+Shift+Enter​: так?​ великолепные — и​ arrFlag() As Boolean​ привели.​End Sub​Next​ из 11000 макросами:​ листа =СУММПРОИЗВ(1/СЧЁТЕСЛИ(Диапазон;Диапазон))​ незначительным…​’=СУММПРОИЗВ(1/СЧЁТЕСЛИ(Диапазон;Диапазон))​ пересчётом).​ значений в указанном​1/Число вхождений​ ячеек и текстовых​ вхождение конкретное значение​ {1/3;1/1;1/1;1/1;1/3;1/3}.​В формуле массив​Помогите написать формулу, которая​ Сергея, и​ Dim I&, J&​Олег55​Sub filter()​Cells(3, 5) =​Degassad 4.171125​СЧЁТ_РАЗНЫХ_3 — 38,22​

​слэн​​СЧЁТ_РАЗНЫХ_2 = Evaluate(«SumProduct(1/CountIf(«​А вот усовершенствованная​​ диапазоне​

​для каждого элемента​​ значений (6)​ она возвращает число​Этот массив констант используется​ единиц длиной в​ высчитывала бы по​

​Очень помогли!​​ Dim rngCond() On​
​: Доброе утро, уважаемые​Start! = Timer​​ Timer — Start​​For Each 1.625004​ сек.​: гораздо быстрее​ & Диапазон.Address &​

​ вами формула даже​​’ Notes :​ и все они​
​Примечания:​ равно количество вхождений​ в качестве аргумента​ количество строк, и​

​ двум столбцам кол-во​​Jack Famous​ Error Resume Next​ знатоки Excel!​Range(«A1:A12000»).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Worksheets(1).Range(«B1»),​
​Cells(3, 6) =​

​For i 2.422002​​Да… Гуру форума,​
​Казанский​ «,» & Диапазон.Address​ в исходном примере​’—————————————————————————————​ суммируются, что и​ ​

​ значения. Для каждого​​ для функции​ если строк много,​ уникальных записей. Пример​: Олег55, доброго дня!​

​ cl = rngU.Value​​Пожалуйста, помогите в​ Unique:=True​ Col.Count​Dictionary 1.530991​​ конечно, обычно правы,​​: Алексей, а протестируй​ & «))»)​ топик-стартера (т.е. без​

​Dim iCell As​​ даст нам количество​

​В формулах в этом​​ случая того же​
​SUM​ например 99, то​
​ на рисунке. Спасибо​

​Предлагаю вариант на​​ With CreateObject(«Scripting.Dictionary») For​ ячейке вычислить количество​[CalcTime] = Timer​[CalcTime] = Timer​AdvFilter 0.280985355​ но чтобы разница​ разные методы и​End Function​ пустых ячеек в​ Range​

planetaexcel.ru

Подсчет уникальных значений (Формулы/Formulas)

​ уникальных элементов:​​ примере должны быть​ значения после первого​(СУММ), давая результат​

​ единицы устанешь писать.​​Supreme council​ связях таблиц между​ I = 1​

​ точек сети Магнит,​​ — Start​

​ — Start​​Сами коды:​ во времени выполнения​ отпишись?​слэн​​ диапазоне) даёт 47,​

​Set Диапазон =​​Если в диапазоне встречаются​ введены как формулы​ эта функция возвращает​ 4.​

​ Выход массив единиц​​: 1. Вычисление количества​

excelworld.ru

Excel: Как посчитать количество уникальных записей?

​ собой. Использованы только​ To UBound(cl) ReDim​ по которым хотя​Cells(6, 5) =​End Sub​

​Sub btnDeggasad_Click()​​ достигала более 400​Для тестов создай​
​: а не надо​
​ когда реально и​ Intersect(Диапазон.Parent.UsedRange, Диапазон)​
​ пустые ячейки, то​ массива. Выделите все​ значение 0.​Урок подготовлен для Вас​ делать формулой​
​ уникальных числовых значений​
​ штатные функции Excel.​ arrFlag(Int(UBound(Conditions) / 2))​
​ бы в одной​ Timer — Start​Sub btnCollections2_Click()​
​Dim aOld, aNew,​
​ раз…!!! Не ожидал.​ массив из миллиона​ этой «элегантности» :)​ моим макросом и​
​On Error Resume​ придется немного усовершенствовать​ ячейки, содержащие формулы,​Функция ПОИСКПОЗ используется для​ командой сайта office-guru.ru​=СУММ (1/МУМНОЖ (—(A1:A5&B1:B5=ТРАНСП​=SUM(IF(FREQUENCY(A2:A10;A2:A10)>0;1))​ Результат на листе​ For J =​ товарной накладной начислен​End Sub​Dim NewMyArray(), MyArray​ i As Long,​Так что, формулисты,​

​ случайных чисел в​​в vba эффективнее​ вашей же исходной​
​ Next​ формулу, добавив проверку​ нажмите клавишу F2​ возврата позиции текстового​Источник: http://www.excel-easy.com/examples/count-unique-values.html​
​ (A1:A5&B1:B5));СТРОКА (A1:ИНДЕКС (A:A;СЧЁТЗ​=СУММ (ЕСЛИ (ЧАСТОТА​ «shop» 3ий столбец​
​ LBound(Conditions) To UBound(Conditions)​ бонус (т.е. бонус​Файл не даю,​Dim Col As​ str As String​ бросайте своё тормозное​ диапазоне 1…200 000.​ будет простым перебором,​

​ формулой там 48​​With New Collection​ на пустые ячейки​
​ и нажмите клавиши​ значения в диапазоне.​
​Перевела: Ольга Гелих​ (A:A)))^0))​ (A2:A10;A2:A10)>0;1))​alex1210​ Step 2 If​ >0).​ сильно секретный :)​ New Collection​
​Start! = Timer​ занятие и пишите​Guest​

​ только к вашему​

Числовые значения

В диапазоне

= СУММ(—(ЧАСТОТА(данные; данные)>0))

Если вам нужно подсчитать уникальные числовые значения в диапазоне, вы можете использовать формулу, которая использует частотную функцию вместе с функцией СУММ.

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

ID номера работников появляются в диапазоне B5: B14. Для того, чтобы получить количество уникальных номеров, вы можете использовать следующую формулу:

= СУММ(— (ЧАСТОТА(B5:B14; B5:B14) > 0))

Функция ЧАСТОТА возвращает массив значений, которые соответствуют интервалам. В этом случае мы сравниваем один и тот же набор чисел для массива данных и для массива интервалов.

Результатом является то, что ЧАСТОТА возвращает массив значений, которые представляют собой счетчик для каждого числового значения в массиве данных. Это работает, потому что ЧАСТОТА имеет специальную функцию, которая автоматически возвращает ноль для любых чисел, которые появляются более чем один раз в массиве данных, поэтому обратный массив выглядит следующим образом:

{3; 0; 0; 2; 0; 3; 0; 0; 2; 0; 0}

Далее, каждое из этих значений проверяется, чтобы было больше нуля. Результат выглядит следующим образом:

{ИСТИНА; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ}

Теперь каждая ИСТИНА в списке представляет собой уникальный номер в списке, и нам просто нужно сложить Истинные значения с СУММ.

Однако СУММ не будет добавлять логические значения в массиве, так что мы должны сначала превратить значения в 1 или ноль. Это делается с помощью двойного минуса (двойной унарный). В результате массив только 1 или 0.

{1; 0; 0; 1; 0; 1; 0; 0; 1; 0; 0}

И, наконец, СУММ добавляет эти значения и рассчитывает общее число, которое в данном случае равно 4.

Примечание: вы можете также использовать СУММПРОИЗВ для сложения элементов в массиве.

Использование СЧЁТЕСЛИ вместо ЧАСТОТА для подсчета уникальных значений

Другой способ подсчета уникальных числовых значений состоит в использовании СЧЁТЕСЛИ вместо ЧАСТОТА. Это гораздо проще, но нужно учитывать, что с помощью СЧЁТЕСЛИ на больших наборах данных для подсчета уникальных значений может вызвать проблемы с производительностью. Формула на основе частотных полос, хотя и более сложная, вычисляет гораздо быстрее.

С критериями

{=СУММ(—(ЧАСТОТА(ЕСЛИ(criteria;значения);значения)>0))}

Если вам нужно подсчитать уникальные числовые значения в диапазоне с критериями, можно использовать формулу, основанную на СУММ и частотной функции, вместе с функцией применять критерии.

Например, предположим, у вас есть список номеров сотрудников, которые записывали часы в двух разных зданиях: здание А и здание Б. Вы хотите знать, сколько уникальных сотрудников регистрирует время в каждом здании. Так как одно и то же число сотрудников более одного раза в списке, то вам нужна формула, которая будет рассчитывать уникальные идентификаторы сотрудника в здании.

В показанном примере, формула в G4 является:

{= СУММ(— (ЧАСТОТА(ЕСЛИ(B5:B14 = «A»; C5:C14); C5:C14)> 0))}

Примечание: это формула массива и должна быть введена с помощью сочетания клавиш Ctrl+Shift+Enter

Функция ЧАСТОТА рассчитывает массив значений, которые соответствуют массиву_интервалов. В этом случае мы подставлем «отфильтрованный» набор идентификаторов для массива данных, а также полный набор идентификаторов для массива_интервалов. Фильтрация осуществляется с помощью функции ЕСЛИ:

ЕСЛИ (B5:B14 = «А»; С5:С14)

Которая в примере рассчитывает это:

{81400; 81405; 81405; 82364; 82364; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ}

Обратите внимание на то, что все идентификаторы не в здании А были преобразованы в ЛОЖЬ. Далее, ЧАСТОТА рассчитывает массив значений, которые представляют собой счетчик для каждого числового значения в массиве данных. Это работает, потому что ЧАСТОТА имеет специальную функцию, которая автоматически возвращает ноль для любых чисел, которые появляются более чем один раз в массиве данных, поэтому обратный массив выглядит следующим образом:

{1; 2; 0; 2; 0; 0; 0; 0; 0; 0; 0}

Далее, каждое из этих значений проверяется, чтобы было больше нуля. Результат выглядит следующим образом:

{ИСТИНА; ИСТИНА; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ}

Каждая ИСТИНА в списке представляет собой уникальный номер в списке, и нам просто нужно сложить Истинные значения с СУММ.

Однако СУММ не будет добавлять логические значения в массиве, так что мы должны сначала превратить значения в 1 или ноль. Это делается с помощью двойного минуса (двойной унарный). В результате массиве только 1 или 0.

{1; 1; 0; 1; 0; 0; 0; 0; 0; 0; 0}

И, наконец, СУММ добавляет эти значения и рассчитывает общее число, которое в данном случае составляет 3.

Текстовые значения

В диапазоне

=СУММПРОИЗВ(—(ЧАСТОТА(ПОИСКПОЗ (данные;данные;0); СТРОКА (данные)- СТРОКА (данные.первая ячейка)+1)>0))

Если вам нужно подсчитать уникальные текстовые значения в диапазоне, вы можете использовать формулу, которая использует несколько функций: ЧАСТОТА, ПОИСКПОЗ, СТРОКА и СУММПРОИЗВ.

Кроме того, можно использовать СЧЁТЕСЛИ, как описано ниже.

Предположим, у вас есть список имен сотрудников вместе с часами работы над проектом, и вы хотите знать, сколько сотрудники работали над этим проектом. Глядя на данные, вы можете увидеть, что имена повторяются, а вы хотите подсчитать уникальные имена.

Имена работников находятся в диапазоне B5:B14. Для того, чтобы получить количество уникальных имен, вы можете использовать следующую формулу:

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

Эта формула является более сложной, чем аналогичная формула, которая использует ЧАСТОТА для подсчета уникальных числовых значений, поскольку ЧАСТОТА не работает с нечисловыми значениями. В результате, большая часть формулы просто преобразует нечисловые данные в числовые данные, которые может обрабатывать ЧАСТОТА.

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

Поскольку ПОИСКПОЗ принимает и массив значений для значения совпадений аргумента, она рассчитывает массив позиций. Они подаются к частотному в аргументе массива данных.

{1; 1; 1; 4; 4; 6; 6; 6; 9; 9}

Аргумент, хранимый в массиве строится из этой части формулы:

СТРОКА(B5:B14) -СТРОКА(B5) +1

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

{1; 2; 3; 4; 5; 6; 7; 8; 9; 10}

Функция ЧАСТОТА возвращает массив значений, которые соответствуют «хранимым». В этом случае мы подставляем массив, рассчитываемый ПОИСКПОЗ для массива данных, а также рассчитываемый массив построчного массива выше, как массив хранимых.

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

{3; 0; 0; 2; 0; 3; 0; 0; 2; 0; 0}

Далее, каждое из этих значений преобразуется в значение ИСТИНА или ЛОЖЬ, которые > 0, а затем в 1 или ноль с двойным отрицанием (двойной минус). Это делается потому что СУММПРОИЗВ нужны числовые значения, она не может работать непосредственно с текстом или логическими значениями.

Внутри функции СУММПРОИЗВ конечный массив выглядит следующим образом:

{1; 0; 0; 1; 0; 1; 0; 0; 1; 0; 0}

И, наконец, СУММПРОИЗВ просто добавляет эти значения и рассчитывает общее число, которое в данном случае равно 4.

Обработка пустых ячеек в диапазоне

Если какая-либо из ячеек в диапазоне пустая, и вы хотите использовать ЧАСТОТА вместо СЧЁТЕСЛИ, вам необходимо использовать более сложную формулу массива, которая включает в себя ЕСЛИ:

{= СУММ(ЕСЛИ(ЧАСТОТА(ЕСЛИ(данные <> «»;ПОИСКПОЗ(данные; данные; 0));СТРОКА(данные) -СТРОКА(данные.первая ячейка) +1); 1))}

Примечание: поскольку логическая проверка в операторе ЕСЛИ содержит массив, то формула автоматически становится формулой массива, которая требует Ctrl+Shift+Enter. Поэтому СУММПРОИЗВ был заменен СУММ.

Рассмотрим формулу изнутри, ЕСЛИ требуется, потому что ПОИСКПОЗ рассчитает # N / A, если значение совпадений содержит пустые значения. Тестируя для пустых значений с данными <> «», в том числе и ПОИСКПОЗ как значение, если оно истинно, то результирующий массив будет содержать номера в сочетании с ЛОЖЬ:

{1; 1; ЛОЖЬ; 4; 4; 6; 6; ЛОЖЬ; 9; 9}

которая подается ЧАСТОТА, что и массив данных. ЧАСТОТА будет рассчитывать массив вроде этого:

{2; 0; 0; 2; 0; 2; 0; 0; 2; 0; 0}

Элементы в этом массиве преобразуются либо в 1, или ЛОЖЬ с окончательным (внешним) ЕСЛИ заявлением. Результат выглядит следующим образом:

{1; ЛОЖЬ; ЛОЖЬ; 1; ЛОЖЬ; 1; ЛОЖЬ; ЛОЖЬ; 1; ЛОЖЬ; ЛОЖЬ}

СУММ затем складывает 1 и рассчитывает 4.

Эта формула из замечательной книги Майка Гивина по формулам массива.

Использование СЧЁТЕСЛИ вместо ЧАСТОТЫ для подсчета уникальных значений

Другой способ подсчета уникальных числовых значений состоит в использовании СЧЁТЕСЛИ вместо ЧАСТОТЫ. Эта формула гораздо проще, но нужно учитывать, что использование СЧЁТЕСЛИ на больших наборах данных для подсчета уникальных значений, может вызвать проблемы с производительностью. Формула на основе ЧАСТОТА, хоть и более сложная, но вычисляет гораздо быстрее.

С критериями

{=СУММ(—(ЧАСТОТА(ЕСЛИ(критерий;ПОИСКПОЗ(значение; значение;0));СТРОКА(значение)-СТРОКА(значение.первая ячейкаl)+1)>0))}

Для уникальных значений в диапазоне с критериями, вы можете использовать формулу массива, основанную на функции ЧАСТОТА.

Предположим, у вас есть список имен сотрудников вместе с часами работы над проектом, и вы хотите знать, сколько сотрудники работали над этим проектом. Глядя на данные, вы можете увидеть, что имена повторяются, а вы хотите подсчитать уникальные имена.

В показанном примере формула в G5 является:

= {СУММ(—(ЧАСТОТА(ЕСЛИ(C5:C11 = G4;СТРОКА(B5:B11; B5:B11;0));СТРОКА(B5:B11) — СТРОКА(B5)+1) > 0))}

Эта формула использует ЧАСТОТА для подсчета уникальных числовых значений, которые получены с помощью функции ПОИСКПОЗ, которая сравнивает все значения против самих себя, чтобы определить позицию.

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

С помощью ЕСЛИ ПОИСКПОЗ рассчитывает только для строк, которые соответствуют критериям.

В конце концов, массив позиций, генерируемых ПОИСКПОЗ подаются к частотному в аргументе массива данных.

Аргумент, хранимый в массиве, строится из этой части формулы:

СТРОКА(B5:B11) -СТРОКА(B5) +1

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

{1; 2; 3; 4; 5; 6; 7; 8; 9; 10}

Функция ЧАСТОТА рассчитывает массив значений, которые соответствуют «хранимым». В этом случае мы подставляем один и тот же набор чисел, как для массива данных и хранимых для массива.

Результатом является то, что ЧАСТОТА рассчитывает массив значений, которые указывают на количество, которое появляется каждое значение в массиве данных. Это работает, потому что ЧАСТОТА запрограммирована так, чтобы рассчитывать ноль для любых чисел, которые появляются более чем один раз в массиве данных.

Далее, каждое из этих значений преобразуется в значение ИСТИНА или ЛОЖЬ, которые > 0, а затем в 1 или ноль с двойным отрицанием (двойной минус). Это сделано, чтобы превратить все ненулевые значения в 1.

Наконец, СУММПРОИЗВ просто складывает эти значения и рассчитывает общее число.

Примечание: это формула массива и должна быть введена с помощью Ctrl + Shift + Enter.

Обработка пустых ячеек в диапазоне

Если какая-либо из ячеек в диапазоне пустая, вам необходимо скорректировать формулу, добавив дополнительно ЕСЛИ для предотвращения пустых клеток, передающуюся в функцию ПОИСКПОЗ, которая сгенерирует сообщение об ошибке. Формула:

{= СУММ(— (ЧАСТОТА(ЕСЛИ(B5:B11 <> «»; ЕСЛИ(C5:C11 = G4; ПОИСКПОЗ(B5:B11; B5:B11;0))); СТРОКА(B5:B11) -СТРОКА(B5) +1)> 0))}

С двумя критериями

Если у вас есть два критерия, вы можете расширить логику формулы путем добавления другого вложенного ЕСЛИ:

= {СУММ(— (ЧАСТОТА(ЕСЛИ(c1; ЕСЛИ(c2; ПОИСКПОЗ (значения;значения;0))); СТРОКА (значения) — СТРОКА (значения.первая позиция) +1)> 0))}

Там, где c1 = критерий1, c2 = критерий2 и значения = диапазон значений.

Взято из замечательной книги Майка Гивина по формулам массива, Control-Shift-Enter.

В диапазоне с СЧЁТЕСЛИ

=СУММПРОИЗВ(1/СЧЁТЕСЛИ(данные;данные))

Если вам нужно подсчитать количество уникальных значений в диапазоне ячеек, вы можете использовать формулу, которая использует СЧЁТЕСЛИ и СУММПРОИЗВ.

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

{3; 3; 3; 2; 2; 3; 3; 3; 2; 2}.

После того, как СЧЁТЕСЛИ заканчивает, результаты используются в качестве делителя, 1 в качестве числителя. Значения, которые появляются в данных, после появляются в массиве как 1, но значения, которые появляются несколько раз будут отображаться как дробные значения, которые соответствуют множеству. (Т.е. значение, которое появляется в 5 раз, данные будут генерировать 5 элементов в массиве со значением 1/5 = 0,2).

Наконец, функция СУММПРОИЗВ суммирует все значения в массиве и рассчитывает результат.

Обработка пустых ячеек

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

=СУММПРОИЗВ(1/СЧЁТЕСЛИ(данные; данные&»»))

Данные &»» выражение предотвращает нули массива, созданного СЧЁТЕСЛИ, когда имеются пустые ячейки данных. Она делает это путем обеспечения того, что критерии для пустой ячейки «», а не ноль. Это важно, так как ноль в делителе выбросит #ДЕЛ/0. Так что эта версия формулы не будет выдавать ошибку, когда есть пустые ячейки, но она будет включать в себя пустые ячейки в счете. Если вы хотите исключить пустые ячейки в счете, используйте:

=СУММПРОИЗВ((данные <> «»)/СЧЁТЕСЛИ(данные;данные и «»))

Низкая производительность?

Это крутая и элегантная формула, но она вычисляет гораздо медленнее, чем формулы, которые используют ЧАСТОТА для подсчета уникальных значений. Для больших наборов данных, вы можете переключиться на формулу, основанную на частотную функцию.

Если возникла задача посчитать количество уникальных значений в Экселе (Excel), то её можно решить несколькими способами — с формулами и без формул.

1) В том случае, если диапазон данных (числовых или текстовых) является статичным и меняться не будет, то можно воспользоваться командой «Удалить дубликаты».

Для этого выделяем диапазон (в нашем примере — это B1:B14) и на панели инструментов Excel выбираем «Данные» -> «Удалить дубликаты».

Появится окно, в котором указываются столбец / диапазон, из которых удаляются дубликаты.

Нужно, чтобы обязательно стояла галочка «Мои данные содержат заголовки» — иначе заголовок тоже будет учитываться при отборе уникальных значений.

Теперь нажимаем на кнопку «ОК».

В результате Эксель выведет сообщение о том, сколько найдено дубликатов и сколько осталось уникальных значений.

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

---

2) Для определения количества уникальных значений в Excel можно использовать специальную формулу, состоящую из 2 функций:

СУММ

СЧЁТЕСЛИ

Функция СЧЁТЕСЛИ будет использоваться для того, чтобы посчитать, сколько раз встречается то или иное значение в диапазоне.

Далее с помощью функции СУММ суммируются дроби вида:

1 / Число вхождений каждого элемента.

Для нашего примера формула будет выглядеть так:

{=СУММ(1/СЧЁТЕСЛИ(B2:B14;B2:B14))}

При этом вводить её нужно как формулу массива — через комбинацию Ctrl + Shift + Enter.

В результате получится всё те же 10 уникальных значений.

А вот примеры работы этой формулы, если в таблице Excel имеются текстовые данные: