Проектирование таблицы для расчета

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

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

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

Ø Макет таблицы с указанием ячеек с произвольными данными и формулами.

Ø Тестовые варианты для 3^х строк таблицы, проверенные вручную.

Ø Математическую модель с указанием ячеек с числовыми данными и их форматов, а также описанием алгоритма вычислений по формулам.

Ø Встроенный контроль ввода.

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

Ø Примечание к указанным ячейкам, содержащим данные или формулы.

Ø Печать готовой таблицы с тестовыми вариантами.

После выполнения задания оформляется отчет, содержащий:

Ø тему работы;

Ø постановку задачи;

Ø макет таблицы;

Ø тестовые варианты;

Ø математическую модель;

Ø алгоритм ввода формулы (по выбору преподавателя);

Ø алгоритм оформления контроля ввода;

Ø алгоритм оформления условного форматирования;

Ø алгоритм оформления примечания;

Ø готовую таблицу с тестовыми вариантами;

 

Пример. Спроектируйте таблицу для решения задачи Расчет квартплаты:

ПОСТАНОВКА ЗАДАЧИ: Расчет квартплаты: Расчет платы за квартиру состоит из оплаты за коммунальные услуги (стоимость 1 м² умноженная на площадь), обслуживание здания (% износа от стоимости здания) и газоснабжение (число проживающих умноженное на стоимость газа).

Предусмотреть контроль ввода для площадей и проживающих (только положительные числа). Квартплата выше 1000 выводится красным курсивом на желтом фоне. Для ячейки с формулой расчета стоимости обслуживания здания для отдельной квартиры создать примечание и показать зависимости.

 

МАТЕМАТИЧЕСКАЯ МОДЕЛЬ:

Макет таблицы:

	A	B	C	D	E	F	G	H
	Расчет квартплаты для жильцов дома № 5	Цена кв. метра	10,00р.
	№ кв.	площадь	кол-во прожив.	коммунальные услуги	оплата газа	итого	Цена газа	3,00р.
		ХХХ кв.м	ХХХ чел.	??? р.	??? р.	??? р.	Обслуживание здания	0,2%
							Стоимость здания	100000,00р.
							Износ здания	15%
							ИТОГО обслуживание	??? р.
…	…
	ИТОГО:	??? р.

Тестовые варианты:

Итого обслуживание (H6) – 170 р.

	A	B	C	D	E	F
	№1 кв.	площадь	кол-во прожив.	Ком. услуги	оплата газа	итого
		25 кв.м	1 чел	250 р.	3 р.	423 р.
		50 кв.м	5 чел	500 р.	15 р.	685 р.
		30 кв.м	3 чел	300 р.	9 р.	479 р.

Вводимые числовые данные:

Ячейки	Диапазон, формат, способ ввода, контроль ввода
А3:А14	Целое число, вводится как арифметическая прогрессия от 1 до 12 с шагом 1
B3:B14	Произвольное положительное вещественное число (контроль ввода) с двумя десятичными разрядами, с обозначение «кв.м.»
C3:C14	Произвольное положительное целое число (контроль ввода) с обозначение «чел».

Формулы:

Ячейка ввода, формат	Копирование	Формула Excel
H6 – имя обслуживание, денежный формат (р.), 2 десятичных разряда	-	=H4*(1-H5)*H3
D3, денежный формат (р.), 2 десятичных разряда	D4:D15	=B3*H$1
E3, денежный формат (р.), 2 десятичных разряда	E4:E15	=C3*H$2
F3, денежный формат (р.), 2 десятичных разряда, выше 1000 р. выводится красным курсивом на желтом фоне	F4:F15	=D3+E3+обслуживание
D16, денежный формат (р.), 2 десятичных разряда	E16:F16	=СУММ(D2:D15)

ВЫПОЛНЕНИЕ РАБОТЫ:

1. Заполнение таблицы текстовыми, числовыми данными и формулами согласно математической модели.

2. Оформление контроля ввода.

Ячейки	Параметры	Сообщение для ввода	Сообщение об ошибке
В3:В14	Тип данных: действительное, Значение: больше, Минимум: 5	Заголовок: Площадь квартиры, Сообщение: больше 5	Вид: Предупреждение, Заголовок: Ошибка ввода, Сообщение: больше 5
C3:C14	Тип данных: целое число, Значение: больше или равно, Минимум: 1	Заголовок: Проживает, Сообщение: не меньше 1	Вид: Останов, Заголовок: Ошибка ввода, Сообщение: не меньше 1

3. Оформление условного форматирования.

Ячейка	Параметры условного форматирования
F3: F15	Значение - >= 1000. Формат- красный курсив на желтом фоне.

4. Оформление примечаний.

Ячейка	Примечание	Команда для выполнения
H6	Стоимость обслуживания здания для отдельной квартиры	Добавить примечание контекстного меню ячейки

5. Показ зависимостей в формулах.

РЕЗУЛЬТАТ РАБОТЫ:

Расчет квартплаты для жильцов дома № 5	Цена кв. метра	10,00р.
№ кв.	площадь	кол-во прожив.	коммунальные услуги	оплата газа	итого	Цена газа	3,00р.
	25,00 кв. м.	1чел.	250,00р.	3,00р.	423,00р.	Обслуживание здания	0,2%
	50,00 кв. м.	5чел.	500,00р.	15,00р.	685,00р.	Стоимость здания	100000,00р.
	30,00 кв. м.	3чел.	300,00р.	9,00р.	479,00р.	Износ здания	15%
	125,00 кв. м.	3чел.	1250,00р.	9,00р.	1429,00р.	Обслуживание квартиры	170,00р.
	45,00 кв. м.	2чел.	450,00р.	6,00р.	626,00р.
	14,00 кв. м.	1чел.	140,00р.	3,00р.	313,00р.
	15,00 кв. м.	1чел.	150,00р.	3,00р.	323,00р.
	56,00 кв. м.	3чел.	560,00р.	9,00р.	739,00р.
	65,00 кв. м.	4чел.	650,00р.	12,00р.	832,00р.
	70,00 кв. м.	4чел.	700,00р.	12,00р.	882,00р.
	47,00 кв. м.	2чел.	470,00р.	6,00р.	646,00р.
	47,00 кв. м.	1чел.	470,00р.	3,00р.	643,00р.
ИТОГО:	5 890,00р.	90,00р.	8190,00р.

 

Варианты заданий для самостоятельного выполнения:

№	Постановка задачи
	Вычислить заработанную рабочим сумму в зависимости от количества отработанных им в неделю часов и их вида. <Зарплата> определяется как число отработанных <Нормальных> часов, умноженных на <Стоимость нормального часа> плюс стоимость сверхурочных часов и часов, отработанных в выходные дни. Стоимость таких часов увеличивается на 150% и 200% относительно «нормального» часа.
	Вычислить <Стоимость всего> товара, хранящегося на складе магазина. Она определяется стоимостью товара первого сорта (<Число единиц 1 сорта>, умноженной на <Цену 1 сорта>) плюс стоимость товара 2 сорта (<Число единиц 2 сорта>, умноженной на <Цену 1 сорта>, уменьшенную на <Процент скидки 2 сорта>), плюс стоимость товара 3 сорта, полученную аналогичным образом, плюс стоимость просроченного товара по цене 10% от цены 1 сорта.
	Вычислить сумму налога и сумму «на руки» для работников производства. <Налог %> составляет 13% от заработка, однако не от всего. <Сумма обложения> меньше <Зарплаты> на одну <Минимальную зарплату> и еще на одну <Минимальную зарплату> каждого ребенка в семье.
	Вычислить материальную помощь нуждающимся пенсионерам. <Расчетная помощь> определяется как процент от <Минимальной зарплаты> в зависимости от наличия детей (<На ребенка> - 80%).
	Вычислить <Общую стоимость> объекта. Она вычисляется как <Стоимость квадратного метра земли>, умноженная на <Площадь> + <Стоимость дома> и плюс по одному проценту от <Стоимости участка> за водопровод и электроэнергию.
	Вычислить налог на недвижимость. <Налог> является суммой налога на землю и налога на строение. Известна <Стоимость квадратного метра земли>, <Площадь участка>, <Стоимость дома>.
	Определить пенсию по старости. Пусть она назначается в размере четырех минимальных зарплат. Также пенсионерам полагается прибавка к пенсии в размере 5% за каждый отработанный год после достижения пенсионного возраста.
	Вычислить сумму оплаты товара в рублях при торговле в валюте на оптовом складе. Исходная стоимость товара представлена в долларах. Стоимость покупки она уменьшается на величину оптовой скидки.
	Произвести расчеты с покупателем за товар при наличной и безналичной формах оплаты. Покупатель вносит сумму, которую он может оплатить наличными. Остаток суммы в форме безналичной оплаты вычисляется с учетом наценки за «безнал».
	Произвести расчеты с клиентом за купленный товар с оплатой частями. Наценка на остаток составляет 1% в день. Покупатель указывает сумму, которую он может внести сразу и число дней, через которое будет оплачен остаток. Также находится полная сумма, вносимая покупателем за товар.