ПРЕДЛАГАЮ КОЛЛЕГАМ

О.А. Житкова, Т.И. Панфилова
Москва

VBA в приложении к Excel, Word и Power Point

Продолжение. Начало в № 1, 2/2006

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Практическая работа № 3–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–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. Привяжите эти макросы к графическим объектам.

Практическая работа № 3–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. Привяжите созданный макрос к графическому объекту.

Практическая работа № 3–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. Проверьте работу макросов.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Продолжение в следующем номере.