Материал: Информационные системы в экономике. лабораторный практикум (MS Excel 2010). Лубянская Э.Б., Лукаш Е.Н

ЛАБОРАТОРНАЯ РАБОТА № 6

1. Создать новую книгу Страховка.xlsx и ввести указанную таблицу:

Стоимость страхового полиса

2.Для ячеек, содержащих названия граф, задать расположение по центру столбца, шрифт жирный, задать перенос по словам.

3.Вычислить базовую стоимость страхового полиса. Базовая стоимость полиса вычисляется следующим образом: если марка автомобиля BMW или AUDI, то базовая стоимость полиса равна 7% от стоимости автомобиля, если марка автомобиля CHEVROLET или FORD, то стоимость полиса составляет 6% от стоимости авто, если марка ВАЗ или ГАЗ, то стоимость полиса - 5% стоимости автомобиля. Для автомобилей любой другой марки стоимость полиса равна 5,5% от стоимости авто. Для вычисления базовой стоимости полиса следует использовать вложенные функции ЕСЛИ и функцию ИЛИ.

4.Вычислить размер доплаты/скидки. Величина доплаты равна 1% от стоимости автомобиля при условии, если его возраст больше 5 лет. Если же возраст автомобиля меньше 5 лет, то предоставляется скидка в размере 1% от стоимости авто.

5.Вычислить итоговую стоимость страхового полиса как сумму базовой стоимости и величины доплаты/скидки.

121

ЛАБОРАТОРНАЯ РАБОТА № 7

1. Создать рабочую книгу Расчет зарплаты.xlsx. Сохранить ее с указанным именем в папке Мои документы.

2. Переименовать текущий лист рабочей книги в лист с именем Справочник работников, второй лист в Январь, а третий – в Справочник окладов.

3. Добавить в рабочую книгу еще два листа (щелкнуть правой клавишей мыши по ярлычку любого листа книги, в появившемся контекстном меню выбрать команду Добавить и выбрать объект «Лист») и дать им соответственно названия

Справочные данные и Праздничные дни.

4. Создать следующую таблицу на листе Справочник работников:

Справочник работников предприятия

5. Установить для всех ячеек выравнивание по центру по вертикали и горизонтали, включить режим переноса по словам.

6. На листе Справочник работников ввести 10 строк с информацией с учетом следующих правил:

- табельные номера вводить в произвольном порядке; - ФИО вводить произвольно;

- разряд вводить в диапазоне от 1 до 7 (несколько человек будут иметь одинаковые разряды);

- предусмотреть только три отдела (по несколько человек в одном отделе), т.е. отделы нумеровать только числами от

1 до 3;

- количество льгот по налогообложению: от 0 до 5; - предусмотреть только 3 варианта должностей (не-

сколько человек будут занимать одинаковые должности);

122

-сделать 5 человек членами профсоюза (для членов профсоюза вводить 1, для других работников – 0);

-процент удержания алиментов указать для трех человек в диапазоне от 25 до 50. У остальных работников – 0.

7.Отсортировать строки таблицы по возрастанию табельного номера (вкладка “Главная”, в группе «Редактиро-

вание»).

8.На листе Праздничные дни создать список из 10 праздничных дней текущего года, например: 01.01.2017, 23.02.2017, 08.03.2017, 09.05.2017, 12.06.2017, 01.11.2017 и т.

д. Установить для этих ячеек соответствующий тип в формате

«Дата».

9.Ввести на листе Справочные данные информацию для дальнейших расчетов, при этом установить для всех ячеек столбца с данными соответствующий формат данных: денежный, процентный, дата, числовой:

Рассчитать показатель «Плановое количество рабочих дней в месяце» с помощью функции: ЧИСТРАБДНИ.

Ввести в качестве начальной даты текст: “1.01.2017”. Ввести в качестве конечной даты ссылку на ячейку, со-

держащую текущую расчетную дату.

Ввести в качестве третьего аргумента (праздники) весь диапазон ячеек на листе Праздничные дни, содержащий список праздничных дней.

10. Создать следующую таблицу на листе Справочник окладов:

123

Справочник окладов

11. На листе Январь создать таблицу для расчета зарплаты за январь месяц.

Расчет зарплаты за январь 2017 г.

12.Заполнить колонку с табельными номерами работников (10 строк) в произвольном порядке (сами номера должны совпадать с номерами в таблице Справочник, но порядок их должен отличаться). Заполнить столбец с количеством отработанных дней произвольными цифрами в диапазоне от 1 до 20.

13.Ввести расчетные формулы в строку первого работ-

ника:

- для автоматического определения фамилии по табельному номеру необходимо использовать стандартную функцию ВПР. В качестве искомого значения указывается ячейка, содержащая табельный номер работника, в поле «Таблица» - ссылка на диапазон, содержащий диапазон всей таблицы; по-

124

сле выделения таблицы нажать клавишу F4 (абсолютные ссылки); номер столбца - 2, интервальный просмотр - 1;

- начислено:

ЗП=ЗПР*ФТ/Т,

где ФT - фактически отработанное время (дней);

T – плановое количество рабочих дней в месяце; ЗПР - оклад работника в соответствии с его разря-

дом, находится путем применения функции ВПР к табельному номеру сотрудника;

- премия:

При полном отработанном месяце начисляется премия в размере 10 % от оклада

- удержание подоходного налога:

УПН=(ЗП+ПР-МРОТ*Л)*ПРПДН;

где ПР – премия; МРОТ – минимальный размер оплаты труда;

ПРПДН - процент удержания подоходного налога;

- удержание профсоюзных взносов (только для членов профсоюза):

УПВЗН=ЗП*ПРВЗН;

Для определения, является ли сотрудник членом профсоюза или нет, необходимо использовать функции ЕСЛИ и ВПР;

125