Как сделать меру в excel power pivot
Обновлено: 07.07.2024
Power Pivot Особенности
Что делает Power Pivot сильным инструментом, так это набор его функций. Вы узнаете о различных функциях Power Pivot в главе «Функции Power Pivot».
Данные Power Pivot из различных источников
Power Pivot может сопоставлять данные из различных источников данных для выполнения необходимых расчетов. Вы узнаете, как получить данные в Power Pivot, в главе «Загрузка данных в Power Pivot».
Модель данных Power Pivot
Управление моделью данных и отношениями
Вам необходимо знать, как вы можете управлять таблицами данных в модели данных и связями между ними. Подробнее об этом вы узнаете в главе «Управление моделью данных Power Pivot».
Создание Power Pivot Tables и Power Pivot Charts
Power PivotTables и Power Pivot Chart предоставляют вам возможность проанализировать данные, чтобы прийти к выводам и / или решениям.
Основы DAX
Изучение и представление данных Power Pivot
Вы можете изучить данные Power Pivot, которые находятся в модели данных, с помощью Power PivotTables и Power Pivot Charts. В этом уроке вы узнаете, как вы можете исследовать данные и сообщать о них.
Иерархии
Вы можете определить иерархии данных в таблице данных, чтобы было легко обрабатывать связанные поля данных вместе в Power PivotTables. Вы узнаете подробности создания и использования Иерархий в главе «Иерархии в Power Pivot».
Эстетические отчеты
Вы можете создавать эстетические отчеты о своем анализе данных с помощью Power Pivot Charts и / или Power Pivot Charts. У вас есть несколько вариантов форматирования, чтобы выделить важные данные в отчетах. Отчеты являются интерактивными по своей природе, что позволяет человеку, просматривающему компактный отчет, быстро и легко просматривать любую необходимую информацию.
Эти подробности вы узнаете в главе «Эстетические отчеты с данными Power Pivot».
Power Pivot в Excel предоставляет модель данных, соединяющую различные источники данных, на основе которых можно анализировать, визуализировать и исследовать данные. Простой в использовании интерфейс, предоставляемый Power Pivot, позволяет человеку с практическим опытом работы в Excel легко загружать данные, управлять данными как таблицами данных, создавать связи между таблицами данных и выполнять необходимые вычисления для получения отчета. ,
В этой главе вы узнаете, что делает Power Pivot сильным и востребованным инструментом для аналитиков и лиц, принимающих решения.
Power Pivot на ленте
Если у вас Excel 2010, вкладка POWERPIVOT может не отображаться на ленте, если вы еще не включили надстройку Power Pivot.
Надстройка Power Pivot
В поле Управление выберите Надстройки COM из раскрывающегося списка.
Проверьте Power Pivot и нажмите ОК.
В поле Управление выберите Надстройки COM из раскрывающегося списка.
Проверьте Power Pivot и нажмите ОК.
Что такое Power Pivot?
Удобный интерфейс Power Pivot в окне PowerPivot позволяет выполнять операции с данными без знания какого-либо языка запросов к базе данных. Затем вы можете создать отчет о вашем анализе в течение нескольких секунд. Отчеты являются универсальными, динамичными и интерактивными и позволяют вам дополнительно исследовать данные, чтобы получить представление и прийти к выводам / решениям.
Данные, с которыми вы работаете в Excel и в окне Power Pivot, хранятся в аналитической базе данных внутри книги Excel, а мощный локальный механизм загружает, запрашивает и обновляет данные в этой базе данных. Поскольку данные находятся в Excel, они сразу доступны для сводных таблиц, сводных диаграмм, Power View и других функций Excel, которые вы используете для агрегирования и взаимодействия с данными. Представление данных и интерактивность обеспечиваются Excel, а объекты данных и презентации Excel содержатся в одном файле рабочей книги. Power Pivot поддерживает файлы размером до 2 ГБ и позволяет работать с 4 ГБ данных в памяти.
Мощные функции в Excel с Power Pivot
Функции Power Pivot бесплатны в Excel. Power Pivot повысил производительность Excel благодаря мощным функциям, которые включают следующее:
Способность обрабатывать большие объемы данных, сжатые в небольшие файлы, с удивительной скоростью.
Фильтруйте данные и переименовывайте столбцы и таблицы при импорте.
Организовать таблицы в отдельные страницы с вкладками в окне Power Pivot по сравнению с таблицами Excel, распределенными по всей книге или по нескольким таблицам в одной рабочей таблице.
Создайте связи между таблицами, чтобы совместно анализировать данные в таблицах. До Power Pivot приходилось полагаться на интенсивное использование функции VLOOKUP, чтобы объединить данные в одну таблицу перед таким анализом. Раньше это было трудоемким и подверженным ошибкам.
Добавьте мощность в простую сводную таблицу со многими дополнительными функциями.
Предоставить язык выражений анализа данных (DAX) для написания расширенных формул.
Добавьте вычисляемые поля и вычисляемые столбцы в таблицы данных.
Создайте KPI для использования в сводных таблицах и отчетах Power View.
Способность обрабатывать большие объемы данных, сжатые в небольшие файлы, с удивительной скоростью.
Фильтруйте данные и переименовывайте столбцы и таблицы при импорте.
Организовать таблицы в отдельные страницы с вкладками в окне Power Pivot по сравнению с таблицами Excel, распределенными по всей книге или по нескольким таблицам в одной рабочей таблице.
Создайте связи между таблицами, чтобы совместно анализировать данные в таблицах. До Power Pivot приходилось полагаться на интенсивное использование функции VLOOKUP, чтобы объединить данные в одну таблицу перед таким анализом. Раньше это было трудоемким и подверженным ошибкам.
Добавьте мощность в простую сводную таблицу со многими дополнительными функциями.
Предоставить язык выражений анализа данных (DAX) для написания расширенных формул.
Добавьте вычисляемые поля и вычисляемые столбцы в таблицы данных.
Создайте KPI для использования в сводных таблицах и отчетах Power View.
Вы поймете особенности Power Pivot подробно в следующей главе.
Использование Power Pivot
Вы можете использовать Power Pivot для следующих целей:
Для выполнения мощного анализа данных и создания сложных моделей данных.
Быстрое объединение больших объемов данных из нескольких разных источников.
Для анализа информации и обмена знаниями в интерактивном режиме.
Для написания расширенных формул на языке выражений анализа данных (DAX).
Создать ключевые показатели эффективности (KPI).
Для выполнения мощного анализа данных и создания сложных моделей данных.
Быстрое объединение больших объемов данных из нескольких разных источников.
Для анализа информации и обмена знаниями в интерактивном режиме.
Для написания расширенных формул на языке выражений анализа данных (DAX).
Создать ключевые показатели эффективности (KPI).
Моделирование данных с помощью Power Pivot
Power Pivot предоставляет расширенные возможности моделирования данных в Excel. Данные в Power Pivot управляются в модели данных, которая также называется базой данных Power Pivot. Вы можете использовать Power Pivot, чтобы получить новое представление о ваших данных.
Вы можете создавать отношения между таблицами данных, чтобы вы могли выполнять общий анализ данных в таблицах. С DAX вы можете писать расширенные формулы. Вы можете создавать вычисляемые поля и вычисляемые столбцы в таблицах данных в модели данных.
Вы можете определить иерархии в данных, чтобы использовать их везде в книге, включая Power View. Вы можете создавать KPI для использования в сводных таблицах и отчетах Power View, чтобы сразу увидеть, является ли производительность включенной или выключенной для одной или нескольких метрик.
Бизнес-аналитика с Power Pivot
Вы можете предоставить доступ к своей книге в среду SharePoint, в которой включены службы Excel. На сервере SharePoint службы Excel обрабатывают и отображают данные в окне браузера, где другие могут анализировать данные.
В этой главе вы получите краткий обзор функций Power Pivot, которые подробно будут показаны позже.
Загрузка данных из внешних источников
Вы можете загрузить данные в модель данных из внешних источников двумя способами:
Загрузите данные в Excel, а затем создайте модель данных Power Pivot.
Загрузка данных непосредственно в модель данных Power Pivot.
Загрузите данные в Excel, а затем создайте модель данных Power Pivot.
Загрузка данных непосредственно в модель данных Power Pivot.
Второй способ более эффективен благодаря эффективному способу обработки данных в памяти Power Pivot.
Для получения более подробной информации см. Главу «Загрузка данных в Power Pivot».
Окно Excel и Power Pivot Window
Вам не нужно иметь данные в таблицах Excel при импорте данных из внешних источников. Если у вас есть данные в виде таблиц Excel в книге, вы можете добавить их в модель данных, создавая таблицы данных в модели данных, которые связаны с таблицами Excel.
При создании сводной таблицы или сводной диаграммы из окна Power Pivot они создаются в окне Excel. Однако данные по-прежнему управляются из модели данных.
Вы всегда можете легко переключаться между окном Excel и окном Power Pivot в любое время.
Модель данных
Подробнее о модели данных вы узнаете в главе «Общие сведения о модели данных (Power Pivot Database)».
Оптимизация памяти
Модель данных Power Pivot использует хранилище xVelocity, которое сильно сжимается при загрузке данных в память, что позволяет хранить в памяти сотни миллионов строк.
Таким образом, если вы загружаете данные непосредственно в модель данных, вы будете делать это в эффективной сильно сжатой форме.
Компактный размер файла
Если данные загружаются непосредственно в модель данных, при сохранении файла Excel они занимают очень мало места на жестком диске. Вы можете сравнить размеры файлов Excel, первый с загрузкой данных в Excel, а затем с созданием модели данных, а второй с загрузкой данных непосредственно в модель данных, пропустив первый шаг. Второй будет в 10 раз меньше первого.
Power PivotTables
Вы можете создать Power PivotTables из окна Power Pivot. Созданные таким образом сводные таблицы основаны на таблицах данных в модели данных, что позволяет объединять данные из связанных таблиц для анализа и составления отчетов.
Power PivotCharts
Вы можете создать Power PivotCharts из окна Power Pivot. Созданные таким образом сводные диаграммы основаны на таблицах данных в модели данных, что позволяет объединять данные из связанных таблиц для анализа и составления отчетов. Power PivotCharts обладает всеми функциями сводных диаграмм Excel и многими другими, такими как кнопки полей.
Вы также можете иметь комбинации Power PivotTable и Power PivotChart.
DAX Language
Преимущество Power Pivot заключается в языке DAX, который можно эффективно использовать в модели данных для выполнения расчетов с данными в таблицах данных. У вас могут быть рассчитанные столбцы и вычисляемые поля, определенные DAX, которые можно использовать в сводных таблицах и сводных диаграммах питания.
В этой главе мы научимся загружать данные в Power Pivot.
Вы можете загрузить данные в Power Pivot двумя способами:
Загрузить данные в Excel и добавить их в модель данных
Загружайте данные непосредственно в PowerPivot, заполняя модель данных, которая является базой данных PowerPivot.
Загрузить данные в Excel и добавить их в модель данных
Загружайте данные непосредственно в PowerPivot, заполняя модель данных, которая является базой данных PowerPivot.
Если вам нужны данные для Power Pivot, сделайте это вторым способом, даже если Excel даже не знает об этом. Это потому, что вы будете загружать данные только один раз, в сильно сжатом формате. Чтобы понять разницу, предположим, что вы загружаете данные в Excel, сначала добавляя их в модель данных, размер файла, скажем, 10 МБ.
Если вы загружаете данные в PowerPivot и, следовательно, в модель данных, пропуская дополнительный шаг Excel, размер вашего файла может составлять всего 1 МБ.
Источники данных, поддерживаемые Power Pivot
DAX-формулы или Data Analysis Expressions — выражения для анализа данных в Microsoft Power BI, в Analysis Services и Power Pivot в Excel. DAX-формулы позволяют, по аналогии с формулами Excel, выполнять вычисления и настраивать произвольную фильтрацию и представление данных в таблицах.
Язык DAX есть в следующих приложениях:
Power BI Desktop
Power Pivot в Excel
SQL Server Management Studio
Впервые DAX-формулы появились в Excel 2010 года во внешней com-надстройке Power Pivot . С тех пор этот язык становится все более популярным, и если раньше о DAX слышали только единицы, то сейчас он широко применяется в бизнес-аналитике и проектировании моделей. Поэтому DAX-формулы вам точно пригодятся для продвинутого анализа.
В этой статье описание DAX приводится для Power Pivot в Excel и Power BI.
Строение DAX-формул
DAX-формулы очень похожи на обычные формулы Excel. Многие из них записываются одинаково, например, «сумма»- SUM и «если»- IF . Но сами вычисления работают по-разному: в отличие от обычного Excel, в языке DAX нет расчетов по ячейкам. DAX-формулы обращаются сразу к таблицам и столбцам целиком. Примерно похожий способ вычислений есть и в «обычном» Excel – с помощью формул массивов. И если вы работали с форматированными smart-таблицами, то чтобы лучше понять DAX, вспомните, как в них выглядят ссылки – название столбца в квадратных скобках.
В DAX-формулах почти также: названия таблиц обычно пишут в одинарных кавычках (или без кавычек, если имя таблицы написано латинскими буквами без пробелов и цифр в начале). Названия столбцов пишут в квадратных скобках:
- && — аналог формулы И (AND)
- || — аналог ИЛИ (OR)
- IN – поиск элемента в списке
- NOT – логическое отрицание, аналог формулы НЕ.
Вычисляемые столбцы, меры и таблицы
С помощью DAX-формул в Power BI можно создавать:
- вычисляемые столбцы;
- меры;
- вычисляемые таблицы.
В Excel есть только вычисляемые столбцы и меры.
Понятия столбцов и мер – основы работы с DAX. Давайте разберемся, что это такое.
Вычисляемый столбец – это столбец, который добавляется в существующую таблицу, а DAX-формула определяет значения этого столбца.
Как и обычные столбцы в модели данных, вычисляемые столбцы можно использовать в других вычислениях. А также для создания связей между таблицами, для построения визуализаций и срезов. В сводных таблицах вычисляемые столбцы можно помещать в области фильтров, колонок, строк и значений.
Если данные в вашем файле загружаются в режиме импорта, то столбец рассчитывается и записывается в файл при загрузке и обновлении данных, увеличивая размер файла. Вычисляемые столбцы лучше использовать, когда нужен текст, дата или когда вычисление зависит от соседних колонок.
Вычисляемые столбцы создаются просто, как в Power Pivot, так и в Power BI: добавляется новый столбец, пишется «равно» и формула.
Чтобы обратиться к вычисляемому столбцу в других вычислениях, нужно написать имя таблицы, в которой он находится, и название самого столбца. Например, ' Таблица ' [Столбец]
Меры – это динамические вычисления, результаты которых рассчитываются в зависимости от контекста. Результат вычисления меры можно увидеть в отчете, где мы задаем в каком именно контексте (в разрезе каких полей, фильтров и др.) нужно посчитать меру.
Как создать меру:
- В Excel меры записывают в окне Power Pivot в области для вычислений под таблицей: выберите ячейку, введите название меры и знак :=
Или в меню Power Pivot → Меры → Создать меру. - Чтобы создать меру в Power BI, нажмите Главная → Создать меру (или нажать правой кнопкой мышки в области полей по таблице → Создать меру).
При создании мер нужно обязательно использовать агрегирующие функции, например суммирования SUM . Мера не может быть создана просто как обращение к столбцу таблицы:
- Так не работает : прибыль:= ' Данные ' [выручка] — ' Данные ' [расходы]
+ Так работает : прибыль:= SUM ( ' Данные ' [выручка]) – SUM ( ' Данные ' [расходы])
Меры лучше создавать, когда нужны числовые вычисления, например, для промежуточных итогов, вычисления процентов, доли продукта в группе и так далее. Меры можно использовать для вычисления других мер и столбцов. При оформлении отчетов и сводных таблиц меры добавляются только в область значений.
Чтобы использовать меру в других вычислениях, ее название пишут в квадратных скобках.
Пример: МераВ = [МераА] + 100
Примечание о записи формул и разделителей:
- В Power BI формулы записывают с помощью знака равно = и разделителей-запятых.
Пример: Мера = IF ( [kpi] >100, [a] , [b] )
В настройках Power BI есть возможность выбрать, какой именно разделитель использовать в формулах – запятую или точку с запятой.
- В Power Pivot разделителем в формулах может быть запятая «,» или точка с запятой «;» в зависимости от региональных настроек.
Вычисляемые столбцы записывают с помощью знака =
При создании меры пишут её название и знак :=
Пример: Мера:= IF ( [kpi] >100; [a] ; [b] )
Базовые DAX-формулы
В языке DAX существует множество формул или функций, позволяющих выполнять продвинутые аналитические вычисления. Эти функции относятся к разным группам — агрегирующие, логические, математические, для работы с текстом, со временем и др. Полный список функций можно посмотреть на сайте Microsoft. Для начала разберем наиболее часто встречающиеся (на наш взгляд) формулы.
1. SUM
SUM суммирует числа в столбце. Её аналог в Excel – формула СУММ .
Синтаксис формулы очень простой:
2. BLANK
Записывается формула очень просто:
Никаких аргументов у нее нет.
Формулы BLANK нет в Excel, но в вычислениях с DAX она используется очень часто. Для чего нужна формула BLANK ? Она помогает скрыть в отчетах ненужные значения.
3. IF
Формула IF – это логическая формула, аналог ЕСЛИ в Excel. Она проверяет условие и, если условие выполнено, возвращает одно значение, иначе – другое значение.
Какой же анализ данных может обойтись без логических формул? При всей важности формулы IF , используется она не так часто, как может показаться. Потому что во многих DAX-вычислениях её заменяют формулы фильтрации, о которых мы расскажем позже.
4. DIVIDE
Формула DIVIDE – формула для улучшенного деления.
Несмотря на то, что в DAX есть привычный нам оператор деления / , формула DIVIDE лучше. Она удобнее и в ней не надо делать проверку ошибки деления на ноль. Формула сама всё проверит и заменит ошибку на пустое значение.
<альтернативный результат> — это значение, которое будет выводиться, когда деление на ноль приводит к ошибке. Его указывать необязательно, по умолчанию формула возвращает пустое значение.
5. MIN и MAX
Формулы MIN и MAX – это агрегирующие формулы. Они находят минимальное и, соответственно, максимальное значение из столбца или из двух выражений (выражение должно вычислять единичное значение).
Если вы думаете, что эти формулы нужны для поиска наименьшего или наибольшего значения показателя, то вы правы. А еще MIN и MAX часто применяются в вычислениях, связанных с датами. То есть они вам точно пригодятся – выписываем и берем на вооружение!
6. DISTINCTCOUNT
DISTINCTCOUNT – полезная формула. Она подсчитывает количество уникальных значений в столбце таблицы.
С помощью этой формулы можно узнать, например, сколько покупателей сделали покупки или количество уникальных заказов, по которым велась работа. И многое другое.
7. COUNTROWS
Формула COUNTROWS считает количество всех строк в таблице. В отличие от предыдущей формулы, она считает все подряд строки, а не только уникальные значения. С помощью этой формулы можно узнать, например, число всех транзакций за период.
Кстати, COUNTROWS умеет считать строки не только в простой таблице, но и в таблице, заданной каким-то выражением, например, с помощью фильтрации. Для подсчета пустых ячеек используется формула COUNTBLANK .
Давайте разберем, какие еще вычисления можно делать с помощью DAX.
Функции агрегирования
Как мы уже говорили, в DAX-формулах для обращения к данным нужно писать формулы агрегирования, такие как SUM , MAX и MIN . Также часто встречаются AVERAGE и COUNT – среднее и количество.
Кроме таких формул существуют еще другие, похожие на них с окончаниями «А» и «Х». Функции с «A» на конце обрабатывают непустые ячейки. Формулы с «X» позволяют выполнять вычисления по строкам.
| Что считать | Вычисления по таблице | Вычисления для непустых значений (A) | Вычисления для каждой строки таблицы (Х) |
| Сумма | SUM | SUMX | |
| Среднее | AVERAGE | AVERAGEA | AVERAGEX |
| Максимум | MAX | MAXA | MAXX |
| Минимум | MIN | MINA | MINX |
| Количество | COUNT | COUNTA | COUNTX |
Для чего нужны построчные вычисления в формулах с «X»? Если создать меру так:
Мера = SUM ( ' Данные ' [Цена]) * SUM ( ' Данные ' [Количество]), вычисления будут некорректные.
Необходимы вычисления по строкам:
Мера = SUMX ( ' Данные ' ; [Цена] * [Количество])
Логические функции
Логические функции в DAX довольно просты для понимания. Они выполняют то же, что в «обычном» Excel. Чтобы вам было проще разобраться, собрали в таблице часто используемые логические функции.
| Формула | Что делает | Похожая формула Excel |
| IF | проверка выполнения условия | ЕСЛИ |
| AND , && | проверяет, все ли аргументы истинные | И |
| OR , || | проверяет, есть ли хотя бы один аргумент, равный TRUE | ИЛИ |
| NOT | меняет логическое значение на противоположное | НЕ |
| TRUE , FALSE | значения Истина и Ложь | ИСТИНА, ЛОЖЬ |
| IFERROR | проверяет, нет ли ошибки | ЕСЛИОШИБКА |
| SWITCH | аналог формулы IF , более удобный для множественных условий | ВЫБОР |
В пояснении нуждаются только две последние формулы — IFERROR и SWITCH .
Если формула в некоторых случаях выдает ошибку, ее можно «перехватить» с помощью IFERROR , например:
Формула SWITCH может выбрать 255 вариантов значений в зависимости от того, чему равна влияющая ячейка.
Например, мы можем записать формулу для времени года так:
и так далее – даже если мы используем OR , легче не станет.
Со SWITCH все проще:
и так далее – уже проще и понятнее.
Математические функции
Чтобы хорошо разобраться в математических формулах, вспомните, какие именно из них вы чаще всего применяете в вычислениях и найдите аналогичные в DAX. Про формулы SUM и DIVIDE мы уже писали, а далее в таблице собраны другие популярные формулы.
| Формула | Что делает | Похожая формула Excel |
| ABS | находит модуль числа | ABS |
| SIGN | определяет знак числа | ЗНАК |
| POWER | возведение в степень | СТЕПЕНЬ |
| SQRT | находит квадратный корень | КОРЕНЬ |
| QUOTIENT | возвращает только целую часть деления | ОТБР |
| RANDBETWEEN | возвращает случайное число в диапазоне между двумя числами | СЛУЧМЕЖДУ |
| ROUND | округление до заданного числа десятичных разрядов | ОКРУГЛ |
| ROUNDUP | округление в большую сторону | ОКРУГЛВВЕРХ |
| ROUNDDOWN | округление в меньшую сторону | ОКРУГЛВНИЗ |
Текстовые функции
Текстовые функции в DAX основаны на аналогичным списке функций в Excel. Наиболее часто используемые функции собраны в таблице.
| Формула | Что делает | Похожая формула Excel |
| CONCATENATE , CONCATENATEX и оператор & | объединяет текстовые строки в одну, оператор & используется для объединения строк текста | СЦЕПИТЬ, ОБЪЕДИНИТЬ и & |
| TRIM | удаляет лишние пробелы | СЖПРОБЕЛЫ |
| LOWER и UPPER | преобразует все буквы в строке в строчные / прописные | СТРОЧН и ПРОПИСН |
| LEFT и RIGHT | возвращает указанное количество символов с начала (конца) строки | ЛЕВСИМВ и ПРАВСИМВ |
| LEN | возвращает число символов в строке | ДЛСТР |
| FIND и SEARCH | возвращает номер начальной позиции искомого текста в строке (с учетом или без учета регистра) | НАЙТИ и ПОИСК |
| MID | возвращает строку из текста по начальной позиции и длине | ПСТР |
| FORMAT | преобразует значение в текст в соответствии с указанным форматом | ТЕКСТ |
Функции для работы с датами
В DAX часто встречаются вычисления, связанные с датами. Поэтому там много формул, позволяющих такие расчеты выполнять.
| Формула | Что делает | Похожая формула Excel |
| TODAY | определяет сегодняшнюю дату | СЕГОДНЯ |
| DATE | возвращает заданную дату | ДАТА |
| DAY , MONTH , YEAR | вычисляет день, месяц, год для заданной даты | ДЕНЬ, МЕСЯЦ, ГОД |
| WEEKDAY | возвращает номер дня недели, от 1 до 7 | ДЕНЬНЕД |
| WEEKNUM | определяет номер недели в году | НОМНЕДЕЛИ |
| EDATE | находит дату через указанное число месяцев от заданной даты | ДАТАМЕС |
| EOMONTH | находит дату последнего дня месяца до или после указанного числа месяцев | КОНМЕСЯЦА |
Функции фильтрации
А еще в DAX есть формулы фильтров, аналога которых в «обычном» Excel нет и быть не может. Потому что такие формулы позволяют ссылаться не просто на столбец, а целиком на таблицу. Формулы фильтрации можно подставлять в меры и тогда они будут выдавать «виртуальные» таблицы с заданными параметрами. Такие таблицы не дают видимого результата и используются как промежуточные функции внутри вычисления. Примером таких функций являются SUMMARIZE , ADDCOLUMNS и более часто используемые формулы FILTER , ALL .
В определениях DAX функция CALCULATE относится к функциям фильтрации. CALCULATE работает по аналогии с формулой СУММЕСЛИМН при указании в этой формуле суммы и условия отбора – фильтра:
Самыми яркими представителями функций фильтрации являются FILTER и ALL :
Кроме функций, перечисленных выше, в DAX существуют другие – функции связей, обработки таблиц, информационные, статистические, финансовые, функции операций со временем и т.д. Как видите, язык DAX позволяет выполнять самые разные вычисления. Примеры таких вычислений можно посмотреть в следующих статьях.
Работая в сфере аналитики и мониторя различные инструменты BI рано или поздно наталкиваешься на обзор или упоминание надстройки Power Pivot Excel. В моем случае знакомство с ним произошло на конференции Microsoft Data Day.
Особых впечатлений после презентации инструмент не оставил: Да, бесплатен (в рамках лицензии Office), да — есть некий ETL функционал в части получения данных с разрозненных источников (БД,csv,xls, и т.д.), Join-ов этих источников и скармливания в оперативку записей на порядки выше 1 млн.строк в Excel. Короче, посмотрел и забыл.
А вспомнить пришлось, когда появилась необходимость идентификации определённых явлений в данных
На написание данной статьи сподвигло то что в рунете по этому инструменту не много какой либо детальной информации о конкретных приемах работы, все больше о звездах, поэтому решил, изучая этот инструмент, написать данный обзор.
Собственно, постановка задачи (на обезличенном примере) следующая:
В исходных данных csv файла:
Есть торговые точки, детализированные до строк накладных, при этом допускается для точек с одинаковым наименованием иметь разные адреса только в том случае если они расположены в разных городах, но в исходном массиве данных есть точки, у которых попадаются разные адреса в одном и том же городе при том, что названия точек одинаковые (имя торговой точки уникально, т.е. это единица сети или отдельно стоящая точка). Как частный случай в агрегированном виде:
Поиску и очистке данных штатными средствами office мешают следующие обстоятельства:
• Детализация данных до строк накладной
• Количество записей в несколько миллионов строк
• Отсутствие sql инструментария (К примеру: Access — не в комплекте)
Конечно можно залить любую бесплатную СУБД (хоть десктоп версию, хоть серверную) но для этого во-первых нужны админские права, во-вторых статья была бы уже не про Power Pivot.
Задача: для каждой атомарной записи требуется дополнительное вычисляемое поле, которое посчитает для каждого наименования торговой точки уникальное количество адресов в рамках того же города. Данное поле требуется для быстрого нахождения всех имен торговых точек в городе, где адресов больше 1.
Думаю, удобнее всего решать и рассказывать итерационно, с допущением что у нас знания по DAX в зачаточном уровне.
Поэтому предлагаю пока оторваться от задачи и рассмотреть некоторые базовые аспекты.
Шаг 1. Чем отличается вычисляемый столбец от вычисляемой меры?
Вот пример вычисляемого столбца для выделения НДС из поля отгрузки с НДС используя встроенные формулы DAX:
=ROUND([Отгрузка с НДС]*POWER(1,18;-1)*0,18;2)
Как видно из примера вычисляемый столбец (Назовем его НДС) работает с каждой атомарной записью по горизонтали.
Теперь добавим вычисляемое поле для цены за штуку без НДС:
=ROUND([Отгрузка с НДС]*POWER(1,18;-1)/[Отгрузка шт];2)
Теперь для сравнения добавим в меру расчет средней цены за штуку:
Средняя цена за штуку без НДС: =ROUND(AVERAGE([Поле_Цена за штуку без НДС]);2)
Как видно из формулы, мера работает со столбцом исходных данных по вертикали, поэтому она всегда должна содержать в себе какую то работающую с множеством функцию (Сумму, среднюю, дисперсию и т.д.)
При возврате в сводную таблицу Excel это выглядит так:
Обратите внимание, если вычисляемое поле НДС на каждом уровне данных (зеленая обводка на уровне торговой точки, города или итого по таблице) показывает сумму, что в принципе – корректно, то сумма цен вычисляемого поля «Цена за штуку без НДС» (красная обводка) вызывает вопросы.
А вот вычисляемая мера «Средняя цена за штуку без НДС» вполне имеет право на жизнь в рамках данного аналитического куба.
Отсюда делаем вывод, что вычисляемое поле «Цена за штуку без НДС» является вспомогательным инструментом для расчета меры «Средняя цена за штуку без НДС» и дабы не смущать пользователя этим полем мы скроем его из списка клиентских средств, оставив меру средней цены.
Еще одно отличие меры от столбца – она позволяет добавить визуализацию:
К примеру, построим KPI степени разброса цен с целевой границей 35% путем деления корня из дисперсии на среднюю арифметическую.
К_вар:=STDEV.P([Поле_Цена за штуку без НДС])/AVERAGE([Поле_Цена за штуку без НДС])
В итоге видим такую таблицу в Excel (кстати расчетное вспомогательное поле цен уже не в списке доступных полей справа):
Двойной клик на 80%-м коэффициенте показывает, что цены действительно колбасит вокруг средней:
Cильнее чем при коэффициенте 15%:
Итак, на данном шаге мы рассмотрели основные отличия мер от полей в рамках PowerPivot.
Шаг 2. Усложняем: Посчитаем долю каждой записи в общих продажах.
Вот первый пример сравнения подходов оконных функций MS SQL Server и DAX:
Понятно, что в рамках сводных таблиц это делается буквально в 2 клика мышкой не касаясь клавиатуры, но для понимания попробуем это непосредственно в PowerPivot с применением формул.
На sql я бы это написал так (за огрехи не пинать, ибо Word синтаксис SQL Server не проверяет):
Здесь, как можно заметить окно открывается через все записи датасэта, попробуем аналогичную вещь в PowerPivot:
=[Отгрузка шт]/CALCULATE(SUM([Отгрузка шт]);ALL('Таблица1'))
Основное внимание обратим к знаменателю: Я уже упоминал выше что основное отличие вычисляемого поля от меры заключается в том что в поле формулы считают по горизонтали ( в рамках одной записи) а меры – по вертикали ( в рамках одного атрибута). Здесь мы смогли скрестить свойства поля и свойство меры через метод CALCULATE. И если ширину окна в SQL мы отрегулировали через Over() то здесь мы сделали это через All().
Попробуем теперь, обладая данным навыком, сделать с нашими данными что –нибудь полезное, например, вспомнив что показатель разброса цен вокруг средних варьировался в широком диапазоне, попробуем выделить статистические выбросы цен через правило 3-х сигм.
Оконные функции на sql будут смотреться так:
А вот то же самое в DAX:
=if(ABS([Поле_Цена за штуку без НДС]-CALCULATE(AVERAGE([Поле_Цена за штуку без НДС]);ALL('Таблица1')))>(3*CALCULATE(STDEV.P([Поле_Цена за штуку без НДС]);all('Таблица1')));1;0)
Как видите, цена несколько высоковата при средней арифметической 40,03 руб.
Шаг 3. Сужаем окна.
Попробуем теперь посчитать в вычисляемом поле каждой записи общее количество записей в рамках того города, к которому принадлежит и данная запись.
На MS sql Server оконные функции будут выглядеть так:
В DAX:
=CALCULATE(COUNTROWS('Таблица1');ALLEXCEPT('Таблица1';'Таблица1'[Город]))
Обратите внимание на разницу в отображении данных в таблице, я специально бросил адреса в область мер что бы посчитать их количество и сравнить с новым полем которое вывел в заголовок строк после имени торговой точки.
Отчетлива видна разница: если обычный расчет количества адресов идет для каждой точки в городе и потом только выводит промежуточный итог для агрегата «Город» то использование оконных функций позволяет присвоить каждой атомарной записи значение любого агрегата, либо использовать его в каких-то промежуточных расчетах вычисляемого поля ( как было показано выше).
Возвращаемся к исходной задаче
Итак, напомню, исходная постановка задачи: для каждой атомарной записи требуется дополнительное вычисляемое поле, которое посчитает для каждого наименования торговой точки уникальное количество адресов в рамках того же города. Не забываем, что датасэт у нас детализирован до строк накладной, поэтому перед подсчетом адресов внутри окна их необходимо сгруппировать.
Запрос на SQL Server:
Теперь нам ничего не мешает это сделать и в DAX:
В итоге у нас появилась возможность отобрать подозрительные записи, где на одну и ту же точку в одном городе приходится более 1 адреса.
Конечно в процессе изучения (пробежавшись взглядом на другие формулы) становится понятно что DAX в PowerPivot гораздо мощнее чем показано в данном топике, но объять необъятное за раз – точно не получится.
Данная статья содержит информацию о том, как с помощью надстройки Power Pivot в Excel добавлять текстовые поля в область значений сводной таблицы. Это может быть полезно, когда необходимо перечислить сущности, не добавляя их в группировку сводной таблицы. Такими сущностями могут быть номера договоров, перечень ассортимента, различные комментарии и другие характеристики.
ИНСТРУКЦИЯ ПО ДОБАВЛЕНИЮ ТЕКСТОВЫХ ПОЛЕЙ В СВОДНУЮ ТАБЛИЦУ
Чтобы с помощью надстройки Power Pivot добавить текст в сводную таблицу, нужно выполнить последовательно следующие действия:
Шаг 1. Включаем надстройку Power Pivot.
Надстройка Power Pivot входит в стандартный комплект Excel 2013, 2016 и Excel 365 для Windows. Она подключается одной галочкой в окне надстроек:
Файл → Параметры → Надстройки → Надстройки COM → Microsoft Power Pivot.
Шаг 2. Загружаем исходную таблицу Exсel в модель данных Power Pivot и создаем сводную таблицу с подключением к модели. В качестве примера возьмем таблицу (рис. 1), в которой ведутся остатки ассортимента в магазине обуви.
Исходную таблицу с информацией об остатках обуви загружаем в модель данных Power Pivot, как показано на рис. 2.
Выполнив загрузку, выходим из окна Power Pivot, создаем новый лист, через команду «Вставка» строим сводную таблицу.
Важный момент: в случае с Power Pivot для создания сводной таблицы выделять исходную таблицу не нужно.
Шаг 3. Добавим в область строк сводной таблицы следующие поля: Тип обуви, Полное наименование, Размеры, Цвет (рис. 3).
Для начала подсчитаем количество уникальных цветов для выбранной группировки.
По аналогии с вычисляемыми полями сводной таблицы в Power Pivot есть меры, которые создают такие же вычисляемые поля. В отличие от классических вычисляемых полей меры более функциональны и интуитивно понятны.
Чтобы создать меру, нужно на вкладке Power Pivot выбрать «Меры» и «Создать меру» (см. рис. 3).
В окне создания меры необходимо указать таблицу, в которой данная мера располагается, имя меры и формулу. По желанию можно указать формат и описание (рис. 4). Одним из вариантов определения количества уникальных цветов будет мера, записанная следующим образом:
Функция VALUES() создает таблицу из одного столбца с уникальными значениями (в нашем случае это «коричневый», «синий», «черный»).
Функция COUNTROWS() подсчитывает в таблице количество строк.
К мере применяются фильтры сводной таблицы (учитываются ее разрезы).
Рассмотрим, как считает написанная мера.
В группировке Ботильонов и следующего наименования обуви указано 3 — максимальное количество уникальных цветов. Все три цвета повторяются в первом по порядку размере 38, поэтому у данного размера тоже 3. В размере 39 только один цвет, поэтому стоит 1 (рис. 5).
Если функция VALUES() имеет единственное значение, то в значения сводной таблицы его можно добавить текстом, а не только рассчитать количество таких значений, чем ограничивается стандартный Excel.
Предположим, нами создана мера, которая выглядит следующим образом:
Чтобы обойти данную ошибку, нужно определить, когда VALUES() возвращает одно или несколько значений. Для этого воспользуемся функцией HASONEVALUE(). Она возвращает 1 (ИСТИНА), если в ней столбец с одним значением, и 0 (ЛОЖЬ), если столбец с несколькими значениями.
П. М. Чеглаков, ведущий аналитик АО «Тандер»
Материал публикуется частично. Полностью его можно прочитать в журнале «Планово-экономический отдел» № 10, 2018.
Добавьте таблицы поиска в свои наборы данных с помощью этого изобретательного дополнения
У вас есть данные и их много. Если вы хотите проанализировать все эти данные, узнайте, как использовать надстройку Power Pivot с Excel для импорта наборов данных, определения связей, создания сводных таблиц и создания сводных диаграмм.
Инструкции в этой статье применяются к Excel 2019, 2016, 2013 и Excel для Office 365.
Как получить надстройку Excel Power Pivot
Power Pivot предоставляет вам мощное приложение для анализа и анализа бизнес-данных. Вам не нужно специализированное обучение для разработки моделей данных и выполнения расчетов. Вам просто нужно включить его, прежде чем вы сможете его использовать.
Откройте Excel .
Выберите Файл > Параметры .
Выберите Надстройки .
Откройте раскрывающееся меню Управление и выберите Надстройки COM .
Выберите Перейти .
Выберите Microsoft Power Pivot для Excel .
Выберите ОК . Вкладка Power Pivot добавлена в Excel.
Следуйте вместе с учебником
Если вы хотите быстро приступить к работе с Power Pivot, учитесь на примере. У Microsoft есть несколько примеров наборов данных, доступных для бесплатной загрузки, которые содержат необработанные данные, модель данных и примеры анализа данных. Это отличные инструменты обучения, которые позволяют понять, как профессионалы анализируют большие данные.
В этом руководстве используется образец рабочей книги Microsoft Student Data Model. В первой заметке на странице вы найдете ссылку для загрузки учебного пособия и заполненной модели данных.
Данные в этом образце книги Excel имеют следующее:
- Рабочая тетрадь содержит четыре рабочих листа.
- Каждый лист содержит связанные данные, то есть, по крайней мере, один заголовок столбца на листе соответствует заголовку столбца в другом листе.
- Данные в каждом рабочем листе форматируются в виде таблицы.
- Каждая ячейка в таблице содержит данные. В таблицах нет пустых ячеек, строк или столбцов.
Есть другие примеры наборов данных на веб-сайте Microsoft. Изучите эти учебные ресурсы:
- Загрузите данные из базы данных Microsoft Access, в которой описаны олимпийские медали.
- Загрузите три образца Business Intelligence, которые показывают, как использовать Power Pivot для импорта данных, создания отношений, создания сводных таблиц и проектирования сводных диаграмм.
Перед использованием любого набора данных очистите его. Используйте функцию CLEAN в Excel, чтобы избавиться от непечатных символов, запустить проверку орфографии, удалить дублирующиеся строки данных, преобразовать числа и даты в нужный формат и перегруппировать данные.
Как добавить данные в файл Excel и построить модель данных
Вы собрали данные, которые вам нужны. Теперь пришло время импортировать ваши наборы данных в Excel и автоматически создать модель данных. Модель данных похожа на реляционную базу данных и предоставляет табличные данные, используемые в сводных таблицах и сводных диаграммах.
Если вам нужны данные для школьного задания, рабочего проекта или для выполнения этого учебного руководства, вы найдете отличные общедоступные наборы данных на GitHub.
Чтобы импортировать данные Excel в модель данных Power Pivot:
Откройте пустой лист и сохраните файл с уникальным именем.
Выберите Данные , затем выберите Получить данные > Из файла > Из рабочей книги , чтобы открыть Импорт данных диалоговое окно.
В Excel 2013 выберите Power Query > Получить внешние данные и выберите источник данных.
Перейдите в папку, содержащую файл Excel, выберите файл, затем выберите Импорт , чтобы открыть навигатор.
Установите флажок Выбрать несколько элементов .
Выберите таблицы, которые вы хотите импортировать.
При импорте двух или более таблиц Excel автоматически создает модель данных.
Выберите Загрузить , чтобы импортировать таблицы данных в модель данных.
Чтобы убедиться, что импорт прошел успешно и модель данных была создана, перейдите в раздел Данные и в группе Инструменты данных выберите Перейти в окно Power Pivot .
Окно Power Pivot отображает ваши данные в формате рабочего листа и состоит из трех основных областей: таблица данных, область расчета и вкладки таблицы данных.
Вкладки в нижней части окна Power Pivot соответствуют каждой из импортированных таблиц.
Закройте окно Power Pivot.
Если вы хотите добавить новые данные в модель данных, в окне Excel перейдите на Power Pivot и выберите Добавить в модель данных . Данные отображаются в виде новой вкладки в окне Power Pivot.
Создание связей между таблицами с помощью Power Pivot Excel
Теперь, когда у вас есть модель данных, пришло время создать отношения между каждой из таблиц данных.
Выберите Power Pivot , затем выберите Управление , чтобы открыть окно Power Pivot.
Выберите Главная , затем выберите Вид диаграммы .
Импортированные таблицы отображаются в виде отдельных блоков в представлении схемы. Перетащите, чтобы переместить таблицы в другое место. Перетащите угол окна, чтобы изменить его размер.
Перетащите заголовок столбца из одной таблицы в другую или в таблицы, содержащие одинаковый заголовок столбца.
Продолжайте сопоставлять заголовки столбцов.
Выберите Главная , затем выберите Просмотр данных .
Как создавать сводные таблицы
Когда вы используете Power Pivot для создания модели данных, большая часть тяжелой работы, включающей сводные таблицы и сводные диаграммы, была выполнена для вас. Отношения, которые вы создали между таблицами в вашем наборе данных, используются для добавления полей, которые вы будете использовать для создания сводных таблиц и сводных диаграмм.
В окне Power Pivot выберите Главная , затем выберите Сводная таблица .
В диалоговом окне Создать сводную таблицу выберите Новый лист , затем выберите ОК .
На панели Поля сводной таблицы выберите поля, которые нужно добавить в сводную таблицу. В этом примере создается сводная таблица, которая содержит имя учащегося и его среднюю оценку.
Чтобы отсортировать данные сводной таблицы, перетащите поле в область «Фильтры». В этом примере поле «Имя класса» добавляется в область «Фильтры», поэтому список можно отфильтровать, чтобы показать среднюю оценку ученика для класса.
Чтобы изменить метод расчета, используемый полем в области «Значения», выберите раскрывающийся список рядом с именем поля и выберите Настройки поля значения . В этом примере сумма оценки была изменена на среднюю оценку.
Проанализируйте ваши данные. Поэкспериментируйте с фильтрами и отсортируйте данные, используя стрелки раскрывающегося списка заголовков столбцов.
Преобразовать сводную таблицу в сводную диаграмму
Если вы хотите визуализировать данные сводной таблицы, превратите сводную таблицу в сводную диаграмму.
- Выберите сводную таблицу, затем перейдите в Анализ инструментов сводной таблицы .
- Выберите Сводная диаграмма , чтобы открыть диалоговое окно Вставить диаграмму .
- Выберите диаграмму, затем нажмите ОК .
Создать сводные диаграммы
Если вы предпочитаете анализировать данные в визуальном формате, создайте сводную диаграмму.
В окне Power Pivot выберите Главная , затем выберите стрелку раскрывающегося списка Сводная таблица . Появится список параметров.
Выберите Сводная диаграмма .
Выберите Новый лист и выберите ОК . Заполнитель PivotChart появится на новом листе.
Перейдите на страницу Анализ инструментов сводной диаграммы и выберите Список полей , чтобы отобразить панель полей сводной диаграммы.
Перетащите поля для добавления в сводную диаграмму. В этом примере создается сводная диаграмма, показывающая среднюю оценку для классов, отфильтрованных по семестрам.
Проанализируйте ваши данные. Поэкспериментируйте с фильтрами и отсортируйте данные с помощью стрелок раскрывающегося списка заголовков столбцов.
Читайте также: