Power Query ПАВЕЛ ЛАСАК. в 10 шагов. для EXCEL версии 01
1 Power Query 0 за 10 шагов для EXCEL PAVEL LASAK 2019 версии 01
2 СОДЕРЖАНИЕ Глава 1. Что такое Power Query. 5 Глава 2. У вас есть Power Query. 6 Глава 3. Исходные данные. 7 Глава 4. Загрузка данных. 8 Глава 5. Редактирование строк Глава 6. Редактирование столбцов Глава 7. Сортировка и фильтрация Глава 8. Условный столбец Глава 9. Закрыть (данные в Excel) Глава 10. Редактировать запрос Глава 11. Подробнее о Power Query Павел Ласак Как использовать Power Query в Excel Страница 2 из 21 Не продается.
3 Автор Павел Ласак является энтузиастом Microsoft Excel, за последние 3 года он был награжден престижной наградой MVP (Самый ценный профессионал для приложений Excel Office). Более 20 лет создает сайт о Microsoft Excel (на сайте более 1200 статей, 500 рабочих тетрадей и шаблонов). В сотрудничестве с SEDUO создает обучающие видеоролики для работы в Microsoft Excel. Он преподает Excel, включая Power Query, Power Pivot и программирование на VBA не только в MUNI в Брно, но и в некоммерческой организации Czechitas (в качестве старшего преподавателя). Он также выступает на различных конференциях. В компаниях, помимо обучения, консультирует престижные чешские компании. Он программирует и создает шаблоны также с помощью Power Query и VBA. Информация об авторе: LinkedIn: MVP: Прежде всего спасибо вам за загрузку этой электронной книги. Большое спасибо семье, которая была терпелива со мной во время работы над этой электронной книгой. Зачем эта электронная книга В обучении я часто сталкиваюсь с просьбами о практическом использовании Excel. При обсуждении того, что и как им нужно, мой ответ часто указывает на использование инструмента Power Query. К сожалению, я еще не видел на чешском рынке полной инструкции по эксплуатации этого инструмента, поэтому решил опубликовать эту электронную книгу, которая прольет свет на работу этого инструмента. Если будет интерес, я также создам электронную книгу по соответствующему инструменту Power Pivot. Огромным преимуществом являетсячто этот же инструмент является частью Power BI. Инструменты из мастерской Microsoft, которая сейчас снова в центре внимания. То, что вы узнаете в Excel, вы сможете применить и в других инструментах. Что вам нужно знать Чтобы получить максимальную отдачу от этой электронной книги, вам необходимо знать: Уметь работать с папками и открывать, распаковывать (распаковывать) файлы Использовать клавиатуру (уметь писать символы и мышь (двукратно нажмите) Основы работы с Excel. Откройте и сохраните файл. Напишите формулу Pavel Lasák Power Query в Excel Страница 3 из 21 Не для продажи.
4 Условные обозначения электронной книги Для облегчения ориентации я использую в тексте следующие условные обозначения. Пример написания функции: Функция будет записана следующим образом: =ВПР(поиск;таблица;столбец;[тип]) Клавиши для быстрых клавиш При необходимости используйте сочетания клавиш F4, Ctrl+F4 Работа с лентой (меню) Если мне нужно использовать ленту: Главная > Вставить на английском языке Главная > Вставка Выделение в тексте: Обращает внимание на что-то важное (): Осторожно: Что не забыть, что заполнить, что может вызвать. Веб-сайт: образцы для скачивания. Совет/Примечание: Что и как улучшить, о чем помнить. Примечания и предложения по улучшению Это первая версия электронной книги. Для любых идей по улучшению или исправлению, я буду рад, если вы пришлете их по адресу, я постараюсь включить их в следующую версию. Исходные файлы Вы можете использовать ссылки, входящие в состав электронной книги, или можете создать (использовать) свои собственные данные, если не используете данные из соответствующей главы. Павел Ласак Как использовать Power Query в Excel Страница 4 из 21 Не продается.
5 Глава 1. Что такое Power Query В этой главе вы узнаете, что на самом деле может делать Power Query. Как вы можете это использовать. Что такое PowerQuery Power Query — отличный инструмент для загрузки данных из различных источников (книга excel, лист, csv, xml, web) и их последующей модификации, очистки, преобразования возможных связей. В равной степенивы можете использовать плагин для динамического выполнения этих преобразований. Вкратце, Power Query может: Загружать данные из различных источников (рабочая книга, Интернет, JSON, XML, база данных и т. д.) Объединять данные (из папок, нескольких листов) Соединять и соединять данные (данные из нескольких таблиц в одну, а также «ВПР»). » ) Очистка и модификация данных (замена, удаление) Преобразование данных (классные вещи, такие как транспонирование «поворота» и «разворота», группировка) Операции со столбцами (объединение, разбиение, вычисления) Сортировка и фильтрация (даже повторяющиеся значения и значения ошибок) Вычисления работа с данными (использование функций и создание собственных функций) Создание постоянных списков (в том числе динамических) Создание динамических запросов (изменение в Excel выполнит пересчет в Power Query) М-язык (скажем, язык программирования, на котором можно улучшить свои запросы по своему вкусу) Подготовьте данные для надстройки PowerPivot (или Power Pivot, Power Map) Совет/примечание. До сих пор этим инструментом пренебрегали, но как только вы поймете его мощь, вы не сможете работать без него. Это может сэкономить много работы или денег. Подробнее: Если вам недостаточно текстов, можете посмотреть сериал про Power Query: Интернет: Актуальность: Внимание: Инструмент находится в разработке, поэтому ваша версия может выглядеть немного иначе, кроме того, этот же инструмент также содержит Power BI, работает так же, только иконки можно перетасовать. Павел Ласак Как использовать Power Query в Excel Страница 5 из 21 Не продается.
6 Глава 2. У вас есть Power Query? В этой главе вы узнаете, включает ли ваш Excel этот инструмент и не может ли он быть установлен. Ваша версия Power Query включена. Power Query входит в стандартную комплектацию Excel 2016 для Windows (из пакета Office 2016 для Windows) и более поздних версий. , а также если у вас есть подписка на Office 365, то эти версии содержат инструмент Power Query (надстройка). Для версий 2013 и 2010 (точнее Microsoft Office 2010Professional Plus с Software Assurance, Microsoft Office 2013) опять же под операционную систему Windows, нужно просто скачать дополнение. Конечно, у вас должен быть установлен PC Explorer (который является частью Windows, но на всякий случай я предупреждаю вас, если вы его удалили). Кроме того, вы должны выбрать правильную версию в соответствии с установкой (32 или 64 бит). Однако страница, с которой вы скачиваете, подскажет вам об этом. Интернет: Ссылка на надстройку. Недостатком является то, что эта надстройка недоступна для Excel на компьютерах Apple или Excel в операционной системе ios. Внимание: Недоступно для Excel на Apple (ios). О том, что у вас установлена надстройка, можно узнать, взглянув на ленту Данные, она содержит раздел Получить и преобразовать на английском языке Get&Transform (Excel 2016 и более поздние версии). Или собственную ленту Power Query (Excel 2013/2010 после установки) Excel 365 (версия CZ) Excel 2016 (версия EN) Excel 2013 (2010) Павел Ласак Power Query в Excel Страница 6 из 21 Не продается.
7 Глава 3. Исходные данные В этой главе вы научитесь подготавливать данные, которые будете использовать в следующем тексте Что нам понадобится для работы — данные Для работы с Power Query вам потребуются исходные данные. Для образцов мне нужны данные, содержащие текст, числа, дату. Для дальнейших примеров заголовок по-прежнему состоит из двух строк. Таблица сохраняется в отдельной книге, например, под именем Data.xlsx. Примечание: данные не обязательно должны быть обширными для понимания, вы будете понимать лучше и быстрее с меньшим объемом данных, позже вы сможете обработать даже больше строки в 20 файлах. Для более продвинутых Если вы хотите узнать больше, таблицы могут по-прежнему содержать объединенные столбцы, опечатки, значения ошибок, логические значения, время, сводки, вы можете иметь таблицы на нескольких листах, в нескольких файлах, в другом формате (txt, xml, ) вы можете иметь данные в базе данных. Кроме того, файлы хранятся где-то в Интернете. какаяэто выходит за рамки этой электронной книги, но по следующим ссылкам вы можете начать тексты, которые покажут вам, как это сделать. Веб-сайт: Загружаемые данные: Павел Ласак Как использовать Power Query в Excel Страница 7 из 21 Не для продажи.
8 Глава 4. Загрузка данных В этой главе вы узнаете, что вы будете загружать данные, которые будете использовать в следующем тексте, из другого файла Как загружать данные в целом Power Query может загружать данные из различных источников: Из файла — Из файла (не только файл, но и папку) Из базы данных — Из базы данных Из Azure — Из Azure Из онлайн-сервисов — Из онлайн-сервисов Из других источников — Из других источников (веб, но, возможно, и пустой запрос) Старые версии мастеров — если у вас их нет отдельно в меню на английском языке Устаревшие мастера Объединить запросы Объединить и соединить Загружаем исходные данные из другого файла Самый распространенный случай — загрузка данных из другого файла. Я предполагаю, что вы подготовили книгу Excel из предыдущей главы, которую нужно загрузить в PowerQuery. Как загрузить У вас есть файл и еще, что вы хотите загрузить первый лист, поэтому вам нужно загрузить его. На ленте Данные > Загрузка и преобразование > Загрузить данные > Файл > Из файла In English Data > Получите & Преобразование > Получить данные > Файл > Из файла Павел Ласак Как использовать Power Query в Excel Страница 8 из 21 Не для продажи.
9 Появится диалоговое окно Navigator, в котором вы можете увидеть имя выбранной книги и список листов. Вы выбираете нужный лист, который хотите загрузить (больше листов описано ниже). Вы выбираете Transform data (а по-английски Transform), либо среднюю кнопку (кнопка может иметь другое название, Microsoft переводит по-разному в зависимости от версии), так вы можете загрузить выбранный лист прямо в Power Query, где вы можете напрямую редактировать загруженные данные. Совет/Примечание: Если вы тестируете несколько дней, навигатор может также показывать информацию, котораяфайл устарел, и вы сможете обновить предварительный просмотр (если вы не видите его при первой загрузке). После нажатия на Transform data (и на английском Transform) запустится редактор Power Query. Загружать по-другому или в другом месте Веб-сайт: Query-Excel/ Pavel Lasák How to Power Query in Excel Страница 9 из 21 Не для продажи.
10 Глава 5. Редактирование строк В этой главе вы научитесь работать со строками, главным образом, как редактировать первую строку в качестве заголовка. Данные загружены Просто таблица в редакторе Power Query, она выглядит не так, как должна. Исходная таблица содержит две строки заголовков, Power Query загрузил вторую строку заголовка в первую строку таблицы. Работа с заголовками Когда данные загружены, могут возникнуть три ситуации: Они загружены правильно — заголовок содержит описания столбцов и данные. Щелкните правой или левой кнопкой мыши по значку таблицы в разделе таблицы. Вы выбираете первый вариант Использовать первую строку в качестве заголовка, и все готово. Другие параметры Ленту можно использовать для сброса настроек. У вас есть выбор: Главная > Используйте первую строку в качестве заголовка, и вы сами выбираете, что и как Использовать первую строку в качестве заголовка Преобразование > Используйте первую строку в качестве заголовка. Дополнительная информация Интернет: Excel/ Pavel Lasák How to Power Query in Excel Страница 10 из 21 Не для продажи.
11 Глава 6. Редактирование столбцов В этой главе вы узнаете, как удалять, переименовывать, изменять тип, объединять/разделять Мы приступим к удалению ненужных столбцов Когда вы начинаете редактировать таблицу, часто бывает необходимо удалить столбцы, которые вы будете в выводе не нуждается. Кроме того, вам не нужно удалять по одному, вы можете использовать опцию, чтобы отметить сразу несколько столбцов (используя Ctrl — так же, как в Excel). Вы даже можете использовать отрицание при удалении, т.е. удалять столбцы, которые не отмечены. Как это сделать, щелкните правой кнопкой мыши столбец, который хотите удалить.а затем выберите Удалить или Удалить другие столбцы (не отмеченные). Или из ленты Transform. Переименовать столбец Вы изменили порядок столбцов и должны их переименовать. Знайте, что находится в столбце, когда вы ссылаетесь на него. Внимание: Возможные имена могут быть в первой строке. Так что не перезаписывайте без необходимости. Самый простой метод. Дважды щелкните имя данного столбца, чтобы ввести имя и переименовать его. Или щелкните правой кнопкой мыши над выбранным столбцом и выберите «Переименовать». В качестве альтернативы, если у вас есть выбранный столбец, вы можете нажать «Переименовать» в меню «Преобразование» в разделе «Любой столбец». Павел Ласак Как использовать Power Query в Excel Страница 11 из 21 Не для продажи.
12 Объединение столбцов Если у вас в отдельных столбцах день, месяц, год, удобно их объединить (получается дата), а также иногда необходимо, чтобы имя и фамилия были в одном столбце. Опять же, у вас есть два варианта объединения столбцов: путем добавления столбца (добавляется еще один столбец) путем преобразования (исходный объединенный столбец исчезает и создается объединенный столбец) до того, как мы выберем слияние. Вам нужно отметить столбцы, которые вы хотите объединить (Ctrl может отметить связанные столбцы). Путем добавления столбца Внимание: Это зависит от порядка, в котором вы отмечаете! В меню «Добавить столбец» в разделе «Из текста» нажмите «Объединить столбцы». Столбцы объединяются в новый столбец (столбец добавляется). Отображаемое окно описано выше. Путем преобразования В меню «Преобразование» в разделе «Из текста» нажмите «Объединить столбцы». Выбранные столбцы будут объединены в один (как минимум один столбец исчезнет, если было выбрано два, или выделенные столбцы станут одним!). В обоих случаях появится диалоговое окно «Объединить столбцы», которое мы обсудим. Объединить столбцы — диалоговое окно В диалоговом окне вы выбираете: Разделитель Какой разделитель будет разделять столбцы (он не обязательно должен быть любым) или вы можете выбрать из предустановленныхвы выбираете свое собственное имя нового столбца, вы можете выбрать свое собственное имя, если вам не нравится предустановленное имя из редактора запросов Pavel Lasák How to Power Query in Excel Страница 12 из 21 Не для продажи.
13 Результатом будет полное имя объединенного столбца. Установите тип данных столбца. Вы заполнили столбцы, но также желательно отредактировать их всплывающую подсказку (значок слева от имени столбца говорит о том, какая подсказка находится в столбце). Редактор Power Query не всегда правильно угадывает тип при загрузке, поэтому вам нужно отредактировать его вручную. Почему? Сделайте это яснее. Если это только дни, зачем отображать часы, минуты и секунды или десятичное число в столбце? Как это сделать? Щелкните левой кнопкой мыши по значку типа и выберите то, что вам нужно: Десятичное число Валюта Целое число. Дополнительная информация Веб-сайт: Excel/ Pavel Lasák How to Power Query in Excel Страница 13 из 21 Не для продажи.
14 Глава 7. Сортировка и фильтрация В этой главе вы узнаете, как фильтровать и сортировать таблицы, загруженные в редактор Power Query. Как сортировать Мне, наверное, не нужно напоминать вам о классической сортировке, знакомой вам по Excel. Вы можете сортировать аналогичным образом в PowerQuery. Вы выбираете столбец и хотите ли вы сортировать по возрастанию (от А до Я, от 0 до «бесконечности») или по убыванию (Я — А, «бесконечность» — 0, т. е. «минус бесконечность»): Отфильтровать число Как и при сортировке, нажмите на значке рядом с соответствующим столбцом . И выберите либо соответствующий номер. Или, щелкнув числовые фильтры, вы можете отфильтровать больше, меньше и т. д. Фильтровать тексты Возможно, вы знакомы с соглашением о звездочке и вопросительном знаке для фильтрации текста в Excel (что, на первый взгляд, вы не сможете сделать в PowerQuery). ). Но как это сделать в Power Query. В меню можно использовать такие параметры, как «начинается с», «содержит» и т. д. Pavel Lasák Power Query in Excel Страница 14 из 21 Не продается.
15 Диалоговое окно «Фильтровать строки» Выбрав, например, параметр «Содержит», вы получите диалоговое окно «Фильтр строк», в котором вы можетезадайте необходимые параметры (или добавьте еще, используя логические коннекторы А и Или): я выберу, например, букву М над столбцом Фамилия. Результат будет (Только фамилии, начинающиеся с заглавной М): Прописные и строчные буквы Внимание Редактор Power Query чувствителен к регистру. Дополнительная информация Совет/примечание: PowerQuery чувствителен к регистру. В статье вы можете узнать больше о том, как перемещать столбцы, как разделять столбцы и многое другое. Web: Excel/ Pavel Lasák How to Power Query in Excel Страница 15 из 21 Не для продажи.
16 Глава 8. Условный столбец В этой главе вы узнаете, как создать условный столбец, отвечающий условию Как создавать условия В таблице мне нужно, чтобы Чешская Республика / Словакия была указана на основе состояния Чешская Республика / Словацкая Республика. Для этого достаточно нажать на Conditional Column (Условный столбец) в ленте Add Columns (и на английском Add Columns): Условный столбец Просто заполните в полученном диалоговом окне: Имя нового столбца Имя столбца — над которым будет формироваться условие Оператор — равно, больше и т.д. Value На какое значение будет реагировать Output — что будет если Else — что будет в остальных случаях. Павел Ласак Как сделать Power Query в Excel Страница 16 из 21 Не продается.
17 Результат Новый столбец Conditional содержит необходимую информацию в зависимости от типа состояния. Дополнительная информация Web: Query-Excel/ Кроме того, вы можете использовать пользовательские функции: Web: Query-Excel/ Или даже язык M, но это выходит за рамки этой электронной книги. Web: PowerQuery-Excel/ Pavel Lasák Power Query в Excel Страница 17 из 21 Не для продажи.
18 Глава 9. Закрыть (данные в Excel) В этой главе вы узнаете, как закрыть или загрузить измененные данные в Excel Закрыть и загрузить (Закрыть и загрузить) Самый простой способ вернуть измененные данные обратно в Excel. Вы можете сохранить либочерез главное меню и значок «Закрыть и загрузить» и выберите «Закрыть и загрузить». На английском через Главное меню, а затем Close & Загрузить > Закрыть & Загрузить Другой вариант — использовать значок на вкладке «Файл» и выбрать «Закрыть и загрузить» в меню. Последним вариантом является крестик в правом верхнем углу приложения и нажмите Сохранить. Загрузка в Excel Данные загружаются в новый лист в Excel, таблица естественно будет обрабатываться как таблица. Все подключения Query будут доступны в боковом меню. Здесь вы увидите название редактируемой таблицы и способ ее загрузки. Также есть возможность обновления и редактирования. Павел Ласак Power Query в Excel Страница 18 из 21 Не продается.
19 Обновлений Ваша таблица, например, использует данные из Википедии, и в Википедии были обновления (например, количество изменений фамилии). Итак, как вы можете обновить? Давайте рассмотрим отдельные параметры: Автоматически Если вы правильно настроите соединение, таблица может обновляться сама. Сочетание клавиш Вероятно, самый быстрый способ Обновить все Ctrl + Alt + F5 Обновить Alt + F5 Над таблицей Щелкните правой кнопкой мыши над таблицей и выберите Обновить. В меню (лента данных) На ленте данных просто нажмите «Обновить все» или в меню, нажав на стрелку, вы можете выбрать, хотите ли вы обновить все или только текущую выбранную таблицу (со все более и более обширными таблицами вы часто будете хотите обновить только один и не ждать, пока обновится все). Другие способы Существуют и другие способы обновления данных, которые я написал и опубликовал на сайте. Дополнительная информация Веб-сайт: Pavel Lasák How to Power Query in Excel Страница 19 из 21 Не для продажи.
20 Глава 10. Редактирование запроса В этой главе вы узнаете, как редактировать выполняемый запрос. Редактирование Последнее, что вам нужно в Power Query, — это редактировать параметры запроса, которые выполняет Power Query. Например, вам нужно удалитьследующий столбец, переставьте столбцы. Данный запрос уже существует в Excel. Или если вы измените то, что PowerQuery делает в фоновом режиме (вы не обновляете данные, как описано в предыдущей главе). Вернитесь обратно в редактор Power Query в Excel. В Excel просто щелкните правой кнопкой мыши запрос в разделе «Запросы и подключения» и выберите «Изменить» в меню. Нажмите, чтобы вернуться в редактор PowerQuery, здесь вы можете отредактировать то, что вам нужно, и через Закрыть и загрузить обновление выполнит новую обработку данных, и вы вернетесь в Excel. Просмотр запросов и подключений Если вы не видите нужное окно запросов и подключений, используйте Data > Запросы и соединения. Дополнительная информация Веб-сайт: Query-Excel/ Pavel Lasák How to Power Query in Excel Страница 20 из 21 Не для продажи.
