MS Excel Advanced: продвинутые инструменты

Целевая аудитория

• аналитик продаж или маркетинга
• финансист или финансовый аналитик
• маркетолог либо Интернет-маркетолог
• сотрудник отдела продаж
• сотрудник отдела логистики
• руководитель отдела или топ менеджер
• владелец бизнеса

Цель мероприятия

Курс обучает использованию продвинутых и сложных инструментов Excel, содержит множество реальных примеров бизнес-аналитики и бизнес задач. При условии качественной проработки всех занятий, выполнения Всех домашних заданий и комплексного курсового проекта Вы точно станете "Богом" MS EXCEL

Программа

Модуль-01. Подключение к внешним источникам и очистка данных: редактор запросов (POWER QUERY)

• особенности подключения к разным источникам данных: БД, другие файлы Excel, папки с файлами, .csv, сайтам в Интернете и др.          
• принципы создания и работы с запросами на извлечение и очистку данных
• работаем со столбцами и строками, замена значений, pivot, unpivot и другие операции
• объединение (append) и слияние (merge) запросов. Понимаем типы соединений (join)
• работаем с параметрическими запросами 

Модуль-02. Подключение к внешним источникам и очистка данных: основы языка "M"

• понимаем синтаксис "M", инструкция let … in
• работаем с основными встроенными функциями "M
• создание пустого запроса, добавление скрипта на "М"
• создание и использование пользовательских функций
• обходим ограничения пользовательского интерфейса редактора запросов

Модуль-03. Магия использования динамических ссылок и диапазонов. блок-1

• как работают ссылочные функции Excel: выходим на новый уровень понимания формул
• использование СМЕЩ (OFFSET) для организации динамических диапазонов
• мощь ссылочной формы Индекс (INDEX)
• волатильные и неволатильные функции, оптимизация процессов вычислений в Excel
• трюки использования ссылочных форм Выбор (CHOOSE) и ЕСЛИ (IF)
• Практикум: создаем отчет на основе "плавающих" (динамических) диапазонов, закрепляем приобретенные навыки
• Дополнительное видео-занятие. использование функций баз данных: условия отбора произвольной сложности

Модуль-04. Магия использования динамических ссылок и диапазонов. блок-2 добавляем в арсенал ДВССЫЛ (INDIRECT) и АДРЕС (ADDRESS)

• широкие возможности и досадные ограничения пары ДВССЫЛ (INDIRECT) - АДРЕС (ADDRESS)
• расширяем возможности автоматизации с помощью информационных функциq: ЯЧЕЙКА (CELL), ЛИСТ (SHEET), ЛИСТЫ (SHEETS)
• уникальные практические кейсы с использованием динамических диапазонов

Модуль-05. Именованные формулы и диапазоны. Работаем активно с динамическими именованными диапазонами

• Способы создания именованных диапазонов, использование их в формулах
• Именованные формулы и их использование
• Область видимости имен. Управление именами
• Динамические именованные диапазоны: использование СМЕЩ (OFFSET), ссылочной формы ИНДЕКС (INDEX)
• Создание двухуровневых зависимых выпадающих списков – использование ДВССЫЛ (INDIRECT)
• Создание многоуровневых зависимых выпадающих списков

Модуль-06. Концепция массивов в excel. Решение сложных задач с помощью формул массивов

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

Модуль-07. Практикум: создаем автоматизированную систему управленческого учета

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

Модуль-08. Продвинутое использование инструментов «проверка данных» и «условное форматирование»

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

Модуль-09. Элементы управления: делаем отчеты и аналитические панели (dashboards) интерактивными

• Полосы прокрутки, кнопки, флажки и переключатели для управления отображением данных
• Связь элементов управления с ячейками и как этим умело пользоваться
• Практикум: Создание отчета с полосами прокрутки, переключателями сортировки по столбцам

Модуль-10. Модель данных. Рower pivot для построения и работы с моделью данных

• Реляционные базы данных, первичные и внешние ключи, связи между таблицами, типы связей, направление связи
• Импорт таблицы из разных источников в модель данных, связывание таблиц
• Вычисляемые столбцы и меры: понимаем суть и разницу
• Практический кейс: объединяем данные разных отделов компании в модель данных и строим аналитику с помощью сводных таблиц Excel
• Ключевые показатели эффективности (KPI) и иерархии: добавляем в аналитику

Модуль-11. Модель данных и power pivot: основы dax

• Концепция DAX и использование основных функций, имеющих аналоги в Excel
• Понимание "контекста строк" и "контекста фильтров"
• Преобразование контекста с помощью функций - итераторов
• Практический кейс: расширяем аналитику компании, добавляя показатели в модель данных

Дополнительное видео-занятие. olap куб. использование функций excel для работы с olap кубами

Модуль-12. Визуализация. Диаграммы excel: трюки визуализации, создание интерактивных диаграмм

• Имитация различных визуальных объектов с помощью диаграмм Excel
• Использование фигур в диаграммах, связь фигур с ячейками
• Факторный анализ: каскадная диаграмма (диаграмма «водопад»)
• Интерактивные диаграммы с масштабированием и «прокручиванием» рядов данных
• Использование элементов управления для управления интерактивными диаграммами

Модуль-13. Визуализация. power view и power map

• концепция отчетов Power View, плюсы и минусы по сравнению с диаграммами Excel
• строим аналитическую панель (dashboard) Power View на модели данных
• какие типы визуальных объектов Power View для какой визуализации уместны
• добавляем фильтры, слайсеры, "проваливания" (drill-down) на разные уровни детализации данных
• визуализируем данные на карте с помощью Power Map

Модуль-14. Прогнозируем. Инструменты и функции excel для прогнозирования погружаемся в концепцию прогнозирования и основные статистические понятия

• лист прогноза в Excel 2016, метод экспоненциального сглаживания; в каких случаях это может подойти
• линейные и нелинейные модели, как выбирать модель, функции Excel ТЕНДЕНЦИЯ (TREND), РОСТ (GROWTH) и другие
• как использовать функцию ТЕНДЕНЦИЯ (TREND) для моделирования нелинейных задач
• прогнозируем вывод нового продукта на рынок, объем следующего заказа
• инструменты "пакета анализа" для прогнозирования
• множественная линейная регрессия: прогнозируем прибыль предприятия

Модуль-15. Решение оптимизационных задач с помощью «поиск решения» (solver)

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