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

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

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

1. Создать отчет о деятельности страховых агентов в соответствии с образцом (рис. 9). Ввести в таблицу следующие названия столбцов: № п/п, Фамилия Имя, Январь, Февраль, Март, 1 кв., Процент вознаграждения, Комиссионное вознаграждение (руб.), Ранг агента, Процентная норма ранга.Ввести 10 записей.

2. В столбец № п/п ввести ряд чисел с использованием команды Правка-Заполнить.

3. Для заполнения столбца Фамилия Имяиспользовать дополнительную таб.1 и оператор конкатенации.

Рис. 9

4. Названия столбцов Январь, Февраль, Мартввести, используя автозаполнение. Ввести в столбцыпроизвольные значения количества страховых сделок в диапазоне от 50 до 450.

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

- 1 кв. =Январь + Февраль + Март;

- столбец Процент вознаграждения заполнить значениями, используя дополнительную таб.2 и функцию ПРОСМОТР[1];

- Комиссионное вознаграждение = Цена сделки *1 кв. * Процент вознаграждения.Значение цены сделки (50 р.) ввести за пределами таблицы.

- в столбцеРанг агентарассчитать ранг каждого страхового агента по значениям комиссионного вознаграждения. Использовать функцию РАНГ;

- в столбцеПроцентная норма рангарассчитать процентную норму ранга по значениям комиссионного вознаграждения, используя функцию ПРОЦЕНТРАНГ;

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

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

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

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

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

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

- значения столбца Ранг агента выделить золотистым фоном;

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

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

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

- добавить примечания к ячейкам с фамилиями агентов, имеющих максимальное или минимальное значение комиссионного вознаграждения.

8. Присвоить листу имя Отчет.

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

9. Создать внедренную гистограмму. В качестве ряда использовать данные из столбца Комиссионное вознаграждение,в качестве категорий – данные из столбца Фамилия Имя.

10. Добавить в гистограмму ряд Ранг агента перетаскиванием мышью. Изобразить ряд Ранг агента в виде графика с маркерами, используя вспомогательную ось. Изменить форму маркеров у графика. Вывести значение ранга для точек графика с наибольшим и наименьшим значениями. Присвоить заголовок диаграмме и названия осям.

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

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

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

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

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

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

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

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

13. Отформатировать диаграмму по собственному усмотрению. Добавить на лист узор подложки. Листу с диаграммой присвоить имя Диаграмма_Ранг.Изменить цвет ярлычка листа.

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

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

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

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