Excel VBA (Макросы) : Автоматизация бизнес-задач [2020] [DATAbi] [Анастасия Пасочник]

Excel VBA (Макросы) : Автоматизация бизнес-задач [2020] [DATAbi] [Анастасия Пасочник]

EXCEL VBA (МАКРОСЫ): АВТОМАТИЗАЦИЯ БИЗНЕС-ЗАДАЧ [2020]
DATAbi
Анастасия Пасочник

Особенности курса:
1. В основе курса - реальный проект

Изучать предмет, выполняя проект на реальных данных – один из самых эффективных подходов в образовании. Проходя курс, вы автоматизируете компанию “Smart Education Ltd”​

2. Глубоко структурировано

Четкая логика и стройная структура курса – это не мы себе придумали. Это главный фидбек наших выпускников, обучавшихся в разных компаниях. Этот курс – не исключение​

3. Тщательная проработка

Практическая работа на занятиях, проработка видео записей, обязательное выполнение домашних заданий, практикумы – это лишь некоторые части нашей системы обучения и развития навыков​

3. Система практикумов

Курс включает 3 модуля-практикума. Это не только возможность еще раз резюмировать пройденные части курса, но и новые особенности уже знакомых инструментов​

Спойлер: Содержание

Модуль 1. С ЧЕГО НУЖНО НАЧАТЬ: ЗАПИСЬ МАКРОСОВ И РЕДАКТИРОВАНИЕ КОДА. ОБЪЕКТ Range (ДИАПАЗОН ЯЧЕЕК)

Автоматизация "Smart Education Ltd":

Знакомство с проектом, положенным в основу курса​

VBA:

• Макросы и программирование на VBA – достигаем понимания
• Настройки безопасности, разрешение на выполнение макросов
• Макрорекордер - незаменимый помощник, запись макросов макрорекордером
• Выполнение макросов: с ленты, горячие клавиши. Удаление
• Среда разработки VBA, структура окон
• Процедуры: структура, зарезервированные слова
• Что делаем с макросом дальше: оптимизация кода VBA
• Объекты: Что это? Свойства и методы объектов
• Работа с диапазоном ячеек и ячейкой, объект Range: свойста и методы

Создаем на занятии:

• Макрос-1. Программное форматирование ячеек , смещение на к-во строк / столбцов
• Макрос-2. Поиск последней заполненной строки / столбца. Вывод сообщения на экран
• Макрос-3. Методы очистки, копирования и вставки данных диапазона

Дополнительно:

• Пример в видео: Конструкция With … End With
• Пример в видео: Вызов выполнения макроса из другого макроса
• Видео (15 мин.): Совместимость с МАС

Задания (самостоятельно):

• Макрос-1: форматирование таблицы
• Макрос-2: нахождение последней ячейки таблицы
• Оптимизация кодов двух макросов

​

Модуль 2. ПЕРВЫЙ КОД: СОЗДАНИЕ ПРОГРАММЫ (МАКРОСА) ЧЕРЕЗ ОБОЛОЧКУ VBA

Автоматизация "Smart Education Ltd":

Ввод данных об учащихся (клиентах)​

VBA:

• Создание макроса с нуля в среде разработки VBA
• Правила синтаксиса VBA
• Работа с модулями: вставка, экспорт, импорт макросов, удаление
• Назначение книги макросов
• Переменные: оглашение, типы данных
• Операторы присвоения
• Объекты: Range (диапазоны ячеек), свойства и методы (продолжение)

Создаем на занятии:

• Код-1: +2 способа поиска последней заполненной строки диапазона
• Код-2: Поиск текста в диапазоне, работа с диалоговым окном ввода данных. Вывод информации: пользователь системы и Excel, дата и время ввода. Привязка макроса к кнопке

Задания (самостоятельно):

1. Разработка кода: поиск по выбранной дате, указанному диапазону, изменение найденных данных, отслеживание изменений
2. Тестирование кода, привязка к кнопке

​

Модуль 3. ДОБАВЛЯЕМ "ВЕТВЛЕНИЯ" В КОДЕ: ОПЕРАТОРЫ ПРОВЕРКИ УСЛОВИЙ. ОБЕЪЕКТ Sheet (ЛИСТ)

Автоматизация "Smart Education Ltd":

Оптимизируем и совершенствуем ввод данных об учащихся (клиентах)​

VBA:

• Алгоритмизация: что такое алгоритм, логика и блоки алгоритма
• Операторы проверки условий: If...Then... Else, вложенные конструкции If
• Оператор Select Case
• Объекты: Sheet (лист), свойства и методы
• События листа

Создаем на занятии:

• Код-1. Многоуровневые проверки – по дате рождения определяем месяц и знак зодиака
• Код-2. Рабочие листы Excel: добавление, переименование, копирование, подсчет количества в книге.
• Код-3. Мультивыбор в выпадающем списке на листе

Дополнительно:

Видео-занятие (21 мин.): Использование функций текстовых строк Len, InStr​

Задания (самостоятельно):

1. Разработка кода: "умное" добавление новых листов, взаимодействие с пользователем через диалоговое окно. Генерация статуса по рабочим листам книги Excel
2. Совершенствование кода: различные сценарии поведения в зависимости от условий

​

Модуль 4. СОЗДАЕМ ПОВТОРЕНИЯ БЛОКОВ КОДА: ОПЕРАТОРЫ ЦИКЛОВ. ЧАСТЬ-1. ОБЪЕКТ Workbook (РАБОЧАЯ КНИГА)

Автоматизация "Smart Education Ltd":

Ввод данных пользователями (продолжение). Автоматизируем консолидацию и моделирование данных, подготовку для генерации отчетов​

VBA:

• Операторы цикла: конструкция For...Next, конструкция For Each...Next
• Объекты: Workbook (книга), свойства и методы. Работа с книгами: открытие, закрытие, сохранение

Создаем на занятии:

• Код-1. Организация цикла по столбцам для внесения данных в таблицу
• Код-2. Проверка существования листа в книге и вывод всех имен листов в список
• Код-3. Поиск искомого в определенном пользователем диапазоне, вывод результата поиска для каждой ячейки диапазона
• Код-4. Открытие нескольких определенных книг из папки и копирования определенных листов в новую книгу. Закрытие, сохранение

Дополнительно:

Видео-занятие (30 мин.): События книги​

Задания (самостоятельно):

Разработка кода: Преобразование рабочих листов файла в отдельные файлы и их сохранение по заданному пути в файловой системе​

Модуль 5. СОЗДАЕМ ПОВТОРЕНИЯ БЛОКОВ КОДА: ОПЕРАТОРЫ ЦИКЛОВ. ЧАСТЬ-2. ОБЪЕКТ ListObject ("УМНАЯ ТАБЛИЦА")

Автоматизация "Smart Education Ltd":

Организуем обновление и добавление информации по ученикам для отметок посещаемости в кабинете Администратора​

VBA:

• Конструкции Do While и Do Until
• Объекты: ListObject ("Умная таблица"), свойства и методы

Создаем на занятии:

• Коды 1-4. Организация циклов с «пред» и «после» условием для вывода данных в ячейки
• Код-5. Создание «умной» таблицы, добавление / удаление строк / столбцов, выделение частей таблицы, копирование и очищение таблиц
• Код-6. Генерация и заполнении строк данными из других таблиц с комплексными условиями в циклах

Задания (самостоятельно):

Разработка кода: Реорганизация и консолидация данных о клиентах и посещениях в кабинете Администратора "Smart Education Ltd"​

Модуль 6. ПРАКТИКУМ. РЕЗЮМИРУЕМ РАБОТУ С ОБЪЕКТАМИ EXCEL, ОПЕРАТОРАМИ УСЛОВИЙ И ЦИКЛОВ

Автоматизация "Smart Education Ltd":

Программируем консолидацию данных компании и подготовку к генерации отчетов​

VBA:

• Работа со встроенными файловыми функциями
• Application.GetOpenFilename – вызов стандартного окна выбора файлов, настройка параметров
• Файловые операторы директорий ChDrive, ChDir
• Использование функций определения границ массивов LBound, UBound для перебора файлов в цикле
• Строковая функция InStrRev

Создаем на занятии:

Код-1. Консолидация однотипных файлов, определенных пользователем через диалоговое окно​

Модуль 7. ПРОГРАММИРУЕМ ПОЛЬЗОВАТЕЛЬСКИЙ ИНТЕРФЕЙС: ФОРМЫ И ЭЛЕМЕНТЫ УПРАВЛЕНИЯ

Автоматизация "Smart Education Ltd":

Разрабатываем и программируем формы ввода данных компании о доходах​

VBA:

• Формы: создание, основные свойства, команды и методы
• Наполнение форм – основные элементы управления: метки, текстовые поля, выпадающие списки, кнопки
• Привязка объектов формы к объектам книги

Создаем на занятии:

• Код-1.Обработка события нажатия на кнопку формы: добавление строк в таблицу с данными с элементов управления формы
• Код-2. Вызов формы при активации листа
• Код-3. Очистка содержимого элементов формы
• Код 4-5. Обработка события элемента управления: проверка данных в момент ввода

Задания (самостоятельно):

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

Модуль 8. ПРОГРАММИРУЕМ ПОЛЬЗОВАТЕЛЬСКИЙ ИНТЕРФЕЙС: МНОГОВКЛАДОЧНЫЕ ФОРМЫ

Автоматизация "Smart Education Ltd":

Разрабатываем и программируем формы ввода данных о расходах​

VBA:

• Глобальные переменные: принципы использования и оглашения
• Элемент управления MultiPage - инструмент для создания вкладок на форме. Свойства и настройки
• Связанные выпадающие списки ComboBox на формах
• Основные события формы, порядок их возникновения
• Понятие модальности: модальные и не модальные формы

Создаем на занятии:

• Код-1-9. Доработка формы вкладками. Разработка интерфейса формы для ввода информации по расходам компании, заполнение элементами связанных выпадающих списков.

Задания (самостоятельно):

1. Разработка кода: Добавление вкладок на форму для ввода информации о записях учеников на посещение занятий.

​

Модуль 9. ПРАКТИКУМ. РЕЗЮМИРУЕМ РАБОТУ С ФОРМАМИ И ЭЛЕМЕНТАМИ УПРАВЛЕНИЯ

Автоматизация "Smart Education Ltd":

Разрабатываем аутентификацию пользователей кабинетов администратора и собственников​

VBA:

• Детальное изучение Controls: TextBox - поле для ввода пароля; RefEdit – элемент для выделения и передачи в код диапазона ячеек. Полезные свойства и конструкции
• Работа с событиями форм: обработка события закрытия формы

Создаем на занятии:

• Код 1-6. Защита файла: Разработка формы для входа пользователя под логином и паролем. Проверки соответствия логина и пароля, скрытие и отображение определенных листов, вызов формы при открытии файла

​

Модуль 10. ФОРМУЛЫ В КОДЕ VBA?: ИСПОЛЬЗУЕМ ПРИВЫЧНЫЕ ФОРМУЛЫ И ФУНКЦИИ РАБОЧИХ ЛИСТОВ EXCEL

Автоматизация "Smart Education Ltd":

Переходим к программированию отчетности, генерируемой по одному клику на кнопке​

VBA:

• Создание кнопок на ленте для запуска макросов, создание собственной надстройки с макросами для быстрого доступа с любого файла
• Различные варианты использования функций в коде на VBA: формулы рабочих листов, встроенные функции VBA
• Основные понятия про ссылки с нотацией R1C1

Создаем на занятии:

• Код-1. Моделирование отчетности ("подтягивание" данных из между двумя таблицами)
• Код-2. Замена формул значениями в выделенном пользователем или определенном диапазоне
• Код-3. Автоматическое заполнение отчетов о поступлении денежных средств в различных разрезах

Задания (самостоятельно):

• Разработка кода: Формирование динамического, автоматически заполняемого отчета в разрезе статей дохода

​

Модуль 11. НЕТ ВСТРОЕННОЙ ФУНКЦИИ EXCEL ДЛЯ РЕШЕНИЯ ВАШЕЙ ЗАДАЧИ?: РАЗРАБАТЫВАЕМ СОБСТВЕННЫЕ ФУНКЦИИ

Автоматизация "Smart Education Ltd":

Программируем генерацию отчетов по доходам и расходам компании​

VBA:

• Функции: синтаксис, область видимости
• Программно добавляем параметры описания функций методом Application.MacroOptions
• Расширяем возможность использования авторских функций: доступ к функции с любого файла Excel

Создаем на занятии:

• Код-1. Функция для извлечения чисел с текста
• Код-2. Функция для определения номера квартала на основании даты
• Код-3. Функция для расчета % маржи и наценки

Задания (самостоятельно):

1. Разработка кода: функция, выдающая полное название дня недели на основании даты

​

Модуль 12. АВТОМАТИЗИРУЕМ БИЗНЕС-АНАЛИТИКУ: ПРОГРАММИРУЕМ ПОВЕДЕНИЕ СВОДНЫХ ТАБЛИЦ

Автоматизация "Smart Education Ltd":

Программируем генерацию интерактивного отчета об активности посещения занятий, автоматическое создание инвойсов​

VBA:

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

Создаем на занятии:

• Код-1-6. Формирование отчета по статусу оплат на основании консолидированных данных о доходах и посещениях занятий. Подготовка в формированию счетов на оплату
• Код-7. Отчет по активности посещений занятий учениками

Задания (самостоятельно):

1. Разработка кода: отчеты сводных таблицы о доходах и расходах компании, формирование общего баланса

​

Модуль 13. ВИЗУАЛИЗАЦИЯ В БИЗНЕС-АНАЛИТИКЕ: ПРОГРАММНАЯ ГЕНЕРАЦИЯ ИНТЕРАКТИВНОГО DASHBOARD

Автоматизация "Smart Education Ltd":

Создаем генерацию интерактивной информационной панели (dashboard) с визуальными объектами​

VBA:

• Программная работа с даиграмами. Работа со свойствами и методами диаграмм
• Программная работа с элементами диаграммы
• Программная работа со срезами и временными шкалами

Создаем на занятии:

• Код-1. Генерация интерактивной информационной панели (dashboard)

Задания (самостоятельно):

• Разработка кода: визуализация финансовых результатов компании

​

Модуль 14. ИЗВЛЕКАЕМ ПОЛЬЗУ ИЗ ОШИБОК: ДОБАВЛЯЕМ ОБРАБОТКУ ОШИБОК В КОД VBA

Автоматизация "Smart Education Ltd":

Добавляем обработку нестандартных действий пользователей в программный код​

VBA:

• Конструкции On Error GoTo …
• Конструкции On Error Resume Next, On Error GoTo 0
• Работа с объектом Err

Создаем на занятии:

• Код 1 -6. Добавление обработок ошибок в ранее созданные коды

Задания (самостоятельно):

1. Добавление обработок ошибок в коды предыдущих заданий

​

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

Автоматизация "Smart Education Ltd":

Оптимизируем генерацию интерактивной информационной панели (dashboard) с визуальными объектами​

Создаем на занятии:

• Код 1-2. Еще полезные пользовательские функции
• Код-3. Оптимизация кода интерактивной информационной панели (dashboard)

​

Общая стоимость курса: 69 (USD), онлайн свободный график пакет 1 месяц

Продажник