Количество отработанных дней заполняется вручную, все остальные данные – по формулам!!!!!
Таблица 2 Разрядная сетка
| Разряд | Оклад |
В процессе решения задачи необходимо будет задавать размер минимальной зарплаты и количество рабочих дней в месяце.
7.На листе «Справочник» в ячейках К5:К14 создать список праздничных (выходных) дней в январе месяце, например: 01.01.2009, 02.01.2009 и т. д. Установить для этих ячеек соответствующий формат отображения дат (рис.1).
Рис.1. Лист «Справочник»
III. Создать таблицу для расчета зарплаты за ЯНВАРЬ месяц:
Перейти на другой лист рабочей книги. Переименовать его в «январь». Ввести «шапку» таблицы.
1.Заполнить колонку с табельными номерами работников (8 строк) в произвольном порядке (рис.2).
Количество отработанных дней заполняется вручную, все остальные данные – по формулам!!!!!
Необходимо будет рассчитать:
- начисленную зарплату:
ЗП=ЗПР*ФТ/Т;
- удержание в пенсионный фонд:
УПФ=ЗП*0.01;
- удержание подоходного налога:
УПН=(ЗП-УПФ-МЗП*Л)*ПРПДН;
- удержание профсоюзных взносов:
УПВЗН=ЗП*ПРВЗН
(только для членов профсоюза);
- удержание алиментов:
УАЛ=ПРАЛ*(ЗП-УПН)/100
(только для лиц, выплачивающих алименты);
- зарплату к выдаче:
ЗПВ=ЗП-УПФ-УПН-УПВЗН- ПРАЛ,
где:
ФT – фактически отработанное время (дней);
Л – количество льгот;
ЗПР – оклад работника в соответствии с его разрядом;
МЗП – минимальный размер оплаты труда;
Т – плановое количество рабочих дней в месяце;
ПРАЛ – процент удержания алиментов;
ПРВЗН – процент удержания профсоюзных взносов;
ПРПДН – процент удержания подоходного налога.
3.Ввести внизу под расчетной таблицей справочные данные для текущего месяца:
- размер минимальной оплаты труда;
- процент удержания подоходного налога;
- процент удержания профсоюзных взносов;
- процент удержания для выплат пенсионный фонд;
- текущая расчетная дата, например 01.01.2009;
- плановое количество рабочих дней в месяце.
4.Рассчитать плановое количество рабочих дней в январе, используя стандартную функцию: ЧИСТРАБДНИ (нач_дата;кон_дата;праздники)
Перед вводом этой функции убедиться, что она доступна, т. е. имеется в списке стандартных функций категории «Дата и время». Если этой функции в списке нет, то ввести ее в список, выполнив команду СЕРВИС/Надстройки и включить на вкладке флажок «Пакет анализа».
Ввести в качестве начальной даты текст: «01/01/1998».
Ввести в качестве конечной даты текст: «31/01/1998».
Ввести в качестве 3-го аргумента (выходные дни) диапазон ячеек на листе «Справочник», содержащий список выходных дней.
5.Ввести расчетные формулы в строку первого работника:
- для определения фамилии по табельному номеру ввести в ячейку формулу поиска фамилии в справочнике: =ВПР ();
- для вставки формулы установить курсор в заданную клетку и щелкнуть по кнопке «=». Выбрать в окне со списком функций: функцию просмотра и ссылки ВПР;
- щелкнуть мышью по клетке с таб. номером. В поле «Искомое значение» появится соответствующий адрес;
- щелкнуть мышью по полю «Табл_массив», активизируя его. Выделить мышью область клеток справочной таблицы, перейдя на лист «Справочник». В активизированном поле появятся соответствующие адреса справочной таблицы. Последовательно устанавливать курсор на адреса клеток и нажимать клавишу «F4» для замены адресов на абсолютные;
- занести в поле «Номер_индекса_ столбца» значение 2 (для фамилии);
- ввести в поле «Диапазон_просмотра» текст «ЛОЖЬ» или число 0.
6.После ввода и отладки всех формул 1-ой строки скопировать их вниз для каждого работника. Предусмотреть невозможность отрицательных значений подоходного налога (функция ЕСЛИ)
7.Рассчитать сумму «Удержано всего» по каждому работнику.
8.Рассчитать сумму «К выдаче» как разность между начисленной суммой и суммой «удержано всего».
9.Подсчитать итоговые суммы по всем работникам.
IV. Создать таблицы для расчета зарплаты за месяцы Февраль, Март, Апрель, Май, Июнь:
Таблицы создавать по аналогии с Январем, каждый раз переходя на новый лист, листы переименовать соответствующим образом.
Рис.2 Лист «Январь»
I. Функция консолидации данных позволяет получать результирующие таблицы, содержащие итоги для данных, расположенных в различных областях одного или нескольких рабочих листах или даже в разных книгах. Консолидируемые данные могут быть динамически связаны с исходными данными таким образом, что при изменении исходных, автоматически обновляется итоговые данные.
Задание. Сформировать сводную таблицу с итогами по начислениям и удержаниям заработной платы методом консолидации. Необходимо создать новую таблицу, содержащую итоги по каждому работнику за полгода и имеющую связь с исходными данными в таблицах расчета заработной платы за период январь-июнь.
Технология формирования сводной таблицы
Открыть файл, созданный на предыдущем занятии (Расчет заработной платы работников фирмы за 1-й квартал 2008г.);
открыть новый лист, присвоив ему имя «Консолидация»;
выделить на листе начальную ячейку, например А5, и выполнить команду ДАННЫЕ/Консолидация;
в окне «Консолидация» выбрать в качестве функции «Сумму», ввести ссылку на консолидируемые ячейки таблицы, находящейся на листе «январь». Для этого щелкнуть в окне ссылки, затем по ярлыку листа «январь» и выделить ячейки справа и вниз, начиная с ячейки «Фамилия, имя, отчество»;
вернуться в окно консолидации и нажать кнопку «Добавить»;
повторить описанные выше действия для следующих месяцев расчета;
после добавления ссылок на данные марта в окне консолидации включить флажки«использовать в качестве имени значения левого столбца» и «создавать связи с исходными данными»;
щелкнуть по кнопке «Ok». На экране появится таблица консолидации. Добавить к таблице отсутствующие заголовки столбцов;
используя расположенную слева схему управления структурой включить детальный (второй) уровень просмотра. Переключить на первый уровень просмотра итогов;
проверить наличие динамической связи с исходными таблицами, изменив в них первичные данные, например количество отработанных дней.