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

Электронные таблицы EXCEL

Методические указания

для выполнению лабораторных работ

Часть 1

Санкт-Петербург

Общие указания к лабораторным работам

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

Запуск программы Microsoft Excel осуществляется ПускèПрограммыè Microsoft Excel или с помощью соответствующего ярлыка на рабочем столе.

Отчетность по работе

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

Файл следует сохранить папке V:\N_группы\фамилия_студента.Имя файла: ЛабРаб#_задание# (где символ # обозначает номер лабораторной работы и задания).

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

Лабораторная работа 1

Создание типовых шаблонов для расчетов

Цель работы: вычисление по формулам, использование абсолютной адресацией (2 способа), оформление документа, создание шаблонов, защита ячеек листа

Задание 1. Вычисления и построение диаграмм

Заполнить таблицу, выполнить вычисления. Вычисляемые значения – жирнымкурсивным шрифтом.

При выполнении задания необходимо:

• при вводе табельного номера должен обеспечиваться ввод нулей в первой позиции;
• для вычисления стоимости затрат рублях использовать абсолютную адресацию, использовать 2 способа задания абсолютного адреса (с помощью символа $ в адресе ячейки и присвоении ячейке имени Курс).

Построить диаграмму, иллюстрирующую числовые данные.

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

Задание 2. Использование абсолютной адресации и констант

Заполнить таблицу, выполнить вычисления. Вычисляемые значения – жирнымкурсивным шрифтом.

При выполнении задания необходимо:

• для вычисления расхода и стоимости топлива использовать абсолютную адресацию;
• константы (цену и расход топлива) разместить на отдельном листе, присвоить данному листу имя Константы;
• ячейкам с константами (цена и расход топлива) присвоить имена Цена и Расход соответственно.

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

Лабораторная работа 2

Сводные таблицы

Ввести данные, выполнить вычисления и сформировать сводные таблицы (распределение поступления оборудования в отделы). При вычислениях использовать абсолютную адресацию.

Сформировать сводные таблицы, показывающие:

• распределение поступления оборудования в отделы;
• поступление оборудования по датам

Указание.

1. Сводные таблицы формируются: Данные->Сводные_таблицы.
2. Начинает работать мастер сводных таблиц.
3. Шаги 1 и 2 проходим «по умолчанию».
4. На шаге 3 нажимаем кнопку МАКЕТ. В макете сводной таблицы буксируем поля исходной таблицы на макет сводной.
5. На шаге 4 выбираем место расположения сводной таблицы. По умолчанию – на новом листе.

Лабораторная работа 3

Использование логических функций

1. Произвести расчет заработной платы (Начислено) сотрудникам компании в соответствии с отработанными рабочими днями и установленным дневным тарифом.
2. Рассчитать премию, используя логическую функцию ЕСЛИ., исходя из условия - премия выплачивается, если сотрудник отработал 15 и более дней.
3. Произвести расчет налога (13% от общей суммы) и рассчитать сумму К выдаче.
4. Закрыть для ввода все ячейки, за исключением числовых данных (отработано дней), используя функции защиты листа.

Лабораторная работа 4

Подбор параметра

Постановка задачи.

1. У студента имеется некоторая сумма свободная денег (S), которую он хочет положить на вклад в банк, допустим S=1000р.
2. Банк выплачивает ежемесячно доход (p в %) от суммы вклада на последний день месяца, допустим p=0.8% в месяц.
3. Студент положил деньги в банк 1 января[1] текущего года, а 31 декабря того же года получил доход по вкладу. Рассчитать сумму дохода D в программе Excel
4. На полученный доход студент хочет к новогоднему столу купить коробку конфет, допустим, коробка конфет стоит K=65р.

Определить

1. Сколько коробок конфет можно купить на полученный доход (количество может быть только целым), рассчитать сдачу.
2. В результате того, что при покупке осталась сдача, очевидно, что часть денег на вкладе «не работали», т.е. можно было положить либо меньшую сумму, либо большую и, тем самым, купить на 1 коробку конфет больше. Поэтому необходимо произвести подбор параметра – определить сумму, при вложении которой дохода хватит на покупку 1 или 2 коробок конфет.
3. Произвести подбор параметра – дохода банка.

Подбор параметра в Microsoft Excel производится следующим образом:

• активизируем ячейку, в которой должен быть подобран параметр;
• в меню выбираем Сервис - > Подбор параметра;
• в появившемся диалоговом окне указываем требуемое значение (в данном случае 1 или 2);
• указываем адрес ячейки, в которой должен быть изменен параметр (в данном случае – начальный вклад или процент банка).

Лабораторная работа 5

Создание функций пользователя

Задание 1. Функция, автоматизирующая расчет времени в пути для самолета

Запустите Excel

Введите таблицу по образцу, предложенному ниже.

Присвойте ячейкам в столбцах B, C, D числовой формат Время

Время в числовом формате— это десятичная дробь в интервале от 0 (нуля) до 0,99999999, представляющая время от 0:00:00 (12:00:00 ночи) до 23:59:59 (11:59:59 вечера).

Введите в столбцы B и C данные - время отправления и время прибытия. Время при этом вводится через двоеточие (часы:минуты:секунды).

Запустите редактор VBA последовательностью команд

СервисèМакросèРедакторèVisual Basic

Выполните команду Вставкаèи создайте новый модуль. Выберите его значок в окне проекта, чтобы активизировать окно редактора для создания собственной функции. В открывшемся окне напишите на VBA программу функции Время_Пути для определения времени полета:

Function Время_пути(Вылет, Прилет)

v = Вылет

p = Прилет

If p > v Then t = p - v Else t = 1 + p - v

Время_пути = t

End Function

Активизируйте лист Excel. В ячейку D2 введите созданную вами функцию, выбрав ее из категории Определенные пользователем. Проверьте работоспособность созданной вами функции.

В результате таблица должна иметь следующий вид:

Задание 2. Функция, автоматизирующая расчет времени в пути для поезда

Запустите Excel. Введите таблицу по образцу, предложенному ниже.

Присвойте ячейкам в столбцах B, C числовой формат Время, тип 13:30.

Присвойте ячейкам в столбце E числовой формат Время, тип 37:30:55

Присвойте ячейкам в столбце D – числовой формат Числовой без десятичных знаков.

Время в числовом формате— это десятичная дробь в интервале от 0 (нуля) до 0,99999999, представляющая время от 0:00:00 (12:00:00 ночи) до 23:59:59 (11:59:59 вечера).

Введите в столбцы B и C данные - время отправления, время прибытия. Время при этом вводится через двоеточие (часы:минуты).

Введите в столбец D данные - число полных суток.

Запустите редактор VBA последовательностью команд

СервисèМакросèРедакторèVisual Basic

Выполните команду Вставкаèи создайте новый модуль. Выберите его значок в окне проекта, чтобы активизировать окно редактора для создания собственной функции. В открывшемся окне напишите на VBA программу функции ПоездВпути для определения времени поезда в пути в часах:

Function ПоездВпути(Отправление, Прибытие, Суток)

v = Отправление

p = Прибытие

s = Суток

If p > v Then t = p - v Else t = 1 + p - v

ПоездВпути = t + s

End Function

Активизируйте лист Excel. В ячейку Е2 введите созданную вами функцию, выбрав ее из категории Определенные пользователем. Проверьте работоспособность созданной вами функции.

В результате таблица должна иметь следующий вид:

Лабораторная работа 6

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

Задание 1

1. Запустите Excel

2. Включите режим записи макроса, для чего выберите режим Сервис„Макрос„Начать запись.

В открывшемся меню Запись макроса введите имя макроса, например МакросПроверка, и присвойте ему комбинацию клавиш – Ctrl+z.

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

3. Введите в ячейку А1 первого рабочего листа чистой книги слово Проверка. Введите в ячейку А2 число 26 и число 15 в ячейку В2. Остановите запись макроса нажатием кнопки Остановить запись.

4. Очистите область А1:С3 рабочего листа и вызовите на выполнение макрос МакросПроверка с помощью комбинации клавиш Ctrl+z.

5. Ознакомьтесь с набором рабочих окон редактора VBA и с текстом созданной программы, для чего с помощью команд Сервис„Макрос„Макросы вызовите макрос МакросПроверка и нажмите кнопку Изменить. Аналогичный результат можно получить, выполняя последовательность команд Сервис„Макрос„Редактор Visual Basic.

6. Ознакомьтесь с текстом созданного модуля МакросПроверка. Пример полученного текста представлен ниже.

Sub МакросПроверка()

'

' МакросПроверка Макрос

' Макрос записан 03.11.2003 (NNNN)

'

' Сочетание клавиш: Ctrl+z

'

Range("A1").Select

ActiveCell.FormulaR1C1 = "Проверка"

Range("A2").Select

ActiveCell.FormulaR1C1 = "26"

Range("B2").Select

ActiveCell.FormulaR1C1 = "15"

Range("C2").Select

ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"

Range("C3").Select

End Sub

7. Первая строка представляет собой заголовок созданной процедуры МакросПроверка.

Параметры процедуры отсутствуют. Символом обозначен комментарий. Первая рабочая строка программы вызывает объект Аррliсаtiоп (приложение) с методом Run (выполнить), вызывающим на выполнение объект Range (диапазон) с параметрами начальной установки рабочего листа. Далее производится выделение ячейки А1 текущего рабочего листа и запись в активную ячейку в формате R1C1 текста “Проверка”.

Отметим, что в Ехсеl существует два формата (А1 и R1СI) и два способа адресации (относительная и абсолютная) ячеек памяти. В формате А1 признаком абсолютной адресации является символ “$“. В формате RIСI абсолютный адрес задается индекс рабочей строки и столбца, а при необходимости использования относительной адресации в квадратных скобках указывается относительное смещение со знаком по отношению к текущей рабочей ячейке. Последующие действия программы соответствуют выполненной последовательности при программировании макроса. Как следует из текста, константы и формулы заносятся с использованием метода FormulaR1С1 объекта ActiveCell.

9. Модифицируйте созданную программу. Введите в ее текст описание трех дополнительных переменных:

Dim b, c As Integer

Предварительно описав переменную d1 как строку символов, введите в текст программы оператор ввода данных:

Dim d As String

D = InputBox (“Первый сомножитель”, ”Проверка”)

Преобразуйте значение d из символьного вида к числовому с помощью функции:

b = Val(d)

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

Ниже представлен пример созданной в процессе модернизации макроса программы на языке VВА:

Sub МакросПроверка ()

'

' МакросПроверка Макрос

' Макрос записан 03.11.2003 (APTS)

Dim b, c As Integer

Dim d As String

MsgBox "Умножение", vbOKOnly, “Проверка”

d = InputBox("Первый сомножитель", "Проверка")

b = Val(d)

d = InputBox("Второй сомножитель", "Проверка")

c = Val(d)

Range("A1").Select

ActiveCell.FormulaR1C1 = "Проверка"

Range("A2").Select

ActiveCell.FormulaR1C1 = b

Range("B2").Select

ActiveCell.FormulaR1C1 = c

Range("C2").Select

ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"

End Sub

Задание 2