Автоматизация в Excel: от простых макросов до заполнения шаблонов (Часть 3)

Автоматизация задач в Excel с помощью макросов – от записи простых действий до заполнения шаблонов. Примеры VBA-кода, советы по созданию макросов, типичные ошибки.

Абсолютные, относительные и смешанные ссылки в формулах Excel: секреты и примеры (Часть 1)

Имена в формулах Excel: делаем таблицы понятными для команды (Часть 2)

Автоматизация в Excel: от простых макросов до заполнения шаблонов (Часть 3)

Введение

Каждый день или месяц вы тратите часы на повторяющиеся действия в Excel? Пора познакомиться с макросами! Макрос – это набор команд, который Excel может выполнить автоматически, сэкономив вам время и устранив рутину. Вместо того чтобы десятки раз щёлкать по одним и тем же кнопкам, вы можете нажать одну клавишу и позволить программе всё сделать за вас.

Многие пользователи избегают макросов, думая, что для их создания нужно быть программистом. Отчасти это миф. Excel предоставляет удобное средство записи макросов: вы просто включаете запись, выполняете нужные шаги вручную, а редактор VBA сам составляет для вас код макроса. В итоге у вас появляется программа, которую можно запустить в любое время.

В этой заключительной части серии мы рассмотрим два примера автоматизации. Сначала запишем простой макрос и научимся им пользоваться. Затем перейдём к более сложной задаче – напишем код, который автоматически распределит данные по шаблону (сформирует отдельные листы/отчёты на основе списка). Шаг за шагом вы увидите, что макросы Excel – это не так сложно и очень полезно. Давайте начнём!

Пошаговое руководство

Начнём с создания простого макроса с помощью записывающего устройства Excel:

1. Активируйте режим разработчика. По умолчанию вкладка Разработчик может быть скрыта. Чтобы её включить, зайдите в Файл > Параметры > Настройка ленты и поставьте галочку напротив "Разработчик". На этой вкладке находятся все инструменты для работы с VBA.
2. Запустите запись макроса. Нажмите на вкладке Разработчик кнопку Записать макрос (или воспользуйтесь сочетанием Alt+T+M+R). Появится окно настройки. Введите имя макроса, например FormatHeader. Можно назначить удобную горячую клавишу (например, Ctrl+Shift+H) – тогда запускать макрос будет проще. Убедитесь, что поле "Сохранить макрос в" установлено в Эта книга, и нажмите OK – запись началась.
3. Выполните нужные действия вручную. Теперь Excel запоминает каждый ваш шаг. Например, выделите диапазон A1:D1 на вашем листе (предположим, это заголовки таблицы) и примените заливку цветом и полужирный шрифт к этим ячейкам. Вы можете совершить любые действия, которые хотите автоматизировать – вычисления, ввод текста, форматирование, вставку объектов и т.д.
4. Остановите запись. Вернитесь на вкладку Разработчик и нажмите Остановить запись. Макрос сохранён. Вы только что запрограммировали Excel выполнить последовательность ваших действий!
5. Запустите макрос. Чтобы проверить, что всё записалось правильно, сначала отмените или уберите сделанные изменения (например, очистите форматирование A1:D1). Далее нажмите Макросы (на той же вкладке) или нажмите Alt+F8 – откроется список макросов. Выберите FormatHeader и нажмите Выполнить. На ваших глазах Excel повторит все записанные действия – диапазон снова форматируется так, как вы задали. Поздравляем, вы создали свой первый макрос!
6. Посмотрите код макроса (по желанию). Для интереса нажмите Alt+F11 – откроется редактор Visual Basic. В списке модулей найдите Module1 и откройте его. Вы увидите программный код вашего макроса FormatHeader. Excel автоматически сгенерировал его на VBA, повторив там каждый шаг (например, изменение свойств Range для установки цвета заливки и т.д.). Вы можете закрыть редактор, если не планируете пока редактировать код вручную.

Если вам нравится идея экономить время с помощью макросов, поддержите наш канал Макрос решает – подпишитесь и поставьте лайк! Ваши комментарии и вопросы также очень ценны для нас.

Примеры применения

Пример 1: Простой макрос с циклом (увеличение чисел на 10%)

Записывать макросы – не единственный способ автоматизации. Если вы немного освоите язык VBA, сможете писать макросы вручную, добиваясь большего контроля. Рассмотрим простой пример: у вас есть диапазон чисел, и вы хотите увеличить каждое число на 10%. Это можно сделать формулой, но макрос позволит выполнить операцию сразу для всего диапазона.

Ниже приведён код макроса, который умножает каждое число в текущем выделении на 1.1 (то есть +10%):

Sub IncreaseBy10()
Dim cell As Range
For Each cell In Selection
If IsNumeric(cell.Value) Then
cell.Value = cell.Value * 1.1
End If
Next cell
End Sub

Чтобы его использовать, можно скопировать этот код в модуль VBA. Макрос IncreaseBy10 пройдётся по всем ячейкам в выделенной области (объект Selection), проверит, является ли значение числом (IsNumeric), и если да – перемножит его на 1.1. Все числа увеличатся на 10%. Такой макрос экономит уйму времени, когда нужно массово пересчитать данные.

Пример 2: Распределение данных по шаблону (генерация отчётов)

Представим более сложную задачу: у вас есть список данных, и требуется на основе каждой строки этого списка создать отдельный отчёт по заданному шаблону. Вручную это заняло бы много времени: копировать шаблон, вводить значения, сохранять... Макрос справится с этим автоматически.

Сценарий: допустим, на листе "Данные" у вас таблица с перечнем проектов: в каждой строке указано название проекта, ответственный исполнитель и плановое значение. Также имеется лист "Шаблон" – красиво оформленная заготовка отчёта, где предусмотрены места для подставки этих значений (например, в ячейки B2, B3, B4). Наша цель – для каждой строки из "Данных" создать копию листа-шаблона, подставить туда значения и переименовать лист по названию проекта.

Это можно осуществить с помощью макроса на VBA:

Sub GenerateReports()
Dim lastRow As Long, i As Long
Dim wsData As Worksheet, wsTemplate As Worksheet
Set wsData = ThisWorkbook.Sheets("Данные")
Set wsTemplate = ThisWorkbook.Sheets("Шаблон")
lastRow = wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row
For i = 2 To lastRow
wsTemplate.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
With ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
.Name = wsData.Cells(i, 1).Value
.Range("B2").Value = wsData.Cells(i, 1).Value
.Range("B3").Value = wsData.Cells(i, 2).Value
.Range("B4").Value = wsData.Cells(i, 3).Value
End With
Next i
End Sub

Разберём логику этого кода:

1. Мы заранее создали объекты wsData и wsTemplate для удобства, ссылающиеся на листы "Данные" и "Шаблон".
2. Переменная lastRow находит последний заполненный ряд в первом столбце листа "Данные" (предполагаем, что там перечислены названия проектов). Цикл For i = 2 To lastRow пойдёт по каждой строке списка, начиная со 2-й (строка 1 – заголовки).
3. Внутри цикла происходит основная магия: wsTemplate.Copy After:=... – мы копируем лист-шаблон в конец файла. Excel создаёт его копию с именем вроде "Шаблон (2)" и делает её активной.
4. Далее конструкция With ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count) позволяет обратиться к только что скопированному листу (он теперь последний в книге). Мы даём ему имя из значения в колонке A текущей строки (например, название проекта).
5. Затем заполняем ячейки B2, B3, B4 на этом новом листе значениями из соответствующих колонок текущей строки листа "Данные". Предположим, в колонках A, B, C "Данных" хранятся Название, Исполнитель, План соответственно – эти значения подставляются в шаблон. - Цикл повторится для каждой строки – в результате у вас появится пачка листов, каждый с данными своего проекта по заданному шаблону.

Такой макрос можно развивать дальше: например, вместо создания листов в той же книге генерировать отдельные файлы для каждого проекта, или отправлять отчёты сразу на печать. Но даже в базовом варианте, как выше, мы автоматизировали рутинную работу, исключив риск ошибок при копировании и сэкономив уйму времени сотрудникам.

Мини-задачи для практики

1. Мини-задача 1. Определите рутинную операцию, которую вы часто выполняете в Excel (например, оформление отчёта, перенос данных, применение фильтров). Попробуйте записать макрос для автоматизации этой задачи. Затем запустите его и убедитесь, что всё прошло правильно. Экспериментируйте с горячими клавишами и разными вариантами – так вы поймёте возможности рекордера макросов.
2. Мини-задача 2. Откройте редактор VBA (Alt+F11) и создайте новый модуль. Скопируйте туда код из Примера 1 (IncreaseBy10). Вернитесь в Excel, введите в несколько ячеек числа и выполните макрос – убедитесь, что значения изменились. Попробуйте изменить код: например, вместо умножения на 1.1 сделать прибавление фиксированной суммы или изменение формата ячеек. Запустите снова и посмотрите на результат.
3. Мини-задача 3. Смоделируйте ситуацию из Примера 2 в учебном файле: создайте лист с небольшим списком (3–4 строки данных) и лист-шаблон. Вставьте код GenerateReports в модуль и протестируйте его на ваших данных. Все ли листы создались правильно? Попробуйте затем адаптировать макрос под другой похожий сценарий ваших рабочих задач. Если что-то не получается – смело задавайте вопросы (и не забудьте сохранить книгу в формате XLSM, чтобы макросы не пропали!).

Советы и типичные ошибки

Полезные советы

• Всегда сохраняйте файл с макросами правильно. При первой записи макроса сохраните книгу в формате .xlsm (Macro-Enabled Workbook). Иначе при закрытии обычной .xlsx все ваши макросы будут утеряны. Не забывайте это правило – очень обидно потерять проделанную работу.
• Держите копию данных и отладочных версий. Макросы выполняются быстро и без возможности "Отменить". Поэтому тестируйте новый макрос на копии данных или резервной версии файла. Убедитесь, что код работает правильно, прежде чем применять его на боевых данных.
• Используйте относительные ссылки при записи, если нужно. По умолчанию рекордер макросов записывает действия привязано к конкретным ячейкам. Если вам нужен более гибкий макрос (работающий от текущей ячейки или диапазона), нажмите кнопку Относительные ссылки перед записью. Тогда в код попадут относительные адреса, и макрос можно будет применять в разных местах.
• Создайте "личную книгу" для макросов. Если у вас есть макросы, которыми вы хотите пользоваться во всех таблицах, сохраните их в личной книге макросов (Personal.xlsb). Эта скрытая книга открывается вместе с Excel, и её макросы всегда доступны. При записи макроса можно сразу выбрать "Личная книга макросов" в поле "Сохранить макрос в".
• Давайте понятные имена и комментарии. Названия макросов (Sub ...) пишите так, чтобы отражали действие, например Sub ОчиститьФормат() вместо Sub Macro1(). Также не стесняйтесь добавлять комментарии (строки, начинающиеся с ') внутри кода – это поможет понять логику, если вы вернётесь к нему через месяц, и облегчит работу коллегам.
• Оптимизируйте длительные макросы. Если макрос обрабатывает большой объём данных и работает медленно, его можно ускорить. Например, перед началом выполнений отключите обновление экрана (Application.ScreenUpdating = False) и автоматическое пересчёт формул (Application.Calculation = xlCalculationManual), а в конце снова включите. Это предотвратит мигание окна и лишние вычисления, заметно повысив скорость выполнения.

Распространённые ошибки

• Забыли включить макросы при открытии файла. При получении книги с макросами убедитесь, что нажали кнопку "Включить содержимое". Иначе никакие макросы не будут работать (и Excel может даже не сообщить об этом явно).
• Неправильные ссылки в коде. Записанный макрос может жёстко ссылаться на диапазон или лист (например, лист "Sheet1"). Если структура файла изменилась (переименовали лист, изменили расположение диапазона), макрос выдаст ошибку или отработает некорректно. Решение – проверять и обновлять ссылки в коде при изменениях структуры или делать код более универсальным (использовать объекты Worksheets вместо конкретных имён и т.п.).
• Дублирование имён и объектов. Ошибка случается, если макрос пытается, например, присвоить листу имя, которое уже используется, или обратиться к несуществующему элементу. В Примере 2, например, две строки с одинаковым названием проекта привели бы к попытке создать второй лист с уже существующим именем – VBA выдаст ошибку.
  
  Такие ситуации нужно предусматривать: либо очищать старые листы перед запуском, либо добавлять проверку (оператор On Error и обработку случая конфликтов имён).
• Пренебрежение безопасностью. Макросы – мощный инструмент, но в руках злоумышленника также могут навредить. Не запускайте макросы из неизвестных источников. В рабочих процессах обеспечьте, чтобы VBA-проект был защищён паролем (если там чувствительный код), а пользователи понимали, зачем им нужно включать макросы. Всегда проверяйте код макросов, скачанных из интернета, прежде чем выполнять их на своих данных.

Теперь вы освоили основы автоматизации в Excel с помощью макросов! Мы прошли путь от правильного использования ссылок и имён в формулах до записи и написания собственных VBA-программ. В ваших руках мощный инструмент, который экономит время и избавляет от рутины.

Это была заключительная, третья часть серии. Если материалы оказались полезными – поддержите нас подпиской на канал Макрос решает и лайком. Обязательно расскажите в комментариях, какие идеи для автоматизации вы планируете воплотить с помощью макросов. Желаем успехов в оптимизации ваших задач и ждем обратной связи!