Excel powerpivot курси. За допомогою PowerPivot у Microsoft Excel. Основні відомості про агрегати Power Pivot

Ті, хто часто користується Зведеними таблицями, вже змогли оцінити наскільки потужним інструментомВони є. Однак, на жаль, зведені таблиці не можуть робити звіти з різних джерел даних, бази даних або файли. Також, коли ми оперуємо великими обсягами даних, наші звичайні комп'ютерипочинають не справлятися з обробкою цих даних, і починають "гальмувати" чи взагалі зависають. Це стосується таблиць, що мають понад 300 тис. рядків. Отже, щоб вирішити всі ці проблеми, корпорація Microsoft розробила безкоштовний додатокдо Excel, яке розширює можливості звичайних Зведених таблиць.

Тому, представляємо вам надбудову для MS Excel, яка дозволить створювати супертаблиці - Power Pivot .

То що таке PowerPivot?

Power Pivot- це додаткова надбудова для Excel, яка розширює функціонал Зведених таблиць (Pivot Tables). Використовується лише для MS Office 2010. нової версії Office 2013 PowerPivotвже є невід'ємним елементом Excel.

Як встановити PowerPivot?

Перед встановленням PowerPivotвам необхідно (для Windows XP):

Після інсталяції PowerPivot в Excel з'являється додаткова вкладка:

Натиснувши PowerPivot WindowВідкриється окреме вікно, в яке можна завантажувати дані з різних джерел.

Після завантаження даних з різних файлів Excelу вкладку PowerPivot Window, ви зможете працювати з ними, і на звичайному аркуші Excel, тобто. додавати колонки, робити обчислення, використовуючи формули. Також, з'являється унікальна як для Excel"я можливість робити зв'язки між завантаженими таблицями як в Access. Використовуючи таблиці зі зв'язками, ми можемо робити Зведену таблицю, яка братиме дані з різних таблиць, без попереднього їх ручного об'єднання, без багаторазового використання VLOOKUP (ВПР)просто і швидко.

Натиснувши на зображення Зведеної таблиці, ми отримуємо можливість почати її формувати відповідно до наших потреб. Таким чином, ми отримаємо такий макет таблиці з доступними всіма даними, які ми попередньо підготували і завантажили:

Також, як ми бачимо, у Зведений таблиціз'являються нові додаткові поля, які називаються Візуальними фільтрами (Slicers).

Ще однією важливою особливістю розширених Зведених таблицьє прискорена обробка великих таблиць (фільтрація, сортування тощо), які займають понад 1 млн. рядків, що у стандартному Excelє майже неможливим.

Пропонуємо також переглянути відео, за інструментами самостійного аналізу даних у PowerPivot.

Мільйони користувачів використовують формули Excel для виконання розрахунків. Ці розрахунки можуть бути простими, як складання стовпців з числами, або набагато складнішими, наприклад, моделювання бізнес-процесів. Але у будь-якому разі, кожна формула будується за допомогою основних операторів та функцій, які є будівельними блоками таких формул.

PowerPivot є для Excel будівельним блоком, необхідним для створення рішень бізнес-аналітики. Незалежно від того, чи використовує це рішення прості розрахунки або щось набагато складніше. PowerPivot включає можливість імпорту таблиць з широкого спектру джерел даних, виконувати швидкі розрахунки великих обсягів даних, використовувати DAX формули (Вирази аналізу даних) і створювати звіти зведених таблицьна основі отриманої інформації.

Вирази DAX дуже схожі з формулами Excel. Але, незважаючи на те, що список функцій DAX багато в чому співпадає зі списком Excel, у виразах аналізу існують нові функції, яких у Excel немає. Ці функції призначені можливості аналізу даних, зокрема, для зв'язування таблиць і динамічного аналізу. Здатність створювати розрахунки, які динамічно оцінюватимуться у різних контекстах, є потужним інструментом. До PowerPivot і DAX, такі розрахунки часто вимагали знання концепції багатовимірного програмування.

Введення в PowerPivot надбудову

Після встановлення на стрічці з'явиться нова вкладка PowerPivot.

Клацніть на ВікноPowerPiwotзапустить нову вкладку PowerPivotдляExcel.

Давайте зупинимося, щоб визначитися, як PowerPivot і Excel доповнюють один одного.

PowerPivot має вбудований алгоритм обробки даних, який дозволяє 1) зберігати і обробляти б пробільші обсяги даних, ніж доступно в Excel; 2) імпортувати дані з різних джерел даних; 3) моделювати операції, такі як визначення зв'язків між таблицями та створення DAX формул.

Надбудова PowerPivot дляExcel(див. вище) має Excel-орієнтований інтерфейс користувача.

Excel має широкий спектр інструментів візуалізації, таких як , зведені діаграми і . Також їх можна використовувати для роботи з даними PowerPivot.

Excel документ є чудовим способом упаковки даних та візуалізації. Це означає, що все, що ви бачите у вікні надбудови зберігається всередині вашої книги. Що полегшує управління та обмін даними.

Імпорт даних

Звичайно, все починається з даних, і тому PowerPivot дозволяє імпортувати дані з різних джерел. Нижче наведено невеликий фрагмент списку можливих ресурсів.

Залежно від типу джерела, ви можете вибрати необхідні таблиці, переглядати та фільтрувати поля таблиць та/або використовувати запит для імпорту.

Копіювати вставити

Часто користувачам потрібно об'єднати дані, що зберігаються в PowerPivot, з невеликим об'ємом даних на аркуші. Це можна зробити за допомогою створення зв'язків між таблицями.

Робота з таблицями

Після того, як дані були імпортовані, вони відображаються у вікні PowerPivot дляExcel.

Якщо джерела даних мають зв'язок між таблицями, PowerPivot підтягне їх автоматично. В іншому випадку ви можете створити їх вручну.

Навіщо створювати зв'язок між таблицями? Такі відносини дозволяють коректно орієнтуватися та групувати дані, що виключає необхідність зводити пов'язані стовпці в одну велику таблицю.

Розрахунки

Кінцева мета PowerPivot дляExcelзробити аналіз даних простим. На відміну від продуктів аналізу даних, призначених для IT-фахівців (наприклад, SQL Server Analysis Services), PowerPivot призначений для людей, які використовують зведені таблиці у своїй щоденній роботі. Ідея полягає в тому, щоб користувачі могли застосовувати наявні навички Excel без необхідності вивчення спеціалізованих мов.

If(>100000000, "Відмінно", if(>10000000, "Добре", "Погано"))

Тому під час роботи можна зустріти знайомі формули, такі як ABS, AVERAGE, AVERAGEA (думаю, згодом їх також переведуть російською мовою). Також є абсолютно нові: ALL, ALLEXEPT, AVERAGEX.

Візуалізація

Для створення багатофункціональних , PowerPivot використовує інструменти Excel для візуалізації. На щастя у Excel цього добра достатньо: зведені таблиці, зведені діаграми та зрізи.

Після декількох хвилин форматування та розміщення елементів керування, у мене вийшла функціональна автономна книга, яка може працювати як програма.

Підсумок

Отже, що ми маємо? PowerPivot для Excel – це надбудова, яка використовується для додавання та аналізу великих обсягів даних. У Excel книганадає всі можливості візуалізації даних та взаємодії з ними. Навички складання звітів за допомогою зведених таблиць дозволять створювати складні книги, що ґрунтуються на складних аналітичних даних Excel.

office.microsoft

Менш ніж за годину ви навчитеся створювати в Excelзвіти зведеної таблиці, що поєднують дані з кількох таблиць. Перша частина цього підручника допоможе вам імпортувати дані та вивчити їх. У другій частині ви навчитеся уточнювати модель даних, що лежить в основі звіту, додавати до звітів Power View нові обчислення та ієрархії, а також оптимізувати їх за допомогою надбудови Power Pivot.

Почнемо з імпорту даних.

1. Завантажити зразки даних (ContosoV2) для цього підручника. Вийміть файли даних та збережіть їх у зручному місці, наприклад, у папці "Завантаження" або "Мої документи".
2. Відкрийте порожню книгу в Excel.
3. Виберіть Дані > Отримання зовнішніх даних > З Access.
4. Перейдіть до папки з файлами зразків даних і виберіть ContosoSales.
5. Натисніть Відкрити. Оскільки ви підключаєтеся до файлу бази даних, який містить кілька таблиць, з'явиться діалогове вікно Вибір таблиці, де можна вибрати таблиці, які потрібно імпортувати.

1. У діалоговому вікні "Вибір таблиці" встановіть прапорець Дозволити вибір кількох таблиць.
2. Виберіть усі таблиці та натисніть кнопку ОК.
3. На вкладці "Імпорт даних" виберіть Звіт зведеної таблиціта натисніть кнопку ОК.

ПРИМІТКА.Вітаємо! Ви щойно створили модель даних. Модель – це рівень інтеграції даних, який створюється автоматично, коли ви імпортуєте кілька таблиць або працюєте з ними одночасно в одному звіті зведеної таблиці.

Модель практично не видно в Excel, але ви можете переглядати та змінювати її безпосередньо за допомогою надбудови Power Pivot. У Excel наявність моделі даних простежується тоді, коли ви бачите набір таблиць у списку полів зведеної таблиці. Існує кілька способів створення моделі.

Перегляд даних у зведеній таблиці

Щоб переглянути дані у зрозумілому вигляді, ви можете перетягнути поля в області Значення, Стовпціі Рядкиу списку полів зведеної таблиці.

1. Перейдіть до списку полів і знайдіть у ньому таблицю FactSales.
2. Виберіть стовпець SalesAmount. Оскільки він містить числові дані, Excel автоматично помістить SalesAmount в область "Значення".
3. З таблиці DimDate перетягніть стовпець CalendarYear в область "Стовпці".
4. З таблиці DimProductSubcategory перетягніть стовпець ProductSubcategoryName в область "Рядки".
5. З таблиці DimProduct перетягніть стовпець BrandName в "Рядки", помістивши його під підкатегорією.

Зведена таблиця має бути схожа на зображену нижче.

Не витрачаючи особливих зусильВи створили зведену таблицю, яка містить поля з чотирьох різних таблиць. Це завдання виявилося настільки легким завдяки заздалегідь створеним зв'язкам між таблицями. Так як зв'язки між таблицями існували в джерелі даних і ви імпортували всі таблиці відразу, програма Excel змогла відтворити ці зв'язки в моделі.

Але що робити, якщо дані походять із різних джерел або імпортуються не одночасно? У загальних випадках ви зможете внести нові дані, створивши зв'язки на основі стовпців, що збігаються. На наступному етапі ви імпортуєте додаткові таблиці та дізнаєтеся про вимоги до нових зв'язків та етапи їх створення.

Додавання додаткових таблиць

Щоб навчитися встановлювати зв'язки, потрібні додаткові незв'язані таблиці. На цьому етапі ви отримаєте дані, що використовуються в цьому підручнику, імпортувавши додаткову базу даних і вставивши дані з двох інших книг.

Додавання категорій продуктів

1. Відкрийте у книзі новий аркуш. У ньому зберігатимуться додаткові дані.
2. Виберіть Дані > Отримання зовнішніх даних > З Access.
3. Перейдіть до папки, що містить файли зразків даних, та виберіть ProductCategories. Натисніть кнопку Відкрити.
4. На вкладці "Імпорт даних" виберіть пункт Таблицята натисніть кнопку ОК.

Додавання географічних даних

1. Додати ще один лист.
2. З файлів зразків даних відкрийте Geography.xlsx, помістіть курсор у полі A1, потім натисніть клавіші CTRL+SHIFT+END, щоб вибрати всі дані.
3. Скопіюйте дані у буфер обміну.
4. Вставте дані в щойно доданий порожній лист.
5. Виберіть Форматувати як таблицюз будь-яким стилем. Форматування даних у вигляді таблиці дозволяє присвоїти ім'я, що дуже зручно при заданні зв'язків у наступному кроці.
6. Переконайтеся, що у вікні "Форматування таблиці" встановлено прапорець Таблиця із заголовками. Натисніть кнопку ОК.
7. Надайте таблиці ім'я Geography. Виберіть вкладку Робота з таблицями > Конструкторта введіть назву Geography у полі "Ім'я таблиці".
8. Закрийте файл Geography.xlsx, щоб видалити його з робочої області.

Додавання даних по магазинах

• Повторіть попередні кроки для файлу Stores.xlsx - вставте вміст у порожній аркуш. Надайте таблиці ім'я Stores.

Тепер вийшло чотири аркуші. Лист1 містить зведену таблицю, лист Лист2 містить ProductCategories, Лист3 містить Geography, а Лист4 - Stores. Оскільки ви вже надали імена всім таблицям, наступний крок - створення зв'язків - буде набагато простіше.

Використання полів із нових імпортованих таблиць

Ви можете прямо зараз використовувати поля з щойно імпортованих таблиць. Якщо Excel не вдається визначити, як внести поле до звіту зведеної таблиці, з'явиться запит на створення зв'язку між таблицями, за допомогою якого нова таблицябуде зіставлена ​​з тією, яка є частиною моделі.

1. Виберіть пункт Усеу верхній частині списку полів зведеної таблиці, щоб переглянути повний списокдоступні таблиці.
2. Перейдіть донизу. Там знаходяться нові таблиці, які ви щойно додали.
3. Розгорніть Stores.
4. Перетягніть стовпець StoreName в область "Фільтри".
5. Зауважте, що Excel видасть запит на створення зв'язку. Це повідомлення з'являється через те, що ви використовували поля таблиці, яка не пов'язана з моделлю.
6. Натисніть Створити, щоб відкрити діалогове вікно "Створення зв'язку".
7. У області "Таблиця" виберіть FactSales. Таблиця FactSales із зразка даних містить докладні відомостіпро продаж та вартість для компанії Contoso, а також ключі інших таблиць, у тому числі коди магазинів, які є у файлі Stores.xlsx, імпортованому на попередньому етапі.
8. В області Стовпець (зовнішній) виберіть StoreKey.
9. В області "Зв'язана таблиця" виберіть Stores.
10. В області "Св'язаний стовпець" виберіть StoreKey.
11. Натисніть кнопку ОК.

Зв'язування ProductSubcategory з ProductCategory

1. В Excel виберіть Дані > Відносини > Створити.
2. В області Таблиця виберіть DimProductSubcategory.
3. В області "Стовпець (зовнішній)" виберіть ProductCategoryKey.
4. В області "Зв'язана таблиця" виберіть Table_ProductCategory.accdb.
5. У розділі "Пов'язаний стовпець (основний)" виберіть ProductCategoryKey.
6. Натисніть кнопку ОК.
7. Закрийте діалогове вікно Управління зв'язками.

Додавання категорій до зведеної таблиці

Хоча модель даних були додані додаткові таблиці і зв'язку, вони ще використовуються у зведеній таблиці. У цій задачі до списку полів зведеної таблиці додається ProductCategory.

1. Усе, щоб відобразити таблиці, які є в моделі даних.
2. Перейдіть донизу.
3. В області "Рядки" видаліть BrandName.
4. Розгорніть вузол Table_DimProductCategories.accdb.
5. Перетягніть ProductCategoryName в область "Рядки", помістивши його над ProductSubcategory.
6. В області полів зведеної таблиці виберіть пункт Активніщоб тільки що використані таблиці стали використовуватися в зведеній таблиці.

Контрольна точка: повторіть вивчений матеріал

Ви створили зведену таблицю, яка містить дані з кількох таблиць, імпортованих на попередньому етапі. Щоб звести дані докупи, потрібно створити зв'язки таблиць, які Excel використовує для узгодження рядків. Ви дізналися, що наявність стовпців з даними про зіставлення необхідне пошуку пов'язаних рядків. У зразку даних усі таблиці містять стовпець, який можна використовувати з цією метою.

Хоча зведена таблиця працює, ви, можливо, помітили деякі недоліки, що залишилися. Список полів зведеної таблиці виглядає так, ніби в ньому є зайві таблиці (DimEntity) та стовпці (ETLLoadID), не пов'язані з компанією Contoso. Крім того, дані з таблиці Geography досі не інтегровані.

Далі: перегляд та розширення моделі за допомогою Power Pivot

У наступній серії завдань ви розширите модель за допомогою надбудови Microsoft Office Power Pivot у Microsoft Excel 2013. Ви дізнаєтеся, що найпростіше створювати зв'язки за допомогою представлення діаграми, передбаченого в цій надбудові. Крім того, вам доведеться використовувати цю надбудову для створення обчислень та ієрархій, приховування елементів, які не повинні відображатися у списку полів, та оптимізації даних для додаткових звітів.

ПРИМІТКА.Надбудова Power Pivot у Microsoft Excel 2013 доступна в Office професійний плюс.

Додати Power Pivotна стрічку Excel, увімкнувши надбудову Power Pivot.

1. Перейдіть на вкладку Файл > Параметри > Надбудови.
2. В полі УправлінняВиберіть Надбудови COM> Перейти.
3. Встановіть прапорець Microsoft Office Power Pivot у Microsoft Excel 2013, а потім натисніть кнопку ОК.

На стрічці з'явиться вкладка Power Pivot.

Додавання зв'язку за допомогою представлення діаграми Power Pivot

1. В Excel виберіть Лист3, щоб зробити його активним. Аркуш3 містить імпортовану раніше таблицю Geography.
2. На стрічці виберіть Power Pivot > Додати до моделі даних. На цьому етапі таблицю Geography буде додано в модель. Також відкриється надбудова Power Pivot, яку можна використовувати для виконання етапів завдання, що залишилися.
3. Зауважте, що у вікні Power Pivot відображаються всі таблиці моделі, включаючи таблицю Geography. Перегляньте кілька таблиць. У цій надбудові можна переглядати всі дані, що містяться в моделі.
4. У вікні Power Pivot у розділі "Вид" виберіть Подання діаграми.
5. За допомогою смуги прокручування змініть розмір діаграми таким чином, щоб бачити усі об'єкти в діаграмі. Зверніть увагу, що дві таблиці не пов'язані з іншою діаграмою: DimEntity та Geography.
6. Клацніть правою кнопкою миші DimEntity, а потім натисніть видалити. Ця таблиця є частиною вихідної бази даних і не потрібна моделі.
7. Налаштуйте масштаб у таблиці Geography таким чином, щоб було видно всі поля. Можна збільшити діаграму таблиці за допомогою повзунка.
8. Зверніть увагу, що таблиця Geography містить стовпець GeographyKey. У цьому стовпці є значення, які є унікальними ідентифікаторамикожного рядка таблиці Geography. Давайте визначимо, чи використовують інші таблиці у цій моделі такий самий ключ. Якщо це так, ми зможемо створити зв'язок, який з'єднає таблицю з рештою моделі.
9. Виберіть Знайти.
10. У полі "Пошук метаданих" введіть GeographyKey.
11. Кілька разів натисніть кнопку Знайти далі. Значення GeographyKey буде знайдено у таблицях Geography та Stores.
12. Перетягніть таблицю Geography до таблиці Stores.
13. Перетягніть стовпець GeographyKey у таблиці Stores на стовпець GeographyKey у таблиці Geography. Power Pivot проведе межу між двома стовпцями, що позначає зв'язок.

У цьому завдання ви дізналися новий спосібдодавання таблиці та створення зв'язків. Тепер у вас повністю інтегрована модель, в якій всі таблиці з'єднані та доступні для зведеної таблиці на аркуші Аркуш1.

ПОРАДА.У поданні схеми деякі схеми таблиць повністю розширені та відображають стовпці ETLLoadID, LoadDate та UpdateDate. Ці конкретні поля є частиною вихідного сховища даних Contoso та додані для підтримки операцій вилучення та завантаження даних. У вашій моделі вони не потрібні. Щоб позбутися їх, виділіть та клацніть поля правою кнопкою миші, а потім натисніть видалити .

Створення обчислюваного стовпця

У додатку Power Pivot ви можете використовувати формули DAX, щоб додати обчислення. У цьому завдання ви обчислите підсумковий прибуток і додайте обчислюваний стовпець, який посилається на значення даних інших таблиць. Пізніше ви дізнаєтеся, як використовувати стовпці посилань для спрощення моделі.

1. У вікні Power Pivot поверніться до представлення даних.
2. Дайте таблиці Table_ProductCategories accdb більш зрозуміле ім'я. Ви посилатиметеся на цю таблицю на наступних етапах, і коротке ім'я спростить читання обчислень. Клацніть правою кнопкою миші ім'я таблиці, а потім натисніть Перейменувати, введіть ім'я ProductCategories та натисніть клавішу ENTER.
3. Виберіть FactSales.
4. Виберіть Конструктор > Стовпці > Додати.
5. У рядку формул над таблицею введіть таку формулу. Функція автозаповнення допоможе ввести повні імена стовпців та таблиць та покаже доступні функції. Ви також можете просто клацнути стовпець, і Power Pivot додасть його ім'я до формули.

= - -

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

1. Перейменуйте стовпець, клацнувши правою кнопкою CalculatedColumn1 і вибравши Перейменувати стовпець. Введіть Profit і натисніть клавішу ENTER.
2. Тепер оберіть таблицю DimProduct.
3. Виберіть Конструктор > Стовпці > Додати.
4. У рядку формул над таблицею введіть таку формулу.

RELATED(ProductCategories)

Функція RELATED повертає значення пов'язаної таблиці. У нашому випадку таблиця ProductCategories містить назви категорій продуктів, які потрібно додати до таблиці DimProduct, коли ви збудуєте ієрархію, яка включає відомості про категорії.

1. Коли ви закінчите вводити формулу, натисніть клавішу ENTER, щоб підтвердити її.

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

1. Перейменувати стовпець. Введіть ProductCategory та натисніть клавішу ENTER.
2. Виберіть Конструктор > Стовпці > Додати.
3. У рядку формул над таблицею введіть таку формулу, а потім натисніть клавішу ENTER, щоб підтвердити її.

RELATED(DimProductSubcategory)

1. Перейменуйте стовпець, клацнувши правою кнопкою миші CalculatedColumn1 і вибравши Перейменувати стовпець. Введіть ProductSubcategory та натисніть клавішу ENTER.

Створення ієрархії

Більшість моделей містять дані, що є за своєю природою ієрархічними. Найпоширеніші приклади: дані календаря, географічні дані та категорії продуктів. Створення ієрархій корисно тим, що дозволяє перетягувати елемент (ієрархію) до звіту, а не збирати і впорядковувати одні й самі поля щоразу заново.

1. У Power Pivot перейдіть до представлення діаграми. Розгорніть таблицю DimDate, щоб простіше працювати з її полями.
2. Натисніть і утримуйте клавішу CTRL і клацніть стовпці CalendarYear, CalendarQuarter та CalendarMonth (виконайте прокручування вниз у таблиці).
3. Вибравши три стовпці, клацніть правою кнопкою миші один із них і натисніть кнопку Створити ієрархію. У нижній частині таблиці буде створено батьківський вузол ієрархії Hierarchy 1, а вибрані стовпці будуть скопійовані до ієрархії як дочірні вузли.
4. Введіть ім'я нової ієрархії Dates.
5. Додайте в ієрархію стовпець FullDateLabel. Клацніть правою кнопкою миші FullDateLabel і виберіть пункт Додати до ієрархії. Виберіть пункт Дата. Стовпець FullDateLabel містить дату у повному форматі, включаючи рік, місяць та день. Переконайтеся, що стовпець FullDateLabel з'явився в ієрархії внизу. Тепер у вас є багаторівнева ієрархія, яка включає рік, квартал, місяць та окремі календарні дні.
6. Залишаючись у поданні діаграми, виберіть таблицю DimProduct та натисніть кнопку Створити ієрархіюу заголовку таблиці. У нижній частині таблиці з'явиться порожній батьківський вузол ієрархії.
7. Введіть ім'я нової ієрархії Product Categories.
8. Щоб створити дочірні вузли ієрархії, перетягніть стовпці ProductCategory і ProductSubcategory в ієрархію.
9. Клацніть правою кнопкою миші ProductName і виберіть пункт Додати до ієрархії. Виберіть Product Categories.

Тепер, коли знаєте кілька способів створення ієрархії, скористаємося ними у зведеній таблиці.

1. Поверніться назад до Excel.
2. На Листі1 (цей лист містить зведену таблицю) видаліть поля в області "Рядки".
3. Замініть їх у новій ієрархії Product Categories у DimProduct.
4. Аналогічно замініть CalendarYear в області "Стовпці" ієрархією Dates з DimDate.

Тепер під час перегляду даних легко помітні переваги ієрархій. Ви можете незалежно розгортати та закривати різні області зведеної таблиці, що забезпечує більш повний контроль над використанням доступного місця. Крім того, додавання єдиної ієрархії для областей "Рядки" та "Стовпці" забезпечує можливість миттєвої деталізації без необхідності нагромаджувати кілька полів для отримання аналогічного ефекту.

Приховування стовпців

Після створення ієрархії Product Categories та її розміщення у DimProductDimProductCategory або DimProductSubcategory у списку полів зведеної таблиці більше не потрібні. У цьому завдання ви дізнаєтеся, як приховати зайві таблиці та стовпці, які посідають місце у списку полів зведеної таблиці. Приховування таблиць та стовпців дозволяє оптимізувати роботу зі звітами, не торкаючись моделі, яка надає зв'язки та обчислення даних.

Можна приховати як окремі стовпці, і їх діапазон, і навіть всю таблицю. Імена стовпців та таблиць стають неактивними, що відображає їх прихований стан для клієнтських засобів підготовки звітів, що використовують модель. Приховані стовпці відображаються у моделі сірим, але залишаються видимими у поданні даних, щоб з ними можна було працювати.

1. Переконайтеся, що Power Pivot вибрано представлення даних.
2. На вкладках у нижній частині екрана клацніть правою кнопкою миші DimProductSubcategory та виберіть.
3. Повторіть ProductCategories.
4. Відкрийте середовище DimProduct.
5. Клацніть правою кнопкою миші наступні стовпці та виберіть пункт Приховати у клієнтських засобах.

• ProductKey
• ProductLabel
• ProductSubcategory

1. Виділіть кілька суміжних стовпців, починаючи з ClassID і закінчуючи ProductSubcategory. Клацніть правою кнопкою миші, щоб приховати їх.
2. Повторіть цю дію з іншими таблицями, видаляючи ідентифікатори, ключі та інші докладні відомості, яких не повинно бути у звіті.

Тепер ви розумієте, що, створюючи поля, що обчислюються, ви зможете аналізувати дані незліченною кількістю дієвих способів. Давайте дізнаємось, як створювати ці поля.

Створювати обчислювані поля в Power Pivot легко, якщо скористатися функцією Автосума.

1. В таблиці FactSalesвиберіть стовпець Profit.
2. Виберіть Обчислення > Автосума. Зверніть увагу, що було створено нове поле, що обчислюється, з назвою Sum of Profitв осередку області обчислень прямо під стовпцем Profit.
3. У Excel на Листі1 у списку полів виберіть у таблиці FactSalesобчислюване поле Sum of Profit.

Готово! Як бачите, за допомогою стандартних агрегатних функцій ми всього за кілька хвилин створили в Power Pivot поле Sum of Profit, що обчислюється, і додали його в зведену таблицю. Тепер можна швидко аналізувати прибуток, використовуючи різні фільтри. У цьому випадку ви бачите стовпець Sum of Profit, значення якого відфільтровано згідно з ієрархіями Product Category та Dates.

Але що робити, якщо вам необхідно провести більш поглиблений аналіз, наприклад, підрахувати продажі по певному каналу, продукту чи категорії? Для цього вам потрібно створити інше поле, яке обчислює кількість рядків, по одному для кожного продажу з таблиці FactSales, залежно від використовуваних фільтрів.

1. У таблиці FactSales виберіть стовпець SalesKey.
2. В області Обчисленнянатисніть стрілку вниз під кнопкою Автосума > РАХУНОК.
3. Перейменуйте нове поле, клацнувши правою кнопкою миші стовпець Count of SalesKeyв області обчислень та вибравши команду Перейменувати. Введіть Countта натисніть клавішу ENTER.
4. У Excel на Листі1 у списку полів виберіть FactSalesта натисніть Count.

Зверніть увагу, що до зведеної таблиці було додано новий стовпець Count, який відображає кількість продажів залежно від фільтрів. Як і у випадку з обчислюваним стовпцем Sum of Profit, поле Count, що обчислюється, відфільтровано згідно ієрархіям Product Category і Dates.

Давайте потренуємося ще. На цей раз ви створите поле, яке обчислює відсоток від суми продажів відповідно до конкретного контексту або фільтра. Однак на відміну від попередніх полів, які ви створювали за допомогою функції "Автосума", цього разу необхідно буде ввести формулу вручну.

1. У таблиці FactSales в області обчислень виберіть пусту комірку. Порада: найзручніше буде розміщувати обчислювані поля, починаючи з комірки у лівому верхньому кутку. Таким чином, їх буде легше знайти. Ви можете переміщатися в будь-якому обчислюваному полі в області обчислень.
2. У рядку формул введіть таку формулу, використовуючи IntelliSense: Percentage of All Products:=/CALCULATE(, ALL(DimProduct))
3. Натисніть клавішу ENTER, щоб підтвердити формулу.
4. В Excel на Листі1 у списку полів у таблиці FactSalesВиберіть Percentage of All Products.
5. У зведеній таблиці виберіть кілька стовпців Percentage of All Products.
6. На вкладці ГоловнаВиберіть Число > Відсотковий формат. Для форматування нових стовпців використовуйте два десяткові знаки після коми.

Це нове поле, що обчислюється, обчислює відсоток від суми продажів для заданого контексту фільтра. У нашому випадку фільтрами, як і раніше, виступають ієрархії Product Category і Dates. Серед іншого можна побачити, що відсоток комп'ютерів від суми продажів продуктів з часом збільшився.

Вам не важко створити формули для обчислюваних стовпців і полів, якщо ви знайомі з процедурою створення формул Excel. Але, незалежно від того, чи знайомі ви з формулами Excel чи ні, у вас є чудова можливість вивчити основні формули DAX, пройшовши уроки з електронної книги Короткий посібник: основи DAX за 30 хвилин .

Збереження роботи

Збережіть книгу, щоб можна було використовувати її з іншими підручниками або для подальшого вивчення.

Наступні кроки

Хоча можна легко імпортувати дані з Excel, часто швидше та ефективніше виявляється імпорт за допомогою надбудови Power Pivot. Ви можете відфільтрувати дані, що імпортуються, виключивши непотрібні стовпці. Ви також можете вирішити, чи буде виймати дані за допомогою будівельника запитів або команди запиту. В якості наступного кроку вивчіть такі альтернативні способи: Отримання даних із веб-каналу даних у Power Pivot та Імпорт даних із служб Analysis Services або Power Pivot .

Звіти Power View призначені для роботи з моделями даних, аналогічними щойно побудованій вами. Додаткові матеріали про розширене уявлення, яке Power View забезпечує в Excel: Включення Power View у додатку Excel 2013 та Power View: дослідження, візуалізація та подання даних.

Спробуйте вдосконалити свою модель даних, щоб у подальшому створювати наочніші звіти Power View, вивчивши наступний Підручник. Оптимізація моделі даних для звітів Power View.

Power Pivot є надбудовою, що можна використовувати для проведення глибокого аналізу в Excel. Надбудова вбудована в деяких версіях Office, але не включена за промовчанням.

Список версій Office, включаючи Power Pivot, а також список версій, які не рекомендується, ознайомтеся зі статтею: де PowerPivot?

Нижче описано, як увімкнути Power Pivot перед використанням вперше.

Перейдіть на вкладку Файл > Параметри > Надбудови.

В полі УправлінняВиберіть Надбудови COMта натисніть Перейти.

Встановіть прапорець Microsoft Office Power Pivotта натисніть кнопку ОК. Якщо встановлені інші версії Power Pivot, вони також будуть перераховані у списку надбудов COM. Виберіть надбудову Power Pivot для Excel.

На стрічці з'явиться вкладка Power Pivot.

Відкрийте вікно Power Pivot.

Відкриється вікно Power Pivot. Тут ви можете натиснути кнопку "Зовнішні дані", щоб використовувати майстер імпорту таблиць для фільтрації даних при їх додаванні до файлу, створення зв'язків між таблицями, збагачення даних обчисленнями та виразами та створення зведених таблиць та зведених діаграм на їх основі.

Усунення несправностей: зникнення стрічки Power Pivot

У деяких випадках Power Pivot стрічки з'являться в меню, якщо Excel визначає, що надбудова стабільності в Microsoft Excel. Це може статися, якщо Excel аварійно завершує роботу при відкритому вікні Power Pivot. Щоб відновити меню Power Pivot, зробіть таке:

Виберіть Файл > Параметри > Надбудови.

В полі УправлінняВиберіть Вимкнені об'єкти > Перейти.

Виберіть Microsoft OfficePower Pivotта натисніть кнопку увімкнути.

Якщо не вдається відновити стрічку Power Pivot, виконаючи вказані вище дії, або стрічка зникає, коли ви закриваєте та знову відкриваєте Excel, зробіть таке:

закрити Excel;

відкрийте меню Пуск > Виконатита введіть команду regedit;

У редакторі реєстру розгорніть наступний розділ:

Для Excel 2013: HKEY_CURRENT_USER > Software > Microsoft > Office > 15.0 > User Settings.

Для Excel 2016: HKEY_CURRENT_USER > Software > Microsoft > Office > 16.0 > User Settings

клацніть правою кнопкою миші PowerPivotExcelAddin, а потім натисніть видалити;

поверніться у верхню частину редактора реєстру;

розгорніть розділ HKEY_CURRENT_USER > Software > Microsoft > Office > Excel > Addins;

клацніть правою кнопкою миші PowerPivotExcelClientAddIn.NativeEntry.1, а потім натисніть видалити;

закрийте редактор реєстру;

відкрийте Excel;

увімкніть надбудову, виконавши дії, описані на початку цієї статті.

[у зв'язку зі спірним перенесенням 1 частини посту на geektimes (при тому, що 2-а частина залишилася на хабрі) повертаю 1-у частину на місце]

Працюючи у сфері аналітики та монітору різні інструменти BI рано чи пізно натрапляєш на огляд чи згадку надбудови Power Pivot Excel. У моєму випадку ознайомлення з ним відбулося на конференції Microsoft Data Day.

Особливих вражень після презентації інструмент не залишив. і згодовування в оперативність записів на порядки вище 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% коефіцієнті показує, що ціни дійсно ковбасить навколо середньої:

Сильніше, ніж при коефіцієнті 15%:

Отже, на даному кроціми розглянули основні відмінності заходів від полів у рамках PowerPivot.

Крок 2. Ускладнюємо: Порахуємо частку кожного запису у загальних продажах.
Ось перший приклад порівняння підходів віконних функцій MS SQL Server та DAX:

Зрозуміло, що в рамках зведених таблиць це робиться буквально в 2 кліки мишкою не торкаючись клавіатури, але для розуміння спробуємо це безпосередньо в PowerPivot із застосуванням формул.

На SQL я б це написав так (за огріхи не штовхати, бо Word синтаксис SQL Server не перевіряє):

Begin Select "t1.Ім'я ТТ", "t1.Місто", "t1.Адреса", "t1.Продукт", "t1.№ ТТН", "t1.Дата ТТН", "t1.Відвантаження, шт", " t1.Відвантаження з ПДВ", "t1.Відвантаження, шт"/sum("t1.Відвантаження, шт") over () as share from Table , Шт") desc

Тут, як можна помітити вікно відкривається через всі записи датасету, спробуємо аналогічну річ у PowerPivot:

=[Відвантаження шт]/CALCULATE(SUM([Відвантаження шт]);ALL("Таблиця1"))

Основну увагу звернемо до знаменника: Я вже згадував вище, що основна відмінність поля, що обчислюється, від міри полягає в тому, що в полі формули вважають по горизонталі (в рамках одного запису) а заходи – по вертикалі (в рамках одного атрибуту). Тут ми змогли схрестити властивості поля та властивість міри через метод CALCULATE. І якщо ширину вікна в SQL ми відрегулювали через Over(), то тут ми зробили це через All().

Спробуємо тепер, володіючи даним навичкою, зробити з нашими даними що корисне, наприклад, згадавши що показник розкиду цін навколо середніх варіювався в широкому діапазоні, спробуємо виділити статистичні викиди цін через правило 3-х сигм.

Віконні функції на SQL будуть виглядати так:

Select "t1.Ім'я ТТ", "t1.Місто", "t1.Адреса", "t1.Продукт", "t1.№ ТТН", "t1.Дата ТТН", "t1.Відвантаження, шт", "t1. .Відвантаження з ПДВ", "t1.Ціна за шт без ПДВ", CASE WHEN ABS("t1.Ціна за шт без ПДВ" - AVG("t1.Ціна за шт без ПДВ)) OVER()) > 3 * STDEV ("t1.Ціна за шт без ПДВ") OVER() THEN 1 ELSE 0 END as Outlier from Table as t1 Go

А ось те саме в DAX:

If(ABS([Поле_Ціна за штуку без ПДВ]-CALCULATE(AVERAGE([Поле_Ціна за штуку без ПДВ]));ALL("Таблиця1")))>(3*CALCULATE(STDEV.P([Поле_Ціна за штуку без ПДВ)) );all("Таблиця1")));1;0)

Як бачите, ціна трохи зависока при середній арифметичній 40,03 руб.

Крок 3. Звужуємо вікна.
Спробуємо тепер порахувати в полі кожного запису, що обчислюється, загальна кількість записів у рамках того міста, до якого належить і цей запис.
На MS SQL Serverвіконні функції виглядатимуть так:

Select "t1.Ім'я ТТ", "t1.Місто", "t1.Адреса", "t1.Продукт", "t1.№ ТТН", "t1.Дата ТТН", "t1.Відвантаження, шт", "t1. .Відвантаження з ПДВ", "t1.Ціна за шт без ПДВ", count("t1.*) OVER(partition by "t1.Місто")

У DAX:
=CALCULATE(COUNTROWS("Таблиця1");ALLEXCEPT("Таблиця1";"Таблиця1"[Місто]))

Зверніть увагу на різницю у відображенні даних у таблиці, я спеціально кинув адреси в область заходів, щоб порахувати їх кількість і порівняти з новим полем, яке вивів у заголовок рядків після імені торгової точки.

Виразна видно різниця: якщо звичайний розрахунок кількості адрес йде для кожної точки в місті і потім тільки виводить проміжний підсумок для агрегату «Місто» то використання віконних функцій дозволяє привласнити кожному атомарного запису значення будь-якого агрегату, або використовувати його в якихось проміжних розрахунках поля, що обчислюється (Як було показано вище).

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

Запит на SQL Server:

With a1 as (Select "t1.Ім'я ТТ", "t1.Місто", "t1.Адреса", "t1.Продукт", "t1.№ ТТН", "t1.Дата ТТН", "t1.Відвантаження, шт ", "t1.Відвантаження з ПДВ", "t1.Ціна за шт без ПДВ", count(Distinct "t1.Адреса") OVER(partition by "t1.Місто", "t1.Ім'я ТТ") as adrcnt from Table as t1) Select * from a1 where adrcnt>1

Тепер нам нічого не заважає це зробити і в DAX:

CALCULATE(DISTINCTCOUNT("Таблиця1"[Адреса]);ALLEXCEPT("Таблиця1";"Таблиця1"[Місто];"Таблиця1"[Ім'я ТТ])))

У результаті у нас з'явилася можливість відібрати підозрілі записи, де на ту саму точку в одному місті припадає більше 1 адреси.

Звичайно в процесі вивчення (пробігши поглядом на інші формули) стає зрозуміло що DAX в PowerPivot набагато потужніше ніж показано в даному топіку, але осягнути неосяжне за раз - точно не вийде.

Сподіваюся було цікаво.
Продовження статті