Как сделать макросы в экселе. Как записать макрос в Excel? Пошаговая инструкция.

Как заавтоматизировать рутинные задачки в 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 говорит о том, что включен режим записи макроса в 2-ух местах. Во-1-х, в меню 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 (Главная).

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

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

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

Вот так это обязано смотреться в итоге:

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

Поздравляем! Вы лишь что без помощи других записали собственный 1-ый макрос в Excel.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читайте также  Через модем не работает интернет. 3G USB модем - проблемы и их диагностика

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

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

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

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

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

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

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

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

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

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

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

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

Способы записи макросов в Excel

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

Вариант 1: Автоматическая запись макросов

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

Подробнее: Включение и отключение макросов в Microsoft Excel

Когда все готово, приступаем к записи.

  1. Перейдите на вкладку «Разработчик». Кликните по кнопочке «Запись макроса», которая размещена на ленте в блоке инструментов «Код».
  2. Открывается окно опции записи макроса. Здесь можно указать хоть какое имя для него, ежели установленное по умолчанию вас не устраивает. Основное, чтоб имя это начиналось с буковкы, а не с числа, а также в заглавии не обязано быть пробелов. Мы оставили заглавие по умолчанию – «Макрос1».
  3. Тут же при желании можно установить сочетание кнопок, при нажатии на которые макрос будет запускаться. Первой кнопкой непременно обязана быть Ctrl, а вторую юзер устанавливает без помощи других. Мы в качестве примера установили кнопку М.
  4. Далее следует найти, где будет храниться макрос. По умолчанию он размещен в данной для нас же книжке (файле), но при желании можно установить хранение в новейшей книжке либо в отдельной книжке макросов. Мы оставим значение по умолчанию.
  5. В самом нижнем поле можно бросить хоть какое подходящее по контексту описание макроса, но это делать не непременно. Когда все опции выполнены, нажимаем на клавишу «OK».
  6. После этого все ваши деяния в данной книжке (файле) Excel будут записываться в макрос до тех пор, пока вы сами не остановите запись.
  7. Для примера запишем простейшее арифметическое действие: сложение содержимого 3-х ячеек (=C4+C5+C6).
  8. Когда метод был выполнен, щелкаем на клавишу «Остановить запись». Эта клавиша преобразовалась из клавиши «Запись макроса» опосля включения записи.
Читайте также  Нод 32 секьюрити ключи. Ключ для Nod32 на 90 дней 2020 – 2021

Запуск макроса

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

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

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

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

  1. Снова щелкаем на клавишу «Макросы». В открывшемся окне избираем подходящий и кликаем по кнопочке «Изменить».
  2. Открывается «Microsoft Visual Basic» (VBE) – среда, где происходит их редактирование.
  3. Запись каждого макроса начинается с команды , а заканчивается командой . Сходу же опосля указывается имя макроса. Оператор показывает выбор ячейки. К примеру, при команде «Range(«C4»).Select» выбирается ячейка «C4». Оператор употребляется для записи действий в формулах и остальных расчетов.
  4. Попытаемся мало поменять макрос, дописав выражение:
  5. Выражение заменим на .
  6. Закрываем редактор и запускаем макрос. Как лицезреем, вследствие введенных нами конфигураций была добавлена доборная ячейка с данными. Она также была включена в расчет общей суммы.
  7. В случае ежели макрос очень большой, его выполнение может занять существенное время, но внесением ручного конфигурации в код мы можем убыстрить процесс. Добавляем команду . Она дозволит сохранить вычислительные мощности, а означает, убыстрить работу. Это достигается методом отказа от обновления экрана во время выполнения вычислительных действий. Чтоб возобновить обновление опосля выполнения макроса, в его конце пишем команду .
  8. Добавим также команду в начало кода, а в его конец дописываем . Сиим мы поначалу отключаем автоматический пересчет результата опосля каждого конфигурации ячеек, а в конце макроса – включаем. Таковым образом, Excel подсчитает итог лишь один раз, а не будет его повсевременно пересчитывать, чем сэкономит время.
  9. Вариант 2: Написание кода макроса с нуля

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

    1. Чтобы приступить к этому, необходимо надавить на клавишу «Visual Basic», которая размещена в самом начале ленты разработчика.
    2. Откроется окно редактора VBE, которое уже быдло продемонстрировано в прошлом варианте.
    3. Программист пишет там код макроса вручную.

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

    Мы рады, что смогли посодействовать Для вас в решении проблемы.

    Добавьте веб-сайт в закладки и мы еще пригодимся для вас.

    Опишите, что у вас не вышло. Наши спецы постараются ответить очень быстро.

    Помогла ли для вас эта статья?

    ДАНЕТ

Поделиться статьей в соц сетях:

Оставьте комментарий