8. Задания для самостоятельной работы

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

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

Задание 1

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

	A	B	C	D		E		F		G		H
1	Оценки		Математика			Физика				Литература
2	ученика		Дата	Оценка		Дата		Оценка		Дата		Оценка
3	класса:		05.09.05	5		07.09.05		4		05.09.05		4
4	11^а		13.09.05	4		14.09.05		2		18.09.05		3
5	класс		15.09.05	4						03.10.05		5
6	Иванова									24.10.05		4
7	фамилия
8	Петра
9	имя
10
11
12	Количество оценок				3				2				4
13	Средняя оценка				4,3				3,0				4,0
14	Наивысшая оценка				5				4				5
15	Худшая оценка				4				2				3

Все тексты и числа отображаются шрифтами "Times New Roman" с размером 12, кроме подстрочных надписей под классом, фамилией и именем ученика, которые должны быть записаны шрифтом с размером 8 и текстов "Дата" и "Оценка", которые должны быть записаны шрифтом с размером 10.

Класс, фамилия и имя ученика отображаются жирным курсивом.

Названия дисциплин (жирный шрифт) должны центрироваться по двум клеткам (колонки "Дата", "Оценка"). Все остальные данные, кроме текстов в клетках блока A12:A15, также центрируются.

В клетках блоков C3:C10, E3:E10, G3:G10 должен быть задан формат представления числа как даты в виде "ДД.ММ.ГГ". Дата располагается по центру клетки. Количество оценок, полученных учеником (а также дат их получения) и отображаемых в строках с третьей по десятую колонок D, F и H, может быть произвольным, но не должно превышать восьми.

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

Расчетные формулы занесите в клетки блока D12:D15, а затем скопируйте их в блоки F12:F15 и H12:H15. Используйте в формулах функции СЧЁТ, СРЗНАЧ, МИН, МАКС.

Задание 2

Создайте в среде электронной таблицы представленный ниже документ, предназначенный для расчета годовых взносов членов "Клуба предпринимателей города N", а также суммы взносов и среднего размера взноса.

	A	B	C	D	E	F	G
1	Ф. И.О.	Годовой доход (тыс. у. е.)	Размер взноса (у.е.)		Коэффи- циент	Процент дохода	МРОТ (у. е.)
2	Иванов И. П.	230 000	1 700		13	0,71%	7,5
3	Сидоров П. С.	370 000	2 700
4	Петров С. И.	180 000	1 400
5	Ванькин Е. К.	200 000	1 500
6	Соколов К. Т.	450 000	3 300
7		Всего:	10 600
8		Средний размер взноса:	2 120,00

Взнос складывается из двух частей: фиксированной части и части, зависящей от годового дохода предпринимателя.

Фиксированная часть взноса равна произведению минимального размера оплаты труда (МРОТ), указанного в ячейке G2, на коэффициент, заносимый в ячейку E2. Вторая часть взноса составляет указанное в ячейке F2 число процентов годового дохода предпринимателя. Величины обеих частей суммируются и округляются до сотен (число должно оканчиваться двумя нулями).

Средний размер взноса также округляется до сотых.

В представлении чисел в колонках B и C, группы (тройки) разрядов отделяются друг от друга разделителями (пробел или др.).

Расчетные формулы занесите в ячейки C2, C7 и C8. Содержимое ячейки C2 скопируйте в блок ячеек C3:C6. Используйте в формулах функции СРЗНАЧ, СУММ и ОКРУГЛ.

Задание 3

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

	A	B	C	D	E
1	Фамилия И.О.	Год	Прошло кален-		Текущая
2	работника	рождения	дарных лет		дата
3	Иванов П.С.	1951	52		29.09.2003
4	Петров С.И.	1984	19
5	Сидоров И.П.	1976	27

Формулу, необходимую для расчета числа календарных лет, занесите в ячейку C3 и скопируйте в ячейки блока C4:C5. Формулу, позволяющую получить текущую дату, занесите в ячейку E3. Используйте в формулах функции ГОД и СЕГОДНЯ.

Задание 4

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

	A	B	C	D	E
1	Наименование продукции	Подготовка к выпуску
2		Начало	Продолжительность		Завершение
3			Месяцев	Дней
4	Продукт 1	01.10.2003	3	10	11.01.2004
5	Продукт 2	25.12.2003	18	0	25.06.2005
6	Продукт 3	13.06.2003	5	15	28.11.2003

Для каждого вида продукции известна дата начала подготовки к выпуску (колонка B) и продолжительность подготовки в месяцах и днях (колонки C, D).

Расчетную формулу занесите в ячейку E4 и скопируйте в блок ячеек E5:E6. Используйте в формуле функции ДАТА, ГОД, МЕСЯЦ и ДЕНЬ.

Задание 5

Создайте в среде электронной таблицы представленный ниже документ, предназначенный для расчета числа дней, прошедших по истечении заданного числа месяцев (колонка A) и дней (строка 2) после некоторой даты (ячейка H2).

	A	B	C	D	E	F		G		H
1		Дни							Начальная дата
2	Месяцы	5	10	15	20	25				01.10.2003
3	1	36	41	46	51	56
4	3	97	102	107	112	117
5	5	157	162	167	172	177
6	9	279	284	289	294	299

Расчетную формулу занесите в ячейку B3 и скопируйте в блок ячеек B3:F6. Используйте в формуле функции ДАТА, ГОД, МЕСЯЦ и ДЕНЬ.

Задание 6

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

	A	B	C	D	E	F	G	H	I
1	Название	Месячная	Количество месяцев					Стоимость	Скидка
2	газеты	стоимость	1	3	6	12		доставки	(%)
3	Газета Х	100	150	338	590	986		50	2%
4	Дайджест Y	120	170	395	698	1173
5	Журнал Z	300	350	914	1670	2858

Стоимость подписки равна произведению месячной стоимости издания на число месяцев, но в случае подписки более чем на один месяц стоимость уменьшается на число процентов, равное произведению указанной в ячейке I3 величины на количество месяцев уменьшенное на единицу. Кроме того, к стоимости подписки добавляется стоимость доставки, указываемая в ячейке H3. Если после расчета стоимости получается не целое число, то дробная часть отбрасывается.

Расчетную формулу занесите в ячейку C3 и скопируйте в блок ячеек C3:F5. Используйте в формуле только функцию ЦЕЛОЕ.

Задание 7

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

	A	B	C	D	E	F	G		H		I
1		Месяц								Начало эксплуатации		Июнь 2003
2	Год	1	3	6	9	12			Начальная стоимость		10000
3	2003	10000	10000	10000	9400	8800			Снижение за месяц (%)		2,00%
4	2005	6200	5800	5200	4600	4000
5	2007	1400	1000	400	0	0

Известны начальная стоимость автомобиля (ячейка I2), дата начала его эксплуатации (ячейка I1, в которую заносится дата, но отображаются только месяц и год) и процент снижения стоимости за каждый календарный месяц после начала эксплуатации (ячейка I3).

Стоимость автомобиля начинает снижаться по прошествии первого календарного месяца с момента начала эксплуатации (до начала эксплуатации она остается неизменной) и уменьшается по прошествии каждого месяца. Остаточная стоимость не может стать отрицательной.

Расчетную формулу занесите в ячейку B3 и скопируйте в блок ячеек B3:F5. Используйте в формуле только функции ГОД, МЕСЯЦ, МАКС и МИН.

Задание 8

Создайте в среде электронной таблицы представленный ниже документ, предназначенный для расчета стоимости проезда в поезде до городов, находящихся на различном расстоянии от Петрозаводска.

	A	B	C		D		E		F		G	H
1	Стоимость проезда от Петрозаводска
2	Пункт назначения	Расстоя-ние (км)	Цена билета				Тарифы за 1 км пути (у.е.) при расстоянии (км)
3	Вологда	720	200,00				до		400		км	свыше
4	Медвежьегорск	200	60,00				0,30					0,25
5	Москва	1100	295,00
6	Мурманск	1000	270,00
7	Новгород Великий	500	145,00
8	Санкт-Петербург	450	132,50

Цена билета рассчитывается по следующему правилу: если расстояние (целое число километров) не превышает указанного в клетке F3, то стоимость одного километра пути составляет величину, находящуюся в клетке F4, в противном случае стоимость каждого километра пути сверх указанного в клетке F3 определяется величиной, находящейся в клетке H4.

Расчетную формулу занесите в ячейку C3 и скопируйте в блок ячеек C4: C8.

Задание выполните в двух вариантах:

а) используя в расчетной формуле функцию ЕСЛИ;

б) не используя в расчетной формуле функцию ЕСЛИ (пользуйтесь функциями МИН, МАКС).

Задание 9

Создайте в среде электронной таблицы представленный ниже документ, предназначенный для расчета суммарных объемов выпущенной с начала года продукции и сравнения их с требуемым количеством.

	A	B	C	D	E	F		G
1		Квартал							Требуется
2		1-й	2-й	3-й	4-й			10000
3	Выпуск	3000	2500	3000	2000
4	Всего	3000	5500	8500	10000

Расчет ведется путем суммирования объемов выпущенной в текущем квартале и предшествующих ему кварталов продукции (строка 3), но если суммарный объем становится больше указанной в ячейке G2 величины, то отображается значение из ячейки G2.

Расчетную формулу занесите в ячейку B4 и скопируйте в блок ячеек C4:E4. Не используйте в формуле функцию ЕСЛИ (пользуйтесь функциями МИН, МАКС).

Задание 10

В ячейках A1 и B1 электронной таблицы находятся натуральные числа. Если содержимое ячейки A1 в два раза больше содержимого ячейки B1, то значение, отображаемое в ячейках A2 и B2, должно равняться сумме содержимого ячеек A1 и B1, в противном случае – произведению содержимого ячеек A1 и B1.

Формулы, необходимые для расчета, занесите в ячейки A2 и B2.

Задание выполните в двух вариантах:

а) используя в расчетной формуле (ячейка A2) функцию ЕСЛИ;

б) не используя в расчетной формуле (ячейка B2) функцию ЕСЛИ (пользуйтесь функциями ABS, ЗНАК, МИН, МАКС).

Задание 11

Создайте в среде электронной таблицы представленный ниже документ, предназначенный для расчета необходимости выплаты повышенной стипендии студентам.

	A	B	C	D	E	F	G	H	I
1	Ф. И.О.	Наименования дисциплин					Стипендия		Средний
2		Матем.	Информ.	Физика	Химия	Ин. яз.			балл
3	Альтов И. К.	5	4	4	5	5	–		4,5
4	Ерохин Т. Р.	5	5	4	4	4	Положена
5	Зудов Н. Г.	3	5	4	3	4	–
6	Лесина А. Ф.	4	5	5	4	4	Положена
7	Петров С. Л.	5	4	4	5	5	–
8	Яшина К. Д.	5	5	5	5	3	–

Стипендия выплачивается тем, у кого нет оценок, меньших четверки, а средний балл по математике, информатике и физике не ниже указанного в ячейке I3. В случае необходимости выплаты в графе "Стипендия" (колонка H) появляется текст "Положена", в противном случае – прочерк (знак "минус").

Расчетную формулу занесите в ячейку G3 и скопируйте в блок ячеек G4:G8.

Задание выполните в двух вариантах:

а) используя в формуле только функцию ЕСЛИ;

б) не используя в формуле функции И, ИЛИ.

Задание 12

Создайте в среде электронной таблицы представленный ниже документ, предназначенный для расчета цены подержанных автомобилей, состоит из двух частей, расположенных на разных страницах электронной таблицы: основного документа и области справочных данных.

	A	B	C	D	E	F
1	Цена автомобиля на			3 Января 2004 г.
2	Марка автомобиля	Цена нового (у. е.)	Год выпуска	Пробег (км)	Износ (%)	Продажная цена (у. е.)
3	ВАЗ-2111	6300	2002	2 700	8,0%	5800
4	Фольксваген	14700	1997	130 000	47,5%	7700
5	ВАЗ-2101	2400	1987	250 000	100,0%	–
6	Фиат-Типо	11400	1998	120 000	42,0%	6600

	A	B
1	Справочные данные
2	Процент износа за календарный год		Процент износа за каждые полные 10 тыс. км пробега
3	4,00%		1,50%

Износ автомобиля (колонка Е) рассчитывается по следующему правилу: суммируются процент износа, указанный в области справочных данных (ячейка A3 соответствующей страницы) за каждый календарный год, прошедший с года выпуска и процент износа (ячейка B3) за каждые полные 10 000 км пробега (в общей сложности не более 100%). Рассчитанное значение износа округляется до десятых.

Продажная цена рассчитывается по следующему правилу: цена нового автомобиля минус величина износа (для автомобилей с износом 100% вместо новой цены ставится прочерк). Рассчитанное значение цены округляется до сотен.

В ячейку D1 заносится дата (отображается в указанном формате).

Расчетные формулы занесите в ячейки E3 и F3 и скопируйте их в блок ячеек E4:F6. При этом в формуле, заносимой в ячейку E3, не допускается использование функции ЕСЛИ. Используйте в формулах функции МИН, ЦЕЛОЕ, ОКРУГЛ и др.

Задание 13

Создайте в среде электронной таблицы представленный ниже документ, представляющий собой вертикальный справочник – блок клеток A3:E7, поиск в котором ведется по параметрам, заносимым в клетки H3, H4 и H5. Все отображаемые сведения (исключая ячейку H7) заносятся в документ вручную. Поиск в справочнике осуществляется с помощью формулы, заносимой в клетку H7.

	A	B	C	D	E	F	G	H
1	Номера столбцов справочника						Поиск
2	1	2	3	4	5		в справочнике
3	−17	Текст № 1	3,1	Столбец 4-1	−7,2		Код	9
4	−5,8	Текст № 2	3,2	Столбец 4-2	24,8		Номер столбца	5
5	2,1	Текст № 3	3,3	Столбец 4-3	13		Тип поиска	ЛОЖЬ
6	9	Текст № 4	3,4	Столбец 4-4	0
7	22	Текст № 5	3,5	Столбец 4-5	−0,027		Результат	0

Задание выполните в три этапа.

Этап 1

Занесите в ячейку H7 формулу, выполняющую поиск в вертикальном справочнике, используя в формуле только функцию ВПР. Определите, какой результат будет получен в ячейке H7 при занесении в ячейки H3:H5 различных вариантов значений параметров поиска, показанных в таблице ниже. Объясните получаемые результаты.

	Варианты значений параметров
Код	22	2,8	2,8	0	-10
Номер столбца	1	4	3	6	3
Тип поиска	ЛОЖЬ	ИСТИНА	ЛОЖЬ	ИСТИНА	ИСТИНА

Этап 2

Модифицируйте формулу в ячейке H7 таким образом, чтобы при невозможности корректного поиска в справочнике выводилось сообщение "ПОИСК НЕВОЗМОЖЕН". Используйте в формуле функции ВПР, ЕСЛИ и ЕОШИБКА.

Этап 3

Аналогичным образом самостоятельно создайте электронный документ, содержащий горизонтальный справочник с теми же сведениями и организуйте поиск в нем с теми же значениями параметров поиска. Используйте в формуле функции ГПР, ЕСЛИ и ЕОШИБКА.

Задание 14

Создайте в среде электронной таблицы представленный ниже документ, предназначенный для определения по коду предмета и номеру класса оценки, полученной учащимся N-й школы Сидоровым Иваном.

	A	B	C	D	E	F
1	Оценки учащегося N-й школы Сидорова Ивана
3	Код	Наименование	Класс
4	предмета	предмета	8	9	10	11
5	001	Математика	5	4	5	5
6	005	Физика	4	3	4	4
7	013	Информатика	4	5	4	5
8	007	История	3	5	4	3
9	004	Литература	4	3	3	4
10
11		Код предмета	013
12		Класс	9
14		Предмет	Информатика
15		Оценка	5

Вручную в документ заносятся все тексты, номера классов (ячейки C4:F4), справочные данные (ячейки A5:F9), код предмета (ячейка C11) и номер класса (ячейка C12).

Автоматически определяются на основе справочных данных:

• наименование предмета (ячейка C14);

• оценка (ячейка C15).

Задание выполните в три этапа.

Этап 1

Определите, какие формулы необходимо занести в ячейки C14 и С15, чтобы получить результаты, соответствующие указанным в ячейках С11 и С12 данным (при условии их корректности). Используйте в формулах только функцию ВПР.

Этап 2

Модифицируйте формулы таким образом, чтобы в случае ввода недопустимых данных (номер класса, код предмета) в ячейку C11 и/или C12, в ячейках C14 и C15 появлялось сообщение "Ошибка в данных". Используйте в формулах функции ВПР, ЕСЛИ и др.

Задание 15

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

Фамилия И.О.	Дата выдачи	Сумма кредита	Срок (мес.)	Про- цент	Дата возврата	Сумма к возврату
Афонин П. Т.	11.12.98	45000	2	180	11.02.99	58500
Иванов К. Д.	05.01.99	4000	6	165	05.07.99	7300
Сидоров К. Е.	24.08.98	20000	4	180	24.12.98	32000
Петров Н. В.	17.09.98	8000	10	150	17.07.99	18000
Фоменко Л. Г.	30.11.98	8000	12	135	30.11.99	18800

Срок кредита	Сумма кредита (не менее)
(мес.)	0	10000	50000	100000
1	180	185	190	200
2	175	180	185	195
5	165	170	175	185
8	150	155	160	170
12	135	140	145	150

Вручную в документ заносятся:

• фамилия и инициалы клиента;

• дата выдачи кредита;

• сумма кредита;

• срок кредита;

• все справочные данные.

Автоматически должны рассчитываться: процент (годовой) выплат за кредит; дата возврата кредита; сумма к возврату. Годовой процент выплат за кредит рассчитывается на основе справочных данных и зависит от суммы кредита и срока, на который выдан кредит.

Рекомендуем выполнить расчет процента выплат за кредит в три этапа: сначала рассчитать величину процентов в зависимости только от срока кредита (для этого потребуется функция ВПР), затем в зависимости только от суммы кредита (для этого потребуется функция ГПР) и лишь после этого расчет процента в зависимости и от суммы кредита и от срока (для этого можно использовать две указанные выше функции, а также функцию ПОИСКПОЗ).

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

Сумма к возврату определяется как сумма кредита плюс проценты за каждый месяц кредита (процент за месяц определяется как частное от деления годового процента на 12). Сумма округляется до ближайшего целого.

Задание 16

Вручную сформируйте в произвольной области электронной таблицы базу данных, содержащую сведения о работниках некоторой организации (внешний вид базы данных показан ниже). Дополните базу данных произвольными сведениями еще о 15-20 работниках.

Ф. И. О.	Год рождения	Дата принятия на работу	Стаж работы	Должность	Дата выхода в отпуск
Афонин П. Т.	1943	01.07.86	25	Инженер	14.06.99
Иванов К. Д.	1962	19.10.90	8	Слесарь	28.06.99
Сидоров К. Е.	1977	26.01.96	3	Мастер	15.07.99
Петров Н. В.	1980	05.01.98	1	Слесарь	08.02.99
Фоменко Л. Г.	1957	08.04.90	19	Инженер	09.08.99
...	...	...	...	...	...
Яблоков Д. К.	1979	03.04.97	2	Техник	01.12.98

Выполните сортировку строк базы данных так, чтобы строки были упорядочены:

• по фамилиям работников в алфавитном порядке;

• в первую очередь по названиям должностей, во вторую − по дате выхода в отпуск;

• в первую очередь по должности, во вторую − по стажу работы, в третью − по дате принятия на работу.

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

• инженерам;

• всем работникам, чьи фамилии заканчиваются на "ров";

• работникам, родившимся в период с 1940 по 1970 год;

• работникам, принятым на работу до 1990 или после 1995 года;

• инженерам, выходящим в отпуск с июня по август 1999 года.

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

• работникам, родившимся в 40-е или в 60-е годы;

• работникам в должности "Мастер" или "Инженер", выходящим в отпуск в июле 1999 года;

• всем работникам, фамилии которых начинаются с букв от "В" до "П", родившимся в период с 1950 по 1970 годы и принятым на работу в 1980 году или после 1990 года.

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

Используя функции для работы с базой данных (БСЧЁТА, ДМИН, ДМАКС, ДСРЗНАЧ и др.), организуйте подсчет:

• количества работников, принятых на работу до некоторой заданной даты;

• средний стаж работы у инженеров и мастеров (совместно);

• наибольший и наименьший стаж работы у тех, кто занимает должность, отличную от должности "Инженер", и выходит в отпуск либо весной (с апреля по май), либо осенью (с сентября по октябрь);

• количество и суммарный стаж работы работников в должности "Слесарь" и "Мастер", принятых на работу за последние 10 лет.

Задание 17

Создайте в среде электронной таблицы представленный ниже документ, предназначенный для учета междугородных переговоров и расчета их стоимости согласно тарифам, а также для подготовки квитанций

на оплату.

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

Стоимость переговоров
Номер абонента	Код города	Название города	Время разговора	Длительность (мин.)	Стоимость (у. е.)
553313	818	Архангельск	20:00	10	800
7756381	495	Москва	11:25	7	840
5154753	812	С.-Петербург	7:50	12	504
553313	817	Вологда	14:00	3	300
7756384	812	С.-Петербург	17:45	9	540
5533135	495	Москва	21:50	6	504
517480	816	Новгород	6:10	1	52,5
776019	818	Архангельск	9:35	8	640
5533136	812	С.-Петербург	22:40	3	126
775638	818	Архангельск	11:05	10	800
5174801	812	С.-Петербург	8:00	4	168
7760199	495	Москва	12:20	15	1800
7760199	495	Москва	12:55	22	2640

Внешний вид основного документа (страница "Документ")

Счет за оплату междугородных переговоров
Номер телефона:	999999
Фамилия И.О. владельца:	Номер не существует
Число разговоров:	12
Сумма к оплате:	8914,5

Внешний вид счета на оплату (страница "Счет")

Справочные данные
Тарифы Льготы
Код города	Название города	Стоимость 1 мин. (у. е.)		Льготное время с по			Размер льготы (%)
815	Мурманск	110		21:00	8:00		30
816	Новгород	75
817	Вологда	100
812	С.-Петербург	60
495	Москва	120
818	Архангельск	80

Внешний вид справочников "Тарифы" и "Льготы" (страница "Тарифы")

Абоненты
Номер телефона	Ф. И. О. абонента
515475	Кузнецов В. А.
517480	Бердино Н. Ю.
553313	Поляков В. В.
775638	Воронин А. В.
776019	Кузнецова К. С.

Внешний вид справочника "Абоненты" (страница "Абоненты")

Этапы подготовки электронного документа.

Этап 1

В основной документ вручную заносятся следующие сведения:

• номер абонента и код города;

• время начала разговора в формате "часы : минуты";

• длительность разговора в минутах.

Автоматически определяются и рассчитываются в основном документе:

• название города;

• стоимость разговора по следующему правилу:

• если начало разговора не попадает в промежуток льготного времени (справочник "Льготы"), то разговор оплачивается по полному тарифу (справочник "Тарифы");

• если начало разговора попадает на льготное время, то, независимо от времени окончания разговора, стоимость разговора снижается в соответствии с размером льготы, указанной на странице "Тарифы".

Необходимо предусмотреть контроль правильности вносимых в документ номера телефона и кода города. Если номер телефона или код города отсутствуют в справочниках "Тарифы" и "Абоненты", вместо названия города должно появляться сообщение "Ошибка!".

Этап 2

В счет вручную заносятся все текстовые данные и номер телефона абонента, для которого готовится счет. Фамилия владельца извлекается из справочника "Абоненты", для расчета остальных данных используются сведения из основного документа.

Если в счет внесен ошибочный номер телефона, вместо фамилии владельца должно появляться сообщение "Номер не существует".

Этап 3

Справочники формируются вручную.

Этап 4

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

а) только звонкам в Москву;

б) звонкам во все города, кроме Вологды и Архангельска;

в) разговорам длительностью от 5 до 15 минут;

г) звонкам, сделанным в течение льготного времени;

д) разговорам абонента с заданным номером с Москвой – длительностью не менее 10 минут, и с Архангельском – длительностью не менее 5 минут.

Примечание: при копировании в другую область электронного документа графа "Код города" должна быть автоматически исключена из копии.

Этап 5

Скопируйте документ "Стоимость переговоров" на другую страницу электронной таблицы и выполните сортировку строк документа различным образом:

а) по возрастанию номера абонента;

б) в первую очередь по убыванию номеру абонента, во вторую - по названию города (в алфавитном порядке);

в) в первую очередь по коду города, во вторую - стоимости разговора, в третью – по номеру абонента.

Этап 6

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

Этап 7

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

Этап 8

Используя данные о длительности переговоров (этап 7 данной работы), постройте несколько диаграмм разного типа (гистограмма, круговая, кольцевая и др.), наглядно демонстрирующих:

а) соотношение суммарной длительности переговоров с каждым из городов, с которыми велись переговоры (несколько диаграмм разного типа);

б) соотношение длительности переговоров в льготное и остальное время для каждого города и городов друг с другом (несколько диаграмм разного типа).