Контрольная работа №2

<Процент скидки 2 сорта>), плюс стоимость товара 3 сорта, полученную аналогичным образом, плюс стоимость просроченного товара по цене 10% от цены 1 сорта. Кроме того, следует определить факт затоваривания или нехватки товара. Если совокупная стоимость любого товара всех сортов составляет величину большую 100000 руб., в столбце <Состояние запасов> формируется слово "Избыток". Если стоимость менее 20000 руб. — "Нехватка". Если равна нулю — слово "Нет". В остальных случаях не выдается никакого сообщения — пустые кавычки (""). В клетке H… следует подсчитать число наименований, для которых наблюдается "Нехватка" товара, а в H… — его полное отсутствие ("Нет" – красным цветом).

	А				В	С	D	Е	F	G	Н
1	Скидки:
2	2 сорт				10%
3	З сорт				20%
…							ТОВАРНЫЕ ЗАПАСЫ
…	Наименовани					Число единиц			Цена	Стоимость	Состояние
…	е товара				1 сорт	2 сорт	3 сорт	просрочено	1-го сорта	всего	запасов
…	X				X	Х	Х	Х	Хр	?р	?
…	• • •					. . .
…	Всего				?	?	?	?		?р
…										Нехватка:	?
…										Нет:	?
	Для ввода Наименований товаров (столбец А) использовать список

{Стул, Стол, Шкаф, Плита}.

Выделить наименование товара, если Состояние запасов Нет. Диаграмма: Название товара - Стоимость всего.

Вариант 32

Выполнить расчеты по страхованию дома от пожара на садовом участке. Страховой взнос равен 3% от стоимости кирпичного (обозначается буквой "к") дома. Если дом деревянный (буква "д"), страховка составляет 5% при наличии в непосредственной близости (на расстоянии до 20 м) водоема. Если ближайший водоем находится на расстоянии до 400м — страховка деревянного дома увеличивается до 7%, если до 2000м — до 10%, если более

— до 15%. Кроме того, каждый год страхования снижает размер взноса на 3%, но в общей сложности, не более чем на половину (взнос со скидкой). В области С16:С19 подсчитать объемы страховых взносов в соответствующих диапазонах их сумм.

51

	А		В	С	D		Е	F	G
1		Тарифы для дома из горючих материалов
2	Близость		20м	400м	2000м		> 2000м
	водоема
3			5%	7%	10%		15%
4				СТРАХОВАНИЕ ДОМА
5	Клиент		Дом (д/к)	Вода	Лет		Стоимость	Полный	Взнос со
							дома	взнос	скидкой
6	Х		Х	Хм	Х		Хр	?р	?р
	• • •			• • •
15	Всего						?р	?р	?р
16			до 5т	?р
17	Объемы		до 10т	?р
18	страховок:		до 30т	?р
19			свыше 30т	?р

Контроль В6:В14: Дом ={д, к}.

Форматирование А6:А14: если количество лет страхования < 5. Диаграмма: Клиент - Взносы (полный и со скидкой).

Вариант 33

Рассчитать стоимость железнодорожного билета, заказанного пассажиром на поезд. Положим, поезда могут быть скорыми и пассажирскими (обозначаются буквами "с" и "п" в столбце В), вагоны общими, плацкартными и купейными (буквы "о", "п" и "к" в столбце С). Известна стоимость перевозки пассажира на один километр для самого "дешевого" варианта (пассажирский поезд, общий вагон) - 0,3 руб./км. Определить: минимальную стоимость билета для пассажира (D8 определяется независимо от конкретного вида вагона/поезда), следующего на заданное расстояние, стоимость билета с учетом выбранного вида вагона и поезда. Перемещение на скором поезде увеличивает стоимость билета на 20%, размещение в плацкартном вагоне - на 25%, а в купейном - на 35%. Далее следует установить окончательную стоимость билета. Его стоимость может быть снижена, если пассажир следует на дальнее расстояние. При длине маршрута от 500 до 1500 км. скидка составляет 5% от полной стоимости билета, при расстоянии до 5000 км - 7%, если расстояние больше - 10%. Влияние скидок следует оценить с использованием функции ГПР( ). Кроме того, следует создать сводку (В4:Е4) о числе билетов, взятых на заданные диапазоны расстояний.

52

	А	В	С	D	Е	F	G
1		Скидки от расстояния
2	Расстояние	0	500	1500	5000		Мин тариф
3	Скидки	0%	5%	7%	10%		0,30р.
4	Заявки	?шт	?шт	?шт	?шт
5		ТАРИФИКАЦИЯ БИЛЕТОВ
			Вид	Стоимость билета
		Вид поезда
7					с учетом
	Расстояние		вагона	минималь
		(с/п)			вагона и	со скидками
			(к/п/о)	ная	поезда
8	Х	Х	Х	?р	?р	?р
				• • •
15

Контроль В8:В15: Вид поезда {с, п} С8:С15 Вид вагона ={к, п, о}. Форматирование А8:А15: если поезд скорый и вагон купейный. Диаграмма: Расстояние - Стоимость билета со скидками.

Вариант 34

Определить материальную помощь нуждающимся. Пусть на одного ребенка человеку назначается разовая дотация в размере 4-х минимальных зарплат (4мз), на второго — 2мз, на каждого следующего ребенка — 0,5мз. На 5-го ребенка и дальше помощь не оказывается. Инвалидам назначается помощь в размере — 4мз, участникам войны — 6мз (эти лица обозначаются в таблице знаком "+"). Для граждан, старше 70-ти лет выделяется помощь в размере 20% от МЗ на каждый прожитый год свыше 70-го. Сумма всех четырех дотаций образует так называемую Расчетную помощь. В области Число людей, получившихподсчитывается количество лиц, получивших помощь в объеме до 5мз, до 10мз и свыше. В диапазоне ячеек ВСЕГО подсчитать число инвалидов и участников войны.

53

	А	В	С	D		Е	F
1	Размер помощи					МЗ:	Хр
2	Инвалид	4мз				Год:	Х г
3	Уч. BOB	5мз
4	На 1 ребенка	2мз
		СОЦИАЛЬНАЯ ПОМОЩЬ
	Фамилия	Год	Детей	Инвалид		Уч ВОВ	Расчетная
		рождения					помощь
	Х	Хг	Хчел	Х		Х	?р
	• • •			• • •
	ВСЕГО		?чел	?чел		?чел	?р
	Число	до 5мз	?чел
	людей,	до 10мз	?чел
	получивших:	>10мз	?чел

Контроль столбец Год рождения: 1910< Год рождения<1990. Форматирование столбец Фамилия: если человек старше 70-и лет. Диаграмма: Фамилия - Расчетная помощь.

Вариант 35

Построить таблицу расчетов страхования имущества от кражи, пожара и протечки (в произвольной комбинации). Желаемый вид страховки обозначается знаком "+". Клиент может застраховаться на любую Страховую сумму, для чего делает Страховой взнос в размере 10% от страховой суммы, если имущество страхуется от пожара, 8% — если от протечки, 7% — от кражи. Страховка от кражи снижается на 1% при наличии стальной двери и еще на 2%, если в подъезде имеется вахтер. Взнос уменьшается на 1%, если страхование производится на все виды страховых случаев сразу. В области B4:D4 подсчитать число страховок по видам.

	А		В	С		D	Е	F	G	Н	I
1		Тарифы страхования
2	Вид		пожар	кража		протечк
	страх.					а
3	%		10%	7%		8%
4	Всего		?	?		?
5					СТРАХОВАНИЕ ИМУЩЕСТВА
6			Защита входа			Вид страховки			Страховая	Страховой взнос
7			Стальная					Протеч
	Клиент			вахтѐр		Пожар	Кража		сумма	полный	со скидкой
			дверь					ка
	Х		+			+	+		Хр	?р	?р
	• • •
	Всего								?р	?р	?р

Контроль столбец Страховая сумма: Страховая сумма кратна 1000р. Форматирование столбец Клиент: если страховка на все случаи.

54

Диаграмма: Клиент - Страховая сумма.

Вариант 36

Выполнить расчеты с клиентами на туристическое обслуживание их семей, включающее перелет и проживание в месте отдыха (найти Цену путевки). О каждой семье известны: имя покупателя путевки, число взрослых членов семьи и число детей, а также желаемый класс салона в самолете (1-3) и класс отеля от трех до пяти звезд (обозначаются буквами "х"). Стоимость авиабилета для взрослого пассажира задана в таблице тарифов на перелет (детский билет в половину дешевле). Стоимость проживания взрослого туриста в период отдыха задана в таблице тарифов на проживание в отеле. Один ребенок в семье с двумя взрослыми проживает бесплатно. На семью от четырех взрослых делается скидка в 10% стоимости проживания (но только в пятизвездном отеле). Для розыска тарифов использовать функцию ГПР( ). В клетке Е17 показать число путевок, с проживанием в пятизвездном отеле.

	А	В		С	D	Е	F	G	Н
1					Тарифы обслуживания
2		Отель					Полет
3	Класс:	xxx		хххх	ххххх	Класс:	1	2	3
4	Цена:	200$		280$	400$	Цена:	200$	250$	300$
5					ТУРИЗМ
6	Клиент	Число			Класс			Стоимость
7		взрослых		детей	полета	отеля	полета	прожив.	путевки
	Х	Х		Х	Х	Х	?$	?$	?$
	• • •				• • •
	Всего	?		?			?$	?$	?$
	"Пятизвездных" путевок					?

Контроль столбец Класс отеля: Класс отеля= {ххх, хххх, ххххх}. Форматирование столбец Клиент: если Число заказанных путевок

одним клиентом 5.

Диаграмма: Клиент - Цена путевки

Вариант 37

Рассчитать сдельную зарплату рабочих в зависимости от количества и качества их труда. Положим, работник может изготовить некоторое число деталей с разным качеством, оцениваемым ОТК цеха как изделия 1-4 сортов, на основании чего и определяется его Заработок. Кроме того, он получает Премию в 20% от стоимости деталей 1-го сорта и 10% от стоимости изделий 2-го сорта. Если имеются детали 4-го сорта, премия не назначается вообще. В области Сводка следует показать имена рабочих (Лидер/Аутсайдер), изготовивших наибольшее/наименьшее число деталей 1-го сорта, а также число этих деталей и число деталей 4-го сорта. Для вычисления Заработка

55

удобно использовать функцию СУММПРОИЗВ( ).

	А	В	С	D	Е	F	G	Н	I	J
1			Тарифы				Сводка	Имя	1 сорт	4 сорт
2	Сорт	1	2	3	4		Лидер:	?	?шт	?шт
3	Цена	10р	8р	5р	2р		Аутсайдер:	?	?шт	?шт
					ЗАРПЛАТА
	Имя		Сорт			Зара	Премия	Всего
						боток
		1	2	3	4
	Х	Хшт	Хшт	Хшт	Хшт	?р	?р	?р
	• • •					•••
	Итог	Хшт	Хшт	Хшт	Хшт	?р	?р	?р

Контроль столбец Имя: {Петр, Олег, Иван, Сергей, Михаил, Василий}. Форматирование столбец Имя: если имеется 4-ый сорт.

Диаграмма: Имя-Заработок.

Порядок выполнения контрольной работы

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

Во многих задачах, кроме вычислений, относящихся к отдельным объектам (обычно, они находятся в строках), следует получить некоторые сводные характеристики, для чего применяются функции СЧЁТЕСЛИ(), СУММЕСЛИ(), МАКС(), МИН(), НАИБОЛЬШИЙ(), НАИМЕНЬШИЙ() и др., или функциями, использующими массивы: {=СУММ(ЕСЛИ(...))}, {=СЧЁТ(ЕСЛИ(...))} и др. Итоговые функции должны строиться так, чтобы допускались вставки новых данных в любом месте области обработки без их редактирования.

После содержательного заполнения таблицы нужно выполнить все необходимые действия по ее оформлению: выравнивание, обрамление, заливка, выделение заголовков шрифтами и другое форматирование данных, включая установление единиц измерения. Здесь следует использовать готовые (если есть) форматы, или создать собственные форматы (например, 432,8р, 25т.р, 10 тонн, 45кг, 3000км и т.п.).

В каждом задании необходимо организовать:

Встроенный контроль ввода. Используется для организации проверки вводимых значений. Все ограничения по вводу заданы в пункте Контроль в формулировке задачи. Если возникает необходимость, то добавьте сообщение для ввода и(или) сообщение

56

об ошибке.

Пункт «Форматирование» предполагает использование Условного форматирования для указанных ячеек. Форматирование, если не задано иначе, может быть любым - заливка, шрифт, рамка и т.п. по своему выбору.

Результаты вычислений отобразить в виде графика (в пункте График указаны данные, которые должны быть связаны в

диаграмме). Тип графика выбирать исходя из наибольшей наглядности.

Для лучшего уяснения задачи, прежде чем формировать нужные клеточные выражения, следует составить формулы для вычислений и для первых двух-трех строк вручную выполнить вычисления при разных вариантах заполнения. Исходные данные в таблицах отображены символами "X", а вычисляемые - символами "?". Для этих вычисляемых ячеек следует составить клеточные функции. Хотя в некоторых случаях для наглядности данные заданы конкретными числами, при построении таблиц имеется в виду, что ВСЕ исходные данные могут иметь переменные значения. Длина таблицы также может быть произвольной.

Оформление контрольной работы

Контрольная работа предоставляется преподавателю в электронном и бумажном видах.

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

Вбумажном виде контрольная работа должна содержать:

1.Титульный лист (оформленный по правилам оформления титульных листов курсовых работ с обязательным указанием № варианта)

2.Формулировку задачи (с исходной таблицей)

3.Формулы вычислений с описанием используемых величин

4.Таблицу с результатами

5.Описание, каким образом были выполнены пункты «Контроль» и «Форматирование» (можно для наглядности использовать скриншоты)

6.Диаграмма

Примечание: Как вставить в документ скрин-шот. Скрин-шот представляет собой образ экрана, помещенного в буфер памяти

57

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

1.Отобразить на экране фрагмент документа, образ которого необходим в другом приложении (документе)

2.Нажать клавишу Print Scrn на клавиатуре

3.Открыть стандартное приложение Windows Paint

4.Выполнить команду Правка Вставить

5.Используя элемент «Выделить» выделить необходимый фрагмент изображения и скопировать его в буфер

6.Открыть документ, в который необходимо вставить фрагмент

7.Вставить из буфера обмена скопированный фрагмент (команда Вставить (Вкладка «Главная» Ленте

58

Используемые источники:

1.Справочная система по MS Excel

2.О.В. Мачула Excel 2007. Секреты и трюки. М.: АСТ: Астрель: Полиграфиздат, 2010. – 480с.

3.О.Б. Богомолова Готовимся стать сертифицированным специалистом по MS Excel: учебное пособие. М.: БИНОМ. Лаборатория знаний, 2010 – 343с.

4.Л.В.Рудикова Microsoft Excel для студента. – СПб.: БХВПетербург, 2005. – 368с.

5.С.М. ЛавреновExcel: Сборник примеров и задач. М.: Финансы и статистика, 2003. – 336с.

6.А.А. Попов Excel: практическое руководство. М.: ДЕСС КОМ,

2000. – 302с.

7.А.Г. Горбачев, Д.В. Котлеев Microsoft Excel. Работайте с электронными таблицами в 10 раз быстрее. М.: Издательский дом «ДМК-пресс», 2007. – 96с.

8.С.Д. Данилова, С.В. Дамбаева, И.С. Евдокимова Табличный процессор Microsoft Excel. Методические указания. ВСГТУ,

Улан-Удэ, 2001. – 51с.

59