Как сделать меру в excel power pivot
В этой статье мы разберемся в основах создания формул вычислений для вычисляемого столбца и меры в Power Pivot. Если вы еще не знаете, как использовать DAX, ознакомьтесь со краткой и краткой информации: основы DAX за 30 минут.
Основы формул
Power Pivot содержит выражения анализа данных (DAX) для создания пользовательских вычислений в Power Pivot таблицах и в Excel таблицах. DAX включает некоторые функции, которые используются в формулах Excel, а также дополнительные функции, предназначенные для работы с реляционными данными и выполнения динамической агрегирования.
Вот некоторые основные формулы, которые можно использовать в вычисляемом столбце:
Вставка сегодняшней даты в каждую строку столбца.
Вставляет значение 3 в каждой строке столбца.
Добавляет значения в той же строке [Столбец1] и [Столбец2] и помещает результаты в ту же строку вычисляемого столбца.
Формулы для вычисляемой Power Pivot можно создавать так же, как формулы в Microsoft Excel.
При создании формулы с помощью следующих действий:
Каждая формула должна начинаться со знака равно.
Вы можете ввести или выбрать имя функции или выражение.
Начните ввести первые несколько букв нужной функции или имени, и автозаполнеть отобразит список доступных функций, таблиц и столбцов. Нажмите tab, чтобы добавить в формулу элемент из списка автозаполнений.
Чтобы предоставить функции аргументы, выберите их из списка возможных таблиц и столбцов либо введите значения или другую функцию.
Проверьте синтаксис: убедитесь, что все скобки закрыты, а столбцы, таблицы и значения ссылались правильно.
Нажмите клавишу ВВОД, чтобы подтвердить формулу.
Примечание: Как только вы примете формулу в вычисляемом столбце, он будет заполнен значениями. При нажатии на меру нажатие ввод сохраняет определение меры.
Создание простой формулы
Создание вычисляемго столбца с помощью простой формулы
Mini Battery Charger
Telephoto Conversion Lens
Вы можете выбрать и скопировать данные из таблицы выше, включая заголовки таблицы.
В Power Pivot щелкните Главная> В paste.
В диалоговом окне В режиме предварительного просмотра нажмите кнопку ОК.
Щелкните Конструктор> столбцов и>добавить.
Введите следующую формулу в формуле над таблицей.
Нажмите клавишу ВВОД, чтобы подтвердить формулу.
Затем значения заполняются в новом вычисляемом столбце для всех строк.
Советы по использованию функции автозаполнения
Функцию автозаполнения формул можно использовать в середине существующей формулы со вложенными функциями. Текст, расположенный непосредственно перед точкой вставки, используется для отображения значений раскрывающегося списка, а остальной текст остается без изменений.
Power Pivot не добавляет закрываюю скобки функций и не автоматически соответствует скобкам. Необходимо убедиться, что каждая функция синтаксически правильна или не удается сохранить или использовать формулу. Power Pivot выделяет скобки, что упрощает проверку правильности их закрытия.
Работа с таблицами и столбцами
Power Pivot таблицы похожи на Excel, но отличаются в том, как они работают с данными и формулами:
Формулы в Power Pivot работают только с таблицами и столбцами, а не с отдельными ячейками, ссылками на диапазоны или массивами.
В формулах можно использовать связи для получения значений из связанных таблиц. Полученные значения всегда связаны со значением текущей строки.
Невозможно в Power Pivot формулы на Excel и наоборот.
Вы не можете иметь неравных или неровных данных, как на Excel. Каждая строка в таблице должна содержать одинаковое количество столбцов. Однако в некоторых столбцах могут быть пустые значения. Excel и таблицы Power Pivot данных не являются взаимозаменяемыми, но вы можете связать их с таблицами Excel из Power Pivot и в Excel в Power Pivot. Дополнительные сведения см. в дополнительных сведениях Добавление данных из таблицы в модель данных с помощью связанной таблицы и Копирование и добавление строк в модель данных в Power Pivot.
Ссылки на таблицы и столбцы в формулах и выражениях
Вы можете ссылаться на любую таблицу и столбец, используя их имена. Например, следующая формула показывает, как ссылаться на столбцы из двух таблиц с использованием полного имени:
=SUM('New Sales'[Amount]) + SUM('Past Sales'[Amount])
Примечание: Контекст — это важная функция Power Pivot данных, которая позволяет создавать динамические формулы. Контекст определяется таблицами в модели данных, отношениями между ними и примененными фильтрами. Дополнительные сведения см. в статье Контекст в формулах DAX.
Связи между таблицами
Таблицы могут быть связаны с другими таблицами. Создавая связи, вы получаете возможность искать данные в другой таблице и использовать связанные значения для выполнения сложных вычислений. Например, можно воспользоваться вычисляемым столбцом, чтобы найти все записи об отправке, относящиеся к текущему посреднику, а затем просуммировать стоимость доставки для каждой из них. Результат выглядит как запрос с параметрами: вы можете вычислить разную сумму для каждой строки в текущей таблице.
Многие функции DAX требуют на то, чтобы между таблицами или несколькими таблицами существовала связь, чтобы найти столбцы, на которые вы ссылались, и получить нужные результаты. Другие функции попытались определить связь; однако для лучших результатов всегда следует создать связь по возможности.
При работе со сводными таблицами особенно важно соединить все таблицы, используемые в сводной таблице, чтобы итоговые данные можно было вычислять правильно. Дополнительные сведения см. в статье Работа со связями в сводных таблицах.
Устранение ошибок в формулах
Если при определении вычисляемого столбца вы получаете ошибку, формула может содержать синтаксическую или семантическую ошибку.
Синтаксические ошибки устранять проще всего. Они обычно вызваны пропущенной скобкой или запятой. Справку по синтаксису отдельных функций см. в справочнике по функциям DAX.
Ошибки другого типа возникают, когда синтаксис задан правильно, но значение упоминаемого столбца не имеет смысла в контексте формулы. Такие семантические ошибки могут быть вызваны одной из следующих проблем:
Формула ссылается на несуществующий столбец, таблицу или функцию.
Формула кажется правильной, но когда Power Pivot извлекает данные, обнаруживает несоответствие типов и вызывает ошибку.
Формула передает функции неверное число или тип параметров.
Формула ссылается на другой столбец, который содержит ошибку, поэтому ее значения недопустимы.
Формула ссылается на столбец, который не был обработан. Это может произойти, если вы изменили режим книги вручную, внося изменения, а затем не обновляли данные и не обновляли вычисления.
В первых четырех случаях DAX помечает весь столбец, содержащий недопустимую формулу. В последнем случае DAX выделяет имя столбца серым цветом, чтобы показать, что он находится в необработанном состоянии.
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 – формула СУММ .
Синтаксис формулы очень простой:
SUM — это базовая формула, а всё потому что вычисления, связанные с цифрами, в DAX делаются с помощью мер. Нельзя просто так взять и обратиться к цифрам какого-то столбца напрямую. Придется это сделать с помощью какой-то агрегирующей формулы, чаще всего – с помощью SUM . Так что эта формула не только считает сумму, без нее в принципе мало какие расчёты работают )
2. BLANK
Формула BLANK возвращает пустое значение. Пустое значение в DAX – это отсутствие значения, а не привычный нам в Excel 0 (ноль) или пустая строка («»).
Записывается формула очень просто:
Никаких аргументов у нее нет.
Формулы 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 позволяет выполнять самые разные вычисления. Примеры таких вычислений можно посмотреть в следующих статьях.
Меры в Power Pivot. Что это и как создавать? Способы создания, организация хранения записей. Меры, для представления, можно сравнивать с вычисляемыми полями сводной таблицы Excel.
Данная статья опубликована в рамах серии видеороликов посвященных моделированию и анализу данных в Power Pivot по материалам книги от Microsoft «Profit and Loss Data Modeling and Analysis with Microsoft PowerPivot in Excel» (Разработка модели данных и анализ прибыли и убытков в PowerPivot Excel) по данным условной компании Contoso Ltd.
В материале показывается способы создания мер, хранения записей в модели данных.
Далее дополнительно цитирую официальное пояснение о вычислениях в Power Pivot:
Меры, также называемые мерами в Power Pivot в Excel 2013, являются вычислениями, используемыми при анализе данных. К примерам, часто встречающимся в бизнес-отчетах, относятся суммы, средние, минимальные и максимальные значения, счетчики или более сложные вычисления, создаваемые с использованием формулы выражений анализа данных (DAX).
В сводной таблице, сводной диаграмме или отчете мера помещается в область "Значения", в которой метки строки и столбца, окружающие ее, определяют контекст значения. Например, при измерении продаж по годам (в столбцах) и регионам (в строках) значение меры вычисляется в зависимости от данного года и региона. Значение меры всегда изменяется в ответ на выбор строк, столбцов и фильтров, обеспечивая нерегламентированный просмотр данных.
Хотя меры и вычисляемые столбцы похожи тем, что основаны на формуле, они отличаются в использовании. Меры чаще всего используются в области Значения сводной таблицы или сводной диаграммы. Вычисляемые столбцы применяются, когда необходимо разместить результаты вычислений в другой области сводной таблицы (например, в строке или столбце сводной таблицы или на оси сводной диаграммы). Очень важно понимать, когда следует использовать меры, а когда — вычисляемые столбцы. Дополнительную информацию см. в разделах Вычисления в Power Pivot и Вычисляемые столбцы в Power Pivot.
Написать комментарий
В прошлой статье я попытался объяснить что же из себя представляет Power Pivot и в каких случаях его можно использовать.
Уверен что Вам также как и мне не терпится скорее познакомиться со всеми возможностями этого монстра поближе. Однако, чтобы творить чудеса с данными в Power Pivot, нужно сначала понять как эти данные в него загружать. Так что, в этой статье мы с Вами будем изучать процесс "кормёжки".
- из буфера обмена - любые скопированные данные, имеющие форму таблицы;
- из текстового файла;
- из баз данных, вроде MS Access и SQL Server;
- из сервисов Microsoft наподобие Share Point и Azure;
- из интернета;
Самый простой способ добавления данных в Power Pivot. Нужно лишь выделить на листе Excel диапазон с данными и на вкладке Power Pivot нажать на кнопку "Добавить в модель данных" .
Появится окно "Создание таблицы". Ставим галочку, указывая что наша таблица имеет заголовки и жмем ОК.
После этого сразу же открывается окно Power Pivot.
- Перед добавлением диапазона в модель данных Power Pivot сначала преобразует его в умную таблицу;
- Для каждого диапазона добавляемого в модель данных создаётся отдельная вкладка;
- Вкладке присваивается название умной таблицы.
Так как умная таблица была создана автоматически то и имя ей было присвоено соответствующее. Поэтому лучше заранее преобразовать наш диапазон в умную таблицу и присвоить этой таблице то имя, которое нас больше всего устраивает.
Обратите внимание на значок рядом с названием вкладки. Наличие этого значка говорит о том, что эта таблица является связанной, т.е. что исходные данные находятся в той же книге в которой открыт Power Pivot
Преимущества загрузки данных из 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 с помощью данного метода таблицу можно добавлять дополнительные строки, но невозможно добавить дополнительные столбцы;
- Отсутствует возможность обновления данных.
В Power Pivot можно загружать данные из любого txt-файла, но авторы книги использовать именно csv-файл.
Прежде всего создадим таблицу на листе Excel и сохраним её в качестве csv-файла - "Файл>Сохранить как", выбираем тип файла - "CSV (разделители-запятые)(*.csv).
После этого закрываем текущую книгу Excel, открываем новую, заходим в окно Power Pivot и на вкладке "Главная" выбираем "Из других источников > Текстовый файл".
Далее, указываем путь к нашему csv-файлу, выбираем в качестве разделителя столбцов "Точка с запятой (;)" и опять-таки используем в первую строку в качестве заголовков столбцов.
- Подходит для работы с большими объёмами данных;
- Можно добавлять дополнительные строки и столбцы;
- В книге в качестве недостатка данного метода приводится то что заголовки столбцов в таком формате файла легко могут быть изменены, что может вызвать проблемы с обновлением данных в Power Pivot. Но лично мне кажется, что вероятность возникновения данной ситуации равна нулю. Формат CSV именно тем и хорош, что при двойном нажатии файл открывается в окне Excel, так что содержимое файла удобно изменять при этом не нарушая его структуру.
На ленте инструментов Power Pivot, на вкладке "Конструктор", спрятана очень важная кнопка - "Свойства таблицы".
Данная кнопка позволяет изменять свойства загруженной таблицы. Например, отображать или скрывать дополнительные столбцы, или же отображать только определённые отфильтрованные строки.
Также, на вкладке "Главная" находится ещё одна полезная кнопка - "Существующие соединения", которая позволяет настраивать параметры текущих соединений.
В этой статье я описал основные и общедоступные методы загрузки данных в Power Pivot. Из описанных способов самым быстрым, лично для меня является загрузка из Excel, самым удобным - загрузка из текстового файла.
Тем не менее, советую Вам самим на практике изучить методы "кормёжки" и выбрать наиболее подходящие именно для Вас.
Те из Вас, кто знаком со сводными таблицами наверняка знают о "Вычисляемых полях". Они дают возможность проводить дополнительные вычисления в самой сводной таблице. Но их функционал настолько ограничен и ими так неудобно пользоваться, что я всегда предпочитал проводить дополнительные вычисления в самой таблице данных.
Тем не менее, я всегда задавался вопросом, почему Microsoft не могут доработать этот инструмент до конца, вооружив его всем арсеналом существующих функций Excel, чтобы пользователи имели возможность проводить все свои дополнительные вычисления в сводной таблице. Недавно я узнал ответ на этот вопрос.
Вот что пишет Rob Collie в книге Power Pivot and Power BI: The Excel User's Guide to the Data Revolution:
"У формул сводной таблицы совершенно отдельный от основных формул (тех что используются на листе Excel) движок. Каждый раз, когда наступало время планировать новую версию Excel, нам приходилось решать на что тратить выделенные нам средства. У нас никогда не возникало долгих прений по поводу того инвестировать ли выделенные средства в видимые всем атрибуты, наподобие формул листа Excel или же менее явные. Движок формул сводной таблицы был предан забвению и никогда по-настоящему не улучшался."
К счастью с появлением Power Pivot у нас теперь есть полноценный движок формул для работы со сводными таблицами. Так что "Меры" можно охарактеризовать как "формулы которые мы добавляем к сводной таблице". Эти формулы дают нам небывалую мощь и гибкость а преимуществ от их использования гораздо больше чем кажется на первый взгляд.
Добавляем нашу первую меру
- в окне Excel, через боковую панель "Поля сводной таблицы";
- в окне Power Pivot (в области вычислений)
Думаю, здесь нужно отметить, что оба метода добавления мер эквивалентны. Т.е. независимо от того какой метод Вы используете для создания меры, в конечном итоге эта мера будет добавлена в модель данных Power Pivot.
Для начала создадим сводную таблицу из нашей модели данных Power Pivot, что также можно сделать двумя способами:
- Нажав на кнопку "Сводная таблица" на вкладке "Главная" в окне Power Pivot;
В результате мы получим пустую сводную таблицу на новом листе.
Обратите внимание, что список полей нашей сводной содержит все три таблицы данных из окна Power Pivot. Для меня сам факт того, что у нас появляется возможность полноценно работать сразу с несколькими таблицами данных в одной сводной таблице уже является фантастикой.
Теперь приступим непосредственно к созданию новой меры, что опять-таки можно сделать двумя способами:
- Выбрав "Меры > Создать меру . " на вкладке "Power Pivot";
2. Кликнув правой кнопкой мышки по названию таблицы данных в списке полей сводной таблицы и выбрав пункт "Добавить меру . ".
Появится окно с заголовком "Меры", которое мы в дальнейшем будем называть просто "редактором мер".
Обратите внимание на поле со знаком " tr_bq"> = SUM ((t_sales[СуммаПродаж])
Как видите кнопка "ОК" ещё неактивна, и чтобы её активировать нам нужно подобрать для нашей меры соответствующее имя и ввести его в поле "Имя меры:".
Жмём "ОК" и перетаскиваем нашу меру в поле "Значения".
Поэкспериментируем с нашей новой мерой ещё. Перетащим НомерМесяца в поле "Строки" и Год в поле "Столбцы".
Как видим наша мера работает нормально.
"Явные" и "Неявные" меры
Пока, что мы не сделали ничего выдающегося. Т.е. тот же самый результат можно было получить просто перетащив в поле "Значения" столбец СуммаПродаж.
В данном, конкретном случае мы можем получить желаемый результат двумя способами:
- Явной мерой - создав нужную формулу в редакторе мер;
- Неявной мерой - просто перетащив нужный столбец в поле "Значения".
НИКОГДА не пользуйтесь неявными мерами! Хоть даже если это и проще, лучше запустить редактор мер и написав формулу создать нужную меру. Вы получите гораздо больше выгоды от использования явной меры.
Создаём Меры со ссылкой на другие Меры
А теперь создадим новую меру, используя те меры, которые были созданы нами ранее.
Мы можем убрать из поля "Значения" прежние меры и переместить туда нашу новую меру.
Давайте вернём [Прибыль] обратно в поле значения
И чуть изменим нашу формулу увеличив прибыль на 10%
У вас должна вылезти ошибка.
Проблема в запятой. Насколько я понял, в отличие от движка формул Excel, движок DAX не локализирован. В нём используются английские имена функций а также принятая в США система отделения целой части числа от дробной не запятой а точкой.
Изменяем запятую на точку и жмём "ОК".
Посмотрите внимательно, значения в столбцах "Процент Прибыли" тоже изменились.
Другие выгоды от использования мер
До сих пор мы работали лишь с одной сводной таблицей. Но если мы создадим совершенно новую сводную таблицу, все меры которые мы создали в первой сводной будут доступны и в новой.
Читайте также: