Сборник макросов и функций excel. Как автоматизировать рутинные задачи в Excel с помощью макросов

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Следующие простые примеры макросов Excel иллюстрируют некоторые возможности и приёмы, описанные в самоучителе по Excel VBA .

Макрос Excel: пример 1

Первоначально эта процедура Sub была приведена, как пример использования комментариев в коде VBA. Однако, здесь также можно увидеть, как объявляются переменные, как работают ссылки на ячейки Excel, использование цикла For , условного оператора If и вывод на экран окна сообщения.

"Процедура Sub выполняет поиск ячейки, содержащей заданную строку "в диапазоне ячеек A1:A100 активного листа Sub Find_String(sFindText As String) Dim i As Integer "Целое число типа Integer, используется в цикле For Dim iRowNumber As Integer "Целое число типа Integer для хранения результата iRowNumber = 0 "Просматривает одну за другой ячейки A1:A100 до тех пор, пока не будет найдена строка sFindText For i = 1 To 100 If Cells(i, 1).Value = sFindText Then "Если совпадение с заданной строкой найдено "сохраняем номер текущей строки и выходим из цикла For iRowNumber = i Exit For End If Next i "Сообщаем пользователю во всплывающем окне найдена ли искомая строка "Если заданная строка найдена, указываем в какой ячейке найдено совпадение If iRowNumber = 0 Then MsgBox "Строка " & sFindText & " не найдена" Else MsgBox "Строка " & sFindText & " найдена в ячейке A" & iRowNumber End If End Sub

Макрос Excel: пример 2

Следующая процедура Sub – пример использования цикла Do While . Здесь также можно увидеть, как объявляются переменные, работу со ссылками на ячейки Excel и применение условного оператора If .

"Процедура Sub выводит числа Фибоначчи, не превышающие 1000 Sub Fibonacci() Dim i As Integer "Счётчик для обозначения позиции элемента в последовательности Dim iFib As Integer "Хранит текущее значение последовательности Dim iFib_Next As Integer "Хранит следующее значение последовательности Dim iStep As Integer "Хранит размер следующего приращения "Инициализируем переменные i и iFib_Next i = 1 iFib_Next = 0 "Цикл Do While будет выполняться до тех пор, пока значение "текущего числа Фибоначчи не превысит 1000 Do While iFib_Next < 1000 If i = 1 Then "Особый случай для первого элемента последовательности iStep = 1 iFib = 0 Else "Сохраняем размер следующего приращения перед тем, как перезаписать "текущее значение последовательности iStep = iFib iFib = iFib_Next End If "Выводим текущее число Фибоначчи в столбце A активного рабочего листа "в строке с индексом i Cells(i, 1).Value = iFib "Вычисляем следующее число Фибоначчи и увеличиваем индекс позиции элемента на 1 iFib_Next = iFib + iStep i = i + 1 Loop End Sub

Макрос Excel: пример 3

Эта процедура Sub просматривает ячейки столбца A активного листа до тех пор, пока не встретит пустую ячейку. Значения записываются в массив. Этот простой макрос Excel показывает работу с динамическими массивами, а также использование цикла Do Until . В данном примере мы не будет совершать какие-либо действия с массивом, хотя в реальной практике программирования после того, как данные записаны в массив, над ними такие действия, как правило, совершаются.

"Процедура Sub сохраняет значения ячеек столбца A активного листа в массиве Sub GetCellValues() Dim iRow As Integer "Хранит номер текущей строки Dim dCellValues() As Double "Массив для хранения значений ячеек iRow = 1 ReDim dCellValues(1 To 10) "Цикл Do Until перебирает последовательно ячейки столбца A активного листа "и извлекает их значения в массив до тех пор, пока не встретится пустая ячейка Do Until IsEmpty(Cells(iRow, 1)) "Проверяем, что массив dCellValues имеет достаточный размер "Если нет – увеличиваем размер массива на 10 при помощи ReDim If UBound(dCellValues) < iRow Then ReDim Preserve dCellValues(1 To iRow + 9) End If "Сохраняем значение текущей ячейки в массиве dCellValues dCellValues(iRow) = Cells(iRow, 1).Value iRow = iRow + 1 Loop End Sub

Макрос Excel: пример 4

В этом примере процедура Sub считывает значения из столбца A рабочего листа Лист2 и выполняет с ними арифметические операции. Результаты заносятся в ячейки столбца A на активном рабочем листе. В этом макросе продемонстрировано использование объектов Excel. В частности, производится обращение процедурой Sub к объекту Columns , и показано, как доступ к этому объекту осуществляется через объект Worksheet . Показано так же, что при обращении к ячейке или диапазону ячеек на активном листе, имя этого листа при записи ссылки указывать не обязательно.

"Процедура Sub при помощи цикла считывает значения в столбце A рабочего листа Лист2, "выполняет с каждым значением арифметические операции и записывает результат в "столбец A активного рабочего листа (Лист1) Sub Transfer_ColA() Dim i As Integer Dim Col As Range Dim dVal As Double "Присваиваем переменной Col столбец A рабочего листа Лист 2 Set Col = Sheets("Лист2").Columns("A") i = 1 "При помощи цикла считываем значения ячеек столбца Col до тех пор, "пока не встретится пустая ячейка Do Until IsEmpty(Col.Cells(i)) "Выполняем арифметические операции над значением текущей ячейки dVal = Col.Cells(i).Value * 3 - 1 "Следующая команда записывает полученный результат в столбец A активного рабочего листа "Имя листа в ссылке указывать нет необходимости, так как это активный лист. Cells(i, 1) = dVal i = i + 1 Loop End Sub

Макрос Excel: пример 5

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

"Данный код показывает окно с сообщением, если на текущем рабочем листе "выбрана ячейка B1 Private Sub Worksheet_SelectionChange(ByVal Target As Range) "Проверяем выбрана ли ячейка B1 If Target.Count = 1 And Target.Row = 1 And Target.Column = 2 Then "Если ячейка B1 выбрана, выполняем необходимое действие MsgBox "Вы выбрали ячейку B1" End If End Sub

Макрос Excel: пример 6

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

"Процедура Sub присваивает аргументам Val1 и Val2 значения ячеек A1 и B1 "из рабочей книги Data.xlsx, находящейся в папке C:\Documents and Settings Sub Set_Values(Val1 As Double, Val2 As Double) Dim DataWorkbook As Workbook On Error GoTo ErrorHandling "Открываем рабочую книгу с данными Set DataWorkbook = Workbooks.Open("C:\Documents and Settings\Data") "Присваиваем переменным Val1 и Val2 значения из заданной рабочей книги Val1 = Sheets("Лист1").Cells(1, 1) Val2 = Sheets("Лист1").Cells(1, 2) DataWorkbook.Close Exit Sub ErrorHandling: "Если файл не найден, пользователю будет предложено поместить искомый файл "в нужную папку и после этого продолжить выполнение макроса MsgBox "Файл Data.xlsx не найден! " & _ "Пожалуйста добавьте рабочую книгу в папку C:\Documents and Settings и нажмите OK" Resume End Sub

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

Макрос можно записать двумя способами:

  • автоматически;
  • вручную.

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

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

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

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

Открывается окно настройки записи макроса. Тут можно указать любое имя макроса, если установленное по умолчанию вас не устраивает. Главное, чтобы имя это начиналось с буквы, а не с цифры. Также, в названии не должно быть пробелов. Мы оставили название по умолчанию – «Макрос1».

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

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

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

Когда все настройки выполнены, жмем на кнопку «OK».

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

Для примера, запишем простейшее арифметическое действие: сложение содержимого трёх ячеек (=C4+C5+C6).

После этого, жмем на кнопку «Остановить запись». Эта кнопка преобразовалась из кнопки «Запись макроса», после включения записи.

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

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

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

Можно поступить ещё проще, и не вызывать даже окно выбора макросов. Мы же помним, что записали сочетание «горячих клавиш» для быстрого вызова макроса. В нашем случае, это Ctrl+М. Набираем данную комбинацию на клавиатуре, после чего макрос запускается.

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

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

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

Открывается Microsoft Visual Basic (VBE) – среда, где происходит редактирование макросов.

Запись каждого макроса начинается с команды Sub, а заканчивается командой End Sub. Сразу же после команды Sub указывается имя макроса. Оператор «Range(«…»).Select» указывает выбор ячейки. Например, при команде «Range(«C4»).Select» выбирается ячейка C4. Оператор «ActiveCell.FormulaR1C1» используется для записи действий в формулах, и для других расчетов.

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

Range(«C3»).Select
ActiveCell.FormulaR1C1 = «11»

Выражение «ActiveCell.FormulaR1C1 = «=R[-3]C+R[-2]C+R[-1]C»» заменим на «ActiveCell.FormulaR1C1 = «= R[-4]C+R[-3]C+R[-2]C+R[-1]C»».

Закрываем редактор, и запускаем макрос, как и в прошлый раз. Как видим, вследствие введенных нами изменений была добавлена ещё одна ячейка с данными. Она также была включена в расчет общей суммы.

В случае, если макрос слишком большой, его выполнение может занять значительное время. Но, путем внесения ручного изменения в код, мы можем ускорить процесс. Добавляем команду «Application.ScreenUpdating = False». Она позволит сохранить вычислительные мощности, а значит ускорить работу. Это достигается путем отказа от обновления экрана во время выполнения вычислительных действий. Чтобы возобновить обновление после выполнения макроса, в его конце пишем команду «Application.ScreenUpdating = True»

Добавим также команду «Application.Calculation = xlCalculationManual» вначале кода, а в конце кода дописываем «Application.Calculation = xlCalculationAutomatic». Этим мы вначале макроса отключаем автоматический пересчет результата после каждого изменения ячеек, а в конце макроса – включаем. Таким образом, Excel подсчитает результат только один раз, а не будет его постоянно пересчитывать, чем сэкономит время.

Написание кода макроса с нуля

Продвинутые пользователи могут выполнять не только редактирование и оптимизацию записанных макросов, но и записывать код макросов с нуля. Для того, чтобы приступить к этому, нужно нажать на кнопку «Visual Basic», которая расположена в самом начале ленты разработчика.

После этого, открывается знакомое нам окно редактора VBE.

Программист пишет там код макроса вручную.

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

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

Макрос 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

Похожие публикации