Часть 2. Анализ и управление данными. Работа с массивами. Часть 1. Создание, редактирование, форматирование таблицы

Часть 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.