Работа со справочниками
Общие сведения Функция ВПР Позволяет работать со справочной информацией. Общий формат: ВПР(Искомое значение; Адрес справочной таблицы; Номер столбца в таблице, из которого берется результат поиска)
Например. Пусть магазин работает по следующей системе: – если сумма покупок менее 2000 руб., то покупателю не предоставляется никаких скидок; – если сумма покупок от 2000 руб. до 10000 руб., то покупателю предоставляется скидка, равная 5 %; – если сумма покупок больше 10000 руб., то покупателю предоставляется скидка, равная 10 %. И пусть на кассе должна накапливаться следующая информация:
Столбец C заполняется естественно кассиром вручную. А вот столбцы D и E должны рассчитываться. Для этого организуется справочная таблица следующего вида:
На основе такого расположения данных в D3 вводится формула:
=ВПР(C3;$J$4:$K$6;2)
а в E3 формула: =C3*(1–D3) которые затем копируются на нужный диапазон столбцов D и E.
Варианты заданий 1. «Разносортица» В магазин «Меха» поставляют свои изделия 4 фирмы (А, В, С и D). Поставляемые товары могут быть следующих сортов – высшего, первого, второго и третьего. При этом величины наценки по отношению к базовой цене третьего сорта составляют соответственно – 40%, 25%, и 10%. Однако проверка качества товаров показала, что товары, поставляемые фирмой С, не соответствуют заявленным сортам. Поэтому магазин, беспокоясь о своей торговой марке, понижает сортность товаров фирмы С – т.е. ее товар высшего сорта продается как первый сорт, товар первого сорта – как второго, товар второго сорта – как третьего, а третий сорт так и идет как третий. Имеются данные о поставках товаров следующего вида:
По данным этой таблицы рассчитать объем планируемой выручки.
2. «Маршрутное такси» В схеме маршрутных такси имеется 7 маршрутов следующей длины:
Водители такси получают зарплату в зависимости от выручки за минусом расходов на бензин в размере 20%. Стоимость бензина 10,3 руб/л. Расход бензина – 0,12 л/км. Стоимость проезда – 6 руб. Имеются данные по перевозкам за месяц следующего вида:
Определить среднюю зарплату водителей.
3. «Гостиница» В гостинице имеется набор различных видов номеров со следующими параметрами:
Кроме того, имеется такая дополнительная услуга как доставка заказов в номер, стоимостью 120 руб. Имеются данные по занятости номеров и количеству оказанных услуг:
Подсчитать объем выручки за выбранный период времени.
4. «Автовокзал» В распоряжении имеется набор автобусов различных марок. Их характеристики приводятся в следующей таблице.
Имеются данные по перевозкам следующего вида:
Подсчитать величину прибыли за отчетный период, если стоимость бензина равна 18 руб./л
5. «Книжное издательство» Имеются справочные таблицы расценок на издание печатной продукции. Вид переплета: твердый, глянцевый мягкий, мягкий. Стоимость мягкого переплета равна 10 руб. По отношению к мягкому переплету величина наценки соответственно равна 40% и 25%. Сорт бумаги: высший, первый и второй. Стоимость листа бумаги второго сорта равна 30 коп. Величина наценки по отношению ко второму сорту соответственно равна 20% и 10%. Тип печати: многоцветный, двуцветный и одноцветный. Стоимость одноцветной печати – 3 коп/лист. Величина наценки по отношению к одноцветному типу соответственно равна 60% и 20%. Имеются данные по выпуску следующего вида:
Рассчитать планируемый объем выручки 6. «Продукты» На складе магазина находятся товары, на которые имеется сопроводительная документация со следующей информацией: – код товара, наименование, количество, закупочная цена, дата изготовления и конечный срок реализации. Если срок реализации товара истекает, то он списывается и магазин несет убытки. Данные о продажах представлены в таблице:
На основе этих данных определить прибыль магазина за текущий период, исходя из расчета, что величина торговой наценки составляет 25%.
7. «Коттеджи» Стоимость коробки для коттеджа зависит от типа стен (дерево, бетонная панель или кирпич), вида крыши (шифер, железо или черепица), этажности, общего метража и т.д. Расценки приведены ниже. Стена: деревянная – 157 руб./кв. м; панельная – 525 руб./кв. м; кирпичная – 836 руб./кв. м. Крыша: шифер – 128 руб./кв. м: железо – 226 руб./кв. м; черепица – 390 руб./кв. м. Каждый дополнительный этаж повышает стоимость работ на 30%. Имеются данные по готовым к продаже коробкам для коттеджей.
Подсчитать общий планируемый объем выручки.
8. «Гастроли» В постоянных гастролях по стране участвуют звезды эстрады – A, B, C, D и E. Величины их гонораров за один концерт представлены в таблице.
Однако по разным причинам возможны срывы как одного концерта так и всего тура в данном городе (например, по причине недостаточного объема проданных билетов). При этом звезда несет убытки, равные 30% от гонорара за один сорванный концерт или 140% за полностью сорванный тур. Имеются данные по гастролям указанных звезд в нескольких городах.
Рассчитать средний заработок звезд за указанный период
9. «Туристическое агентство» В рекламном проспекте туристического агентства имеются сведения о различных турах следующего вида:
Стоимость проживания в отелях разного класса задана в таблице:
Имеются также сведения о реализованных путевках за месяц.
Однако туристам, побывавшим в Таиланде, через суд удалось доказать, что предоставленные им отели не соответствуют заявленному классу. Этот класс во всех случаях должен быть снижен как минимум на один уровень. Определить сумму, которую должно вернуть туристическое агентство своим клиентам, побывавшим в Таиланде.
10. «Комплектующие» Имеется прайс–лист цен на комплектующие для компьютеров и данные по их продаже за текущий месяц.
Для каждого дня известен также курс рубля по отношению к доллару.
Определить объем выручки в рублях.
11. «Авиаперевозки» Компании, занимающиеся воздушными сообщениями, предоставляют своим клиентам разнообразную систему скидок (в процентах по отношению к базовой цене). Часть их приводится в таблице.
Имеются данные о продажах билетов следующего вида:
Подсчитать общий объем выручки. 12. «Винный погребок» Магазин «Винный погребок» торгует винами российского, молдавского, грузинского и французского производства. Заданы цены основных марок российских вин: шампанское – 70 руб; коньяк (3 звездочки)– 98 руб; вино столовое – 65 руб; вино марочное – 82 руб. При этом, практически автоматически, в зависимости от страны производителя, на одинаковые марки вин устанавливаются следующие наценки (по отношению к российским винам): на французские ––700%; на молдавские – 10%; на грузинские – 5%. Данные о продажах представлены в таблице.
Определить объем выручки за указанный период
13. «Сберкасса» В сберкассе имеются сведения о вкладах частных лиц следующего вида.
Известны текущие величины годовых процентных ставок по вкладам различных видов:
Известно также, что все эти ставки до 01.01.01 года были в 1.5 раза выше. Рассчитать общую наращенную сумму вкладов на 01.01.04, если наращивание вкладов происходит по формуле простых процентов. 14. «Мебельная фабрика» Мебельная фабрика производит диваны, различающиеся видом обивки, материалом каркаса и наличием встроенного выдвижного ящика. Цены различных вариантов исполнения приведены в таблице.
Наличие выдвижного ящика увеличивает стоимость дивана на 530 руб. Имеются данные о выпуске диванов следующего вида:
Рассчитать объем планируемой выручки. 16. «Сага о таре» Ликеро–водочный завод поставляет свою продукцию в торговую сеть в возвратной таре двух видов – металлической и пластмассовой. По условиям договора металлическая тара должна быть возвращена не позднее 6 дней со дня отгрузки, а пластмассовая – не позднее 10 дней. В противном случае магазин должен выплачивать штраф за каждый день просрочки. Размер штрафа за металлическую тару равен 2% от стоимости тары и 1% для пластмассовой тары. Стоимость тары: – металлический ящик – 120 руб.; – пластмассовый ящик – 67 руб. Имеются данные об обороте тары следующего вида:
Рассчитать общую сумму штрафов.
|
