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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  • = СУММ (B2: K2)
  • = СРЕДНИЙ (B2: K2)
  • = МИН (B2: K2)
  • = МАКС (B2: K2)
  • = МЕДИАНА (B2: K2)

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

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

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

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

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

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

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

  • Смелый.
  • По центру.
  • Цвет заливки фона.

И наконец, примените стиль к итоговым значениям.

Когда все будет готово, вот как выглядит наша таблица данных:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Развивая наш пример на шаг впереди…

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

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

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

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

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

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

Загрузите шаблон макроса Excel из How-To Geek