Создание таблицы «Расчет зарплаты»

Постановка задачи. Технология создания таблицы «Расчёт зарплаты».

Расчет зарплаты с учетом квалификации работника (должностного класса) и коэффициента (КТУ) трудового участия. Положим, заказчик оценил всю работу в 10000 руб. и выдал аванс в размере 4000 руб., который и был распределен между людьми произ­вольным образом (кто сколько попросил). Наша задача состоит в том, чтобы по завершении работы распределить остальную часть зарабо­танного (6000 руб).

Заполняем текстовую информацию, диапазоны С2:Е3, В2:В8, как на рисунке 5.12. Коэффициент за класс определяем по формуле С6=ГПР(В6;С2:Е3;2;0), смотрите рисунок 5.13. Значения КТУ (диапазон D6:D8) определяются руководителем коллектива. Затем определяем влияние класса (коэффициента за класс) и КТУ на размеры выплат. Найдем сумму этих величин. Поскольку коэффициент доплат за класс работника имеет тот же смысл, что и КТУ, то эта сум­53а образуется сложением пар сомножителей, образованных из обеих величин. В нашем случае это D9=СУММПРОИЗВ(С6:С8;D6:D8).

Функция ГПР выполняется аналогично функции ВПР, только она определяет искомые значения в строке. Третий аргумент функции задаёт номер строки. Пример приведён в таблице расчётных формул (рисунок 5.13).

Рисунок 5.12. Таблица расчёта зарплаты

Теперь можно определить причитающуюся рабочему сумму <заработано>=<всего>/<итого КТУ>*<коэфф. За класс>*<КТУ>, для первого рабочего это F6=$B$1/D$9*C6*D6.

Рисунок 5.13. Таблица расчётных формул для зарплаты

Поскольку работник уже получил аванс, ему предстоит выдать (а может быть и взыскать с него, если аванс не был «отработан») сумму

<выдать остаток>=<заработано>–<получен аванс> или G6=F6-E6.

На рисунке 5.12 приведены результаты расчёта зарплаты, на рисунке 5.13 формулы для расчётов, а на рисунке .

Правильность наших расчетов подтверждает совпадение значений F9=B1 и G9=F1.

Рисунок 5.14. Объёмная гистограмма распределения зарплаты

На рисунках 5.14 и 5.15 приведены: объёмная гистограмма распределения зарплаты (аванс, заработано, выдать остаток) и круговая диаграмма – сколько заработано.

Рисунок 5.15. Круговая диаграмма распределения зарплаты

Замечание 3!!! Не забудьте сохранить созданные задания в рабочей книге.

Контрольные Вопросы

1. Чётко объясните постановку задачи, что является исходными данными и результатом.

2. По каким формулам проводились вычисления?

3. Какими встроенными функциями пользовались при вычислениях?

4. Объясните назначение этих функций и синтаксис.

5. Как проводили условное форматирование заданных ячеек?

6. Как организовывали встроенный контроль ввода?

7. Как организовывали форматирование результирующей таблицы?

Варианты заданий

Задача 1. Вычислить современную (на текущий год) стоимость основных фондов предприятия с учетом их износа и инфляции. О каждом объекте известны год и цена приобретения. Стоимость каждого объекта уменьшается за счет износа на 10% в год за период от <Года приобретения> до текущего <Года> и увеличивается на величину <Инфляции> (положим, средняя инфляция составляет 30% в год). Сначала нужно вычислить вспомогательное значения:

• Число лет инфляции в период эксплуатации объекта, с учетом того, что она возникла только с 1992 года.

• Коэффициент инфляции – во сколько раз возросла стоимость объекта за счет инфляции. Его значение всегда >1 (1 – инфляция еще не повлияла )

- Коэффициент износа – какая часть объекта еще не изношена. Его значение находится в диапазоне от 1 до 0 (0 – полный износ).

Имея эти данные, можно вычислить современную стоимость, как произведение исходной цены, коэффициента инфляции и коэффициен­та износа. При полном износе современная стоимость равна нулю. В этом случае в столбце G следует вывести не 0 руб., а слово «Списать». В ячейке С9 подсчитывается число списанных объектов. В области С1О:С13 подсчитать современную суммарную стоимость фондов по “возрастным» категориям.

Контроль В5:В7: Год приобретения > 1950г.

Форматирование G5.G7: слово «Списать» на красном фоне.

График: Название – Современная стоимость.

Задача 2. Вычислить стоимость автоперевозок заданного веса груза на заданное расстояние разными типами автомобилей. Путевая скорость всех типов а/м считается равной 50 км/час. Здесь <Число ездок> это <Вес груза>/<Грузоподъемность>, округленное до большего целого; <Пробег> это <Число ездок>*<Расстояние>*2 (удваивается, поскольку автомобиль каждый раз должен возвращаться в исходный пункт). <Стоимость перевозок> состоит из зарплаты и стоимости арен­ды. <3арплата> водителя определяется <Временем в пути>. Кроме то­го, если автомобиль находится в пути в оба конца больше 12 часов, во­дителю производится доплата (командировочные) в размере 50 руб. за каждые 12 часов в пути на каждом маршруте. В области D3:D5 подсчи­тывается число машиночасов (время в пути), необходимых для обслуживания заявок на перевозки разными типами а/м. Для извлечения данных их таблицы тарифов использовать функцию ВПР().

Контроль А8:А10: Тип а/м ={3ил, Газ, Камаз}.

Форматирование А8:А10: если Пробег>1000км.

График: Тип а/м – Объем заказов.

Задача 3. Вычислить заработанную рабочим сумму в зависимо­сти от количества отработанных им в неделю часов и их вида.

Контроль А8:А10: Фамилия ={Петров, Куликов, Васин, Рыбин}.

Форматирование Е8:Е10: если Отработано всего > 50час.

График: Фамилия – выдать На руки.

<3арплата> определяется как число отработанных <Нормальных> часов, умноженных на <Стоимость нормального часа> плюс стоимость сверхурочных часов и часов, отработанных в выходные дни. Стои­мость таких часов увеличивается на 150% и 200% относительно нормального» часа. Кроме того, если общее число отработанных ча­сов превышает 52, работник получает <Доплату> в 100 руб., если больше 60 часов – 200 руб., если больше 66 – 250 руб. и еще 5% от зар­платы. Сумма, выдаваемая <На руки>, это <Зарплата>+<Доплата> с учетом <Налога>. В строке «В среднем» подсчитываются соответст­вующие средние значения. В области С13:С15 показать фамилии ра­ботников, занявших первые три места по суммам, полученным <На руки>.

Задача 4. Вычислить <Цену авиабилета> в зависимости от пол­ной протяженности маршрута до всех пунктов посадок если есть).

Контроль B8:D10: 100км < Расстояние до пункта посадки < 6000км.

Форматирование А8:А10: если общая длина маршрута > 10000км.

График: Номер рейса – Цена билета.

Цена билета состоит из трех слагаемых:

5. Стоимости собственно перевозки пассажира, определяемой умно­59а5959дел длины маршрута на <Стоимость 1 км> полета. Последняя не постоянна. Если длина перелета менее 1000 км., она равна 0,5 руб., если от 1000 до 3000 – меньше на 10%, если свыше 3000 –меньше на 15%. Причем по меньшему тарифу оплачивается только та часть маршрута, которая приходится в соответствующий диапазон.

2. Стоимости питания. Пассажиров кормят каждые 1000 км полета.<Стоимость питания> определяется общей протяженностью маршрута, деленной на 1000 (результат округляется до целого значения) и умноженной на его цену (50 руб.).

3. Стоимости доставки в аэропорт. Она составляет 100 р и выполняется только для пассажиров, следующих на расстояние не менее 3000 км

В области С14:С16 подсчитать число рейсов, совершаемых с одной и двумя посадками и без промежуточных посадок.

Задача 5. Вычислить <Стоимость всего> товара, хранящегося на складе магазина. Она определяется стоимостью первого сорта товара. (<Число единиц 1 сорта>, умноженной на <Цену 1 сорта>) плюс стои­мость 2 сорта (<Число единиц 2 сорта>, умноженная на <Цену 1 сорта>, уменьшенную на <Процент скидки 2 сорта>), плюс стоимость товара 3 сорта, полученную аналогичным образом, плюс стоимость просроченного товара по цене 10% от цены 1 сорта.

Контроль А7:А9: Название товара={Стул, Стол, Шкаф, Плита}.

Форматирование Н7:Н9: если запасов Нет.

График: Название товара – Стоимость всего.

Кроме того, следует определить факт затоваривания или нехватки товара. Если совокупная стоимость любого товара всех сортов составляет величину большую 100000 руб., в столбце <Состояние запасов> формируется слово «Избыток». Если стоимость менее 20000 руб. –«Нехватка». Если равна нулю – слово «Нет». В остальных случаях не выдается никакого сообщения – пустые кавычки («»). В ячейке Н11 следует подсчитать число наименований, для которых наблюдается “Нехватка» товара, а в HI2 – его полное отсутствие («Нет»).

Задача 6. Вычислить величину квартплаты. Она определяется количеством квадратных метров <Площади>, умноженных на <Цену 1 квадратного метра>. Кроме того, если в квартире имеется излишек площади относительно санитарной нормы, он оплачивается в двойном размере. Излишек определяется как <Площадь> квартиры минус число проживающих в ней <Человек>, умноженное на <Санитарную норму>. Если в квартире проживает один человек, ему положена удвоенная са­нитарная норма. Некоторым категориям жильцов положены льготы при оплате коммунальных услуг. Инвалиды платят на 25%, а участни­61а войны – на 50% меньше. Эти лица отмечены в колонке <Льготы> буквами «и», «у» или «иу» соответственно. Кроме того, для жильцов пер­вого этажа квартплата снижается на 20% в виду отсутствия необходи­мости платить за лифт, а жильцам второго – на 10% по тем же причи­нам. В ячейке Е10 подсчитывается число квартир с льготной оплатой.

Контроль Е6:Е8: Льготы={и, у}.

Форматирование D6.D8: если этаж 1 или 2.

График: Номер квартиры – Квартплата фактическая.

Задача 7. Определить <Новую цену> товара, продаваемого в комиссионном магазине. О каждом <Товаре> известна <Дата сдачи> его на комиссию и исходная, установленная в этот момент на него, це­на. По условиям магазина после первых 15-ти дней товар подвергается уценке на 5%, после 30-ти – еще на 10% и далее каждый день на один процент. Цена товара со всеми уценками отображается в колонке <Цена с уценкой>. <Новая цена> равна <Цене с уценкой> до тех пор, пока последняя не становится менее четверти исходной цены (по условиям договора товар не может быть уценен более, чем на 75%). В ячейке подсчитывается число предметов, которые не удалось продать более чем за 30дней, а в D13 – более чем за 50 дней.

Контроль С7:С9: Исходная цена >=100руб.

Форматирование D7.D9: если дней хранения >50.

График: Товар – Дней хранения.

Задача 8. Вычислить зарплату рабочего (колонка <3аработано>).

Она определяется числом <Изготовленных им деталей>, умноженным на <Стоимость одной детали>. Заработок также зависит от <Разряда> рабочего. Он увеличивается на соответствующий <Разрядный коэффициент>. Кроме того, если рабочий произвел более 30 деталей, ему начисляется премия в размере 50% от стоимости каждой детали, начиная с 31-й. Зарплата рабочего может быть и уменьшена в случае, если им было изготовлено свыше трех бракованных деталей – из заработанных сумм вычитается штраф в размере 50 руб. В колонке <Брак> выводится восклицательный знак, если бракованных деталей до пяти, вырабатывается сообщение «Брак», если больше пяти, и «Аврал», если больше семи. В ячейке F13 подсчитывается число рабочих, допустивших брак в количестве от пяти деталей. Нужные разрядные коэффици­енты извлекаются из таблицы функцией ВПР(). В области F2:F5 подсчитать число рабочих, имеющих соответствующий разряд.

Контроль А9:А11: Фамилия ={Иванов, Петров, Лукин, Васин}.

Форматирование F9.F11: слово «Аврал» на красном фоне.

График: Фамилия – Заработано.

Задача 9. Вычислить сумму налога и сумму «на руки» для 63а­ботников производства. <Налог %> составляет 13% от заработка, од­нако не от всего. <Сумма обложения> меньше <3арплаты> на одну <Минимальную зарплату> и еще на одну <Минимальную зарплату> за каждого ребенка в семье.

Контроль D7:D9: Льготы={и, у, иу}.

Форматирование G7:G9: если Выдать на руки >5000руб.

График: Фамилия – Выдать на руки.

Кроме того, инвалиды и участники войны имеют льготы при нало­гообложении в 10% и 20% соответственно относительно «обычного» налога. Эти лица отмечаются в колонке <Льготы> буквами «и», «у», «иу» (человек может быть одновременно инвалидом и участником). В ячейке D10 подсчитывается общее число лиц, имеющих льготы по оплате на­логов, в D11 – число инвалидов, в D12 – число участников (в D11, D12 учитываются и люди, имеющие обе льготы).

Задача 10. Вычислить ежедневный и недельный заработок 64а­бочего. Ежедневный заработок (колонки <3аработано>) определяется числом <Отработанных часов>, умноженных на стоимость рабочего часа, которая не постоянна. Она увеличивается на 50% за сверхуроч­ные часы (время, отработанное свыше 8 часов). Субботние часы опла­чиваются по тарифу, увеличенному на 90%. Заметим, что и в субботу возможен сверхурочный труд. Кроме того, если рабочий отработал в течение недели больше 55 часов, он получает прибавку в сумме <Минимальной зарплаты>, а если больше 65 – две минимальные зарплаты. В ячейке H12 выводится фамилия человека, отработавшего макси­мальное число часов, а в О12 – фамилия получившего максимальную зарплату на этой неделе.

Контроль B8:G10: Отработано часов в день<=12часов.

Форматирование О8:О10: если Всего >3000руб.

График: Работник – Всего часов.

Задача 11. Вычислить размер стипендии в зависимости от сред­него балла, полученного в сессию, и наличия детей. Средний балл счи­тается равным нулю, если у студента есть задолженности – двойка по одному предмету или «незачет» по одному зачету. Зачет обозначается буквой «з» в колонке зачетов, незачет – буквой «н». Отсутствие на ко­нец сессии экзаменационных оценок и отметок о зачетах/незачетах хо­тя бы по одному предмету означает, что сессия данного студента про­длена. Этот студент получает только доплаты на детей, а в столбце “Продлено» ставится отметка «+». Считаем, что всем студентам, не имеющим задолженностей, назначается стипендия. Пусть «обычная» стипендия равна минимальной зарплате. Полагаем также, что отлич­65а6565 получают стипендию на 40% выше номинальной, а студенты, не имеющие троек, - на 10%. Кроме того, студенты с детьми получают по одной минимальной зарплате на каждого ребенка. В последней строке Всего подсчитывается число человек, сдавших отдельные предметы (диапазон В8:Е8), в F8 – средний балл для всей студенческой группы, в I8 – число студентов, которым была продлена сессия. В В9 подсчитать число отличников, в В10 – число студентов, сдавших сессию без троек.

Контроль B5:D7: 2<Экзамен<=5.

Форматирование А5:А7: если средний балл=5.

График: Студент – Стипендия.

Задача 12. Вычислить материальную помощь нуждающимся пенсионерам. <Расчетная помощь> определяется как процент от <Минимальной зарплаты> в зависимости от наличия детей (<На ребенка> -80%), возраста (<Старше 70-ти лет>), инвалидности, участия в войне. Последнее отмечено в колонке <Льготы> буквами «и», «у» и «иу». Од­нако <Фактическая помощь> назначается таким образом, чтобы в сум­ме с <Пенсией> она не превышала шести минимальных зарплат. Число лет человека определяется как разность между <Текущим годом> и <Годом рождения>. В области B13:D13 подсчитывается количество пенсионеров соответствующих возрастов.

Контроль А8:А10: Фамилия={Кулик, Петров, Лукин, Васин}.

Форматирование А8:А10: если человек старше 70-и лет.

График: Фамилия – Фактическая помощь.

Задача 13. Вычислить суммы вкладов клиентов банка на конец года. Все вкладчики банка получают <Премию> в зависимости от сум­мы и длительности вклада. Для этого сначала вычисляется <Средняя сумма> как сумма остатков вклада за все кварталы, деленная на четыре.

Контроль А10:А12: Номер счета = 5 символов.

Форматирование А10:А12: если средняя сумма >100 000руб.

График: Номер счета – Всего.

Далее компенсируется годовая <Инфляция> (сумма вклада увели­чивается на 12%). Затем, в зависимости от величины вклада, 68а6868деляя­ется собственно премия. Если вклад (<Средняя сумма>) до 5 тыс. руб. – премия составляет 18%, если от 5 до 20 тыс. – 25%, если от 20 до 30 тыс. – 30%, если свыше 30 тыс. рублей – 35% от <Средней суммы>. В ячейках НЗ-Н6 следует подсчитать число вкладов (средних сумм), на­ходящихся в заданных пределах (до 5т., до 20т. И т.д.).

Задача 14. Вычислить налог на недвижимость. Сначала опреде­ляется <Общая стоимость> объекта, облагаемая налогом. Она вычис­ляется как <Стоимость кв. метра земли>, умноженная на <Площадь>, плюс <Стоимость дома> и плюс по одному проценту от <Стоимости участка> за водопровод и электроэнергию (обозначаются знаком «+» в колонках <Вода> и <Свет>). <Налог> является суммой налога на зем­лю и налога на строение. Положим также, что налог на землю удваива­ется на каждый метр земли свыше 100 кв. метров и утраивается на 68а­ждый метр свыше 400. В ячейках D10 и Е10 – подсчитать число участ­ков, имеющих соответствующие коммуникации. В области С11:С13 подсчитать число участков соответствующей площади.

Контроль В7:В9: 50м<=площадь<=500м.

Форматирование А7:А9: если площадь >400 м.

График: Владелец – Общая стоимость.

Задача 15. Вычислить размер заработка продавцов фирмы. Зарплата работника состоит из двух частей – фиксированного небольшого <Оклада>, определяемого <Разрядом>, и <Премии>, зависящей от фак­тического объема продаж (<Продано>). Если объем продаж менше <Нормы>, она составляет 10% от <Продаж>, если больше – 20%, если больше в два раза, добавляется еще 1000 руб. В ячейке С8 вычислить количество человек, продавших товаров более чем на 50000 руб. Для определения оклада следует воспользоваться функцией ВПР().В облас­ти С8:С10 показать фамилии продавцов, занявших по объему продаж первые три места и суммы их продаж.

Контроль В4:В6: Разряд={1,2,3,4,5}.

Форматирование А4:А6: если Продано > 100 000руб.

График: Фамилия – Заработок.

Задача 16. Вычислить размер недельной заработной платы 69а­бочего. Ежедневно он может находиться как в обычном, так и во вред­ном производстве. Часы работы по дням недели указаны в двух стро­ках для каждого человека. По итогам недели вычисляются число дней, отработанных в обычных и вредных условиях, и сумма часов. На их основе определяется оплата труда умножением <часов> на соответст­вующую <Часовую оплату>. Кроме того, рабочим начисляется <Доплата> за сверхурочный труд. <Доплата> за труд в обычных условиях производится при наличии сверхурочного времени. Разность между фактической длиной рабочей недели и 48 часами оплачивается по та­рифу сверхурочных часов (ячейка L1). Доплата за работу во вредных условиях производится аналогично, но только если отработано свыше 20-ти «вредных» часов. Кроме того, в доплату входит сумма на покупку молока (L2) за каждый день, отработанный во вредных условиях. В столбце М формируется сообщение (слово Отгул), если отработано свыше 30 часов во вредном производстве (на следующей неделе работник получит один отгул). В клетке Ml 1 вычислить число всех отгулов за неделю.

Контроль C7:G10: Отработано в день <=14час.

Форматирование М7:М10: слово «Отгул» на красном фоне.

График: Фамилия – Всего.

Задача 17. Вычислить стоимость ремонта квартиры. Она состо­ит из <Стоимости ремонта потолка> (получается умножением <Площади> квартиры на <Стоимость окраски 1 кв.м потолка>) плюс <Стоимость ремонта стен> (определяется умножением <Площади стен> на <Стоимость оклейки/окраски/обивки 1 кв. м. стен>).

Контроль D9:D11: Покрытие стен={71а, кр, об}.

Форматирование Н9:Н11: если Всего>50 000руб.

График: Номер квартиры – Всего.

Вид отделки стен указывается буквами «71а», «кр» или «об» в колонке <Покрытие стен>. Допускается отсутствие какой-либо отделки стен. Стоимость срочного ремонта увеличивается на 40%. Этот факт показывается (если есть) в колонке <Срочность> знаком «+». Если стоимость ремонта превышает 50 тыс. руб., клиенту дается скидка в 10%. В ячейке G12 подсчитать число срочных заказов. В области F2:F5 подсчит общие объемы работ (в метрах) по видам.

Задача 18. Вычислить стоимость заказов в фирме, торгующей однородным жидким товаром. Товар отпускается бочками по 150 кг и канистрами по 40 кг. Известна цена продукта и цена тары. В таблице сначала следует определить, сколько полных бочек уйдет под товар. Остаток поставляется в канистрах. Поскольку и бочки и канистры заполнены целиком, может оказаться, что <Вес отгрузки> несколько больше заказанного. <Полная стоимость> будет состоять из товара размещенного в бочках и канистрах с учетом стоимости тары. Кроме того, следует учитывать скидки оптовым покупателям. Если полная стоимость превышает установленный <Порог скидки>, разность между полной стоимостью и порогом оплачивается по цене на 10% меныше обычной. Сказанное относится только к самому товару (стоимость та­ры не снижается). Для определения числа канистр следует воспользо­ваться функцией ОКРВВЕРХ(). В ячейке Е11 подсчитать число зака­занных партий товара весом более 1000 кг.

Контроль В7:В9: Вес заказа > 1000 кг.

Форматирование F7:F9: если возможна скидка.

График: Заказчик – Стоимость со скидкой.

Задача 19. Определить стоимость обслуживания туристических экскурсий на маршрутах А, Б и т.д. О каждом маршруте известна стоимость собственно экскурсии и стоимость транспортных расходов. Известна также емкость автобуса. В самой таблице фиксируется же­лаемый маршрут и число заявок (человек) на обслуживание. Мини­мальное число автобусов определяется как целая часть от <Число заявок>/<Вместимость автобуса>. Фирма обслуживает не всех туристов, а только такое их максимальное количество, чтобы не оказалось ни од­ного автобуса, заполненного менее чем на 30%. Фактическое число оп­ределяется в колонке <Выделено автобусов>. Для этого нужно выяс­нить, сколько туристов еще не размещено в автобусы. Если их оказа­лось больше чем 30% емкости автобуса, значит <выделено автобусов> будет на единицу больше минимально необходимого их числа. В про­тивном случае, будет <выделен> этот минимум. <Стоимость> обслу­живания определяется произведением числа выделенных автобусов на сумму экскурсионного и транспортного обслуживания маршрута. В ко­лонке <Примечание> следует показать число пустых мест в автобусе или число отклоненных заявок (что есть). В ячейке Е13 показать число об­ращений для обслуживания более 1000 заявок. Для выявления стоимости маршрута из таблицы тарифов следует воспользоваться функцией ГПР()

Контроль А9:А11: Номер маршрута={А, Б, В, Г}.

Форматирование F9:F11: если есть отказные заявки.

График: Номер маршрута – Автобусов выделено.

Задача 20. Рассчитать размеры месячного <заработка> работни­ков, состоящего из <зарплаты> и <премии>, которые нужно разделить между сотрудниками. Зарплата определяется фиксированным окладом, выплачиваемым пропорционально числу отработанных дней. Премии распределяются пропорционально двумя параметрам: а) коэффициенту трудового участия (КТУ), назначаемому на общем собрании коллекти­ва, б) должностному окладу (чем больше оклад, тем больше премия). Лица, отработавшие менее недели (<5 дней), премию не получают. Для облегчения расчетов по распределению премии здесь введен столбец <коэффициент премирования> где вычисляется коэффициент, учиты­вающий как КТУ, так и должностной оклад работника. Собственно <премия> тогда определяется умножением этого коэффициента на размер <Суммы премии>. В области D8:D10 формируется сводка о числе человек, отработавших определенное число дней в месяце.

Контроль С4:С6: Рабочих дней <= Рабочих дней в месяце.

Форматирование А4:А6: если Премия=0.

График: Ф.И.О. – Заработок.

Задача 21. Определить размер квартплаты в кооперативном до­ме, которая состоит из расходов на оплату лифта (В1) и коммунальных расходов (G1). Расходы на коммунальное обслуживание делятся между всеми пропорционально площади квартир. Аналогично делится стои­мость обслуживания лифта, за исключением первого и второго этажей: жильцы первого этажа за лифт не платят совсем, жители второго – 50% от полной стоимости. Остальные платят 100%, приходящейся на них суммы. Для того чтобы определить «вес» в рублях одного процента об­служивания лифта и одного метра площади, нужно найти суммы всех процентов (D7) и общую площадь (Е7) всех квартир, а потом поделить на них средства, которые следует выплатить мэрии за данный вид обслуживания. В доме имеются не только жилые помещения, но и офисы (обозначены знаком «+»). Их арендаторы, кроме квартплаты вносят еще сумму в доход кооператива, равную 200 руб. за кв. метр площади. Если арендуемая площадь более 100 кв.м. – то по 180 руб., если более 300 кв.м. – то по 160 руб. за метр. Весь доход идет на ремонт дома. В ячей­ке В7 подсчитать число офисов, размещенных в доме.

Контроль С4:С6: 1<=Этаж<=5.

Форматирование А4:А6: если это Офис.

График: Номер квартиры – Квартплата.

Задача 22. Вычислить сумму оплаты товара при торговле за валю­ту. Исходная стоимость товара (В7) представлена в долларах. При оп­товой покупке она может быть уменьшена на величину оптовой скидки (при сумме покупки от 500$ до 1000$ - на 4%, до 3000$ - на 10%, свыше – на 15%). Новая цена вычисляется в С7. Сама оплата может осуществляться за любую из трех валют (доллары, немецкие марки, рубли) в произвольной комбинации по выбору покупателя. Суммы в пер­вых двух валютах указывает покупатель. Если они недостаточны для по­купки, остаток вычисляется в рублевом эквиваленте (F7). Соотношения всех валют на день покупки содержатся в курсовой таблице (А1 :ВЗ).

Контроль А7:А9: Товар={магнитола, приемник, ТВ, компьютер}.

Форматирование А7:А9: если Сумма покупки > 10000$.

График: Товар – Сумма покупки.

Задача 23. Произвести расчеты с покупателем за товар при наличной (нал) и безналичной (безнал) формах оплаты. Исходная цена товара представлена в таблице исходя их наличной оплаты. Оплата может осуществляться за наличный/безналичный расчет в любой комбинации. Покупатель вносит сумму, которую он может оплатить на­личными (Е6). Остаток суммы в форме безналичной оплаты вычисляется в ячейке F6 с учетом наценки за «безнал» (G1). Кроме того, для опто­вых покупателей осуществляется бесплатная доставка груза. При цене партии от 10000 руб. – в пределах Москвы, от 50000 – в Московской области, от 90000 – в центральном районе РФ. В зависимости от этого в G должно выводиться одно из слов: Москва, МО, Центр. В G10 и G11 подсчитывается объем заказов (в рублях) с доставкой в Область и Центр.

Контроль Е6:Е8: Оплата наличными < Цена партии.

Форматирование А6:А8: если Цена партии > 90 000руб.

График: Товар – Цена партии.

Задача 24. Произвести расчеты с клиентом за купленный товар с учетом времени оплаты, которая может осуществляться частью в форме предоплаты (в размере не менее 60% от всей стоимости товара и не менее 1000 руб.), частью в более поздние сроки. Если покупатель не хочет заплатить за весь товар сразу, он может внести остаток суммы позже, но с наценкой, определяемой таблицей А1:Е2. Наценка на оста­ток составляет 1% при оплате в срок от 1 до 3 дней, 2% - на срок до 7 дней и т.д. Покупатель указывает сумму, которую он может внести сразу (не менее, чем указано в G2) и число дней (не более 15), через кото­рое будет оплачен остаток. Остаток суммы с учетом наценок за кредит определяется в ячейке F6. В G6 находится полная сумма, вносимая поку­пателем за товар. В G10 вычисляется число крупных заказов, превышаю­щих 50000 руб. Наценку на безналичную оплату найти функцией ГПР().

Контроль А6:А8: Товар={Мука, Масло, Сахар, Хлеб}.

Форматирование G6:G8: если Всего > 50 000руб.

График: Товар – Всего.

Задача 25. Определить суточный заработок рабочих в зависи­мости от числа часов, отработанных ими в дневную (с 9:00 до 16:00 ча­сов) и вечернюю (остальные часы) смены. Расценки на работу в вечер­нюю смену на 60% выше, чем в дневную. Кроме того, если рабочий от­работал более 8-и часов, ему положена доплата за сверхурочные часы (часы свыше восьмого) в размере 50% от обычного тарифа. Для каждо­го работника заданы фактические начало и конец его смены. Считает­ся, что рабочий трудится только в дневную или только в вечернюю смену. В ячейках D10 и Е10 подсчитать число рабочих, трудившихся в вечернюю и дневную смены.

Контроль А6:А8: Фамилия ={Иванов, Петров, Лукин, Васин}.

Форматирование А6:А8: если общее число часов > 20час.

График: Фамилия – Сумма.

Задача 26. Определить <Сумму> на счету клиента банка по ис­течении установленного <Срока> хранения. <Процент банковской премии> определяется тарифной сеткой (А1:Е5), зависящей от суммы <Вклада> (от 0$, от 5000$ и т.п.) и <Срока> его хранения (от 0, 6 и т.д. месяцев). Кроме того, если клиент открыл счет более, чем на 100 00 сроком не менее, чем на 6 месяцев, ему вручается подарок в размере 1% исходного значения вклада. Здесь также по известной продолжительности депозита нужно вычислить дату его закрытия (число месяцев плюс один день). В ячейке H12 подсчитать число человек, получивших подарки.

Контроль В8:В10: Вклад > 200$.

Форматирование D8:D10: если Срок > 30 мес.

График: Вкладчик-Сумма.

Задача 27. Построить таблицу расчетов страхования жизни Клиент может застраховаться на любую <Страховую сумму>, для чего делает <Взнос> в размере, зависящем от возраста застрахованного (до года – 10% от страховой суммы, от года до пяти – 8% и т.д.). Лица старше 65 лет вносят 10% и еще по одному проценту за каждый год после 65-ти. Кроме того, необходимо рассчитать сумму выплаты в за­висимости от тяжести <Травмы> (всего 4 категории). При возникнове­79а79 травмы или болезни клиенту возвращается соответствующий про­цент от страховой суммы. В ячейке Е12 подсчитать число всех страховых случаев. Проценты взноса и выплат определяются с помощью функции ГПР().

Контроль В9:В11: Год рождения>=1900.

Форматирование А9:А11: если Возраст > 65.

График: Клиент – Взнос.

Задача 28. Построить таблицу расчетов ежегодного страхования автомобиля. Клиент может застраховаться на любую «Страховую сумму>, для чего делает <Взнос> в размере 3% от страховой суммы.

Контроль В1: Текущий год >=1998.

Форматирование А6:А8: если Угон.

График: Клиент – Выплата.

Этот взнос уменьшается на 5% для лиц, имеющих более 10 лет во­дительского стажа (отсчитывается от года получения автомобилистом водительских прав). Кроме того, учитывается число безаварийных лет, в течение которых владелец страховался в данной фирме. Если свыше 1 года, размер взноса снижается на 5% от номинального, если свыше 1 лет – на 7% и т.д. При возникновении страхового случая (аварии) стра­ховой агент устанавливает фактическую сумму потерь клиента, которая и выплачивается ему в размере, не превышающем страховой суммы. В случае угона автомобиля в колонку «Угон» вносится буква «у» и выплачивается вся страховая сумма. В ячейке G9 подсчитывается число всех угнанных, ранее застрахованных, автомобилей.

Задача 29. Создать сводную таблицу расчета числа отработан­ных в месяц часов и заработков работников цеха, каждый из которых может трудиться на любом из трех участков цеха. Часовая оплата труда отображена в Тарифах, фактически отработанные часы – в Ежеднев­ном графике (этот раздел таблицы может неограниченно расти). В Сводке следует подсчитать общее число, отработанных каждым рабо­чим часов на каждом из участков, их сумму и зарплату (Сводку удобно расположить на отдельном листе). Размер этой части таблицы не изме­няется. Здесь каждый работник, независимо от количества выходов на работу, представлен одной сводной строкой. Зарплата работника уве­личивается на 10%, если он отработал более 200 часов.

Контроль В6:В 10: Участок= {1,2,3}.

Форматирование I7:I10: если Всего часов>200ч.

График: Фамилия (Е7:Е9) – Зарплата (J7:J9).

Задача 30. Создать таблицу расчетов с клиентами отеля, о кото­рых известны даты въезда, съезда и класс занимаемого номера (от Люкс до 3-го). <Оплата> за номер определяется числом дней прожива­ния, умноженным на тариф соответствующего класса (использовать функцию ГПР()). Кроме того, имеются <Скидки/доплаты>. Если кли­ент проживает в номере больше 10 дней, ему делается скидка по опла­те в 15% за каждый день свыше десятого. Если номер клиентом был ранее предварительно забронирован, он доплачивает за бронь сумму в размере платы за один день проживания. <Общая сумма> складывается из <Оплаты> и <Доплаты/Скидки>. В области В4:Е4 формируется сводка по наполнению номеров. Здесь подсчитывается число занятых номеров соответствующего класса.

Контроль Е8:Е10: Класс номера= {Люкс, 1,2,3}.

Форматирование А8:А10: если Класс номера=Люкс.

График: Клиент – Оплата.

Задача 31. Построить таблицу расчетов страхования имущества от кражи, пожара и протечки (в произвольной комбинации). Желаемый вид страховки обозначается знаком «+». Клиент может застраховаться на любую <Страховую сумму>, для чего делает <Страховой взнос> в размере 10% от страховой суммы, если имущество страхуется от пожа­82а, 8% - если от протечки, 7% - от кражи. Страховка от кражи снижа­ется на 1 % при наличии стальной двери и еще на 2%, если в подъезде имеется вахтер. Взнос уменьшается на 1%, если страхование произво­дится на все виды страховых случаев сразу. В области B4:D4 подсчитать число страховок по видам.

Контроль G8:G10: Страховая сумма кратна 1000р.

Форматирование А8:А10: если страховка на все случаи.

График: Клиент – Страховая сумма.

Задача 32. Выполнить расчеты с клиентами на туристическое обслуживание их семей, включающее перелет и проживание в месте отдыха (найти <цену путевки>).

Контроль Е7:Е9: Класс отеля= {***, ****, *****}.

Форматирование А7:А9: если Скидки > 0.

График: Клиент – Цена путевки.

О каждой семье известны: имя покупателя путевки, общее число членов семьи и число детей в ней, а также желаемый класс салона в самолете (1-3) и класс отеля (от трех до пяти звезд). Стоимость авиабилета для взрослого пассажира задана в таблице тарифов на перелет (детский билет в половину дешевле). Стоимость проживания взрослого туриста в период отдыха задана в таблице тарифов на проживание в отеле. Один ребенок в семье с двумя взрослыми проживает бесплатно. Во всех других случаях его проживание в половину дешевле. На семью от четырех взрослых делается скидка в 10% стоимости проживания (но только в пятизвездном отеле). Для розыска тарифов использовать функцию ГПР(). В Е11 показать число путевок, с проживанием в пяти­звездном отеле.

Задача 33. Построить таблицу расчетов с постоянными клиентами-покупателями, которым товар отпускается в кредит. О каждом покупке известны: название фирмы-покупателя, дата приобретения, стоимость приобретенного товара (в тыс. руб.) и плановая дата возврата кредита за товар. В столбце <Долги> вычисляется величина кредита, которая зависит от его длительности. При сроке свыше 5-и дней – это 2% от исходной стоимости, свыше 10-и – 4%, свыше 15-и – 5% и еще по 1% за каждый день поле 15-го (таблицы кредитных ставок находятся в области В2:ЕЗ). Если оплата произведена, она фиксируется зна­ком «+». При задержке в оплате свыше 20-го дня в столбце G (значок  берется из шрифта Wingdings) должно появляться слово «Звонить». В клетке G11 вычислить сумму всех таких непогашенных кредитов. В области I6:J8 подсчитываются общие неоплаченные долги клиентов. Здесь каждый клиент представлен только одной строкой.

Контроль С4:С6: Дата покупки <= Сегодня.

Форматирование А6:А10: если «Звонить».

График: Клиент (I6:I8) – Общие долги (J6:J8).

Задание №6. Создание меню (оглавление) рабочей книги

Постановка задачи

Создать автоматизированное меню «Оглавление» созданной рабочей книги с помощью объектов панели инструментов Элементы управления.

Требования к создаваемому меню

Меню должно находиться на отдельном листе и состоять из требуемого числа кнопок. Число кнопок определяется количеством созданных рабочих листов.

Технология создания меню «Контрольная работа»

5. Необходимо выполнить настройку панели инструментов – Элементы управления. Эту панель инструментов можно открыть, выполнив команду Вид/Панели Инструментов/Элементы Управления, после чего откроется окно панели инструментов Элементы Управления смотрите рисунок 6.1.

Рисунок 6.1. Панель элементы управления.

Замечание. Если установить указатель мыши на какой-либо кнопке панели инструментов, то приблизительно через 2 секунды на экране появится подсказка – описание функции данной кнопки.

2. Создаём новый рабочий лист с именем «Меню», на котором будет создано меню, состоящее из кнопок с кратким именем (для простоты) созданных рабочих листов контрольной работы.

3. Создание кнопок меню.

С помощью кнопки панели элементов управления создадим на листе «Меню» требуемое количество кнопок:

- выделим произвольную ячейку;

- щелкнем по кнопке ;

- щелкнем по выделенной ячейке, появиться кнопка – Command Button;

- вызываем контекстное меню появившейся кнопки и выбираем редактор: меню «Объект CommandButton»/Edit.

- в редакторе создаём имя для первой кнопки и задаём её размеры.

4. Создание процедур активизации рабочих листов контрольной работы.

Каждой созданной кнопке «Меню» должна соответствовать процедура, активизирующая данный рабочий лист. Такую процедуру можно создать с помощью системы программирования Visual Basic. Двойным щелчком по созданной кнопке открывается окно MS VB, в заголовке которого указаны имя книги с контрольной работой и лист, где находится меню (смотрите рисунок 6.2).

Рисунок 6.2. Окно MS Visual Basic Application.

Слева расположена схема объектов созданной книги. Справа – автоматически созданная «заготовка» процедуры, состоящая из двух операторных скобок – объявление начала процедуры и конца процедуры, которую требуется заполнить. В теле процедуры будем использовать метод Activate активизации объекта – рабочий лист «Табулирование». Пример приведён на рисунке 6.3.

Рисунок 6.3. Процедура активизации рабочего листа «Табулирование»

5. Для выполнения процедуры перехода к окну Excel (открытия листов рабочей книги) необходимо отключить кнопку – режим конструктора щелчком мыши.

6. Просмотрев рабочий лист контрольной работы, нужно вернуться на лист «Меню». Для этого создаём кнопку возврата на листе «Табулирование», аналогично созданной ранее кнопки листа «Меню».

7. Далее по очереди создаём все недостающие кнопки для каждого рабочего листа книги «Контрольная работа» (пункты 3 – 6).

Созданное меню приведено на рисунке 6.4. Пример листа «Табулирование» - на рисунке 6.5.

8. Сохранить созданное меню на листе «Меню» рабочей книги «Контрольная работа».

Рисунок 6.4. Меню контрольной работы.

Рисунок 6.5. Рабочий лист «Табулирование» книги

«Контрольная работа».