Табличный процессор Excel Лабораторная работа № 8
Проектирование таблицы для расчета
Постановка задачи: Построить таблицу согласно варианту постановки задачи с произвольными данными размером не менее пяти строк. При построении формул предусмотреть возможность их копирования без искажения сути, т.е. выполнить в нужных случаях фиксацию («замораживание») ссылок. Итоговые функции должны строиться так, чтобы допускались вставки новых данных в любом месте области обработки без их редактирования.
После содержательного заполнения таблицы выполнить все необходимые действия по ее оформлению: выравнивание, обрамление, заливка, выделение заголовков шрифтами и другое форматирование данных, включая установление единиц измерения, используя готовые или собственные форматы.
В каждом задании необходимо организовать:
Макет таблицы с указанием ячеек с произвольными данными и формулами.
Тестовые варианты для 3х строк таблицы, проверенные вручную.
Математическую модель с указанием ячеек с числовыми данными и их форматов, а также описанием алгоритма вычислений по формулам.
Встроенный контроль ввода.
Условное форматирование для указанных ячеек. Форматирование, если не задано иначе, может быть любым – заливка, рамка, шрифт по выбору исполнителя.
Примечание к указанным ячейкам, содержащим данные или формулы.
Печать готовой таблицы с тестовыми вариантами.
После выполнения задания оформляется отчет, содержащий:
тему работы;
постановку задачи;
макет таблицы;
тестовые варианты;
математическую модель;
алгоритм ввода формулы (по выбору преподавателя);
алгоритм оформления контроля ввода;
алгоритм оформления условного форматирования;
алгоритм оформления примечания;
готовую таблицу с тестовыми вариантами;
Пример. Спроектируйте таблицу для решения задачи Расчет квартплаты:
ПОСТАНОВКА ЗАДАЧИ: Расчет квартплаты: Расчет платы за квартиру состоит из оплаты за коммунальные услуги (стоимость 1 м2 умноженная на площадь), обслуживание здания (% износа от стоимости здания) и газоснабжение (число проживающих умноженное на стоимость газа).
Предусмотреть контроль ввода для площадей и проживающих (только положительные числа). Квартплата выше 1000 выводится красным курсивом на желтом фоне. Для ячейки с формулой расчета стоимости обслуживания здания для отдельной квартиры создать примечание и показать зависимости.
МАТЕМАТИЧЕСКАЯ МОДЕЛЬ:
Макет таблицы:
|
A |
B |
C |
D |
E |
F |
G |
H |
1 |
Расчет квартплаты для жильцов дома № 5 |
Цена кв. метра |
10,00р. |
|||||
2 |
№ кв. |
площадь |
кол-во прожив. |
коммунальные услуги |
оплата газа |
итого |
Цена газа |
3,00р. |
3 |
1 |
ХХХ кв.м |
ХХХ чел. |
??? р. |
??? р. |
??? р. |
Обслуживание здания |
0,2% |
4 |
2 |
|
|
|
|
|
Стоимость здания |
100000,00р. |
5 |
3 |
|
|
|
|
|
Износ здания |
15% |
6 |
4 |
|
|
|
|
|
ИТОГО обслуживание |
??? р. |
… |
… |
|
|
|
|
|
|
|
14 |
12 |
|
|
|
|
|
|
|
16 |
ИТОГО: |
??? р. |
|
|
|
|
||
Тестовые варианты:
Итого обслуживание (H6) – 170 р.
|
A |
B |
C |
D |
E |
F |
2 |
№1 кв. |
площадь |
кол-во прожив. |
Ком. услуги |
оплата газа |
итого |
3 |
1 |
25 кв.м |
1 чел |
250 р. |
3 р. |
423 р. |
4 |
2 |
50 кв.м |
5 чел |
500 р. |
15 р. |
685 р. |
5 |
3 |
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) |
ВЫПОЛНЕНИЕ РАБОТЫ: