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

Ответ 1

Вставьте третий столбец и в ячейке C2 вставьте эту формулу

=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1)

и скопируйте его. Теперь создайте свой стержень на основе 1-го и 3-го столбцов. См. Снимок

Ответ 2

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

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

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

Наконец, прокрутите страницу до последней опции и выберите "Distinct Count".

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

Ответ 3

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

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

Ответ 4

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

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

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

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

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

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

Ответ 5

См. Debra Dalgleish Count Unique Items

Ответ 6

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

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

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

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

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

Это приводит к возврату 1 при первом обнаружении записи и 0 для всех времен позже.

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

Ответ 7

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

Вот предыдущий (TOP; нормальный Count) и после (BOTTOM; Distinct Count)

Ответ 8

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

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

Проверка единственной уникальности

• Сортировка по двум столбцам (A, B в этом примере)

• Используйте формулу, в которой меньше данных

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

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

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

Вместо этого

• Сортировка одного столбца (A)

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

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

Ответ 9

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

Ответ 10

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

• (Сначала сделайте копию своих данных)
• Конкатенация столбцов
• Удалить дубликаты в конкатенированном столбце
• Последний - поворот на результирующем наборе

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

Ответ 11

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

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

Ответ 12

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

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

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

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

Ответ 13

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

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

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

Ответ 14

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

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

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

Ответ 15

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

Ответ 16

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

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