- •Введение
- •Лабораторная работа 1 сводные таблицы. Консолидация.
- •Практическая часть
- •Лабораторная работа 2 экономические расчеты с применением мастера функций
- •Практическая часть
- •Задания для самостоятельной работы
- •Лабораторная работа 3 матричные операции в excel
- •Задания для самостоятельной работы
- •Лабораторная работа 4 аппроксимация зависимостей
- •Практическая часть
- •Лабораторная работа 5 автома тизация работы в excel с помощью макросов
- •Практическая часть
- •Задание для самостоятельной работы
- •Лабораторная работа 6 списки и базы данных в excel
- •Практическая часть
- •Библиографический список
Задания для самостоятельной работы
Вариант 1
Вычислить современную стоимость основных фондов предприятия с учетом их износа и инфляции:
|
А |
В |
С |
D |
Е |
F |
G |
1 |
Износ |
10% |
|
|
Год |
2006г. |
|
2 |
Инфляция |
30%
|
ПЕРЕОЦЕНКА ФОНДА |
|
|||
3 |
|
|
|||||
4 |
Название |
Год приобретения |
Цена |
Лет инфляции |
Коэффициент инфляции |
Коэффициент износа |
Современная стоимость |
5 |
Станок |
ХХг. |
ХХр. |
??лет |
?? |
?? |
??р. |
…. |
|
|
|
…. |
|
|
|
8 |
ВСЕГО |
|
??р. |
|
|
|
??р. |
9 |
Списано: |
??шт. |
|
|
|
|
|
10 |
«Возрасти фондов |
до 5 лет |
??р. |
|
|||
11
|
до 10 лет |
??Р.
|
|||||
12
|
свыше 10 лет |
??р.
|
О каждом объекте известны год и цена приобретения. Стоимость каждого объекта уменьшается за счет износа на 10 % в год за период от <Года приобретения> до текущего <Года> и увеличивается на величину <Инфляции> (предположим, что средняя инфляция составляет 30 % в год). Сначала нужно вычислить вспомогательные значения:
число лет инфляции в период эксплуатации объекта, с учетом того, что она возникла только с 1992 г.;
коэффициент инфляции – во сколько раз возросла стоимость объекта за счет инфляции. Его значение всегда >1 (1 – инфляция еще не повлияла);
коэффициент износа – какая часть объекта еще не изношена. Его значение находится в диапазоне от 1 до 0 (0 – полный износ).
Имея эти данные, можно вычислить современную стоимость как произведение исходной цены, коэффициента инфляции и коэффициента износа. При полном износе современная стоимость равна нулю. В этом случае в столбце G следует вывести не 0 р., а слово «Списать». В клетке С9 подсчитывается число списанных объектов. В области С10:С13 вычислить современную суммарную стоимость фондов по «возрастным» категориям.
Контроль В5.В7: Год приобретения > 1950 г. Форматирование G5:G7: слово «Списать» на красном фоне. График: Название – Современная стоимость.
Вариант 2
Вычислить стоимость автоперевозок заданного веса груза на заданное расстояние разными типами автомобилей:
|
А |
В |
С |
D |
Е |
F |
G |
Н |
1 |
ТАРИФЫ |
|
|
|
|
|||
2 |
Тип автомобиля |
Цена часа аренды |
Грузоподъемность |
Объем заказов |
|
|
Цена часа работы водителя |
|
3 |
ЗИЛ |
50р. |
4т |
??ч
|
ХХр. |
|||
4 |
ГАЗ |
30р. |
Зт |
??ч
|
|
|
||
5 |
КамАЗ |
50р. |
5т |
??ч
|
||||
6 |
АВТОПЕРЕВОЗ
|
|||||||
7 |
Тип автомобиля |
Вес груза |
Расстояние |
Число ездок |
Время в пути |
Пробег |
Зарплата |
Стоимость перевозок |
8 |
ЗИЛ |
ХХт |
ХХкм |
?? |
??ч |
??км |
??р. |
??р. |
|
|
|
|
|
|
|
|
|
11 |
Всего |
???т |
??км |
|
??ч |
??км |
??р. |
??р. |
Путевая скорость всех типов а/м считается равной 50 км/ч. Здесь <Число ездок> – это <Вес груза>/<Грузоподъемность>, округленное до большего целого; <Пробег> – это <Число ездок>*<Расстояние>*2 (удваивается, поскольку автомобиль каждый раз должен возвращаться в исходный пункт). <Стоимость перевозок> состоит из зарплаты и стоимости аренды. <3арплата> водителя определяется временем в пути. Кроме того, если автомобиль находится в пути в оба конца больше 12 ч, водителю производится доплата (командировочные) в размере 50 р. за каждые 12 ч в пути на каждом маршруте. В области D3:D5 подсчитывается число машиночасов (время в пути), необходимых для обслуживания заявок на перевозки разными типами а/м. Для извлечения данных их таблицы тарифов использовать функцию ВПР().
Контроль А8:А10: Тип а/м ={ЗИЛ, ГАЗ, КамАЗ}. Форматирование А8:А10: если Пробег > 1 000 км. График: Тип а/м – Объем заказов.
Вариант 3
Вычислить заработанную рабочим сумму в зависимости от количества отработанных им в неделю часов и их вида:
|
А | В |
В |
С |
D |
Е |
F |
G |
||
1 |
СТОИМОСТЬ ЧАСА |
Налог |
13% |
||||||
2 |
Нормальный |
ХХр. |
|
||||||
3 |
Сверхурочный |
150% |
|
||||||
4 |
В выходные |
200% |
|
||||||
5 |
ЗАРПЛАТА |
||||||||
6 |
Фамилия |
Отработано |
Зарплата |
Доплата |
На руки |
||||
7 |
|
Норм. |
Сверх. |
Выходн. |
|
|
|
||
8 |
XX |
ХХч |
ХХч |
ХХч |
??р. |
??р. |
??р. |
||
|
|
|
|
|
|
|
|
||
11 |
Всего |
??ч |
??ч |
??ч |
??р. |
??р. |
??р. |
||
12 |
В среднем |
??ч |
??ч |
??ч |
??р. |
|
|||
13 |
Лидеры: |
1-е место |
?? |
|
|||||
14 |
|
2-е место |
?? |
|
|||||
15 |
|
3-е место |
?? |
|
<3арплата> определяется как число отработанных <Нормальных> часов, умноженных на <Стоимость нормального часа>, плюс стоимость сверхурочных часов и часов, отработанных в выходные дни. Стоимость таких часов увеличивается на 150 и 200 % относительно «нормального» часа. Кроме того, если общее число отработанных часов превышает 52, работник получает <Доплату> в 100 руб., если больше 60 ч – 200 р., если больше 66 ч – 250 р. и еще 5 % от зарплаты. Сумма!, выдаваемая <На ру-ки>, это <3арплата> + <Доплата> с учетом <Налога>. В строке «В среднем» подсчитываются соответствующие средние значения. В области С13:С15 показать фамилии работников, занявших первые три места по суммам, полученным <На руки>.
Контроль А8:А10: Фамилия ={Петров, Куликов, Васин, Рыбин}. Форматирование Е8:Е10: если Отработано всего > 50 ч. График: Фамилия – выдать На руки.
Вариант 4
Вычислить <Цену авиабилета> в зависимости от полной протяженности маршрута до всех пунктов посадок (если есть):
|
А |
В |
С |
D |
Е |
F |
G |
1 |
Питание пассажиров |
|
|
Стоимость 1 км полета пассажира |
|||
2 |
Расстояние |
стоимость |
|
|
|
до 1тыс. км |
0,5р. |
3 |
1 000км |
50р |
|
|
|
до Зтыс. км |
10% |
4 |
|
|
|
свыше Зтыс. км |
15% |
||
5 |
СТОИМОСТЬ АВИАПЕРЕВОЗОК |
||||||
6 |
Номер рейса |
Расстояние до пунктов пересадки |
Длина маршрута |
Стоимость питания |
Цена билета |
||
7
|
1-й |
2-й |
3-й
|
||||
8 |
XX |
ХХкм |
ХХкм |
ХХкм |
??км |
??р. |
??Р. |
… |
|
|
|
|
|
|
|
11 |
Всего |
??км |
??км |
??км |
??км |
??р. |
??Р. |
12 |
Средняя длина маршрута: |
??км |
|
|
|||
13 |
Число рейсов: |
? |
|
|
|||
14 |
беспосадочных |
?? |
|
|
|
||
15 |
с одной посадкой |
?? |
|
|
|
||
16 |
с двумя посадками |
?? |
|
|
|
Цена билета состоит из трех слагаемых:
-
Стоимости собственно перевозки пассажира, определяемой умножением длины маршрута на <Стоимость 1 км> полета. Последняя не постоянна. Если длина перелета менее 1 000 км, она равна 0,5 р., если от 1 000 до 3 000 км – меньше на 10 %, если свыше 3 000 км – меньше на 15 %. Причем по меньшему тарифу оплачивается только та часть маршрута, которая приходится в соответствующий диапазон.
-
Стоимости питания. Пассажиров кормят каждые 1 000 км полета. <Стоимость питания> определяется общей протяженностью маршрута, деленной на 1 000 (результат округляется до целого значения) и умноженной на его цену (50 р.).
-
Стоимости доставки в аэропорт. Она составляет 100 р. и выполняется только для пассажиров, следующих на расстояние не менее 3 000 км.
В области С14:С16 подсчитать число рейсов, совершаемых с одной и двумя посадками и без промежуточных посадок.
Контроль B8:D10: 100 км < Расстояние до пункта посадки < 6 000 км. Форматирование А8:А10: если общая длина маршрута > 10 000 км. График: Номер рейса – Цена билета.
Вариант 5
Вычислить <Стоимость всего> товара, хранящегося на складе магазина:
|
А |
В |
С |
D |
Е |
F |
G |
Н |
1 |
СКИДКИ |
|
|
|
|
|
||
2 |
2-й сорт |
10% |
|
|
|
|
|
|
3 |
3-й сорт |
20% |
|
|||||
4 |
|
|
ТОВАРНЫЕ |
ЗАПАСЫ |
|
|
|
|
5 |
Товар |
Число единиц |
Цена |
Стои- |
Состоя- |
|||
6 |
|
1-го сорта |
2-го сорта |
3-го сорта |
Просрочено |
1-го сорта |
мость всего |
ние запасов |
7 |
XX |
XX |
XX |
XX |
XX |
ХХр. |
?? |
|
8 |
|
|
|
|
|
|
|
|
|
Всего |
?? |
?? |
?? |
?? |
??р. |
?? |
|
11 |
|
Нехватка |
?? |
|||||
12 |
|
Нет |
?? |
Она определяется стоимостью 1-го сорта товара (<Число единиц 1-го сорта>, умноженное на <Цену 1-го сорта>), плюс стоимость 2-го сорта (<Число единиц 2-го сорта>, умноженное на <Цену 1-го сорта>, уменьшенное на <Процент скидки 2-го сорта>), плюс стоимость товара 3-го сорта, полученная аналогичным образом, плюс стоимость просроченного товара по цене 10 % от цены 1-го сорта.
Кроме того, следует определить факт затоваривания или нехватки товара. Если совокупная стоимость любого товара всех сортов составляет величину большую 100 000 р., в столбце <Состояние запасов> формируется слово «Избыток». Если стоимость менее 20 000 р., то – «Нехватка». Если равна нулю – слово «Нет». В остальных случаях не выдается никакого сообщения – пустые кавычки («»). В клетке НИ следует подсчитать число наименований, для которых наблюдается «Нехватка» товара, а в HI2 ~ его полное отсутствие («Нет»).
Контроль А7:А9: Название товара={Стул, Стол, Шкаф, Плита}. Форматирование Н7:Н9: если запасов Нет. График: Название товара -Стоимость всего.