Задание 4. Работа со встроенными функциями MS Excel

1. Добавить в книгу новый рабочий лист. Присвоить имя «Табель».

2. Построить табель учета отработанных часов сотрудниками магазина за первую половину месяца (табл. 3). Продолжить заполнение граф «Числа месяца» обозначениями: «8» – рабочий день, «б» – дни по болезни, «о» – отпуск, «п» – дни прогула.

3. Для ввода фамилий сотрудников использовать автозаполнение, создав предварительно список фамилий для Автозаполнения. Фамилия первого сотрудника – это Ф.И.О. студента, выполняющего задание.

Таблица 3

4. Определить количество:

- отработанных часов (функция СУММ);

- дней, пропущенных по болезни (функция СЧЕТЕСЛИ, критерий − текст "б");

- прогулов (функция СЧЕТЕСЛИ, критерий − текст "п");

- дней отпуска (функция СЧЕТЕСЛИ, критерий − текст "о");

- отработанных дней (функция СЧЕТ);

- общее количество дней отпуска каждой категорией работников (функция СУММЕСЛИ).

5. Отформатировать таблицу.

6. Создать колонтитул.

7. Ввести на новый рабочий лист данные для расчета заработной платы сотрудников за первую половину месяца (табл. 4). Таблица 4

8. Построить на новом рабочем листе дополнительные таблицы (табл. 5, табл. 6). Рассчитать заработную плату каждого сотрудника, используя следующие данные:

- Начислено = Оклад / Количество рабочих дней * Количество отработанных дней. Данные о количестве рабочих дней и количестве отработанных рабочих дней находятся на листе «Табель».

- 20 % РК (районный коэффициент) = Начислено * 20 %.

- 30 % ДВК (дальневосточный коэффициент) = Начислено * 30 %.

- Индивидуальная надбавка = Начислено * % надбавки. Индивидуальная надбавка зависит от должности, стажа работы и оклада. (Для вычисления индивидуальной надбавки использовать функции ЕСЛИ, И, ИЛИ).

Дополнительные таблицы для решения задачи:

Таблица 5

Должность

% надбавки

директор, гл. бухгалтер

Стаж работы >= 15 лет

директор, гл. бухгалтер

Стаж работы < 15 лет

остальные сотрудники

Стаж >= 10 лет или оклад <3000

Таблица 6

№ п/п

Ф.И.О. сотрудника

Должность

Стаж работы в организации (лет)

Количество детей

Оклад

Совокупный доход

Среднедневной заработок

A

директор

15 000

176 538

B

гл. бухгалтер

10 000

62 308

C

бухгалтер

8 000

53 169

D

бухгалтер

8 000

51 920

F

продавец

4 000

37 800

E

продавец

4 000

40 230

G

продавец

3 000

33 231

H

уборщица

2 000

15 508

- Отпуск = Среднедневной заработок * Количество дней отпуска

- ПВНТС (больничный) за счет средств работодателя = кол-во дней по болезни * среднедневной заработок * Р.

Р = 100 %, если стаж работы ≥ 8, Р = 80 %, если 5 ≤ стаж < 8, Р = 60 %, если стаж работы < 5 лет. Для вычисления ПВНТС использовать вложенные функции ЕСЛИ.

- НДФЛ (налог) = (Итого по кредиту счета 70 – Вычет 1 – Вычет 2 * Количество детей) * 13%. Вычет 1 предоставляется на работающего до момента достижения суммы совокупного годового дохода = 20 000. Вычет 2 предоставляется на каждого ребенка до достижения суммы совокупного дохода = 40 000 (табл. 7). Для вычисления НДФЛ использовать функцию ЕСЛИ. Таблица 7

Сумма, не облагаемая налогом

на работающего

на ребенка

400,00р.

600,00р.

- Аванс = Итого по кредиту счета 70 * 40 %

- Итого по кредиту счета 70 = Начислено + 20 % РК + 30 % ДВК + Персональная надбавка + Отпуск + ПВНТС за счет средств работодателя.

- Итого по дебету счета 70 = НДФЛ + Аванс.

- К выдаче = Итого по кредиту счета 70 – Итого дебету по счета 70. В формуле предусмотреть округление полученных сумм к выдаче с точностью до копеек с помощью функции ОКРУГЛ.

- Рассчитать итоговую сумму к выдаче, итого по кредиту счета 70, итого по дебету счета 70 по всему предприятию (СУММ).

- Отсортировать и отформатировать таблицу «Ведомость учета оплаты труда».

- Для денежных величин задать «Финансовый формат».

9. Присвоить рабочим листам имена «Ведомость» и «Дополнительные таблицы». Создать колонтитул, поместив в него Ф.И.О. и дату создания.

10. По столбцам «Итого по кредиту сч. 70» и «Итого по дебету сч. 70» построить коническую и цилиндрическую диаграммы. Коническую диаграмму построить по столбцам выделенного диапазона, цилиндрическую – по строкам.

11. На отдельном листе построить график по столбцу «К выдаче».

В диаграммах должны присутствовать следующие элементы:

- заголовок диаграммы;

- заголовки осей;

- легенда с именами рядов данных;

12. Показать работу преподавателю.