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

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

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

Group date by fiscal year, half year, week number or other specific dates in pivot table

Normally, you can group the pivot table by month, year, quarter quickly, but, sometimes, you may want to group data based on the fiscal year, half-year and other specific dates. In this case, Kutools for Excel‘s PivotTable Special Time Grouping utility extends the original Grouping function and supports more dates grouping. Click to download Kutools for Excel!

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

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

1. В новом столбце, помимо данных, введите эту формулу =IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1) в ячейку C2, а затем перетащите маркер заполнения в ячейки диапазона, к которым вы хотите применить эту формулу, и уникальные значения будут идентифицированы, как показано ниже:

2. Теперь вы можете создать сводную таблицу. Выберите диапазон данных, включая вспомогательный столбец, затем щелкните Вставить > PivotTable > PivotTable, см. снимок экрана:

3. Тогда в Создать сводную таблицу В диалоговом окне выберите новый рабочий лист или существующий рабочий лист, на котором вы хотите разместить сводную таблицу, см. снимок экрана:

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

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

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

1. Выберите диапазон данных и нажмите Вставить > PivotTable, В Создать сводную таблицу в диалоговом окне выберите новый лист или существующий лист, на котором вы хотите разместить сводную таблицу, и установите флажок Добавьте эти данные в модель данных флажок, см. снимок экрана:

2. Тогда в Поля сводной таблицы панели, перетяните Класс поле к Строка поле и перетащите Имя и фамилия поле к Ценности box, см. снимок экрана:

3. А затем щелкните Граф имени раскрывающийся список, выберите Настройки поля значений, см. снимок экрана:

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

5, Затем нажмите OK, вы получите сводную таблицу, в которой будут учитываться только уникальные значения.

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

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

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

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

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

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

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

Разные значения против уникальных значений

Кажется, что это одно и то же, но это не так.

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

Разница между уникальным и разными значениями

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

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

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

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

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

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

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

• Сколько сотрудников в каждом регионе (а это не что иное, как количество разных сотрудников в каждом регионе)?
• Сколько сотрудников продали принтер в 2019 году?

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

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

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

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

• Добавление вспомогательного столбца в исходный набор данных для подсчета разных значений (работает во всех версиях).
• Добавление данных в модель данных и использование параметра «Число различных элементов» (доступно в Excel 2013 и последующих версиях).

Существует третий метод, он называет метод сводной таблицы в сводной таблице.

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

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

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

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

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

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

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

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

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

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

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

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

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

Ниже приведены шаги, как сделать это:

• Выберите любую ячейку в таблице.
• Нажмите вкладку «Вставка».

• Нажмите на кнопку Сводная таблица.

• В диалоговом окне «Создание сводной таблицы» убедитесь, что таблица / диапазон указаны правильно (и включает вспомогательный столбец), и выбран «На новый лист» в качестве места размещения.

• Нажмите ОК.

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

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

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

Теперь вы можете изменить заголовок столбца с «Сумма по полю Помощник» на «Количество сотрудников».

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

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

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

Добавить данные в модель данных и суммировать, используя «Число различных элементов»

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

В случае, если вы используете предыдущую версию, вы не сможете использовать этот метод (используйте метод, описанный выше).

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

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

• Выберите любую ячейку в таблице.
• Нажмите вкладку «Вставка».

• Нажмите на кнопку Сводная таблица.

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

• Нажмите ОК.

Приведенные выше шаги вставят новый лист с новой сводной таблицей.

Перетащите регион в область «Строки» и «Сотрудник» в область «Значения». Вы получите такую сводную таблицу:

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

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

• Щелкните правой кнопкой мыши по любой ячейке в «Число элементов в столбце Сотрудник»
• Нажмите на «Параметры полей значений».

• В диалоговом окне «Параметры поля значений» выберите «Число различных элементов» в качестве операции (вам может потребоваться прокрутить список вниз, чтобы найти его).

• Нажмите ОК.

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

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

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

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

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

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

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

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

Вот формула для этого случая:

= ЕСЛИ (ЕСЛИ (СЧЁТЕСЛИМН ($C$2:$C$1001; С2; $B$2:$B$1001; В2) / СЧЁТЕСЛИ ($C$2:$C$1001; С2) <1;0;1); ЕСЛИ (СЧЁТЕСЛИ ($С2:С$22; С2) > 1;0;1);0)

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

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

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

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

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

Excel works!

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

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

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

Подсчет уникальных значений. Специальная возможность Excel

В табличном редакторе есть специальная кнопка — Данные — Удалить дубликаты. Здесь останавливаться на этом способе не буду, т.к. на сайте есть отдельная статья . Когда вы удалите дубликаты, то можно подсчитать оставшиеся уникальные города функцией СЧЁТЗ.

Считаем неповторяющиеся значения сводной таблицей

Распространенный способ у любителей Excel подсчитать уникальные значения сводной таблицей. Вполне удобно. Если вы еще на знаете о сводных таблицах — рекомендую прочитать тут .

Выделяем таблицу с городами. Идем на вкладку Вставка на ленте (для версии 2007 и выше) в разделе. Самая левая кнопка — Сводная таблица. Жмем ее.

На отдельном листе формируем сводную таблицу — перетаскиваем/добавляем Города в название строк.

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

Подсчет уникальных значений формулой

Переходим к самому интересному — как посчитать уникальные города формулой? Здесь нам поможет одна хитрость и формула СЧЁТЕСЛИ . Специальной функции для подсчета уникальных значений в таблице нет, но проявим изобретательность. По факту нам нужно для каждого города понимать сколько раз он встречается в таблице и вместо этого итогового количества учитывать город 1 раз. Если город встречается 4 раза, то 1 строка, исходя из логики предыдущего предложения должна учитываться как четверть раза, т.е. 1 деленный на 4.

Теперь можно посчитать сумму по этому столбцу

Но можно пойти дальше и посчитать все в одной ячейке при помощи СУММПРОИЗВ.

Это уже совсем модно.

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

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

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

Значения 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.