Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах)

В версиях 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 всегда, она нужна, чтобы хранить те макросы, которые могут понадобиться вам в любой момент при работе с любой книгой, а не с отдельным файлом. Чтобы она у вас появилась, достаточно начать запись макроса (можно будет ее сразу закончить) и выбрать в списке «Сохранить в» соответствующий пункт.