Работа с надстройкой "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