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

Модуль 1. ПОДКЛЮЧЕНИЕ К ВНЕШНИМ ИСТОЧНИКАМ И ОЧИСТКА ДАННЫХ: РЕДАКТОР ЗАПРОСОВ (POWER QUERY)

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

Модуль 2. ПОДКЛЮЧЕНИЕ К ВНЕШНИМ ИСТОЧНИКАМ И ОЧИСТКА ДАННЫХ: ОСНОВЫ ЯЗЫКА "M"

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

Модуль 3. МАГИЯ ИСПОЛЬЗОВАНИЯ ДИНАМИЧЕСКИХ ССЫЛОК И ДИАПАЗОНОВ. БЛОК-1

• как работают ссылочные функции Excel: выходим на новый уровень понимания формул
• использование СМЕЩ (OFFSET) для организации динамических диапазонов
• мощь ссылочной формы ИНДЕКС (INDEX)
• волатильные и неволатильные функции, оптимизация процессов вычислений в Excel
• трюки использования ссылочных форм ВЫБОР (CHOOSE) и ЕСЛИ (IF)
• ПРАКТИКУМ: создаем отчет на основе "плавающих" (динамических) диапазонов, закрепляем приобретенные навыки
• ДОПОЛНИТЕЛЬНОЕ ВИДЕО-ЗАНЯТИЕ. ИСПОЛЬЗОВАНИЕ ФУНКЦИЙ БАЗ ДАННЫХ: УСЛОВИЯ ОТБОРА ПРОИЗВОЛЬНОЙ СЛОЖНОСТИ

Модуль 4. МАГИЯ ИСПОЛЬЗОВАНИЯ ДИНАМИЧЕСКИХ ССЫЛОК И ДИАПАЗОНОВ. БЛОК-2

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

Модуль 5. ИМЕНОВАННЫЕ ФОРМУЛЫ И ДИАПАЗОНЫ. РАБОТАЕМ АКТИВНО С ДИНАМИЧЕСКИМИ ИМЕНОВАННЫМИ ДИАПАЗОНАМИ

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

Модуль 6. КОНЦЕПЦИЯ МАССИВОВ В EXCEL. РЕШЕНИЕ СЛОЖНЫХ ЗАДАЧ С ПОМОЩЬЮ ФОРМУЛ МАССИВОВ

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

Модуль 7. ПРАКТИКУМ: СОЗДАЕМ АВТОМАТИЗИРОВАННУЮ СИСТЕМУ УПРАВЛЕНЧЕСКОГО УЧЕТА

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

Модуль 8. ПРОДВИНУТОЕ ИСПОЛЬЗОВАНИЕ ИНСТРУМЕНТОВ «ПРОВЕРКА ДАННЫХ» И «УСЛОВНОЕ ФОРМАТИРОВАНИЕ»

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

Модуль 9. ЭЛЕМЕНТЫ УПРАВЛЕНИЯ: ДЕЛАЕМ ОТЧЕТЫ И АНАЛИТИЧЕСКИЕ ПАНЕЛИ (DASHBOARDS) ИНТЕРАКТИВНЫМИ

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

Модуль 10. МОДЕЛЬ ДАННЫХ. POWER 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)

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