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

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

Например, у меня есть следующее:

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

То, что я хочу, это сводная таблица, которая показывает мне следующее:

 ABC 1 DEF 2 

Простая сводная таблица, которую я создаю, дает мне это (количество строк):

 ABC 3 DEF 4 

Но вместо этого я хочу количество уникальных значений.

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

Я уверен, что есть более простой способ сделать это, но я подумал, что я бы поставил сводную таблицу …

    16 Solutions collect form web for “Простая сводная таблица для подсчета уникальных значений”

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

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

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

    введите описание изображения здесь

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

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

    Отметьте поле рядом с надписью «Добавить эти данные в модель данных».

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

    Наконец, прокрутите список до самого последнего и выберите «Distinct Count». Выберите опцию «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, вот shiny способ решить эту проблему без хлопот по выполнению функции: http://datapigtechnologies.com/blog/index.php/distinct-count-in-pivot-tables- наконец , в-первенствует-2013 /

    Смотрите уникальные предметы Дебра Далглиша

    введите описание изображения здесь

    Нет необходимости сортировать таблицу для следующей формулы, чтобы вернуть 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 для всех времен после этого.

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

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

    Расположение, где нажать

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

    COUNT

    DISTINCT COUNT

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

    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 может делать Count в разных центрах. Если нет доступа к 2013 году, и это меньший объем данных, я делаю две копии необработанных данных, а в копии b выбираю оба столбца и удаляю дубликаты. Затем сделайте опорный стержень и подсчитайте свою колонку b.

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

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

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

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

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

    Шаг 4. Фильтр «1» в колонке с формулой

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

    Я имею в виду, что ячейка 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) .

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

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

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

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

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

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

    Быстрый набор клавиш VMware Workstation?

    Можно ли использовать AppsKey или другие ключи в качестве модификатора

    Windows: Как удалить ACL файла и просто наследовать ACL в содержащем каталоге?

    Что такое группа методов в C #?

    Как я могу скрыть часть заголовка в документе, но показать его в содержимом?

    Правильный способ переопределить Equals () и GetHashCode ()

    Мягкие / жесткие ограничения в Z3

    Имеет ли доступ к MS Access 2003 и 2007 на одном компьютере?

    Настройка локальной сети удаленного доступа за несколькими маршрутизаторами?

    Ограничение использования ОЗУ в Chrome?

    Как работает форматирование жесткого диска?

    Мой компьютер не перейдет в режим ожидания или спящий режим

    Microsoft Word 2010 сбой при открытии второго документа Word, пока он еще открыт

    Как получить звуковой сигнал?

    В Windows 7 dir или tree не могут отображать символы Unicode, даже начинать cmd с cmd / U

    Давайте будем гением компьютера.