Макросы для экселя готовые. Избежание выбора ячеек и диапазонов. Написание макросов в Excel

Добрый день!

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

Как вы знаете из собственного опыта, при работе с макросом есть очень много «рутинны», то есть производятся одни и те же операции и действия которые нужны для получения результата, это могут быть заполнение однотипных таблиц ну или бланков, обработка данных, похожие как близнецы еженедельные, ежемесячные отчёты, и т.д. А вот использование макросов позволит вам производить эти действия в автоматическом режиме, используя возможности Excel на полную катушку, скидая эти рутинные и монотонные операции на мощные плечи Excel. Также причиной использования макросов может быть добавления нужных возможностей, которые еще не реализованы в стандартных функциях Excel (например, вывод , сбор данных на одном листе и прочее).

Если вы никогда не слышали о макросе, то самым точным его определением будет таким, это действия которые запрограммированы на определённую последовательность и записаны в среде программирования на языке Visual Basic for Applications (VBA) . Запуск макроса может производиться многократно и это заставит Excel выполнять любую последовательность необходимых нам действий, которые вручную выполнять нам просто не нравится или не хочется. Несмотря на великое множество языков программирования для всего комплекса Microsoft Office стандартом является именно VBA и он работает в любом приложении офисного пакета.

Итак, создать макрос в Excel возможно 2 способами:

Создать макрос в Excel с помощью макрорекордера

Для начала проясним, что собой представляет макрорекордер и при чём тут макрос.

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

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

• Записать макрорекордер может только то, что может пощупать, а значит записывать действия он может только в том случае, когда используются кнопки, иконки, команды меню и всё в этом духе, такие варианты как для него недоступна;
• В случае, когда в период записи была допущена ошибка, она также запишется. Но можно кнопкой отмены последнего действия, стереть последнюю команду которую вы неправильно записали на VBA;
• Запись в макрорекордере проводится только в границах окна MS Excel и в случае, когда вы закроете программу или включите другую, запись будет остановлена и перестанет выполняться.

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

Следующим шагом в работе с макрорекордером станет настройка его параметров для дальнейшей записи макроса, это можно произвести в окне «Запись макроса» , где:

• поле «Имя макроса» — можете прописать понятное вам имя на любом языке, но должно начинаться с буквы и не содержать в себе знаком препинания и пробелы;
• поле «Сочетание клавиш» — будет вами использоваться, в дальнейшем, для быстрого старта вашего макроса. В случае, когда вам нужно будет прописать новое , то эта возможность будет доступна в меню «Сервис» — «Макрос» — «Макросы» — «Выполнить» или же на вкладке «Разработчик» нажав кнопочку «Макросы» ;
• поле «Сохранить в…» — вы можете задать то место, куда будет сохранен (но не послан) текст макроса, а это 3 варианта:
  • «Эта книга» — макрос будет записан в модуль текущей книги и сможет быть выполнен только в случае, когда данная книга Excel будет открыта;
  • «Новая книга» — макрос будет сохранен в тот шаблон, на основе которого в Excel создается пустая новая книга, а это значит, что макрос станет доступен во всех книгах, которые будут создаваться на этом компьютере с этого момента;
  • «Личная книга макросов» — является специальной книгой макросов Excel, которая называется «Personal.xls» и используется как специальное хранилище-библиотека макросов. При старте макросы из книги «Personal.xls» загружаются в память и могут быть запущены в любой книге в любой момент.
• поле «Описание» — здесь вы можете описать, что и как должен делать макрос, для чего он создавался и какие функции несет, это чисто информативное поле, что называется на память.

После того как вы запустили и записали свой макрос, выполнив все нужные действия, запись можно прекратить командой «Остановить запись» и ваш макрос с помощью макрорекордера будет создан.

Создать макрос в Excel в редакторе Visual Basic.

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

Запуск редактора программ на VBA происходит по-разному, в зависимости от версии вашей программы Excel:

• в версиях Excel 2003 и более старше , нам нужно в меню «Сервис» , выбрать пункт «Макрос» и нажать «Редактор Visual Basic» ;
• в версиях Excel 2007 и более новее , нам нужно на вкладке «Разработчик» нажать кнопку «Редактор Visual Basic» . В случае когда вы не находите эту вкладку вам нужно ее активировать выбрав пункт меню «Файл» — «Параметры» — «Настройка ленты» и в диалоговом окне флажком активируем вкладку «Разработчик» .

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

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

Все наши макросы будут храниться в так называемых программных модулях . В любой из книг любой версии Excel нам доступно создание любых программных модулей в любом количестве и размещать в них все созданные нами макросы. Один модуль в состоянии содержать в себе любое количество нужных вам или созданных макросов. Модули доступны в окне «Project Explorer» и размещаются в верхнем левом углу редактора макросов (так же его можно вызвать комбинацией клавиш CTRL+R ).

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

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

Рассмотрим на примере работающего макроса :

• Все макросы в обязательном порядке будут начинаться с оператора Sub , после которого следует имя вашего макроса и список аргументов в скобочках. В случаях, когда аргументы отсутствуют скобки нужно оставить пустыми;
• В обязательном порядке все макросы заканчиваются оператором End Sub ;
• Данные что находятся между операторами Sub и End Sub , является телом макроса, которое будет работать при запуске макроса. В примере, макрос проверяет и при вводе данных находит их в списке базы данных и , выводя указанное по критериям значение.

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

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

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

Данный способ доступен для любой из версий MS Excel и заключается он в том, что мы вынесем кнопку прямо на наш рабочий лист как графический объект. Для этого вам нужно:

• В MS Excel 2003 и более старше переходите в меню «Вид» , выбираете «Панель инструментов» и нажимаете кнопку «Формы» .
• В MS Excel 2007 и более новее вам нужно на вкладке «Разработчик» открыть выпадающее меню «Вставить» и выбрать объект «Кнопка» .

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

Как создать пользовательские функции на VBA

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

К примеру, для создания пользовательской функции налога на добавленную стоимость, он же НДС, нам нужно открыть наш редактор VBA и добавить новый модуль, выбрать в меню «Insert» пункт «Module» и ввести туда текст для нашей функции: Следует заметить, что главное отличие функции от макроса это заголовок Function заменило Sub и в наличии заполненный список аргументов, в нашем примере это Summa . После того как наш код будет введен, он станет доступен в стандартном окне Мастера функций, который находится в меню «Формулы» , пункт «Вставить функцию» .
И выбираем категорию «Определенные пользователем» в которой и будет отображаться наша написанная функция «NDS» .
После выбора нашей функции можно установить курсор на ячейку с аргументом, в которой будет содержаться та сумма, для которой мы считаем НДС, всё происходит, как и с обычной функцией.
А на этом у меня всё! Я очень надеюсь что статья о том как создать макрос в 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 в окне проектов и дважды щелкните по появившейся ветке Module 1 . Редактор откроет окно с кодом, как изображено на картинке.

Здесь можно редактировать сгенерированный код, который был записан при работе в 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.

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

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

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

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

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

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

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

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

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

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

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

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

Рассмотрим более подробно примеры создания в Excel 2007, 2013:

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

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

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

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

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

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

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

Для этого следуйте нижеприведенной инструкции:

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

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

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

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

Работа с книгами

Макрос 1. Создание новой рабочей книги с нуля
Макроc 2. Сохранение книги при изменении определенной ячейки/диапазона
Макрос 3. Сохранение рабочей книги перед закрытием
Макрос 4. Защита рабочего листа в книге перед закрытием
Макрос 5. Снятие защиты с листа при открытии Excel-файла
Макрос 6. Открыть книгу на нужном листе
Макрос 7. Открытие конкретной книги, определенной пользователем
Макрос 8. Определяем, открыта ли книга
Макрос 9. Определяем, существует ли книга в папке
Макрос 10. Обновляем все связи в открытых книгах
Макрос 11. Закрываем сразу все книги
Макрос 12. Открыть все рабочие книги в папке
Макрос 13. Распечатать все книги в папке
Макрос 14. Не даем закрыть книгу до тех пор, пока не заполнит ячейку
Макрос 15. Создаем резервную копию текущей книги с сегодняшней датой

Работа с листами

Макрос 16. Добавляем новый рабочий лист и присваиваем имя
Макрос 17. Удалить все листы, кроме активного
Макрос 18. Скрыть все, кроме активного рабочего листа
Макрос 19. Отобразить все листы книги
Макрос 20. Перемещение рабочих листов
Макрос 21. Сортировка листов по названию
Макрос 22. Группа листы по Цвету ярлычка
Макрос 23. Скопировать лист в новую книгу
Макрос 24. Создать новую рабочую книгу для каждого листа
Макрос 25. Печать листов
Макрос 26. Защитить все листы
Макрос 27. Снять защиту со всех листов
Макрос 28. Создание оглавления
Макрос 29. Изменение масштаба изображения рабочего листа с помощью двойного щелчка
Макрос 30. Выделить столбец активной строки

Выделение и изменение диапазонов

Макрос 31. Выбор и форматирование диапазона
Макрос 32. Создание и выбор именованных диапазонов
Макрос 33. Перебор с помощью ряда клеток
Макрос 34. Выбор и форматирование диапазонов
Макрос 35. Вставка пустых строк в диапазоне
Макрос 36. Отобразить все скрытые строки и столбцы
Макрос 37. Удаление пустых строк
Макрос 38. Удаление пустых столбцов
Макрос 39. Выбор и форматирование всех формул в рабочем журнале
Макрос 40. Найдите и выберите первую пустую строку или столбец
Макрос 41. Применить дополнительный цвет заливки
Макрос 42. Отсортировать диапазоны по двойному щелчку
Макрос 43. Ограничение диапазона прокрутки в той или иной области
Макрос 44. Автоматически задать область печати листа

Работа с данными

Макрос 45. Копирование и вставка диапазона
Макрос 46. Преобразование всех формул в диапазоне в значения
Макрос 47. Преобразуем текстовых значений в числовые
Макрос 48. Преобразование тире в минус
Макрос 49. Удаляем лишние пробелы из всех ячеек в диапазоне
Макрос 50. Отсекаем слева 5 знаков в каждой ячейки диапазона
Макрос 51. Добавляем в ячейку недостающие нули
Макрос 52. Заменить пустые ячейку нулём
Макрос 53. Добавление текста в начало или конец ячейки
Макрос 54. Создание макроса преобразования данных
Макрос 55. Очистка данных (непечатаемые символы)
Макрос 56. Выделим дубликаты в диапазоне данных
Макрос 57. Скрываем повторяющиеся строки
Макрос 58. Выборочно скрываем стрелки автофильтра
Макрос 59. Копируем отфильтрованные строки в новую книгу
Макрос 60. Создание нового листа для каждого элемента в Автофильтре
Макрос 61. Показать отфильтрованные столбцы в строке состояния

Работа со сводными таблицами

Макрос 62: Создание обратной совместимости сводной таблицы
Макрос 63. Обновление всех сводных таблиц книги
Макрос 64. Создание «описи» всех сводной таблицы книги
Макрос 65. Создаем все сводные таблицы, используя тот же кэш данных
Макрос 66. Скрываем все промежуточные итоги в сводной таблице
Макрос 67. Изменяем названия данных всех полей сводной
Макрос 68. Принудительное суммирование для всех данных сводной
Макрос 69. Применить числовой формат для всех элементов данных
Макрос 70. Сортировка полей сводной в алфавитном порядке
Макрос 71. Применить пользовательскую сортировку к элементам данных
Макрос 72: Ставим защиту на сводную таблицу
Макрос 73. Применять ограничения сводного поля
Макрос 74. Автоматическое удаление листов с детализацией сводной
Макрос 75. Печать сводной таблицы для каждого элемента фильтра
Макрос 76. Создание нового файла для каждого элемента фильтра
Макрос 77. Готовим диапазон данных для сводной таблицы

Работа с диаграммами и графиками

Макрос 78. Изменение размера диаграмм на рабочем листе
Макрос 79. Привязываем график к определенному диапазону
Макрос 80. Создание набора бессвязных диаграмм
Макрос 81. Печать всех диаграмм на рабочем листе
Макрос 82. Отмечаем лучшее и худшее значение на
Макрос 83. Одинаковые цвета для значений на разных диаграммах
Макрос 84. Соответствие цвета диаграмм цвету диапазонов

Отправка писем из Excel

Макрос 85. Отправка активной книги почтой (вложение)
Макрос 86. Отправка диапазон значений в качестве вложения
Макрос 87. Отправка одного листа в качестве вложения
Макрос 88. Отправить письмо с ссылкой на наши файлы
Макрос 89: Рассылка писем с добавлением адресов в наш список контактов
Макрос 90. Сохранение всех вложений в отдельной папке
Макрос 91. Сохранение определенных вложений в папку

Взаимодействие с другими приложениями Office

Макрос 92. Запуск запроса доступа из Excel



Макрос 96. Сжатие базы данных Access из Excel
Макрос 97. Отправка данных Excel в документ в формате Word
Макрос 98. Делаем Слияние с документом в формате Word
Макрос 99. Отправка данных Excel в презентации PowerPoint
Макрос 100. Отправка Excel диаграмм в PowerPoint презентации
Макрос 101. Преобразование рабочей книги в презентации PowerPoint

Взаимодействие с другими приложениями Office
научимся работать с Word, Access и PowerPoint
Макрос 92. Запуск запроса доступа из Excel
Макрос 93. Запуск макроса Access из Excel
Макрос 94. Открытие отчета Access из Excel
Макрос 95. Открытие формы доступа из Excel

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

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

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

Макрос в Microsoft Office (да, этот функционал работает одинаково во многих приложениях пакета Microsoft Office) – это программный код на языке программирования (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. Иконка Стоп (маленький квадратик) указывает на то, что включен режим записи макроса. Нажатие на неё остановит запись. И наоборот, когда режим записи не включен, в этом месте находится иконка для включения записи макроса. Нажатие на неё даст тот же результат, что и включение записи через меню.

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

• =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 .