Часть 1. Создание, редактирование, форматирование таблицы. Построение диаграмм
Создание таблицы
1. Создать таблицу расчета заработной платы сотрудников в соответствии с образцом (рис. 20). Ввести в таблицу следующие названия столбцов: Фамилия, Дата рождения, Возраст, Стаж работы, Оклад, Надбавка, К выдаче, ЕСН, Страховая часть пенсии, Накопительная часть пенсии. Ввести 10 записей.
Рис. 20
2. Ввести исходные данные в столбцы Фамилия, Дата рождения, Стаж работы, Оклад.
3. Значения остальных столбцов рассчитать по формулам:
- Возраст сотрудников рассчитать, как частное от деления разности между текущей датой и датой рождения на количество дней в году. При расчете использовать функции ЦЕЛОЕ или ОКРУГЛ и СЕГОДНЯ;
- Перед столбцом Надбавкавставить столбец % Надбавки. Процент надбавки зависит от стажа работы:
| Стаж, лет | |||||
| % Надбавки |
Рассчитать значения столбца % Надбавки с помощью функции ПРОСМОТР, составив отдельную таблицу надбавок;
- Надбавка = % Надбавки * Стаж;
- значения констант (% ПН (подоходный налог) = 13%, % ЕСН (единый социальный налог) = 28%, % страховой части пенсии = 10% и 14%, % накопительной части пенсии = 4% и 0%.) разместить за пределами таблицы. В формулах использовать абсолютные ссылки на эти ячейки;
- К выдаче = (Оклад + Надбавка) – (Оклад + Надбавка) * % ПН;
- ЕСН = К выдаче * % ЕСН;
- Страховая и накопительная часть пенсии рассчитываются как процент от К выдаче и зависят от возраста сотрудников:
| Страховая часть, % | ||
| Накопительная часть, % | ||
| Возраст, лет | <=38 | >38 |
Для расчета использовать функцию ЕСЛИ.
4. В соответствии с образцом (рис. 20) в выделенных ячейках рассчитать итоговые, максимальные, минимальные и средние значения. При вводе функций использовать различные средства: Мастер функций и Автосуммирование.
Форматирование таблицы
5. Отформатировать таблицу:
- для ячеек первой строки («шапки») таблицы длинные заголовки столбцов перенести в ячейке по словам;
- изменить шрифт первой строки на жирный цветной курсив, выполнить заливку, расположить текст по центру;
- столбец К выдаче выделить светло-коричневым фоном;
- присвоить ячейкам, содержащим денежные величины, денежный формат; для ячеек со значениями процентов – процентный формат;
- добавить строку для заголовка таблицы и ввести в заголовок текст «Расчет зарплаты сотрудников фирмы». Заголовок таблицы отцентрировать в пределах блока таблицы, назначить жирный цветной шрифт (размер 14 pt), увеличить высоту строки. После заголовка перед шапкой вставить пустую строку;
- расчертить таблицу линиями: внешнюю рамку таблицы – жирной синей линией, «шапку» таблицы подчеркнуть двойной красной линией, столбцы – тонкими черными линиями, строки линиями не разделять;
- в ячейку, содержащую название столбца Надбавка, вставить примечание «Надбавка зависит от стажа сотрудников»;
- ячейку с названием столбца ЕСН оформить выноской с текстом «Отчисления в бюджет»;
6. Присвоить листу имя Ведомость. Изменить цвет ярлычка листа.
Построение диаграмм
7. На отдельном листе создать цилиндрическую линейчатую диаграмму со столбцами в виде цилиндров. В качестве рядов использовать данные из столбцов Оклад и К выдаче, в качестве категорий – данные из столбца Фамилия. Присвоить заголовок диаграмме и названия осям. Добавить подписи данных для ряда К выдаче.
8. Добавить в диаграмму ряд Надбавка посредством добавления данных (пункт меню Диаграмма). Листу с диаграммой присвоить имя Диаграмма_Выплаты.
9. Создать внедренную круговую диаграмму, отображающую распределение итоговых значений по всем видам начислений. В качестве ряда использовать соответствующие данные из строки Итого. В качестве категорий использовать данные из «шапки» таблицы (ячейки Оклад, Надбавка, К выдаче, ЕСН). Включить в подписи данных имена категорий и доли. Ввести в диаграмму заголовок.
10. Отделить сектор ЕСН от остальных секторов диаграммы. Изменить его заливку, выбрав в качестве узора текстуру «Джинсовая ткань».
11. Освоить и применить следующие операции по форматированию элементов диаграмм:
12. применение цветов и узоров к маркерам данных (столбцам, линиям, секторам);
- создание рамок вокруг заголовков, названий осей и легенд; применение цвета к области внутри рамки;
- изменение шрифта, размера, стиля и выравнивания текстов;
- форматирование чисел в метках данных и категориях;
- изменение стиля линий и цвета сетки.
13. Отформатировать диаграммы по собственному усмотрению.
14. Сохранить файл под именем Фамилия_№ задания.xlsx.
Часть 2. Анализ и управление данными. Работа с массивами
Подбор параметра
15. Используя инструмент Подбор параметра, рассчитать, каков должен быть оклад у сотрудника Иванова, чтобы надбавка к окладу у него увеличилась на 500 р.
Таблица подстановки
16. При помощи таблицы подстановки данных с одной переменной проанализировать зависимость итоговой суммы столбца К выдаче от процента подоходного налога (подставить значения 11%, 12%, 13%, 14% соответственно). Исходные значения выделить цветом.
17. Сохранить файл под именем Фамилия_№ задания_2.xlsx.
