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

2024 Автор: Geoffrey Carr | [email protected]. Последнее изменение: 2023-12-17 10:57

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

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

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

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

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

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

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

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

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

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

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

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

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

Есть несколько мест, которые указывают, что Excel - это режим записи. Один из них - просмотр меню Macro и запись, что Stop Recording заменила параметр Record Macro.

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

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

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

= СУММ (В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», который мы записали выше. Выберите его и нажмите «Выполнить».

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

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

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

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

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

Принимая наш пример на шаг дальше …

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

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

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