Excel: Бизнес-анализ и прогнозирование

Модуль 1. МАНИПУЛИРОВАНИЕ БОЛЬШИМИ МАССИВАМИ ДАННЫХ Горячие клавиши Excel Специальная вставка: транспонирование и основные арифметические преобразования Многоуровневая сортировка и расширенная фильтрация данных Специальные символы и сложные условия отбора данных Удаление дубликатов и «текст по столбцам» «Умная таблица»: свойства и использование, срезы Модуль 2. ПОДКЛЮЧЕНИЕ К ВНЕШНИМ ИСТОЧНИКАМ И ОЧИСТКА ДАННЫХ Классический инструмент «подключение к внешним источникам данных»: подключение к внешним текстовым файлам, страницам в Интернете и базам данных Управление подключениями, настройка автоматического обновления Связка “SQL (язык запросов к БД) – Excel” Инструменты Power BI в Excel - POWER QUERY: расширяем возможности работы с внешними источниками, очистка данных POWER QUERY: группировка, агрегирование, Pivot и Unpivot, др. Модуль 3. ГЛУБОКОЕ ПОНИМАНИЕ ФОРМУЛ И ФУНКЦИЙ. СОЗДАНИЕ МЕГАФОРМУЛ. ТЕКСТОВЫЕ ФУНКЦИИ Ячейки как переменные, абсолютная, относительная и смешанная адресации Глубокое понимание функций Excel, аргументы как входные параметры Использование функций обработки текстовой информации: ПОИСК (SEARCH), ЛЕВСИМВ (LEFT), ПСТР (MID) и др. Техника создания сложных формул (мегаформул) Модуль 4. РАБОТАЕМ РАСШИРЕННО С ДАТАМИ И ВРЕМЕНЕМ. МАТЕМАТИЧЕСКИЕ И ЛОГИЧЕСКИЕ ФУНКЦИИ Аналитика дат и времени: ТДАТА (NOW), ДЕНЬНЕД (WEEKDAY), ВРЕМЗНАЧ (TIMEVALUE) и др. Использование математических функций: ОКРУГЛ (ROUND), ЦЕЛОЕ (INT) и др. Понимаем логические операции, используем логические функции: И (AND), ИЛИ (OR), ЕСЛИ (IF) Модуль 5. ИСКУССТВО «ПОДТЯГИВАНИЯ» ДАННЫХ: ФУНКЦИИ ССЫЛОК И МАССИВОВ Классика: ВПР (VLOOKUP) и ГПР (HLOOKUP) – все нюансы использования Точное или приблизительное совпадение? Понимаем почему их два ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH) - «подтягиваем неподтягиваемое» Усложняем и автоматизируем: сложные кейсы «подтягивания данных» Модуль 6. «ТЯНЕМ» ДАННЫЕ И ФОРМИРУЕМ ПОКАЗАТЕЛИ В ОТЧЕТАХ: ФУНКЦИИ ПОДСЧЕТА И СУММИРОВАНИЯ Условные и безусловные функции Искусство использования СУММЕСЛИ (SUMIF), СЧЁТЕСЛИМН (COUNTIFS) и др. Использование сложных условий и символов подстановки Лайфхаки структурирования и форматирования больших формул Трюки для быстрого создания «многоэтажных» формул Модуль 7. ПРАКТИКУМ. СТРОИМ АВТОМАТИЧЕСКИ ОБНОВЛЯЕМЫЙ DASHBOARD Создаем заготовку отчета: повторяем и закрепляем инструменты манипулирования данными Заполняем отчет показателями: резюмируем работу с формулами и функциями Функции обработки ошибок ЕСЛИОШИБКА (IFERROR), ЕНД (IFNA) и др. Добавляем лайфхаки: быстрая вставка пустых строк перед каждой и др. Модуль 8. ПРАКТИКУМ. DASHBOARD: ДОБАВЛЯЕМ ИНТЕРАКТИВНОСТЬ И ВИЗУАЛИЗАЦИЮ Создаем структуру: многоуровневая группировка и настройки структуры «Проверка данных»: типы ограничений, использование условий любой сложности «Проверка данных»: добавляем систему выпадающих списков «Условное форматирование»: встроенные группы правил «Условное форматирование»: классификация типов правил, как находить подходящий «Условное форматирование»: использование формул для задания сложных условий «Условное форматирование»: превращаем конфликты правил в возможности Визуализируем показатели с помощью спарклайнов (мини диаграммы) Модуль 9. ВИЗУАЛЬНЫЙ АНАЛИЗ ДАННЫХ: ДИАГРАММЫ EXCEL Анализ динамики показателей: типы и подтипы диаграмм, настройки элементов, вторичная ось, как добиться адекватной визуализации Анализ структуры: работа с 3D диаграммами, настройка фигур, круговые и вторичные круговые диаграммы Анализ зависимостей (корреляции): точечные и пузырьковые диаграммы Трюки: использование «невидимого ряда», ссылок на ячейки в метках и фигурах и др. Трюки: имитация других типов диаграмм и объектов Модуль 10. «КРУТИМ» ДАННЫЕ «НА ЛЕТУ»: СВОДНЫЕ ТАБЛИЦЫ EXCEL. POWER PIVOT И МОДЕЛЬ ДАННЫХ Понимание «фактов» и «измерений», концепция «вращения куба данных», технологии OLAP Сводная таблицы на одном источнике данных: из таблицы в файле Excel и из внешнего источника Добавляем показатели, основанные на вычислениях одного поля Группировка: добавляем «измерения» Вычисляемые поля и вычисляемые объекты: добавляем показатели на основе вычислений на нескольких полях Использование срезов, временных шкал и сводных диаграмм Как «тянуть данные» из сводной таблицы в другие отчеты Модель данных и надстройка Power Pivot: сводные таблицы на модели данных Power Pivot: построение модели данных, связи между таблицами Power Pivot: вычисляемые столбцы и меры – в чем разница, язык формул DAX Power Pivot: иерархии и ключевые показатели эффективности (KPI) Модуль 11. ПРОГНОЗИРОВАНИЕ В MS EXCEL Концепция массивов и формулы массивов Формулы массивов с результатом «массив» и с результатом «значение» Понимаем суть прогнозирования и подходы Сглаживание исторических данных, методы сглаживания Сезонность – что это и как с ней работать? Сезонные коэффициенты, исключение и наложение сезонной компоненты Выбираем модель: линейные и нелинейные тренды, функция ТЕНДЕНЦИЯ (TREND) Моделируем будущее и накладываем сезонную составляющую, известные факторы и активности Модуль 12. РЕШЕНИЕ ОПТИМИЗАЦИОННЫХ ЗАДАЧ БИЗНЕСА: «АНАЛИЗ ЧТО-ЕСЛИ» И ДИСПЕТЧЕР СЦЕНАРИЕВ Модель оптимизационной задачи, входные параметры и целевые функции Для каких задач использовать «Таблицу данных»? Анализ чувствительности «Подбор параметра», при котором показатель (целевая функция) равен … Организуем наборы входных параметров в сценарии развития событий («Диспетчер сценариев»)