Початок роботи з 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

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

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

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

×