Макросы в Excel — Инструкция по использованию. Макросы для экономистов в excel


Макросы Excel | Эксель Практик

Макрос Excel – это набор команд, которые записаны на языке Microsoft Visual Basic. Что важно – макрос может быть сделан без знания VBA. Microsoft Office любезно предоставил возможность записывать макросы через рекордер. Что это дает? Это дает возможность сделать какой-либо алгоритм действий, которые вы монотонно делаете изо дня в день, за секунды. Придется повозиться, но эффект сногсшибательный. Для примера: когда я начал работать в финансовом отделе, мне часто приходилось вытаскивать отчеты из 1С. Кто сталкивался, в курсе, что отчеты в формате Excel практически не готовы к работе – сортировке, например. Поэтому приходилось приводить в божеский вид – убирать ненужные строчки, отменять объединения ячеек, приведение в нужный формат и т.д. Уходило примерно полчаса. А написав макросы, я всю процедуру свел в полторы минуты, причем в основном время уходило на открытие нужных в тот момент мне файлов.

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

Итак, перед написанием макроса следует знать, что

  1. Макрос работает только с той структурой, которая существовала в момент написания. Т.е., не меняйте поля, не задавайте другие имена, не удаляйте листы, которые задействованы в макросе, иначе придется переделывать макрос по новой.
  2. Командой «Отмена» действия, которые произвел макрос не отменить.
  3. Изменить макрос можно только зная в какой-то степени язык VBA, тем, кто его не знает, придется стирать макрос и писать заново. Поэтому, чтобы этого избежать, определите по шагам (!) все действия, которые вы планируете записать в макрос.

 Чтобы записать макрос, надо выполнить команду Вид/Макросы/Записать макрос.

макросы excel

Надо определиться с именем макроса.

макросы excel

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

После того, как все команды будут вами сделаны, чтобы остановить запись макроса нужно нажать на знак «Стоп», на панели внизу слева. макросы excel Или Вид/Макросы/Остановить запись.

Запуск макроса можно сделать как по комбинации клавиш, так и из меню макросов (Вид/Макросы). Но если вы будете пользоваться часто этим макросом, рекомендую поставить его в панель быстрого доступа: Значок Офис/Параметры Excel/Настройка/Левый список (Макросы), выбрать и перенести в правое окно. Желательно для каждого макроса свой значок, чтобы не запутаться.макросы excel

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

Если макрос не запускается, вполне возможно, что у вас стоит запрещение на запуск макросов.  В этом случае надо включить их.

Эксель Практик

«Глаза боятся, а руки делают»

P.S. Понравилась статья? Подпишитесь на рассылку в правой части страницы (Бесплатный курс "Топ-10 инструментов Excel") и будьте в курсе новых событий.

excelpractic.ru

Макрос для создания сводной таблицы в Excel

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

Создание сводных таблиц макросом

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

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

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

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

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

В данном примере создание сводной таблицы VBA-макросом будет достаточно простым. Простота решения будет достигнута за счет симуляции использования подобных параметров и действий, которые предоставляет к распоряжению мастер сводных таблиц в Excel. Используя экземпляр объекта PivotCaches запишем настройки своей сводной таблицы присвоив ей определенное имя. Это позволит потом непосредственно ссылаться на сводную таблицу в любой части кода. Для создания сводной таблицы используя макрос будем использовать метод PivotTableWizard. На этом же шаге будем использовать возможности объекта PivotFields, в котором определим структуру сводной таблицы с учетом исходных данных источника.

Написание кода макросов в Excel всегда начинается с открытия VBA-редактора (ALT+F11): «РАЗРАБОТЧИК»-«Код»-«Visual Basic».

Затем создадим модуль где будет храниться исходный код. Для этого выберите инструмент в редакторе VBA: «Insert»-«Module». В появившемся окне модуля введите следующий VBA-код макроса:

Sub CreateTableM()ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="Лист1!A1:D21").CreatePivotTable TableDestination:="", TableName:="ТаблицаМ"With ActiveSheet.Name = "Анализ".PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)End WithWith ActiveSheet.PivotTables("ТаблицаМ").SmallGrid = True.PivotFields("Оборот").Orientation = xlDataField.PivotFields("Год").Orientation = xlPageField.PivotFields("Месяц").Orientation = xlRowField.PivotFields("Магазины").Orientation = xlColumnFieldEnd WithEnd Sub

Теперь достаточно лишь запустить макрос выбрав инструмент: «РАЗРАБОТЧИК»-«Код»-«Макросы»-«CreateTableM»-«Выполнить»:

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



Описание VBA-кода макроса для создания сводной таблицы Excel

Данный исходный код макроса позволяет автоматически сгенерировать сводную таблицу, такую же как показано на рисунке 4. Первая строка кода выглядит весьма необычно, но жизненно необходима для дальнейшей работы программы. Она содержит набор базовых параметров для коллекции PivotCaches, которые будут применены к создаваемой сводной таблице. В первом свойстве SourceType объявляется источник для загрузки данных в бедующую сводную таблицу. Параметр xlDatabase говорит нам о том, что исходные данные берутся с рабочего листа Excel.

Используя свойство SourceData задаем ссылку на диапазон ячеек, в котором находиться исходные данные предназначенные для последующей обработки. Указывая адрес ссылки в форме Лист1!A1:D21, мы определяем целый диапазон таблицы с исходными данными включая ее заголовки столбцов. Оба свойства (SourceType и SourceData) являются здесь аргументами для метода Add, который выполняет функцию сбора информации – аналогично инструкциям выполняемых при работе с мастером сводных таблиц. Функция создания сводной таблицы реализуется методом .CreatePivotTable, для которого так же необходимо указать следующие параметры.

Свойство TanleDestination позволяет установить место для вставки будущей сводной таблицы. Если есть необходимость вставить таблицу в конкретном месте выбранного листа, тогда следует указать здесь адрес ячейки для вставки. А если же необходимо создать сводную таблицу на новом листе, тогда достаточно указать пустую строку (“”) в качестве параметра. С помощью свойства TableName присваиваем внутреннее имя будущей таблицы, в данном примере это «ТаблицаМ». Обратите внимание, что название таблицы отличается от названия листа, в котором она будет находится. Благодаря присвоению внутреннего имени для создаваемой таблицы, появляется возможность отличать ее от многих других таблиц. Но самое главное преимущество – это возможность непосредственно ссылаться к таблице через имя, вместо того чтобы каждый раз указывать место и диапазон, где будет находиться сводная таблица.

Далее с помощью инструкции конструктора With собираем блок инструкций методов, свойств и параметров структуры. Все дальнейшие опции будут реализованы внутри свойства ActiveSheet. То есть будут касаться листа, в котором будет создана сводная таблица.

Свойством Name мы определяем имя для нового листа – в данном примере это имя «Анализ». Если нужно задать для нового листа имя такое же как у новой сводной таблицы (Таблица М), тогда эту задачу лучше всего реализовать именно на данном этапе написания кода макроса.

В следующей строке определяем место на новом листе, где будет сгенерирована сводная таблица. Указывая для параметра TanleDestination свойство:

ActiveSheet.Cells(3,1)

Так мы определяем, что таблица будет сгенерирована в третьей строке и в первом столбце листа. То есть в ячейке A3. Эти строки кода определяют базовую информацию о сводной таблице. Закрываем конструктор инструкцией End With.

Читайте также: Макрос для настройки полей и форматирования сводной таблицы.

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

exceltable.com

Макросы в Excel — Инструкция по использованию

Грамотно организовать процесс работы в эксэле помогут такие объекты, как макросы в Excel.

Рассмотрим более подробно все особенности работы с данными объектами в пакете программ MS Office.

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

Что такое макросы и зачем они нужны

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

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

Их создание и использование поможет существенно сэкономить время и максимально автоматизировать роботу в программе.

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

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

С понятием макроса можно также столкнуться в программе Ворд, но в Экселе он имеет несколько преимуществ:

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

Отмечаем еще две статьи, которые могут вам пригодиться:

  • Практичные советы — Как объединить ячейки в Excel
  • Сводные таблицы Excel — Примеры создания

Создание собственных макросов в Excel 2010, 2007, 2013

  • Откройте документ, с которым работаете и для которого хотите создать макрос. К слову, каждая ячейка, над которой производится действие должна быть проработана;
  • Отобразите вкладку разработчика в ленте. Для этого откройте пункт меню «Файл» и откройте параметры, как показано на рисунке;
Параметры программы

Параметры программы

  • Затем выберите настройку ленты и добавьте окно разработчика в список основных окон, как это показано на рисунке ниже;

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

  • Теперь можно перейти непосредственно к созданию самого макроса пользователя.После его создания, каждая ячейка будет автоматизирована – это означает, что любая ячейка пользовательского документа будет выполнять однотипное действие, которое укажет пользователь;
  • Найдите во вкладке разработчика специальную клавишу для создания. Ее расположение указано на рисунке ниже;

Клавиша создания макроса во вкладке для разработчика программы ексель

  • Нажмите на клавишу. Появится окно создания, в котором необходимо указать имя, сочетание клавиш, с помощью которых он будет включаться. Также можно добавить короткое описание функционирования макроса.Это необходимо сделать, если у вас их слишком много, чтобы не запутаться;
Начальное окно создания пользовательского макроса

Начальное окно создания пользовательского макроса

  • Далее нажмите ОК. Окно закроется и начнется процесс записи. Для остановки записи, нажмите соответствующую клавишу на панели управления;
Процесс записи

Процесс записи

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

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

  • На вкладке разработчика найдите кнопку под названием «Макросы». Нажмите на нее;
Клавиша для открытия основного окна макросов в программе

Клавиша для открытия основного окна макросов в программе

  • Выберите нужный вам макрос из списка и нажмите кнопку «Выполнить»;

Запуск выбранного макроса

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

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

Также вам может быть полезным прочтение статей:

  • Как закрепить строку в Excel — Подробные инструкции
  • Выпадающий список в Excel — Инструкция по созданию

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

Макросы создаются с помощью языка программирования под названием Visual Basic (или просто аббревиатура VB).

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

Впервые технология создания макросов в программе эксель была усовершенствована и стала доступна для использования простыми юзерами в версии 2007-го года.

Удобнее всего их создавать в таких версиях Ворда: 2007, 2010, 2013.

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

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

Существую разные типы операторов.

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

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

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

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

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

Макрос их переведет в язык программирования и запомнит все проделанные пользователем команды.

  1. Откройте окно управления с помощью клавиши «Макросы» на главной вкладке разработчика;
  2. Выберите необходимый вам объект и в правой части окна нажмите на кнопку удаления;
  3. Подтвердите удаление.

Удаление пользовательского макроса в программе Excel

Поделиться

ddr64.ru

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

Макросы и средства VBA находятся на вкладке Разработчик, которая по умолчанию скрыта, поэтому сначала нужно включить ее. Дополнительные сведения см. в статье Отображение вкладки "Разработчик".

Вкладка "Разработчик" на ленте

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

Дополнительные сведения о создании макросов см. в разделе Краткое руководство. Создание макроса.

  1. Перейдите на вкладку " Разработчик " и нажмите кнопку Запись макроса.

    Группа "Код" на вкладке "Разработчик"

  2. В диалоговом окне Запись макроса беспокойтесь ввод имени макроса в поле имя макроса. Вы можете принять имя, которое Excel позволяет, например Macro1, так как это временного макроса.

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

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

  4. Щелкните Разработчик > Остановить запись и Excel будет создан worbook вашей личной макрос.

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

Чтобы просмотреть макрос, который вы создали:

  1. Последовательно выберите пункты Разработчик > Visual Basic, чтобы запустить Редактор Visual Basic (VBE), равное место хранения макросов.

  2. В левой области Обозревателя проектов можно найти личную книгу макросов. Если оно не отображается, перейдите в представление > Окно проекта.

  3. Дважды щелкните папку Проект VBA (PERSONAL.xlsb) > модули > модуль1 и вы найдете пустой Macro1 записанного. Можно удалить или сохранить его для добавления кода к более поздней версии.

    Примечание: При записи макроса в новый экземпляр Excel VBA автоматически создать папку модуля и увеличить его номер. Поэтому если у вас уже есть модуль1 и Module2, VBA будет создана Module3. Можно переименовать модули в окне свойств под Обозревателя проектов, лучше отражать выполните макросы в них.

Перемещение макросов на другой компьютер

Файл Personal.xlsb сохраняется в папке XLSTART. Если вы хотите поделиться с другими пользователями макросов, можно скопировать его в папку XLSTART на других компьютерах или скопировать некоторые или все его макросы в файл Personal.xlsb на других компьютерах. Вы можете искать XLSTART в Проводнике найдите его.

Если у вас может быть вызвано лишь несколько макросов, которые вы хотите поделиться с другими пользователями, их можно отправить книгу, содержащую их. Вы также можете книги доступны на общем сетевом диске или в библиотеке SharePoint Services.

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

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

  1. Выберите Excel > Параметры> Лента и панель.

  2. В категории Настроить ленту в списке Основные вкладки установите флажок Разработчик, а затем нажмите кнопку Сохранить.

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

Дополнительные сведения о создании макросов см. в разделе Краткое руководство. Создание макроса.

  1. Перейдите на вкладку " Разработчик " и нажмите кнопку Запись макроса.

  2. В диалоговом окне Запись макроса беспокойтесь ввод имени макроса в поле имя макроса. Вы можете принять имя, которое Excel позволяет, например Macro1, так как это временного макроса.

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

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

  4. Щелкните Разработчик > Остановить запись и Excel будет создан worbook вашей личной макрос.

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

Чтобы просмотреть макрос, который вы создали:

  1. Щелкните Разработчик > Visual Basic, чтобы запустить Редактор Visual Basic (VBE), равное место хранения макросов.

  2. В левой области Обозревателя проектов можно найти личную книгу макросов. Если оно не отображается, перейдите в представление > Окно проекта.

  3. Дважды щелкните папку Проект VBA (PERSONAL.xlsb) > модули > модуль1 и вы найдете пустой Macro1 записанного. Можно удалить или сохранить его для добавления кода к более поздней версии.

Примечание: При записи макроса в новый экземпляр Excel VBA автоматически создать папку модуля и увеличить его номер. Поэтому если у вас уже есть модуль1 и Module2, VBA будет создана Module3. Можно переименовать модули в окне свойств под Обозревателя проектов, лучше отражать выполните макросы в них.

Перемещение макросов на другой компьютер

Файл Personal.xlsb сохраняется в папке автозагрузки системы. Если вы хотите поделиться с другими пользователями макросов, можно скопировать Personal.xlsb в папку "Автозагрузка" на других компьютерах или скопировать некоторые или все его макросы в файл Personal.xlsb на других компьютерах. В Finder выберите Перейти, затем нажмите и удерживайте клавишу Option и выберите пункт Библиотека. В области библиотеки перейти: Контейнеры групп > xyz. Office (где xyz быть текстовая строка, например «UBF8T346G9») > Содержимого пользователя > запуска > Excel. Вставьте свой файл Personal.xlsb в папке Excel.

Если у вас может быть вызвано лишь несколько макросов, которые вы хотите поделиться с другими пользователями, их можно отправить книгу, содержащую их. Вы также можете книги доступны на общем сетевом диске или в библиотеке SharePoint Services.

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

support.office.com

Видео: работа с макросами - Excel

Браузер не поддерживает видео. Установите Microsoft Silverlight, Adobe Flash Player или Internet Explorer 9.

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

Содержание курса

Работа с макросами (5:08) Лучший способ автоматизировать выполнение повторяющихся задач в Excel — записать макрос.

Редактирование макроса (3:29) Узнайте, как изменить макрос в специальном текстовом редакторе Visual Basic. Visual Basic — это язык программирования, используемый для записи макросов.

Сохранение макроса (3:09) Чтобы макрос всегда был доступен на компьютере, сохраните его в личной книге макросов — специальной скрытой книге, загружаемой при каждом запуске Excel.

Назначение кнопки макросу (2:55) В этом заключительном видео мы свяжем макрос с кнопкой и сделаем его доступным в двух местах: в настраиваемой группе на вкладке "Разработчик" и на панели быстрого доступа.

Сводка курса Краткое описание ключевых моментов этого курса.

Другие курсы см. на странице Обучение работе с Microsoft Office.

support.office.com