Калькулятор расчета страхового тарифа по ОСАГО.

Первый урок по калькуляторам и расчетам в Microsoft Excel будет посвящен созданию расчета страхового платежа по ОСАГО.  Данный калькулятор может быть использован страховыми агентами и сотрудниками продажного подразделения.

Шаг 1. Создаем базу

На новой странице (Коэффициенты) создаем таблицы с параметрами, условиями и значениями коэффициентов. Все коэффициенты действенны на 29.10.2014.

Шаг 2. Создаем таблицу с параметрами ОСАГО

Создаем новую страницу «Расчет ОСАГО», на которой создаем таблицу с параметрами страхования:

• Место регистрации автомобиля;
• Тип транспортного средства;
• Стаж вождения;
• Статус страхователя;
• Срок страхования.

А также список условий для каждого параметра.

Шаг 3. Создаем выпадающий список значений параметра

Создаем значения в виде выпадающего списка для каждого параметра:

1. Переходим на вкладку «Данные»;
2. Устанавливаем курсор в месте, где должен быть выпадающий список (C2);
3. Выбираем в вкладке «Работа с данными» функцию «Проверка данных»;
4. В открывшемся окне на вкладке «Параметры», выбираем «Тип данных» «Список»;
5. В строке «Источник» указываем диапазон данных (Все условия параметра);
6. Нажимаем «ОК»

Итак, у нас создана таблица с параметрами и условиями страхования ОСАГО.

Шаг 4. Создаем таблицу критериев для отбора коэффициента значения.

Для того чтобы извлекать коэффициенты, необходимые для расчета страхового платежа ОСАГО, по каждому параметру с выбранным значением, будем использовать функцию Excel «БИЗВЛЕЧЬ». Для этого  подготовим массив с критериями отбора отдельной таблицей, где сверху будет указан параметр, а снизу будет подтягиваться выбранное значение.

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

• Устанавливаем курсор, где необходимо установить значение;
• Нажимаем клавишу «=» на клавиатуре;
• Указываем путь к значению (выпадающий список).

Таким образом, заполняем всю таблицу с критериями.

Шаг 5. Извлекаем коэффициенты значений

Для того чтобы извлечь коэффициент для каждого значения, будем использовать функцию «БИЗВЛЕЧЬ».

1. Устанавливаем курсор на ячейку в которое хотим извлечь коэффициент;
2. Нажимаем кнопку «Вставить функцию»;
3. Выбираем Категорию «Работа с базой данных»;
4. Выбираем функцию «БИЗВЛЕЧЬ»;
5. В открывшемся окне:
   1. В строку «База_данных» вносим диапазон таблицы, вместе с заголовками, из которой хотим извлечь коэффициент;
   2. В строке «Поле» указываем ячейку заголовка таблицы по которому будет производится поиск. В нашем случае «Коэффициент»;
   3. В строку «Критерий» указываем критерий поиска (Заголовок столбца и значение). Например, Заголовок столбца «Место регистрации автомобиля», а значение «Житомир».

Таким образом, извлекаем для каждого выбранного значения параметра свой коэффициент.

Шаг 6. Расчет страхового платежа.

После того, как были извлечены для каждого значения коэффициент, рассчитываем страховой платеж по ОСАГО путем умножения базового страхового платежа 180 грн. на все коэффициенты.

Шаг 7. Оформление калькулятора ОСАГО

Скрываем все не нужные столбцы и строки, а также «листы», чтобы не вводили в заблуждение. Оформляем таблицу по собственному желанию и вкусу. Выделяя заголовки, параметры и значения шрифтом или цветом.

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

Расчет страхового платежа ОСАГО готов к использованию. Рассчитать ОСАГО становится легко и просто за какие-то полминуты драгоценного времени.