Создание и использование макросов Электронные таблицы EXCEL
Методические указания
для выполнению лабораторных работ
Часть 1
Санкт-Петербург
Общие указания к лабораторным работам
Запуск программы
Запуск программы Microsoft Excel осуществляется ПускèПрограммыè Microsoft Excel или с помощью соответствующего ярлыка на рабочем столе.
Отчетность по работе
Студент по результатам каждой лабораторной работы должен представить отчет и файл; отчет выполняется в рукописном виде и должен содержать исходные данные и результаты работы.
Файл следует сохранить папке V:\N_группы\фамилия_студента.Имя файла: ЛабРаб#_задание# (где символ # обозначает номер лабораторной работы и задания).
Примечание. Папка Мои документы при завершении сеанса пользователя очищается, поэтому студент, сохранивший файл в папке Мои документы, при следующем входе в систему не обнаружит свой файл и работа не будет засчитана. Будьте внимательны!
Лабораторная работа 1
Создание типовых шаблонов для расчетов
Цель работы: вычисление по формулам, использование абсолютной адресацией (2 способа), оформление документа, создание шаблонов, защита ячеек листа
Задание 1. Вычисления и построение диаграмм
Заполнить таблицу, выполнить вычисления. Вычисляемые значения – жирнымкурсивным шрифтом.
При выполнении задания необходимо:
- при вводе табельного номера должен обеспечиваться ввод нулей в первой позиции;
- для вычисления стоимости затрат рублях использовать абсолютную адресацию, использовать 2 способа задания абсолютного адреса (с помощью символа $ в адресе ячейки и присвоении ячейке имени Курс).
Построить диаграмму, иллюстрирующую числовые данные.
Закрыть для ввода все ячейки, за исключением числовых данных, используя функции защиты листа.
Таб. номер
| Фамилия, инициалы
| Месяцы
|
| Январь
| Февраль
| Март
| Всего
| Всего, р
| Курс
|
| Николаев А.Л.
| ? 1 236
| ? 456
| ? 852
| ? 2 544
| 85 376,64р.
| 33,56р.
|
| Петров П.Д.
| ? 145
| ? 896
| ? 987
| ? 2 028
| 68 059,68р.
|
|
| Семенова Е.Г.
| ? 789
| ? 742
| ? 456
| ? 1 987
| 66 683,72р.
|
|
| Устинов К.Ф.
| ? 785
| ? 258
| ? 852
| ? 1 895
| 63 596,20р.
|
|
| Петров В.Д.
| ? 963
| ? 896
| ? 789
| ? 2 648
| 88 866,88р.
|
|
| Итого
| ? 3 918
| ? 3 248
| ? 3 936
| ? 11 102
|
|
|
| Итого, р
| 131 488,08р.
| 109 002,88р.
| 132 092,16р.
| 372 583,12р.
|
|
|
Задание 2. Использование абсолютной адресации и констант
Заполнить таблицу, выполнить вычисления. Вычисляемые значения – жирнымкурсивным шрифтом.
При выполнении задания необходимо:
- для вычисления расхода и стоимости топлива использовать абсолютную адресацию;
- константы (цену и расход топлива) разместить на отдельном листе, присвоить данному листу имя Константы;
- ячейкам с константами (цена и расход топлива) присвоить имена Цена и Расход соответственно.
Закрыть для ввода все ячейки, за исключением числовых данных (пробег на начало дня при поступлении автомобиля в эксплуатацию – 01.09.03 и на конец каждого дня), используя функции защиты листа.
Расходы на эксплуатацию автомобиля
|
|
|
|
|
|
| Дата
| Пробег, км
| Суточный пробег
| Расход топлива, л
| Стоимость топлива
| На начало дня
| На конец дня
| 01.09.03
| 15 800
| 16 250
|
| 38,3
| 478,13р.
| 02.09.03
| 16 250
| 16 500
|
| 21,3
| 265,63р.
| 03.09.03
| 16 500
| 16 800
|
| 25,5
| 318,75р.
| 04.09.03
| 16 800
| 17 325
|
| 44,6
| 557,81р.
| 05.09.03
| 17 325
| 18 000
|
| 57,4
| 717,19р.
| 06.09.03
| 18 000
| 18 256
|
| 21,8
| 272,00р.
| 07.09.03
| 18 256
| 18 380
|
| 10,5
| 131,75р.
| 08.09.03
| 18 380
| 19 000
|
| 52,7
| 658,75р.
| 09.09.03
| 19 000
| 19 257
|
| 21,8
| 273,06р.
| 10.09.03
| 19 257
| 19 500
|
| 20,7
| 258,19р.
| 11.09.03
| 19 500
| 19 800
|
| 25,5
| 318,75р.
| 12.09.03
| 19 800
| 20 500
|
| 59,5
| 743,75р.
| 13.09.03
| 20 500
| 21 000
|
| 42,5
| 531,25р.
| 14.09.03
| 21 000
| 21 300
|
| 25,5
| 318,75р.
|
Константы
|
|
| Наименование
| Значение
| Примечание
| Цена
| 12,50р.
| Бензин А92 на ПТК
| Расход
| 8,5
| литров на 100 км в летний период
|
Лабораторная работа 2
Сводные таблицы
Ввести данные, выполнить вычисления и сформировать сводные таблицы (распределение поступления оборудования в отделы). При вычислениях использовать абсолютную адресацию.
Учет поступления оборудования
|
|
|
|
|
|
|
| Дата
| Отдел
| Наименование
| Цена, $
| Кол
| Стоимость, $
| Стоимость, р
| 10.01.03
| Администрация
| Монитор
| $125
|
| $125
| 3 820р.
| 10.01.03
| Отдел кадров
| Сканер
| $89
|
| $178
| 5 440р.
| 10.01.03
| Служба безопасности
| Принтер
| $210
|
| $840
| 25 670р.
| 15.01.03
| Технический отдел
| Системный блок
| $450
|
| $1 350
| 41 256р.
| 15.01.03
| Администрация
| Монитор
| $125
|
| $500
| 15 280р.
| 20.01.03
| Администрация
| Сканер
| $89
|
| $89
| 2 720р.
| 21.01.03
| Отдел кадров
| Принтер
| $210
|
| $420
| 12 835р.
| 01.02.03
| Технический отдел
| Системный блок
| $450
|
| $2 250
| 68 760р.
| 01.02.03
| Технический отдел
| Монитор
| $125
|
| $500
| 15 280р.
| 15.02.03
| Отдел кадров
| Принтер
| $210
|
| $630
| 19 253р.
| 15.02.03
| Технический отдел
| Сканер
| $89
|
| $356
| 10 879р.
| 02.03.03
| Администрация
| Принтер
| $210
|
| $420
| 12 835р.
| 02.03.03
| Служба безопасности
| Монитор
| $125
|
| $500
| 15 280р.
| 25.03.03
| Технический отдел
| Системный блок
| $450
|
| $900
| 27 504р.
| 25.03.03
| Служба безопасности
| Сканер
| $89
|
| $89
| 2 720р.
|
|
|
|
|
|
|
| Курс $
|
|
|
|
|
|
| 30,56р.
|
|
|
|
|
|
|
Сформировать сводные таблицы, показывающие:
- распределение поступления оборудования в отделы;
- поступление оборудования по датам
Указание.
- Сводные таблицы формируются: Данные->Сводные_таблицы.
- Начинает работать мастер сводных таблиц.
- Шаги 1 и 2 проходим «по умолчанию».
- На шаге 3 нажимаем кнопку МАКЕТ. В макете сводной таблицы буксируем поля исходной таблицы на макет сводной.
- На шаге 4 выбираем место расположения сводной таблицы. По умолчанию – на новом листе.
Отдел
| Администрация
|
|
|
|
|
|
|
|
|
|
| Сумма по полю Стоимость, р
| Дата
|
|
|
|
| Наименование
| 10.01.2003
| 15.01.2003
| 20.01.2003
| 02.03.2003
| Общий итог
| Монитор
|
|
|
|
|
| Принтер
|
|
|
|
|
| Сканер
|
|
|
|
|
| Общий итог
|
|
|
|
|
|
Наименование
| Монитор
|
|
|
|
|
|
|
|
|
|
| Сумма по полю Стоимость, р
| Дата
|
|
|
|
| Отдел
| 10.01.2003
| 15.01.2003
| 01.02.2003
| 02.03.2003
| Общий итог
| Администрация
| 3 820р.
| 15 280р.
|
|
| 19 100р.
| Служба безопасности
|
|
|
| 15 280р.
| 15 280р.
| Технический отдел
|
|
| 15 280р.
|
| 15 280р.
| Общий итог
| 3 820р.
| 15 280р.
| 15 280р.
| 15 280р.
| 49 660р.
|
Наименование
| Принтер
|
|
|
|
|
|
|
|
|
|
| Сумма по полю Стоимость, р
| Дата
|
|
|
|
| Отдел
| 10.01.2003
| 21.01.2003
| 15.02.2003
| 02.03.2003
| Общий итог
| Администрация
|
|
|
| 12 835р.
| 12 835р.
| Отдел кадров
|
| 12 835р.
| 19 253р.
|
| 32 088р.
| Служба безопасности
| 25 670р.
|
|
|
| 25 670р.
| Общий итог
| 25 670р.
| 12 835р.
| 19 253р.
| 12 835р.
| 70 593р.
|
Лабораторная работа 3
Использование логических функций
- Произвести расчет заработной платы (Начислено) сотрудникам компании в соответствии с отработанными рабочими днями и установленным дневным тарифом.
- Рассчитать премию, используя логическую функцию ЕСЛИ., исходя из условия - премия выплачивается, если сотрудник отработал 15 и более дней.
- Произвести расчет налога (13% от общей суммы) и рассчитать сумму К выдаче.
- Закрыть для ввода все ячейки, за исключением числовых данных (отработано дней), используя функции защиты листа.
Начисление заработной платы в августе 2003 г.
| |
|
|
|
|
|
|
| | Таб. номер
| Фамилия, инициалы
| Отработано, дней
| Начислено
| Премия
| Налог 13%
| К выдаче
| | |
| Николаев А.Л.
|
|
|
|
|
| |
| Петров П.Д.
|
|
|
|
|
| |
| Семенова Е.Г.
|
|
|
|
|
| |
| Устинов К.Ф.
|
|
|
|
|
| |
| Петров В.Д.
|
|
|
|
|
| |
|
|
|
|
|
|
| |
|
|
|
|
|
|
| |
|
|
|
|
|
|
| | Тариф
| Премия
|
|
|
|
|
| | 350р.
| 1 000р.
|
|
|
|
|
| |
|
|
|
|
|
|
| |
|
| |
Лабораторная работа 4
Подбор параметра
Постановка задачи.
- У студента имеется некоторая сумма свободная денег (S), которую он хочет положить на вклад в банк, допустим S=1000р.
- Банк выплачивает ежемесячно доход (p в %) от суммы вклада на последний день месяца, допустим p=0.8% в месяц.
- Студент положил деньги в банк 1 января[1] текущего года, а 31 декабря того же года получил доход по вкладу. Рассчитать сумму дохода D в программе Excel
- На полученный доход студент хочет к новогоднему столу купить коробку конфет, допустим, коробка конфет стоит K=65р.
Определить
- Сколько коробок конфет можно купить на полученный доход (количество может быть только целым), рассчитать сдачу.
- В результате того, что при покупке осталась сдача, очевидно, что часть денег на вкладе «не работали», т.е. можно было положить либо меньшую сумму, либо большую и, тем самым, купить на 1 коробку конфет больше. Поэтому необходимо произвести подбор параметра – определить сумму, при вложении которой дохода хватит на покупку 1 или 2 коробок конфет.
- Произвести подбор параметра – дохода банка.
Подбор параметра в Microsoft Excel производится следующим образом:
- активизируем ячейку, в которой должен быть подобран параметр;
- в меню выбираем Сервис - > Подбор параметра;
- в появившемся диалоговом окне указываем требуемое значение (в данном случае 1 или 2);
- указываем адрес ячейки, в которой должен быть изменен параметр (в данном случае – начальный вклад или процент банка).
Лабораторная работа 5
Создание функций пользователя
Задание 1. Функция, автоматизирующая расчет времени в пути для самолета
Запустите Excel
Введите таблицу по образцу, предложенному ниже.
| A
| B
| C
| D
|
| Направление
| Время отправления
| Время прибытия
| Время в пути
|
| Москва
| 10:30
| 11:35
| |
| Челябинск
| 13:15
| 15:50
| |
| Владивосток
| 18:00
| 0:10
| |
| Мурманск
| 23:40
| 1:10
| |
Присвойте ячейкам в столбцах 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 введите созданную вами функцию, выбрав ее из категории Определенные пользователем. Проверьте работоспособность созданной вами функции.
В результате таблица должна иметь следующий вид:
| A
| B
| C
| D
|
| Направление
| Время отправления
| Время прибытия
| Время в пути
|
| Москва
| 10:30
| 11:35
| 1:05:00
|
| Челябинск
| 13:15
| 15:50
| 2:35:00
|
| Владивосток
| 18:00
| 0:10
| 6:10:00
|
| Мурманск
| 23:40
| 1:10
| 1:30:00
|
Задание 2. Функция, автоматизирующая расчет времени в пути для поезда
Запустите Excel. Введите таблицу по образцу, предложенному ниже.
| A
| B
| C
| D
| E
|
| Направление
| Время отправления
| Время прибытия
| Число полных суток
| Время в пути
|
| Москва
|
|
|
|
|
| Челябинск
|
|
|
|
|
| Владивосток
|
|
|
|
|
| Мурманск
|
|
|
|
|
Присвойте ячейкам в столбцах 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 данные - число полных суток.
| A
| B
| C
| D
| E
|
| Направление
| Время отправления
| Время прибытия
| Число полных суток
| Время в пути
|
| Москва
| 10:30
| 11:35
|
|
|
| Челябинск
| 13:15
| 15:50
|
|
|
| Владивосток
| 18:00
| 0:10
|
|
|
| Мурманск
| 23:40
| 1:10
|
|
|
Запустите редактор 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 введите созданную вами функцию, выбрав ее из категории Определенные пользователем. Проверьте работоспособность созданной вами функции.
В результате таблица должна иметь следующий вид:
| A
| B
| C
| D
| E
|
| Направление
| Время отправления
| Время прибытия
| Число полных суток
| Время в пути
|
| Москва
| 10:30
| 11:35
|
| 1:05:00
|
| Челябинск
| 13:15
| 15:50
|
| 50:35:00
|
| Владивосток
| 18:00
| 0:10
|
| 126:10:00
|
| Мурманск
| 23:40
| 1:10
|
| 25:30:00
|
Лабораторная работа 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
|