Узнайте, как использовать макросы Excel для автоматизации утомительных задач

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

Если вам интересно, что такое макросы или как их создавать, не проблема - мы проведем вас через весь процесс.

Замечания: тот же процесс должен работать в большинстве версий Microsoft Office. Скриншоты могут выглядеть немного иначе.

Что такое макрос?

Макрос Microsoft Office (поскольку эта функция применяется к нескольким приложениям MS Office) - это просто код Visual Basic для приложений (VBA), сохраненный внутри документа. Для сопоставимой аналогии представьте документ как HTML, а макрос - как Javascript. Почти так же, как Javascript может манипулировать HTML на веб-странице, макрос может манипулировать документом..

Макросы невероятно мощные и могут делать практически все, что может придумать ваше воображение. В качестве (очень) краткого списка функций, которые вы можете сделать с помощью макроса:

Применить стиль и форматирование.
Манипулировать данными и текстом.
Связь с источниками данных (база данных, текстовые файлы и т. Д.).
Создать совершенно новые документы.
Любая комбинация в любом порядке любого из вышеперечисленного.

Создание макроса: объяснение на примере

Начнем с вашего CSV файла сорта. Здесь нет ничего особенного, просто набор чисел от 10 до 20 от 0 до 100 с заголовком строки и столбца. Наша цель - создать хорошо отформатированный, презентабельный лист данных, который включает итоговые итоги для каждой строки..

Как мы уже говорили выше, макрос - это код VBA, но одна из приятных сторон Excel - вы можете создавать / записывать их без нужды в кодировании - как мы это сделаем здесь.

Чтобы создать макрос, перейдите в «Просмотр»> «Макросы»> «Запись макроса»..

Назначьте макросу имя (без пробелов) и нажмите ОК.

Как только это будет сделано, все ваши действия записываются - каждое изменение ячейки, действие прокрутки, изменение размера окна, вы называете это.

Есть несколько мест, которые указывают, что Excel является режимом записи. Одним из них является просмотр меню «Макрос» и замечание, что «Остановить запись» заменил параметр «Запись макроса».

Другой в правом нижнем углу. Значок «Стоп» указывает, что он находится в режиме макросъемки, и нажатие здесь остановит запись (аналогично, когда он не находится в режиме записи, этот значок будет кнопкой «Запись макроса», которую можно использовать вместо перехода в меню «Макросы»)..

Теперь, когда мы записываем наш макрос, давайте применим наши сводные вычисления. Сначала добавьте заголовки.

Далее примените соответствующие формулы (соответственно):

= СУММ (В2: К2)
= СРЗНАЧ (В2: К2)
= MIN (В2: К2)
= MAX (В2: К2)
= Медиана (В2: К2)

Теперь выделите все ячейки вычислений и перетащите длину всех наших строк данных, чтобы применить вычисления к каждой строке..

Как только это будет сделано, каждая строка должна отображать свои соответствующие резюме.

Теперь мы хотим получить сводные данные для всего листа, поэтому мы применим еще несколько расчетов:

Соответственно:

= СУММ (L2: L21)
= СРЕДНИЙ (B2: K21) *Это должно быть рассчитано для всех данных, потому что среднее значение для средних значений по строке не обязательно равно среднему значению для всех значений..
= MIN (N2: N21)
= MAX (О2: O21)
= Медиана (В2: К21) * Рассчитано по всем данным по той же причине, что и выше.

Теперь, когда расчеты выполнены, мы применим стиль и форматирование. Сначала примените общее форматирование чисел ко всем ячейкам, выполнив команду «Выбрать все» (либо Ctrl + A, либо щелкните ячейку между заголовком строки и столбца) и выберите значок «Стиль запятой» в главном меню..

Затем примените некоторое визуальное форматирование к заголовкам строк и столбцов:

Смелый.
Сосредоточенный.
Цвет заливки фона.

И, наконец, примените некоторый стиль к итогам.

Когда все закончено, вот как выглядит наш лист данных:

Поскольку мы удовлетворены результатами, остановите запись макроса.

Поздравляем - вы только что создали макрос Excel.

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

Для этого выделите все ячейки и удалите их.

Теперь, когда данные очищены (но макросы все еще включены в файл Excel), мы хотим сохранить файл как файл шаблона с поддержкой макросов (XLTM). Важно отметить, что если вы сохраните его как файл стандартного шаблона (XLTX), то макросы будут не быть в состоянии убежать от этого. Кроме того, вы можете сохранить файл как файл устаревшего шаблона (XLT), что позволит запускать макросы.

Как только вы сохранили файл в качестве шаблона, закройте Excel.

Использование макроса Excel

Прежде чем рассказать о том, как мы можем применить этот недавно записанный макрос, важно рассказать о нескольких макросах в целом:

Макросы могут быть вредоносными.
Смотрите пункт выше.

Код VBA на самом деле довольно мощный и может манипулировать файлами за пределами области действия текущего документа. Например, макрос может изменить или удалить случайные файлы в папке «Мои документы». Поэтому важно убедиться, что вы только запускать макросы из надежных источников.

Чтобы использовать наш макрос формата данных, откройте файл шаблона Excel, который был создан выше. Когда вы сделаете это, при условии, что у вас включены стандартные настройки безопасности, вы увидите предупреждение в верхней части книги, в котором говорится, что макросы отключены. Поскольку мы доверяем макросу, созданному нами, нажмите кнопку «Включить содержимое».

Далее мы собираемся импортировать самый последний набор данных из CSV (это источник рабочего листа, используемого для создания нашего макроса).

Чтобы завершить импорт файла CSV, вам может потребоваться установить несколько параметров, чтобы Excel мог правильно его интерпретировать (например, разделитель, заголовки и т. Д.).

После импорта наших данных просто перейдите в меню «Макросы» (на вкладке «Вид») и выберите «Просмотр макросов»..

В появившемся диалоговом окне мы видим макрос «FormatData», который мы записали выше. Выберите его и нажмите Run.

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

Взгляд под капотом: что делает макрос

Как мы уже упоминали пару раз, макрос управляется кодом Visual Basic для приложений (VBA). Когда вы «записываете» макрос, Excel фактически переводит все, что вы делаете, в соответствующие инструкции VBA. Проще говоря - вам не нужно писать код, потому что Excel пишет код для вас.

Чтобы просмотреть код, который запускает наш макрос, в диалоговом окне «Макросы» нажмите кнопку «Редактировать»..

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

Взяв наш пример на шаг дальше ...

Гипотетически, предположим, что наш исходный файл данных, data.csv, создается автоматическим процессом, который всегда сохраняет файл в одном и том же месте (например, C: \ Data \ data.csv всегда самые последние данные). Процесс открытия и импорта этого файла также может быть легко преобразован в макрос:

Откройте файл шаблона Excel, содержащий наш макрос «FormatData».
Запишите новый макрос с именем «LoadData».
При записи макроса импортируйте файл данных, как обычно.
Как только данные импортированы, остановите запись макроса.
Удалить все данные ячейки (выбрать все, затем удалить).
Сохраните обновленный шаблон (не забудьте использовать макрос с включенным форматом шаблона).

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

Если вы действительно хотите испачкать руки небольшим редактированием кода, вы можете легко объединить эти действия в один макрос, скопировав код, полученный из «LoadData», и вставив его в начале кода из «FormatData»..

Скачать этот шаблон

Для вашего удобства мы включили как шаблон Excel, созданный в этой статье, так и образец файла данных, с которым вы можете поиграть.

Скачать шаблон макроса Excel с How-To Geek