Формул и функций в MS Excel

Решение практических задач с использованием

Задание: Создать рабочую книгу для расчета заработной платы работникам УПТП ”Алина”. Произвести расчеты начисления заработной платы, удержаний из заработной платы и суммы к выдаче по каждому работнику и в целом по предприятию.

ТЕХНОЛОГИЯ РАБОТЫ

1) Создать рабочую книгу Ведомость расчета заработной платы;

2) Построить следующие пять таблиц и разместить их на отдельных листах рабочей книги;

3) Внести в таблицы исходные данные только для полей, выделенных в приведенных ниже схемах таблиц:

Таблица 1 «Справочник должностей»

Для поля Должность – формат Текстовый; для поля Оклад – формат Денежный.

Таблица 2 «Справочник сотрудников»

Для поля Табельный номер – формат Дополнительный- Табельный номер; для поля Стаж и Количество детей – формат Числовой.

Для поля Должность сделать ссылки на поле Должность в Справочнике должностей (т.е в ячейке C4 должна стоять формула ='Справочник должностей'!A4 )

Таблица 3 «Ведомость начислений»

4. Для поля Фактически отработано дней – формат поля Числовой; для полей Начислено по окладу, Начислено по окладу с учётом фактически отработанных дней и Всего начислено – формат поля Денежный; для полей Надбавка за стаж, Премия – формат Процентный.

5. Начислено по окладу =ВПР('Справочник сотрудников'!С4; 'Справочник должностей'! $A$4:$B$11; 2; ИСТИНА):

· вызвать функцию ВПР. После появления окна функции выполнить следующие действия

· установить курсор в строку искомо_значение .  Щелкнуть мышью по закладке листа Справочник сотрудников.

· щелкнуть мышью но ячейке С4

· перейти в строку табл_миссив . Щелкнуть по закладке листа Справочник должностей

· выделить диапазон A 4: B 11  таблицы Справочник должностей, охватывающий ячейки со значениями полей Должность и Оклад;

· вернуться в окно функции. Втекущей строке произвести замену ссылки диапазона с абсолютной на относительную, т.е. вместо A 4: B 11  установить A $4$:$ B $11  

· в строке номер индекса столбца указать 2

· в строке диапазон просмотра указать Истина

6. Начислено по окладу с учётом фактически отработанных дней = Начислено по окладу* Фактически отработано дней/24

7. Для графы «Надбавка за стаж», процент надбавки устанавливается в следующих размерах: свыше 20 лет — 20 %; 15—20 лет- 15%; 10-15 лет – 10%; 5-10 лет – 5%; до 5 лет - 0 %.

Надбавка за стаж = =ЕСЛИ('Справочник сотрудников'!D4>15;20%; ЕСЛИ('Справочник сотрудников'!D4>15; 15%; ЕСЛИ('Справочник сотрудников'!D4>10; 10%; ЕСЛИ('Справочник сотрудников'!D4>5; 5%;0)))):

· вызвать функцию Если

· в строке логическое_выражение установить 'Справочник сотрудников'D4>15

· в строке значение_если_истина указать 20 %

· перейти в строку значение_если_ложь и в строке формул, щелкнув по кнопке Раскрыть список, снова выбрать функцию ЕСЛИ

8. Для графы «Премия,%» - процент премии устанавливается исходя на должности в следующих размерах: продавец 1-й или 2-й категории — 30 %; для остальных должностей — 50 %.

Премия =ЕСЛИ(ИЛИ('Справочник сотрудников'!C4='Спавочник должностей'!$A$8; 'Справочник сотрудников'!C4='Спавочник должностей'!$A$9); 30%; 50%):

· сделать текущей ячейку F4 таблицы Ведомость начислений

· выбрать функцию ЕСЛИ;

· установить курсор в строке логическое_выражение в появившемся окне функции ЕСЛИ;

· щелкнув по кнопке Раскрыть список в строке формул,

· выбрать Другие функции... --> Логические -> ИЛИ

· в появившемся окне функции  ИЛИ в строке логическое1 установить параметр 'Справочник сотрудников'!С4 и присвоить этому параметру значение ='Справочник должностей'!$А$8

· в строке логическое2 установить параметр 'Справочник сотрудников'!С4 и присвоить ему значение ='Справочник должностей'!$А$9

· вновь обратиться к функции ЕСЛИ, щелкнув по слову "ЕСЛИ" в строке формул;

· в строке значение_если_истина указать 30 %

· в строке значение_если_ложь указать 50 %

9. Рассчитать всего начислено по формуле:

Всего начислено = СУММА(Начислено по окладу с учётом фактически отработанных дней; Начислено по окладу с учётом фактически отработанных дней * Надбавка за стаж; Начислено по окладу с учётом фактически отработанных дней* Премия).

Таблица 4 «Ведомость удержаний»

Для полей Подоходный налог, Фонд социальной защиты, Профсоюзные взносы, Всего удержано - формат Денежный.

Подоходный налог рассчитывается 12% от Всего начислено.

Фонд социальной защиты рассчитывается 3% от Всего начислено.

Профсоюзные взносы рассчитываются 1% от Всего начислено.

Всего удержано равен Сумма Подоходный налог, Фонд социальной защиты, Профсоюзные взносы.

Таблица 5 «Сводная ведомость начисления заработной платы

 по УПТП ”Алина” за март 2010 года»

Для полей Всего начислено, Всего удержано, К выдаче – формат Денежный.

Всего начислено = Ссылка на Ведомость начислений поле Всего начислено.

Всего удержано = Ссылка на Ведомость удержаний поле Всего удержано.

К выдаче = Всего начислено – Всего удержано.

Для проверки правильности ваших расчётов . Если вы всё правильно рассчитали, то у вас получилась итоговая таблица с такими значениями:

Лабораторная работа №6