Как сделать макрос в экселе: Краткое руководство: создание макроса — Служба поддержки Office

Краткое руководство: создание макроса — Служба поддержки Office

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

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

Процедура

Дальнейшие действия

Процедура

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Макросы в Excel: создание, изменение, выполнение

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

Создание макросов

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

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

Метод 1: Записываем макрос в автоматическом режиме

Чтобы иметь возможность записать макросы автоматически, для начала нужно их активировать в параметрах Эксель.

Узнать, как это можно сделать, можно в нашей статье – “Как включать и отключать макросы в Excel”.

После того, как макросы включены, можно перейти к нашей основной задаче.

1. Переключаемся во вкладку “Разработчик”. В группе инструментов “Код” щелкаем по значку “Записать макрос”.
2. На экране появится окошко, в котором мы можем выполнить настройки записи. Здесь указывается:
   • имя макроса – любое, но должно начинаться с буквы (не с цифры), не допускаются пробелы.
   • комбинация клавиш, которая будет использоваться для запуска макроса. Обязательной является Ctrl, а вторую мы можем назначить в специальном поле. Допустим, пусть это будет клавиша “r”. Если нужно использовать заглавную букву, нужно зажать клавишу Shift, которая будет добавлена в сочетание клавиш.
   • место хранения макроса. Стандартно – это будет текущая книга, но, если требуется, можно выбрать новую книгу или личную книгу макросов. В нашем случае оставляем стандартный вариант.
   • При желании можно добавить описание к макросу.
   • По готовности кликаем OK.
3. Теперь в созданный макрос будут записываться все действия, которые мы будем выполнить в книге. И так до тех пор, пока мы не остановим запись.
4. Допустим, давайте давайте в ячейке D2 выполним умножение двух ячеек (B2 и B3): =B2*B3.
5. После того, как выражение готово, нажимаем клавишу Enter, чтобы получить результат. Затем останавливаем запись макроса, нажав на значок “Стоп” (появилась вместо кнопки “Записать макрос”) в группе инструментов “Код”.

Запускаем выполнение макроса

Чтобы проверить работу записанного макроса, нужно сделать следующее:

1. В той же вкладке (“Разработчик”) и группе “Код” нажимаем кнопку “Макросы” (также можно воспользоваться горячими клавишами Alt+F8).
2. В отобразившемся окошке выбираем наш макрос и жмем по команде “Выполнить”.Примечание: Есть более простой вариант запустить выполнение макроса – воспользоваться сочетанием клавиш, которое мы задали при создании макроса.
3. Результатом проверки будет повторение ранее выполненных (записанных) действий.

Корректируем макрос

Созданный макрос можно изменить. Самая распространенная причина, которая приводит к такой необходимости – сделанные при записи ошибки. Вот как можно отредактировать макрос:

1. Нажимаем кнопку “Макросы” (или комбинацию Ctrl+F8).
2. В появившемся окошке выбираем наш макрос и щелкаем “Изменить”.
3. На экране отобразится окно редактора “Microsoft Visual Basic”, в котором мы можем внести правки. Структура каждого макроса следующая:
   • открывается с команды “Sub”, закрывается – “End Sub”;
   • после “Sub” отображается имя макроса;
   • далее указано описание (если оно есть) и назначенная комбинация клавиш;
   • команда “Range(“…”).Select” возвращает номер ячейки. К примеру, “Range(“B2″).Select” отбирает ячейку B2.
   • В строке “ActiveCell.FormulaR1C1” указывается значение ячейки или действие в формуле.
4. Давайте попробуем скорректировать макрос, а именно, добавить в него ячейку B4 со значением 3. В код макроса нужно добавить следующие строки:
   Range("B4").Select
ActiveCell.FormulaR1C1 = "3"
5. Для результирующей ячейки D2, соответственно, тоже нужно изменить начальное выражение на следующее:
   ActiveCell.FormulaR1C1 = "=RC[-2]*R[1]C[-2]*R[2]C[-2]".Примечание: Обратите внимание, что адреса ячеек в данной строке (ActiveCell.FormulaR1C1) пишутся в стиле R1C1.
6. Когда все готово, редактор можно закрывать (просто щелкаем на крестик в правом верхнем углу окна).
7. Запускаем выполнение измененного макроса, после чего можем заметить, что в таблице появилась новая заполненная ячейка (B4 со значением “3”), а также, пересчитан результат с учетом измененной формулы.
8. Если мы имеем дело с большим макросом, на выполнение которого может потребоваться немало времени, ручное редактирование изменений поможет быстрее справиться с задачей.
   • Добавив в конце команду Application.ScreenUpdating = False мы можем ускорить работу, так как во время выполнения макроса, изменения на экране отображаться не будут.
   • Если потребуется снова вернуть отображение на экране, пишем команду: Application.ScreenUpdating = True.
9. Чтобы не нагружать программу пересчетом после каждого внесенного изменения, в самом начале пишем команду Application.Calculation = xlCalculationManual, а в конце – Application.Calculation = xlCalculationAutomatic. Теперь вычисление будет выполняться только один раз.

Метод 2: создание макроса вручную

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

1. Во вкладке “Разработчик” нажимаем на значком “Visual Basic” (группа инструментов “Код”).
2. В результате, на экране появится уже знакомое окно, которое мы рассмотрели выше.
3. Здесь создается макрос и пишется ее код – полностью вручную.

Заключение

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

Как написать макрос в Excel — создание, запись и редактирование VBA макросов в Excel

VBA считается стандартным языком написания сценариев для приложений Microsoft, и в настоящее время он входит в состав всех приложений Office и даже приложений других компаний. Следовательно, овладев VBA для Excel, вы сможете сразу перейти к созданию макросов для других программных продуктов Microsoft. Более того, вы сможете создавать полноценные программные продукты, одновременно использующие функции самых разных приложений.

Как включить макросы в Excel

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

На ленте появиться новая вкладка Разработчик с элементами управления автоматизации Excel.

Написание макросов в Excel

Во вкладке Разработчик в группе Код, нажмите кнопку Запись макроса. Появиться диалоговое окно Запись макроса, которая запрашивает некоторую информацию о будущем записываемом коде. Если вы впервые создаете макрос, можете просто нажать кнопку ОК. С данного момента Excel будет фиксировать каждое действие пользователя в модуле VBA, будь то ввод данных, форматирование или создание диаграмм. Чтобы остановить запись макроса, нажмите кнопку Остановить запись, которая находится в той же группе Код.

Вы также можете воспользоваться альтернативным вариантом записи макросов, воспользовавшись кнопкой Запись макроса, которая находится в левом нижнем углу рабочей книги Excel (правее статуса Готово).

Теперь вы можете посмотреть список всех созданных макросов, нажав на кнопку Макрос, находящуюся в группе Код. В появившемся диалоговом окне вы можете дать более описательные имена своим кодам или задать сочетания клавиш, которые бы запускали тот или иной макрос. Альтернативным вариантом запуска данного окна является нажатие клавиш Alt + F8.

Редактирование макросов

Поздравляю! Вы написали свой первый макрос. Логичным будет проверить теперь, какой же код сгенерировал нам Excel. Сгенерированный код написан на языке VBA (Visual Basic for Applications). Чтобы увидеть его, нужно открыть Редактор VB (VBE), который запускается нажатием клавиш Alt + F11 или кнопкой Visual Basic на вкладке Разработчик.

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

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

Для просмотра кода, щелкните по ветке Modules в окне проектов и дважды щелкните по появившейся ветке Module1. Редактор откроет окно с кодом, как изображено на картинке.

Здесь можно редактировать сгенерированный код, который был записан при работе в Excel. К примеру, вам требуется заполнить определенный столбец значениями от 1 до 10. У вас уже есть первые три шага, которые вводят значения 1, 2 и 3 в первые три ячейки столбца А. Нам необходимо дописать оставшиеся семь шагов.

Если вы посмотрите на приведенный выше код, вы увидите, что макрос определенным образом структурирован. Сначала приложение перемещает курсор на ячейку с помощью команды Range(«A1»).Select, затем редактирует его содержимое с помощью ActiveCell.FormulaR1C1 = «1». Таким образом, для оставшихся шагов мы можем повторить эти действия, меняя адрес ячейки и значение, которое вы хотите записать в эту ячейку. Например, чтобы задать ячейке A4 значение 4, вы должны написать:

И повторить аналогичные шаги для оставшихся значений.

После того, как вы закончите редактирование, сохраните книгу. Запустить макрос вы сможете нажатием кнопки F5, либо, вернувшись в рабочую книгу Excel, перейти по вкладке Разработчик в группу Код -> Макросы и выбрать из списка, интересующий вас макрос.

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

Увеличение скорости выполнения макросов Excel

Пока все хорошо. Давайте разберем пару хитростей, которые помогут ускорить выполнение макроса. Рассмотрим в качестве примера фрагмент кода, приведенный выше. Современные компьютеры отработают рассматриваемый код так быстро, что вы даже не заметите этого. Но что если вам необходимо выполнить операцию 50000 раз. Это займет какое-то время. Если макрос, написанный вами, исчисляется сотнями строк, ускорить выполнение кода можно путем обрезки части процессов, который не используется во время исполнения макроса.

Использование команды Application.ScreenUpdating

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

Команда Application.ScreenUpdating говорит Excel, чтобы он прекратил выводить пересчитанные данные на экран, а выдал готовые значения в конце выполнения кода.

Использование команды Application. Calculation

Вторая хитрость заключается в отключении автоматических вычислений. Давайте я поясню. Каждый раз, когда пользователь или процесс обновляет ячейку, Excel пытается пересчитать все зависимые от нее ячейки. Так скажем, если ячейка, которую пытается обновить макрос, влияет на 10000 остальных ячеек, Excel будет пытаться пересчитать их все до того, как закончится выполнение кода. Соответственно, если существует целый ряд влияющих ячеек, пересчет может значительно замедлить выполнение кода. Чтобы этого не происходило, вы можете установить команду Application. Calculation в начале кода, которая переключит пересчет формул в ручной режим, а затем вернуть автоматическое вычисление в конце макроса.

Будьте внимательны, не забудьте переключить данную опцию снова в автоматический режим в конце макроса. В противном случае, вам необходимо будет это сделать в самом Excel, перейдя по вкладке Формулы в группу Вычисление и выбрать Параметры вычислений –> Автоматический.

Избежание выбора ячеек и диапазонов

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

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

Примеры макросов Excel

Ниже приведены несколько примеров кодов VBA, которые помогут автоматизировать наиболее часто повторяющиеся задачи:

Макрос вставки строки

Макрос вставки столбца

Макрос форматирования

Макрос обхода диапазона ячеек

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

Вам также могут быть интересны следующие статьи

Как автоматизировать рутинные задачи в Excel с помощью макросов

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

Вам уже любопытно, что такое макрос, и как он работает? Тогда смелей вперёд – далее мы шаг за шагом проделаем весь процесс создания макроса вместе с Вами.

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

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

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

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

Создание макроса – практический пример

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

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

Чтобы создать макрос, откройте View (Вид) > Macros (Макросы) > Record Macro (Запись макроса…)

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

Начиная с этого момента, ВСЕ Ваши действия с документом записываются: изменения ячеек, пролистывание таблицы, даже изменение размера окна.

Excel сигнализирует о том, что включен режим записи макроса в двух местах. Во-первых, в меню Macros (Макросы) – вместо строки Record Macro (Запись макроса…) появилась строка Stop Recording (Остановить запись).

Во-вторых, в нижнем левом углу окна Excel. Иконка Стоп (маленький квадратик) указывает на то, что включен режим записи макроса. Нажатие на неё остановит запись. И наоборот, когда режим записи не включен, в этом месте находится иконка для включения записи макроса. Нажатие на неё даст тот же результат, что и включение записи через меню.

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

Далее, введите в ячейки формулы в соответствии с названиями заголовков (даны варианты формул для англоязычной и русифицированной версии Excel, адреса ячеек – всегда латинские буквы и цифры):

• =SUM(B2:K2) или =СУММ(B2:K2)
• =AVERAGE(B2:K2) или =СРЗНАЧ(B2:K2)
• =MIN(B2:K2) или =МИН(B2:K2)
• =MAX(B2:K2) или =МАКС(B2:K2)
• =MEDIAN(B2:K2) или =МЕДИАНА(B2:K2)

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

После выполнения этого действия в каждой строке должны появиться соответствующие итоговые значения.

Далее, мы подведем итоги для всей таблицы, для этого делаем ещё несколько математических действий:

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

• =SUM(L2:L21) или =СУММ(L2:L21)
• =AVERAGE(B2:K21) или =СРЗНАЧ(B2:K21) – для расчёта этого значения необходимо взять именно исходные данные таблицы. Если взять среднее значение из средних по отдельным строкам, то результат будет другим.
• =MIN(N2:N21) или =МИН(N2:N21)
• =MAX(O2:O21) или =МАКС(O2:O21)
• =MEDIAN(B2:K21) или =МЕДИАНА(B2:K21) – считаем, используя исходные данные таблицы, по причине указанной выше.

Теперь, когда с вычислениями закончили, займёмся форматированием. Для начала для всех ячеек зададим одинаковый формат отображения данных. Выделите все ячейки на листе, для этого воспользуйтесь комбинацией клавиш Ctrl+A, либо щелкните по иконке Выделить все, которая находится на пересечении заголовков строк и столбцов. Затем нажмите Comma Style (Формат с разделителями) на вкладке Home (Главная).

Далее, изменим внешний вид заголовков столбцов и строк:

• Жирное начертание шрифта.
• Выравнивание по центру.
• Заливка цветом.

И, наконец, настроим формат итоговых значений.

Вот так это должно выглядеть в итоге:

Если Вас все устраивает, остановите запись макроса.

Поздравляем! Вы только что самостоятельно записали свой первый макрос в Excel.

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

Чтобы очистить все ячейки от данных, щёлкните правой кнопкой мыши по иконке Выделить все, которая находится на пересечении заголовков строк и столбцов, и из контекстного меню выберите пункт Delete (Удалить).

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

Важный момент! Если Вы сохраните файл с расширением XLTX, то макрос в нём работать не будет. Кстати, можно сохранить книгу как шаблон Excel 97-2003, который имеет формат XLT, он тоже поддерживает макросы.

Когда шаблон сохранён, можно спокойно закрыть Excel.

Выполнение макроса в Excel

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

• Макрос может нанести вред.
• Ещё раз прочти предыдущий пункт.

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

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

Следующим шагом, мы импортируем последний обновлённый набор данных из файла CSV (на основе такого файла мы создавали наш макрос).

При выполнении импорта данных из CSV-файла, возможно, Excel попросит Вас настроить некоторые параметры для правильной передачи данных в таблицу.

Когда импорт будет закончен, зайдите в меню Macros (Макросы) на вкладке View (Вид) и выберите команду View Macros (Макросы).

В открывшемся диалоговом окне мы увидим строку с именем нашего макроса FormatData. Выберите его и нажмите Run (Выполнить).

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

Заглянем под капот: Как работает макрос?

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

Чтобы увидеть этот программный код, нужно в меню Macros (Макросы) на вкладке View (Вид) кликнуть View Macros (Макросы) и в открывшемся диалоговом окне нажать Edit (Изменить).

Откроется окно Visual Basic for Applications, в котором мы увидим программный код записанного нами макроса. Да, Вы правильно поняли, здесь этот код можно изменить и даже создать новый макрос. Те действия, которые мы совершали с таблицей в этом уроке, вполне можно записать с помощью автоматической записи макроса в Excel. Но более сложные макросы, с тонко настроенной последовательностью и логикой действий требуют программирования вручную.

Добавим ещё один шаг к нашей задаче…

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

1. Откройте файл шаблона, в котором мы сохранили макрос – FormatData.
2. Создайте новый макрос с именем LoadData.
3. В процессе записи макроса LoadData сделайте импорт данных из файла data.csv – как мы это делали в предыдущей части урока.
4. Когда импорт будет завершён, остановите запись макроса.
5. Удалите все данные из ячеек.
6. Сохраните файл, как шаблон Excel с поддержкой макросов (расширение XLTM).

Таким образом, запустив этот шаблон, Вы получаете доступ к двум макросам – один загружает данные, другой их форматирует.

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

Оцените качество статьи. Нам важно ваше мнение:

Как создать макрос в Excel?

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

Давайте поподробнее разберемся в тонкостях работы с макросами.

Введение

Зачастую нам приходится выполнять одни и те же действия в Excel большое количество раз. C такой «рутинной работой» рано или поздно сталкивается почти каждый пользователь Excel — формирование ежедневных и еженедельных отчетов, обработка однообразных данных и т.д.

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

Макрос в Excel — это программа, записанная на языке программирования Visual Basic for Applications (VBA), которая выполняет определенную последовательность действий.

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

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

1. Макрорекордер записывает все Ваши действия, в том числе и случайно сделанные ошибки. Поэтому перед записью макроса четко продумайте список выполняемых действий — это поможет избежать ошибок;
2. Макрорекордер записывает действия, выполненные только в программе Excel. Переключения на другие программы макрорекордер не записывает;
3. Макрорекордер записывает только те действия, для которых есть кнопки или команды меню в Excel.

Запись макроса макрорекордером

Чтобы записать макрос в Excel нужно в нижней панели нажать на кнопку «Запись макроса» или перейти в панели инструментов на вкладку «Вид» в группу «Макросы» и выбрать «Запись макроса»:

Создание макроса

Настраиваем поля записываемого макроса (можно оставить поля пустыми):

Выбор имени макроса и сочетания клавиш

1. Имя макроса — произвольное имя на русском или английском языке;
2. Сочетание клавиш — комбинация клавиш, которой в дальнейшем будет запускаться макрос;
3. Сохранить в — книга в которой будет сохраняться макрос.

Остановить запись макроса можно двумя способами: в нижней панели нажать на кнопку «Остановить запись» или перейти в панели инструментов на вкладку «Вид» в группу «Макросы» и выбрать «Остановить запись»:

Конец записи макроса

Как выполнить макрос в Excel?

Чтобы выполнить и запустить макрос можно нажать выбранное сочетание клавиш или перейти на вкладку «Вид» в группу «Макросы», выбрать «Макросы» и нажать кнопку «Выполнить» или нажать сочетание клавиш Alt+F8:

Выбор макроса

Просмотр кода макроса

Для просмотра кода макроса нужно перейти на вкладку «Вид» в группу «Макросы» и выбрать «Макросы»,а затем нажать кнопку «Изменить»:

Код макроса

При знании языка VBA код макроса можно либо изменить, например, для удаления ошибок или для добавления новых действий для данного макроса, либо создать макрос с нуля на Visual Basic.

Резюме

Для создания макроса в Excel нужно выполнить следующие шаги:

1. Создание макроса. Выбрать на панели инструментов «Вид» -> «Макросы» -> «Запись макроса» и при необходимости заполнить настройки макроса;
2. Запись макроса. Выполнить требуемые действия;
3. Остановить запись макроса. Выбрать на панели инструментов «Вид» -> «Макросы» -> «Остановить запись».

Удачи вам и до скорых встреч на страницах блога Tutorexcel.ru!

Поделиться с друзьями:

Поиск по сайту:

Как запустить макрос в Excel разными способами

Если уже имеете код макроса записанный в рабочей книге или надстройке, можно его запустить и выполнить несколькими способами. Если еще нет кода макроса или вы еще не знаете, как его написать, тогда сначала прочитайте статью: Как написать макрос в Excel на языке программирования VBA.

В данной статье для Вас буде приведет пошаговый пример с картинками и фрагментом простейшего готового кода VBA.

Как выполнить макрос VBA в Excel

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

1. Выберите инструмент: «РАЗРАБОТЧИК»-«Код»-«Макросы».
2. В появившемся окне «Макрос» из списка всех в первую очередь доступных макросов для данной рабочей книги Excel выберите любой понравившийся вам и нажмите на кнопку «Выполнить».

Программа макроса выполнить свои функции.

Теперь мы можем двигаться от простого к еще простейшему) Ведь запускать макросы таким способом весьма неудобно. Далее рассмотрим более простые варианты.

Полезный совет! Если вы хотите отредактировать макрос тогда выберите его в списке выше описанного инструмента и нажмите на кнопку «Войти», а не используйте кнопку «Изменить». С помощью кнопки войти вы всегда сможете перейти к исходному коду выбранного макроса. Автоматически откроется редактор Visual Basic именно в том месте где написан код данного макроса. Кнопка «Изменить» к сожалению, не для всех макросов работает одинаково.

Внимание! Макросы, созданные в надстройках Excel, могут быть по умолчанию не доступны в списке диалогового окна «Макрос» в целях политики безопасности. Ведь имена макросов в надстройках Excel обычно скрыты от сторонних пользователей. Но если ввести правильно соответственное значение в поле «Имя макроса:» то все кнопки в диалоговом окне будут активными. А значит готовы к работе с данным макросом из надстройки и пользователь является автором надстройки или доверительным лицом.



Запуск макроса с помощью горячих клавиш

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

Чтобы присвоить свою комбинацию клавиш для определенного макроса следует сделать так:

1. Снова выберите инструмент: «РАЗРАБОТЧИК»-«Код»-«Макросы».
2. В появившемся диалоговом окне «Макрос» выберите желаемую макропрограмму из списка и нажмите на кнопку «Параметры».
3. В появившемся диалоговом окне «Параметры макроса» в поле ввода «Сочетание клавиш: CTRL+» введите маленькую или большую латинскую букву. И нажмите на кнопку ОК.

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

Обратите внимание! В диалоговом окне «Параметры макроса» доступно поле для ввода его описания. Данное описание будет доступно внизу при выборе макроса из списка. Рекомендуем всегда подписывать свои макросы. Такой пользовательский тон этики работы с Excel будет высоко оценен, когда список пополниться десятками макросов.

Полезный совет! Старайтесь не использовать популярные комбинации горячих клавиш для работы с Excel: CTRL+S; CTRL+A; CTRL+T; CTRL+C; CTRL+V; CTRL+SHIFT+L и т.п. Ведь тогда их предназначение будет изменено. В окне «Параметры макроса» так же присутствует возможность использовать в комбинациях клавиш клавишу SHIFT. Как только вы придадите фокус клавиатуры для поля ввода «Сочетание клавиш: CTRL+» и попытаетесь ввести большую букву для комбинации, естественно с использованием клавиши SHIFT на клавиатуре, сразу название поля ввода поменяется на «Сочетание клавиш: CTRL+ SHIFT+».

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

Как запустить макрос на панели быстрого доступа

Excel позволяет пользователям запускать макросы с помощью панели быстрого доступа к инструментам. Данная панель по умолчанию находиться в самом верху окна программы.

Чтобы запустить макрос с помощью панели быстрого доступа, требуется сделать так:

1. Щелкните на самую последнюю кнопку на панели быстрого доступа (стрелка вниз), чтобы получить доступ к меню «Настроить панель», в котором необходимо выбрать опцию «Другие команды». В результате чего откроется окно «Параметры Excel», к которому можно было получить доступ и другим путем: «ФАЙЛ»-«Параметры»-«Панель быстрого доступа». Или щелкнув правой кнопкой мышки по любой панели Excel на любой закладке и выбрать опцию «Настройка панели быстрого доступа».
2. В левом выпадающем списке «Выбрать команду из:» выберите опцию «Макросы».
3. В левом списке элементов ниже выберите необходимый макрос и нажмите на кнопку «Добавить». В результате чего в правом списке элементов появиться кнопка для нового инструмента быстрого запуска вашего макроса. Справа от правого списка элементов можно использовать стрелки для настройки положения и порядка расположения кнопок инструментов на панели быстрого доступа. И нажмите ОК.

Теперь на панели быстрого доступа появилась еще одна кнопка для. Достаточно нажать на нее и сразу же запуститься макрос.

Полезный совет! Чтобы изменить внешний вид кнопки для макроса в окне «Параметры Excel»-«Панель быстрого доступа», из правого списка элементов «Настройка панели быстрого доступа» следует выделить макрос и нажать на кнопку «Изменить». Будет предложена небольшая галерея иконок для нового оформления внешнего вида кнопки запуска макроса с панели инструментов.

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

Запуск макроса с помощью кнопки на рабочем листе Excel

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

1. Выберите инструмент: «РАЗРАБОТЧИК»-«Элементы управления»-«Вставить»-«Кнопка (элемент управления формы».
2. Выделите курсором мышки место где и как будет вставлена кнопка для запуска макроса.
3. В появившемся окне «Назначить макрос объекту» из списка доступным макросов выберите желаемый и нажмите на кнопку ОК.
4. Теперь нужно назначить кнопку макросу. Правой кнопкой мышки щелкните по созданной кнопке и в появившемся контекстном меню выберите опцию «Изменить текст». Удалите старый и введите новый текст «Мой макрос».

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

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

Руководство и примеры работы с макросами в Excel

Многие люди думают, что макросы в Excel – это очень трудно, но это не так. В этой статье Вы найдёте несколько интересных и простых примеров, как можно автоматизировать Ваши повседневные задачи в Excel. Макрос – это программа на Visual Basic, созданная для того, чтобы автоматизировать задачи в Microsoft Office. В своих примерах я использую Excel 2010, но с таким же успехом Вы можете использовать Excel 2007.

Создаем макрос при помощи команды «Запись макроса»

1. Для начала откройте вкладку View (Вид) на Ленте. В выпадающем списке Macros (Макросы) нажмите кнопку Record Macro (Запись макроса).Откроется диалоговое окно Record Macro (Запись Макроса).

2. Задайте имя макросу (не допускаются пробелы и специальные символы), клавишу быстрого вызова, а также, где бы Вы хотели сохранить свой макрос. При желании, Вы можете добавить описание.
3. С этого момента макрос записывает действия. Например, Вы можете ввести слово “Hello” в ячейку A1.
4. Теперь снова нажмите иконку Macros (Макросы) и в раскрывшемся меню выберите Stop Recording (Остановить запись).

Доступ к записанному макросу можно получить с помощью команды View Macros (Макросы), которая находится на вкладке View (Вид) в выпадающем меню Macros (Макросы). Откроется диалоговое окно Macro (Макрос), в котором Вы сможете выбрать нужный. Дважды кликните по имени макроса, чтобы выполнить программу.

Кроме этого, Вы можете связать макрос с кнопкой. Для этого:

1. На вкладке File (Файл) нажмите Options (Параметры) > Quick Access Toolbar (Панель быстрого доступа).
2. В поле Choose commands from (Выбрать команды из) выберите All Commands (Все команды).
3. Найдите команду Option Button (Кнопка), нам нужна та, что относится к разделу Form Control (Элементы управления формы). Выделите ее и нажмите Add (Добавить). Затем нажмите ОК, чтобы закрыть параметры Excel.
4. Выберите команду, только что добавленную на Панель быстрого доступа, и начертите контур кнопки на рабочем листе Excel.
5. Назначьте макрос объекту.

Примечание: Если у вас включена вкладка Developer (Разработчик), то получить доступ к элементам управления формы можно с нее. Для этого перейдите на вкладку Developer (Разработчик), нажмите на иконку Insert (Вставить) и из раскрывающегося меню выберите нужный элемент.

Не знаете, как отобразить вкладку Developer (Разработчик)? Excel 2007: жмем на кнопку Office > Excel Options (Параметры Excel) > Popular (Основные) и ставим галочку напротив опции Show Developer tab in the Ribbon (Показывать вкладку “Разработчик” на ленте). Excel 2010: жмем по вкладке File (Файл) > Options (Параметры) > Customize Ribbon (Настройка ленты) и в правом списке включаем вкладку Developer (Разработчик).

Цикл FOR

В следующем примере Вы увидите, как использовать цикл FOR. Цикл FOR позволяет нам выполнить повторение цикла с разными значениями. Давайте посмотрим, как можно заполнить числами от 1 до 5 ячейки A1:A5.

Для этого на вкладке Developer (Разработчик) нажмите Visual Basic. Дважды кликните по объекту из списка Microsoft Excel Objects, в котором должен быть сохранён макрос. Введите вот такой код:

Sub Macro1 ()
   For n = 1 To 5
      Cells(n, 1) = n
   Next n
End Sub

Сохраните файл. Чтобы выполнить макрос, перейдите View > Macros > View Macros (Вид > Макросы > Макросы), выберите из списка название нужного макроса и нажмите Run (Выполнить).

Следующий код отображает фразу “Hello World” в окне сообщений Windows.

Sub MacroName()
   MsgBox ("Hello World!")
End Sub

В следующем примере мы создаём сообщение с выбором Yes (Да) или No (Нет). Если выбрать вариант Yes (Да), то значение ячейки будет удалено.

Sub MacroName()
   Dim Answer As String
 
   Answer = MsgBox("Are you sure you want to delete the cell values ?", 
      vbQuestion + vbYesNo, "Delete cell")
 
   If Answer = vbYes Then
      ActiveCell.ClearContents
   End If
End Sub

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

Если Вы нажмёте Yes (Да), значение в выделенной ячейке будет удалено. А если No (Нет) – значение сохранится.

Конструкция IF

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

Sub MacroName()
   Dim CellValue As Integer
   CellValue = ActiveCell.Value

   If CellValue > 20 Then
      With Selection.Font
         .Color = -16776961
      End With
   Else
      With Selection.Font
         .ThemeColor = xlThemeColorLight2
         .TintAndShade = 0
      End With
   End If
End Sub

Для проверки этого кода выберем ячейку со значением больше 20:

Когда Вы запустите макрос, цвет шрифта изменится на красный:

При выполнении второго условия шрифт станет синим:

Конструкция CASE

Вы также можете использовать конструкцию Case, чтобы связать выполнение действия с появлением определённого значения в ячейке. В следующем примере отображается сообщение, содержание которого зависит от указанного возраста человека.

Sub MacroName()
   Dim CellValue As Integer
   CellValue = ActiveCell.Value
 
   Select Case CellValue
      Case 60 To 200
         MsgBox "The person is old"

      Case 30 To 59
         MsgBox "The person is adult"
 
      Case 18 To 29
         MsgBox "The person is young"
 
      Case 0 To 17
         MsgBox "The person is a child"
 
      Case Else
         MsgBox "Unknown age"
 
   End Select
End Sub

Чтобы протестировать этот пример, Вы должны выбрать ячейку со значением и запустить макрос. Если значение выбранной ячейки, к примеру, равно 44, то будет показано вот такое сообщение:

Заключение

Макросы – это отличный способ автоматизировать задачи в Excel. Эта статья показывает лишь немногие примеры того, что можно сделать при помощи макросов.

Оцените качество статьи. Нам важно ваше мнение:

Быстрый старт: создание макроса

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

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

Как?

Следующие шаги

Как?

Нужна дополнительная помощь?

Вы всегда можете спросить эксперта в техническом сообществе Excel, получить поддержку в сообществе Answers или предложить новую функцию или улучшение в Excel User Voice.

.

Как создать макрос Excel

Создайте макрос Excel, чтобы ускорить выполнение повторяющихся задач!

Макросы — вы, наверное, слышали это слово раньше и, возможно, даже слышали, как люди говорят о том, какие они замечательные. Но в чем дело?

Когда дело доходит до работы с электронными таблицами в Excel 2013, некоторые процессы могут быть повторяющимися (и, смею сказать, скучными). Один из примеров — форматирование отчета. Хотя редактирование стилей текста и корректировка ячеек может показаться рутинной задачей, представление данных очень важно для тех, кто позже получит смысл из отчета.Но здесь вам могут помочь макросы: вы можете создать макрос Excel, который превратит 10 минут щелчка в один щелчок!

Как создать макрос Excel

Диалоговое окно «Запись макроса в Excel» с выборками образцов.

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

После того, как вы добавили вкладку «Разработчик», выполните следующие действия, чтобы создать макрос Excel:

1. Перейдите на вкладку «Разработчик» и нажмите кнопку «Запись макроса» в группе «Код» ИЛИ нажмите кнопку в нижнем левом углу экрана. выглядит как электронная таблица с красной точкой в ​​верхнем левом углу.
2. Создайте имя для вашего макроса. Пробелы не допускаются, но вместо них можно использовать подчеркивание. На рисунке на этой странице мы назвали наш макрос «Format_Report».
3. Выберите сочетание клавиш. Обязательно выберите букву, которая еще не используется в качестве сочетания клавиш, поскольку она заменит исходную (т. Е. Не используйте Ctrl + Z, если вы не хотите потерять сочетание клавиш для отмены). Используя Shift, вы можете расширить свои параметры (то есть вместо этого вы можете использовать Ctrl + Shift + Z). На нашей иллюстрации мы обозначили Ctrl + Shift + R в качестве сочетания клавиш.
4. Выберите, где сохранить макрос. Обычно вы хотите сохранить свой макрос в этой книге, что мы и выбрали в нашем примере. Если вы хотите, чтобы он был доступен всякий раз, когда вы используете Excel, выберите «Личная книга макросов» в раскрывающемся меню.
5. Введите краткое описание того, что будет делать ваш макрос, в поле «Описание».
6. Нажмите ОК.

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

Когда вы закончите, нажмите кнопку «Остановить запись» в группе «Код» на вкладке «Разработчик» ИЛИ щелкните синий квадрат в нижнем левом углу окна Excel.

Поздравляем! Вы только что узнали, как создать макрос Excel!

Изучите VBA, чтобы стать экспертом в создании макросов Excel

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

Дополнительный совет: абсолютные ссылки и относительные ссылки

Ведущий инструктор Microassist, Энди Уивер, рассказывает об абсолютных и относительных ссылках в этом виртуальном классе.

Дополнительные ресурсы Excel

Это лишь поверхностный обзор того, что вы можете узнать в Microsoft Excel! Мы предлагаем несколько курсов по Excel 2013, от ознакомления с Excel для новичков, до продвинутых классов, до классов по сводным таблицам и т. Д.

Ознакомьтесь с расписанием курса , чтобы узнать, что будет дальше!

Подписаться на новости обучения

.

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

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

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

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

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

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

Макросы

— это код, который автоматизирует работу в программе — они позволяют вам добавлять свои собственные крошечные функции и улучшения, которые помогут вам выполнить именно то, что вам нужно, быстро, всего лишь одним щелчком кнопки. В таком инструменте работы с электронными таблицами, как Excel, макросы могут быть особенно мощными. Скрытые за обычным пользовательским интерфейсом, они более мощные, чем стандартные функции, которые вы вводите в ячейку (например,г. = ЕСЛИ (A2 <100,100; 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» в проводнике проекта.

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

Ваш, вероятно, выглядит иначе, чем мой. Вы можете догадаться, что делает мой код?
– Sub Makebold - это просто текст Sub , за которым следует имя, которое я ввел, когда начал запись.
- Зеленая линия на самом деле ничего не делает - это комментарий, в который вы можете добавить объяснение того, что делает макрос.
- Выбор.Font.Bold = True делает значения в выбранных ячейках жирным шрифтом .
- 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 («Вставьте здесь диапазон»). Скопируйте . Вот несколько примеров:
– Диапазон («A: C»). Копировать ← копирует столбцы от A до C
– Диапазон («A1: C100»). Копировать ← копирует диапазон A1: C100

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

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

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

Наклеивание можно выполнить по-разному в зависимости от того, что вы хотите вставить. В 99% случаев вам понадобится одна из этих двух строк кода:
– Диапазон («Ячейка / область, в которую вы хотите вставить»). Вставьте специальные ← вставки как обычно (формулы и форматирование)
- Диапазон («Ячейка / область, в которую нужно вставить»). Pastespecial xlPasteValues ​​ ← вставляет только значения

Ячейки для резки с VBA

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

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

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

Например, вот код, который вам нужно вырезать из диапазона A: C и вставить в D1 :
– Range («A: C»).Вырезать
– Диапазон («D1»). Выберите
– ActiveSheet. Вставьте

.

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

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

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

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

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

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

1. Эта строка гарантирует, что цикл начинается в верхней левой ячейке листа и случайно не испортит данные, начавшись где-то еще.
2. Строка For i = от 1 до 500 означает, что количество запусков цикла (представленное i ) является возрастающим числом, которое начинается с 1 и заканчивается на 500. Это означает, что цикл будет выполняться 500 раз. .Количество запусков цикла зависит от того, какие действия вы хотите выполнить. Используйте здесь свой здравый смысл. 500 раз - это слишком много для нашего образца набора данных, но идеально подошло бы, если бы в базе данных было 1500 строк данных.
3. Эта строка распознает активную ячейку и указывает Excel переместиться на 3 строки вниз и выбрать эту ячейку, которая затем станет новой активной ячейкой. Если бы в наших данных была потеряна каждая четвертая строка, а не каждая третья, мы могли бы просто заменить 3 на 4 в этой строке.
4. Эта строка сообщает Excel, что делать с этой вновь выбранной ячейкой. В этом случае мы хотим удалить ячейку таким образом, чтобы ячейки справа от ячейки сдвинулись влево. Это достигается с помощью этой линии. Если бы мы хотели сделать что-то еще с неуместными строками, то это самое подходящее место. Если бы мы хотели полностью удалить каждую третью строку, то должна была бы быть строка: Selection.Entirerow.delete .
5. Эта строка сообщает Excel, что в цикле больше нет действий.В этом случае 2 и 5 - это кадр цикла, а 3 и 4 - действия внутри цикла.

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

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

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

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

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

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

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

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

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

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

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

1. Это первая часть IF-оператора. Он говорит, что , если ячейка справа от активной ячейки (или Activecell.Offset (0,1) в коде VBA) пуста (представлена ​​как = "" ) , тогда что-то сделайте. Это то же самое действие, что и при создании цикла в первую очередь: удаление активной ячейки и перемещение активной строки на одну ячейку влево (выполняется с помощью Selection.Удалить 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 и нажмите кнопку Make a Zap в правом верхнем углу. Затем выберите Typeform в средстве выбора приложения и настройте его для отслеживания вашей формы на предмет новых записей.

Zapier может следить за вашей формой Typeform на предмет новых записей

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

Zapier позволяет добавлять, обновлять или находить строки в электронной таблице Excel

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

Zapier может добавлять данные формы прямо в нужную строку электронной таблицы

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

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

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

Журнал данных в электронную таблицу Excel

Работайте с таблицей

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

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

Чтобы узнать больше, вот несколько дополнительных ресурсов, которые помогут вам максимально эффективно использовать макросы Excel:
- Узнайте больше о том, как повторять код VBA с циклами
- Узнайте глубже, что вы можете сделать с if операторами и другой логикой прыжки в Excel Macros
- Изучите справочник Microsoft Excel VBA, чтобы изучить все, что вы можете делать с кодом и макросами VBA.
- Использовать Google Таблицы вместо Excel? Ознакомьтесь с нашим руководством по скрипту Google Apps, чтобы узнать, как автоматизировать Google Sheets

.
.

Как создать макрос с нуля в Excel

«Секрет успеха в том, чтобы начать» - Марк Твен.

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

Давайте начнем с быстрого взгляда, что такое макросы?

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

Термин «Макросы Excel» вызывает большую путаницу. Вкратце, макросы Excel относятся к языку программирования VBA, который встроен в Excel.

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

Что такое VBA?

VBA - это язык программирования Visual Basic для приложений. Этот язык предустановлен в Excel. Это очень мощный инструмент, который позволяет расширить функциональные возможности Excel.

VBA также входит в комплект всех других приложений Microsoft Office, таких как Microsoft Word, Access и Outlook.

В следующих разделах мы собираемся создать макрос с нуля. Под макросом мы подразумеваем Sub .

Sub (сокращение от Subroutine) - это просто набор строк кода VBA, который выполняет определенные задачи. Любое приложение VBA, которое вы создаете, по сути представляет собой набор подписок.

Итак, теперь мы понимаем фон, давайте создадим макрос.

Шаг 1. Сделайте вкладку разработчика видимой

Вкладка «Разработчик» по умолчанию обычно отображается на ленте. Если нет, вы можете сделать его видимым, выполнив следующие действия:

1. Щелкните ленту правой кнопкой мыши. В появившемся списке выберите «Настроить ленту»

.

2. В появившемся диалоговом окне вы увидите список вкладок с правой стороны. Убедитесь, что установлен флажок «Разработчик».

Шаг 2 Проверьте настройки безопасности

Следующим шагом является проверка правильности настроек макроса. На ленте разработчика нажмите кнопку «Безопасность макросов» в разделе «Код».

Ваши настройки безопасности должны совпадать с настройками на скриншоте ниже

Шаг 3 Сохранить как правильный тип файла

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

Выберите «Файл» -> «Сохранить как» и выберите «Книга с поддержкой макросов» в типе файла. Затем нажмите «Сохранить».

Шаг 4 Войдите в редактор Visual Basic

Чтобы войти в редактор Visual Basic, щелкните значок Visual Basic на вкладке «Разработчик» на ленте. В качестве альтернативы вы можете нажать Alt + F11 на клавиатуре

Шаг 5 Создайте модуль

Макросы хранятся в модулях, поэтому создайте модуль.

Посмотрите в окне проекта VBA. Если окно не отображается, выберите в меню «Просмотр» -> «Проводник проекта» (сочетание клавиш Ctrl и R).

Выберите книгу, в которую вы хотите поместить код. Рабочие книги будут заключены в скобки рядом с «VBAProject». Каждая открытая книга будет иметь такую ​​запись в окне проекта VBA

.

Щелкните правой кнопкой мыши книгу, в которую вы хотите поместить код. Выберите «Вставить», а затем «Модуль».

появится новый модуль:

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

Шаг 6 Создайте макрос

Введите «Sub MyFirstMacro» в главном окне кода и нажмите клавишу возврата.У вас должны быть следующие

Шаг 7 Добавьте код в свой макрос

Используйте MsgBox «Hello World» . Это отобразит диалоговое окно с «Hello World» для пользователя

.

Шаг 8 Запустите макрос

Для запуска макроса:

1. Щелкните в любом месте макроса
2. Выберите Run-> Run Sub / UserForm

Примечание. Если курсор не находится внутри макроса, VBA отобразит список доступных макросов и попросит вас выбрать один для запуска.

Вы должны увидеть это диалоговое окно

Заключение

В этом посте показано, как начать работу с VBA. Он прошел через шаги для правильной настройки и закончился запуском макроса.

Теперь вы готовы начать работу с VBA / макросами. Возможно, вы захотите ознакомиться с The Ultimate VBA Tutorial, предназначенным для начинающих.

Что дальше?

Бесплатное руководство по VBA Если вы новичок в VBA или хотите отточить свои существующие навыки VBA, почему бы не попробовать The Ultimate VBA Tutorial.

Связанное обучение: Получите полный доступ к обучающим вебинарам по Excel VBA и всем учебным материалам.

( ПРИМЕЧАНИЕ: Планируете создание приложения VBA или управление им? Узнайте, как создать 10 приложений Excel VBA с нуля.)

Получите бесплатную электронную книгу

Не стесняйтесь подписаться на мой информационный бюллетень и получить эксклюзивный контент VBA, который вы не можете найти здесь, в блоге, а также бесплатный доступ к моей электронной книге How to Ace the 21 Most Common Question in VBA , которая полна примеров, которые вы можно использовать в собственном коде.

.