Курс рассчитан на опытных пользователей Excel, задачами которых является подготовка и анализ больших массивов данных.

Чему научатся слушатели:

Определять типы данных в ячейках и преобразовывать типы данных для большого количества ячеек (переводить числа в текст, текст в числа и даты, работать с логическими значениями и ошибками в ячейках)


Создавать нестандартные числовые форматы при помощи кодов


Применять расширенный фильтр (через таблицу с условиями) для продвинутой фильтрации больших таблиц


Выполнять сортировку с использованием пользовательского ключа, сортировать по нескольким столбцам и делать горизонтальную сортировку


Выполнять построение и настройку Сводных таблиц и сводных диаграмм


Использовать продвинутые вариации функций VLOOKUP, HLOOKUP, INDEX, MATCH в различных ситуациях


Выполнять построение сложных формул с использованием непрямых ссылок на ячейки через OFFSET и INDIRECT


Выполнять сложные вычисления и манипуляции при помощи формул массива


Использовать инструменты анализа ЧТО-ЕСЛИ для моделирования ситуаций


Создавать запросы Power Query на начальном уровне


Создавать и использовать макросы на начальном уровне


Выполнять защиту документа от изменений


Предоставлять общий доступ к файлу для одновременной совместной работы с другими людьми

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

МОДУЛЬ 1: Введение

• Типы данных в ячейках


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


• Пользовательские числовые форматы


• Написание своих кодов форматов для вывода чисел и текста в нужном виде.




МОДУЛЬ 2: Построение отчетов

• Работа со списками


• Простой и расширенный фильтр, простая и сложная сортировка. Преобразование данных в Таблицу. Применение проверки данных. Инструмент Промежуточные Итоги для подведения итогов. Группировка данных.


• Сводные таблицы


• Построение сводных таблиц с применением фильтров, группировки, стандартных и дополнительных вычислений, настройка внешнего вида отчета, Срезы. Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ.


• Сводные диаграммы


• Построение сводных диаграмм на базе сводных таблиц, применение фильтров и Срезов.




МОДУЛЬ 3: Формулы и функции

• Функции подстановки (ВПР, ГПР, ИНДЕКС, ПОИСКПОЗ)


• Функции переноса/подстановки данных, множество примеров ВПР, где для аргументов применяются вложенные функции ЕСЛИ, ПОИСКПОЗ, сложный ключ. Недостатки ВПР и преимущество пары ИНДЕКС(ПОИСКПОЗ()). Надстройка Fuzzy Lookup.


• Функции ссылок и массивов (ДВССЫЛ, СМЕЩ, ТРАНСП)


• Построение формул с обращением к ячейкам «на напрямую». Формирование адреса в виде текста и преобразование в ссылку через ДВССЫЛ, примеры ДВССЫЛ+СУММЕСЛИ для выборки данных по листам. Функция СМЕЩ для манипулирования ячейками, функция ТРАНСП для динамического транспонирования.


• Формулы массива


• Построение простых и сложных формул массива для решения нестандартных задач. Сокращение длины формулы через применение массива внутри формулы.


• Динамические массивы и новые функции Excel 365


• Особенности динамических формул массива в Excel 365. Функции для динамических массивов ПРОСМОТРХ, ФИЛЬТР, УНИК, СОРТ, СОРТПО, СЛУЧМАССИВ, ПОСЛЕДОВ. Функции LET и LAMBDA.
МОДУЛЬ 4: Инструменты для анализа данных и моделирования

• Консолидация листов


• Инструмент Консолидация на вкладке Данные.


• Анализ ЧТО-ЕСЛИ


• Инструмент Подбор параметра для поиска константы при известном результате формулы, использование Сценариев, анализ данных при помощи Таблиц подстановки.


• Использование Элементов управления (Controls) на рабочем листе


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


• Поиск решения


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




МОДУЛЬ 5: Безопасность и общий доступ

• Защита документа


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


• Предоставление общего доступа


• Сохранение файла в облаке. Предоставление общего доступа в письме или по ссылке.