Программы [iMacros] Создание макросов и их применение в Excel. Написание макросов Excel (2019)

[SliwBoy]

Автор: iMacros
Название: Создание макросов и их применение в Excel. Написание макросов Excel (2019)

Описание:

Вебинар 1. Минимум необходимой теории

Теоретический вебинар:

Что такое макросы? Как их писать?

Наша задача научиться трём способам написания макросов:

• с помощью макрорекордера (только как помощника)
• используя чужой код (где брать и как изменять под себя)
• сами с нуля (посвятим бОльшую часть времени)

Редактор VBE

Макросы пишут в редакторе Visual Basic Editor и наша задача научиться эффективно использовать его для решения своих задач. На вебинаре узнаем из каких окон состоит, как настроить "под себя" (какие панелии окно добавить, а какие лучше убрать).

Объектная модель Excel

90% макросов что-то делают с ячейками (Cells), диапазонами (Range), листами (WorkSheets) и excel-файлами (Workbooks), нам предстоит разобраться с иерархией этих объектов. Также узнаем их основные свойства и методы.

Домашнее задание:

Пишем с нуля макросы, которые:

• создаёт оглавление Excel-файла с большим количеством листов
• записывает информацию о пользователе, который открывает файл

Вебинар 2. Изменяем, копируем, удаляем через VBA

Практический вебинар:

3 варианта копирования

Существует три варианта копирования ячеек/диапазонов и мы научимся их использовать в зависимости от задачи и результата, который хотим получить.

5 способов удаления

Вы замечали, что вариантов удаления несколько:

• удалить только значения (ClearContents),
• очистить форматы (ClearFormats),
• удалить комментарий (ClearComments),
• удалить всё сразу (Clear),
• да ещё и со смещением (Delete)

Рассмотрим на примерах все способы.

Определяем последнюю заполненную ячейку

Макросы должны быть универсальными, для этого потребуется каждый раз вычислять диапазон, для которого писать формулу, либо границы копирования и т.д. Расскажу про три способа, которые точно будете использовать в своих кодах:

• свойство Range.End (когда в столбце все строки заполнены значениями)
• метод Range.Find (самую последнюю строку/столбец)
• метод SpecialCells ("самую-самую" последнюю (даже удалённую) строку/столбец)

Домашнее задание:

Пишем с нуля макросы, которые:

• создаёт оглавление Excel-файла с большим количеством листов
• записывает информацию о пользователе, который открывает файл

Вебинар 3. Циклы - короли автоматизации

Практический вебинар:

Что такое циклы? Почему они короли?

Макросы должны уметь делать работу за нас. Мы напишем код, а макрос пусть +100500 раз повторит все наши действия.

Если Вы уже "в теме" макросов, то вот какие типы циклом рассмотрим:

• For ... next (цикл со счётчиком)
• Do ... Loop (с условиями While и Until)
• For each ... (по объектам коллекций WorkSheets, Сells и др.)

К примеру, Вам надо заполнить данными 150 договоров по шаблону. Вот мы и запишем пару строк кода как заполнить 1 договор, а циклы повторят процедуру 150 раз. Делая руками, мы бы потратили 2 дня, а макрос за 2 минуты выполнит эту работу.

Основы работы с переменными

Если циклы - это короли, то переменные - их верные помощники. Имя листа, файла, путь сохранения и т.д. - постоянно меняются. Определим для подобных параметров переменные и напишем макрос, который будет использовать универсальный код и выполняться в 2-3 раза быстрее.

Домашнее задание:

Пишем с нуля макросы, которые:

• заполнит 138 договоров ГПХ и отправит их каждому сотруднику на почту
• из выгрузки продаж за 9 мес. сформирует excel-файлы по зонам ответственности каждого менеджера и директора региона и сохранит в нужную папку

Вебинар 4. Учимся общаться с пользователем

Практический вебинар:

Скажи мне: "Кто ты?"

До искуственного интеллекта нам будет ещё далеко, но вот запросить информацию от пользователя (ответить Да/Нет, ввести число, выделить диапазон ячеек, для которых будет выполнен макрос и т.д.) мы научимся. Возможно, Вы уже слышали про:

• MsgBox (информационные сообщения),
• InputBox (получение данных от пользователя)
• Application.InputBox (продвинутый вариант InputBox)

Мы на практических примерах разберёмся во всех тонкостях.

Работа с файловой системой

Нам следует научиться писать команды, которые будут:

• создавать и удалять папки (нужно, чтобы макрос смог сохранять результат в нужное место),
• выводить FileDialog (решили собрать данные с нескольких excel-файлов, вот и надо попросить пользователя указать какие конкретно) и т.д.

Ничего сложного в этой теме нет, пугаться не стоит.

Домашнее задание:

Пишем с нуля макросы, которые:

• создаёт список файлов в папке (покажу два принципиально разных варианта решения задачи)
• сбор данных из нескольких файлов Excel в один (все из одной папки, по списку файлов, по тем, что выберет пользователь)

Вебинар 5. Функции VBA. + Создание собственных

Практический вебинар:

Макросы на VBA - это Вам не ВПР писать. Тут думать надо!

Формулы в VBA можно писать несколькими способами, каждый имеет свои особенности, вот нам и надо будет разобраться с:

• текстом (UCase, LCase, StrConv, Left, Mid, Len, InStr, RevStr, Trim и др.)
• датой и временем (Date, Time, Now, WeekDay, DateDiff, DateAdd и др.)
• числами (Abs, Fix, Int, Round, Rnd, Sgn, Sin, Cos, Tan, Atn и др.)
• преобразование данных (IsNumeric, IsDate, IsArray, IsObject, IsEmpty и др.)
• форматами (Format, FormatCurrency/DateTime/Number/Percent)

Function - то тоже макрос, но необычный

Узнаем про отдельный вид макросов - Функции (Function). Расскажу, про принципиальные отличия от "обычных", научу писать свои собственные формулы (их нет ни в одной версии Excel):

• СуммаЯчеекПоЦвету (находит сумму ячеек с определённым цветом заливки)
• ЛатиницаВРусские и РусскиеВЛатиницу (преобразует буквы)
• ЧислоИзЯчейки (извлекает из ячейки с текстом число)

Также поделюсь собственными готовыми функциями, которые не надо писать самим, а просто взяли и используете (+можно под свои задачи легко изменить).

WorkSheetFunction - ещё один вариант написания формул VBA

Особый вид функций, который работает быстрее обычных, но имеет свои особенности. К примеру формулу ВПР в ячейку B1 можно написать так:

• Range("B1").FormulaLocal = "=ВПР(A1;$D$1:$F$5;2;0)"
• Range("B1") = Application.VLookup(Сells(1,1);Range($D$1:$F$5);2;0)
• Range("B1") = Application.WorksheetFunction.Vlookup(сells(1,1);Range($D$1:$F$5);2;0)

Да, и каждый вернёт свой результат. На вебинаре разберёмся

Домашнее задание:

Напишем много строк кода, которые:

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

Вебинар 6. Автоматизируем создание сводных таблиц

Практический вебинар:

Сводная макросом? Вы серьёзно?

Сразу предостерегу Вас о идеи строить ВСЕ СВОДНЫЕ таблицы с помощью VBA. У нас нет такой задачи!!! На вебинаре напишем код VBA для анализа данных, а сводная таблица будет промежуточным этапом этого анализа.

Так чему же мы научимся?

Нам следует научиться писать команды, которые будут:

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

+ будет много мелких команд, которые важны, но я не смог выделить их в отдельные группы.

Домашнее задание:

Построим с помощью макросов 5 сводных таблицы, в каждой будет своя изюминка (надо будет не просто построить, а где-то применить фильтр, выполнить группировку и доп.вычисления, создать вычисляемое поле и т.д.). Скучно точно не будет!

Скачать: