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

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

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

Значения 5, 6, 7 и 6, результатом являются три уникальных значения : 5, 6 и 7.

Значения «Брэнли», «Дойл», «Дойл», «Дойл» — это два уникальных значения: «Андрей» и «Дойл».

Существует несколько способов подсчета уникальных значений среди дубликатов.

В диалоговом окне Расширенный фильтр можно извлечь уникальные значения из столбца данных и ввести их в новое место. Затем с помощью функции ЧСТРОК можно подсчитать количество элементов в новом диапазоне.

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

Убедитесь, что диапазон ячеек имеет заголовок столбца.

На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно.

Появится диалоговое окно Расширенный фильтр.

Нажмите кнопку Копировать в другое место.

В поле Копировать в введите ссылку на ячейку.

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

Выберите поле Уникальные записи и нажмите кнопку ОК.

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

В пустой ячейке под последней ячейкой диапазона введите функцию СТРОКИ. Используйте диапазон уникальных значений, скопированные в качестве аргумента, исключая заголовок столбца. Например, если диапазон уникальных значений — B2:B45, введите =СТРОКИ(B2:B45).

Для этой задачи используйте сочетание функций ЕСЛИ,СУММ,ЧАСТОТА,НАЙТИИ LEN:

Назначьте значение 1 каждому из истинных условий с помощью функции ЕСЛИ.

Сложить итог с помощью функции СУММ.

Подсчет количества уникальных значений с помощью функции ЧАСТОТА. Функция ЧАСТОТА игнорирует текст и нулевые значения. Для первого вхождения определенного значения эта функция возвращает число, равное количеству его вхождений. Для каждого вхождения с одинаковым значением после первого функция возвращает ноль.

Возвращает положение текстового значения в диапазоне с помощью функции MATCH. Возвращаемая величина затем используется в качестве аргумента функции ЧАСТОТА для оценки соответствующих текстовых значений.

Находите пустые ячейки с помощью функции LEN. Пустые ячейки имеют длину 0.

Формулы, приведенные в этом примере, должны быть введены как формулы массива. Если у вас установлена текущая версия Microsoft 365, можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.

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

Функция ЧАСТОТА вычисляет частоту ветвей значений в диапазоне значений и возвращает вертикальный массив чисел. Например, с помощью частоты можно подсчитать количество результатов тестирования, которые попадают в диапазоны оценок. Так как эта функция возвращает массив, она должна быть введена как формула массива.

Функция ПОИСК ПОИСК ПО ищет указанный элемент в диапазоне ячеек, а затем возвращает его относительную позицию в диапазоне. Например, если диапазон A1:A3 содержит значения 5, 25 и 38, формула =MATCH(25;A1:A3;0) возвращает число 2, поскольку 25 является вторым элементом в диапазоне.

Функция LEN возвращает количество символов в текстовой строке.

Функция СУММ вычисляет сумму всех чисел, указанных в качестве аргументов. Каждый аргумент может быть диапазоном, ссылкой на ячейку, массивом, константой, формулой или результатом другой функции. Например, СУММ(A1:A5) суммирует все числа, содержащиеся в ячейках A1–A5.

Функция ЕСЛИ возвращает одно значение, если условие, которое вы указываете, возвращает значение ИСТИНА, и другое, если условие возвращает значение ЛОЖЬ.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

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

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

Пусть в столбце B имеется список с повторяющимися значениями, например список с названиями компаний (см. файл примера ). Столбец А содержит номера позиций уникальных значений.

Для наглядности уникальные значения в исходном списке выделены цветом с помощью Условного форматирования .

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

Примечание : Выделять столбец требуется для того, чтобы сводная таблица содержала только одно поле (столбец В ). В противном случае сводная таблица будет содержать 2 поля. Это не повлияет на вычисления, но для наглядности пока не будем включать в сводную таблицу столбец А .

Поле Сводной таблицы Исходный список перетащите в область Названия строк.

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

Сортировка как в источнике данных

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

Эта формула пронумерует все первые повторы значений, остальные строки будут содержать значение Пустой текст «».

Теперь создадим другую сводную таблицу. Для этого нужно выделить любую ячейку в диапазоне таблице ( А7:В22 ). В этой таблице будет 2 поля.

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

Подсчет различных значений в сводной таблице Excel (простое пошаговое руководство)

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

С базовым пониманием и небольшим перетаскиванием, вы можете выполнить большую часть работы за несколько секунд.

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

И одна из таких вещей — подсчет различных значений в сводной таблице.

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

Но прежде чем я перейду к подсчету различных значений, важно понять разницу между «уникальным счетом» и «уникальным счетом».

Отличный счет против уникального счетчика

Хотя это может показаться одним и тем же, это не.

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

Уникальные значения / имена те, которые возникают только один раз. Это означает, что все повторяющиеся и повторяющиеся имена не уникальны. Уникальные имена перечислены в столбце C в приведенном выше наборе данных.

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

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

Подсчет различных значений в сводной таблице Excel

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

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

Предположим, вы хотите найти ответ на следующие вопросы:

1. Сколько торговых представителей работает в каждом регионе (это не что иное, как отдельное количество торговых представителей в каждом регионе)?
2. Сколько торговых представителей продали принтер в2021-2022 гг.?

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

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

В этом руководстве рассматриваются следующие два метода:

• Добавление вспомогательного столбца в исходный набор данных для подсчета уникальных значений (работает во всех версиях).
• Добавление данных в модель данных и использование опции Distinct Count (доступно в Excel 2013 и более поздних версиях).

В этой статье Роджер показывает третий метод (который он называет методом Pivot the Pivot Table).

Добавление вспомогательного столбца в набор данных

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

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

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

Предположим, у меня есть набор данных, как показано ниже:

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

В приведенной выше формуле используется функция СЧЁТЕСЛИМН, чтобы подсчитать, сколько раз имя появляется в данном регионе. Также обратите внимание, что диапазон критериев — $ C $ 2: C2 и $ B $ 2: B2. Это означает, что он продолжает расширяться по мере того, как вы спускаетесь по столбцу.

Например, в ячейке E2 диапазоны критериев: $ C $ 2: C2 и $ B $ 2: B2, а в ячейке E3 эти диапазоны расширяются до $ C $ 2: C3 и $ B $ 2: B3.

Это гарантирует, что функция СЧЁТЕСЛИМН считает первый экземпляр имени как 1, второй экземпляр имени как 2 и так далее.

Поскольку мы хотим получить только отдельные имена, используется функция ЕСЛИ, которая возвращает 1, когда имя появляется для региона в первый раз, и возвращает 0, когда оно появляется снова. Это гарантирует, что учитываются только отдельные имена, а не повторения.

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

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

Ниже приведены шаги для этого:

1. Выберите любую ячейку в наборе данных.
2. Щелкните вкладку "Вставить".
3. Нажмите на сводную таблицу (или воспользуйтесь сочетанием клавиш — ALT + N + V)
4. В диалоговом окне «Создание сводной таблицы» убедитесь, что таблица / диапазон указаны правильно (и включают вспомогательный столбец) и выбран параметр «Новый рабочий лист».
5. Щелкните ОК.

Вышеупомянутые шаги позволят вставить новый лист со сводной таблицей.

Перетащите поле «Регион» в область «Строки» и поле «D Count» в область «Значения».

Вы получите сводную таблицу, как показано ниже:

Теперь вы можете изменить заголовок столбца с «Sum of D count» на «Sales Rep».

Недостатки использования вспомогательной колонки:

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

• Источник данных со вспомогательным столбцом не такой динамичный, как сводная таблица. Хотя с помощью сводной таблицы вы можете разрезать данные как угодно, когда вы используете вспомогательный столбец, вы теряете часть этой способности. Допустим, вы добавили вспомогательный столбец, чтобы получить количество отдельных торговых представителей в каждом регионе. Теперь, что, если вы также хотите получить отчетливое количество торговых представителей, продающих принтеры. Вам нужно будет вернуться к исходным данным и изменить формулу вспомогательного столбца (или добавить новый вспомогательный столбец).
• Поскольку вы добавляете больше данных в источник сводной таблицы (которая также добавляется в сводный кеш), это может привести к увеличению размера файла Excel.
• Поскольку мы используем формулу Excel, это может замедлить работу вашей книги Excel, если у вас есть тысячи строк данных.

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

В сводную таблицу в Excel 2013 добавлены новые функции, которые позволяют получать точное количество при суммировании набора данных.

Если вы используете предыдущую версию, вы не сможете использовать этот метод (следует также попробовать добавить вспомогательный столбец, как показано в методе выше этого).

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

Ниже приведены шаги, чтобы получить отчетливое значение счетчика в сводной таблице:

1. Выберите любую ячейку в наборе данных.
2. Щелкните вкладку Вставка.
3. Нажмите на сводную таблицу (или воспользуйтесь сочетанием клавиш — ALT + N + V)
4. В диалоговом окне «Создание сводной таблицы» убедитесь, что таблица / диапазон указаны правильно и «Новый рабочий лист» выбран.
5. Установите флажок «Добавить эти данные в модель данных».
6. Щелкните ОК.

Вышеупомянутые шаги позволят вставить новый лист с новой сводной таблицей.

Перетащите область в область "Строки" и "Торговый представитель" в область "Значения". Вы получите сводную таблицу, как показано ниже:

В приведенной выше сводной таблице указано общее количество торговых представителей в каждом регионе (а не отдельное количество).

Чтобы получить точное количество в сводной таблице, выполните следующие действия:

1. Щелкните правой кнопкой мыши любую ячейку в столбце «Количество торговых представителей».
2. Нажмите на Настройки поля значений.
3. В диалоговом окне «Параметры поля значения» выберите «Distinct Count» в качестве типа расчета (возможно, вам придется прокрутить список вниз, чтобы найти его).
4. Щелкните ОК.

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

Некоторые вещи, которые вы знаете, добавляя данные в модель данных:

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

Щелкните здесь, чтобы загрузить файл примера

Что делать, если вы хотите подсчитывать уникальные значения (а не отдельные значения)?

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

Помните: уникальные значения и разные значения — это не одно и то же. Щелкните здесь, чтобы узнать разницу.

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

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

В этом случае поможет следующая формула:

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

Если имя встречается более чем в одном регионе, возвращается 0, иначе возвращается единица.

Формула также проверяет, повторяется ли имя в том же регионе или нет. Если имя повторяется, только первый экземпляр имени возвращает значение 1, а все остальные экземпляры возвращают 0.

Это может показаться немного сложным, но это опять же зависит от того, чего вы пытаетесь достичь.

Итак, если вы хотите подсчитать уникальные значения в сводной таблице, используйте вспомогательные столбцы, а если вы хотите подсчитать отдельные значения, вы можете использовать встроенные функции (в Excel 2013 и выше) или можете использовать вспомогательный столбец.

Щелкните здесь, чтобы загрузить файл примера

Вам также могут понравиться следующие руководства по сводным таблицам: