Курс Excel VBA (Макроси): автоматизація бізнес-задач

Вивчати предмет, виконуючи проект на реальних даних - один з найбільш ефективних підходів в освіті. Проходячи курс, ви автоматизуєте компанію "Smart Education Ltd". Курс включає 3 модуля-практикума. Це не тільки можливість ще раз резюмувати пройдені частини курсу, а й нові особливості вже знайомих інструментів.

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

З чого потрібно почати: запис макросу і редагування коду. Об'єкт Range (Діапазон осередків)

VBA:

• Макроси і програмування на VBA - досягаємо розуміння
• Налаштування безпеки, дозвіл на виконання макросів
• Макрорекордер - незамінний помічник, запис макросів макрорекордер
• Виконання макросів: з стрічки, гарячі клавіші. Видалення
• Середовище розробки VBA, структура вікон
• Процедури: структура, зарезервовані слова
• Що робимо з макросом далі: оптимізація коду VBA
• Об'єкти: Що це? Властивості і методи об'єктів
• Робота з діапазоном осередків і осередком, об'єкт Range: властивості і методи

Створюємо на занятті:

• Макрос-1. Програмне форматування осередків, зміщення на к-ть рядків/стовпців
• Макрос-2. Пошук останньої заповненого рядка/стовпця. Висновок повідомлення на екран
• Макрос-3. Методи очищення, копіювання і вставки даних діапазону

Додатково:

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

Завдання (самостійно):

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

Перший код: Створення програми (Макрома) через оболонку VBA

VBA:

• Створення макросу з нуля в середовищі розробки VBA
• Правила синтаксису VBA
• Робота з модулями: вставка, експорт, імпорт макросів, видалення
• Призначення книги макросів
• Змінні: оголошення, типи даних
• Оператори присвоєння
• Об'єкти: Range (діапазони осередків), властивості і методи (продовження)

Створюємо на занятті:

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

Завдання (самостійно):

• Розробка коду: пошук за обраною датою, вказаному діапазону, зміна знайдених даних, відстеження змін
• Тестування коду, прив'язка до кнопки

Додаємо "Галуження" в коді: оператори перевірки умов. Об'єкт Sheet (лист)

VBA:

• Алгоритмізація: що таке алгоритм, логіка і блоки алгоритму
• Оператори перевірки умов: If ... Then ... Else, вкладені конструкції If
• Оператор Select Case
• Об'єкти: Sheet (лист), властивості і методи
• Події листа

Створюємо на занятті:

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

Додатково:

Відео-заняття (21 хв.): Використання функцій текстових рядків Len, InStr.

Завдання (самостійно):

• Розробка коду: "розумне" додавання нових листів, взаємодія з користувачем через діалогове вікно. Генерація статусу по робочим листам книги Excel
• Удосконалення коду: різні сценарії поведінки в залежності від умов

Створюємо повторення блоків коду: Оператори циклів. Частина 1. Об'єкт Workbook (Робоча книга)

VBA:

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

Створюємо на занятті:

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

Додатково:

Відео-заняття (30 хв.): Події книги.

Завдання (самостійно):

Розробка коду: Перетворення робочих листів файлу в окремі файли і їх збереження по заданому шляху в файлової системі.

Створюємо повторення коду: оператори циклів. Частина 2. Об'єкт ListObject ("Розумна таблиця")

VBA:

• Конструкції Do While і Do Until
• Об'єкти: ListObject ("Розумна таблиця"), властивості і методи

Створюємо на занятті:

• Коди 1-4. Організація циклів з "перед" і "після" умовою для виведення даних в комірці
• Код-5. Створення "розумної" таблиці, додавання/видалення рядків/стовпців, виділення елементів таблиці, копіювання та очищення таблиць
• Код-6. Генерація та заповнення рядків даними з інших таблиць з комплексними умовами в циклах

Завдання (самостійно):

Розробка коду: Реорганізація і консолідація даних про клієнтів і відвідини в кабінеті Адміністратора "Smart Education Ltd".

Практикум. Резюмуючи роботу з об'єктами Excel, операторами умов і режимів

VBA:

• Робота з вбудованими файловими функціями
• Application.GetOpenFilename - виклик стандартного вікна вибору файлів, наалаштування параметрів
• Файлові оператори директорій ChDrive, ChDir
• Використання функцій визначення меж масивів LBound, UBound для перебору файлів в циклі
• Строкова функція InStrRev

Створюємо на занятті:

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

Програмуємо користувацький інтерфейс: форми і елементи управління

VBA:

• Форми: створення, основні властивості, команди і методи
• Наповнення форм - основні елементи управління: мітки, текстові поля, списки що випадають, кнопки
• Прив'язка об'єктів форми до об'єктів книги

Створюємо на занятті:

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

Завдання (самостійно):

Розробка коду: Програмування форми введення інформації про учнів в кабінеті адміністратора. Прив'язка відображення форми до події. Очищення полів форми.

Програмування користувацького інтерфейсу: багатовкладкові форми

VBA:

• Глобальні змінні: принципи використання і оголошення
• Елемент управління MultiPage - інструмент для створення вкладок на формі. Властивості і налаштування
• Пов'язані випадають списки ComboBox на формах
• Основні події форми, порядок їх виникнення
• Поняття модальності: модальні і не модальні форми

Створюємо на занятті:

Код-1-9. Доопрацювання форми вкладками. Розробка інтерфейсу форми для введення інформації за видатками компанії, заповнення елементами пов'язаних випадаючих списків.

Завдання (самостійно):

Розробка коду: Додавання вкладок на форму для введення інформації про записи учнів на відвідування занять.

Практикум. Резюмуючи роботу з формами і елементами управління

VBA:

• Детальне вивчення Controls: TextBox - поле для введення паролю; RefEdit - елемент для виділення і передачі в код діапазону комірок. Корисні властивості і конструкції
• Робота з подіями форм: обробка події закриття форми

Створюємо на занятті:

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

Формули в коді VBA?: Використовуємо звичні формули і функції робочих листів Excel

VBA:

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

Створюємо на занятті:

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

Завдання (самостійно):

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

Немає вбудованої функції Excel для вирішення вашого завдання?: Розробляємо власні функції

VBA:

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

Створюємо на занятті:

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

Завдання (самостійно):

Розробка коду: функція, що видає повну назву дня тижня на підставі дати.

Автоматизуємо бізнес-аналітику: програмуємо поведінку зведених таблиць

VBA:

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

Створюємо на занятті:

• Код-1-6. Формування звіту по статусу оплат на підставі консолідованих даних про доходи і відвідини занять. Підготовка до формування рахунків на оплату
• Код-7. Звіт по активності відвідувань занять учнями

Завдання (самостійно):

Розробка коду: звіти зведених таблиць про доходи та витрати компанії, формування загального балансу.

Візуалізація у бізнес-аналітиці: програмна генерація інтерактивного Dashboard

VBA:

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

Створюємо на занятті:

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

Завдання (самостійно):

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

Витягуємо користь з помилок: додаємо обробку помилок у код VBA

VBA:

• Конструкції On Error GoTo
• Конструкції On Error Resume Next, On Error GoTo 0
• Робота з об'єктом Err

Створюємо на занятті:

Код 1 -6. Додавання обробок помилок в раніше створені коди.

Завдання (самостійно):

Додавання обробок помилок в коди попередніх завдань.

Практикум. Резюмуючи роботу з функціями і зведеними таблицями

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

Оптимізуємо генерацію інтерактивної інформаційної панелі (dashboard) з візуальними об'єктами.

Створюємо на занятті:

• Код 1-2. Ще корисні для користувача функції
• Код-3. Оптимізація коду інтерактивної інформаційної панелі (dashboard)

Попередні вимоги

• Для проходження курсу не потрібні знання програмування в цілому і мови VBA зокрема. У цій частині - курс з нуля
• Однак для комфортного проходження курсу необхідно бути впевненим користувачем Excel
• Знати і розуміти об'єкти Excel: комірки (cells) та діапазони комірок (ranges), робочі листи (worksheets) і робочі книги (workbooks)
• Створювати і добре розуміти формули Excel, адресацію комірок, вміти конструювати складні формули
• Вміти працювати з інструментами "перевірка даних" (data validation) і "умовне форматування" (conditional formatting)
• Добре знати і вміти працювати з об'єктами Excel: tables ("розумні таблиці"), зрізи і тимчасові шкали (slicers and time lines), діаграми (charts)
• Розуміти концепцію і вміти працювати зі зведеними таблицями (pivot table) і зведеними діаграмами (pivot charts)

Особливості курсу

• В основі курсу - реальний проєкт
• Глибоко структуровано
• Ретельне опрацювання
• Система практикумів
• Доступ до відеозаписів
• Індивідуальна підтримка

Викладачі курсу

Анастасія Пасочник - Тренер і автор курсів VBA (макроси)