Методические указания к зданию
ЗАДАНИЯ НА КОНТРОЛЬНУЮ РАБОТУ ПО ТЕМЕ EXCEL
Дисциплина “Информационные технологии”
Задание 1. Вычислить льготную квартплату. Некоторым категориям жильцов положены льготыпри оплате коммунальных услуг. Инвалиды платят на 25%, а участники войны - на 50% меньше. Эти лица отмечены в колонке <Льготы>буквами "и", "у" или "иу" соответственно. Кроме того, для жильцов первого этажа квартплата снижается на 10% в виду отсутствия необходимости платить за лифт, а жильцам второго - на 5% по тем же причинам. Квартплата снижается по 10% на каждого ребенка. В клетке D10 подсчитывается число квартир с льготной оплатой.
| A | B | C | D | E | F | G | H | I | |
| КВАРТПЛАТА | |||||||||
| № Квартиры | Этаж | Дети | Льготы (и, у, иу) | Полная квартплата | Льготы | ||||
| За этаж | И, У, ИУ | На детей | Квартплата с учетом льгот | ||||||
| и | 1 000р. | ? | ? | ? | ? | ||||
| у | 1 000р. | ? | ? | ? | ? | ||||
| 1 000р. | ? | ? | ? | ? |
Задание 2.Вычислить, заработанную рабочим сумму в зависимости от количества отработанных им в неделю часов и их вида. <3арплата> определяется как число отработанных <Нормальных часов> умноженных на <Стоимость нормального часа> плюс стоимость сверхурочных часов и часов, отработанных в выходные дни. Стоимость таких часов увеличивается на 150% и 200% относительно "нормального" часа. Кроме того, если общее число отработанных часов превышает 52, работник получает <Доплату> в 100 руб. если больше 60 часов 200 руб. если больше 66 - 250 руб. и еще 5% от зарплаты. Сумма, выдаваемая <На руки>, это <Зарплата> + <Доплата> с учетом <Налога> т.е. (от зарплаты и от доплаты берется налог в размере 13%).
Примечание:при решении задания, не применять дополнительные колонки расчетов.
| A | B | C | D | E | F | G | |
| Стоимость часа | Налог: | 13% | |||||
| Нормальный: | 20р. | ||||||
| Сверхурочный: | 150% | ||||||
| В выходные: | 200% | ||||||
| ЗАРПЛАТА | |||||||
| Фамилия | Отработано | Зарплата | Доплата | На руки | |||
| Норм. | Сверх. | Выход. | |||||
| Иванов | ? | ? | ? | ||||
| Петров | ? | ? | ? | ||||
| Сидоров | ? | ? | ? |
Задание 3.Определить <Новую цену> товара, продаваемого в комиссионном магазине. О каждом <Товаре> известна <Дата сдачи> его на комиссию и исходная, установленная в этот момент на него цена. По условиям магазина:
после первых 14-ти дней товар подвергается уценке на 5%,
после 29-ти - на 10% и далее каждый день на один процент.
Цена товара со всеми уценками отображается в колонке <Цена с уценкой>. <Новая цена> равна <Цене с уценкой> до тех пор, пока последняя не становится менее четверти исходной цены (по условиям договора товар не может быть уценен более чем на 75%). В клетке D11 подсчитывается число предметов, которые не удалось продать более чем за 30 дней, а в D12- более чем за 50 дней. Графу <Дней хранения > настроить на общий формат. Графу <Дата сдачи> настроить на формат даты. Графы <Исходная цена> и <Новая цена> настроить на денежный формат. Построить график зависимости товара от новой цены.
| A | B | C | D | E | F | |
| Уценка товара: | Сегодня: | 30.11.2012 | ||||
| Дни | % | |||||
| 5% | ||||||
| 10% | ||||||
| ЦЕНА ТОВАРА | ||||||
| Товар | Дата сдачи | Исходная цена | Дней хранения | Цена с уценкой | Новая цена | |
| сапоги | 26.10.2012 | 100р. | ? | ? | ? | |
| пальто | 11.11.2012 | 100р. | ? | ? | ? | |
| коляска | 06.10.2012 | 100р. | ? | ? | ? |
Задание 4
Построить таблицу расчетов с постоянными клиентами-покупателями, которым товар отпускается в кредит. О каждой покупке известны: название фирмы-покупателя, дата приобретения, стоимость приобретенного товара (в тыс. руб.) и плановая дата возврата кредита за товар. В столбце <Долги> вычисляется величина кредита, которая зависит от его длительности, т.е. (<Плановая дата платежа> - <Дата покупки>).
При сроке свыше 5-и дней - это 2%от исходной стоимости, свыше 10-и - 4%, свыше 15-и - 5% и еще по 1% за каждый день поле 15-го (таблицы кредитных ставок находятся в области В2:ЕЗ).
Если оплата произведена, она фиксируется значком "+". При задержке в оплате свыше 20-го дня (<Сегодня> - <Плановая дата платежа> в столбце G (значок телефона берется из программы Microsoft Word Вставка à Символ шрифт Wingdings) должно появляться слово "Звонить". В области K6:K8 подсчитываются общие неоплаченные долги клиентов. Здесь каждый клиент представлен только одной строкой. Графы <Дата покупки> и <Плановая дата платежа> настроить на вывод формата даты.
| A | B | C | D | E | F | G | H | I | J | K | |
| Условия кредита | |||||||||||
| Срок кредита (дни): | Сегодня | 30 янв | |||||||||
| Процент: | 0% | 2% | 4% | 5% | |||||||
| ПРОДАЖИ | Сводка долгов | ||||||||||
| Клиент | Дата покупки | Стоимость товара | Плановая дата платежа | Длительность | Долги | Факт оплаты | ( | Клиент | Общие долги | ||
| Факел | 1 янв | 21 янв | ? | ? | ? | ? | Факел | ? | |||
| Спорт | 1 янв | 12 янв | ? | ? | ? | ? | Спорт | ? | |||
| Дом | 2 янв | 8 янв | ? | ? | ? | ? | Дом | ? | |||
| Факел | 1 янв | 5 янв | ? | ? | ? | ? | |||||
| Спорт | 8 янв | 18 янв | ? | ? | ? | ? | |||||
| Дом | 1 янв | 6 янв | ? | ? | ? | ? |
Задание 5. Вычислить <Цену авиабилета> в зависимости oт полной протяженности маршрута до всех пунктов посадок (если есть).
Цена билета состоит из трех слагаемых:
1. Стоимости собственно перевозки пассажира, определяемой умножением длины маршрута на <Стоимость 1 км.> полета. Последняя не постоянна. Если длина перелета менее 1000 км., она равна 0,5руб., если от 1000 до 3000 - меньше на 10%, если свыше 3000 -меньше на 15%.
2. Стоимости питания. Пассажиров кормят каждые 1000 км полета. <Стоимость питания> определяется общей протяженностью маршрута, деленной на 1000 (результат округляется до целого значения) и умноженной на его цену (50 руб.).
3. Стоимости доставки в аэропорт. Она составляет 100р и выполняется только для пассажиров, следующих на расстояние не менее 3000 км.
| A | B | C | D | E | F | G | H | I | J | ||
| Питание пассажиров | Стоимость 1 км полета пассажира | ||||||||||
| расстояние, км. | до 1т км | 100р. | |||||||||
| стоимость | 50р. | до 3т км | 10% | ||||||||
| свыше 3т | 15% | ||||||||||
| СТОИМОСТЬ АВИАПЕРЕВОЗОК | |||||||||||
| № рейса | Расстояние до пунктов посадки | Длина маршрута | Стоимость питания | Цена билета | Стоимость 1км | Стоимость перевозки | Стоимость доставки | ||||
| 1-й | 2-й | 3-й | |||||||||
| ? | ? | ? | ? | ? | ? | ||||||
| ? | ? | ? | ? | ? | ? | ||||||
| ? | ? | ? | ? | ? | ? | ||||||
| ВСЕГО | ? | ? | ? | ? | ? | ? | ? | ? | ? | ||
| Средняя длинна маршрута: | ? | ||||||||||
Задание 6. Вычислить материальную помощь нуждающимся пенсионерам. <Расчетная помощь> определяется как процент от <Минимальной зарплаты> в зависимости от наличия детей (<На ребенка 80%), возраста (<Старше 70-ти лет>), инвалидности, участия в войне. Последнее отмечено в колонке <Льготы> буквами "и", "у" и "иу". Однако <Фактическая помощь> назначается таким образом, чтобы вместе с <Пенсией> она не превышала шести минимальных зарплат. Число лет человека определяется как разность между <Текущим годом> и <Годом рождения>. В области B16:D16 подсчитывается количество пенсионеров соответствующих возрастов.
| A | B | C | D | E | F | G | H | |
| Доплаты | Текущий год | |||||||
| На ребенка: | 80% | Мин. зарплата | 100р. | |||||
| Инвалид: | 100% | |||||||
| Уч. Вов: | 125% | |||||||
| Старше 70-ти: | 60% | |||||||
| ПОМОЩЬ | ||||||||
| Фамилия | Год рожд. | Детей | Льготы | Пенсия | Расчетная помощь | Фактическая помощь | Возраст | |
| ххх | У | ? | ? | ? | ||||
| ххх | у | ? | ? | ? | ||||
| ххх | иу | ? | ? | ? | ||||
| Всего | ? | ? | ||||||
| Возрастные группы | до 65 | до 70 | >70 | |||||
| ? | ? | ? |
Задание 7. Вычислить размер недельной заработной платы рабочего. Ежедневно он может находиться как в обычном, так и во вредном производстве. Часы работы по дням недели указаны в двух строках для каждого человека. По итогам недели вычисляются число дней отработанных в обычных и вредных условиях, и сумма часов. На их основе определяется оплата труда умножением <часов> на соответствующую <Часовую оплату>. Кроме того, рабочим начисляется <Доплата> за сверхурочный труд. <Доплата> за труд в обычных условиях производится при наличии сверхурочного времени. Разность между фактической длиной рабочей недели и 48 часами оплачивается по та рифу сверхурочных часов (клетка L1). Доплата за работу во вредных условиях производится аналогично, но только если отработано свыше 20-ти "вредных" часов. Кроме того, в доплату входит сумма на покупку молока (L2) за каждый день, отработанный во вредных условиях. В столбце М формируется сообщение (слово Отгул), если отработано свыше З0 часов во вредном производстве (т.е. на следующей неделе работник получи один отгул). В клетке Ml1 вычислить число всех отгулов за неделю.
| A | B | C | D | E | F | G | H | I | J | K | L | M | |
| Часовая оплата | Сверхурочн.: | 200% | |||||||||||
| Обычное произв.: | Молоко: | ||||||||||||
| Вредное произв.: | |||||||||||||
| ОПЛАТА ТРУДА НА ВРЕДНОМ ПРОИЗВОДСТВЕ | |||||||||||||
| ФИО | виды работ | Отработано (ч) | Всего | Оплата труда | Доплата | всего | отгулы | ||||||
| пн | вт | ср | чт | пт | дней | часов | |||||||
| Петр | Обычн. | ? | ? | ? | ? | ? | ? | ||||||
| Вредн. | ? | ? | ? | ? | |||||||||
| Олег | Обычн. | x | x | x | x | x | ? | ? | ? | ? | ? | ? | |
| Вредн. | x | x | x | x | x | ? | ? | ? | ? |
Задание 8. Произвести расчеты с покупателем за товар при наличной (нал) и безналичной (безнал) формах оплаты. Исходная цена товара представлена в таблице исходя их наличной оплаты. Оплата может осуществляться за наличный/безналичный расчет в любой комбинации. Покупатель вносит сумму, которую он может оплатить наличными (Е6). Остаток суммы в форме безналичной оплаты вычисляется в ячейке F6 с учетом наценки за "безнал" (G1). Кроме того, для оптовых покупателей осуществляется бесплатная доставка груза. При цене партии от 10000 руб. - в пределах Москвы, от 50000 - в Московской области, от 90000 - в центральном районе РФ. В зависимости от этого в графе <Доставка> должно выводиться одно из слов: Москва, МО, Центр. В G10 и G11 подсчитывается объем заказов (в рублях) с доставкой в Область и Центр.
| A | B | C | D | E | F | G | |
| Сумма | 10000р. | 50000р. | 90000р. | Наценка за б/нал | 10% | ||
| Доставка | Москва | МО | Центр | ||||
| ПРОДАЖА И ДОСТАВКА | |||||||
| Товар | Кол-во | Цена | Цена партии | Оплата | Доставка | ||
| Нал. | Безнал | ||||||
| ххх | x | х р. | ? р. | ? р. | ? р. | ? | |
| … | ххх | x | х р. | ? р. | ? р. | ? р. | ? |
| ВСЕГО | ? | ? | ? | ||||
| Доставка | МО | ? | |||||
| Центр | ? |
Задание 9. Вычислить сумму оплаты товара при торговле за валюту. Исходная стоимость товара (В7) представлена в долларах. При оптовой покупке она может быть уменьшена на величину оптовой скидки: (при сумме покупки от 500$ до 1000$ - на 4%. до 3000$ - на 10%. свыше - на 15%). Новая цена вычисляется в ячейке С7. Сама оплата может осуществляться за любую из трех валют (доллары, евро, рубли) в произвольной комбинации по выбору покупателя. Суммы в первых двух валютах указывает покупатель. Если они недостаточны для покупки, остаток вычисляется в рублевом эквиваленте (F7). Соотношение всех валют на день покупки содержатся в курсовой таблице (А1:ВЗ)
| A | B | C | D | E | F | |
| Курсы валют: | Сумма: | $500 | $1 000 | $3 000 | ||
| $ | € | Скидка: | 4% | 10% | 15% | |
| ХХ р. | ХХ р. | |||||
| ТОРГОВЛЯ | ||||||
| Товар | Сумма | Оплата | ||||
| покупки | со скидкой | USD | € | Руб. | ||
| ххх | $ххх | $ ? | $ xxx | ххх € | ? | |
| … | …. | |||||
| Всего | ? | ? | ? | ? |
ФУНКЦИИ ГПР, ВПР
Задание 10.Рассчитать итоговую заработную плату рабочего, в зависимости от разряда. Оклад увеличивается на соответствующий повышающий коэффициент. Для извлечения соответствующих коэффициентов и премий использовать функцию ГПР.
| A | B | C | D | E | F | G | |
| Разряд | |||||||
| Коэффициент | 1,3 | 1,6 | 1,9 | 2,2 | 2,5 | 2,8 | |
| Премия | |||||||
| ЗАРПЛАТА | |||||||
| ФИО | Оклад | Разряд | Премия | Повышающий коэффициент | Зарплата | Роспись | |
| Иванов | ? (200) | ? (1,9) | ? (1150) | ||||
| Петров | ? (350) | ? (2,8) | ? (1460) |
Методические указания к зданию
Решение:
< Премия > = ГПР (Разряд; $B$1:$G$3; 3 строка)
т.е. D6 = ГПР (C6; $B$1:$G$3; 3)
< Повышающий коэффициент > = ГПР (Разряд; $B$1:$G$3; 2 строка)
т.е. D6 = ГПР (C6; $B$1:$G$3; 2)
< Зарплата > = ( < Оклад > * < Повышающий коэффициент > ) + <Премия>
т.е. F6 = ( B6 * E6 ) + D6
Задание 11.Рассчитать цену билета полета на самолете, в зависимости от класса полета. Стоимость билета для взрослого человека задана в таблице тарифов, детский билет стоит в половину дешевле.