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

	А	В	С	D	Е	F	G
1	Стоимость 1кв.м. земли:			Хр
2	Налог на землю:			Х%
3	Налог на строения:			Х%
4				УЧАСТКИ
5	Владелец	Площадь	Стоимость	Вода	Свет	Общая	Налог
		(кв.м)	дома			стоимость
6	Х	Х	Хр	+	+	?р	?р
	• • •
	Всего	?	?р	?уч	?уч	?р	?р
		до 70м	?уч
	Участки:	до 100м	?уч
		>100м	?уч

Контроль столбец Площадь (кв.м): 50<=площадь<=500. Форматирование столбец Владелец: если площадь >400 м. Диаграмма: Владелец - Общая стоимость.

Вариант 18

Определить пенсию по старости. Пусть она назначается в размере шести минимальных зарплат по достижении женщинами 55-ти лет и мужчинами 60-ти лет. Кроме того, пенсионерам полагается доплата (расчетная) в размере 5% от пенсии при наличии от двадцати до тридцати лет трудового стажа, 20% при наличии от тридцати до сорока лет, 50% — при сорока и более и за каждый год военного стажа прибавляется еще 30% от минимальной зарплаты. Однако сумма (фактическая) всех доплат может быть и меньше. Вместе с пенсией она не должна превышать десяти минимальных зарплат. Таким образом, например, человек, имеющий высокую пенсию, фактически может не получить доплаты совсем. Подсчитать общее число человек, число женщин и число мужчин пенсионеров.

	А	B	C		D	Е		F		G	H
1	Сегодня:	Х г						Мин. зарплата:			Хр
2				НАЗНАЧЕНИЕ ПЕНСИИ
3	Ф.И.О.	Год	Стаж			Пол		Пенсия		Доплата
		рожд.
4			трудовой		воен.					расч.	факт.
5	Х	Х г	X лет		Х лет	Х		?р		?р	?р
	• • •
	Всего						?ч	?р		?р	?р
	Из них				женщин:		?ч
					мужчин		?ч

Контроль столбец Стаж трудовой: Стаж трудовой=<70лет. Форматирование столбец Ф.И.О.: если трудовой стаж превышает 40

лет.

41

Диаграмма: Фамилия – Доплата факт.

Вариант 19

Определить размер квартплаты в кооперативном доме, которая состоит из расходов на оплату лифта (В1) и коммунальных расходов (H1). Расходы на оплату лифта делятся между всеми пропорционально площади квартир с учетом этажа квартиры. Жильцы первого этажа за лифт не платят совсем, жители второго - 50%, жители третьего - 80% от полной стоимости. Остальные платят 100%, приходящейся на них суммы. При правильном виде формул содержимое клеток В1 и Е9 должно совпасть. Расходы на коммунальное обслуживание делятся между квартирами пропорционально их площади. В доме имеются не только жилые помещения, но и помещения (обозначены знаком "+"), которые сдаются в аренду фирмам под офисы. Их арендаторы вместо платы за коммунальные услуги вносят в доход кооператива сумму равную 200 руб. за кв. метр площади, если арендуемая площадь более 100 кв.м. - то по 180 руб., если более 300 кв.м. - то по 160 руб. за метр. Весь доход идет на снижение квартплаты жильцам дома и при правильных расчетах содержимое клетки Всего квартплата (столбец Н) будет равно H1-Всего доход от аренды. Подсчитать число офисов, размещенных в доме, среднюю стоимость аренды одного кв. метра офисов, Остаток от суммы доходов, если он превышает совокупную квартплату (столбец В).

		А	В	С	D	Е	F	G	Н
	1	Лифт:	5000р				Коммун.обслуж.:		20000 р
	2				РАСЧЁТ КВАРТПЛАТЫ
	3	Номер	Офис	Этаж	Пользован.лифтом		Площадь	Доход oт	Кварт-
		квартир						аренды	плата
	4				(в %)	(в руб.)
	5	Х	Х	Х	?%	?р	Хм	?р	?р
		Всего	?		?%	?р	?м	?р	?р
		Ср. цена	?р/м
		Остаток	?р

Контроль столбец Этаж: 1<=Этаж<=5.

Форматирование столбец Номер квартиры: если это Офис. Диаграмма: Номер квартиры - Квартплата.

Вариант 20

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

42

Оплата за пользование отдельным или спаренным телефоном (помечается буквами «о» или «с»).

Оплата за газоснабжение при наличии водонагревательной колонки (помечается знаком «+») и без нее. Рассчитывается на каждого проживающего в квартире человека и только в случае отсутствия электроплит. Считается, что при наличии электроплит, газ к дому не подведен.

Оплата за коммунальные услуги. Рассчитывается пропорционально площади квартиры. При наличии газовой колонки (помечается знаком «+») она снижается на 10% поскольку централизованное снабжение горячей водой тогда

отсутствует (но возрастает сумма выплат за газ)

A

B

C

D

E

F

G

H

I

J

1

ТАРИФЫ

2

Телефон

отдельный

Хр

3

спаренный

Хр

4

Газ

без

Хр/ч

колонки

5

с колонкой

Хр/ч

6

Коммун.у

без

Х р/ч

слуги

колонки

7

с колонкой

-10%

8

РАСЧЕТ КВАРТПЛАТЫ

9

Удобства

Оплата

10

№

колонка.Газ

Телефон спар./отд( .)

плита./Эл

Коммун. услуги

Газ

Телефон

Квартир

Площадь

Человек

Всего

ы

Х

Хкв.м

Х чел.

Х

Х

Х

?р

?р

?р

?р

…

ВСЕГО

? кв.м

? чел.

?

?

?

?р

?р

?р

?р

Контроль: предусмотреть при вводе, что в одной квартире не может быть и электрическая плита и газовая колонка; столбец Телефон (отд./спар.) = {о,с}.

Форматирование столбец № квартиры: если площадь квартиры > 100

м2.

Диаграмма: № квартиры - Всего.

43

Вариант 21

Произвести расчеты с покупателем за товар при наличной (нал) и безналичной (безнал) формах оплаты. Исходная цена товара представлена в таблице исходя их наличной оплаты. Оплата может осуществляться за наличный/безналичный расчет в любой комбинации, но на сумму не менее 1000 руб. Покупатель вносит сумму, которую он может оплатить наличными (столбец Е). Остаток суммы в форме безналичной оплаты вычисляется в столбце F с учетом наценки за "безнал" (столбец G). Кроме того, для оптовых покупателей осуществляется бесплатная доставка груза. При цене партии от 10000 руб. — в пределах Москвы, от 50000 — в Московской области, от 100000 — в центральном районе РФ. В зависимости от этого в столбце Доставка должно выводиться одно из слов: Москва, МО, Центр. Подсчитать количество и объем заказов (в рублях) с доставкой в каждый регион.

	А	В	С	D		Е			F		G
1	Сумма	10000р	50000р	100000р					Наценка		Х%
									за б/нал:
2	Поставка	Москва	МО	Центр
3			ПРОДАЖА И ДОСТАВКА
4	Товар	Кол-во	Цена	Цена			Оплата				Доставка
				партии
5						Нал.			Безнал
6	X	Х	Хр		?р			Хр		?р	?
	• • •
	ВСЕГО				?р			?р		?р
	Доставка					Регион			Кол-во		Объем
									заказов		заказов
						Москва			?		?р
						МО			?		?р
						Центр			?		?р

Контроль столбец Оплата Нал.: Оплата наличными < Цена партии. Форматирование столбец Товар: если Цена партии > 90 000руб. Диаграмма: Товар - Цена партии.

Вариант 22

Вычислить стоимость ремонта квартиры. Она состоит из <Стоимости ремонта потолка> (получается умножением <Площади> квартиры на <Стоимость окраски 1 кв.м потолка>) плюс <Стоимость ремонта стен> (определяется умножением <Площади стен> на <Стоимость оклейки/окраски/обивки 1 кв. м. стен>). Вид отделки стен указывается буквами "кл", "кр" или "об" в столбце <вид отделки>. Допускается отсутствие какой-либо отделки стен. Стоимость срочного ремонта увеличивается на 40%. Этот факт показывается (если есть) в колонке <Срочность> знаком "+". Если стоимость ремонта превышает 50 тыс. руб.,

44

клиенту дается скидка в 10%. Подсчитать число срочных заказов. В области Объемы (столбец D) подсчитать общие объемы ремонтных работ (в метрах) по видам.

	А	В |		С	D		Е	F		G	Н
1	Цена ремонта 1 кв. метра				Объемы
2	Окраска потолка:			Хр/м		?м
3	Оклейка стен:			Хр/м		?м
4	Окраска стен:			Хр/м		?м
5	Обивка стен:			Хр/м		?м
6					РЕМОНТ
7	№			Стены			Цена ремонта
		Площадь
8					вид
	квартиры		площадь				потолок	стены		Срочность	Всего
					отделки
	Х	Хм		Хм	Х		?р		?р	+	?р
	• • •				• • •
	Всего	?м		?м			?р		?р		?р
	Срочных заказов:									?зак

Контроль столбец Вид отделки: Отделка стен={кл, кр, об}. Форматирование столбец № квартиры: если Всего>50 000руб. Диаграмма: Номер квартиры - Всего.

Вариант 23

Определить суточный заработок рабочих в зависимости от числа часов, отработанных ими в дневную (с 9:00 до 16:00 часов) и вечернюю смены (c 16:00 до 24:00 часов). Расценки за работу в вечернюю смену на 60% выше, чем в дневную. Кроме того, если рабочий отработал более 8-и часов, ему положена доплата за сверхурочные часы (часы свыше восьмого) в размере 50% от обычного тарифа. Для каждого работника заданы фактические начало и конец его смены. Считается, что рабочий трудится только в дневную или только в вечернюю смену, но не обязательно рабочий день начинается и заканчивается с началом/концом смены, принятой на предприятии (начало рабочего дня не может отклоняться более чем на час от начала смены). Подсчитать число рабочих, трудившихся в вечернюю и дневную смены.

	А	В	С	D		Е		F
1	Дневная	Начало	Конец	Тариф
	смена
2		9:00	16:00	Хруб/ч
3			СМЕННАЯ РАБОТА
4	Ф.И.О.	Время работы:		Часов в смену:				Сумма
5		начало	конец	дневную		вечернюю
6	X	Х:Х	Х:Х	?час			?час		?р
	• • •		• • •
	ВСЕГО			?час			?час		?р
	Число смен:			?смен			?смен
	Контроль столбец Время работы начало:				начало рабочего дня не
							45

может отклоняться более чем на час от начала смены.

Форматирование столбец Ф.И.О.: если общее число часов > 8 час. Диаграмма: Фамилия-Сумма.

Вариант 24

Определить Сумму на счету клиента банка по истечении установленного Срока хранения. Процент банковской премии определяется тарифной сеткой (А1:Е5), зависящей от Суммы вклада (от 0$, от 5000$ и т.п.) и Срока его хранения (от 0, 6 и т.д. месяцев). Кроме того, если клиент открыл счет более чем на 100 000$, сроком не менее чем на 6 месяцев, ему вручается подарок в размере 1% от исходного значения вклада. Здесь также по известной продолжительности депозита нужно вычислить дату его закрытия (число месяцев плюс один день). Подсчитать число человек, получивших подарки.

	А	В	С	D	Е	F	G	Н
1	Процент	0 мес	6 мес	12 мес	24 мес
2	0$	3%	5%	6%;	7%
3	5000$	5%	7%	8%	9%
4	20000$	7%,	8%	10%	11%
5	50000$	9%	10%	11%	12%
6			БАНКОВСКИЕ ДЕПОЗИТЫ
	Вкладчик	Вклад	Дата	Срок (мес)	Дата	Процент	Сумма	Подарок
	(ФИО)		вклада		закрытия	премии
	X	?$	Х	Хм	?	?%	?$	?$
	• • •
	Всего	?$					?$	?$
	Всего подарков:							?чел

Контроль столбец Вклад: Вклад 200$.

Форматирование: столбец Вкладчик (ФИО) – если Срок> 30 мес. и столбец Сумма – сумму на счете с учетом подарка.

Диаграмма: Вкладчик-Сумма.

Вариант 25

Построить таблицу расчетов страхования жизни. Клиент может застраховаться на любую Страховую сумму, для чего делает Взнос в размере, зависящем от возраста застрахованного (до года - 10% от страховой суммы, от года до пяти - 8% и т.д.). Лица старше 65 лет вносят 10% и еще по одному проценту за каждый год после 65-ти. Кроме того, необходимо рассчитать сумму выплаты в зависимости от тяжести Травмы (всего 4 категории). При возникновении травмы или болезни клиенту возвращается соответствующий процент от страховой суммы. Подсчитать число всех страховых случаев. Проценты взноса и выплат определяются с помощью функции ГПР( ).

46

	А	В	С		D		Е	F	G	Н
1	Текущий год:	Х
2	Возраст (лет)	0	1		5		20	45	50	65
3	% взноса	10%	8%		5%		3%	5%	8%	10%
4		Тарифы выплат
5	Травмы	1	2		3		4
6	% выплат	15%	25%		80%		100%
7		СТРАХОВАНИЕ ЖИЗНИ
8		Год	Страховая				Страховой
	Клиент	рожден			Взнос			Выплаты
			сумма				случай
		ия
	Х	Xр	Хр		?р		X	?р
	• • •
	ВСЕГО		?р		?р		?	?р

Контроль столбец Год рождения: Год рождения>=1910. Форматирование столбец Клиент: если Возраст > 65. Диаграмма: Клиент - Взнос.

Вариант 26

Построить таблицу расчетов ежегодного страхования автомобиля. Клиент может застраховаться на любую страховую сумму, для чего делает Взнос в размере 3% от страховой суммы. Этот взнос уменьшается на 5% для лиц, имеющих более 10 лет водительского стажа (отсчитывается от года получения автомобилистом водительских прав). Кроме того, учитывается число безаварийных лет, в течение которых владелец страховался в данной фирме. Если свыше 1 года, размер взноса снижается на 5% от номинального, если свыше 2 лет — на 7% и т.д. При возникновении страхового случая (аварии) страховой агент устанавливает фактическую сумму потерь клиента, которая и выплачивается ему в размере, не превышающем страховой суммы. В случае угона автомобиля в колонку "Угон" вносится буква "у" и выплачивается вся страховая сумма. Подсчитать число всех угнанных и ранее застрахованных автомобилей.

	А	В		С	D	E		F	G		H
1	Текущий год:	Х
2	Лет страховки:	0		1	2	3		4
3	Скидка:	0%		5%	7%	9%		12%
4			СТРАХОВАНИЕ АВТОМОБИЛЯ
5		Получены		Начато	Сумма			Сумма
	Клиент			страхова-	страхов-	Взнос			Угон		Выплаты
		права						потерь
				ние	ки
	Х	Хг		Хг	Xp	?р		Xp	Х		?p
	• • •			• • •
	ВСЕГО				?p	?р		?p	?		?p
	Контроль столбец Сумма страховки Сумма страховки >=1000р.
											47

Форматирование столбец Клиент: если Угон. Диаграмма: Клиент - Выплата.

Вариант 27

Создать таблицу расчетов с клиентами отеля, о которых известны даты въезда, съезда и класс занимаемого номера (от Люкс до 3-го). Оплата за номер определяется числом дней проживания, умноженным на тариф соответствующего класса (использовать функцию ГПР( )). Кроме того, имеются Скидки/Доплаты. Если клиент проживает в номере больше 10 дней, ему делается скидка по оплате в 15% за каждый день свыше десятого. Если номер клиентом был ранее предварительно забронирован, он доплачивает за бронь сумму в размере платы за половину дня проживания. <Общая сумма> складывается из Оплаты и Доплаты/Скидки. В области В4:Е4 формируется сводка по наполнению номеров. Здесь подсчитывается число занятых номеров соответствующего класса.

	А	В	С	D	Е	F	G	Н
1			Тарифы
2	Класс	Люкс	1	2	3
3	Оплата	Хр	Хр	Хр	Хр
4	Число	?	?	?	?
	клиентов
5			РАСЧЕТЫ С КЛИЕНТАМИ ОТЕЛЯ
	Клиент	Бронь	Проживание		Класс	Оплата	Доплаты	Общая
					номера		/скидки	сумма
			с:	по:
	Х	+	Х.Х	Х.Х	Х	?р	?р	?р
	• • •			...
	Всего					?р	?р	?р

Контроль столбец Класс номера: Класс номера= {Люкс, 1,2,3}. Форматирование столбец Клиент: если Класс номера – Люкс. Диаграмма: Клиент - Общая сумма.

Вариант 28

Произвести расчеты с клиентом за купленный товар с учетом времени оплаты, которая может осуществляться частью в форме предоплаты в размере не менее 60% от всей стоимости товара, частью в более поздние сроки. Если покупатель не хочет заплатить за весь товар сразу, он может внести остаток суммы позже, но с наценкой, определяемой таблицей А1:Е2. Наценка на остаток составляет 1% при оплате в срок от 1 до 3 дней, 2% — на срок до 7 дней и т.д. Покупатель указывает сумму, которую он может внести сразу, и число дней (не более 15), через которое будет оплачен остаток. Остаток суммы с учетом наценок определяется в столбце F. В столбце G находится полная сумма, вносимая покупателем за товар. Вычислить число

48

крупных заказов, превышающих 50000 руб. Наценку найти функцией ГПР( ).

		А	В	С	D	Е	F	G
	1	Дни	1д	3д	7д	10д	Обязат.	60%
							предоплата
	2	Наценка	1%	2%	3%	5%
	3			ТОРГОВЛЯ В КРЕДИТ
	4	Товар	Стоимость	Предоплата		Оплата		Всего
	5			обязат.	фактич.	дни	остатка
	6	X	Хр	?р	Хр	Xд	?р	?р
		• • •
		ВСЕГО			?р		?р	?р
		Крупный опт:						?зак

Контроль: Фактическая предоплата должна быть не меньше обязательной предоплаты.

Форматирование столбец Товар: если Всего > 50 000руб. Диаграмма: Товар - Всего.

Вариант 29

Построить таблицу расчетов за купленные товары в валютах разных стран (в строке 4 представлены их курсы). Цена валюты зависит не только от установленного Центробанком курса валюты, но и от магазинные наценки (если есть) за ее конвертирование. Доллар и марка наценки не имеет, менее "ходовые" валюты (фунт и франк) конвертируется с наценкой в 3%, лира - в 6%. В строке 6 подсчитать суммы продаж за соответствующие виды валют. Для розыска курсов валют и наценок использовать функцию ГПР( ). Кроме того, следует учесть скидку оптовым покупателям. При покупке на сумму от 10000 руб. — 5%, при 50000 руб. — 10%, далее за каждые полные 10000 руб.

— скидка в 2% (т.е. скидка с накоплением), однако общая скидка не может быть более четверти стоимости товара.

	A	В	С	D	Е	F
1			Курсы валют
2	Валюта	Доллар	Немецкая	Фунт	Франк	Лира
			марка	стерлингов
3	Обозначение	$	Dm	Ps	Fr	L
4	Курс	Хр	Хр	Хр	Хр	Хр
5	Наценка	0%	0%	3%	3%	6%
6	Сумма	?$	?DM	?Ps	?Fr	?L
7			ТОРГОВЛЯ ЗА ВАЛЮТУ
8	Товар	Вид валюты	Цена в валюте	Цена в рублях	Скидка	Цена со
						скидкой
	Х	Х	Х	?р	?р	?р
	• • •		• • •
	Всего					?р
	Контроль В9:В16: Валюта = {$, Ps, Dm, Fr, L}.
						49

Форматирование столбец Товар: если оплата в долларах. Диаграмма: Товар - Цена со скидкой.

Вариант 30

Построить таблицу расчетов с постоянными клиентами-покупателями, которым товар отпускается в кредит. О каждой покупке известны: название фирмы-покупателя, дата приобретения, стоимость приобретенного товара (в тыс. руб.) и плановая дата возврата кредита за товар. В столбце Долги вычисляется величина кредита, которая зависит от его длительности (начиная от даты покупки по сегодняшний день). При сроке свыше 5-и дней

— это 2% от исходной стоимости, свыше 10-и — 4%, свыше 15-и — 5% и еще по 1% за каждый день поле 15-го (таблицы кредитных ставок находятся в области В2:Е3). Если оплата произведена, она фиксируется значком "+". При задержке в оплате свыше 20-ти дней от плановой даты платежа в столбце G (значок  берется из шрифта Wingdings) должно появляться слово "Звонить". Вычислить количество всех непогашенных кредитов. Подсчитать общие неоплаченные долги клиентов (столбец J). Здесь каждый клиент представлен только одной строкой.

А

B

С

D

Е

F

G

Н

I

J

1

Условия кредита

2

Срок

0дн

5дн

10дн

15дн

Сегодня:

d.m

кредита:

3

Процент:

0%

2%

4%

5%

4

ПРОДАЖИ

СВОДКА ДОЛГОВ

5

Стоимо

Плановая



Клиент

Общие

Дата

Факт

Клиент

сть

дата

Долги

долги

покупки

оплаты

товара

платежа

6

Х

Х.Х

Х т

Х.Х

?т

Х

?

Факел

?т

Х

Х.Х

Х т

Х.Х

?т

Х

?

Спорт

?т

Х

Х.Х

Х т

Х.Х

?т

Х

?

Дом

?т

…

Всего

?т

Всего

?т

?т

Контроль

столбец Дата покупки: Дата

покупки

<=

Сегодня и по

Клиенту ={Факел, Спорт, Дом} Форматирование столбец Клиент: если "Звонить". Диаграмма: Клиент - Общие долги

Вариант 31

Вычислить <Стоимость всего> товара, хранящегося на складе магазина. Она определяется стоимостью первого сорта товара (<Число единиц 1 сорта>, умноженной на <Цену 1 сорта>) плюс стоимость 2 сорта (<Число единиц 2 сорта>, умноженная на <Цену 1 сорта>, уменьшенную на

50