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

На этом занятии рассматриваются следующие вопросы:

·         Понятие макроса.

·         Запись простых макросов.

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

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

·         Назначение макроса графическим изображениям.

·         Удаление макросов из списка макросов.

·         Ограниченность макросов.

Понятие макроса

Прежде чем приступить к написанию программ на VBA, воспользуемся простой возможностью создания программы (макроса) на языке VBA с использованием MacroRecorder.

MacroRecorder — это стандартное средство записи макросов в Excel, посредством которого можно записывать последовательность действий пользователя и получать соответствующий код (программу) на VBA. Программа, созданная с помощью MacroRecorder, называется макросом.

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

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

Каждому макросу дается имя, а для быстрого запуска макроса можно создать или присвоить ему “горячую” клавишу (клавишу, по нажатию на которую будет производиться запуск макроса). После запуска макрос будет автоматически выполнен тем приложением, в котором он создан и запущен. При выполнении макроса компьютер воспроизведет все действия пользователя.

Макрос — это именованная последовательность заданных пользователем команд и действий, хранящаяся в форме программы на языке VBA.

Запись макросов в приложении Excel

Для работы с макросами в приложении Excel имеется специальная панель.

Рис. 1. Панель для работы с макросами

Для вызова этой панели необходимо выполнить последовательно команды меню: “Вид | Панель инструментов | Visual Basic”.

Для записи макроса необходимо:

1.      Нажать на кнопку “Запись макроса” на панели Visual Basic.

2.      В диалоговом окне “Запись макроса” присвоить имя макросу. (В имени макроса первым символом должна быть буква, не допускается использование пробелов.)

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

4.      Нажать кнопку “Остановить запись”.

Пример

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

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

Это действие в Excel можно выполнить с помощью стандартной функции МАКС, но мы продемонстрируем на этом примере, как сохранить последовательность действий пользователя и на их основе создать макрос.

Выполните следующие действия:

1.      Откройте новую книгу.

2.      Создайте таблицу по приведенному образцу.

3.      В ячейке D10 наберите “Максимальная масса в граммах”. Курсор после набора текста может находиться в любой ячейке, кроме ячейки, в которой мы хотим получить результат. На панели Visual Basic нажмите на кнопку “Запись макроса”. Появится диалоговое окно “Запись макроса”:

Рис. 2. Окно “Запись макроса”

Введите вместо названия “Макрос1” название макроса “Алмаз” и нажмите “ОК”. На экране появится панель инструментов “Остановить запись”. Теперь все производимые нами действия будут записываться до тех пор, пока не будет нажата эта кнопка. Не выполняйте никаких лишних действий. Все ваши действия фиксируются и записываются.

Установите курсор в ячейку E10. В этой ячейке должен появиться результат.

4. Выполните команду “Вставка | Функция”. Появится диалоговое окно “Мастер функций”. В окне “Категории функций” выберите “Статистические”. Затем выберите функцию МАКС. Появится диалоговое окно, в первой строке которого необходимо указать диапазон, в котором мы будем искать максимальное значение, — E2:E9. Нажмите “ОК”.

5. Щелкните на кнопке “Остановить запись” на панели Visual Basic. Запись макроса завершена.

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

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

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

Рис. 3. Диалоговое окно функции МАКС

Затем:

1. Установите курсор в любую ячейку листа.

2. Выполните команду “Сервис | Макрос | Макросы”. Появится диалоговое окно, показанное 

на рис. 4:

Рис. 4. Диалоговое окно “Макрос”

3. Выделите макрос “Алмаз” и щелкните по кнопке “Выполнить”. В ячейке E10 появится результат.

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

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

Выделим макрос “Алмаз” и щелкнем по кнопке “Изменить”. Откроется окно редактора.

Рис. 5. Использование редактора Visual Basic для просмотра
и редактирования кода VBA

При записи макроса выполнялись всего два действия. Сначала мы установили курсор в ячейку E10. На языке VBA этому действию соответствует строка:

Range("E10").Select

Затем мы вызвали функцию МАКС и в качестве аргумента указали диапазон ячеек E2:E9:

ActiveCell.FormulaR1C1 = "=MAX(R[-8]C:R[-1]C)"

Полученный код можно редактировать непосредственно в редакторе Visual Basic. Изменим размер шрифта в ячейке E10 на 16:

Range("E10").Font.Size = 16

Можно также изменить цвет шрифта. Например, изменим цвет текста в ячейке на красный:

Range("E10").Font.ColorIndex = 3.

Значения, которые может принять свойство Font.ColorIndex, изменяются от 1 до 56.

После внесенных изменений текст макроса стал следующим:

Sub Алмаз()

' Алмаз Макрос

Range("E10").Select

ActiveCell.FormulaR1C1 = "=MAX(R[-8]C:R[-1]C)"

Range("E10").Font.Size = 16

Range("E10").Font.ColorIndex = 3

End Sub

Закройте окно редактора, вернитесь на лист Excel, удалите содержимое ячейки E10 и запустите макрос на выполнение.

Назначение макроса графическим изображениям

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

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

Используя панель “Рисование”, нарисуем на листе любую автофигуру. Щелкнем правой кнопкой мыши на изображении и в открывшемся контекстном меню выберем команду “Назначить макрос”. Отобразится диалоговое окно “Назначить макрос объекту”. Затем необходимо выбрать макрос “Алмаз” и щелкнуть вне графического изображения, чтобы снять выделение с объекта.

Теперь макрос “Алмаз” можно запустить посредством щелчка на автофигуре.

Рис. 6. Автофигура и кнопка для запуска макроса

Запустить макрос на выполнение можно и другим способом, использовав командную кнопку. Вставить командую кнопку непосредственно на рабочий лист Excel можно с помощью панели инструментов “Формы”. Затем необходимо назначить ей наш макрос.

Удаление макросов из списка макросов

Для того чтобы удалить макрос, надо:

1. Выполнить команду “Сервис | Макрос | Макросы”. Появится уже знакомое диалоговое окно.

2. Выделить макрос, подлежащий удалению, и щелкнуть по кнопке “Удалить”.

3. Подтвердить выполнение операции в специальном окне, которое появится.

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

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

Эти ограничения приводят к необходимости создания программ на VBA.

Практическая работа № 1 “Годовая температура”

Порядок работы:

1. Откройте Excel.

2. Переименуйте “Лист1” в лист “Температура”.

3. Составьте таблицу “Годовая температура”.

4. В ячейку Е3 поместите значение среднегодовой температуры, рассчитав ее по формуле СРЗНАЧ(В3:D3).

5. Запишите ваши действия в макрос, назвав его “СрГодТемп”.

6. Привяжите макрос к графическому объекту (например, “Кнопка”):

Отформатируйте кнопку и назначьте ей макрос “СрГодТемп”. 

7. Справа от показателя температуры нарисуйте графический объект (кнопку) и назначьте ему макрос, который будет очищать содержимое ячейки Е3.

8. Проверьте работу кнопок “Среднегодовая t” и “Очистить”.

9. Самостоятельно выполните создание кнопок и макросов для подсчета и очистки:

·          Максимальной годовой температуры;

·          Минимальной годовой температуры;

·          Средней температуры по временам года (зимой, весной, летом, осенью);

·          Создайте кнопку “Очистить все” для очистки всех расчетов.

Ваша работа должна выглядеть так, как показано:

Практическая работа № 2 “Протяженность границ РФ”

Порядок работы:

1. Откройте Excel.

2. Переименуйте “Лист2” в лист “Границы”.

3. Составьте таблицу “Протяженность государственной границы РФ, км”.

4. Начиная с ячейки F3, составьте таблицу вопросов и ответов:

5. Справа от таблицы нарисуйте графические объекты, к которым привяжите макросы.

6. Для расчетов используйте формулы:

в ячейке H3: =СУММ(D3:D18);

в ячейке H4: =СУММ(B3:B18);

в ячейке H5: =СУММ(C3:C18);

в ячейке H6: =МАКС(B3:D18);

в ячейке H7: =МИН(B3:D18);

в ячейке H8: =ЕСЛИ(H6=МАКС(H6:H7);B2;ЕСЛИ(H6=МАКС(C3:C18);C2;D2));

в ячейке H9: =ЕСЛИ(H6=МИН(H6:H7);B2;ЕСЛИ(H6=МИН(C3:C18);C2;D2)).

После привязки макросов к графическим объектам и их выполнения получим результат:

7. Выполните последовательно команды меню “Сервис | Параметры | Вкладка | Вид” и установите параметры окна для просмотра формул .

8. Вы увидите все формулы, которые можно было ранее посмотреть только в строке формул.

9. Убрав флажок, восстановите прежнее состояние листа.

10. Создайте макрос, который очистит содержимое ячеек H3–H9, и привяжите его к графическому объекту.

11. Проверьте работоспособность всех макросов, привязанных к графическим объектам.

Практическая работа № 3 “Воспроизводство населения России (%)”

Порядок работы:

1. Откройте Excel.

2. Переименуйте “Лист3” в лист “Рождаемость”.

3. Составьте таблицу “Воспроизводство населения России (%)”.

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

5. Ваш лист должен выглядеть так, как показано вверху справа.

6. После записи макросов ВОПРОС и ОТВЕТ проверьте текст (“Сервис | Макрос | Макросы | Изменить”):

Sub ВОПРОС()

'' ВОПРОС Макрос

' Макрос записан 17.12.2005

'

Range("F15").Select

ActiveCell.FormulaR1C1 = "=AVERAGE(R[–12]C[–4]:R[–11]C[–4])"

Range("F16").Select

ActiveCell.FormulaR1C1 = "=AVERAGE(R[–11]C[–4]:R[–5]C[–4])"

Range("F17").Select

ActiveCell.FormulaR1C1 = "=AVERAGE(R[–14]C[–3]:R[–13]C[–3])"

Range("F18").Select

ActiveCell.FormulaR1C1 = "=AVERAGE(R[–13]C[–3]:R[–7]C[–3])"

Range("F19").Select

ActiveCell.FormulaR1C1 = "=AVERAGE(R[–16]C[–2]:R[–15]C[–2])"

Range("F20").Select

ActiveCell.FormulaR1C1 = "=AVERAGE(R[–15]C[–2]:R[–9]C[–2])"

Range("F21").Select

ActiveCell.FormulaR1C1 = "=MIN(R[–18]C[–4]:R[–10]C[–4])"

Range("F22").Select

ActiveCell.FormulaR1C1 = "=MIN(R[–19]C[–3]:R[–11]C[–3])"

Range("F23").Select

ActiveCell.FormulaR1C1 = "=MIN(R[–20]C[–2]:R[–12]C[–2])"

Range("F24").Select

ActiveCell.FormulaR1C1 = "=MAX(R[–21]C[–4]:R[–13]C[–4])"

Range("F25").Select

ActiveCell.FormulaR1C1 = "=MAX(R[–22]C[–3]:R[–14]C[–3])"

Range("F26").Select

ActiveCell.FormulaR1C1 = "=MAX(R[–23]C[–2]:R[–15]C[–2])"

End Sub

Sub ОЧ()

'' ОЧ Макрос

' Макрос записан 17.12.2005 (*)

' Range("F15:F26").Select

Selection.ClearContents

End Sub

7. Создайте макросы изменения цвета таблиц на зеленый и изменения шрифта на полужирный или курсив.

8. Привяжите эти макросы к графическим объектам.

Практическая работа № 4 “Количество осадков”

Порядок работы:

1. Откройте Excel.

2. Переименуйте “Лист 4” в лист “Осадки”.

3. Составьте таблицу “Количество осадков (мм)”.

Таблица построена на основании наблюдений метеостанции города N.

4. Определите для всей таблицы в целом:

· Минимальное количество осадков, выпавшее за три года;

· Суммарное количество осадков, выпавшее за три года;

· Среднемесячное количество осадков по итогам трехлетних наблюдений;

· Максимальное количество осадков, выпавшее за месяц, по итогам трехлетних наблюдений;

· Количество засушливых месяцев за все 3 года, в которые выпало меньше 10 мм осадков;

· Количество дождливых месяцев за все 3 года, в которые выпало больше 100 мм осадков.

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

6. Создайте макрос для очистки ячеек с полученными результатами. Привяжите макрос к графическому объекту.

7. Ваш лист должен выглядеть так, как показано справа.

Основная таблица располагается в диапазоне B5–E18. Начиная с ячейки G5, находится дополнительная таблица вопросов и ответов (см. табл.).

Ячейка C18 содержит формулу =СУММ(C6:C17);

Ячейка D18 содержит формулу =СУММ(D6:D17);

Ячейка E18 содержит формулу =СУММ(E6:E17);

Подготовьте макрос для нахождения максимального и минимального количества осадков за 3 года. Привяжите созданный макрос к графическому объекту.

Ячейка H6 содержит формулу =МАКС(C6:E17);

Ячейка H7 содержит формулу =МИН(C6:E17);

Подготовьте макрос для нахождения суммарного и среднемесячного количества осадков за 3 года. Привяжите созданный макрос к графическому объекту.

Ячейка H8 содержит формулу =СУММ(C6:E17);

Ячейка H9 содержит формулу =СРЗНАЧ(C6:E17);

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

Ячейка H10 содержит формулу =СУММ(C6:E17);

Ячейка H11 содержит формулу =СРЗНАЧ(C6:E17);

Подготовьте макрос для очистки диапазона ячеек H5–H11. Привяжите созданный макрос к графическому объекту.

Практическая работа № 5 “Работа с автоформой”

Порядок работы:

1. Откройте Excel.

2. Переименуйте “Лист 5” в лист “Список класса”.

3. Составьте таблицу и заполните первую строку:

4. Подготовьте автоформу для заполнения таблицы:

· Установить указатель мыши на заполненную таблицу;

· Выполнить команду меню “Данные | Форма”. Появится форма, которую надо заполнить.

5. Создайте макрос вызова автоформы и привяжите его к графическому объекту.

6. Заполните таблицу:

7. Создайте макрос сортировки таблицы по фамилии и имени (с помощью выделения диапазона ячеек В1–F16), привяжите его к геометрическому объекту.

8. Создайте макрос сортировки по годам и фамилиям, привяжите его к геометрическому объекту.

9. Создайте макрос (с привязкой к графическому объекту) изменения цвета и шрифта в диапазоне:

· A1–F1 — полужирный шрифт, кегль 16, зеленый фон, буквы белые;

· А2–А16 — розовый фон;

· В2–В16 — голубой фон;

· С2–С16 — серый фон;

· D2–D16 — розовый фон;

· E2–E16 — голубой фон;

· F2–F16 — серый фон.

10. Создайте макрос (с привязкой к графическому объекту) возврата в исходное состояние.

11. Проверьте работу макросов.

Практическая работа №6 “Преступления в сфере высоких технологий”

Порядок работы:

1. Откройте Excel.

2. Переименуйте “Лист 6” в лист “Преступления”.

3. Составьте таблицу “Преступления в сфере высоких технологий”.

4. Определите для всей таблицы в целом:

·         Суммарное количество преступлений за 1998, 1999, 2000 и 2001 годы (заполните строку “Итого по преступлениям в сфере высоких технологий”);

·         Максимальное количество преступлений за 4 года;

·         Минимальное количество преступлений за 4 года;

·         Суммарное количество проникновений в чужие компьютерные сети за все 4 года;

·         Суммарное количество распространения вредоносных программ за все 4 года;

·         Суммарное количество нарушений работы ЭВМ и баз данных за все 4 года;

·         Год с наибольшим количеством преступлений по итоговому результату;

·         Год с наименьшим количеством преступлений по итоговому результату;

·         Наиболее распространенный вид преступных деяний.

Результаты представьте в виде дополнительной таблицы.

Литература

1.       Газета "Информатика" Издательского дома "Первое сентября" № 3 2006 г.