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

	А	В	С	D		Е	F	G
1	Цена 1 кв. метра:		Хр
2	Социальная норма:		Хм
3			КВАРТПЛАТА
4	Номер	Площадь	Человек	Этаж		Льготы	Квартплата
	квартиры					(и/у)
5							полная	фактич.
6	Х	Хм	Х	Х		Х	?р	?р
	• • •		. . .
	ВСЕГО	?м	?				?р	?р
	Льготники:					?

Контроль столбец Льготы (и/у): ввод из списка {и, у, иу}. Форматирование: Выделить номера квартир, расположенных на 1 или 2

этаже

Диаграмма: Номер квартиры - Квартплата фактическая.

Вариант 5

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

	А		В	С	D	Е	F		G
1		Стоимость часа					Налог		13%
2	Нормальный:			Хр
3	Сверхурочный:			150%
4	В выходные:			200%
5				ЗАРПЛАТА
6	Фамилия			Отработано		Зарплата	Доп-		На
							лата		руки
7			Норм.	Сверх.	Выходи.
8	Х		Хч	Хч	Хч	?р		?р	?р
	• • •				• • •
	ВСЕГО		?ч	?ч	?ч	?р		?р	?р
	В среднем:		?ч	?ч	?ч	?р
	Лидеры:		1 место	?
			2 место	?
			3 место	?
									31

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

Форматирование столбец Фамилия: если общее количество отработанных часов > 50час.

Диаграмма: Фамилия - На руки.

Вариант 6

Вычислить зарплату рабочего (столбец<3аработано>). Она определяется числом <Изготовленных им деталей>, умноженным на <Стоимость одной детали>. Заработок также зависит от <Разряда> рабочего. Он увеличивается на соответствующий <Разрядный коэффициент. Кроме того, если рабочий произвел более 30 деталей, ему начисляется премия в размере 50% от стоимости каждой детали, начиная с 31-й. Зарплата рабочего может быть и уменьшена в случае, если им было изготовлено свыше трех бракованных деталей — из заработанных сумм вычитается штраф в размере 50 руб. В столбце<Брак> выводится восклицательный знак, если бракованных деталей до пяти, сообщение "Брак", если от пяти до семи, и "Аврал", если больше семи. В ячейке F… подсчитывается число рабочих, допустивших брак в количестве от пяти деталей. Нужные разрядные коэффициенты извлекаются из таблицы функцией ВПР( ). В области Число рабочих подсчитать число рабочих, имеющих соответствующий разряд.

	А		В		С		D	Е	F
1	Стоимость				Хр		Разряд	коэффициент	Число
	детали:								рабочих
2							1	1,0	?
3							2	1,1	?
4							3	1,2	?
5							4	1,4	?
6						ЗАРПЛАТА
7	Фамилия		Разряд			Деталей		Заработано	Брак
	рабочего
8					изготовлено		брака
9	X			Х		Хшт	Хшт	?р	?
	• • •					• • •
	ВСЕГО					?шт	?шт	?р
							Бракоделы:		?чел
	Контроль столбец Разряд: Разряд ={1, 2, 3, 4}.
	Форматирование: Выделите					фамилии рабочего (столбец Фамилия

рабочего), допустивших брак до 5 деталей желтым цветом, а от 5 и выше - на красном фоне.

Диаграмма: Фамилия – Число бракованных деталей.

32

Вариант 7

Вычислить ежедневный и недельный заработок рабочего. Ежедневный заработок (колонки <3аработано>) определяется числом <Отработанных часов>, умноженных на стоимость рабочего часа, которая непостоянна. Она увеличивается на 50% за сверхурочные часы (время, отработанное свыше 8 часов). Субботние часы оплачиваются по тарифу, увеличенному на 90%. Заметим, что и в субботу возможен сверхурочный труд. Кроме того, если рабочий отработал в течение недели больше 55 часов, он получает прибавку в сумме <Минимальной зарплаты>, а если больше 65 — две минимальные зарплаты. В строке Передовики выводится фамилия человека, отработавшего максимальное число часов и фамилия работника, получившего максимальную зарплату на этой неделе.

А

В

С

D

Е

F

G

Н

I

J

К

L

М

N

О

1

Стоимость часа работы

Минимальная зарплата:

Хр

2

Обычного

690р

3

Cвepxypoчного

50%

4

Доплата за субботу

90%

5

HEДЕЛЬНАЯ ЗАРПЛАТА

Работник

Отработано часов

Заработано

пн

вт

ср

чт

пт

сб

всего

пн

вт

ср

чт

пт

сб

всего

X

Хч

Хч

Хч

Хч

Хч

Хч

?ч

?р

?р

?р

?р

?р

?р

?р

• • •

Итог

?ч

?ч

?ч

?ч

?ч

?ч

?ч

?р

?р

?р

?р

?р

?р

?

Передовики

?

?

Контроль диапазон

ячеек

Отработано

часов: Отработано часов

в

день<=12часов.

Форматирование столбец Заработано всего: если Всего >3000руб. Диаграмма: Работник - Всего часов.

Вариант 8

Определить <Новую цену> товара, продаваемого в комиссионном магазине. О каждом <Товаре> известна <Дата сдачи> его на комиссию и исходная, установленная в этот момент на него, цена. По условиям магазина после первых 15-ти дней товар подвергается уценке на 5%, после 30-ти — еще на 10% и далее каждый день на один процент. Цена товара со всеми уценками отображается в колонке <Цена с уценкой>. <Новая цена> равна <Цене с уценкой> до тех пор, пока последняя не становится менее четверти исходной цены (по условиям договора товар не может быть уценен более чем на 75%). В клетке D… подсчитывается число предметов, которые не удалось продать более чем за 30 дней, а в D… — более чем за 50 дней.

33

	А	В	С	D	Е	F
1	Уценка	товара:			Сегодня:	dd.mm.gggg
2	Дни	%
3	15	5%
4	30	10%
5			ЦЕНА ТОВАРА
6	Товар	Дата сдачи	Исходная	Дней	Цена с	Новая цена
			цена	хранения	уценкой
7	X	dd.mm.gggg	Хр	? дн.	?р	?р
	• • •
	ВСЕГО		?р	? дн.	?р	?р
	Не продано:		за 30 дней	?шт
			за 50 дней	?шт

Контроль столбец Исходная цена: Исходная цена >=100руб. Форматирование столбец Товар: выделить товары, если дней

хранения >50.

Диаграмма: Товар - Дней хранения

Вариант 9

Вычислить размер стипендии в зависимости от среднего балла, полученного в сессию, и наличия детей. Средний балл считается равным нулю, если у студента есть задолженности - двойка по одному предмету или "незачет" по одному зачету. Зачет обозначается буквой "з" в колонке зачетов, незачет - буквой "н". Отсутствие на конец сессии экзаменационных оценок или отметок о зачетах/незачетах хотя бы по одному предмету (при условии, что остальные оценки положительны) означает, что сессия данного студента продлена. Этот студент получает только доплаты на детей, а в столбце "Продлено" ставится отметка "+". Считаем, что всем студентам, не имеющим задолженностей, назначается стипендия. Пусть "обычная" стипендия равна минимальной зарплате. Полагаем также, что отличники получают стипендию на 100% выше номинальной, а студенты, не имеющие троек, — на 50%. Кроме того, студенты с детьми получают по одной минимальной зарплате на каждого ребенка. В последней строке Всего подсчитывается: число человек, сдавших отдельные предметы, средний балл для всей студенческой группы, число студентов, которым была продлена сессия. А также подсчитать число отличников и число студентов, сдавших сессию без троек.

34

	А	В	С		D		Е	F	G	Н	I
1	Минимальная зарплата:							Хр
2						СТИПЕНДИЯ
3	Студент		Экзамены				Зачет	Средн.	Число	Стипендия	Сессия
								балл	детей		продлена
4		Этика	Физика		Химия		Логика
5	X	Х		Х		Х	х	?	Х	?р	?
	• • •						• • •
	Всего	?ч		?ч		?ч	?ч	?	?	?р	?ч
	Отличники	?ч
	Без троек	?ч

Контроль диапазона ячеек Экзамен: 2<Экзамен<=5. Форматирование столбец Студент: если средний балл=5. Диаграмма: Студент - Стипендия.

Вариант 10

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

	А		В	С	D	Е	F	G
1	Доплаты					Текущий год:		dd.mm.gggg
2	На ребенка		80%			Миним. зарплата:		Хр
3	Инвалид		100%
4	Уч. ВОВ		200%
5	Старше70-ти		100%
					ПОМОЩЬ
	Фамилия	Год рожд.		Детей	Льготы	Пенсия	Расчетная	Фактич.
							помощь	помощь
	Х		Хг	Х	Х	Хр	?р	?р
	• • •				• • •
	Всего			?		?р	?р	?р
	Возрастные		до 65	до 70	>70
	группы:		?ч	?ч	?ч

Контроль столбец Льготы: Льготы={и, у, иу}.

Форматирование столбец Фамилии: если человек старше 70-и лет. Диаграмма: Фамилия - Фактическая помощь.

35

Вариант 11

Вычислить суммы вкладов клиентов банка на конец года. Все вкладчики банка получают <Премию> в зависимости от суммы и длительности вклада. Для этого сначала вычисляется <Средняя сумма> как сумма остатков вклада за все кварталы, деленная на четыре. Далее компенсируется годовая <Инфляция> (сумма вклада за 4 квартал увеличивается на 12%). Затем, в зависимости от величины вклада, определяется собственно премия. Если вклад (<Средняя сумма>) до 5 тыс. руб. - премия составляет 18%, если от 5 до 20 тыс. - 25%, если от 20 до 30 тыс. - 30%, если свыше 30 тыс. рублей -35%. Кроме того, учитывается длительность вклада. За каждый квартал хранения (т.е. где остаток не равен нулю) добавляется еще по 1% от <Средней суммы>. В столбце Н в диапазоне ячеек Число вкладов следует подсчитать число вкладов (средних сумм), находящихся в заданных пределах (до 5т., до 20т. и т.д.).

	A		В	С	D	Е	F	G	Н
1	Инфляция:			12%			Премия		Число
									вкладов:
2							Вклад	Процент
3							до 5т.	18%	?шт
4							до 20т.	25%	?шт
5							до 30т.	30%	?шт
6							более 30	35%	?шт
7				БАНКОВСКИЕ ВКЛАДЫ
8	№ вклада		Остатки вкладов по кварталам				Средняя	Премия	Всего
							сумма
9			1кв.	2кв.	3 кв.	4 кв.
	X		Хр	Хр	Хр	Хр	?р	?р	?р
	• • •
	Всего		?р	?р	?р	?р	?р	?р	?р

Контроль столбец № вклада: Номер счета = 8 символов. Форматирование столбец № вклада: если средняя сумма >100 000руб. Диаграмма: Номер счета - Всего.

Вариант 12

Вычислить сумму налога и сумму "на руки" для работников производства. <Налог %> составляет 13% от заработка, однако, не от всего. <Сумма обложения> меньше <3арплаты> на одну <Минимальную зарплату> и еще на одну <Минимальную зарплату> за каждого ребенка в семье. Кроме того, инвалиды и участники войны имеют льготы при налогообложении в 15% и 20% соответственно относительно "обычного" налога. Эти лица отмечаются в колонке <Льготы> буквами "и", "у", "иу" (человек может быть одновременно инвалидом и участником, т.е. ему полагается две льготы). В таблице следует не допустить отрицательность суммы обложения (например, если в данном месяце заработок мал). В строке ВСЕГО подсчитывается

36

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

		А	В	С	D	Е	F	G
	1	Льготы:			Налог %:		13%
	2	Инвалид	15%		Миним. зарплата:		Хр
	3	Уч. BOB	20%
	4				НАЛОГИ
	5	Фамилия	Число детей	Зарплата	Льготы		Сумма
	6					обложения	налога	на руки
	7	Х	Х	Хр	Х	?р	?р	?р
		• • •			• • •
		ВСЕГО	?	?р	?чел	?р	?р	?р
		Льготники:	инвалиды:		?чел
			участники:		?чел

Контроль столбец Льготы: Льготы={и, у, иу}.

Форматирование столбец на руки: если Выдать на руки >5000руб. Диаграмма: Фамилия - Выдать на руки.

Вариант 13

Вычислить размер заработка продавцов фирмы. Зарплата работника состоит из двух частей - фиксированного небольшого <Оклада>, определяемого <Разрядом>, и <Премии>, зависящей от фактического объема продаж (<Продано>). Если объем продаж меньше <Нормы>, она составляет 5% от <Продано>, если больше — 15%, если больше в два раза, добавляется еще 1000 руб. В ячейке D1 вычислить количество человек, продавших товаров более чем на 50000 руб. Для определения оклада следует воспользоваться функцией ВПР( ). В области Призеры показать фамилии продавцов, занявших по объему продаж первые три места и суммы их продаж.

	А	В	С	D	Е	F	G	Н
1	Норма:	Хр	Кол-во	? чел.			Разряд	Оклад
			человек с
			продажами
			> 50000
2		ЗАРПЛАТА ПРОДАВЦОВ					1	Хр
3	Ф.И.О.	Разряд	Продано	Премия	Заработок		2	Хр
4	X	Х	Хр	?р	?р		3	Хр
	• • •						4	Хр
	Всего		?р	?р	?р		5	Хр
			1 место	?	?р
	Призеры:		2 место	?	?р
			3 место	?	?р
	Контроль столбец Разряд: Разряд={1,2,3,4,5}.
								37

Форматирование столбец Ф.И.О.: если Продано > 100 000руб. Диаграмма: Фамилия - Заработок.

Вариант 14

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

А

В

С

D

Е

F

G|

Н

I

J

К

L

М

1

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

Сверхурочная

200%

2

Обычное производство

8р

Молоко

20р

3

Вредное производство

10р

4

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

5

ФИО

виды

Отработано часов

всего

оплата

До-

всего

отгу

работ

труда

плата

лы

6

пн

вт

ср

чт

пт

дней

часов

Х

обыч.

Х

Х

Х

Х

Х

?

?ч

?р

?р

?р

?

вред.

Х

Х

Х

Х

Х

?

?ч

?р

?р

Х

обыч.

Х

Х

X

X

Х

?

?ч

?р

?р

?р

?

вред.

Х

Х

X

X

Х

?

?ч

?р

?р

• • •

Итог

обыч.

?

?

?

?

?

?

?ч

?р

?р

?р

?дн

вред.

?

?

?

?

?

?

?ч

?р

?р

Контроль диапазон Отработано часов: Отработано в день <=14час. Форматирование: выделить фамилии работников, получившие отгулы

и в диапазоне ячеек Отгулы (столбец М): слово "Отгул". Диаграмма: Фамилия - Всего.

38

Вариант 15

Вычислить стоимость заказов в фирме, торгующей однородным жидким товаром. Товар отпускается бочками по 150 кг, а остаток — канистрами по 40 кг. Известна цена продукта и цена тары. В таблице сначала следует определить, сколько полных бочек уйдет под товар. Остаток поставляется в канистрах. Поскольку и бочки и канистры заполнены целиком, может оказаться, что Вес отгрузки несколько больше заказанного. Полная стоимость будет состоять из товара, размещенного в бочках и канистрах с учетом стоимости тары. Кроме того, следует учитывать скидки оптовым покупателям. Если полная стоимость превышает установленный Порог скидки, разность между полной стоимостью и порогом оплачивается по цене на 10% меньше обычной. Сказанное относится только к самому товару (стоимость тары не снижается). Для определения числа канистр следует воспользоваться функцией OKPBBEPX( ). Подсчитать число заказанных партий товара весом более 10000 кг (Крупные оптовики).

	А	В	С	D	Е		F	G
1	Тара	Объем	Цена		Цена за 1 кг			Хр
2	бочки	150кг	Хр		Порог скидки			Хр
3	канистры	40кг	Хр
4			ОБРАБОТКА 3АКАЗОВ
5	Заказчик	Вес	Число		Вес		Стоимость
		заказа			отгрузки
6			бочек	канистр			полная	со скидкой
	X	Хкг	?шт.	?шт.	?кг		?р	?р
	• • •			. . .
	Всего	?кг	?шт.	?шт.	?кг		?р	?р
	Крупные оптовики				?зак

Контроль столбец Вес заказа: Вес заказа > 1000 кг. Форматирование столбец Заказчик: если назначается скидка. Диаграмма: Заказчик - Стоимость со скидкой.

Вариант 16

Определить стоимость обслуживания туристических экскурсий на маршрутах А, Б и т.д. О каждом маршруте известна стоимость собственно экскурсии и стоимость транспортных расходов. Известна также емкость автобуса. В самой таблице фиксируется желаемый маршрут и число заявок (человек) на обслуживание. Определить стоимость туров на маршрутах А, Б и т.д., о которых известны стоимость экскурсии и транспортных расходов. Сначала определяется потребное число полных автобусов. Выделено автобусов может быть на единицу больше, если окажется, что дополнительный автобус будет заполнен не менее, чем на 30% емкости. В противном случае, будет выделен этот минимум. <Стоимость> обслуживания

39

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

	А		В	С	D		Е	F
1			Тарифы на маршруты
2	Маршрут		А	Б	В		Г
3	Экскурсия		Хр	Хр	Хр		Хр
4	Транспорт		Хр	Хр	Хр		Хр
5	Отказано		? чел.	? чел.	? чел.		? чел.
6	В автобусе:		40 чел.
7			ОБСЛУЖИВАНИЕ ТУРИСТОВ
8	Номер		Число	Автобусов			Стоимость	Отказано
	маршрута		заявок				обслужив.
9				миним.	выделено
	Х		Х чел.	? шт.	? шт.		?р	? чел.
	• • •				• • •
	ВСЕГО		? чел.		? шт.		?р
	Крупные заявки:						?

Контроль столбец Номер маршрута: Номер маршрута={А, Б, В, Г}. Форматирование столбец Номер маршрута: если есть отказные

заявки.

Диаграмма: Номер маршрута - Выделено автобусов.

Вариант 17

Вычислить налог на недвижимость. Сначала определяется <Общая стоимость> объекта, облагаемая налогом. Она вычисляется как стоимость земли, плюс <Стоимость дома>, плюс по одному проценту от стоимости земли за водопровод и электроэнергию (обозначаются знаком "+" в колонках <Вода> и <Свет>). Если имеется и то и другое, то 5%. <Налог> является суммой налога на землю и налога на строение. Положим также, что налог на землю удваивается на каждый метр земли свыше 100 кв. метров и утраивается на каждый метр свыше 400. В области Участки подсчитать число участков соответствующей площади.

40