Power Query — надзвичайно корисний інструмент для користувачів Excel, яким потрібно імпортувати зовнішні дані, підключатися до них і формувати їх. Процес додавання інструменту до Excel залежить від того, яку версію ви використовуєте.
Користувачам Excel 2016 або новішої версії не потрібно додавати Power Query до Excel. Він вбудований як функція «Отримати та трансформувати дані» на вкладці «Дані». Користувачі Excel 2013 і 2010 повинні завантажити та встановити надбудову Microsoft Power Query.
Ви будете використовувати таблиці Excel, файли CSV, бази даних і багато інших параметрів. З цього моменту потужні функції дозволяють редагувати, перетворювати й аналізувати дані.
Зміст
Як інсталювати надбудову Power Query до Excel 2010 або 2013
Зауважте, що користувачам Mac потрібно мати принаймні Excel 2016, оскільки Power Query недоступний для попередніх версій Excel в Mac OS. Користувачі Windows можуть використовувати безкоштовну надбудову з Excel 2000.
Перш ніж завантажити надбудову, вам потрібно знати, чи встановлено у вас 32-розрядну чи 64-розрядну версію Excel. Сторінка завантаження запитає, який у вас є.
На зображенні нижче показано вибраний 64-розрядний варіант.
Існує незначна різниця в пошуку цієї інформації між 2010 і 2013 роками. В обох версіях розгорніть меню «Файл» і натисніть опцію «Довідка». 32-бітна/64-бітна інформація міститься в одному з таких розділів:
«Про Microsoft Excel» в Excel 2010
«Про Excel» в Excel 2013
Процес інсталяції надбудови по суті однаковий для обох версій. Щоб установити надбудову Power Query, виконайте такі дії:
Закрийте Excel.
Перейдіть на офіційну сторінку завантаження Microsoft .
Натисніть кнопку «Завантажити».
Виберіть правильну версію (32-розрядну або 64-розрядну) на основі інсталяції Excel.
Запустіть завантажений файл і дотримуйтеся вказівок майстра налаштування.
Якщо під час інсталяції Excel відображається повідомлення про помилку, переконайтеся, що у вас є останні оновлення для вашої версії.
Після завершення інсталяції потрібно ввімкнути надбудову в Excel, щоб побачити вкладку Power Query.
Відкрийте Excel.
Виберіть Файл -> Параметри.
Натисніть «Надбудови» на лівій панелі.
У спадному меню внизу вікна виберіть «Надбудови COM».
Поставте прапорець поруч із «Microsoft Power Query for Excel» і натисніть «ОК».
Power Query тепер доступний на окремій вкладці на стрічці Excel!
Excel Power Query для Excel 2016 і пізніших версій
У Excel 2016 і пізніших версіях Power Query тепер є частиною стрічки Excel. Щоб отримати до нього доступ, виконайте такі дії:
Відкрийте Excel і створіть новий документ або завантажте наявний.
Перейдіть на вкладку «Дані».
Використовуйте кнопки в групі «Отримати та трансформувати дані», щоб отримати доступ до інструментів Power Query.
Як знайти інструменти Power Query на вкладці «Дані».
Розділ «Отримання та перетворення даних» на вкладці «Дані» містить кнопки для доступу до ряду інструментів і функцій, які є частиною Power Query в Excel.
Ось опис семи кнопок і функцій.
1. Отримати дані
Ця функція дозволяє підключатися до зовнішніх джерел даних і імпортувати дані з них. Деякі з найбільш часто використовуваних варіантів:
З файлу: імпортуйте з робочих книг Excel, файлів CSV, тексту, XML або JSON.
З бази даних: імпорт із SQL Server, Access, Oracle тощо.
З Azure: імпорт із бази даних SQL Azure, сховища BLOB-об’єктів Azure тощо.
2. З тексту/CSV
Кнопка «З тексту/CSV» дозволяє підключитися до текстового файлу або файлу CSV (значення, розділені комами) та імпортувати дані з них. Відкриється діалогове вікно імпорту даних, у якому ви можете вибрати файл.
Ви можете отримати доступ до того самого файлу TXT або CSV, використовуючи спадне меню «Отримати дані». Кнопка «З тексту/CSV» забезпечує ярлик.
На зображенні нижче показано використовуваний ярлик. У вікні також відображатиметься попередній перегляд даних, які містяться у файлі.
3. З Інтернету
Це ще один ярлик для функції, доступної в спадному меню «Отримати дані». Кнопка «З Інтернету» дозволяє підключатися до веб-сторінок та імпортувати дані з них за допомогою їхніх URL-адрес.
4. З таблиці/діапазону
Ця кнопка дає змогу перетворити вибраний діапазон комірок або наявну таблицю у вашій книзі Excel на запит Power Query.
Якщо вибрати діапазон і натиснути цю кнопку, автоматично відкриється редактор Power Query. Цей редактор описано в наступному розділі.
5. З малюнка
Хоча функція «З зображення» згрупована в розділі Power Query, насправді вона не є частиною Power Query.
Це окрема функція, яка дозволяє отримувати дані з друкованої чи рукописної таблиці за допомогою камери вашого пристрою та перетворювати їх у цифрову таблицю в Excel.
Він використовує технологію оптичного розпізнавання символів (OCR) для розпізнавання та виділення тексту та чисел із зображення.
6. Останні джерела
Ця кнопка забезпечує швидкий доступ до джерел даних, до яких ви нещодавно підключилися та імпортували дані в Excel. Це цінна економія часу для ваших запитів даних.
Коли ви натискаєте кнопку «Останні джерела», у спадному меню відображатиметься список нещодавно використаних джерел даних, таких як онлайн-з’єднання та файли Excel.
7. Існуючі підключення
Це ще одна економія часу. Він дає вам список усіх зв’язків із даними, які раніше були встановлені у вашій поточній книзі.
Як додати конектори Power Query до Excel
Деякі розширені функції або з’єднувачі Power Query можуть бути доступні лише в певних випусках Excel, наприклад Excel 2016 Professional Plus або Excel для Microsoft Office 365.
Наприклад, з’єднувач SharePoint, який дозволяє підключатися до папок SharePoint, доступний лише в корпоративних підписках.
Якщо у вас домашня або бізнес-підписка, ви не побачите цю опцію в списку роз’ємів.
Наведений нижче фрагмент списку доступності Microsoft показує, що підключення до SharePoint і PDF доступні не в усіх версіях.
Якщо вам не вистачає потрібних з’єднувачів і джерел, подумайте про оновлення до вищої версії. Це активує параметри, яких немає.
Як додати дозволи Power Query до Excel
Можуть існувати певні з’єднувачі або функції, які вимагають додаткових дозволів або налаштувань, як-от джерела даних певної організації.
Якщо у вас виникають проблеми з підключенням до певних джерел даних або використанням певних функцій, це може бути пов’язано з дозволами або налаштуваннями на рівні організації. У таких випадках ви можете звернутися за допомогою до ІТ-адміністратора вашої організації або служби підтримки.
Якщо вони надають додаткові дозволи для вашого облікового запису Windows, вам слід перезапустити Excel, щоб отримати доступ до підвищених прав.
Як отримати доступ до редактора Power Query
Саме тут ви проводите більшу частину свого часу, працюючи з Power Query. Це графічний інтерфейс, у якому ви можете створювати, змінювати та керувати своїми запитами.
Редактор також надає візуальне представлення структури вашого запиту та підтримує кілька різних переглядів.
Існує кілька способів доступу до редактора. Ви часто встановлюєте підключення або імпортуєте файл, який запускає редактор.
Крім того, ви можете відкрити редактор як порожній запит і налаштувати підключення з нього. Ось як це зробити в групі «Отримати та перетворити дані»:
Перейдіть на вкладку Дані.
Натисніть «Отримати дані» в групі «Отримати та перетворити дані».
Виберіть «Запустити редактор Power Query».
Використовуйте параметри праворуч від стрічки, щоб отримати доступ до своїх даних.
Група параметрів «Новий запит» знаходиться в правій частині стрічки. Звідси ви можете імпортувати файл, підключитися до бази даних і навіть ввести дані вручну.
Стрічка та вкладки
Стрічка в редакторі Power Query містить кілька вкладок із параметрами, організованими в групи для легкого доступу.
Ці вкладки служать різним цілям, надаючи інструменти та функції, які знадобляться під час роботи з Power Query. Чотири вкладки:
додому
Трансформувати
Додати стовпець
Переглянути
На цьому малюнку показано вкладки у верхній частині вікна. Попередній перегляд даних під стрічкою показує вміст таблиці Excel. Панель запитів знаходиться ліворуч.
Панель попереднього перегляду даних
Панель попереднього перегляду даних не просто показує ваші дані. У цьому вікні можна змінити заголовок стовпця та тип даних.
Рядок формул у верхній частині панелі показує спеціальний код запиту до бази даних.
Розширений редактор
Для більш складних операцій або налаштування Power Query пропонує розширений редактор. Цей редактор дозволяє безпосередньо редагувати код M, який є базовою мовою Power Query.
Вносячи зміни в код M, ви можете точно налаштувати свої запити та отримати додаткові функції, які можуть бути недоступні через графічний інтерфейс.
Щоб отримати доступ до розширеного редактора:
Перейдіть на вкладку «Домашня сторінка» в редакторі Power Query.
Натисніть «Розширений редактор» у групі запитів.
Якщо ви хочете вивчити деякі аспекти коду, ось кілька посібників:
Ось відеоінструкція з використання кожного виразу.
2 способи налаштування Power Query
Ви можете налаштувати роботу з Power Query за допомогою:
налаштування параметрів запиту
керування глобальними параметрами та параметрами конфіденційності
1. Як налаштувати параметри запиту
Щоб отримати доступ до налаштувань запиту:
Відкрийте редактор Power Query.
Перегляньте параметри на панелі з правого боку.
Натисніть посилання «Усі властивості».
За потреби змінює деталі у спливаючому вікні.
Ви можете змінити кілька аспектів свого запиту Microsoft, наприклад:
Назва: перейменуйте свій запит на щось більш описове.
Опис: додайте опис, щоб надати більше контексту.
Налаштування завантаження: виберіть, чи завантажувати дані в модель даних чи лише як з’єднання.
На зображенні нижче я змінюю назву «таблиці» на більш інформативну «розпродажі» та додаю опис.
2. Як налаштувати глобальні параметри
Power Query також дозволяє налаштувати загальний досвід. Щоб отримати доступ до цих параметрів, виконайте такі дії:
В Excel перейдіть на вкладку «Дані».
Натисніть спадне меню «Отримати дані».
Виберіть Параметри запиту внизу.
Відкриється діалогове вікно Параметри запиту. Тут можна змінити макет і поведінку редактора Power Query, установивши або знявши прапорець.
3. Як налаштувати параметри безпеки та конфіденційності
Параметри запиту містять розділ безпеки та конфіденційності.
У розділі безпеки можна контролювати перевірки сертифікатів і веб-попередження, якщо ваші запити надходять до зовнішнього онлайнового джерела даних.
У розділі «Конфіденційність» можна налаштувати, як Power Query обробляє та захищає конфіденційні дані.
Як додати інші інструменти для вдосконалення Power Query
Існують інші надбудови та інструменти, які можна використовувати для розширення функціональності Power Query. Ось три популярні варіанти:
1. Power Pivot
Power Pivot — це потужний інструмент моделювання та аналізу даних, вбудований у новіші версії Excel, який добре працює з Power Query.
Це дозволяє:
створювати складні моделі даних.
керувати зв'язками між таблицями.
виконувати розширені обчислення за допомогою виразів аналізу даних (DAX).
Якщо ви використовуєте Excel для Microsoft 365, Excel 2016 або Excel 2019, виконайте такі дії, щоб увімкнути Power Pivot для своїх зведених таблиць:
Перейдіть на вкладку Файл.
Виберіть Параметри, щоб відкрити діалогове вікно Параметри Excel.
Натисніть категорію «Надбудови» на лівій панелі.
У спадному меню внизу виберіть «COM Add-ins» і натисніть «Перейти».
Знайдіть у списку «Microsoft Power Pivot для Excel» і поставте прапорець біля нього.
Натисніть «OK», щоб увімкнути Power Pivot.
Після ввімкнення Power Pivot на стрічці Excel з’явиться нова вкладка «Power Pivot». Ваш наступний крок — навчитися працювати зі зведеною таблицею за допомогою вікна Power Pivot.
На цьому зображенні показано Power Pivot і Power Map у вікні надбудов:
2. Карта потужності (3D-карти)
Power Map, також відомий як 3D Maps, — це інструмент візуалізації даних для Excel, який дозволяє створювати інтерактивні 3D геопросторові візуалізації, використовуючи ваші дані. Це чудовий спосіб візуалізації географічних даних із Power Query.
Якщо ви використовуєте Excel для Microsoft 365, Excel 2016 або Excel 2019, виконайте такі дії, щоб увімкнути Power Map:
Перейдіть на вкладку «Вставка».
Натисніть кнопку «3D карта» в групі «Тури».
Якщо буде запропоновано ввімкнути надбудови аналізу даних, натисніть «Увімкнути».
Відкриється вікно 3D-карт, де ви можете:
візуалізуйте свої геопросторові дані в трьох вимірах
створювати інтерактивні тури
налаштувати зовнішній вигляд ваших карт.
3. LuckyTemplates
які можуть допомогти вам проаналізувати та візуалізувати дані.
Це не надбудова для Excel. Натомість це окрема автономна програма під назвою LuckyTemplates Desktop. Його можна встановити на Windows 10 або пізнішої версії, для цього потрібна 64-бітна ОС.
Користувачі Excel Mac можуть використовувати службу LuckyTemplates, яка є хмарною версією LuckyTemplates, яка працює у вашому веб-браузері.
Наше останнє або два слова
Тепер ви дізналися, як додати або отримати доступ до інструмента Power Query на робочому аркуші Excel. Цей інструмент дозволяє проводити складний бізнес-аналіз кількома клацаннями миші.
Ви можете відчувати себе трохи приголомшеними всіма функціями на вкладці трансформації та в редакторі. Спробуйте створити новий запит до бази даних або виконати завантаження даних із текстових файлів, щоб попрактикуватися у перетворенні наборів даних.
Незабаром ви будете на шляху до використання можливостей Power Query.
У цьому блозі представлено функцію LuckyTemplates TOPN DAX, яка дає вам змогу отримувати унікальну статистику з ваших даних, допомагаючи вам приймати кращі маркетингові рішення.
Дізнайтеся про деякі фантастичні аналітичні прийоми, які ми можемо використовувати для моделювання даних у LuckyTemplates за допомогою таблиць підтримки DAX.
Тут ми зануримося в LuckyTemplates Advanced DAX і запровадимо логіку ранжирування, щоб отримати дуже унікальну інформацію. У цьому прикладі я також демонструю розгалуження міри.
У цьому блозі представлено нову функцію в LuckyTemplates — параметр аналізу «Що, якщо». Ви побачите, як це робить усе швидким і легким для аналізу сценарію.
Дізнайтеся, як визначити, чи добре зростає ваш дохід, перевіривши, чи зросла ваша маржа за допомогою розгалуження міри LuckyTemplates.
Дізнайтеся та зрозумійте, як можна створювати та використовувати параметри LuckyTemplates, які є потужною функцією редактора запитів.
У цьому підручнику буде обговорено створення круглої гістограми за допомогою Charticulator. Ви дізнаєтеся, як створити їх для звіту LuckyTemplates.
Дізнайтеся, як використовувати функції та формули PowerApps, щоб покращити функціональність і змінити поведінку в нашій програмі Canvas Power Apps.
У цьому підручнику ви дізнаєтесь, як об’єднати функції разом за допомогою оператора каналу dplyr мовою програмування R.
RANKX від LuckyTemplates дозволяє повернути рейтинг певного числа в кожному рядку таблиці, який є частиною списку чисел.
