EXCEL VBA (МАКРОСЫ): АВТОМАТИЗАЦИЯ БИЗНЕС-ЗАДАЧ
Курс
Наиболее частыми слушателями курса являются аналитики, финансисты, сотрудники отделов продаж и логистики. Курс также часто проходят собственники малого и среднего бизнеса – все, кто в своих повседневных задачах сталкивается с необходимостью автоматизации рутинных задач и бизнеса в целом. Знание программирования и даже основ программирования не требуется. Этот курс с нуля. Однако вам необходимо быть достаточно уверенным пользователем Excel, так как курс – об автоматизации в Excel. В курсе мы активно используем объектную модель Excel.
Программа курса
Автоматизация "Smart Education Ltd":
Знакомство с проектом, положенным в основу курса
VBA:
Макросы и программирование на VBA – достигаем понимания
Настройки безопасности, разрешение на выполнение макросов Макрорекордер - незаменимый помощник, запись макросов макрорекордером Выполнение макросов: с ленты, горячие клавиши. Удаление
Среда разработки VBA, структура окон
Процедуры: структура, зарезервированные слова
Что делаем с макросом дальше: оптимизация кода VBA
Объекты: что это? Свойства и методы объектов
Работа с диапазоном ячеек и ячейкой, объект Range: свойства и методы
Создаем на занятии:
Макрос-1. Программное форматирование ячеек, смещение на к-во строк / столбцов Макрос-2. Поиск последней заполненной строки / столбца. Вывод сообщения на экран
Макрос-3. Методы очистки, копирования и вставки данных диапазона
Дополнительно:
Пример в видео: Конструкция With … End With
Пример в видео: Вызов выполнения макроса из другого макроса Видео (15 мин.): Совместимость с МАС
Задания (самостоятельно):
1. Макрос-1: форматирование таблицы
2. Макрос-2: нахождение последней ячейки таблицы
3. Оптимизация кодов двух макросов
Знакомство с проектом, положенным в основу курса
VBA:
Макросы и программирование на VBA – достигаем понимания
Настройки безопасности, разрешение на выполнение макросов Макрорекордер - незаменимый помощник, запись макросов макрорекордером Выполнение макросов: с ленты, горячие клавиши. Удаление
Среда разработки VBA, структура окон
Процедуры: структура, зарезервированные слова
Что делаем с макросом дальше: оптимизация кода VBA
Объекты: что это? Свойства и методы объектов
Работа с диапазоном ячеек и ячейкой, объект Range: свойства и методы
Создаем на занятии:
Макрос-1. Программное форматирование ячеек, смещение на к-во строк / столбцов Макрос-2. Поиск последней заполненной строки / столбца. Вывод сообщения на экран
Макрос-3. Методы очистки, копирования и вставки данных диапазона
Дополнительно:
Пример в видео: Конструкция With … End With
Пример в видео: Вызов выполнения макроса из другого макроса Видео (15 мин.): Совместимость с МАС
Задания (самостоятельно):
1. Макрос-1: форматирование таблицы
2. Макрос-2: нахождение последней ячейки таблицы
3. Оптимизация кодов двух макросов
Автоматизация "Smart Education Ltd":
Ввод данных об учащихся (клиентах)
VBA:
Создание макроса с нуля в среде разработки VBA
Правила синтаксиса VBA
Работа с модулями: вставка, экспорт, импорт макросов, удаление
Назначение книги макросов
Переменные: оглашение, типы данных
Операторы присвоения
Объекты: Range (диапазоны ячеек), свойства и методы (продолжение)
Создаем на занятии:
Код-1: +2 способа поиска последней заполненной строки диапазона
Код-2: Поиск текста в диапазоне, работа с диалоговым окном ввода данных.
Вывод информации: пользователь системы и Excel, дата и время ввода. Привязка макроса к кнопке
Задания (самостоятельно):
1. Разработка кода: поиск по выбранной дате, указанному диапазону, изменение найденных данных, отслеживание изменений
2. Тестирование кода, привязка к кнопке
Ввод данных об учащихся (клиентах)
VBA:
Создание макроса с нуля в среде разработки VBA
Правила синтаксиса VBA
Работа с модулями: вставка, экспорт, импорт макросов, удаление
Назначение книги макросов
Переменные: оглашение, типы данных
Операторы присвоения
Объекты: Range (диапазоны ячеек), свойства и методы (продолжение)
Создаем на занятии:
Код-1: +2 способа поиска последней заполненной строки диапазона
Код-2: Поиск текста в диапазоне, работа с диалоговым окном ввода данных.
Вывод информации: пользователь системы и Excel, дата и время ввода. Привязка макроса к кнопке
Задания (самостоятельно):
1. Разработка кода: поиск по выбранной дате, указанному диапазону, изменение найденных данных, отслеживание изменений
2. Тестирование кода, привязка к кнопке
Автоматизация "Smart Education Ltd":
Оптимизируем и совершенствуем ввод данных об учащихся (клиентах)
VBA:
Алгоритмизация: что такое алгоритм, логика и блоки алгоритма Операторы проверки условий: If...Then... Else, вложенные конструкции If Оператор Select Case
Объекты: Sheet (лист), свойства и методы
События листа
Создаем на занятии:
Код-1. Многоуровневые проверки – по дате рождения определяем месяц и знак зодиака
Код-2. Рабочие листы Excel: добавление, переименование, копирование, подсчет количества в книге.
Код-3. Мультивыбор в выпадающем списке на листе
Дополнительно:
Видеозанятие (21 мин.): Использование функций текстовых строк Len, InStr
Задания (самостоятельно):
1. Разработка кода: "умное" добавление новых листов, взаимодействие с пользователем через диалоговое окно. Генерация статуса по рабочим листам книги Excel
2. Совершенствование кода: различные сценарии поведения в зависимости от условий
Оптимизируем и совершенствуем ввод данных об учащихся (клиентах)
VBA:
Алгоритмизация: что такое алгоритм, логика и блоки алгоритма Операторы проверки условий: If...Then... Else, вложенные конструкции If Оператор Select Case
Объекты: Sheet (лист), свойства и методы
События листа
Создаем на занятии:
Код-1. Многоуровневые проверки – по дате рождения определяем месяц и знак зодиака
Код-2. Рабочие листы Excel: добавление, переименование, копирование, подсчет количества в книге.
Код-3. Мультивыбор в выпадающем списке на листе
Дополнительно:
Видеозанятие (21 мин.): Использование функций текстовых строк Len, InStr
Задания (самостоятельно):
1. Разработка кода: "умное" добавление новых листов, взаимодействие с пользователем через диалоговое окно. Генерация статуса по рабочим листам книги Excel
2. Совершенствование кода: различные сценарии поведения в зависимости от условий
Автоматизация "Smart Education Ltd":
Ввод данных пользователями (продолжение). Автоматизируем консолидацию и моделирование данных, подготовку для генерации отчетов
VBA:
Операторы цикла: конструкция For...Next, конструкция For Each...Next
Объекты: Workbook (книга), свойства и методы. Работа с книгами: открытие, закрытие, сохранение
Создаем на занятии:
Код-1. Организация цикла по столбцам для внесения данных в таблицу
Код-2. Проверка существования листа в книге и вывод всех имен листов в список
Код-3. Поиск искомого в определенном пользователем диапазоне,
вывод результата поиска для каждой ячейки диапазона
Код-4. Открытие нескольких определенных книг из папки и копирования определённых листов в новую книгу.
Закрытие, сохранение
Дополнительно:
Видео-занятие ( мин.): События книги
Задания (самостоятельно):
Разработка кода: Преобразование рабочих листов файла в отдельные файлы и их сохранение по заданному пути в файловой системе
Ввод данных пользователями (продолжение). Автоматизируем консолидацию и моделирование данных, подготовку для генерации отчетов
VBA:
Операторы цикла: конструкция For...Next, конструкция For Each...Next
Объекты: Workbook (книга), свойства и методы. Работа с книгами: открытие, закрытие, сохранение
Создаем на занятии:
Код-1. Организация цикла по столбцам для внесения данных в таблицу
Код-2. Проверка существования листа в книге и вывод всех имен листов в список
Код-3. Поиск искомого в определенном пользователем диапазоне,
вывод результата поиска для каждой ячейки диапазона
Код-4. Открытие нескольких определенных книг из папки и копирования определённых листов в новую книгу.
Закрытие, сохранение
Дополнительно:
Видео-занятие ( мин.): События книги
Задания (самостоятельно):
Разработка кода: Преобразование рабочих листов файла в отдельные файлы и их сохранение по заданному пути в файловой системе
Автоматизация "Smart Education Ltd":
Организуем обновление и добавление информации по ученикам для отметок посещаемости в кабинете Администратора
VBA:
Конструкции Do While и Do Until
Объекты: ListObject ("Умная таблица"), свойства и методы
Создаем на занятии:
Коды 1-4. Организация циклов с «пред» и «после» условием для вывода данных в ячейки Код-5. Создание «умной» таблицы, добавление / удаление строк / столбцов, выделение частей таблицы, копирование и очищение таблиц
Код-6. Генерация и заполнении строк данными из других таблиц с комплексными условиями в циклах
Задания (самостоятельно):
Разработка кода: Реорганизация и консолидация данных о клиентах и посещениях в кабинете Администратора "Smart Education Ltd"
Организуем обновление и добавление информации по ученикам для отметок посещаемости в кабинете Администратора
VBA:
Конструкции Do While и Do Until
Объекты: ListObject ("Умная таблица"), свойства и методы
Создаем на занятии:
Коды 1-4. Организация циклов с «пред» и «после» условием для вывода данных в ячейки Код-5. Создание «умной» таблицы, добавление / удаление строк / столбцов, выделение частей таблицы, копирование и очищение таблиц
Код-6. Генерация и заполнении строк данными из других таблиц с комплексными условиями в циклах
Задания (самостоятельно):
Разработка кода: Реорганизация и консолидация данных о клиентах и посещениях в кабинете Администратора "Smart Education Ltd"
Автоматизация "Smart Education Ltd":
Программируем консолидацию данных компании и подготовку к генерации отчетов
Работа со встроенными файловыми функциями
Application.GetOpenFilename – вызов стандартного окна выбора файлов, настройка параметров
Файловые операторы директорий ChDrive, ChDir
Использование функций определения границ массивов LBound, UBound для перебора файлов в цикле
Строковая функция InStrRev
Создаем на занятии:
Код-1. Консолидация однотипных файлов, определенных пользователем через диалоговое окно
Программируем консолидацию данных компании и подготовку к генерации отчетов
Работа со встроенными файловыми функциями
Application.GetOpenFilename – вызов стандартного окна выбора файлов, настройка параметров
Файловые операторы директорий ChDrive, ChDir
Использование функций определения границ массивов LBound, UBound для перебора файлов в цикле
Строковая функция InStrRev
Создаем на занятии:
Код-1. Консолидация однотипных файлов, определенных пользователем через диалоговое окно
Автоматизация "Smart Education Ltd":
Разрабатываем и программируем формы ввода данных компании о доходах
VBA:
Формы: создание, основные свойства, команды и методы.
Наполнение форм – основные элементы управления: метки, текстовые поля, выпадающие списки, кнопки
Привязка объектов формы к объектам книги
Создаем на занятии:
Код-1. Обработка события нажатия на кнопку формы: добавление строк в таблицу с данными с элементов управления формы
Код-2. Вызов формы при активации листа
Код-3. Очистка содержимого элементов формы
Код 4-5. Обработка события элемента управления: проверка данных в момент ввода
Задания (самостоятельно):
Разработка кода: Программирование формы ввода информации об учащихся в кабинете администратора. Привязка отображения формы к событию. Очистка полей формы
Разрабатываем и программируем формы ввода данных компании о доходах
VBA:
Формы: создание, основные свойства, команды и методы.
Наполнение форм – основные элементы управления: метки, текстовые поля, выпадающие списки, кнопки
Привязка объектов формы к объектам книги
Создаем на занятии:
Код-1. Обработка события нажатия на кнопку формы: добавление строк в таблицу с данными с элементов управления формы
Код-2. Вызов формы при активации листа
Код-3. Очистка содержимого элементов формы
Код 4-5. Обработка события элемента управления: проверка данных в момент ввода
Задания (самостоятельно):
Разработка кода: Программирование формы ввода информации об учащихся в кабинете администратора. Привязка отображения формы к событию. Очистка полей формы
Автоматизация "Smart Education Ltd":
Разрабатываем и программируем формы ввода данных о расходах
VBA:
Глобальные переменные: принципы использования и оглашения.
Элемент управления MultiPage - инструмент для создания вкладок на форме. Свойства и настройки.
Связанные выпадающие списки ComboBox на формах.
Основные события формы, порядок их возникновения.
Понятие модальности: модальные и не модальные формы.
Создаем на занятии:
Код-1-9. Доработка формы вкладками. Разработка интерфейса формы для ввода информации по расходам компании, заполнение элементами связанных выпадающих списков.
Задания (самостоятельно):
Разработка кода: Добавление вкладок на форму для ввода информации о записях учеников на посещение занятий.
Разрабатываем и программируем формы ввода данных о расходах
VBA:
Глобальные переменные: принципы использования и оглашения.
Элемент управления MultiPage - инструмент для создания вкладок на форме. Свойства и настройки.
Связанные выпадающие списки ComboBox на формах.
Основные события формы, порядок их возникновения.
Понятие модальности: модальные и не модальные формы.
Создаем на занятии:
Код-1-9. Доработка формы вкладками. Разработка интерфейса формы для ввода информации по расходам компании, заполнение элементами связанных выпадающих списков.
Задания (самостоятельно):
Разработка кода: Добавление вкладок на форму для ввода информации о записях учеников на посещение занятий.
Автоматизация "Smart Education Ltd":
Разрабатываем аутентификацию пользователей кабинетов администратора и собственников
VBA:
Детальное изучение Controls: TextBox - поле для ввода пароля;
RefEdit – элемент для выделения и передачи в код диапазона ячеек. Полезные свойства и конструкции.
Работа с событиями форм: обработка события закрытия формы.
Создаем на занятии:
Код 1-6. Защита файла: Разработка формы для входа пользователя под логином и паролем. Проверки соответствия логина и пароля, скрытие и отображение определенных листов, вызов формы при открытии файла.
Разрабатываем аутентификацию пользователей кабинетов администратора и собственников
VBA:
Детальное изучение Controls: TextBox - поле для ввода пароля;
RefEdit – элемент для выделения и передачи в код диапазона ячеек. Полезные свойства и конструкции.
Работа с событиями форм: обработка события закрытия формы.
Создаем на занятии:
Код 1-6. Защита файла: Разработка формы для входа пользователя под логином и паролем. Проверки соответствия логина и пароля, скрытие и отображение определенных листов, вызов формы при открытии файла.
Автоматизация "Smart Education Ltd":
Переходим к программированию отчетности, генерируемой по одному клику на кнопке
VBA:
Создание кнопок на ленте для запуска макросов, создание собственной надстройки с макросами для быстрого доступа с любого файла.
Различные варианты использования функций в коде на VBA: формулы рабочих листов, встроенные функции VBA.
Основные понятия про ссылки с нотацией R1C1
Создаем на занятии:
Код-1. Моделирование отчетности ("подтягивание" данных из между двумя таблицами)
Код-2. Замена формул значениями в выделенном пользователем или определенном диапазоне.
Код-3. Автоматическое заполнение отчетов о поступлении денежных средств в различных разрезах.
Задания (самостоятельно):
Разработка кода: Формирование динамического, автоматически заполняемого отчета в разрезе статей дохода
Переходим к программированию отчетности, генерируемой по одному клику на кнопке
VBA:
Создание кнопок на ленте для запуска макросов, создание собственной надстройки с макросами для быстрого доступа с любого файла.
Различные варианты использования функций в коде на VBA: формулы рабочих листов, встроенные функции VBA.
Основные понятия про ссылки с нотацией R1C1
Создаем на занятии:
Код-1. Моделирование отчетности ("подтягивание" данных из между двумя таблицами)
Код-2. Замена формул значениями в выделенном пользователем или определенном диапазоне.
Код-3. Автоматическое заполнение отчетов о поступлении денежных средств в различных разрезах.
Задания (самостоятельно):
Разработка кода: Формирование динамического, автоматически заполняемого отчета в разрезе статей дохода
VBA:
Функции: синтаксис, область видимости
Программно добавляем параметры описания функций методом Application. MacroOptions Расширяем возможность использования авторских функций: доступ к функции с любого файла Excel
Создаем на занятии:
Код-1. Функция для извлечения чисел с текста
Код-2. Функция для определения номера квартала на основании даты
Код-3. Функция для расчета %маржи и наценки
Задания (самостоятельно):
Разработка кода: функция, выдающая полное название дня недели на основании даты
Функции: синтаксис, область видимости
Программно добавляем параметры описания функций методом Application. MacroOptions Расширяем возможность использования авторских функций: доступ к функции с любого файла Excel
Создаем на занятии:
Код-1. Функция для извлечения чисел с текста
Код-2. Функция для определения номера квартала на основании даты
Код-3. Функция для расчета %маржи и наценки
Задания (самостоятельно):
Разработка кода: функция, выдающая полное название дня недели на основании даты
Автоматизация "Smart Education Ltd":
Программируем генерацию интерактивного отчета об активности посещения занятий
VBA:
Программная работа со сводными таблицами. Основные свойства и методы
Программное создание, наполнение данными и удаление отчета сводной таблицы Программная работа с полями сводной таблицы (формат, фильтрация, вычисления) и макетом отчета, итогами
Добавление вычисляемого поля и вычисляемого элемента в сводных таблицах
Обращение к составляющим частям сводной таблицы (областям)
Создаем на занятии:
Код-1-6. Формирование отчета по статусу оплат на основании консолидированных данных о доходах и посещениях занятий.
Подготовка в формировании счетов на оплату
Код-7. Отчет по активности посещений занятий учениками
Задания (самостоятельно):
Разработка кода: отчеты сводных таблицы о доходах и расходах компании, формирование общего баланса
Программируем генерацию интерактивного отчета об активности посещения занятий
VBA:
Программная работа со сводными таблицами. Основные свойства и методы
Программное создание, наполнение данными и удаление отчета сводной таблицы Программная работа с полями сводной таблицы (формат, фильтрация, вычисления) и макетом отчета, итогами
Добавление вычисляемого поля и вычисляемого элемента в сводных таблицах
Обращение к составляющим частям сводной таблицы (областям)
Создаем на занятии:
Код-1-6. Формирование отчета по статусу оплат на основании консолидированных данных о доходах и посещениях занятий.
Подготовка в формировании счетов на оплату
Код-7. Отчет по активности посещений занятий учениками
Задания (самостоятельно):
Разработка кода: отчеты сводных таблицы о доходах и расходах компании, формирование общего баланса
Автоматизация "Smart Education Ltd":
Визуализируем информацию о доходах и расходах компании
VBA:
Программная работа с диаграммами. Работа со свойствами и методами диаграмм Программная работа с элементами диаграммы: название, легенда, метки данных Программная работа со срезами и временными шкалами
Создаем на занятии:
Код-1. Генерация интерактивной информационной панели (dashboard) на основании сводной таблицы, графиков, срезов
Задания (самостоятельно):
Разработка кода: визуализация уровня затрат по собственникам
Визуализируем информацию о доходах и расходах компании
VBA:
Программная работа с диаграммами. Работа со свойствами и методами диаграмм Программная работа с элементами диаграммы: название, легенда, метки данных Программная работа со срезами и временными шкалами
Создаем на занятии:
Код-1. Генерация интерактивной информационной панели (dashboard) на основании сводной таблицы, графиков, срезов
Задания (самостоятельно):
Разработка кода: визуализация уровня затрат по собственникам
Автоматизация "Smart Education Ltd":
Добавляем обработку нестандартных действий пользователей в программный код
VBA:
Конструкции Оn error, Resume, Goto, Exit
Работа с объектом Err
Ускорение макросов. Свойства объекта Application
Работа с проектом: переименование, защита кода от просмотра и редактирования
Создаем на занятии:
Код 1-2. Добавление блоков обработки ошибок в ранее созданные коды
Задания (самостоятельно):
Добавление блоков обработки ошибок в коды предыдущих заданий
Добавляем обработку нестандартных действий пользователей в программный код
VBA:
Конструкции Оn error, Resume, Goto, Exit
Работа с объектом Err
Ускорение макросов. Свойства объекта Application
Работа с проектом: переименование, защита кода от просмотра и редактирования
Создаем на занятии:
Код 1-2. Добавление блоков обработки ошибок в ранее созданные коды
Задания (самостоятельно):
Добавление блоков обработки ошибок в коды предыдущих заданий
НЕ на основании учебного кейса
Резюмируем работу с формами: немодальная форма
Элементы управления и их события: переключатели и флажки Резюмируем работу со сводными таблицами, диаграммами и срезами: формируем объекты по условиям
Создаем на занятии:
Код1-5. Разработка формы настроек для организации информационной панели по заданным условиям
Резюмируем работу с формами: немодальная форма
Элементы управления и их события: переключатели и флажки Резюмируем работу со сводными таблицами, диаграммами и срезами: формируем объекты по условиям
Создаем на занятии:
Код1-5. Разработка формы настроек для организации информационной панели по заданным условиям
Курс ведет
АНАСТАСИЯ ПАСОЧНИК
Практикующий аналитик, последние 5 лет работает в компании «Юг-Контракт» (группа компаний “Фокстрот”)
Опыт работы с аналитикой закупок, продаж, маркетинговых мероприятий
Специализируется на оптимизации и создании системы отчетности "в один клик" (с помощью Excel + VBA Access)
Автор и тренер курса "VBA (макросы): автоматизация бизнес-процессов"
Тренер поддержки по курсу Power BI
© DataBI 2021 Все права защищены.
Киев, Украина, бул. Дружбы Народов, 21, оф.2
e-mail: info@data-b-i.com
Тел.: +38 (050) 020 83 95 +7 499 991 03 34
Мы в соцсетях:
e-mail: info@data-b-i.com
Тел.: +38 (050) 020 83 95 +7 499 991 03 34
Мы в соцсетях:
