Курс Excel Advanced: поглиблені інструменти

Проходячи курс, ви виконуєте більше 180 практичних задач і реальних кейсів, з якими обов'язково стикаєтеся, працюючи професійно з бізнес даними. Всі заняття на 100% практичні, вирішуємо реальні бізнес завдання. Підключаємося до даних - очищаємо - будуємо аналітичні панелі - робимо висновки - приймаємо рішення - прогнозуємо - оптимізуємо. Ви навчитеся організовувати ефективну роботу з даними і зможете налаштувати систему бізнес-аналітики. Значно зменшите час на рутинні операції і автоматизуєте свою роботу. Крім того, Ви безсумнівно підвищите свою вартість на ринку праці і станете справжнім віртуозом Excel.

Програма курсу

Підключення до зовнішніх джерел і очищення даних: редактор запитів (Power Query)

• Особливості підключення до різних джерел даних: БД, інші файли Excel, папки з файлами, .csv, сайтам в інтернеті та ін. Принципи створення і роботи із запитами на вилучення та очищення даних
• Працюємо зі стовпцями і рядками, заміна значень, pivot, unpivot і інші операції
• Об'єднання (append) і злиття (merge) запитів. Розуміємо типи з'єднань (join)
• Працюємо з параметричними запитами

Підключення до зовнішніх джерел і очищення даних: основи мови "М"

• Розуміємо синтаксис "M", інструкція let ... in
• Працюємо з основними вбудованими функціями "M"
• Створення порожнього запиту, додавання скрипта на "М"
• Створення і використання для користувача функцій
• Обходимо обмеження призначеного для користувача інтерфейсу редактора запитів. Тривалість модуля: ОНЛАЙН КУРС ~ 2 год, очний курс ~ 2 год.

Магія використання динамічних посилань і діапазонів. Блок - 1

• Як працюють функції посилання Excel: виходимо на новий рівень розуміння формул
• Використання ЗМІЩ (OFFSET) для організації динамічних діапазонів
• Міць посилальної форми ІНДЕКС (INDEX)
• Волатильні і неволатильні функції, оптимізація процесів обчислень в Excel
• Трюки використання довідкових форм ВИБІР (CHOOSE) і ЯКЩО (IF)
• ПРАКТИКУМ: створюємо звіт на основі "плаваючих" (динамічних) діапазонів, закріплюємо набуті навички

Додаткове відео-заняття: використання функцій баз даних. Умови відбору довільної складності.

Магія використання динамічних посилань і діапазонів. Блок - 2

• Додаємо в арсенал ДВПОСИЛ (INDIRECT) і АДРЕСА (ADDRESS)
• Широкі можливості і прикрі обмеження пари ДВПОСИЛ (INDIRECT) - АДРЕСА (ADDRESS)
• Розширюємо можливості автоматизації за допомогою інформаційних функції: КОМІРКА (CELL), ЛИСТ (SHEET), ЛИСТИ (SHEETS)
• Унікальні практичні кейси з використанням динамічних діапазонів

Іменовані формули і діапазони. Працюємо активно з динамічним іменованим діапазоном

• Способи створення іменованих діапазонів, використання їх в формулах
• Іменовані формули і їх використання
• Область видимості імен. Управління іменами
• Динамічні іменовані діапазони: використання ЗМІЩ (OFFSET), посилальної форми ІНДЕКС (INDEX)
• Створення дворівневих залежних випадаючих списків - використання ДВПОСИЛ (INDIRECT)
• Створення багаторівневих залежних випадаючих списків

Концепція масивів в Excel. Вирішення складних задач за допомогою формул масивів

• Концепція масивів. Масиви констант. Одномірні і двомірні масиви
• Формули масивів з результатом - новий масив
• Формули масивів з результатом - одне значення
• Практичні кейси з використанням формул масивів
• Корисний кейс: автоматичне сортування числових і текстових елементів за допомогою формул масивів

Практикум: створюємо автоматизовану систему управлінського обліку

• Створюємо концепцію автоматизації: як правильно організувати, щоб система працювала потім сама
• Автоматизуємо роботу з банком, клієнтами, постачальниками, проектами, угодами, тощо
• Виконуючи великий практичний кейс резюмуємо і закріплюємо отримані в попередніх модулях навички

Просунуте використання інструментів "Перевірка даних" і "Умовне форматування"

• Накладення умов будь-якої складності на обмеження введення даних в комірки за допомогою логічних формул
• Практичні кейси (завдання про обмеження бюджету, заборону введення дублікатів, введення тексту за маскою, платіжний день - тільки середовище та ін.)
• Нюанси і трюки тонкої настройки правил умовного форматування
• Робимо умови на форматування необмежено складними за допомогою логічних формул
• Цікаві практичні кейси з умовним форматуванням

Елементи управління: робимо звіти і аналітичні панелі (Dashboard) інтерактивними

• Смуги прокрутки, кнопки, прапорці та перемикачі для управління відображенням даних
• Зв'язок елементів управління з комірками і як цим вміло користуватися
• ПРАКТИКУМ: Створення звіту зі смугами прокрутки, перемикачами сортування за стовпцями

Модель даних. Power Pivot для побудови і роботи з моделлю даних

• Реляційні бази даних, первинні і зовнішні ключі, зв'язку між таблицями, типи зв'язків, напрям зв'язку
• Імпорт таблиці з різних джерел в модель даних, зв'язування таблиць
• Обчислювані стовпці і заходи: розуміємо суть і різницю
• ПРАКТИЧНИЙ кейс: об'єднуємо дані різних відділів компанії в модель даних і будуємо аналітику за допомогою зведених таблиць Excel
• Ключові показники ефективності (KPI) і ієрархії: додаємо в аналітику

Модель даних і Power Pivot: основи Dax

• Концепція DAX і використання основних функцій, що мають аналоги в Excel
• Розуміння "контексту рядків" і "контексту фільтрів"
• Перетворення контексту за допомогою функцій - ітераторів
• ПРАКТИЧНИЙ кейс: розширюємо аналітику компанії, додаючи показники в модель даних

Додаткове відео-заняття. OLAP Куб. Використання функцій Excel для роботи з OLAP кубами ~ 1.5 год.

Візуалізація. Діаграми Excel: трюки візуалізації, створення інтерактивних діаграм

• Імітація різних візуальних об'єктів за допомогою діаграм Excel
• Використання фігур в діаграмах, зв'язок фігур з комірками
• Факторний аналіз: каскадна діаграма (діаграма "водоспад")
• Інтерактивні діаграми з масштабуванням і "прокручуванням" рядів даних
• Використання елементів управління для управління інтерактивними діаграмами

Візуалізація, Power View i Power Map

• Концепція звітів Power View, плюси і мінуси в порівнянні з діаграмами Excel
• Будуємо аналітичну панель (dashboard) Power View на моделі даних
• Які типи візуальних об'єктів Power View для якої візуалізації доречні
• Додаємо фільтри, слайсери, "провалювання" (drill-down) на різні рівні деталізації даних
• Візуалізуємо дані на карті за допомогою Power Map

Прогнозуємо. Інструменти і функції Excel для прогнозування

• Занурюємося в концепцію прогнозування та основні статистичні поняття
• Лист прогнозу в Excel 2016, метод експоненціального згладжування; в яких випадках це може підійти
• Лінійні і нелінійні моделі, як вибирати модель, функції Excel ТЕНДЕНЦІЯ (TREND), ЗРСТАННЯ (GROWTH) та інші
• Як використовувати функцію ТЕНДЕНЦІЯ (TREND) для моделювання нелінійних задач
• Прогнозуємо виведення нового продукту на ринок, обсяг наступного замовлення
• Інструменти "пакета аналізу" для прогнозування
• Множинна лінійна регресія: прогнозуємо прибуток підприємства

Розв'язання оптимізаційних задач за допомогою "Пошук рішення" (Solver)

• Лінійні і нелінійні задачі. Вибір методу рішення. Еволюційний метод вирішення
• Задача про оптимальний "продуктовий мікс"
• Задача про оптимальний графік роботи персоналу
• Задача про вибір оптимального місця розміщення складу
• Задача про знаходження оптимального маршруту

Попередні вимоги

Мати навички:

• Робота з формулами і функціями робочих аркушів Excel
• Використання інструментів "перевірка даних" (data validation) і "умовне форматування" (conditional formatting)
• Зведені таблиці та зведені діаграми
• Візуалізація

Особливості курсу

• Доступ до відео і підтримка
• Поглиблені інструменти
• Індивідуальна підтримка
• Практичні кейси
• Робота по одному кліку
• Навчання на реальних бізнес-даних

Викладачі курсу

• Євген Довженко - CEO, автор курсів і Power BI
• Михайло Беленчук - Тренер Excel