Продвинутый Excel: Power Query, Power Pivot и автоматизация отчётов
Научитесь собирать и обновлять рабочие отчёты в Excel. Power Query для загрузки и очистки данных, сложные формулы, Power Pivot и DAX для расчётов. Практикумы по продажам, остаткам и закупкам, план-факту подразделений.
О курсе
Вы уже умеете пользоваться формулами, ВПР и сводными таблицами. Следующий шаг — собрать рабочую книгу, в которую можно загрузить новые данные, обновить расчёты и получить отчёт за следующий месяц.
Начнём с небольшого отчёта по продажам: подключим исходный файл через Power Query, подготовим данные и построим сводную с диаграммой. Затем добавим задачи, которые возникают в работе: выгрузки из нескольких файлов, разные форматы дат и чисел, дубли, отсутствующие товары в справочнике и изменившиеся столбцы.
Формулы, запросы и модель данных
ПРОСМОТРX, ИНДЕКС и ПОИСКПОЗ для поиска по условиям; ФИЛЬТР, УНИК и СОРТ для динамических выборок; LET для длинных расчётов. В Power Query вы научитесь объединять выгрузки со справочниками, собирать файлы из папки и проверять итоги после обновления.
В Power Pivot свяжете несколько таблиц, добавите календарь и месячный план. Меры DAX помогут считать выручку, маржу, доли и выполнение плана под фильтрами. На этих показателях соберёте дашборд со сводными, диаграммами и общими срезами.
Три прикладные работы
- Продажи и маржа: месячные выгрузки, справочник себестоимости и обновление отчёта при появлении нового товара.
- Остатки и закупки: движения товаров, остаток на дату и расчёт закупки по нормативу покрытия.
- План-факт: дневные продажи и месячный план подразделений в одной модели.
В каждой работе нужно проверить контрольные числа и обновить книгу на следующем комплекте данных. Этот опыт пригодится для регулярных отчётов по продажам, финансам, закупкам и работе подразделений.
Чему научитесь
- Собирать обновляемый отчёт из исходных файлов через Power Query и проверять результат после загрузки новых данных.
- Искать значения по нескольким условиям, создавать динамические выборки и разбирать длинные формулы с LET.
- Загружать CSV и Excel-файлы, настраивать типы, очищать значения и находить ошибки в данных.
- Объединять выгрузки со справочниками, собирать месячные файлы из папки и проверять дубли и отсутствующие ключи.
- Строить модель Power Pivot со связями, календарём и несколькими таблицами фактов.
- Создавать меры DAX для выручки, маржи, долей и выполнения плана; понимать влияние фильтров на расчёт.
- Собирать дашборд с общими срезами и проверять показатели под разными фильтрами.
- Готовить отчёты по продажам и марже, остаткам и закупкам, план-факту подразделений.
Для кого
- Тем, кто прошёл базовый Excel и хочет перейти к сложным формулам, Power Query и модели данных.
- Специалистам по продажам, финансам, закупкам и операциям, которые регулярно готовят отчёты из выгрузок.
- Аналитикам, которым нужно объединять несколько источников и объяснять полученные показатели.
Формат
- Программа из 40 текстовых уроков: объяснения, рабочие примеры и самостоятельная практика в Excel.
- Скачиваемые учебные XLSX и CSV, проверочные вопросы и контрольные результаты.
- Три прикладные работы с проверкой обновления на новых данных.
Что нужно на старте
- Базовые формулы и ссылки, ЕСЛИ, ВПР, СУММЕСЛИМН, умные таблицы, простые сводные и диаграммы. Подойдёт курс «Excel с нуля» или аналогичная подготовка.
- Настольный Microsoft Excel из Microsoft 365 на Windows с доступными Power Query и надстройкой Power Pivot. Для курса используется русский интерфейс.
- Возможность сохранять и открывать учебные XLSX и CSV на компьютере. Программирование не требуется.
Автор курса
Алексей Малышкин
Разрабатываю образовательные материалы по программированию, машинному обучению и анализу данных. Темы: Python-разработка, аналитика, ML-инструменты (LangChain, vector databases, AI agents), компьютерное зрение. Стараюсь объяснять технические концепции понятно и структурировано. Ранее преподавал на Stepik и обучил более 2000 студентов. Мой профиль на Stepik - https://stepik.org/users/273781420/profile Мой телеграм для связи @alexey_latorn
