Ведомость на выдачу заработной платы

4) Запишите макрос «Диаграмма», создающий диаграмму на отдельном листе (объемная круговая, подписи данных - значение), отражающую начисления каждого сотрудника. На листе «Начисления» поместите кнопку «Диаграмма» для выполнения этого макроса. После выполнения макроса «Диаграмма» переименуйте созданный лист в лист «Диаграмма».

5) Запишите макрос «Новый сотрудник», добавляющий в таблицу пустую 10 строку и необходимые формулы. На листе «Начисления» поместите кнопку «Новый сотрудник» для выполнения этого макроса.

6) Введите данные нового сотрудника.

7) Запишите макрос «Сортировка ФИО», выполняющий сортировку таблицы сведения о начислениях по фамилии. На листе «Начисления» поместите кнопку «Сортировка ФИО» для выполнения этого макроса.

8) Запустите макрос «Новый сотрудник», введите данные, затем запустите макрос «Сортировка ФИО».

9) Вставьте лист. Переименуйте его в «Итоги». Скопируйте таблицу сведения о начислениях на этот лист и выполните автоматическое вычисление итогов по числу детей.

Пояснения к выполнению работы:

Надбавка районная вычисляется как произведение районного коэффициента на оклад. Формула, которую необходимо ввести в ячейку D7, имеет вид = $K$3*C7. После ввода формулы ее нужно распространить вниз (протянув за маркер автозаполнения).

Всего начислено вычисляется как сумма оклада и районной надбавки. Формула, которую необходимо ввести в ячейку E7, имеет вид = C7+D7. После ввода формулы ее нужно распространить вниз (протянув за маркер автозаполнения).

Принять профсоюзный и пенсионный налоги, составляющими по 1% от оклада. Формула, которую необходимо ввести в ячейку F7, имеет вид = $C7/100 или =$C7*1%. После ввода формулы ее нужно распространить вниз (протянув за маркер выделения) и затем вправо на один столбец.

Ячейке, содержащей значение Мин.з/п, присвоить имя Мин_з_п, выполнив команду Вставка-Имя-Присвоить. Далее в поле Имя введите нужное имя ячейки.

Подоходный налог подсчитать по формуле 13% от Всего начислено за вычетом пенсионного фонда, минимальной зарплаты и льгот на детей. Льготы на детей составляют 75% от минимальной зарплаты умноженной на количество детей. Формула имеет вид = (E7 – G7 – Мин_з_п – Мин_з_п * 75 / 100 * K7) * 13 / 100 или = (E7 – G7 – Мин_з_п –Мин_з_п * 75% * K7) * 13%. После ввода формулы в ячейку H7, ее нужно распространить вниз.

Всего удержано вычисляется как сумма всех налогов и размещенной в ячейке I7 и распространенной вниз. Для подсчета Суммы к выдаче примените формулу, вычисляющую разность всего начислено и налогов. Примерный вид формулы =E7-I7, размещенной в ячейке J7 и распространенной вниз.

Для вычисления Итого, в ячейку D18 введите Автосуммирование S, затем распространите формулу по строке.

Для вычисления среднего значения окладов в ячейку E19 необходимо с помощью мастера функций на панели инструментов (или командой Вставка – Функция) ввести функцию СРЗНАЧ() из категории Статистические. Аргументом для функции является диапазон ячеек E7:E17, т.е. получится формула =СРЗНАЧ(Е7:Е17).

Переименовать ярлычки листов в соответствии с их содержимым. Для этого, выполните команду Формат-Лист-Переименовать и в поле ввода Имя листа введите новое название листа, например, “Начисления”.

Перейдите к Листу 2. Сразу же переименуйте его в “Детские”. Выделите ячейку А2 листа “Детские” и введите формулу: = Начисления!В7, где имя листа определяется восклицательным знаком, а В7 - адрес ячейки, в которой размещена первая фамилия сотрудника на листе “Начисления”. Распространите формулу вниз. В графе “Сумма” разместите формулу =Начисления!K7*Начисления!$K$4, где K7 - адрес первой ячейки на листе “Начисления”, содержащей число детей, K4 - адрес ячейки, содержащей пособие на одного ребенка. Заполните эту формулу вниз.

Для того, чтобы список состоял только из сотрудников, имеющих детей, установите фильтр по наличию детей (Данные-Фильтр-Автофильтр, в раскрывающемся списке “Сумма” выберите и установите критерий по условию >0).

Для записи макроса необходимо выполнить команду Сервис – Макрос – Начать запись. В появившемся диалогом окне введите имя макроса, например: «Выдача». Далее добавьте лист и на этом листе создайте ведомость на выдачу заработной платы, осуществляя связь с листом «Начисления».Выполните оформление таблицы по образцу. Затем остановите запись макроса (Сервис-Макрос-Остановить запись).

Для создания кнопки необходимо добавить панель инструментов – Формы (Вид–панели инструментов-Формы). Выбрать инструмент Кнопка и расположить ее листе «Начисления». В появившемся диалогом окне Назначить макрос объекту выберите необходимый макрос, например: «Выдача». Измените текст названия кнопки на текст «Ведомость». Проверьте выполнение макроса, нажав на созданную кнопку. Переименуйте добавленный лист «К выдаче».

Для создание макроса «Диаграмма» необходимо выполнить аналогичные действия по созданию предыдущего макроса.

Чтобы выделить два столбца в разных местах таблицы, следует удерживать нажатой клавишу Ctrl.

Для построения диаграммы требуется выделить два столбца таблицы: “Фамилия, имя, отчество” и “Сумма к выдаче”, и запустить Мастер диаграмм одним из способов: либо выбрав кнопку на панели инструментов, либо команду меню Вставка-Диаграмма. Передвигаясь по шагам с Мастером диаграмм, выберите тип диаграммы - круговая объемная, подписи данных - Значение. Поместить диаграмму на листе - Отдельном.

Переименуйте лист, содержащий диаграмму из “Диаграмма1” в “Диаграмма”.

При записи макроса «Новый сотрудник» для добавления пустой строки нужно выделить ту строку, которую будете вставлять (у нас 10-ая строка) и выполнить команду Вставка – Строки. Затем, в предыдущей строке выделите ячейки, содержащие формулы (это диапазон ячеек E9:K9), и с помощью маркера заполнения распространите формулы в добавленную строку. Остановите запись макроса. Введите данные о новом сотруднике.

При записи макроса «Сортировка ФИО» для сортировки таблицы нужно выделить ячейку, содержащую Фамилия, имя, отчества или любую другую ячейку из этой строки и выполнить команду Данные - Сортировка. В поле Сортировать по выберите из списка Фамилия, имя, отчество. Переключатели установите в положения: Сортировать по - по возрастанию, Идентифицировать поля по - подписям. Остановите запись макроса

Проверьте, какая связь существует между таблицей начислений и диаграммой. Для этого перейдите на лист “Начисления” и запустите макрос «Новый сотрудник». Введите данные о новом сотруднике. Теперь перейдите на лист “Диаграмма” и проверьте, как новые данные отразились на диаграмме. Данные о новом сотруднике сразу же вносятся в диаграмму.

Выполните сортировку таблицы с помощью макроса «Сортировка ФИО», нажав на кнопку «Сортировка ФИО».

Для выполнения автоматического вычисления итогов по числу детей, сделайте сортировку по числу детей, а затем выполните команду Данные – Итоги. В диалоговом окне Промежуточные итоги установите в поле При каждом изменении – Число детей, в поле Операция – Количество, в поле Добавить итоги по –Число детей. Установите флажки в Заменить текущие итоги и Итоги под данными.