Расчет заработной платы

 

Функциональность MS Excel, используемая при решении задачи:

1) вставка строк и столбцов в спроектированную таблицу;

2) использование абсолютных ссылок;

3) использование функции ЕСЛИ;

4) редактирование формул;

5) использование собственных имен ячеек и диапазонов;

6) проверка содержимого ячеек в режиме Формулы;

7) проверка формул с помощью отображения зависимостей;

8) использование автоформатирования;

9) форматирование ячеек (параметры шрифта, выравнивание, обрамление, заливка, цвет чисел);

10) числовые форматы (процентный, денежный, задание пользовательского формата, количество десятичных цифр);

11) условное форматирование;

12) вставка примечаний и управление их размещением при печати;

13) сортировка таблицы;

14) установка параметров страницы (размер бумаги, ориентация страницы, поля, сквозные строки и столбцы, заголовки строк и столбцов);

15) предварительный просмотр и печать документа.

 

Постановка задачи

Спроектировать электронную таблицу, автоматизирующую расчет зарплаты сотрудников организации.

 

Порядок выполнения работы

1. Активизируйте для работы табличный процессор MS Excel. Создайте электронную таблицу расчета заработной платы, макет которой представлен табл. 2. Присвойте листу книги имя Таблица.

 

Таблица 2

Таблица расчета заработной платы

№ п/п	Фамилии сотрудников	Стаж, лет	Оклад, р.	Премия, р.	Налог, р.	Выплачено, р.	Итого по курсу, долл. США
	Иванов		200 000
	Сидоров		430 000
	Семенов		375 000
	Петров		520 000
	Варинов		240 000
	Лазарев		386 000
	Афанасьев		540 000
	Астафьев		390 000
	Ларионов		460 000
	Королев		210 000

 

2. Сохраните рабочую книгу в папке своей группы под именем Зарплата <Фамилия>. xls.

3. Вставьте над таблицей дополнительную строку, в которую поместите следующую справочную информацию: «Премия – 50 %», «Налог – 12 %», «Курс доллара – 2500 р.». Поместите эту информацию так, чтобы числовые данные (50 %, 12 %, 2500 р.) находились в отдельных ячейках. При этом числовые значения вводите в нужном формате (процентном, денежном).

4. Заполните ячейки столбцов Премия, Налог, Выплачено, Итого по курсу расчетными формулами с использованием абсолютных ссылок на ячейки со справочной информацией. При этом исходить нужно из следующего:

а) премия составляет 50 % от оклада;

б) налог составляет 12 % от оклада и премии;

в) значения графы Выплачено вычисляются как сумма оклада и премии за вычетом налога;

г) значения графы Итого по курсу вычисляются как частное от деления значения из графы Выплачено на курс доллара США (1 долл. США равен 2150 бел. р.).

5. Добавьте в таблицу следующие столбцы:

а) Должность (перед столбцом Оклад);

б) Надбавка за стаж, р. (перед столбцом Премия);

в) Выплаты в фонды, р. (перед столбцом Выплачено);

г) Колебания в выплатах, % (после столбца Итого по курсу, долл. США).

6. Заполните столбец Должность информацией (названия должностей выбирайте по своему усмотрению).

7. Рассчитайте надбавку за стаж в соответствующем столбце с использованием функции ЕСЛИ по следующим условиям:

а) за стаж работы больший или равный 20 лет надбавка составляет 20 % от оклада;

б) за стаж работы больший или равный 15 лет – 15 % от оклада;

в) за стаж работы больший или равный 10 лет – 10 % от оклада.

8. Заполните ячейки столбца Выплаты в фонды расчетными формулами, при этом считайте, что величина выплат для каждого сотрудника составляет 1 % от его оклада.

9. С учетом добавления столбцов Надбавка за стаж, р. и Выплаты в фонды, р. отредактируйте формулы в столбцах Налог (12 % от суммы оклада, премии и надбавки за стаж) и Выплачено (сумма оклада, премии и надбавки за стаж за вычетом выплаты в фонды и налога).

10. В столбце Фамилия под фамилиями введите четыре новые строки Сумма, Максимальные, Минимальные, Средние и произведите выравнивание этих названий в ячейках по правому краю. С помощью встроенных функций СУММ, МАКС, МИН, СРЗНАЧ рассчитайте сумму, максимальное, минимальное и среднее значение во всех столбцах, содержащих денежные значения. При этом используйте операцию автозаполнения.

11. В столбце Колебания в выплатах, % рассчитайте процент выплаты зарплаты для каждого сотрудника по отношению к максимальному значению выданной зарплаты, при этом используйте не абсолютную ссылку на ячейку с максимальным значением, а собственное имя этой ячейки.

Для этого выполните следующее:

1) присвойте имя МаксВыплата ячейке, содержащей максимальное значение по столбцу Выплачено (используйте поле имени или команд меню Вставка / Имя / Присвоить);

2) в формулах столбца Колебания в выплатах, % используйте имя ячейки МаксВыплата.

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

13. Установите режим отображения в ячейках формул, а не их значений (меню Сервис / Параметры / вкладка Вид флажок Формулы) и проверьте формулы в ячейках расчетной таблицы. Вернитесь к прежнему режиму.

14. Проверьте формулы в ячейках столбца Надбавка за стаж, р.
с использованием команд меню Сервис / Зависимости / Влияющие ячейки и Сервис / Зависимости / Зависимые ячейки. Уберите с экрана все стрелки зависимостей.

15. Добавьте перед таблицей две пустые строки и введите в ячейку А1 заголовок таблицы Расчет зарплаты. Примените к заголовку следующие параметры форматирования: выравнивание по центру всей таблицы, размер шрифта – 18 пунктов, начертание – полужирный курсив.

16. Отформатируйте таблицу с помощью команды меню Формат / Автоформат. Отмените результаты форматирования. Отформатируйте таблицу с использованием панели инструментов Форматирование и команды меню Формат / Ячейки... следующим образом:

· выравнивание заголовков столбцов выполните по центру ячеек, а числовой информации – по правому краю;

· установите для ячеек, содержащих список фамилий сотрудников, следующие параметры шрифта: гарнитура шрифта – Times New Roman Cyr, начертание – полужирное;

· для числовых значений столбцов Оклад, Надбавка за стаж, Премия, Налоги, Выплаты в фонды, Выплачено задайте числовой формат – денежный без десятичных цифр;

· для числовых значений столбца Итого по курсу, долл. США задайте числовой формат – денежный с соответствующим обозначением денежной единицы ($) перед числом, а также установите разрядность чисел с точностью до двух знаков после запятой;

· для чисел столбца Колебания в выплатах задайте числовой формат – процентный с одной десятичной цифрой;

· задайте цвет заливки для различных областей таблицы (ячейки с исходными данными, ячейки с расчетными формулами, строки с итоговыми значениями), при этом цвет заливки выберите самостоятельно;

· выполните обрамление таблицы.

17. С использованием условного форматирования отформатируйте ячейки с числовыми значениями столбца Надбавка за стаж, р. следующим образом:

· выделите числовые значения, равные нулю, красным цветом;

· числовые значения от 0 до 50000 – синим цветом;

· числовые значения, большие либо равные 50000, – зеленым цветом.

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

18. Создайте примечание (меню Вставка / Примечание) для ячейки, содержащей заголовок Надбавка за стаж, с кратким описанием алгоритма вычисления значений в данном столбце. Установите режим одновременного вывода на экран индикатора и примечания (меню Сервис / Параметры / вкладка Вид). Расположите поле примечания под расчетной таблицей (Файл / Параметры страницы / вкладка Лист).

19. Отсортируйте информацию о сотрудниках в таблице по столбцу Фамилия в алфавитном порядке.

20. Установите следующие параметры страницы:

· формат бумаги – А4, ориентация страницы – альбомная;

· верхнее и нижнее поля – 1,5 см, левое и правое – 2 см;

· горизонтальное и вертикальное центрирование таблицы на странице печати;

· в левой части верхнего колонтитула укажите фамилию, имя и отчество; в правой части – текущую дату и текущее время; в центре нижнего колонтитула – номер страницы;

· установите режим вывода на печать примечаний (меню Файл / Параметры страницы /вкладка Лист /флажок Печатать примечания);

· если таблица не помещается на странице, то задайте сквозные строки (строку заголовков столбцов таблицы) либо сквозные столбы (столбец с фамилиями сотрудников).

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

22. Проиллюстрируйте данные столбцов Оклад и Выплачено с помощью гистограммы. В качестве меток по оси Х установите фамилии, в качестве легенды – названия столбцов, вставьте заголовки осей Х и Y, заголовок диаграммы, добавьте метки значений. Диаграмму в рабочей книге поместите на отдельном листе Диаграммы. Переключитесь к типу диаграммы С областями. Добавьте на область диаграммы текстовое поле или выноску с краткой информацией об авторе.

23. Проиллюстрируйте с помощью объемной круговой диаграммы данные столбца Итого по курсу. Добавьте метки к секторам с указанием соответствующего числового значения его заработной платы в долларах США. В качестве легенды используйте фамилии сотрудников. Поместите диаграмму на лист Диаграммы.

24. Постройте диаграмму следующего типа: нестандартная с двумя осями по данным столбцов Стаж и Надбавка за стаж (стаж отобразите в виде гистограммы, надбавку за стаж – в виде графика). Смешанная диаграмма должна содержать название, подписи всех осей и легенду. Поместите диаграмму на лист Диаграммы.

25. В режиме предварительного просмотра проверьте расположение диаграмм на странице, добейтесь, чтобы все диаграммы уместились на одной странице печати.

26. Распечатайте оба листа книги.