Учебное пособие по макросам Excel: как записывать и создавать собственные макросы Excel

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

Вероятно, вы уже используете такие функции, как = sum (A1: A5 ) , простые фрагменты текста, которые складывают, усредняют и вычисляют ваши значения. Именно они делают электронные таблицы мощным инструментом для обработки чисел и текста. Следующий шаг — макросы: это инструменты, которые автоматизируют простые задачи и помогают делать больше за меньшее время. Вот как разблокировать эту новую часть ваших навыков работы с Excel, создав свои собственные макросы в Excel.

Впервые работали с электронными таблицами? Начните сначала с нашего руководства по электронной таблице 101 — оно знакомит вас с основными функциями электронных таблиц, которые помогут вам начать работу с любым приложением для работы с электронными таблицами: Google Таблицы, Excel или любой другой инструмент для работы с электронными таблицами.

Этот пост был первоначально опубликован в августе 2016 года, но был дополнен дополнительными советами


  • Что такое макросы Excel?

  • Почему макросы?

  • Как создать свой первый макрос Excel

  • Как кодировать свои собственные макросы Excel

  • Подключите Excel к другим приложениям с помощью интеграции


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

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

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

Чтобы создать эти макросы, вы можете просто записать свои действия в Excel, чтобы сохранить их как повторяемые шаги, или вы можете использовать Visual Basic для приложений (VBA ), простой язык программирования, встроенный в Microsoft Office. Мы покажем вам, как использовать и то, и другое ниже, а также поделимся примерами макросов Excel, которые помогут вам начать работу.

Совет: Это руководство и все примеры написаны в Excel 2016 для Windows, но принципы применимы к Excel 2007 и новее как для Mac, так и для ПК.

Зачем использовать макросы Excel?

Изучение автоматизации Excel — один из самых простых способов ускорить вашу работу, особенно потому, что Excel используется во многих рабочих процессах. Скажем, каждую неделю вы экспортируете данные аналитики из своей системы управления контентом (CMS) для создания отчета о своем сайте.. Единственная проблема в том, что эти экспортируемые данные не всегда в формате, удобном для Excel. Они беспорядочные и часто содержат гораздо больше данных, чем требуется в вашем отчете. Это означает, что вам нужно очистить пустые строки, скопировать и вставить данные в нужное место и создать свои собственные диаграммы для визуализации данных и сделать их удобными для печати. Все эти шаги могут занять у вас часы.

Если бы был только способ нажать одну кнопку и позволить Excel сделать это за вас в одно мгновение … Ну, вы можете догадаться, кто я собираетесь сказать следующее?

Есть!

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

Как создать свой первый макрос Excel

Вы уже разбираетесь в Excel , и знакомы с его сеткой ячеек, в которую вы вводите свой текст и функции. Однако для создания макросов Excel вам понадобится дополнительный инструмент, встроенный в Excel: редактор Visual Basic.

Прежде чем приступить: Обязательно загрузите файл нашего проекта — он понадобится вам в дальнейшем, чтобы следовать нашему руководству по макросам Excel.

Знакомьтесь с редактором VBA

Excel имеет встроенный инструмент для написания макросов, называемый редактором Visual Basic, или для краткости редактором VBA. Чтобы открыть это, откройте электронную таблицу и используйте сочетание клавиш Alt + F11 (для Mac: Fn + Shift + F11 ).

Новое всплывающее окно называется редактором VBA. Здесь вы будете редактировать и хранить все свои макросы. Его расположение может немного отличаться от этого снимка экрана, но вы можете перемещать окна в нужном вам порядке. Просто убедитесь, что панель Project Explorer открыта, чтобы вы могли легко редактировать свои макросы.

Ваши макросы будут состоять из «модулей» или файлов с ваш код VBA. Вы добавите новый модуль или откроете существующий в редакторе VBA, а затем введите нужный код. Чтобы вставить модуль, нажмите «Вставить», а затем «Модуль». Затем вы увидите пустое место для написания кода справа.

Как записать макрос Excel

Там Есть два способа создать макрос: закодировать или записать. Основное внимание в этой статье уделяется первому, но запись макроса настолько проста и удобна, что ее тоже стоит изучить. Запись макроса — хороший способ познакомиться с основами VBA. Позже он служит удобным хранилищем для кода, который вам не нужно запоминать.

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

На это есть ограничения, поэтому вы не можете автоматизировать каждую задачу или станьте экспертом в автоматизации, только записывая. Иногда вам все равно придется вводить или редактировать код вручную. Но это все еще удобный способ начать работу. Вот как: 1. Перейдите на вкладку «Просмотр» на ленте и щелкните крошечную стрелку под кнопкой «Макросы». 2. Затем нажмите «Записать макрос». 3. Введите имя вашего макроса и нажмите «ОК», чтобы начать запись. 4. Выполните действия в вашей электронной таблице, которые вы хотите превратить в макрос. 5. Когда вы закончите, перейдите на вкладку «Просмотр», снова щелкните маленькую стрелку под кнопкой «Записать макрос» и выберите «Остановить запись».

Теперь используйте сочетание клавиш Alt + F11 (для Mac: Fn + Shift + F11 ), чтобы открыть редактор VBA, и дважды щелкните » Модуль 1 «в Project Explorer.

Это ваш первый код! Удивительно, правда? Возможно, вы не написали его сами, но он все равно создается вашими действиями.

Ваш, вероятно, отличается от моего. Вы можете догадаться, что делает мой код?

  • Sub Makebold — это просто текст Sub , за которым следует имя, которое я ввел, когда начал запись.

  • Зеленая линия на самом деле ничего не делает — это комментарий, в который вы можете добавить объяснение того, что делает макрос.

  • Selection.Font.Bold = True делает значения в выбранных ячейках bold .

  • End sub просто сообщает Excel, что макрос здесь останавливается.

Что произойдет, если я изменю часть True в третьей строке на False ? Затем макрос удалит любое выделение жирным шрифтом из выделения вместо того, чтобы делать его жирным.

Вот как вы записываете простой макрос. Но настоящая сила макросов приходит тогда, когда вы можете написать их самостоятельно, поэтому давайте начнем учиться писать простой код VBA.

Как кодировать свои собственные макросы Excel

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

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

Добавить кнопку для запуска макроса

Вы можете использовать различные объекты Excel в качестве кнопок для запуска макросов, но я предпочитаю использовать фигуру из вкладки «Вставка». Когда вы вставили свою фигуру, щелкните ее правой кнопкой мыши и выберите «Назначить макрос…» Затем выберите макрос, который вы хотите запускать при щелчке фигуры — возможно, тот, который вы только что создали с записью, и сохраните его, нажав «ОК».

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

Прежде чем мы начнем, следует отметить еще одну вещь: сохранение вашей электронной таблицы с помощью макросов.. По умолчанию файлы электронных таблиц Excel с расширением .xlsx не могут содержать макросы. Вместо этого при сохранении электронной таблицы выберите формат «Excel Macro-Enabled Workbook ( * . Xlsm)» и добавьте имя файла как обычно.

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


Теперь давайте началось с кодирования!

Копирование и вставка — это самый простой способ перемещения данных, но он по-прежнему утомителен. Что, если бы ваша таблица могла сделать это за вас? С макросом это возможно. Давайте посмотрим, как создать макрос, который будет копировать данные и перемещать их в электронной таблице.

Откройте файл проекта, который вы загрузили ранее, и убедитесь, что выбран лист «Копировать, вырезать и вставить» . Это образец базы данных сотрудников с именами, отделами и заработной платой некоторых сотрудников.

Давайте попробуем скопировать все данные из столбцов с A по C в D по F с помощью VBA. Во-первых, давайте посмотрим на код, который нам нужен:

Копирование ячеек с помощью VBA

Копирование в VBA довольно просто. Просто вставьте этот код в редактор VBA: Range («Вставьте здесь диапазон»). Скопируйте . Вот несколько примеров:

  • Range ("A: C"). Копировать ← копирует столбцы с A по C

  • Диапазон ("A1: C100"). Копировать ← копирует диапазон A1: C100

Помните, когда вы записывали макрос раньше? Макрос имел Sub Nameofmacro () и End sub в верхней и нижней строке кода. Эти строки всегда должны быть включены. В Excel это тоже упрощается: когда вы вводите «Sub», а затем имя макроса в начале кода, End sub автоматически вставляется в нижнюю строку.

Совет: не забудьте ввести эти строки вручную, когда вы не используете средство записи макросов.

Вставка ячеек с помощью VBA

Вставка может выполняться разными способами в зависимости от того, что вы хотите вставить. В 99% случаев вам понадобится одна из этих двух строк кода:

  • Range ("Ячейка/область, в которой вы хотите вставить "). Специальная вставка ← вставляет как обычно (формулы и форматирование)

  • Range (" Ячейка/ область, в которую вы хотите вставить »). Pastespecial xlPasteValues ​​ ← вставляет только значения

Вырезание ячеек с помощью VBA

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

Вот код: Range («Вставить здесь диапазон»). Вырезать

При вырезании нельзя использовать команду «Специальная вставка». Это означает, что нельзя вставлять только значения или только форматирование. Следовательно, вам нужны эти строки, чтобы вставить ячейки с помощью VBA: Range («Вставьте то место, которое вы хотите вставить»). Выберите ActiveSheet.. Вставить

Например, вот код, который вам нужно вырезать из диапазона A: C и вставить его в D1 :

  • Диапазон ("A: C"). Вырезать

  • Диапазон ("D1"). Выберите

  • ActiveSheet.Paste


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

Добавление циклов в VBA

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

Взгляните на лист «Циклы» в файле проекта. Это те же данные, что и на предыдущем листе, но теперь каждая третья строка данных перемещена на один столбец вправо. Этот тип ошибочной структуры данных не является необычным при экспорте данных из старых программ.

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

Давайте создадим цикл, который исправит это за вас. Введите этот код в модуль и посмотрите пояснения под изображением:

  1. Эта строка гарантирует, что цикл начинается в верхней левой ячейке листа и не случайно портит данные, начиная с другого места.

  2. For i = 1 Строка To 500 означает, что количество запусков цикла (представленное i ) является возрастающим числом, которое начинается с 1 и заканчивается на 500. Это означает, что цикл будет пробежать 500 раз. Количество запусков цикла зависит от того, какие действия вы хотите выполнить. Используйте здесь свой здравый смысл. 500 раз — это слишком много для нашего образца набора данных, но они идеально подошли бы, если бы в базе данных было 1500 строк данных.

  3. Эта строка распознает активную ячейку и сообщает Excel, чтобы переместиться на 3 строки вниз и выбрать эту ячейку, которая затем станет новой активной ячейкой. Если бы в наших данных была потеряна каждая четвертая строка, а не каждая третья, мы могли бы просто заменить 3 на 4 в этой строке.

  4. Это строка сообщает Excel, что делать с этой недавно выбранной ячейкой. В этом случае мы хотим удалить ячейку таким образом, чтобы ячейки справа от ячейки сдвинулись влево. Это достигается с помощью этой линии. Если бы мы хотели сделать что-то еще с неуместными строками, то это самое подходящее место. Если бы мы хотели полностью удалить каждую третью строку, она должна была быть такой: Selection.Entirerow.delete .

  5. Эта строка сообщает Excel, что в цикле больше нет действий. В этом случае 2 и 5 — это кадр цикла, а 3 и 4 — действия внутри цикла.

Когда мы запустим этот макрос, он приведет к в аккуратном наборе данных без каких-либо неуместных строк.

Добавление логики в VBA

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

Этот раздел посвящен операторам IF, которые включают логику «если-то-то-то», точно так же, как функция ЕСЛИ в Excel.

Допустим, экспорт с CMS нашего веб-сайта был еще более ошибочным, чем ожидалось. Каждая третья строка по-прежнему неуместна, но теперь некоторые из неуместных строк размещаются на 2 столбца справа, а не на 1 столбец справа. Взгляните на лист «IF-statement» в файле проекта, чтобы увидеть, как он выглядит.

Как это учесть в нашем макросе? Мы добавляем в цикл IF-оператор!

Давайте сформулируем, что мы хотим, чтобы Excel делал:

Мы начинаем с ячейки A1. Затем мы переходим на три строки вниз (до ячейки A4, A7, A10 и т. Д.), Пока не кончатся данные. Каждый раз, когда мы опускаемся на три строки вниз, мы проверяем эту строку, чтобы увидеть, не смещены ли данные на 1 или 2 столбца. Затем переместите данные в строке на 1 или 2 столбца влево.

Теперь давайте переведем это в код VBA. Мы начнем с простого цикла, как и раньше:

Единственное, что нам сейчас нужно, это написать, что должно происходить внутри цикла. Это часть «на три ряда вниз», которую мы разработали еще в разделе о петлях. Теперь мы добавляем IF-оператор, который проверяет, насколько данные неуместны, и исправляет это соответственно.

Это последний код, который нужно скопировать в редактор вашего модуля, каждый шаг которого описан ниже:

  1. Это первая часть IF-оператора. В нем говорится, что if ячейка справа от активной ячейки (или Activecell.Offset (0,1) в коде VBA) пуста (представлена ​​ = "" ) затем сделайте что-нибудь. Это то же самое действие, что и при создании цикла в первую очередь: удаление активной ячейки и перемещение активной строки на одну ячейку влево (выполняется с помощью Selection.Delete Shift: = xlToLeft код). На этот раз мы делаем это два раза вместо одного, потому что в левой части строки есть две пустые ячейки.

  2. Если указанное выше неверно , и ячейка справа от активной ячейки не пуста, тогда активная ячейка пуста. Следовательно, нам нужно всего лишь удалить активную ячейку и один раз переместить активную строку на одну ячейку влево.

IF-оператор всегда должен заканчиваться End If , чтобы сообщить Excel о завершении работы. После IF-оператора цикл может выполняться снова и снова, каждый раз повторяя IF-оператор

Поздравляем, вы только что создали макрос, который может очищать беспорядочные данные! Посмотрите анимацию ниже, чтобы увидеть его в действии (если вы еще не пробовали это сами).

Автоматизируйте Excel Без макросов

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

Инструмент интеграции приложений Zapier может помочь. Он связывает версию Excel Office 365 для бизнеса с сотнями других приложений — Stripe, Salesforce, Slack и др. — так что вы можете автоматически записывать данные в электронную таблицу или запускать задачи в других приложениях прямо из Excel.

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

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

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

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

Управление данными ваших электронных таблиц

Сохранение записей формы в электронную таблицу Excel

Сохранение данных в электронной таблице Excel

Выполняйте работу из своей электронной таблицы


Создавайте собственные макросы!

Теперь вы узнали некоторые из наиболее важных инструментов VBA для создания макросов для очистки данных и автоматизация вашей работы. Поэкспериментируйте с уловками и инструментами, которые вы только что изучили, потому что они являются основой автоматизации в VBA. Не забывайте использовать макрорекордер (и Google), когда чувствуете, что находитесь над головой.

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

  • Подробнее о том, как повторять код VBA с циклами

  • Узнайте глубже, что можно делать с помощью операторов if и других логических переходов Макросы Excel

  • Изучите справочник Microsoft Excel VBA, чтобы узнать обо всем, что можно делать с кодом и макросами VBA.

  • Использовать Google Таблицы вместо Excel? Ознакомьтесь с нашим руководством по скрипту Google Apps, чтобы узнать, как автоматизировать Google Таблицы

Оцените статью
techsly.ru
Добавить комментарий