Продвинутый уровень Microsoft Excel: применение инструментов для автоматизации процессов обработки и анализа данных. Практикум для специалистов

День 1.

Продвинутые функции Excel: сложные формулы и функции для анализа данных.

• Создание формул для данных с нескольких листов.
• Расчет выражений, зависящих от сложных условий.
• Встроенные функции для работы с ссылками.
• Применение функций регрессионного анализа.
• Использование именованных диапазонов.
• Создание выпадающих списков.
• Автоматическое подведение итогов
• Вложенные функции.
• Извлечение значений из таблиц.
• Функции ВПР и ГПР.
• ВПР для переноса данных между таблицами.
• Логика поиска данных ВПР.
• Поиск названия строки по нескольким столбцам.
• Для каких таблиц создан интервальный просмотр.
• ИНДЕКС+ПОИСКПОЗ для переноса данных между таблицами.
• Как устроен ИНДЕКС.
• Помощник ПОИСКПОЗ.
• ЕСЛИ от 0 до PRO логические функции.
• Логика построения функции ЕСЛИ и ЕСЛИОШИБКА.
• Настройка числовых и текстовых условий.
• Варианты сравнения больше/меньше/равно/неравно/содержит.
• Последовательные проверки вложенными ЕСЛИ.
• Одновременная проверка нескольких условий.
• СУММЕСЛИ/МН логика расчетов по условию
• Числовые и текстовые условия.
• Варианты сравнения больше/меньше/равно/неравно/содержит.
• ФУНКЦИИ ДАТЫ и ВРЕМЕНИ
• Расчеты по времени (часовые тарифы, интервалы).
• Расчеты по месяцам.
• Вычисление рабочего дня.
• ТЕКСТОВЫЕ ФУНКЦИИ
• Перенос в отдельную ячейку первых, последних или средних знаков ЛЕВСИМВ, ПРАВСИМВ, ПСТР
• Как посчитать знаки, если нет явной числовой закономерности.

Динамические диаграммы.

• Динамические диаграммы с автоматическим добавлением данных.
• Добавление ряда в диаграмму.
• Диаграммы с элементами управления.
• Диаграмма с выбором отображаемых данных флажками.
• Диаграмма Парето.
• Диаграмма Ганта.
• Приближение диаграммы.

Практикумы.

День 2.

Визуализация данных: гистограммы и дашборды.

• Стандартные и динамические графики.
• Гистограмма с перекрытием.
• Нормированная центрованная гистограмма.
• Визуальное представление данных с помощью SmartArt.
• Функции и область применения дашборда.
• Сбор данных для дашборда.
• Проектирование и дизайн дашборда.
• Спарклайны.
• Состыкованная гистограмма.
• Макет дашборда.
• Инструменты форматирования.

Динамические отчеты.

• Подготовка данных.
• Создание отчетов.
• Обновление данных.
• Расчеты в сводных таблицах с фильтрацией и срезами.
• Группировка по датам и числовым рядам.
• Группировка сводных таблиц по листам.
• Применение результирующей ячейки для выборки связанных данных.
• Преобразование сводной таблицы в типовой диапазон.

Организация совместного доступа и защиты.

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

Практикумы.

День 3.

Работа с внешними источниками.

• Экспорт и импорт информации.
• Объединение данных из нескольких источников.
• Разбиение одного столбца с данными на несколько.
• Перенос данных в Google таблицы.
• Работа с базами данных.
• Возможности надстройки Power Pivot.
• Возможности надстройки Power Query.

Работа с макросами.

• Настройка Excel.
• Автоматизация повторяющихся задач.
• Запись макросов макрорекордером.
• Параметры безопасности макроса.
• Создание кнопок для запуска макросов.
• Создание пользовательских функций.
• Использование абсолютных и относительных ссылок при записи.
• Редактирование макросов и функций.

Практикумы.

День 4.

Автоматизация: работа со сценариями.

• Составление сценариев: подбор параметра и поиск решения.
• Представление альтернативных наборов данных.

Программирование в Excel.

• Редактор Visual Basic: интерфейс и ввод кода. Язык VBA.
• Логические выражения.
• Операторы условного перехода.
• Оператор простого выбора If…Then…Else.
• Оператор сложного выбора Select…Case.
• Оператор безусловного перехода GoTo.
• Использование циклов для автоматизации процессов.
• Циклы «до тех пор, пока…» Do…While, For … Next, While … Wend.
• Операторы работы с большими объемами данных, управления объектами и коллекциями.
• Оператор With…End With.
• Программная обработка ошибок.
• Процедуры Sub, Function.
• Объекты Worksheet, Range, Cells.
• Использование Браузера объектов.

Практикумы.

Занятия проводятся в компьютерном классе.