Варианты заданий

1. Создайте макрос, преобразующий температуру по шкале Цельсия в градусы Фаренгейта.

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

Занятие 11 (2 ч., лабораторное)

Тема: Связь между файлами Excel.

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

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

При заполнении ведомостей для учета результатов сдачи экзаменов использовать оценки 2, 3, 4, 5, н/я (неявка). В ведомости производить подсчет общего количества каждой оценки.

В ведомости начисления стипендии производить подсчет количества сданных экзаменов, среднего балла по результатам сдачи экзаменов, а также суммы стипендии. Сумму стипендии рассчитывать следующим образом (при условии, что сданы все экзамены):

• если средний балл больше либо равен 4,5, то выплачивается 50%-ная надбавка к базовой стипендии;

• если средний балл больше либо равен 3 но меньше 4,5, то выплачивается базовая стипендия;

• если средний балл меньше 3, то стипендия не выплачивается.

Ход работы

1. Создайте следующую таблицу на Листе1 новой рабочей книги Excel:

	A	B		C		D		E		F		G		H		I		J
1		Экзаменационная ведомость
2
3	Дисциплина		Математика
4	Группа
5
6	№	Фамилия		№ зачетной		Оценка		Подпись		5		4		3		2		н/я
7	п/п	И.О.		книжки				экзаменатора
8	1	Ахвердов С.И.		45782		н/я
9	2	Борисова В.П.		45786		5
10	3	Гаранина О.И.		45811		3
11	4	Дибров Л.Н.		45812		2
12	5	Косиненко Н.С.		45816		4
13	6	Кулаков В.М.		45820		3
14	7	Носков Р.А.		45822		5
15	8	Орлов В.Д.		45823		2
16	9	Степанов М.Ф.		45824		4
17	10	Федорова А.Л.		45825		5
18
19		"отлично"
20		"хорошо"
21		"удовлетворительно"
22		"неудовлетворительно"
23		"н/я"
24		Итого
25

Столбцы F, G, H, I, J – дополнительные, они необходимы для подсчета общего количества каждой оценки.

2. В ячейку F8 вставьте формулу

= ЕСЛИ(D8=5;1;0).

Скопируйте формулу из ячейки F8 в ячейки F9:F17.

3. В ячейку G8 вставьте формулу

= ЕСЛИ(D8=4;1;0).

Скопируйте формулу из ячейки G8 в ячейки G9:G17.

4. В ячейку H8 вставьте формулу

= ЕСЛИ(D8=3;1;0).

Скопируйте формулу из ячейки H8 в ячейки H9:H17.

5. В ячейку I8 вставьте формулу

= ЕСЛИ(D8=2;1;0).

Скопируйте формулу из ячейки I8 в ячейки I9:I17.

6. В ячейку J8 вставьте формулу

= ЕСЛИ(D8=”н/я”;1;0).

Скопируйте формулу из ячейки J8 в ячейки J9:J17.

7. Ячейкам F8:F17 присвойте имя Отлично:

7.1. выделите ячейки F8:F17;

7.2. перейдите в поле имени, которое располагается слева в строке формул, и щелкните левой кнопкой мыши (или Вставка è Имя è Присвоить);

7.3. введите имя и нажмите клавишу Enter.

8. Ячейкам G8:G17 присвойте имя Хорошо.

9. Ячейкам H8:H17 присвойте имя Удовлетворительно.

10. Ячейкам I8:I17 присвойте имя Неудовлетворительно.

11. Ячейкам J8:J17 присвойте имя Неявка.

12. В ячейку C19, для вычисления общего количества отличных оценок, используя кнопку Вставка функции панели инструментов или команду Вставка è Функция…, вставьте формулу

= СУММ(Отлично).

13. В ячейку C20, для вычисления общего количества хороших оценок, используя кнопку Вставка функции панели инструментов или команду Вставка è Функция…, вставьте формулу

= СУММ(Хорошо).

14. В ячейку C21, для вычисления общего количества удовлетворительных оценок, используя кнопку Вставка функции панели инструментов или команду Вставка è Функция…, вставьте формулу

= СУММ(Удовлетворительно).

15. В ячейку C22, для вычисления общего количества неудовлетворительных оценок, используя кнопку Вставка функции панели инструментов или команду Вставка è Функция…, вставьте формулу

= СУММ(Неудовлетворительно).

16. В ячейку C23, для вычисления общего количества неявок, используя кнопку Вставка функции панели инструментов или команду Вставка è Функция…, вставьте формулу

= СУММ(Неявка).

17. В ячейку C24, для вычисления общего количества оценок, используя кнопку Вставка функции панели инструментов или команду Вставка è Функция…, вставьте формулу

= СУММ(C19:C23).

18. Присвойте Листу1 текущей рабочей книги имя Математика: Формат è Лист è Переименовать.

19. Удалите все другие листы из текущей рабочей книги.

Для этого:

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

• для удаления листа используйте команду Правка è Удалить лист.

20. Сохраните текущую рабочую книгу под именем lesson7-m.xls: Файл è Сохранить как…

21. С использованием команды Правка è Переместить/скопировать лист… скопируйте лист Математика в новую рабочую книгу.

Для этого:

• перейдите на лист Математика;

• выберите команду Правка è Переместить/скопировать лист… Отобразится диалоговое окно Переместить или скопировать;

• в диалоговом окне Переместить или скопировать в области в книгу: выберите (новая книга); включите опцию Создавать копию и нажмите на кнопке OK.

22. На скопированном листе в ячейку C3 введите название другой дисциплины – Информатика.

23. В ячейках D8:D17 измените экзаменационные оценки.

24. Присвойте листу Математика текущей рабочей книги имя Информатика: Формат è Лист è Переименовать.

25. Сохраните текущую рабочую книгу под именем lesson7-i.xls: Файл è Сохранить как…

26. С использованием команды Правка è Переместить/скопировать лист… скопируйте лист Информатика в новую рабочую книгу.

27. На скопированном листе в ячейку C3 введите название другой дисциплины – История.

28. В ячейках D8:D17 измените экзаменационные оценки.

29. Присвойте листу Информатика текущей рабочей книги имя История: Формат è Лист è Переименовать.

30. Сохраните текущую рабочую книгу под именем lesson7-h.xls: Файл è Сохранить как…

31. С использованием команды Правка è Переместить/скопировать лист… скопируйте лист История в новую рабочую книгу.

32. На скопированном листе в ячейку C3 введите название другой дисциплины – Статистика.

33. В ячейках D8:D17 измените экзаменационные оценки.

34. Присвойте листу История текущей рабочей книги имя Статистика: Формат è Лист è Переименовать.

35. Сохраните текущую рабочую книгу под именем lesson7-s.xls: Файл è Сохранить как…

36. Создайте следующую таблицу на Листе1 новой рабочей книги Excel:

	A	B	C	D	E
1		Ведомость начисления стипендий
2
3	Базовая стипендия		400,00р.
4
5	№	Фамилия	Сдано	Средний	Сумма
6	п/п	И.О.	экзаменов	балл	стипендии
7	1	Ахвердов С.И.
8	2	Борисова В.П.
9	3	Гаранина О.И.
10	4	Дибров Л.Н.
11	5	Косиненко Н.С.
12	6	Кулаков В.М.
13	7	Носков Р.А.
14	8	Орлов В.Д.
15	9	Степанов М.Ф.
16	10	Федорова А.Л.
17

В ячейках C3 и E7:E16 установите денежный формат: Формат è Ячейки… è вкладка Число.

37. В ячейки C7:C16 вставьте формулы для расчета количества сданных экзаменов каждым студентом.

Для этого:

• в ячейку C7 вставьте формулу

= СЧЁТ('C:\My Documents\[lesson7-m.xls]Математика'!D8;

'C:\My Documents\[lesson7-i.xls]Информатика'!D8;

'C:\My Documents\[lesson7-h.xls]История'!D8;

'C:\My Documents\[lesson7-s.xls]Статистика'!D8).

Приведенная формула соответствует случаю, когда файлы lesson7-m.xls, lesson7-i.xls, lesson7-h.xls и lesson7-s.xls находятся в папке C:\My Documents.

• скопируйте формулу из ячейки C7 в ячейки C8:C16.

38. В ячейки D7:D16 вставьте формулы для расчета среднего балла каждого студента по результатам сдачи экзаменов.

Для этого:

• в ячейку D7 вставьте формулу

= СРЗНАЧ('C:\My Documents\[lesson7-m.xls]Математика'!D8;

'C:\My Documents\[lesson7-i.xls]Информатика'!D8;

'C:\My Documents\[lesson7-h.xls]История'!D8;

'C:\My Documents\[lesson7-s.xls]Статистика'!D8).

Приведенная формула соответствует случаю, когда файлы lesson7-m.xls, lesson7-i.xls, lesson7-h.xls и lesson7-s.xls находятся в папке C:\My Documents.

• скопируйте формулу из ячейки D7 в ячейки D8:D16.

39. В ячейки E7:E16 вставьте формулы для расчета сумм стипендий каждого студента.

Для этого:

• в ячейку E7 вставьте формулу

= ЕСЛИ(И(C7=4;D7>=4,5);$C$3*1,5;ЕСЛИ(И(C7=4;D7>=3);$C$3;0));

• скопируйте формулу из ячейки E7 в ячейки E8:E16.

40. Присвойте Листу1 текущей рабочей книги имя Стипендия: Формат è Лист è Переименовать.

41. Удалите все другие листы из текущей рабочей книги.

Для этого:

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

• для удаления листа используйте команду Правка è Удалить лист.

42. Сохраните текущую рабочую книгу под именем lesson7-stip.xls: Файл è Сохранить как…