Evolcom.ru

Бытовая техника
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как подсчитать уникальные значения в отфильтрованном столбце в Excel?

Функции таблиц уникальных значений в DAX: VALUES и DISTINCT в Power BI и Power Pivot

Антон БудуевПриветствую Вас, дорогие друзья, с Вами Будуев Антон. В данной статье поговорим про функции в DAX, возвращающие таблицы уникальных значений в Power BI и Power Pivot, а именно, про VALUES и DISTINCT.

Для Вашего удобства, рекомендую скачать «Справочник DAX функций для Power BI и Power Pivot» в PDF формате.

Если же в Ваших формулах имеются какие-то ошибки, проблемы, а результаты работы формул постоянно не те, что Вы ожидаете и Вам необходима помощь, то записывайтесь в бесплатный экспресс-курс «Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot».

А также, подписывайтесь на наши социальные сети. Потому что именно в них, Вам будут доступны оперативно и каждый день наши актуальные фишки, секреты, наработки, примеры, кейсы, полезные советы, видео и статьи по темам сквозной BI аналитики (Power BI, DAX, Power Pivot, Excel…): Вконтакте, Инстаграм, Фейсбук, YouTube.

DAX функция VALUES в Power BI и Power Pivot

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

Синтаксис: VALUES (‘Таблица’) или VALUES ([Столбец])

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

В качестве примера возьмем исходную таблицу «СпрМенеджеры», находящуюся в Power BI и содержащую информацию по менеджерам и отделам:

Исходная таблица

Создадим в Power BI Desktop во вкладке «Моделирование» вычисляемую таблицу уникальных значений отделов на основе формулы с участием VALUES:

Читайте так же:
Как поставить галочку или галочку в ячейку Excel?

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

Результат работы формулы на основе функции VALUES

Сама функция VALUES, как в примере выше, используется редко. Он часто используется в формулах в сочетании с другими функциями DAX.

Например, мы можем в рамках одной формулы объединить VALUES с функцией COUNTROWS, которая считает количество строк. Тем самым, мы можем создать меру, вычисляющую на основе исходной таблицы «СпрМенеджеры» количество отделов в организации:

Если мы выполним код этой формулы, то получим число делений, равное 3:

Результат работы формулы с участием функции VALUES

DAX функция DISTINCT в Power BI и Power Pivot

DISTINCT () — DAX функция, которая возвращает столбец с уникальными значениями исходного столбца или таблицы. В своей практике я эту функцию очень часто использую при создании так называемых таблиц справочников (измерений) на основе исходного столбца из таблицы фактов.

Таблицы фактов обычно содержат очень большой объем данных (миллионы строк), которые могут повторятся. Например, таблица фактов «Заявки», в которой в каждой строке указана информация по одной заявке — сумма, затраты, менеджер, дата сделки и т.д.

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

Синтаксис: DISTINCT ([‘Таблица’]) или DISTINCT ([Столбец])

В качестве примера работы функции DISTINCT напишем простую формулу, которая создаст в нашей модели данных справочник по менеджерам на основе исходной таблицы фактов «Заявки»:

Исходная таблица фактов Заявки

Код DAX-формулы выглядит следующим образом:

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

Таблица справочник по менеджерам, созданная на основе функции DISTINCT

Далее, в Power BI Desktop во вкладке «Связи» можно будет объединить все таблицы, содержащие столбец [Менеджер], на основе созданного функцией DISTINCT, справочника «СпрМенеджер»:

Читайте так же:
Как подсчитать максимальное количество последовательных положительных / отрицательных чисел в Excel?

Объединение связью нескольких таблиц на основе таблицы, созданной функцией DISTINCT

В результате использования функции DISTINCT, мы смогли создать справочник с уникальными значениями менеджеров, при помощи которого, объединили две таблицы фактов «Заявки» и «Дополнительные затраты».

Статья завершается обсуждением функций DAX VALUES и DISTINCT, входящих в состав Power BI и Power Pivot.

Оцените статью:

  1. 5
  2. 4
  3. 3
  4. 2
  5. 1

[Экспресс-видеокурс] Быстрый старт в языке DAX

Антон БудуевУспехов Вам, друзья!
С уважением, Будуев Антон.
Проект «BI — это просто»

Если у вас есть вопросы по материалу этой статьи, задавайте их в комментариях ниже. Я обязательно вам отвечу. И вообще, просто оставляйте там свои отзывы, я буду очень рад.

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

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

Что еще посмотреть / почитать?

DAX функции DATEADD, PARALLELPERIOD и SAMEPERIODLASTYEAR в Power BI

Как в Power BI и Power Pivot сдвинуть даты? DAX функции DATEADD, PARALLELPERIOD и SAMEPERIODLASTYEAR

DAX функции MAX и MIN

DAX функции MAX, MAXA, MAXX и MIN, MINA, MINX (для Power BI и Power Pivot)

DAX функции YEARFRAC, WEEKDAY и WEEKNUM

DAX функции YEARFRAC, WEEKDAY и WEEKNUM в Power BI и PowerPivot

Повторяющиеся значения в Excel — найти, выделить или удалить дубликаты в Excel

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

Читайте так же:
Как перечислить все даты между двумя датами в Excel?

1. Удаление повторяющихся значений в Excel (2007+)

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

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

Нажмите OK, диалоговое окно закроется, а строки, содержащие дубликаты, удалятся.

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

2. Использование расширенного фильтра для удаления дубликатов

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

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

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

3. Выделение повторяющихся значений с помощью условного форматирования в Excel (2007+)

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

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

Читайте так же:
Как переместить последнее слово в следующую соседнюю ячейку?

4. Использование сводных таблиц для определения повторяющихся значений

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

Создайте сводную таблицу. Поместите первые три столбца в поле Row name (Имя строки), а столбец счетчика — в поле Values (Значения). В созданной вами сводной таблице записи со значением больше единицы будут дубликатами, а само значение будет указывать на количество повторяющихся значений. Для наглядности можно отсортировать таблицу по колонке Counter, чтобы сгруппировать дубликаты.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector