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

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

history 14 января 2018 г.
    Группы статей

  • Анализ таблиц и Сводные таблицы
  • Уникальные

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

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

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

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

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

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

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

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

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

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

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

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

В итоге получим сортировку как в исходной таблице.

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

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

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

Ссылка на основную публикацию