Excel Pro
Длительность: 20 часов
Стоимость: 40 000 ₸
Microsoft Excel Pro
Курс рассчитан на опытных пользователей Excel, задачами которых является подготовка и анализ больших массивов данных.
Чему научатся слушатели
- Определять типы данных в ячейках и преобразовывать типы данных для большого количества ячеек (переводить числа в текст, текст в числа и даты, работать с логическими значениями и ошибками в ячейках)
- Создавать нестандартные числовые форматы при помощи кодов
- Применять расширенный фильтр (через таблицу с условиями) для продвинутой фильтрации больших таблиц
- Выполнять сортировку с использованием пользовательского ключа, сортировать по нескольким столбцам и делать горизонтальную сортировку
- Выполнять построение и настройку Сводных таблиц и сводных диаграмм
- Использовать продвинутые вариации функций XLOOKUP, VLOOKUP, HLOOKUP, INDEX, MATCH в различных ситуациях
- Выполнять построение сложных формул с использованием непрямых ссылок на ячейки через OFFSET и INDIRECT
- Выполнять сложные вычисления и манипуляции при помощи формул массива
- Использовать инструменты анализа ЧТО-ЕСЛИ для моделирования ситуаций
- Создавать запросы Power Query на начальном уровне
- Создавать и использовать макросы на начальном уровне
- Выполнять защиту документа от изменений
- Предоставлять общий доступ к файлу для одновременной совместной работы с другими людьми
Программа курса
МОДУЛЬ 1: Введение
Типы данных в ячейках
Отличие констант и формул. Работа с типами данных: число, текст, логическое, ошибка, пустота. Техники выделения по типам данных. Преобразование типов данных при помощи инструментов и функций.
Пользовательские числовые форматы
Написание своих кодов форматов для вывода чисел и текста в нужном виде.
МОДУЛЬ 2: Построение отчетов
Работа со списками
Простой и расширенный фильтр, простая и сложная сортировка. Преобразование данных в Таблицу. Применение проверки данных. Инструмент Промежуточные Итоги для подведения итогов. Группировка данных.
Сводные таблицы
Построение сводных таблиц с применением фильтров, группировки, стандартных и дополнительных вычислений, настройка внешнего вида отчета, Срезы. Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ.
Сводные диаграммы
Построение сводных диаграмм на базе сводных таблиц, применение фильтров и Срезов.
МОДУЛЬ 3: Формулы и функции
Функции подстановки (ПРОСМОТРX, ВПР, ГПР, ИНДЕКС, ПОИСКПОЗ)
Функции переноса/подстановки данных, примеры использования ПРОСМОТРX, ВПР, ИНДЕКС/ПОИСКПОЗ, поиск по нескольким полям, продвинутые примеры подстановки. Надстройка Fuzzy Lookup.
Функции ссылок и массивов (ДВССЫЛ, СМЕЩ, ТРАНСП)
Построение формул с обращением к ячейкам «на напрямую». Формирование адреса в виде текста и преобразование в ссылку через ДВССЫЛ, примеры ДВССЫЛ+СУММЕСЛИ для выборки данных по листам. Функция СМЕЩ для манипулирования ячейками, функция ТРАНСП для динамического транспонирования.
Формулы массива
Построение простых и сложных формул массива для решения нестандартных задач. Сокращение длины формулы через применение массива внутри формулы.
Динамические массивы и новые функции Excel 365
Особенности динамических формул массива в Excel 365. Функции для динамических массивов ФИЛЬТР, УНИК, СОРТ, СОРТПО, СЛУЧМАССИВ, ПОСЛЕДОВ. Функции LET и LAMBDA.
МОДУЛЬ 4: Инструменты для анализа данных и моделирования
Консолидация листов
Инструмент Консолидация на вкладке Данные.
Анализ ЧТО-ЕСЛИ
Инструмент Подбор параметра для поиска константы при известном результате формулы, использование Сценариев, анализ данных при помощи Таблиц подстановки.
Использование Элементов управления (Controls) на рабочем листе
Создание выпадающих списков, галочек, переключателей, счетчиков и полос прокрутки для изменения данных в ячейках и управления работой формул.
Поиск решения
Решение задач на оптимальный план: транспортная задача на минимальный расход топлива и производственная задача на максимальную прибыль при заданных ограничениях ресурсов.
МОДУЛЬ 5: Введение в макросы
Введение в макросы (VBA)
Создание нескольких макросов и функций с объяснением основных принципов и элементов языка программирования: где хранятся макросы, способы создания, инструмент записи, способы запуска. Работа с переменными, типы данных, операции присвоения, простые вычисления, конструкция If-Then-Else, работа с циклами For Each, For Next, перехват ошибок через On Error, работа с объектом Range. Создание новых функций с использованием существующих функций Excel.
МОДУЛЬ 6: Безопасность и общий доступ
Защита документа
Удаление конфиденциальных сведений из файла. Защита на уровне файла, пароль на открытие и изменение. Защиты структуры листов. Защита ячеек отдельных листов.
Предоставление общего доступа
Сохранение файла в облаке. Предоставление общего доступа в письме или по ссылке.