Задание для самостоятельной работы

Лабораторная работа по расчету заработной платы и построению диаграмм в EXSEL.

1. Откройте файл Excel «Данные».

2. Сохраните его под своим именем и выполните задачу по расчету заработной платы.

 

ЗАДАЧА. РАСЧЕТ ЗАРПЛАТЫ.

Дан список сотрудников предприятия, с указанием их табельных номе­ров, должности, номера отдела и начисленной заработной платы.

Необходимо произвести расчет зарплаты с указанием итоговой инфор­мации по всему предприятию в целом и по отделам. Рассмотрим упрощен­ный вариант задачи расчета зарплаты, когда все начисления уже произве­дены, а удержания состоят только из подоходного налога, исчисляемого как 12% от суммы начислений.

 

Этап 1. Создание таблицы расчета зарплаты.

Рис. 1. Таблица для расчета зарплаты

 

1. Введите в ячейки В2:Н2 заголовки столбцов. Ф.И.О., Таб. номер, Отдел, Должность, Начислено, Удержано, К выдаче.

2. Занесите данные в остальные ячейки, в соответствии с рисунком 1.

3. Отрегулируйте ширину столбцов в соответствии с введенными данными.

4. Таккакв простейшем случае удержания составляют 12% от начис­ленного, введите в ячейку G3 формулу =F3*12%.Ответьте на вопрос: Какие ссылки использовались при вводе формулы – относительные или абсолютные?

5. Скопируйте введенную формулу в ячейки G4:G12используя маркер автозаполнения.

6. Сумма к выдаче в ячейке НЗ будет равна разности начислений и удержаний. Введите в эту ячейку формулу =F3-G3.

7. Скопируйте введенную формулу в ячейки H4:H12, используя маркер автозаполнения.

8. Для ячеек F3:H12 установите денежный формат. Для этого выполните следующие действия:

· Выделите необходимые ячейки.

· Выполните команду Формат/Ячейки/закладкаЧисло/числовые форматыДенежный.Нажмите ОК.

9. Присвойте рабочему листу имя Зарплата вместо Лист1 и удалите лишние листы.

 

Этап 2. Выбор данных из таблицы расчета зарплаты (создание запросов).

При обработке данных таблицы расчета зарплаты может возникнуть не­обходимость отбора данных, имеющих определенное значение или удовле­творяющих определенным условиям, т.е. в организации разнообразных запросов.

Скопируйте лист зарплата (используя левую кнопку мыши и удерживая клавишу CTRL) и переименуйте его в «фильтр». На новом листе проделайте следующую работу:

Предположим, что необходимо выделить из таблицы записи о сотруд­никах 3 отдела с зарплатой ниже 1000 р.

Для выбора данных из таблицы можно воспользоваться фильтром

1. Выполните команду Данные/Фильтр. В ячейках заголовков столбцов появятся кнопки-стрелки списков фильтров.

2. Выберите сотрудников только 1го отдела.

3.Выберите сотрудников 1го отдела с зарплатой менее 1000 р, используя Числовые фильтры (Настраиваемый фильтр).

Прим. Отменить режим фильтрации можно командой Данные/Фильтр.

Задание. Выделите из таблицы записи о сотрудниках 1 отдела, у которых сумма к выдаче находится в пределах от 1000 р. до 1500 р.

Этап 3. Сортировка таблицы расчета зарплаты.

Так как предприятие имеет несколько отделов, удобнее работать со списком, в котором записи отсортированы по отделам, а в пределах одного отдела - по Ф.И.О. сотрудников.

1. Выполните команду Данные/Сортировка...

2. В открывшемся диалоговом окне Сортировка диапазона в двух полях ввода задайте или выберите из списка ключи сортировки: Отдел, а затем Ф.И. О. Установите порядок сортировки в обоих случаях - по возрастанию.

Трижды скопируйте рабочий лист Зарплата. Назовите новые листы Сортировка 1, Сортировка 2 и Сортировка 3.

Упражнение 2. На листе Сортировка 1 отсортируйте список по двум ключам: Отдел, Начислено. Установите для поля Начислено порядок сортировки по убыванию.

Упражнение 3.Отсортируйте список по трем ключам:

а) на листе Сортировка 2 - по трем ключам Отдел, Должность, Ф.И. О.;

6) на листе Сортировка 3 - по трем ключам Отдел, Должность, Таб. номер.

Сохраните текущее состояние книги Excel.

Этап 4. Получение ведомости.

Ведомость на зарплату (рис. 2) должна включать все данные таблицы расчета зарплаты, за исключением должности сотрудников, и итоговую ин­формацию:

- суммы начисления, удержания и выдаваемой на руки суммы по каждому отделу и в целом по предприятию,

- среднюю зарплату (начисление) по каждому отделу и в целом по предприятию.

Рис. 2 Итоговая ведомость.

Для получения итоговой информации выполните следующие шаги:

1. Скопируйте рабочий лист Сортировка 3 или Сортировка 2 и присвойте новому листу имя Ведомость 1. (Прим. Т.к. Ведомость по отделам получиться в надлежащем виде, только если таблица будет предварительно отсортирована по отделам).

2. В ячейку С1 введите название таблицы Ведомость,

3. Для сокрытия столбца Должность выделите любую его ячейку и вы­полните команду Формат/Столбец/Скрыть.

4. Выделите одну из ячеек списка и выполните команду Данные/Итоги...

5. В открывшемся диалоговом окне Промежуточные итоги выполните сле­дующие действия:

· Раскройте список в поле ввода При каждом изменении в и выбе­рите то поле, по которому группируются записи для получения итогов. Таким полем в поставленной задаче является поле «Отдел», т.к. все итоги подводятся по отделам.

• В поле ввода Операция по умолчанию будет выбрана функция Сумма

· В поле ввода Добавить итоги по выберите элементы, по которым подводятся итоги: Начислено, Удержано и К выдаче. При помощи полосы прокрутки раскрывающегося списка пройдите по всем его элементам и проверьте, что другие элементы не помечены;

· Оставите отметки для флажка Заменить текущие итоги и для флажка Итоги под данными

• Нажмите кнопку ОК.

6. На экране появятся строки с промежуточными итогами, для каждой группы записей и общий итог. Выровняйте колонку так, чтобы были видны все данные. Обратите внимание на кнопки, которые появились слева от таблицы. Кнопки с цифрами 1, 2 и 3 обеспечивают степень детализации показа данных в таблице: кнопка 3 — вся таблица с промежуточными итогами и общим итогом; кнопка 2 только итоги про­межуточные и общий; кнопка 1 — только общий итог. Той же цели служит и кнопки со знаками, « + » и « - ». Нажмите на каждую из кнопок посмотри­те, как свернулась структурнаясхема слева. После этого разверните весь список со всеми итогами, нажав Кнопку 3.

7. Добавьте в таблицу итоговые строки по вычислению среднего значе­ния по полю Начислено.

• Выполните команду Данные/Итоги...

• В поле ввода При каждом изменении в оставьте то, же значение Отдел.

· В поле ввода Операция выберите элемент Среднее;

• В поле ввода Добавить итоги по уберите отметку с элементов Удержано и
К выдаче.

• Для сохранения, старых промежуточных итогов уберите отметку с флажка Заменить текущие итоги. Это позволит добавить новые итоговые строки к уже имеющимся.

• Нажмите кнопку ОК.

На экране появились новые итоговые строки, добавилась кнопка с номером 4 и изменилась структурная схема детализации.

8. Для улучшения внешнего вида таблицы нужно выполнить ее форматирова­ние.

Можно воспользоваться выделением границ таблицы или Автоформатом. Для этого выполните команду Меню/Формат/Автоформат и выберите один из вариантов форматирования. В окне просмотра для каждого варианта можно увидеть; образец формата. Перед форматированием списка выделите одну из ячеек списка, тогда весь список будет выбран автоматически. Чтобы отформатировать только часть списка, ее следует выделить. Сохраните полученный результат.

Дополнительные сведения:

Чтобы убрать все итоги, выделите любую ячейку списка и выполните команду Данные/Итоги... и в диалоговом окне Промежуточные итоги на­жмите кнопку Убрать все.

Для отображения скрытых столбцов используйте команду Формат/Столбец/Показать.

Задание для самостоятельной работы.

Упражнение 4. На отдельном листе получите таблицу кадрового со­става предприятия с указанием общего числа сотрудников и их количества в каждом отделе.

Указания решению. Скопируйте рабочий лист Зарплата, назовите новый лист Кадры. Отсортируйте таблицу по полям Отдел, Должность и Таб. НомерСкройте столбцы Начислено, Удержано, К выдаче. Подсчитайте количество сотрудников в каждом отделе и в целом на пред­приятии, используя операцию Количество чисел в команде Промежуточные итоги (подумайте, какой столбец нужно указать в поле «При изменении в»).