Методические указания к зданию

ЗАДАНИЯ НА КОНТРОЛЬНУЮ РАБОТУ ПО ТЕМЕ EXCEL

Дисциплина “Информационные технологии”

Задание 1. Вычислить льготную квартплату. Некоторым категориям жильцов положены льготыпри оплате коммунальных услуг. Инвалиды платят на 25%, а участни­ки войны - на 50% меньше. Эти лица отмечены в колонке <Льготы>буквами "и", "у" или "иу" соответственно. Кроме того, для жильцов пер­вого этажа квартплата снижается на 10% в виду отсутствия необходи­мости платить за лифт, а жильцам второго - на 5% по тем же причи­нам. Квартплата снижается по 10% на каждого ребенка. В клетке D10 подсчитывается число квартир с льготной оплатой.

	A	B	C	D	E	F	G	H	I
	КВАРТПЛАТА
	№ Квартиры	Этаж	Дети	Льготы (и, у, иу)	Полная квартплата	Льготы
	За этаж	И, У, ИУ	На детей	Квартплата с учетом льгот
				и	1 000р.	?	?	?	?
				у	1 000р.	?	?	?	?
					1 000р.	?	?	?	?

Задание 2.Вычислить, заработанную рабочим сумму в зависимости от количества отработанных им в неделю часов и их вида. <3арплата> определяется как число отработанных <Нормальных часов> умноженных на <Стоимость нормального часа> плюс стоимость сверхурочных часов и часов, отработанных в выходные дни. Стоимость таких часов увеличивается на 150% и 200% относительно "нормального" часа. Кроме того, если общее число отработанных часов превышает 52, работник получает <Доплату> в 100 руб. если больше 60 часов 200 руб. если больше 66 - 250 руб. и еще 5% от зарплаты. Сумма, выдаваемая <На руки>, это <Зарплата> + <Доплата> с учетом <Налога> т.е. (от зарплаты и от доплаты берется налог в размере 13%).

Примечание:при решении задания, не применять дополнительные колонки расчетов.

	A	B	C	D	E	F	G
	Стоимость часа			Налог:	13%
	Нормальный:	20р.
	Сверхурочный:	150%
	В выходные:	200%
	ЗАРПЛАТА
	Фамилия	Отработано	Зарплата	Доплата	На руки
	Норм.	Сверх.	Выход.
	Иванов				?	?	?
	Петров				?	?	?
	Сидоров				?	?	?

Задание 3.Определить <Новую цену> товара, продаваемого в комиссионном магазине. О каждом <Товаре> известна <Дата сдачи> его на комиссию и исходная, установленная в этот момент на него цена. По условиям магазина:

после первых 14-ти дней товар подвергается уценке на 5%,

после 29-ти - на 10% и далее каждый день на один процент.

Цена товара со всеми уценками отображается в колонке <Цена с уценкой>. <Новая цена> равна <Цене с уценкой> до тех пор, пока последняя не становится менее четверти исходной цены (по условиям договора товар не может быть уценен более чем на 75%). В клетке D11 подсчитывается число предметов, которые не удалось продать более чем за 30 дней, а в D12- более чем за 50 дней. Графу <Дней хранения > настроить на общий формат. Графу <Дата сдачи> настроить на формат даты. Графы <Исходная цена> и <Новая цена> настроить на денежный формат. Построить график зависимости товара от новой цены.

	A	B	C	D	E	F
	Уценка товара:			Сегодня:	30.11.2012
	Дни	%
		5%
		10%
	ЦЕНА ТОВАРА
	Товар	Дата сдачи	Исходная цена	Дней хранения	Цена с уценкой	Новая цена
	сапоги	26.10.2012	100р.	?	?	?
	пальто	11.11.2012	100р.	?	?	?
	коляска	06.10.2012	100р.	?	?	?

Задание 4

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

При сроке свыше 5-и дней - это 2%от исходной стоимости, свыше 10-и - 4%, свыше 15-и - 5% и еще по 1% за каждый день поле 15-го (таблицы кредитных ставок находят­ся в области В2:ЕЗ).

Если оплата произведена, она фиксируется знач­ком "+". При задержке в оплате свыше 20-го дня (<Сегодня> - <Плановая дата платежа> в столбце G (значок телефона берется из программы Microsoft Word Вставка à Символ шрифт Wingdings) должно появляться слово "Звонить". В области K6:K8 подсчитываются общие неоплаченные долги клиентов. Здесь каждый клиент представлен только одной строкой. Графы <Дата покупки> и <Плановая дата платежа> настроить на вывод формата даты.

	A	B	C	D	E	F	G	H	I	J	K
	Условия кредита
	Срок кредита (дни):							Сегодня	30 янв
	Процент:	0%	2%	4%	5%
	ПРОДАЖИ			Сводка долгов
	Клиент	Дата покупки	Стоимость товара	Плановая дата платежа	Длительность	Долги	Факт оплаты	(		Клиент	Общие долги
	Факел	1 янв		21 янв	?	?	?	?		Факел	?
	Спорт	1 янв		12 янв	?	?	?	?		Спорт	?
	Дом	2 янв		8 янв	?	?	?	?		Дом	?
	Факел	1 янв		5 янв	?	?	?	?
	Спорт	8 янв		18 янв	?	?	?	?
	Дом	1 янв		6 янв	?	?	?	?

Задание 5. Вычислить <Цену авиабилета> в зависимости oт пол­ной протяженности маршрута до всех пунктов посадок (если есть).

Цена билета состоит из трех слагаемых:

1. Стоимости собственно перевозки пассажира, определяемой умно­жением длины маршрута на <Стоимость 1 км.> полета. Последняя не постоянна. Если длина перелета менее 1000 км., она равна 0,5руб., если от 1000 до 3000 - меньше на 10%, если свыше 3000 -меньше на 15%.

2. Стоимости питания. Пассажиров кормят каждые 1000 км полета. <Стоимость питания> определяется общей протяженностью мар­шрута, деленной на 1000 (результат округляется до целого значения) и умноженной на его цену (50 руб.).

3. Стоимости доставки в аэропорт. Она составляет 100р и выполняется только для пассажиров, следующих на расстояние не менее 3000 км.

	A	B	C	D	E	F	G	H	I	J
	Питание пассажиров		Стоимость 1 км полета пассажира
	расстояние, км.		до 1т км	100р.
	стоимость	50р.	до 3т км	10%
			свыше 3т	15%
	СТОИМОСТЬ АВИАПЕРЕВОЗОК
	№ рейса	Расстояние до пунктов посадки	Длина маршрута	Стоимость питания	Цена билета	Стоимость 1км	Стоимость перевозки	Стоимость доставки
	1-й	2-й	3-й
					?	?	?	?	?	?
					?	?	?	?	?	?
					?	?	?	?	?	?
	ВСЕГО	?	?	?	?	?	?	?	?	?
	Средняя длинна маршрута:	?

Задание 6. Вычислить материальную помощь нуждающимся пенсионерам. <Расчетная помощь> определяется как процент от <Минимальной зарплаты> в зависимости от наличия детей (<На ребенка 80%), возраста (<Старше 70-ти лет>), инвалидности, участия в войне. Последнее отмечено в колонке <Льготы> буквами "и", "у" и "иу". Однако <Фактическая помощь> назначается таким образом, чтобы вместе с <Пенсией> она не превышала шести минимальных зарплат. Число лет человека определяется как разность между <Текущим годом> и <Годом рождения>. В области B16:D16 подсчитывается количество пенсионеров соответствующих возрастов.

	A	B	C	D	E	F	G	H
	Доплаты			Текущий год
	На ребенка:	80%			Мин. зарплата	100р.
	Инвалид:	100%
	Уч. Вов:	125%
	Старше 70-ти:	60%
	ПОМОЩЬ
	Фамилия	Год рожд.	Детей	Льготы	Пенсия	Расчетная помощь	Фактическая помощь	Возраст
	ххх			У		?	?	?
	ххх			у		?	?	?
	ххх			иу		?	?	?
	Всего					?	?
	Возрастные группы	до 65	до 70	>70
	?	?	?

Задание 7. Вычислить размер недельной заработной платы ра­бочего. Ежедневно он может находиться как в обычном, так и во вредном производстве. Часы работы по дням недели указаны в двух строках для каждого человека. По итогам недели вычисляются число дней отработанных в обычных и вредных условиях, и сумма часов. На их основе определяется оплата труда умножением <часов> на соответст­вующую <Часовую оплату>. Кроме того, рабочим начисляется <Доплата> за сверхурочный труд. <Доплата> за труд в обычных условиях производится при наличии сверхурочного времени. Разность между фактической длиной рабочей недели и 48 часами оплачивается по та рифу сверхурочных часов (клетка L1). Доплата за работу во вредных условиях производится аналогично, но только если отработано свыше 20-ти "вредных" часов. Кроме того, в доплату входит сумма на покупку молока (L2) за каждый день, отработанный во вредных условиях. В столбце М формируется сообщение (слово Отгул), если отработано свыше З0 часов во вредном производстве (т.е. на следующей неделе работник получи один отгул). В клетке Ml1 вычислить число всех отгулов за неделю.

A

B

C

D

E

F

G

H

I

J

K

L

M

Часовая оплата

Сверхурочн.:

200%

Обычное произв.:

Молоко:

Вредное произв.:

ОПЛАТА ТРУДА НА ВРЕДНОМ ПРОИЗВОДСТВЕ

ФИО

виды работ

Отработано (ч)

Всего

Оплата труда

Доплата

всего

отгулы

пн

вт

ср

чт

пт

дней

часов

Петр

Обычн.

?

?

?

?

?

?

Вредн.

?

?

?

?

Олег

Обычн.

x

x

x

x

x

?

?

?

?

?

?

Вредн.

x

x

x

x

x

?

?

?

?

Задание 8. Произвести расчеты с покупателем за товар при наличной (нал) и безналичной (безнал) формах оплаты. Исходная цена товара представлена в таблице исходя их наличной оплаты. Оплата может осуществляться за наличный/безналичный расчет в любой комбинации. Покупатель вносит сумму, которую он может оплатить наличными (Е6). Остаток суммы в форме безналичной оплаты вычисляется в ячейке F6 с учетом наценки за "безнал" (G1). Кроме того, для оптовых покупателей осуществляется бесплатная доставка груза. При цене партии от 10000 руб. - в пределах Москвы, от 50000 - в Московской области, от 90000 - в центральном районе РФ. В зависимости от этого в графе <Доставка> должно выводиться одно из слов: Москва, МО, Центр. В G10 и G11 подсчитывается объем заказов (в рублях) с доставкой в Область и Центр.

	A	B	C	D	E	F	G
	Сумма	10000р.	50000р.	90000р.		Наценка за б/нал	10%
	Доставка	Москва	МО	Центр
	ПРОДАЖА И ДОСТАВКА
	Товар	Кол-во	Цена	Цена партии	Оплата	Доставка
	Нал.	Безнал
	ххх	x	х р.	? р.	? р.	? р.	?
…	ххх	x	х р.	? р.	? р.	? р.	?
	ВСЕГО	?	?	?
	Доставка	МО	?
	Центр	?

Задание 9. Вычислить сумму оплаты товара при торговле за валюту. Исходная стоимость товара (В7) представлена в долларах. При оптовой покупке она может быть уменьшена на величину оптовой скидки: (при сумме покупки от 500$ до 1000$ - на 4%. до 3000$ - на 10%. свыше - на 15%). Новая цена вычисляется в ячейке С7. Сама оплата может осуществляться за любую из трех валют (доллары, евро, рубли) в произвольной комбинации по выбору покупателя. Суммы в первых двух валютах указывает покупатель. Если они недостаточны для покупки, остаток вычисляется в рублевом эквиваленте (F7). Соотношение всех валют на день покупки содержатся в курсовой таблице (А1:ВЗ)

	A	B	C	D	E	F
	Курсы валют:	Сумма:	$500	$1 000	$3 000
	$	€	Скидка:	4%	10%	15%
	ХХ р.	ХХ р.
	ТОРГОВЛЯ
	Товар	Сумма	Оплата
	покупки	со скидкой	USD	€	Руб.
	ххх	$ххх	$ ?	$ xxx	ххх €	?
…	….
	Всего		?	?	?	?

ФУНКЦИИ ГПР, ВПР

Задание 10.Рассчитать итоговую заработную плату рабочего, в зависимости от разряда. Оклад увеличивается на соответствующий повышающий коэффициент. Для извлечения соответствующих коэффициентов и премий использовать функцию ГПР.

	A	B	C	D	E	F	G
	Разряд
	Коэффициент	1,3	1,6	1,9	2,2	2,5	2,8
	Премия
	ЗАРПЛАТА
	ФИО	Оклад	Разряд	Премия	Повышающий коэффициент	Зарплата	Роспись
	Иванов			? (200)	? (1,9)	? (1150)
	Петров			? (350)	? (2,8)	? (1460)

Методические указания к зданию

Решение:

< Премия > = ГПР (Разряд; $B$1:$G$3; 3 строка)

т.е. D6 = ГПР (C6; $B$1:$G$3; 3)

< Повышающий коэффициент > = ГПР (Разряд; $B$1:$G$3; 2 строка)

т.е. D6 = ГПР (C6; $B$1:$G$3; 2)

< Зарплата > = ( < Оклад > * < Повышающий коэффициент > ) + <Премия>

т.е. F6 = ( B6 * E6 ) + D6

Задание 11.Рассчитать цену билета полета на самолете, в зависимости от класса полета. Стоимость билета для взрослого человека задана в таблице тарифов, детский билет стоит в половину дешевле.