Табличные данные реестра договоров

Содержание

Введение………………………………………………………………………...…3

Основная часть……………………………………………………………...…..4-8

Заключение………………………………………………………………….……9

Список используемой литературы…………………………………………….10

Введение

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

Цель работы: разработка итогового документа в виде «Реестра договоров».

Технология решения задачи основана на применении программы MS Microsoft Excel, использовании межтабличных связей для автоматического заполнения таблиц.

Условие

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

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

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

1. Построить таблицы 1,2,3.

2. Организовать межтабличные связи для автоматического запол­нения документа «Реестр договоров» при помощи функций ВПР или ПРОСМОТР.

3. Произвести расчет суммы полисов по филиалам.

4. Построить и проанализировать графический отчет по получен­ным результатам.

Таблица 1

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

Код вида стра­хового полиса	Наименование страхового полиса	Сумма страхового полиса, руб.
	От несчастного случая	20 000
	От автокатастрофы	60 000
	От авиакатастрофы	50 000
	Медицинский	30 000
	Автомобильный	90 000
	Жилищный	700 000

Таблица 2

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

Код фили­ала	Наименование филиала	Дисконтный процент с каждого полиса по филиалу
	Московский	3%
	Тульский	2%
	Уфимский	1%
	Липецкий	2%
	Ростовский	3%
	Воронежский	2%

Таблица 3

Табличные данные реестра договоров

Код филиала	Наименование филиала	Код стра­хового полиса	Наименование полиса	Дата выдачи полиса	Сумма полиса, руб.	Сумма скидки по дис­конту, руб.
				11.12.10
				12.11.10
				13.11.10
				14.11.10
				11.11.10
				16.11.10
				17.11.10
				12.11.10
				19.11.10
				20.11.10

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

1. Запустить табличный процессор MS Excel.

2. Лист 1 переименовать в лист с названием «Полисы».

3. На рабочем листе «Полисы» MS Excel создать таблицу «Виды страховых полисов».

4. Заполнить таблицу «Виды страховых полисов» исходными данными (Таблица 1).

5. Лист 2 переименовать в «Скидки».

6. На рабочем листе «Скидки» MS Excel создать таблицу «Список филиалов компании «Страховщик»».

7. Заполнить таблицу «Список филиалов компании «Страховщик» » исходными данными (Таблица 2).

8. Лист 3 переименовать в лист с названием «Реестр».

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

10. Заполнить таблицу «Табличные данные реестра договоров» исходными данными (Таблица 3).

11. Заполнить графу « Наименование филиала»таблицы «Табличные данные реестра договоров », находящейся на листе «Реестр » следующим образом:

а) Занести в ячейку В3 формулу:

= ВПР(A3;скидки;2)

Я выполняю функцию ВПР в соответствии с [5]

б) Размножить введенную в ячейку B3 формулу для остальных ячеек (с B3 по B12) данной графы.

Как следствие будет выполнен цикл, управляющий параметром которого является номер строки.

12. Заполнить графу «Наименование полиса» таблицы «Виды страховых полисов», находящейся на листе «Полисы»следующим образом:

а) Занести в ячейку D3 формулу:

=ВПР(C3;полисы;2)

б) Размножить введенную в ячейку D3 формулу для остальных ячеек (с D3 поD12) данной графы.

13. Заполнить графу «Сумма полиса, руб.» таблицы «Виды страховых полисов», находящейся на листе «Полисы», следующим образом:

а) Занести в ячейку F3 формулу:

=ВПР(C3;полисы;3)

б) Размножить введенную в ячейку F3 формулу для остальных ячеек (с F3 по F17) данной графы.

14. Заполнить графу «Сумма скидки по дисконту, руб.» таблицы «Список филиалов компании «Страховщик»», находящейся на листе «Скидки», следующим образом:

а) Занести в ячейку G3 следующую формулу:

=ВПР(Реестр!A3;скидки;3)*F3

б) Размножить введенную в ячейку G3 формулу для остальных ячеек (с G3 по G12) данной графы.

15. В итоге должна получиться таблица следующего вида (Таблица 4):

Таблица 4

Табличные данные реестра договоров
Код филиала	Наименование филиала	Код стра­хового полиса	Наименование полиса	Дата выдачи полиса	Сумма полиса, руб.	Сумма скидки по дисконту, руб.
	Московский		От несчастного случая	11.12.2010
	Уфимский		От авиакатастрофы	12.11.2010
	Тульский		Автомобильный	13.11.2010
	Липецкий		От автокатастрофы	14.11.2010
	Воронежский		Жилищный	11.11.2010
	Ростовский		От автокатастрофы	16.11.2010
	Тульский		Автомобильный	17.11.2010
	Уфимский		Медицинский	12.11.2010
	Уфимский		От автокатастрофы	19.11.2010
	Ростовский		От несчастного случая	20.11.2010

16. По данным таблицы 4 построим график:

Рис.1 – Диаграмма общих сумм договоров по филиалам и сумм скидки по дисконту.(Ряд1 – сумма полиса, Ряд2 – сумма скидки по дисконту, Ось X – код страхового полиса)

На рис. 1 видно, что наибольшая сумма полиса приходится на договор со 106 кодом страхового полиса(Воронежский филиал, сумма - 700000 руб.), а наименьшая - на договор со 101 кодом страхового полиса(Ростовский филиал, сумма – 20000 руб.). Наибольшую скидку по дисконту имеет Воронежский филиал с кодом страхового полиса 106 в размере 14000 руб., а наименьшую – Уфимский филиал с кодом страхового полиса 104 в размере 300 руб.