Содержание:
Excel имеет мощную, но в то же время очень редко используемую возможность создавать автоматические последовательности действий с помощью макросов. Макрос — идеальный выход, если вы имеете дело с однотипной задачей, повторяющейся много раз. Например, обработка данных или форматирование документа по стандартизированному шаблону. В этом случае вам не потребуется знание языков программирования.
Вам уже интересно, что такое макрос и как он работает? Тогда смело вперед – дальше мы пошагово проделаем с вами весь процесс создания макроса.
Что такое макрос?
Макрос в Microsoft Office (да, этот функционал работает одинаково во многих приложениях пакета Microsoft Office) — это программный код на языке программирования. Visual Basic для приложений (VBA), хранящиеся внутри документа. Чтобы было понятнее, документ Microsoft Office можно сравнить с HTML-страницей, тогда макрос — это аналог Javascript. То, что Javascript может делать с данными HTML на веб-странице, очень похоже на то, что макрос может делать с данными в документе Microsoft Office.
Макросы могут делать с документом практически все, что вы захотите. Вот некоторые из них (очень маленькая часть):
- Примените стили и форматирование.
- Выполнять различные операции с числовыми и текстовыми данными.
- Используйте внешние источники данных (файлы баз данных, текстовые документы и т. д.)
- Создайте новый документ.
- Делайте все вышеперечисленное в любой комбинации.
Создание макроса – практический пример
Для примера возьмем самый обычный файл CSV. Это простая таблица размером 10×20, заполненная числами от 0 до 100 с заголовками столбцов и строк. Наша задача — превратить этот набор данных в презентабельно отформатированную таблицу и сгенерировать итоги в каждой строке.
Как уже говорилось, макрос — это код, написанный на языке программирования VBA. Но в Excel можно создать программу, не написав ни строчки кода, чем мы и займемся прямо сейчас.
Чтобы создать макрос, откройте Вид (Тип) > Макрос (Макро) > Запись макроса (Запись макроса…)
Дайте вашему макросу имя (без пробелов) и нажмите OK.
Начиная с этого момента, фиксируются ВСЕ ваши действия с документом: изменения ячеек, пролистывание таблицы, даже изменение размера окна.
Excel сигнализирует о том, что режим записи макросов включен в двух местах. Сначала в меню Макрос (Макросы) – вместо строки Запись макроса (Запись макроса…) появилась строка Остановить запись (Остановить запись).
Во-вторых, в левом нижнем углу окна Excel. Икона Stop (маленький квадрат) означает, что режим записи макросов включен. Нажатие на нее остановит запись. И наоборот, если режим записи не включен, в этом месте отображается значок включения записи макроса. Нажатие на нее даст тот же результат, что и включение записи через меню.
Теперь, когда режим записи макросов включен, приступим к нашей задаче. Прежде всего, добавим заголовки для сводных данных.
Далее введите формулы в ячейки в соответствии с названиями заголовков (приведены варианты формул для английской и версии Excel, адреса ячеек всегда латинские буквы и цифры):
- =СУММ(В2:К2) or =СУММ(В2:К2)
- =СРЕДНЕЕ(B2:K2) or =СРЗНАЧ(B2:K2)
- =МИН(В2:К2) or =МИН(В2:К2)
- =МАКС(В2:К2) or =МАКС(В2:К2)
- =МЕДИАНА(B2:K2) or =МЕДИАНА(B2:K2)
Теперь выделите ячейки с формулами и скопируйте их во все строки нашей таблицы, перетащив маркер автозаполнения.
После выполнения этого шага в каждой строке должны быть соответствующие итоги.
Далее суммируем результаты по всей таблице, для этого проделаем еще несколько математических операций:
Соответственно:
- =СУММ(L2:L21) or =СУММ(L2:L21)
- =СРЕДНЕЕ(B2:K21) or =СРЗНАЧ(B2:K21) – для расчета этой величины необходимо взять именно исходные данные таблицы. Если вы возьмете среднее значение средних значений для отдельных строк, результат будет другим.
- =МИН(N2:N21) or =МИН(N2:N21)
- =МАКС(О2:О21) or =МАКС(О2:О21)
- =МЕДИАНА(B2:K21) or =МЕДИАНА(B2:K21) – рассматриваем возможность использования исходных данных таблицы по причине, указанной выше.
Теперь, когда мы закончили с вычислениями, давайте займемся форматированием. Для начала зададим одинаковый формат отображения данных для всех ячеек. Выделите все ячейки на листе, для этого используйте сочетание клавиш Ctrl +или нажмите на значок Выбрать все, который находится на пересечении заголовков строки и столбца. Затем нажмите Стиль запятой Вкладка (Формат с разделителями) Главная (Домой).
Затем измените внешний вид заголовков столбцов и строк:
- Жирный стиль шрифта.
- Выравнивание по центру.
- Цветовая заливка.
И наконец, настроим формат итогов.
Вот как это должно выглядеть в конечном итоге:
Если вас все устраивает, прекращайте запись макроса.
Поздравляем! Вы только что самостоятельно записали свой первый макрос в Excel.
Чтобы использовать сгенерированный макрос, нам необходимо сохранить документ Excel в формате, поддерживающем макросы. Для начала нам необходимо удалить все данные из созданной нами таблицы, т.е. сделать ее пустым шаблоном. Дело в том, что в дальнейшем, работая с этим шаблоном, мы будем импортировать в него самые свежие и актуальные данные.
Чтобы очистить все ячейки данных, щелкните правой кнопкой мыши значок Выбрать все, который находится на пересечении заголовков строки и столбца, и в контекстном меню выберите Удалить (Удалить).
Теперь наш лист полностью очищен от всех данных, а макрос остается записанным. Нам нужно сохранить книгу как шаблон Excel с поддержкой макросов и расширением XLTM.
Важный момент! Если сохранить файл с расширением XLTX, то макрос в нем работать не будет. Кстати, книгу можно сохранить как шаблон Excel 97-2003, имеющий формат XLT, он также поддерживает макросы.
Когда шаблон будет сохранен, вы можете безопасно закрыть Excel.
Запуск макроса в Excel
Прежде чем раскрыть все возможности созданного вами макроса, считаю правильным обратить внимание на пару важных моментов, касающихся макросов в целом:
- Макросы могут быть вредными.
- Прочтите предыдущий абзац еще раз.
Код VBA очень мощный. В частности, он может выполнять операции с файлами за пределами текущего документа. Например, макрос может удалять или изменять любые файлы в папке. Мои документы. По этой причине запускайте и разрешайте макросы только из источников, которым вы доверяете.
Чтобы запустить наш макрос форматирования данных, откройте файл шаблона, который мы создали в первой части этого руководства. Если у вас стандартные настройки безопасности, то при открытии файла над таблицей появится предупреждение о том, что макросы отключены, и кнопка для их включения. Так как шаблон мы сделали сами и доверяем себе, то нажимаем кнопку Включить контент (Включить контент).
Следующим шагом будет импорт последнего обновленного набора данных из файла. CSV (на основе такого файла мы создали наш макрос).
Когда вы импортируете данные из файла CSV, Excel может попросить вас настроить некоторые параметры, чтобы правильно перенести данные в таблицу.
Когда импорт завершится, зайдите в меню Макрос Вкладка (Макросы) Вид (Просмотр) и выберите команду Макросы просмотра (Макро).
В открывшемся диалоговом окне мы увидим строку с названием нашего макроса ФорматДанные, Выберите его и нажмите Run (Выполнять).
Когда макрос запустится, вы увидите, как курсор таблицы прыгает от ячейки к ячейке. Через несколько секунд с данными будут произведены те же операции, что и при записи макроса. Когда все будет готово, таблица должна выглядеть так же, как оригинал, который мы форматировали вручную, только с другими данными в ячейках.
Давайте заглянем под капот: как работает макрос?
Как уже не раз говорилось, макрос – это программный код на языке программирования. Visual Basic для приложений (ВБА). Когда вы включаете режим записи макросов, Excel фактически записывает каждое ваше действие в виде инструкций VBA. Проще говоря, Excel пишет код за вас.
Чтобы увидеть этот программный код, нужно в меню Макрос Вкладка (Макросы) Вид (просмотреть) нажмите Макросы просмотра (Макросы) и в открывшемся диалоговом окне нажмите кнопку Редактировать (Изменять).
Откроется окно. Visual Basic для приложений, в котором мы увидим программный код записанного нами макроса. Да, вы правильно поняли, здесь вы можете изменить этот код и даже создать новый макрос. Действия, которые мы производили с таблицей в этом уроке, можно записать с помощью автоматической записи макросов в Excel. Но более сложные макросы с точно настроенной последовательностью и логикой действий требуют ручного программирования.
Добавим к нашей задаче еще один шаг…
Представьте, что наш исходный файл данных данные.csv создается автоматически каким-либо процессом и всегда хранится на диске в одном и том же месте. Например, C:Datadata.csv – путь к файлу с обновленными данными. Процесс открытия этого файла и импорта из него данных также можно записать в макросе:
- Откройте файл шаблона, в котором мы сохранили макрос – ФорматДанные.
- Создайте новый макрос с именем Загрузить данные.
- Во время записи макроса Загрузить данные импортировать данные из файла данные.csv – как мы это делали в предыдущей части урока.
- Когда импорт завершится, прекратите запись макроса.
- Удалить все данные из ячеек.
- Сохраните файл как шаблон Excel с поддержкой макросов (расширение XLTM).
Таким образом, запустив этот шаблон, вы получаете доступ к двум макросам – один загружает данные, другой их форматирует.
Если вы хотите заняться программированием, вы можете объединить действия этих двух макросов в один — просто скопировав код из Загрузить данные в начало кода ФорматДанные.