2. ПРАКТИЧЕСКАЯ ЧАСТЬ

Компания «Страховщик» осуществляет страховую деятельность на территории России по видам полисов, представленных на рис. 1. Каждый полис имеет фиксированную цену.

Компания имеет свои филиалы в нескольких городах (рис. 2) и поощряет развитие каждого филиала, предоставляя определенный дисконт. Дисконт пересматривается ежемесячно по итогам общих сумм договоров по филиалам.

В конце каждого месяца составляется общий реестр договоров по всем филиалам (рис. 3).

1. Построить таблицы (рис. 1-3).

2. Организовать межтабличные связи для автоматического заполнения граф реестра (рис. 3): «Наименование филиала», «Наименование полиса», «Сумма полиса, руб.», «Сумма скидки по дисконту, руб.».

3. Организовать двумя способами расчет общей суммы полисов по филиалам:

1) подвести итоги в таблице реестра;

2) построить соответствующую сводную таблицу, предусмотрев возможность одновременно отслеживать итоги по виду полиса.

4. Построить гистограмму по данным сводной таблицы.

Виды страховых полисов
Код вида страхового полиса	Наименование страхового полиса	Сумма страхового полиса, руб
101	От несчастного случая	10000
102	От автокатастрофы	50000
103	От авиакатастрофы	60000
104	Медицинский	25000
105	Автомобильный	150000
106	Жилищный	500000

Рис. 1. Виды страховых полисов

Список филиалов компании "Страховщик"
Код филиала	Наименование филиала	Дисконтный процент с каждого полиса по филиалу, %
100	Московский	3
200	Тульский	2
300	Уфимский	1
400	Липецкий	2
500	Ростовский	3
600	Воронежский	2

Рис. 2. Список филиалов компании «Страховщик»

Реестр договоров
Код филиала	Наименование филиала	Код страхового полиса	Наименование полиса	Дата выдачи полиса	Сумма полиса, руб.	Сумма скидки по дисконту, руб.
100		101		11.11.2005
300		103		12.11.2005
200		105		13.11.2005
400		102		14.11.2005
600		106		11.11.2005
500		102		16.11.2005
200		105		17.11.2005
300		104		12.11.2005
300		102		19.11.2005
500		101		20.11.2005
400		106		11.11.2005
600		103		22.11.2005
100		105		13.11.2005
100		105		24.11.2005
600		103		25.11.2005

Рис. 3. Реестр договоров

2.1. Описание алгоритма решения задачи.

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

1. При запуске Excel открывается окно в котором будем создавать таблицу.

Рис 4. Исходная таблица MS Excel

2. Создаваемой таблице присвоить имя, выбрав пункт меню Файл / Сохранить как …

3. В появившемся окне ввести имя таблицы и нажать ''Сохранить''.

4. Создаем таблицу Страховщик и вводим в нее данные

Рис. 4. Таблица «Виды полисов»

Рис. 5. Таблица «Филиалы»

Рис. 6. Таблица «Реестр»

5. Вводим формулу расчета в клетку В3

=ВПР(A3;Филиалы!A$3:C$8;2)

Копируем ее в клетки В4:В17,

Вводим формулу расчета в клетку D3

=ВПР(C3;'Виды полисов'!A$3:C$8;2)

Копируем ее в клетки D4:D17,

Вводим формулу в клетку F3

=ВПР(C3;'Виды полисов'!A$3:C$8;3)

Копируем ее в клетки F4:F17

Вводим формулу в клетку G3

=ВПР(A3;Филиалы!A$3:C$8;3)*F3/100

Копируем ее в клетки G4:G17

6. Создаем таблицу «Свод»

Выберем пункт меню Данные / Сводная таблица

Рис. 7. Создание сводной таблицы.

Выберем вид создаваемого отчета – Сводная диаграмма (со сводной таблицей.

Рис. 8. Создание сводной диаграммы.

Перетащим поля Наименование филиала и Наименование полиса в поля категорий, а поле Сумма полиса в Элементы данных.

Получим сводную диаграмму:

Рис. 9. Сводная диаграмма

Также получим сводную таблицу:

Рис. 10. Сводная таблица

Для подведения итогов в таблице реестра предварительно отсортируем данные. Выделим таблицу и выберем пункт меню Данные / Сортировка.

Рис. 11. Сортировка данных

В качестве признака для сортировки выберем Код филиала.

После этого, выберем пункт меню Данные / Итоги

Рис. 12. Подведение итогов.

Получим:

Рис 13. Расчет итогов.

СПИСОК ЛИТЕРАТУРЫ

1. Гаевский А.Ю. Самоучитель работы на компьютере.: Учебн. пособ. – М.: ТЕХНОЛОДЖИ – 3000, 2003 – 528 с.: ил.

2. Курицкий Б.Я. Поиск оптимальных решений средствами Excel 7.0 – СПб.: BHV – Санкт-Петербург, 1997, - 384 с., ил.

3. Леонтьев В.П. Персональный компьютер. Карманный справочник. –М.: ОЛМА-ПРЕСС, 2004. -928.: ил.