Представьте себе такую ситуацию, которая, наверняка, возникает у многих: каждый понедельник вы тратите сорок минут ( ато и более) на одно и то же.
Открываете пятнадцать файлов, копируете данные, красите шапку в синий, меняете шрифт на Arial, подгоняете ширину столбцов, ставите дату в колонтитул и сохраняете результат.
Руки делают привычную работу, а мозг тихо умирает от скуки. Знакомо? А теперь представьте, что всё это происходит само, а главное быстро, по одному щелчку мыши, пока вы наливаете вторую чашку кофе.
Это никакая не фантастика. Это все может делать за вас VBA — язык, встроенный в Excel, который умеет разговаривать с таблицами вместо вас.
Многие люди работающие с Excel боятся слова «программирование». Кажется, что VBA — удел избранных, вундеркиндов которые с пелёнок читали двоичный код.
На самом деле освоить первые шаги можно за полчаса, не написав ни строчки вручную. Excel сам запишет ваши действия и превратит их в программу. А дальше вы просто начнёте понимать, как устроен этот механизм, и постепенно перейдёте от записи к собственным командам.
В этой статье мы сделаем три реальных макроса, которые пригодятся в работе сразу. Никакой воды — только практика.
Часть 1. Подготовка плацдарма: включаем инструменты и снимаем защиту
Прежде чем начинать что-то автоматизировать, нужно подготовить рабочее пространство. Разработчики Microsoft почему-то решили, что обычному пользователю макросы не нужны, и спрятали все инструменты подальше. Первым делом вернём их на видное место.
Где живут макросы. Вкладка «Разработчик» — это командный центр автоматизации, но по умолчанию она скрыта. Чтобы включить её, кликните правой кнопкой мыши по ленте, выберите «Настройка ленты» и поставьте галочку напротив заветного пункта.
Всё, у вас появилась новая вкладка с кнопками «Запись макроса», «Макросы» и «Visual Basic». Теперь они всегда будут у вас на виду.
Безопасность превыше всего. Макросы — мощный инструмент, и злоумышленники это знают. Поэтому Excel относится к файлам с кодом с подозрением.
Обычная книга имеет расширение .xlsx и не умеет хранить макросы. Как только вы добавляете в файл хотя бы один макрос, сохранять его нужно в формате .xlsm — «Книга Excel с поддержкой макросов». Запомните это расширение, оно пригодится вам постоянно.
Настройка доверия. По умолчанию Excel блокирует выполнение любого VBA-кода и показывает жёлтую панель с предупреждением. Это правильно, но для собственных файлов такое поведение только мешает.
Зайдите в «Файл» → «Параметры» → «Центр управления безопасностью» → «Параметры центра». Выберите пункт «Отключить все макросы с уведомлением» — тогда при открытии вашего .xlsm-файла Excel спросит разрешение, а вы просто нажмёте «Включить содержимое».
Не выбирайте варианты без предупреждений: безопасность всё-таки важна.
Знакомство с редактором. Нажмите Alt+F11 — и вы попадёте в другую реальность. Это редактор Visual Basic, который выглядит как отдельная программа.
Слева вы увидите дерево проекта: все открытые книги и листы внутри них. Справа — большое пустое поле, где и будет жить ваш код.
Не пугайтесь: сейчас мы не будем писать здесь ни строчки, просто запомните, как сюда попасть. Когда захотите посмотреть на работу макроса изнутри — вам сюда.
Подготовка завершена. Можно начинать!
Часть 2. Три кита автоматизации для начинающих
Я обещал, что теория будет по минимуму, и сдержу свое слово. Дальше — три практических приёма, от полного нуля до первого написанного кода.
Кит №1: Макрос без кода — запись действий
Самый простой способ подружиться с автоматизацией — заставить Excel запомнить то, что вы делаете руками. Это называется «запись макроса», и здесь не нужно знать ни одной команды.
Поставим задачу. У вас есть «сырая» таблица: данные выгружены из учётной системы, все столбцы разной ширины, шапка ничем не выделяется, цифры прилипают к краям ячеек. Каждое утро вы приводите её в приличный вид. Давайте автоматизируем эту скучную работу.
Выделите любую подходящую таблицу и нажмите на вкладке «Разработчик» кнопку «Запись макроса».
В появившемся окне дайте макросу понятное имя, например Форматировать Отчет, и нажмите ОК. Всё — запись пошла.
Теперь каждое ваше действие фиксируется. Сделайте следующее: выделите шапку таблицы и залейте её тёмно-синим, а шрифт сделайте белым и жирным. Затем выделите всю таблицу, проведите границы всех ячеек, выровняйте текст по центру.
В конце выделите все столбцы с данными и дважды кликните по правой границе одного из заголовков, чтобы подогнать ширину. Закончили — нажмите «Остановить запись».
Теперь самое интересное. Очистите форматирование с таблицы (сделайте вид, что это новый отчёт, который только что пришёл) и нажмите кнопку «Макросы» на вкладке «Разработчик». В списке найдите ваш ФорматироватьОтчет, нажмите «Выполнить». Таблица примет корпоративный вид за долю секунды.
Чтобы было ещё удобнее, привяжите макрос к кнопке прямо на листе. Вставьте любую фигуру — прямоугольник или овал, нарисуйте её рядом с таблицей, подпишите «Форматировать». Кликните по фигуре правой кнопкой, выберите «Назначить макрос» и укажите ваш. Теперь любой коллега сможет форматировать отчёты одним кликом, даже не подозревая о существовании VBA.
Заглянем под капот. Нажмите Alt+F11, найдите в левом дереве модуль с записанным макросом и посмотрите на код. Вы увидите множество строк вроде Selection.Font.Bold = True и Range("A1:D15").Select.
Да, код выглядит техническим, но смысл его интуитивно понятен. Excel записал всё дословно, даже лишние движения мышкой. Позже вы научитесь чистить такой код, но для начала главное — понять, что макросы не магия, а просто записанные действия.
Кит №2: Ваш первый цикл «Для каждого» — обрабатываем все листы сразу
Запись — прекрасный инструмент, но у неё есть ограничение. Она фиксирует то, что вы делаете с конкретным листом. А как быть, если нужно прогнать одно и то же действие через двадцать вкладок?
Нажимать запись, переключать листы вручную и останавливать — можно, но это полумера. Давайте научимся писать код, который сам обходит все листы книги.
Задача: у вас в книге двенадцать листов с отчётами за каждый месяц. На каждом листе нужно поставить в верхний колонтитул дату и вашу фамилию, а также закрепить шапку, чтобы она не прокручивалась. Делать это руками на каждом листе — долго и тоскливо. Пусть VBA сделает это за вас сам.
Откройте редактор Alt+F11, в левом окне кликните правой кнопкой по имени вашей книги, выберите Insert → Module. Появится чистое белое поле. Перепишите туда следующий код:
vba
Sub ПодготовитьВсеЛисты()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.PageSetup.LeftHeader = "Отчет подготовлен: &D"
ws.PageSetup.RightHeader = "Иванов И.И."
ws.Activate
Rows("1:1").Select
ActiveWindow.FreezePanes = True
Cells(1, 1).Select
Next ws
MsgBox "Готово! Все листы обработаны."
End Sub
Разберём, что здесь происходит, чтобы вы не просто копировали, но и понимали. Строка Dim ws As Worksheet объявляет переменную — это как временная коробка, куда по очереди будут складываться листы.
For Each ws In ThisWorkbook.Worksheets означает: «возьми каждый лист в этой книге и сделай с ним то, что написано дальше».
Код внутри цикла ставит левый колонтитул (текущая дата подставляется автоматически благодаря коду &D), правый колонтитул (ваша фамилия) и закрепляет первую строку. Финальное MsgBox выводит сообщение, чтобы вы понимали: работа завершена.
Запустите макрос через F5 или закройте редактор и найдите его в списке макросов на вкладке «Разработчик». Через секунду все листы будут обработаны.
Главная магия здесь не в колонтитулах, а в конструкции For Each. Запомните её — это ваш главный инструмент для обработки групп объектов. По такому же принципу можно перебрать все открытые книги или все ячейки в диапазоне.
Кит №3: Диалог с пользователем — макрос, который спрашивает и считает
Движемся дальше. Предыдущий макрос работал сам по себе, но что если нужно, чтобы программа каждый раз действовала по-разному в зависимости от ситуации? Например, вы хотите поднять цены на товары, но процент наценки каждый месяц новый.
Вместо того чтобы вбивать формулу и протягивать её вручную, пусть макрос спросит у вас нужное число и сам сделает вычисления.
Добавьте новый модуль (так же, как в прошлый раз) и вставьте этот код:
vba
Sub ПоднятьЦены()
Dim Percent As Variant
Dim LastRow As Long
Dim i As Long
Percent = InputBox("На сколько процентов поднять цены?", "Наценка", 10)
If Percent = "" Then Exit Sub
If Not IsNumeric(Percent) Then
MsgBox "Пожалуйста, введите число."
Exit Sub
End If
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To LastRow
Cells(i, 2).Value = Cells(i, 1).Value * (1 + Percent / 100)
Next i
MsgBox "Новые цены рассчитаны в столбце B"
End Sub
Что здесь происходит, по шагам. InputBox выводит окно с вопросом и полем для ввода. Если человек нажал «Отмена» или ничего не ввёл — макрос просто выключается (Exit Sub).
Дальше проверка: IsNumeric выясняет, число ли ввёл пользователь. Если он случайно написал «много» вместо «15», программа сообщит об ошибке, но не сломается.
Затем умная строчка LastRow = Cells(Rows.Count, 1).End(xlUp).Row находит последнюю заполненную строку в столбце A — это стандартный трюк, чтобы не обрабатывать пустые ячейки.
И наконец цикл For i = 2 To LastRow проходит по строкам со второй (первая — шапка) до последней, берёт значение из столбца A и записывает увеличенную цену в столбец B.
Запустите макрос, введите, скажем, 15. Готово — прайс пересчитан. На следующий день запустите снова, но введите 12. Механика та же, результат другой.
Вы только что написали программу, которая принимает решение на основе пользовательского ввода, — а это уже серьёзный шаг вперёд по сравнению с простой записью действий.
Часть 3. Шпаргалка новичка: что делать, если макрос не работает
Первые макросы никогда не работают идеально. Это нормально. Сейчас я расскажу о трёх самых частых граблях, на которые наступают новички, и о том, как с них слезть.
Ошибка №1: макрос прыгает не туда
Вы записали действия над таблицей на листе «Январь», а запустили на листе «Март» — и макрос покрасил совсем не те ячейки. Почему? При записи Excel по умолчанию использует абсолютные ссылки: запоминает, что вы кликнули именно в A1, а не в «верхнюю левую ячейку выделенного диапазона».
Решение простое: перед началом записи нажмите на вкладке «Разработчик» кнопку «Относительные ссылки».
Теперь макрос будет работать относительно активной ячейки, а не по жёстко прописанному адресу.
Ошибка №2: данные закончились раньше, чем макрос
Вы записали действия на таблице из ста строк, а в новой выгрузке строк оказалось пятьдесят. Макрос добросовестно полез в сотую строку и наткнулся на пустоту.
Или наоборот: вы записали обработку до сотой строки, а данных пришло на триста — и половина осталась необработанной.
Запомните волшебную конструкцию из третьего примера: Cells(Rows.Count, 1).End(xlUp).Row. Она всегда находит последнюю заполненную ячейку, сколько бы данных ни было. Используйте её вместо фиксированных чисел.
Совет по отладке
Если макрос вылетает с непонятной ошибкой, а вы не понимаете, в каком месте, — откройте редактор Alt+F11, поставьте курсор на любую строку внутри кода и нажимайте F8.
Excel будет выполнять по одной строке и подсвечивать текущую. Вы увидите, в какой именно момент всё ломается, и сможете понять причину.
Золотое правило
Перед первым запуском любого макроса, записанного или скачанного, создайте копию файла. Не пренебрегайте этим даже ради простого форматирования.
VBA не умеет отменять действия через Ctrl+Z — если макрос сделал что-то не то, вернуть прежнее состояние можно только закрытием файла без сохранения. Бережёного бог бережёт.
Часть 4. Куда расти дальше?
Три макроса из этой статьи — только верхушка айсберга. Если вы почувствовали вкус автоматизации, вот направление, в котором стоит двигаться.
От записи — к написанию. Записанный код содержит много шума. Команды вроде Selection.Select или многократные прокрутки экрана не несут пользы, а только замедляют работу.
Когда освоитесь, научитесь чистить сгенерированный код: убирать лишние выделения, заменять Select на прямое обращение к объектам. Код станет короче в два-три раза и начнёт работать быстрее.
События. Макросы не обязательно запускать кнопкой. Можно сделать так, чтобы они выполнялись сами. Открыли книгу — автоматически обновились сводные таблицы.
Перешли на лист «Итоги» — сразу построился график. Изменили значение в ячейке — пересчитался связанный блок. Такие сценарии называют событиями, и они превращают Excel из таблицы в живой дашборд.
UserForms. Те окошки с полями ввода, кнопками и списками, которые вы видите в профессиональных Excel-приложениях, — это пользовательские формы. Освоив их, вы сможете создавать интерфейсы, неотличимые от обычных программ, и ваши коллеги будут думать, что вы наняли программиста.
VBA не требует диплома о высшем техническом образовании. Это инструмент, который освоить может практически каждый и что самое главное, он растёт вместе с вами.
Сначала вы просто записываете форматирование. Через месяц — пишете цикл для обработки ста листов. Ещё через месяц — создаёте полноценное приложение с кнопками и формами. Каждый шаг приносит практическую пользу и экономит время.
👆 Хотите упростить себе жизнь и научиться писать макросы и работать с ними на профессиональном уровне? Пройдите обучение онлайн. Оно стоит недорого и длится всего несколько месяцев. Вот пара вариантов:
1. ⭐⭐⭐ Курс "Excel: макросы" от Skillbox. Уже через месяц вы сможете автоматизировать кучу своих рабочих задач, освободив себе драгоценное время, которое раньше уходило на рутину! Есть промокод на скидку в 5 % - Подарок18.
2. ⭐⭐⭐ Курс от финансового университета sf.education, который называется "Программирование на VBA". За несколько месяцев вы научитесь самостоятельно создавать любые макросы для Excel, интегрировать его с другими программами и визуализировать финансовые данные с помощью VBA. Промокод на скидку в 15 % - advcake60.
Начните прямо сегодня. Откройте Excel, включите вкладку «Разработчик» и запишите свой первый макрос — пусть даже самый простой, красящий шапку в корпоративный синий.
Освоив этот инструмент, вы придёте на работу и сделаете за минуту то, на что раньше уходило полчаса. А когда коллеги спросят, как вы это провернули, — просто улыбнитесь и нажмите Alt+F11.
Реклама. ООО СОВРЕМЕННЫЕ ФОРМЫ ОБРАЗОВАНИЯ, ИНН 7841081586, erid: LdtCKNoev
Реклама. ЧУ ЧАСТНОЕ ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ ДОПОЛНИТЕЛЬНОГО ПРОФЕССИОНАЛЬНОГО ОБРАЗОВАНИЯ ОБРАЗОВАТЕЛЬНЫЕ ТЕХНОЛОГИИ СКИЛБОКС (КОРОБКА НАВЫКОВ), ИНН 9704088880, erid: 2VfnxwisD9b