Початок роботи з Power Query

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

Крок 1. Підключення до сторінки Вікіпедії

Excel 2016: на вкладці Дані виберіть Новий запит > З інших джерел > З Інтернету. Якщо кнопка Новий запит не відображається, виберіть Дані > З Інтернету.

Excel 2010–2013: відкрийте вкладку Power Query й виберіть З Інтернету. Якщо ця вкладка не відображається, переконайтеся, що ​​надбудову Power Query інстальовано.

  1. У діалоговому вікні З Інтернету в текстовому полі URL-адреса вставте URL-адресу Вікіпедії (https://uk.wikipedia.org/wiki/Чемпіонат_Європи_з_футболу)

    Power Query > "З Інтернету"> діалогове вікно введення URL-адреси
  2. Натисніть кнопку OK.

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

    Power Query > "З інтернету" > табличне подання "Навігатор"

  3. Двічі клацніть таблицю Результати[ред], і редактор запитів відкриє дані про чемпіонати.

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

Крок 2. Формування даних

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

  1. У сітці Попередній перегляд запиту виберіть стовпці Рік і Чемпіон, утримуючи клавішу Ctrl.

  2. Виберіть Видалити стовпці > Видалити інші стовпці.

    Power Query > "Редактор запитів" > "Видалити стовпці"

Крок 3. Очищення даних

На цьому кроці ви замінюєте значення та фільтруєте дані.

  1. Виділіть стовпець Рік.

  2. На стрічці редактора запитів виберіть Замінити значення.

  3. У діалоговому вікні Замінення значень у текстовому полі Значення для пошуку введіть "Відомості" та залиште текстове поле Замінити на пустим.

  4. Натисніть кнопку OK.

    Power Query > "Редактор запитів" > "Замінити значення"

Крок 4. Фільтрування значень у стовпці

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

  1. Клацніть стрілку розкривного списку фільтра у стовпці Рік.

  2. У розкривному списку Фільтр зніміть прапорець Рік.

  3. Натисніть кнопку OK.

Power Query > "Редактор запитів" > фільтр для очищення даних

Крок 5. Установлення назви запиту

Тепер потрібно назвати новий запит.

В області Параметри запита в текстовому полі Ім’я введіть Переможці Кубка Європи. Якщо клацнути посилання Усі властивості, також можна ввести опис запиту в текстовому полі Опис.

Power Query > "Редактор запитів" > "Параметри запита"

Крок 6. Завантаження запиту до аркуша

Насамкінець потрібно завантажити запит Переможці Кубка Європи до аркуша.

  1. У лівому верхньому куті натисніть кнопку Закрити й завантажити.

    Power Query відобразить результати запиту на аркуші.

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

Крок 7. Вивчення запиту

  1. Клацніть будь-яке місце в діапазоні даних, а потім на вкладці Запит натисніть кнопку Редагувати.

  2. У правій частині екрана відобразиться список Застосовані кроки. Кроки запиту створювалися в процесі виконання дій. Кожному кроку запиту відповідає формула мовою M.

  3. Нижче наведено опис кожного кроку.

Крок запиту

Завдання

Формула

Джерело

Підключення до джерела веб-даних

= Web.Page(Web.Contents("https://uk.wikipedia.org/wiki/Чемпіонат_Європи_з_футболу"))

Навігація

Вибір таблиці

= Source{2}[Data]

Змінений тип

Змінення типів: Power Query робить це автоматично

= Table.TransformColumnTypes(Data2,{{"Рік", type text}, {"Місце проведення", type text}, {"", type text}, {"Фінальний матч – Чемпіон", type text}, {"Фінальний матч – Рахунок", type text}, {"Фінальний матч – Фіналіст", type text}, {"2", type text}, {"Матч за третє місце – Третє місце", type text}, {"Матч за третє місце – Рахунок", type text}, {"Матч за третє місце – Четверте місце", type text}, {"3", type text}, {"Кількість команд", type text}})

Видалені інші стовпці

Видалити інші стовпці, щоб відображалися тільки потрібні

Table.SelectColumns

= Table.SelectColumns(#"Змінений тип",{"Фінальний матч – Чемпіон", "Рік"})

Замінене значення

Замінити значення, щоб очистити значення у вибраному стовпці

Table.ReplaceValue

= Table.ReplaceValue(#"Видалені інші стовпці","Відомості","",Replacer.ReplaceText,{"Рік"})

Відфільтровані рядки

Застосувати фільтр до значень у стовпці

Table.SelectRows

= Table.SelectRows(#"Замінене значення", each ([Year] <> "Рік"))

Щоб повністю переглянути код M, клацніть на стрічці Power Query параметр Розширений редактор. Докладні відомості про мову формул Power Query див. в цій статті та в довіднику з мови M у Power Query.

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

Потрібна додаткова довідка?

Ви завжди можете поставити запитання експерту в спільноті Tech (у розділі Excel), отримати підтримку в спільноті, що допомагає знайти відповіді на запитання, або запропонувати нову функцію чи вдосконалення на форумі Excel User Voice.

Див. також

Функція "Завантажити та перетворити" в Excel 2016

Імпорт даних із зовнішніх джерел (Power Query)

Формування даних (Power Query)

Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×