МУ-лаб-Информ_№1-6-готов

90

2.Задание

Выполнить задания в соответствии с вариантом в таблице выбора варианта. Открыть Microsoft Excel.

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

Задание 2. Перейти на 2­й лист и назвать «Задание 2». Построить диаграммы в соответствии указанными данными в таблицах и типами диаграмм.

Задание 3. Перейти на 3­й лист и назвать «Задание 3». Выполнить расчеты с использованием логических, финансовых и математических формул.

Задание 4. Перейти на 4­й лист и назвать «Задание 4». Введите данные таблицы, выполните сортировку по указанным столбцам и установите автофильтр. Постройте сводную таблицу указанного вида.

Таблица выбора варианта:

Первая буква

фамилии

				Последняя цифра зачетной книжки
	0	1	2	3	4	5	6	7	8	9
А Л Ф 1		2	3	1	3	2	2	1	3	3
Б М Х 3		3	2	1	1	2	1	2	2	1
В Н Ц	2	1	2	3	3	2	1	3	1	2
Г О Ч	1	3	1	2	2	1	2	3	2	3
Е П Ш 3		2	3	2	1	2	3	1	3	2
Ж Р Щ	2	1	3	1	3	3	1	2	1	3
З С Э	3	3	1	2	1	2	3	1	3	1
И Т Ю	1	2	1	2	3	1	2	3	2	3
К У Я	3	3	1	2	1	2	1	3	1	3

Вариант – 1.

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

­5­7 санитарок;

­8­10 медсестер;

­10­12 врачей;

­1 заведующий аптекой;

­3 заведующих отделениями;

­1 главный врач;

­1 завхоз;

­1 заведующий больницей.

На некоторых должностях число людей может меняться. Например, зная, что найти санитарок трудно, руководитель может принять решение сократить число санитарок, чтобы увеличить оклад каждой из них. Поэтому заведующий берет за основу оклад санитарки, а все остальные вычисляет через него, т.е. на математическом языке любой оклад является линейной функцией от оклада санитарки: A*C+B, где C – оклад санитарки, а A и B – коэффициенты, которые определяются

решением совета трудового коллектива. Совет решил, что:
1.	Оклад санитарки принимается за расчетную единицу		A=1	B=0
2.	Медсестра должна получать в 1,5 раза больше санитарки	A=1,5	B=0
3.	Врач – в 3 раза больше санитарки		A=3	B=0
4.	Заведующий отделением – на 30 д.е. больше, чем врач		A=3	B=30
5.	Заведующий аптекой – в 2 раза больше санитарки	A=2	B=0
6.	Завхоз – на 40 д.е. больше медсестры		A=1,5	B=40
7.	Главный врач – в 4 раза больше санитарки	A=4	B=0
8.	Заведующий больницей – на 20 д.е. больше главного врача		A=4	B=20

						91
Коэф.	Коэф.	Должность	Зарплата	Кол­во	Суммар­	Зарплата
					ная
A	B		сотрудни	сотруд­	зарплата	Санитар­
			ка	ников		ки
1,00	0,00	санитарка		5		150,00
1,50	0,00	медсестра		8
3,00	0,00	врач		10
3,00	30,00	зав.отделением		3
2,00	0,00	зав.аптекой		1
1,50	40,00	завхоз		1
4,00	0,00	главврач		1
4,00	20,00	зав.больницей		1

В столбцы A,B,C,E и H этой таблицы следует ввести необходимые данные. В ячейку H3 ввести зарплату санитарки. Рассчитать зарплату 1­го сотрудника в ячейке D3, остальные значения в диапазоне D4:D10 получить копированием формулы (с помощью маркера заполнения либо буфера обмена). Суммарная зарплата получается по формуле «Зарплата сотрудника»*«Кол­во сотрудников». Рассчитать значение ячейки F3 по заданной формуле и получить значения диапазона F4:F10 аналогично (копированием с помощью маркера заполнения или с помощью буфера обмена).

Вариант – 2.

Заполнить таблицу данных. Столбцы Модель, Название и Стоимость заполняются по исходной таблице. Столбец Цена рассчитывается по формуле: Цена=Стоимость *1,3+НДС. Столбец Сумма рассчитывается по формуле Сумма = Цена*Количество.

	Факсы		НДС: ***
Модель Название		Стоимость Цена	Количество Сумма
F100G	Персональный	1608	567
F150G	Персональный	1840	420
F200G	Персональный Плюс	1730	634
F250G	Персональный Плюс	2076	432
F300G	Деловой	2551	297
F350G	Деловой	2761	437
F400G	Профессиональный	3513	324
F450G	Профессиональный	3615	289
F500G	Профессиональный Плюс	4678	211
F550G	Профессиональный Плюс	5614	108

Рассчитать цену и сумму 1­й модели, остальные значения получить копированием формулы (с помощью маркера заполнения либо буфера обмена).

Указать для столбцов Стоимость, Цена и Сумма денежный формат.

Вариант – 3.

Заполнить таблицу данных. Столбцы Модель, Название и Стоимость заполняются по исходной таблице. Столбец Цена рассчитывается по формуле: Цена=Стоимость *Расчетный коэффициент. Столбец Сумма рассчитывается по формуле Сумма = Цена*Количество.

92

		Расчетный коэффициент		***
		Ксероксы
Модель Название		Стоимость Цена	Количество Сумма
g100gls	Персональный	827	564
g110gls	Персональный	993	632
g200gls	Персональный Плюс	1430	438
g210gls	Персональный Плюс	1716	645
g300gls	Деловой	2470	437
g310gls	Деловой	2965	534
g400gls	Профессиональный	4270	409
g410gls	Профессиональный	5124	395
g420gls	Профессиональный Плюс	6415	298
g500gls	Профессиональный Плюс	7378	328

Рассчитать цену и сумму 1­й модели, остальные значения получить копированием формулы (с помощью маркера заполнения либо буфера обмена).

Указать для столбцов Стоимость, Цена и Сумма денежный формат.

Задание 2.

Вариант – 1. Составьте следующую таблицу и диаграмму к ней: а)

Социальная структура населения Москвы на 1897г

Категория населения	%
Занятые в промышленности	38,6
Прислуга, поденщики	19,4
Занятые в торговле	14
Занятые на транспорте	6,5
рантье и пенсионеры	10,9
чиновники и военные	4,6
люди свободных профессий	6
б)

Социальнаяструктура населенияМосквына1897г% Занятыев промышленности

Прислуга,поденщики

Занятыевторговле

Занятыенатранспорте

рантьеипенсионеры

Использование домашнего компьютера

(исследования фирмы Microsoft)

Вид работы	%
Игры	38,6
Обработка текстов	19,4
Ведение финансов	14
Домашняя работа	6,5
Образование	10,9
Домашний бизнес	4,6

45

40

35

30

25

20

15

10

5

0

ы

в

в

та

е

ес

гр

то

о

ни

с

нс

бо

а

н

И

к

з

те

на

а

ов

би

р

а

и

я

з

й

ф

а

тк

е

я

бр

и

бо

н

О

шн

ни

а

а

е

м

ш

ма

О

бр

ед

о

о

В

Д

Д

93

Вариант – 2.

а)

Республика Казахстан
Население	16721113
Мужчины	8115857
Женщины	8605256
Дети	5247906
Взрослые	10474265
Старики	998942
Мальчики	2659586
Мужчины	5158149
Дедушки	298122
Девочки	2588320
Женщины	5316116
Бабушки	700820
Горожане	9605740
Сельчане	7115373
Городские мужчины	4576954
Городские женщины	5028786
Сельские мужчины	3538903
Сельские женщины	3576470

б)

Казахстан ­ данные о населении

18000000

16000000

14000000

12000000

10000000

8000000

6000000

4000000

2000000

0

е

ые

и

и

н

е

ы

ны

и

к

н

н

ины

ик

и

ы

а

сл

ш

ин

чи

е

щ

ьч

у

щ

ж

жч

л

о

д

ро

муж

е

н

л

Де

н

Ж

В

Ма

Ж

у

с

Го

м

е

зр

е

кие

кие

На

с

с

Го

д

Сель

о

р

	Таблица 1
Численность	РОССИЯ	КАЗАХСТАН
Население	148310174	16721113
Мужчины	69562474	8115857
Женщины	78747700	8605256
Дети	33314753	5247906
Взрослые	98913416	10474265
Старики	16082005	998942

Таблица 1

160000000

РОССИЯ

Численность

140000000

КАЗАХСТАН

40000000

120000000

100000000

80000000

60000000

20000000

0

е

н

ны

е

и

ы

е

и

ни

и

е

и

ы

и

Д

т

сл

р

к

щ

л

ч

о

а

се

уж

н

р

т

е

з

С

а

Ж

В

Н

М

Население

94

Вариант ­ 3 а)

Таблица 2

Проц. Состав РОССИЯ КАЗАХСТАН

Мужчины %	47%	49%
Женщины %	53%	51%
Дети %	22%	31%
Взрослые %	67%	63%
Старики %	11%	6%

Процентный состав населения

Мужчины %	1
Женщины %

Дети %

0

КАЗАХСТАН

Взрослые %

РОССИЯ

Старики %

РОССИЯ КАЗАХСТАН

б)

Численность

	населения
	Германии
Год	Население
1975	61991500
1976	61644600
1977	61442039
1978	61352700
1979	61321663
1980	61439342
1981	61658000
1982	61712700
1983	61546100
1984	61306700
1985	61049300
1986	61020500
1987	61140500
1988	61238100
1989	61715100
1990	62679000
1991	79753227
1992	80274564
1993	80974632

	Население Германии в 1975­1993гг.
100000000
80000000
60000000
40000000
20000000
0
5	7	9			1			3	5			7	9			1			3
97	97	97		8			8		98		8		98		9			9
			9			9				9				9			9
1	1	1	1			1			1	1			1	1			1

95

Задание 3. Вариант – 1.

Имеется таблица №1, содержащая количество осадков в мм, построенная на основе наблюдений метеостанции г.Екатеринбурга.

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

1.Минимальное количество осадков, выпавшее за 3 года.

2.Суммарное количество осадков по итогам 3­х летних наблюдений.

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

4.Максимальное количество осадков по итогам 3­х летних наблюдений.

5.Количество засушливых месяцев за все 3 года, в которые выпало меньше 10мм осадков. Данные оформить в виде отдельной таблицы(№2):

Те же данные определить для каждого года и оформить в виде отдельной таблицы №3. Дополнительно для каждого года определить:

1.Количество месяцев в году с количеством осадков в пределах (>20; <80) мм.

2.Количество месяцев с количеством осадков вне нормы (<10; >100) мм.

	A	B	C	D	E	F	G
1	Таблица					Таблица №2
2	№1	1992	1993	1994		Данные за 1992­1994гг.
3	Январь	37,2	34,5	8		Макс. кол­во осадков за 3 года
						(мм)
4	Февраль	11,4	51,3	1,2		Мин. кол­во осадков за 3 года
						(мм)
5	Март	16,5	20,5	3,8		Суммарное кол­во осадков за 3
						года
6	Апрель	19,5	26,9	11,9		Среднемесячное кол­во осадков
						за 3 года
7	Май	11,7	45,5	66,3		Кол­во засушливых месяцев за 3
8	Июнь	129,1	71,5	60		года
9	Июль	57,1	152,9	50,6
10	Сентябрь	85,7	74,8	79,9
11	Октябрь	86	14,5	74,9
12	Ноябрь	12,5	21	56,6
13	Декабрь	21,2	22,3	9,4
14

Вариант – 2.

Решение квадратного уравнения. В поля, помеченные на рисунке, следует вводить числовые значения. Расчет корней квадратного уравнения должен проводиться для любых чисел. Если решений нет, то в соответствующую ячейку должно быть выведено сообщение: «Решений нет». В ячейках стобца Н при отсутствии данных (a, b, или c), следует вывести «Неполные данные».

1	A	B	D E	F	G H
		C
	Полное квадратное		Расчет дискриминанта
		уравнение
2

3

96

4	Вводимые значения:				D=B^2­4*A*C
5	a	b	c		рассчитываемые
					значения:
6	***	***	***		квадрат коэффициента	***
					b
7					произведение	***
					4ac
8	значение 1­го корня:			***	дискриминант	***
9	значение 2­го корня:			***	корень из	***
					дискриминанта
10					произведение	***
					2a

Вариант – 3.

Дана таблица с итогами экзаменационной сессии.

	A	B	C	D	E
	№	Ф. И.О.	Математи	Эконом.	Информатик	Ср.	Коэф. Сти­
			ка	теория	а	балл		пенди
1	1.	Макаров				s	k	я
2						***	***	***
		С.П.
3	2.	...	...			***	***	***
4	3.

Составить электронную таблицу, определяющую стипендию по следующему правилу:

По рассчитанному среднему баллу за экзаменационную сессию (s) вычисляется повышающий коэффициент (k), на который затем умножается минимальная стипендия (m).

Повышающий коэффициент вычисляется по правилу:

если 3 £ s <4, т о k=1.2, если 4 £ s < 4.5, т о k=1.5, если 4.5 £ s < 5, т о k=1.8, если s= 5, т о k=2.0.

Если же s<3 или s>5, то стипендия не назначается и поэтому нужно в этом случае коэффициент k вычислять специальным образом, например, присвоить k текст «Неправильные данные».

Задание 4.

Вариант – 1.

Продаж и по сот рудникам по месяцам

Продавец Месяц Кол­во Выручка				Город
Иванов	янв	3	57 435	Москва
Бабкина	янв	98	199 315	Москва
Акбаев	фев	70	98 827	Астана
Воронова	фев	51	98 827	Павлодар
Кралев	сен	60	125 342	Москва

97

Акбаев	сен	86	63 651	Москва
Иванов	окт	43	45 916	Астана
Бабкина	ноя	77	247 196	Москва
Новиков	мар	47	64 415	Павлодар
Иванов	мар	37	140 219	Павлодар
Бабкина	мар	6	181 611	Павлодар
Новиков	май	59	37 816	Париж
Воронова	май	54	37 835	Павлодар
Новиков	июн	33	64 415	Москва
Сорокина	июн	98	99 314	Париж
Акбаев	июл	33	160 812	Астана
Сорокина	дек	62	308 123	Астана
Ясенева	апр	96	35 571	Павлодар
Сорокина	авг	36	81 386	Астана
Иванов	авг	70	85 217	Астана

1.Отсортируйте список по столбцу «Продавец».

2.Создайте сводную таблицу со следующей структурой:

Продавец	(Все)
Сумма по полю Кол­во Город
Месяц	Астана	Москва	Павлодар Париж		Общий итог
янв		101			101
фев	70		51		121
мар			90		90
апр			96		96
май			54	59	113
июн		33		98	131
июл	33				33
авг	106				106
сен		146			146
окт	43				43
ноя		77			77
дек	62				62
Общий итог	314	357	291	157	1119

98

Вариант ­ 2

Продаж и по сот рудникам по месяцам

Продавец Месяц Кол­во Выручка				Город
Иванов	янв	3	57 435	Москва
Бабкина	янв	98	199 315	Москва
Акбаев	фев	70	98 827	Астана
Воронова	фев	51	98 827	Павлодар
Кралев	сен	60	125 342	Москва
Акбаев	сен	86	63 651	Москва
Иванов	окт	43	45 916	Астана
Бабкина	ноя	77	247 196	Москва
Новиков	мар	47	64 415	Павлодар
Иванов	мар	37	140 219	Павлодар
Бабкина	мар	6	181 611	Павлодар
Новиков	май	59	37 816	Париж
Воронова	май	54	37 835	Павлодар
Новиков	июн	33	64 415	Москва
Сорокина	июн	98	99 314	Париж
Акбаев	июл	33	160 812	Астана
Сорокина	дек	62	308 123	Астана
Ясенева	апр	96	35 571	Павлодар
Сорокина	авг	36	81 386	Астана
Иванов	авг	70	85 217	Астана

1.Отсортируйте список по столбцу «Месяц».

2.Создайте сводную таблицу со следующей структурой:

Город	(Все)
Сумма по полю Выручка Месяц
Продавец	янв	фев		мар
Акбаев			98827
Бабкина	199315			181611
Воронова			98827
Иванов	57435			140219
Кралев
Новиков				64415
Сорокина
Ясенева
Общий итог	256750		197654	386245

99

Вариант ­ 3

Продаж и по сот рудникам по месяцам

Продавец Месяц Кол­во Выручка				Город
Иванов	янв	3	57 435	Москва
Бабкина	янв	98	199 315	Москва
Акбаев	фев	70	98 827	Астана
Воронова	фев	51	98 827	Павлодар
Кралев	сен	60	125 342	Москва
Акбаев	сен	86	63 651	Москва
Иванов	окт	43	45 916	Астана
Бабкина	ноя	77	247 196	Москва
Новиков	мар	47	64 415	Павлодар
Иванов	мар	37	140 219	Павлодар
Бабкина	мар	6	181 611	Павлодар
Новиков	май	59	37 816	Париж
Воронова	май	54	37 835	Павлодар
Новиков	июн	33	64 415	Москва
Сорокина	июн	98	99 314	Париж
Акбаев	июл	33	160 812	Астана
Сорокина	дек	62	308 123	Астана
Ясенева	апр	96	35 571	Павлодар
Сорокина	авг	36	81 386	Астана
Иванов	авг	70	85 217	Астана

1.Отсортируйте список по столбцу «Город».

2.Создайте сводную таблицу со следующей структурой:

Содержание отчёта

Отчёт должен содержать:

1.Задание к работе.

2.Описание к выполнению каждого задания.

3.Файл со страницами «Задание 1», «Задание 2», «Задание 3», «Задание 4». На каждой странице должно быть выполнено задание в соответствии с вариантом задания.

Контрольные вопросы

1.Перечислить назначение и функциональные возможности Excel.