Работа с надстройкой "Power Query" (Get&Transfom/Скачать и преобразовать) в MS Excel-2016
Power Query — это технология подключения к данным, с помощью которой можно обнаруживать, подключать, объединять и уточнять данные из различных источников для последующего анализа. Надстройка позволяет искать источники данных, создавать подключения и формировать данные в соответствии с потребностями для создания отчетов. Курс ориентирован на опытных пользователей, которые выполняют подключения и обработку внешних данных для подготовки отчетов.
После изучения курса вы сможете
- Создавать подключения к различным источникам данных.
- Преобразовывать данные в запросах.
- Объединять данных из нескольких источников.
- Использовать параметризацию запросов.
Содержание курса
Модуль 1. Знакомство с надстройкой Power Query
- Назначение Power Query
- Интерфейс окна редактора запросов Power Query
- Структура запроса
- Краткая характеристика языка программирования запросов Power Query (язык М).
- Простые операции в запросе
- Создание несложных запросов
- Обновление запроса
- Типы загрузки результата запроса. Просмотр импортированных данных в Power Pivot.
- Базовые сведения о модели данных и работе с моделью данных в Power Pivot.
Модуль 2. Загрузка данных
- Подключение к различным типам источников данных
- Таблица активного рабочего листа
- Файлы MS Excel (.xlsx, .xlsm, .xlsb)
- Текстовые файлы (.csv,.txt)
- Файлы XML (.xml)
- Базы данных (.accdb)
- WEB (интернет)
- Создание и использование шаблона рабочей книги с моделью данных, заполненной таблицами.
- Пути решения проблемы использования данных текстовых файлов (.docs, .rtf, .odt, ...) и .pdf , не загружаемых Power Query
- Навигация по источнику
Модуль 3. Решение задач по трансформации структуры данных
- Операции со столбцами
- Управление столбцами
- Разделение по количеству символов и разделителю
- Усечение и извлечение, добавление префиксов и суффиксов
- Использование столбца индекса и условного столбца
- Изменение регистра текста
- Операции с текстом, числами и датами
- Другие операции
- Операции со строками
- Удаление и фильтрация
- Удаление дубликатов
- Другие операции
- Операции с таблицами
- Pivot (Сведение по столбцу)
- Unpivot (Отмена свёртывания столбцов)
- Транспонирование
- Группировка строк
- Другие операции
- Использование различной логики при трансформации
- Пример обработки статистических данных ВОЗ (для фарм, мед. и страх. компаний)
Модуль 4. Решение задач по консолидации данных из разных источников
- Автоматическая консолидация табличных данных (текстовые файлы и файлы MS Excel)
- Самостоятельная разработка запроса и вспомогательных функций для консолидации данных
- Консолидация из разных файлов и/или с разных листов с трансформацией структуры данных
Модуль 5. Работа с существующими запросами
- Операции над запросами
- Добавление/Append данных одного запроса к данным другого запроса
- Соединение/Merge одного запроса с другим запросом по общему ключу
- Типы соединений: внешние и внутренние, антисоединения.
- Группировка запросов
- Создание новых запросов путем
- Клонирования (дублирования) существующего запроса
- Извлечения предыдущих шагов из существующего запроса
- Использования ссылки на существующий запрос
Модуль 6. Параметризация запросов
- Назначение запросов с параметрами.
- Создание, использование и управление параметрами запроса
- Использование в запросах параметров из таблиц рабочей книги
- Работа с файл-шаблоном упрощающим создание и использования запросов с параметром.
Предварительная подготовка
- Умение работать в среде ОС Windows
- Хорошее знание основ работы в программе Microsoft Excel