Создание таблицы «Расчет заработной платы сотрудников ооо «Вояж»

В данном примере показано использование в расчетах абсолютных ссылок и следующих функций:

• ТДАТА( ) – текущая дата;

• ОКРУГЛ(число;кол-во десятичных знаков после запятой) – округление числа до количества нужных десятичных знаков;

• ОСТАТ(число;делитель) - возвращает остаток от деления аргумента «число» на значение аргумента «делитель». Результат имеет тот же знак, что и делитель.

1. Создайте книгу в MS Excel и сохраните ее в своей папке с именем ООО Вояж.

2. Перейдите на Лист1, измените его имя на Зарплата.

3. Сформируйте, отформатируйте и заполните таблицу в соответствии с приведенным образцом (рис.2.16).

Рис.2.16. Таблица Зарплата

4. Заполните столбцы G4: K4 формулами:

- выполните расчет доплаты или штрафа:

- для расчета в столбце Доплата/Штраф используются абсолютные ссылки и функция ЕСЛИ: если у сотрудника есть жалобы/взыскания, то рассчитывается размер штрафа (со знаком минус - штраф, если у сотрудника нет жалоб, то – величина доплаты:

=ЕСЛИ(Жалобы>0; –Оклад *%штрафа*Кол-во; Оклад*%доплаты)

- вставьте формулу в ячейку G4, используя абсолютные ссылки:

=ЕСЛИ(F4>0;-E4 *$D$17*F4;E4*$D$16)

- выполните расчет начислений в столбце Начислено:

=Оклад + Жалобы(Взыскания)

- выполните расчет удержаний:

- для расчета в столбце НФДЛ используется формула:

=Начислено * % удержания НФДЛ

- вставьте формулу в ячейку Н4, используя абсолютные ссылки:

=G4*$C$20

- аналогичным образом вставьте для расчета в столбце Проф. взносы в ячейку I4 формулу, используя абсолютную адресацию:

=Начислено * % удержания проф. взносы

- для расчета в столбце Удержано, всего вставьте формулу в ячейку J4:

=НФДЛ + Проф. взносы

- для расчета в столбце К выдаче вставьте формулу в ячейку К4:

=Начислено – Удержано, всего

- скопируйте формулы первой строки для остальных сотрудников: выделите интервал ячеек G4:K4 и, используя маркер автозаполнения, скопируйте формулы на весь диапазон;

- выполните форматирование расчетных данных: установите денежный формат, выполните подгонку ширины столбцов.

5. Перейдите на Лист2, измените его имя на Юбиляры;

- сформируйте таблицу в соответствии с образцом (рис.2.17);

Рис.2.17. Таблица Юбиляры

- вставьте формулы в ячейки первой строки:

- для столбца ФИО в ячейку А3 занесите формулу:

=Зарплата!B4

Здесь: Зарплата – это имя предыдущего листа;

! – разделитель (обязательный реквизит);

В4 – имя ячейки, в которой находится данное;

- аналогично для столбца Дата рождения занесите формулу в ячейку В3;

- рассчитайте возраст сотрудников (полных лет), используя функцию ТДАТА( ) и функцию округления до целого ОКРУГЛ:

=(Текущая дата – Дата рождения) / 365

в ячейке С3 эта формула будет иметь вид:

=ОКРУГЛ((ТДАТА()-B3)/365;0)

- отметьте сотрудников, у которых на текущую дату юбилей: если количество лет без остатка делится на 5, то у сотрудника юбилей, иначе в расчетной ячейке будет символ «-», следовательно, в ячейку D3 столбца Юбилей нужно занести следующую формулу:

=ЕСЛИ(ОСТАТ(C3;5)=0;"юбилей";"- ")

- выполните расчет столбца Сумма: если у сотрудника юбилей, то ему начисляется премия в размере 2000 руб., следовательно в ячейку Е4 необходимо вставить формулу:

=ЕСЛИ(D3="юбилей";2000;"")

- скопируйте формулы первой строки на остальной диапазон;

- подведите итоги:

- для столбца Юбилей необходимо подсчитать количество юбиляров, используя функцию СЧЕТЕСЛИ:

=СЧЕТЕСЛИ(D3:D12;"юбилей")

- для столбца Премия – итог по столбцу.

6. Перейдите на Лист3, измените его имя на Премия;

- сформируйте и отформатируйте таблицу согласно образцу (рис.2.18);

Рис.2.18. Таблица Премиальная ведомость

- вставьте формулы в ячейки первой строки:

- для столбца ФИО в ячейку А3 занесите формулу:

=Юбиляры!A3

- в столбец К выдаче в ячейку D3 данные занесите из столбца Премия на листе Юбиляры;

- скопируйте формулы на остальной диапазон;

- подсчитайте итог по столбцу К выдаче;

- скройте пустые строки:

- выделите, при нажатой клавише CTRL все строки кроме юбиляров (рис.2.19);

- установите курсор на выделенном номере строки, откройте меню правой кнопкой мыши и выберите команду Скрыть (обратная команда Отобразить).

2 ,

Рис.2.19. Таблица Премия