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

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

  • автор:

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

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел»
ИНН 7735603520
ОГРН 1147746834949
ИП Павлов Николай Владимирович
ИНН 633015842586
ОГРНИП 310633031600071

Сведение данных в сводной таблице

Для агрегирования (суммирования) значений в сводной таблице можно использовать функции сводки, такие как Sum, Count и Average.

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

Для поля с числовым значением в сводной таблице по умолчанию используется функция СУММ

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

Примечание: Функции сведения недоступны в сводных таблицах на базе источников данных OLAP.

Используйте эту функцию сводки: Чтобы вычислить, выполните приведенные далее действия .
Сумма Сумма значений. Используется по умолчанию для полей с числовыми значениями. Если поле содержит пустые или ненумерные значения (текст, дата или логическое значение) при его помещении в область Значения списка полей, сводная таблица использует функцию Count для поля. После размещения поля в области Значения можно изменить функцию сводки на Sum, а все пустые или ненумерные значения будут изменены на 0 в сводной таблице, чтобы их можно было суммировать.
СЧЁТ Количество заполненных полей. Функция сведения данных СЧЁТ работает так же, как СЧЁТЗ. СЧЁТ по умолчанию используется для пустых полей и полей с нечисловыми значениями.
Среднее Среднее арифметическое.
Максимум Наибольшее значение.
Минимум Наименьшее значение.
Произведение Произведение значений.
Смещенное отклонение Оценка стандартного отклонения генеральной совокупности, где выборка является подмножеством всей генеральной совокупности.
СТАНДОТКЛОНП Стандартное отклонение генеральной совокупности, которая содержит все сводимые данные.
ДИСП Оценка дисперсии генеральной совокупности, где выборка является подмножеством всей генеральной совокупности.
Несмещенная дисперсия Дисперсия генеральной совокупности, которая содержит все сводимые данные.
Число разных элементов Число уникальных значений. Эта сводная функция работает только при использовании модели данных в Excel.

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

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  • числа 5, 6, 7 и 6, будут найдены три уникальных значения — 5, 6 и 7;
  • строки «Руслан», «Сергей», «Сергей», «Сергей», будут найдены два уникальных значения — «Руслан» и «Сергей».

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

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

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

  1. Выделите диапазон ячеек или убедитесь, что активная ячейка находится в таблице. Убедитесь в том, что диапазон ячеек содержит заголовок столбца.
  2. На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно. Появится диалоговое окно Расширенный фильтр.
  3. Установите переключатель скопировать результат в другое место.
  4. В поле Копировать введите ссылку на ячейку. В противном случае нажмите Свернуть диалоговое окно

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

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

Для выполнения этой задачи используйте комбинацию функций ЕСЛИ, СУММ, ЧАСТОТА, ПОИСКПОЗ и ДЛСТР.

  • Назначьте значение 1 каждому из истинных условий с помощью функции ЕСЛИ.
  • Вычислите сумму, используя функцию СУММ.
  • Подсчитайте количество уникальных значений с помощью функции ЧАСТОТА. Функция ЧАСТОТА пропускает текстовые и нулевые значения. Для первого вхождения заданного значения эта функция возвращает число, равное общему количеству его вхождений. Для каждого последующего вхождения того же значения функция возвращает ноль.
  • Узнайте позицию текстового значения в диапазоне с помощью функции ПОИСКПОЗ. Возвращенное значение затем используется в качестве аргумента функции ЧАСТОТА, что позволяет определить количество вхождений текстовых значений.
  • Найдите пустые ячейки с помощью функции ДЛСТР. Пустые ячейки имеют нулевую длину.

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

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

Описание функций

  • Функция ЧАСТОТА вычисляет частоту появления значений в диапазоне и возвращает вертикальный массив чисел. С помощью функции ЧАСТОТА можно, например, подсчитать количество результатов тестирования, попадающих в определенные интервалы. Поскольку данная функция возвращает массив, ее необходимо вводить как формулу массива.
  • Функция ПОИСКПОЗ выполняет поиск указанного элемента в диапазоне ячеек и возвращает относительную позицию этого элемента в диапазоне. Например, если диапазон A1:A3 содержит значения 5, 25 и 38, формула =ПОИСКПОЗ(25;A1:A3;0) возвращает значение 2, так как элемент 25 является вторым в диапазоне.
  • Функция ДЛСТР возвращает число символов в текстовой строке.
  • Функция СУММ вычисляет сумму всех чисел, указанных в качестве аргументов. Каждый аргумент может быть диапазоном, ссылкой на ячейку, массивом, константой, формулой или результатом выполнения другой функции. Например, функция СУММ(A1:A5) вычисляет сумму всех чисел в ячейках от A1 до A5.
  • Функция ЕСЛИ возвращает одно значение, если указанное условие дает в результате значение ИСТИНА, и другое, если условие дает в результате значение ЛОЖЬ.

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

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *