Курс Автоматизация работы в Excel средствами VBA и Power Query

Код: ITCloud-Exc-AVBPQ

Продолжительность курса

40 Академических часов

Ближайшие курсы

Октябрь

02.10.2023 - 06.10.2023 Ект

Февраль

12.02.2024 - 16.02.2024 Ект

В программе рассматриваются объекты приложения Microsoft Excel и управление этими объектами. Кроме этого, слушатели познакомятся с языком программирования Visual Basic Application и надстройкой Power Query, научаться разрабатывать приложения для Excel с помощью макросов VBA и запросов Power Query.
Курс предназначен для версии Microsoft Excel 2019/2016.

Стоимость курса

37 800 руб
Заказать

Вы научитесь:

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

Аудитория:

Настоящая программа составлена для повышения квалификации специалистов. Курс предназначен для специалистов, желающих получить теоретические и практические знания для создания макросов и запросов в приложениях Excel.

Требования к предварительной подготовке слушателя:

  • Знания в пределах курса «Электронные таблицы Microsoft Excel. Углубленный курс»
  • Понимание основ программирования
  • Знание английского языка в пределах чтения компьютерно-ориентированного материала.

Содержание курса

Модуль 1. Язык Visual Basic for Application
• Структура проекта. Вставка модуля VBA. Ввод и запуск подпрограмм VBA. Расположение процедур в модуле.
• Переменные. Типы данных переменных VBA. Описание переменных. Объектные переменные. Типы данных по умолчанию. Использование массивов в VBA. Область действия переменных.
• Операторы. Оператор присваивания. Расположение нескольких операторов на одной строке. Комментарии. Операторы перехода и выбора. Оператор повтора. Оператор with.
• Подпрограммы и функции. Процедура. Процедура-подпрограммы и процедура-функция. Переход в подпрограмму и возвращение из подпрограммы. Вызов процедуры. Область действия процедур и функций.
• Использование одномерных и многомерных массивов
Динамические массивы
• Процедуры обработки ошибок и отладка программы. Разработка процедур, предотвращающих появление ошибок. Перехват и обработка ошибок. Отладка программ. Ошибки компиляции, выполнения и логические ошибки. Пошаговое выполнение программы. Точка останова. Вывод значений свойств и переменных.

Модуль 2. Объектная модель Excel
• Свойства и методы объектов. Получение и установка свойств объектов. Вызов методов. Передача аргументов методами. Передача аргументов по имени.
• Ссылки на объекты: единичные объекты и семейства объектов. Семейства – это объекты. Примеры единичных объектов и семейств. Прямая ссылка на объекты и ссылка с помощью индекса в семействе. Индексация семейств по номеру и по имени. Исключение из правил – объект Range.
• Иерархия объектов Excel. Иерархическая схема объектов. Уровни иерархии. Использование иерархии объектов Excel. Неявные ссылки на объекты. С какого уровня следует начинать ссылки на объект.
• Объекты Application, Workbook, Worksheet, Range и Selection. Объект Application. Свойства объекта Application. Методы объекта Application.
• Объект Workbook и семейство Workbooks. Свойства объекта Workbook. Методы объекта Workbook. Объект Worksheet и семейство Worksheets. Свойства объекта Worksheet. Методы объекта Worksheet. События объектов Workbook и Worksheet.
• Объект Range. Адресация ячеек. Задание групп строк и столбцов с помощью объекта Range. Связь объекта Range и семейства Cells. Свойства объекта Range. Методы объекта Range. Объект Selection.

Модуль 3. Работа с файловой системой
• Открытие документа Excel из программы. Свойство FileDialog объекта Application.
• Методы объекта Application для управления файлами и папками.
• Автоматическое создание списка всех файлов в рабочей папке. Функция Dir

Модуль 4. Использование встроенных средств Excel в макросах
• Методы Сортировка, Промежуточные итоги, Автофильтр, Прогрессия, Автозаполнение. Найти.
• Использование Excel для создания инструментов анализа данных. Объекты, используемые для анализа данных в Excel. Объект PivotTable. Объект PivotCache.

Модуль 5. Разработка пользовательского интерфейса
• Понятие форм в Excel. Виды форм. Форма рабочего листа. Объекты используемые для создания форм.
• Добавление пользовательских форм UserForm в проект VBA
Добавление элементов управления в UserForm
Создание процедур обработки событий для элементов формы
• Элементы управления. Размещение элементов управления на форме. Режим конструктора. Установка свойств элементов управления. Написание кода обработки событий для элементов управления. Общие свойства, методы и события элементов управления. Свойства и события элемента управления CommandButton. Элементы управления CheckBox, OptionButton. Элементы управления ListBox и ConboBox. Элементы управления TextBox, Label, Image, Frame и RefEdit.

Модуль 6. Работа с данными с помощью запросов Power Query
• Назначение Power Query, интерфейс Power Query, структура запроса, простые операции в запросе, создание несложных запросов, обновление запроса.
• Подключение к различным типам источников данных
• Навигация по источнику
• Параметризация запросов.

Модуль 7. Преобразование структуры данных с помощью запросов Power Query
• Операции со столбцами: удаление и фильтрация, объединение и разделение, операции с текстом, числами и датами, другие операции.
• Операции со строками: удаление и фильтрация, удаление дубликатов, другие операции.
• Операции с таблицами: Pivot (Сведение по столбцу), Unpivot (Отмена свёртывания столбцов), транспонирование, группировка строк.
• Взаимодействие макросов VBA с запросами Power Query