Лабораторная работа №3
Построить таблицу с заданной структурой и произвольными данными размером не менее трех-пяти строк (обозначены многоточием). При построении формул предусмотреть возможность их копирования без искажения сути, т.е. выполнить в нужных случаях фиксацию ("замораживание") ссылок.
В задаче, кроме вычислений, относящихся к отдельным объектам (обычно, они находятся в строках), получить некоторые сводные характеристики, с использованием функций СЧЁТЕСЛИ( ), СУММЕСЛИ( ), MAKC( ), МИН( ), НАИБОЛЫШЙ( ), НАИМЕНЬШИЙ( ) и др., а также функциями, использующими массивы: (=СУММ(ЕСЛИ(...))} и {=СЧЁТ(ЕСЛИ(...})}. Итоговые функции должны строиться так, чтобы допускались вставки новых данных в любом месте области обработки без их редактирования.
После содержательного заполнения таблицы нужно выполнить все необходимые действия по ее оформлению: выравнивание, обрамление, заливка, выделение заголовков шрифтами и другое форматирование Данных, включая установление единиц измерения. Здесь следует использовать готовые (если есть) или собственные форматы, например:
32,8р, 25т.р, 10 тонн, 45кг, 3000км и т.п.
В каждом задании необходимо организовать:
• Встроенный контроль ввода (пункт Контроль в формулировке задачи).
• Условное форматирование (пункт Форматирование) для указанных ячеек. Форматирование, если не задано иначе, может быть любым -заливка, рамка и т.п.
• Результаты отобразить в виде графика (в пункте График указаны данные, которые должны быть связаны в диаграмме). Тип графика выбирается, исходя из наибольшей наглядности.
Исходные данные в таблицах отображены символами XXX, а вычисляемые -символами ???. Для этих вычисляемых ячеек следует составить клеточные функции. Хотя в некоторых случаях для наглядности данные заданы конкретными числами, при построении таблиц имеется в виду, что ВСЕ исходные данные могут иметь переменные значения. Длина таблицы также может быть произвольной.
Замечание. Все указанные соотношения в таблицах имеют условный характер и не отражают реальное законодательство.
Вариант 1.
Вычислить современную (на текущий год) стоимость основных фондов предприятия с учетом их износа и инфляции. О каждом объекте известны год и цена приобретения. Стоимость каждого объекта уменьшается за счет износа на 10% в год за период от <Года приобретения> до текущего <Года> и увеличивается на величину <Инфляции> (положим, средняя инфляция составляет 30% в год). Сначала нужно вычислить вспомогательное значения:
• Число лет инфляции в период эксплуатации объекта, с учетом того, что она возникла только с 1992 года.
• Коэффициент инфляции - во сколько раз возросла стоимость объекта за счет инфляции. Его значение всегда >1 (1 - инфляция еще не повлияла).
- Коэффициент износа - какая часть объекта еще не изношена. Его значение находится в диапазоне от 1 до 0 (0 - полный износ).
Имея эти данные, вычислить современную стоимость, как произведение исходной цены, коэффициента инфляции и коэффициента износа. При полном износе современная стоимость равна нулю. В этом случае в столбце G следует вывести не 0 руб., а слово "Списать". В клетке С9 подсчитывается число списанных объектов. В области С10:С13 подсчитать современную суммарную стоимость фондов по "возрастным" категориям.
|
A |
B |
C |
D |
E |
F |
G |
1 |
Износ: |
10% |
|
|
|
Год: |
1999г. |
2 |
Инфляция: |
30% |
|
|
|
|
|
3 |
ПЕРЕОЦЕНКА ФОНДОВ |
||||||
4 |
Название |
Год приобрет |
Цена |
Лет инфл. |
Коэфф. инфл |
Коэфф. износа |
Современная стоимость |
5 |
Станок |
ХХХХг |
ХХр. |
???л |
??? |
??? |
???р. |
...... |
|
|
|
|
|
|
|
8 |
ВСЕГО |
|
???р. |
|
|
|
???р. |
9 |
Списано: |
|
???шт. |
|
|
|
|
10 |
"Возраст" |
до5 лет |
???р. |
|
|
|
|
11 |
фондов |
до 10 лет |
???р. |
|
|
|
|
12 |
|
до 20 лет |
???р. |
|
|
|
|
13 |
|
свыше 20 |
???р. |
|
|
|
|
Контроль В5:В7: Год приобретения > 1950г.
Форматирование G5:G7: слово "Списать" на красном фоне.
График: Название - Современная стоимость.
Вариант 2.
Вычислить стоимость автоперевозок заданного веса груза на заданное расстояние разными типами автомобилей. Путевая скорость всех типов а/м считается равной 50 км/час. Здесь
-
<Число ездок> это <Вес груза>/<Грузоподъемность>, округленное до большего целого;
-
<Пробег> это <Число ездок>*<Расстояние>*2 (удваивается поскольку автомобиль каждый раз должен возвращаться в исходный пункт).
-
<Стоимость перевозок> состоит из зарплаты и стоимости аренды.
-
<3арплата> водителя определяется <Временем в пути>.
-
Кроме того, если автомобиль находится в пути в оба конца больше 12 часов, водителю производится доплата (командировочные) в размере 50 руб. за каждые 12 часов в пути на каждом маршруте.
-
В области D3:D5 подсчитывается число машиночасов (время в пути), необходимых для обслуживания заявок на перевозки разными типами а/м. Для извлечения данных из таблицы тарифов использовать функцию ВПР()
|
A |
B |
C |
D |
E |
F |
G |
H |
|||||
1
|
Тарифы
|
|
|
|
|||||||||
2
|
Тип автомобиля
|
Цена часа аренды
|
Грузоподъемность
|
Объем заказов
|
Цена часа работы водителя
|
||||||||
3
|
Зил
|
50р
|
4т
|
??ч
|
Хр
|
||||||||
4
|
Газ
|
ЗОр
|
Зт
|
??ч
|
|
||||||||
5
|
Камаз
|
50р
|
5т
|
??ч
|
|||||||||
6
|
АВТОПЕРЕВОЗКИ
|
||||||||||||
7
|
Тип а/м
|
Вес груза
|
Расстояние
|
Число ездок
|
Время в пути
|
Пробег
|
Зарплата
|
Стоимость перевозок
|
|||||
8
|
Зил
|
ХХт
|
ХХкм
|
??
|
??ч
|
??км
|
??р
|
??Р
|
|||||
|
|
|
|
. . .
|
|
|
|
|
|||||
11
|
Всего
|
???т
|
??км
|
|
??ч
|
??км
|
??р
|
??Р
|
Контроль А8:А10: Тип а/м ={3ил, Газ, Камаз}.
Форматирование А8:А10: если Пробег> 1000км.
График: Тип а/м - Объем заказов.
Вариант3.
Вычислить заработанную рабочим сумму в зависимости от количества отработанных им в неделю часов и их вида..
- <Зарплата > определяется как число отработанных <Нормальных> часов, умноженных на <Стоимость нормального часа> плюс стоимость сверхурочных часов и часов, отработанных в выходные дни. Стоимость таких часов увеличивается на 150% и 200% относительно "нормального" часа;
-
Если общее число отработанных часов превышает 52, работник получает <Доплату> в 100 руб., если больше 60 часов - 200 руб., если больше 66 - 250 руб. и еще 5% от зарплаты;
-
Сумма, выдаваемая <На руки>, это <Зарплата>+<Доплата> с учетом <Налога>.
-
В строке "В среднем" подсчитываются соответствующие средние значения.
-
В области С13:С15 показать фамилии работников, занявших первые три места по суммам, полученным <На руки>.
|
A |
B |
C |
D
|
Е
|
F
|
G
|
||
1
|
Стоимость часа
|
|
Налог:
|
13%
|
|||||
2
|
Нормальный:
|
Хр
|
|
||||||
3
|
Сверхурочный:
|
150%
|
|||||||
4
|
В выходные: _
|
200%
|
|||||||
5
|
ЗАРПЛАТА
|
||||||||
6 7
|
Фамилия
|
Отработано
|
Зарплата
|
Доплата
|
На руки
|
||||
Норм.
|
Сверх.
|
Выходи.
|
|||||||
8
|
XXX
|
ХХХч
|
ХХХч
|
ХХХч
|
???р
|
???р
|
???р
|
||
|
|
|
|
• • •
|
|
|
|
||
11
|
ВСЕГО
|
???ч
|
???ч
|
???ч
|
???р
|
???р
|
???р
|
||
12
|
В среднем:
|
???ч
|
???ч
|
???ч
|
???р
|
|
|
||
13
|
Лидеры:
|
1 место
|
???
|
|
|||||
14
|
2 место
|
???
|
|||||||
15
|
3 место
|
???
|
Контроль А8:А10: Фамилия ={Петров, Куликов, Васин, Рыбин}.
Форматирование Е8:Е10: если Отработано всего > 50час.
График: Фамилия - выдать На руки.