Книга: Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах)
Назад: Из Excel в Google Таблицы и обратно
Дальше: Заключение

Несколько слов о макросах

В этой книге мы не рассматриваем тему макросов подробно: ей посвящены отдельные издания (я рекомендую книгу «Профессиональное программирование на VBA», подробнее о ней — чуть ниже в ). Скажем несколько слов, чтобы у вас сложилось представление о макросах в первом приближении, если вы еще с ними не сталкивались.
Макросы — это программы на встроенном в Excel языке программирования VBA. В широком смысле задача макросов — управление данными. С помощью макросов можно автоматизировать рутинные операции и избавиться от ручной работы, ускорив обработку данных в Excel и исключив ошибки ручного ввода и/или обработки данных.
Примеры можно приводить долго: сравнивать два файла, поменять шрифт у всех диаграмм на всех листах в десяти документах, исправить некорректно выгруженные данные из 1С, создать из одного сводного файла много отдельных за несколько секунд автоматически, создать оглавление — отдельный лист со ссылками на все остальные (пример такого макроса будет ниже) и многое другое.
Visual Basic был выпущен в 1991 году и стал стандартным языком для написания сценариев в приложениях Microsoft.
Поскольку макрос — это программа, а программа теоретически может выполнить вредоносные действия, с определенного момента Microsoft стал выделять отдельные форматы для документов Excel с макросами. Обычный формат последних версий Excel — XLSX — не предусматривает сохранение макросов. В нем можно их создать, но после этого придется сохранить в одном из специальных типов с макросами. Это XLSM или XLSB.
В версиях Excel 97–2003 файлы были с расширением xls и не имелось разделения на книги с макросами и без.
Создать макрос можно двумя способами: ввести код (или скопировать фрагменты готового кода из книг или интернета) или записать макрос с помощью средства записи (макрорекордера).
После нажатия на эту кнопку начнется запись.

 

 

Пока макрос не остановлен, все ваши действия будут фиксироваться.

 

 

Запись макроса — это своего рода магнитофон: все действия, которые вы производите, записываются на «пленку» в виде кода VBA.
Запись макроса имеет свои ограничения. Как правило, записанный макрос приходится дорабатывать, оптимизировать, не все действия можно записать в принципе (невозможно своими руками создать ссылку «на все листы в книге, сколько бы их ни было» — вы можете создать ссылки на одну, десять или сто книг, но это будет фиксированная история, в записи макросов нельзя использовать циклы или оперировать переменными).
Но этот инструмент очень удобен для обучения и знакомства с инструкциями: если вы не знаете, как называется тот или иной объект или свойство, запишите макрос, проделав действия с ним, и посмотрите на результат.
МАКРОСЫ VS POWER QUERY
Актуальность макросов несколько снизилась после того, как в Excel появилась надстройка Power Query: если раньше для обработки данных и решения нетривиальных задач (собрать все книги Excel из одной папки, объединить данные, очистить от лишнего, поменять форматы и структуру при необходимости, построить на основе этого отчет со сводными таблицами, диаграммами и прочим) приходилось писать макрос, то сейчас есть более дружелюбный интерфейс Power Query, позволяющий автоматизировать многие подобные задачи, не прибегая к написанию кода.
Наглядно об этом говорит вот эта иллюстрация из книги «Приручи данные с помощью Power Query в Excel и Power BI» (Кен Пульс, Мигель Эскобар).

 

 

Это не отменяет того, что с помощью макросов можно автоматизировать практически любой набор действий. А потом вызывать его в любой момент, например с помощью кнопки на панели быстрого доступа.
Давайте рассмотрим пример — решение простой задачи, которую нельзя быстро реализовать встроенными средствами.
ПРИМЕР ПРИМЕНЕНИЯ МАКРОСОВ: ОГЛАВЛЕНИЕ
В Excel (как и во многих других приложениях) можно вставить ссылку на место в документе с помощью сочетания Ctrl + K.
После чего выбрать слева «Связать с» — «Место в документе».
Вводим отображаемый в ячейке текст ссылки и адрес ячейки, внизу в списке выбираем лист.
И получаем ссылку для перехода на соответствующее место в документе.

 

 

Но если листов много, вручную проделывать это для каждого листа будет мучительно, поэтому нам пригодится макрос, который формирует оглавление на отдельном листе автоматически и делает это для любого количества листов в книге.
И это пример задачи, которую можно решить только макросом, причем не записью макроса, а вручную, путем ввода кода. Потому что записать можно определенные действия, но алгоритм «создай ссылку на каждый лист в книге» невозможно записать: тут присутствуют цикл и неизвестное заранее число листов.
Хотя, как правило, большинству хватает встроенных инструментов и функций для решения рабочих задач (тем более когда есть Power Query), но иногда могут возникнуть задачи вроде такой, для которой придется писать (или искать в Сети, на форумах готовый) макрос.
Следующий код создает новый лист с именем «Оглавление» и добавляет в первом столбце ссылки на все остальные листы в книге.

 

Файл с макросом: Оглавление. bas

 

Sub Оглавление()
Dim Contents As Worksheet
Set Contents = ActiveWorkbook.Worksheets.Add(before:=ActiveWorkbook.Worksheets(1))
Contents.Range("A1") = "Оглавление"
Contents.Name = "Оглавление"
For i = 2 To ActiveWorkbook.Worksheets.Count
Contents.Hyperlinks.Add Anchor:=Cells(i, 1), Address:="", SubAddress:="'" & ActiveWorkbook.Worksheets(i).Name & "'!A1", TextToDisplay:=ActiveWorkbook.Worksheets(i).Name
Next i
Contents.Columns(1).EntireColumn.AutoFit
End Sub

 

Его можно добавить в личную книгу макросов, чтобы он был доступен у вас во всех книгах Excel, а потом на панель быстрого доступа, чтобы оглавление создавалось по нажатию кнопки.
Личная книга — это книга, которая открывается в Excel всегда, она нужна, чтобы хранить те макросы, которые могут понадобиться вам в любой момент при работе с любой книгой, а не с отдельным файлом. Чтобы она у вас появилась, достаточно начать запись макроса (можно будет ее сразу закончить) и выбрать в списке «Сохранить в» соответствующий пункт.

 

 

После этого будет создана Личная книга макросов — вы увидите ее в редакторе Visual Basic (его можно открыть сочетанием Alt + F11).

 

 

Назад: Из Excel в Google Таблицы и обратно
Дальше: Заключение