Одной из наиболее мощных, но редко используемых функций Excel является возможность очень легко создавать автоматизированные задачи и настраиваемую логику в макросах. Макросы предоставляют идеальный способ сэкономить время на предсказуемых, повторяющихся задачах, а также стандартизировать форматы документов – много раз без необходимости писать одну строку кода.
Если вам интересно, что такое макросы или как на самом деле их создавать, не проблема – мы проведем вас через весь процесс.
Примечание. один и тот же процесс должен работать в большинстве версий Microsoft Office. Скриншоты могут выглядеть немного иначе.
Что такое макрос?
Макрос Microsoft Office (поскольку эта функция применяется к нескольким приложениям MS Office) – это просто код Visual Basic для приложений (VBA), сохраненный внутри документа. Для сопоставимой аналогии представьте документ как HTML, а макрос – как Javascript. Почти так же, как Javascript может манипулировать HTML на веб-странице, макрос может манипулировать документом.
Макросы невероятно мощные и могут делать практически все, что может придумать ваше воображение. В качестве (очень) короткого списка функций, которые вы можете сделать с помощью макроса:
- Примените стиль и форматирование.
- Манипулировать данными и текстом.
- Связь с источниками данных (база данных, текстовые файлы и т. Д.).
- Создавайте совершенно новые документы.
- Любая комбинация, в любом порядке, любого из вышеперечисленного.
Создание макроса: объяснение на примере
Мы начнем с вашего CSV файла. Здесь нет ничего особенного, просто набор чисел от 10 до 20 в диапазоне от 0 до 100 с заголовком строки и столбца. Наша цель – создать хорошо отформатированный, презентабельный лист данных, который включает итоговые итоги для каждой строки.
Как мы уже говорили выше, макрос – это код VBA, но одна из приятных сторон Excel состоит в том, что вы можете создавать/записывать их без нужды в кодировании – как мы это сделаем здесь.
Чтобы создать макрос, перейдите в «Просмотр»> «Макросы»> «Записать макрос».
Назначьте макросу имя (без пробелов) и нажмите OK.
Как только это будет сделано, все ваших действий будут записаны – каждое изменение ячейки, действие прокрутки, изменение размера окна, вы называете это.
Есть несколько мест, которые указывают, что Excel является режимом записи. Один из них – просмотреть меню «Макрос» и отметить, что «Остановить запись» заменил параметр «Запись макроса».
Другой в правом нижнем углу. Значок «Стоп» указывает, что он находится в режиме макросъемки, и нажатие здесь остановит запись (аналогично, когда он не находится в режиме записи, этот значок будет кнопкой записи макроса, которую можно использовать вместо перехода в меню «Макросы»).
Теперь, когда мы записываем наш макрос, давайте применим наши сводные вычисления. Сначала добавьте заголовки.
Далее примените соответствующие формулы (соответственно):
- = СУММ (В2: К2)
- = СРЗНАЧ (В2: К2)
- = MIN (В2: К2)
- = MAX (В2: К2)
- = Медиана (В2: К2)
Теперь выделите все ячейки вычислений и перетащите длину всех наших строк данных, чтобы применить вычисления к каждой строке.
После того, как это будет сделано, в каждой строке должны отображаться соответствующие сводки.
Теперь мы хотим получить сводные данные для всего листа, поэтому мы применим еще несколько расчетов:
Соответственно:
- = СУММ (L2: L21)
- = СРЕДНИЙ (B2: K21) * Это должно быть рассчитано для всех данных, поскольку среднее значение для средних по строке не обязательно равно среднему значению для всех значений.
- = MIN (N2: N21)
- = MAX (О2: O21)
- = MEDIAN (B2: K21) * Рассчитано для всех данных по той же причине, что и выше.
Теперь, когда вычисления выполнены, мы применим стиль и форматирование. Сначала примените общее форматирование чисел ко всем ячейкам, выполнив команду «Выделить все» (либо 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 с TutoryBird
