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

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

Если у вас такая версия, то выберите данные для создания сводной таблицы при создании таблицы, убедитесь, что опция 'флажок добавить эти данные в модель данных (см. ниже).

затем, когда откроется сводная таблица, создайте свои строки, столбцы и значения обычно. Затем щелкните поле, для которого требуется вычислить количество различных значений, и измените настройки значения поля:

наконец, прокрутите вниз до самого последнего варианта и выберите " Distinct Count.'

Это должно обновить значения сводной таблицы, чтобы показать данные, которые вы ищете.

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

ABC   123  
ABC   123  
ABC   123   
DEF   456  
DEF   567  
DEF   456  
DEF   456

и хотите, чтобы он выглядел так:

ABC   1  
DEF   2

но что-то вроде:

ABC   123  
ABC   123  
ABC   123  
ABC   456  
DEF   123  
DEF   456  
DEF   567  
DEF   456  
DEF   456

и хотел, чтобы он выглядел так:

ABC  
   123    3  
   456    1  
DEF  
   123    1  
   456    3  
   567    1

Я нашел лучший способ получить мои данные в этом формате, а затем быть в состоянии манипулировать им дальше было с помощью следующего:

как только вы выберете "Running total in", выберите заголовок для вторичного набора данных (в этом случае это будет заголовок или заголовок столбца набора данных, который включает 123, 456 и 567). Это даст вам максимальное значение с общим количеством элементов в этом наборе, в вашем основном наборе данных.

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

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

возможность сделать "отдельный счетчик" является частью Excel 2013, но не включается автоматически.

Итак, если вы запустите копию EXCEL 2013, вот блестящий способ решить эту проблему без хлопот прохождения функции: http://datapigtechnologies.com/blog/index.php/distinct-count-in-pivot-tables-finally-in-excel-2013/

смотрите Дебра Далглиш Количество Уникальных Элементов

Я нашел самый простой подход, чтобы использовать под Value Field Settings (левый выберите поле ). Опция для Distinct Count в самом низу списка.

вот перед (TOP; normal Count) и после (снизу;Distinct Count)

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

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

=IF(COUNTIF($B:$B1,B1)>1,0,COUNTIF($B:$B1,B1))

скопировать эту формулу на все строки и последняя строка содержит:

=IF(COUNTIF($B:$B7,B7)>1,0,COUNTIF($B:$B7,B7))

это приводит к тому, что 1 возвращается при первом обнаружении записи и 0 для всех последующих раз.

просто просуммировать столбец в сводной таблице

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

1. (сначала сделайте копию своих данных)
2. объединить столбцы
3. удалить дубликаты в объединенном столбце
4. последний поворот на результирующем наборе

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

ответ Сиддхарта потрясающий.

, этот метод может вызвать проблемы при работе с большим набором данных (мой компьютер застыл на 50 000 строк). Некоторые менее процессороемкие методы:

одиночная проверка уникальности

1. сортировка по двум столбцам (A, B в данном примере)

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

   =IF(SUMPRODUCT(($A2:$A3=A2)*($B2:$B3=B2))>1,0,1) 
   

несколько проверка уникальности

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

вместо

1. сортировка по одному столбцу (A)

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

   =IF(SUMPRODUCT(($A2:$A49=A2)*($B2:$B49=B2))>1,0,1)
   

Excel 2013 может делать подсчет различных в поворотах. Если нет доступа к 2013 году, и это меньший объем данных, я делаю две копии необработанных данных, а в копии b выберите оба столбца и удалите дубликаты. Затем сделайте разворот и подсчитайте свой столбец b.

вы можете использовать COUNTIFS для нескольких критериев,

=1 / COUNTIFS (A:A,A2,B:B,B2), а затем перетащите вниз. Вы можете поместить туда столько критериев, сколько хотите, но это, как правило, занимает много времени для обработки.

Шаг 1. добавить столбец

Шаг 2. использовать формулу =IF(COUNTIF(C2:$C10,C2)>1,0,1) в 1-й записи

Шаг 3. перетащите его на все записи

Шаг 4. фильтр '1' в столбце с формулой

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

Я имею в виду, cell C1 должно быть 1. Ячейка C2 должна содержать формулу =IF(COUNTIF($A:$A1,$A2)*COUNTIF($B:$B1,$B2)>0,0,1). Скопируйте эту формулу вниз, так что клетки C3 содержит =IF(COUNTIF($A:$A2,$A3)*COUNTIF($B:$B2,$B3)>0,0,1) и так далее.

если у вас есть ячейка заголовка, вы хотите переместить их все вниз по строке и ваш C3 формула должна быть =IF(COUNTIF($A:$A2,$A3)*COUNTIF($B:$B2,$B3)>0,0,1).

если данные отсортированы.. я предлагаю использовать следующую формулу

=IF(OR(A2<>A3,B2<>B3),1,0)

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

Я обычно сортирую данные по полю, которое мне нужно сделать, чтобы сделать отличное количество, а затем использовать IF (A2=A1,0,1); Вы получаете затем получить 1 в верхней строке каждой группы идентификаторов. Простой и не занимает много времени для расчета на больших наборах данных.

вы можете использовать для вспомогательного столбца также VLOOKUP. Я проверил и выглядит немного быстрее, чем COUNTIF.

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

=IFERROR(IF(VLOOKUP(A2;$A:A1;1;0)=A2;0;1);1)

Я нашел более простой способ сделать это. Ссылаясь на пример Siddarth Rout, если я хочу подсчитать уникальные значения в столбце A:

• добавить новый столбец C и заполнить C2 Формулой " =1 / COUNTIF ($A:$A, A2)"
• перетащите формулу вниз до конца столбца
• pivot со столбцом A в качестве метки строки и Sum{column C) в значениях, чтобы получить количество уникальных значений в столбце a