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

Ответ 1

Чтобы подсчитать количество различных значений в A2: A100 (не считая пробелов):

=SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100,A2:A100&""))

Скопировано из ответа @Ulli Schmid на Что это за формула COUNTIF()?:

=SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))

Подсчитывает уникальные ячейки в пределах A1: A100, исключая пустые ячейки и пустые строки ("").

Как это делается? Пример:

A1:A100 = [1, 1, 2, "apple", "peach", "apple", "", "", -, -, -, ...]
then:
A1:A100&"" = ["1", "1", "2", "apple", "peach", "apple", "", "", "", "", "", ...]

поэтому этот & " необходим, чтобы превратить пустые ячейки (-) в пустые строки (" "). Если вы должны были подсчитывать напрямую, используя пустые ячейки, COUNTIF() возвращает 0. Используя трюк, оба значения" " и - считаются одинаковыми:

COUNTIF(A1:A100,A1:A100) = [2, 2, 1, 2, 1, 2, 94, 94, 0, 0, 0, ...]
but:
COUNTIF(A1:A100,A1:A100&"") = [2, 2, 1, 2, 1, 2, 94, 94, 94, 94, 94, ...]

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

(A1:A100<>""), which is [1, 1, 1, 1, 1, 1, 0, 0, 0, 0, 0, ...]

нашим промежуточным результатом, COUNTIF (A1: A100, A1: A100 & "") и суммируем по значениям.

SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))  
= (1/2 + 1/2 + 1/1 + 1/2 + 1/1 + 1/2 + 0/94 + 0/94 + 0/94 + 0/94 + 0/94 + ...)
= 4

Если бы мы использовали COUNTIF(A1:A100,A1:A100) вместо COUNTIF(A1:A100,A1:A100&""), то некоторые из этих 0/94 были бы 0/0. Поскольку деление на ноль не разрешено, мы бы допустили ошибку.

Ответ 2

try - =SUM(IF(FREQUENCY(MATCH(COLUMNRANGE,COLUMNRANGE,0),MATCH(COLUMNRANGE,COLUMNRANGE,0))>0,1))

где COLUMNRANGE= диапазон, в котором у вас есть эти значения.

например. - =SUM(IF(FREQUENCY(MATCH(C12:C26,C12:C26,0),MATCH(C12:C26,C12:C26,0))>0,1))

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

Ответ 3

Вот еще один быстрый способ получить уникальный счетчик значений, а также получить уникальные значения. Скопируйте столбец, который вам нужен, на другой рабочий лист, затем выберите весь столбец. Нажмите "Данные" → "Удалить дубликаты" → "ОК". Это удаляет все повторяющиеся значения.

Ответ 4

Здесь представлена ​​элегантная формула массива (которую я нашел здесь http://www.excel-easy.com/examples/count-unique-values.html), которая делает трюк красиво:

Тип

= СУММ (1/СЧЕТЕСЛИ (Список, Список))

и подтвердите с помощью CTRL-SHIFT-ENTER

Ответ 5

Считать уникальным с условием. Col A - это идентификатор и использование условия ID=32, Col B - это имя, и мы пытаемся подсчитать уникальные имена для определенного идентификатора

=SUMPRODUCT((B2:B12<>"")*(A2:A12=32)/COUNTIF(B2:B12,B2:B12))

Ответ 6

Еще один сложный способ, который мне только что пригодился (проверено, и это сработало!).

• Выберите данные в столбце
• В меню выберите Conditional Formatting, Highlight Cells, Duplicate Values
• Выберите, хотите ли вы выделить уникальные или повторяющиеся значения.
• Сохранить выделение
• Выберите данные
• Перейдите к Data, а затем Filter

Фильтр по цвету:

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

Ответ 7

Вы можете выполнить следующие действия:

• Сначала выделите столбец (вставив пустой столбец до и/или после столбца, который вы хотите подсчитать уникальные значения, если есть соседние столбцы;

• Затем выберите весь столбец, перейдите в "Данные" > "Расширенный фильтр" и установите флажок "Только уникальные записи". Это скроет все не уникальные записи, чтобы вы могли подсчитать уникальные, выбрав весь столбец.

Ответ 8

Если вы используете Mac

• выделить колонку
• Копия
• открыть терминал .app
• type pbpaste|sort -u|wc -l

Пользователи Linux заменяют pbpaste на xclip xsel или аналогичные

Пользователи Windows, возможно, но возьмут некоторые скрипты... начните с http://brianreiter.org/2010/09/03/copy-and-paste-with-clipboard-from-powershell/

Ответ 9

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

=IF(COUNTIF($A$2:A2,A2)>1,0,1)

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

Ответ 10

Я использую таблицу с заголовками в строке 1, данные находятся в строках 2 и ниже.

Идентификаторы находятся в столбце A. Чтобы подсчитать количество различных значений, я поместил эту формулу из строки 2 в конец электронной таблицы первого доступного столбца [F в моем случае]: "=IF(A2=A1,F1+1,1)".

Затем я использую следующую формулу в свободной ячейке: "=COUNTIF(F:F,1)". Таким образом, я уверен, что каждый идентификатор подсчитан.

Обратите внимание, что идентификаторы должны быть отсортированы, иначе они будут считаться более одного раза... но в отличие от формул массива это очень быстро даже при таблице 150000.

Ответ 11

С помощью формул динамического массива (на данный момент эта публикация доступна только для инсайдеров Office 365):

=COUNTA(UNIQUE(A:A))

Ответ 12

Мой набор данных - D3: D786, Заголовки столбцов в D2, функция в D1. Формула будет игнорировать пустые значения.

= СУММ (ЕСЛИ (частоты (ПЧ (субтотальная (3, OFFSET (D3, ROW (D3: D786) -строка (D3), 1)), ЕСЛИ (D3: D786 < > ", MATCH (" ~ "& D3: D786, D3: D786 &" ", 0))), СТРОКА (D3: D786) -строка (D3) + 1), 1))

При вводе формулы CTRL + SHIFT + ВВОД

Я нашел это на сайте ниже, там больше объяснений о Excel, который я не понял, если вы в этом верите.

http://www.mrexcel.com/forum/excel-questions/553903-count-unique-values-filtered-column.html#post2735467

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