7 способов подсчета уникальных значений в excel

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

И сейчас…

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

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

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

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

данные.xlsx

Расширенный фильтр для получения количества уникальных значений

Использование расширенного фильтра — один из самых простых способов проверить количество уникальных значений, при этом вам даже не понадобятся сложные формулы. Здесь у нас есть список имен и из этого списка нужно посчитать количество уникальных имен.

Вот шаги для получения уникальных значений:

1. Сначала выберите одну из ячеек в списке.
   
2. После этого перейдите на вкладку «Данные» ➜ «Сортировка и фильтр» ➜ нажмите «Дополнительно» .
   
3. Нажав на нее, вы увидите всплывающее окно для применения расширенных фильтров.
4. Теперь в этом окне выберите « Копировать в другое место ».
5. В поле «Копировать в» выберите пустую ячейку , в которую вы хотите вставить уникальные значения.
6. Теперь установите флажок « Только отдельные записи » и нажмите «ОК».
   
7. На этом этапе у вас есть список уникальных значений .
   
8. Теперь перейдите в ячейку ниже последней ячейки в списке, вставьте следующую формулу и нажмите Enter.

 =COUNTA(B2:B10)

Он вернет количество уникальных значений в этом списке имен.

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

Объединение СУММ и СЧЕТЕСЛИ для подсчета уникальных значений.

Если вы хотите найти количество уникальных значений в одной ячейке, не извлекая отдельный список, вы можете использовать комбинацию СУММА и СЧЁТЕСЛИ.

В этом методе вам просто нужно обратиться к списку значений и формула вернет количество уникальных значений. Это формула массива, поэтому вводить ее нужно в виде таблицы и при вводе использовать Ctrl+Shift+Enter.

И формула:

 =SUM(1/COUNTIF(A2:A17,A2:A17))

Когда вы вводите эту формулу в табличной форме, она будет выглядеть так.

 {=SUM(1/COUNTIF(A2:A17,A2:A17))}

Как это работает

Чтобы понять эту формулу, вам нужно разбить ее на три части и просто помнить, что мы ввели эту формулу в табличной форме и всего в этом списке 16 значений, не уникальных, а суммарных.

Хорошо, так что смотри.

В первой части вы использовали COUNIF для подсчета количества каждого значения, начиная с 16, а здесь COUNTIF возвращает значения, как показано ниже.

Во второй части вы делите все значения на 1, что возвращает такое значение.

Допустим, если значение присутствует в списке дважды, оно вернет 0,5 для обоих значений, так что в конце, когда вы его суммируете, оно станет 1, а если значение присутствует три раза, оно вернет 0,333 для каждого.

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

Эта формула довольно мощная и может помочь вам получить количество в одной ячейке.

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

В последнем методе вы использовали методы SUM и COUNTIF. Но вы также можете использовать СУММПРОИЗВ вместо СУММ.

А при использовании СУММПРОИЗВ вам не нужно вводить формулу в табличной форме. Просто отредактируйте ячейку и введите формулу ниже.

 =SUMPRODUCT(1/COUNTIF(A2:A17,A2:A17))

Когда вы вводите эту формулу в табличной форме, она будет выглядеть так.

 {=SUMPRODUCT(1/COUNTIF(A2:A17,A2:A17))}

Как это работает

Эта формула работает точно так же, как вы узнали из метода, описанного выше, с той лишь разницей, что вы использовали СУММПРОИЗВ вместо СУММ.

А СУММПРОИЗВ может принимать массив без использования Ctrl+Shift+Enter.

Считайте только уникальные текстовые значения из списка

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

 =SUM(IF(ISTEXT(A2:A17),1/COUNTIF(A2:A17, A2:A17),””))

И когда вы вводите эту формулу в табличной форме.

 {=SUM(IF(ISTEXT(A2:A17),1/COUNTIF(A2:A17, A2:A17),””))}

Как это работает

В этом методе вы использовали функции ЕСЛИ и ISTEXT. ISTEXT сначала проверяет, являются ли все значения текстовыми, и возвращает TRUE, если какое-либо значение является текстовым.

После этого IF применяет COUNTIF ко всем текстовым значениям, где у вас есть TRUE, а другие значения остаются пустыми.

И в конце SUM возвращает сумму всех уникальных текстовых значений, и таким образом вы получаете количество уникальных текстовых значений.

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

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

 =SUM(IF(ISNUMBER(A2:A17),1/COUNTIF(A2:A17, A2:A17),””))

Введите эту формулу в виде таблицы.

 {=SUM(IF(ISNUMBER(A2:A17),1/COUNTIF(A2:A17, A2:A17),””))}

Как это работает

В этом методе вы использовали функции ЕСЛИ и ISNUM. ISNUMBER сначала проверяет, являются ли все значения числовыми или нет, и возвращает TRUE, если значение является числом.

После этого IF применяет COUNTIF ко всем числовым значениям, где у вас есть TRUE, а другие значения остаются пустыми.

И в конце SUM возвращает сумму всех уникальных значений, которые являются числами, и таким образом вы получаете количество уникальных чисел.

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

Здесь у меня есть VBA (UDF) , который поможет вам подсчитать уникальные значения без использования каких-либо формул.

 Function CountUnique(ListRange As Range) As Integer Dim CellValue As Variant Dim UniqueValues As New Collection Application.Volatile On Error Resume Next For Each CellValue In ListRange UniqueValues.Add CellValue, CStr(CellValue) ' add the unique item Next CountUnique = UniqueValues.Count End Function

Введите эту функцию в свой VBE, вставив новый модуль, затем перейдите в электронную таблицу и вставьте следующую формулу.

 =CountUnique(range)

Заключение

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

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

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

Если вы спросите меня, расширенный фильтр и СУММПРОИЗВ — мои любимые методы, но теперь вы должны мне сказать:

Какой из них твой любимый?

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