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

Часть 1. Создание, редактирование, форматирование таблицы. Построение диаграмм

Создание таблицы

1. Создать таблицу расчета заработной платы сотрудников музыкальной студии за месяц в соответствии с образцом (рис. 1). Ввести в таблицу следующие названия столбцов: Фамилия, Должность, Кол-во отработанных часов, Оклад, Надбавка, Премия, Всего начислено, Налог, К выдаче. Ввести 10 записей.

2. Ввести исходные данные в столбцы Фамилия, Должность, Кол-во отработанных часов.

3. Значения остальных столбцов рассчитать по формулам:

- значения столбца Оклад определить с помощью функции ПРОСМОТР, составив отдельную таблицу должностных окладов (см. дополнительную табл. 1);

- значения констант % надбавок (20, 10, 0%), % премии (5%) и % налога (13%) разместить за пределами таблицы (см. дополнительную табл 2). В формулах использовать абсолютные ссылки на эти ячейки;

- Надбавкарассчитывается как процент от Оклада и зависит от количества отработанных часов:

% надбавок
Кол-во часов	>160	>120.	<=120

Для расчета надбавки использовать функцию ЕСЛИ;

- Премия = (Оклад + Надбавка) * % премии;

- Всего начислено = Оклад + Надбавка + Премия;

- Налог = Всего начислено * % налога;

- К выдаче = Всего начислено – Налог.

Рис. 3

4. В соответствии с образцом в выделенных ячейках рассчитать итоговые, максимальные, минимальные и средние значения. При вводе функций использовать различные средства: Мастер функций и Автосуммирование.

 

Форматирование таблицы

5. Отформатировать таблицу:

- для ячеек первой строки («шапки») таблицы длинные заголовки столбцов перенести в ячейке по словам;

- изменить шрифт первой строки на жирный цветной курсив, выполнить заливку, расположить текст по центру;

- столбец К выдаче выделить светло-желтым фоном;

- присвоить ячейкам, содержащим денежные величины, денежный формат;

- добавить строку для заголовка таблицы и ввести в заголовок текст «Ведомость начисления заработной платы». Заголовок таблицы отцентрировать в пределах блока таблицы, назначить жирный цветной шрифт (размер 12 pt), увеличить высоту строки. После заголовка перед шапкой вставить пустую строку;

- отменить вывод на экран сетки таблицы. Расчертить таблицу линиями: внешнюю рамку таблицы – жирной синей линией, «шапку» таблицы – двойными красными линиями, столбцы – тонкими черными линиями, строки линиями не разделять;

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

6. Присвоить листу имя Ведомость.Изменить цвет ярлычка листа.

Построение диаграмм

7. На отдельном листе создать цилиндрическую линейчатую диаграмму со столбцами в виде цилиндров. В качестве рядов использовать данные из столбцов Всего начислено и К выдаче, в качестве категорий – данные из столбца Фамилия. Присвоить заголовок диаграмме и названия осям. Добавить подписи данных для ряда К выдаче.

8. Добавить в диаграмму ряд Оклад посредством добавления данных (пункт меню Диаграмма). Листу с диаграммой присвоить имя Диаграмма_Выплаты.

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

10. Освоить и применить следующие операции по форматированию элементов диаграмм:

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

- изменение формы маркеров в графиках;

- создание рамок вокруг заголовков, названий осей и легенд; применение цвета к области внутри рамки;

- изменение шрифта, размера, стиля и выравнивания текстов;

- форматирование чисел в метках данных и категориях;

- изменение стиля линий и цвета сетки.

11. Отформатировать диаграмму по собственному усмотрению. Добавить на лист подложку.

12. Сохранить файл под именем Фамилия_№ задания.xlsx.

 

Часть 2. Анализ и управление данными. Работа с массивами

Подбор параметра

13. Используя инструмент Подбор параметра, рассчитать, каков должен быть процент премии, чтобы итоговая сумма столбца К выдаче увеличилась на 10000 руб.

 

Таблица подстановки

14. При помощи таблицы подстановки данных с двумя переменными проанализировать зависимость итоговой суммы столбца К выдаче от процентов премии и налога (подставить значения 4%, 5%, 6%, 7%. и 11%, 12%, 13%, 14% соответственно). Исходные значения выделить цветом.

 

 

15. Сохранить файл под именем Фамилия_№ задания_2.xlsx.