Материал: Ивличева Н.А. Информиционные системы... Практикум Ч. 1
16
Замечание. После ввода функции вы можете увидеть сообщение об ошибке. Это происходит из-за того, что ни один элемент списка может быть еще не выбран.
Аналогично заполните формулами для обработки соответствующих списков ячейки диапазона С2:С8.
Вячейку С9 добавьте формулу, подсчитывающую сумму набранных по ан-
кете баллов.
Вячейке С10 разместим формулу для принятия решения: если сумма бал-
лов по анкете превышает 70% от максимально возможной суммы баллов, то принимается решение о выдаче кредита, в противном случае в кредите будет отказано. Это можно сделать с помощью функции ЕСЛИ() из категории Логические. Эта функция имеет следующий синтаксис:
ЕСЛИ ( логическое_выражение; значение_если_истина; значение_если_ложь)
В ячейку С9 вставьте логическую функцию ЕСЛИ(), которая проверяла бы истинность логического выражения: сумма набранных по анкете бал-
лов больше, чем 70% от максимально возможной суммы баллов.
Если это условие истинно, то функция должна выдавать значение “Решение
положительно”, если условие ложно – значение “Решение отрицательно”.
Замечание. При вводе логического выражения используйте ссылки на ячейки B36 и B37 скоринговой таблицы.
Обработка анкет
Заполните поля анкеты в соответствии с данными потенциального клиента и определите решение по запросу:
Кредит просит женатый мужчина 34 лет с высшим образованием, имеющий подтверждаемый стаж работы 8 лет. Потенциальный клиент имеет дочь 7 лет и сына 2 лет, проживает в собственной квартире. Клиент обращается за кредитом первый раз, своего автомобиля не имеет.
При правильном выполнении задания лист анкеты примет следующий вид:
17
Проверьте, повлияет ли на решение наличие у клиента автомобиля.
Подбором характеристик составьте четыре целевые группы, на которые рассчитана кредитная программа.
Изменение коэффициентов
Цель скоринговой таблицы – минимизировать риск невозврата кредита. Каждый случай невозврата должен рассматриваться отдельно, коэффициенты, соответствующие характеристикам должника, должны уменьшаться.
Рассмотрим возможный вариант организации пересчета.
Перейдите на Лист 3 и переименуйте его в Невозвраты.
В первой строке введите шапку для анкеты должника, как показано на рисунке 2.3.
Рисунок 2.3
Предположим, что кредит не вернул клиент, заполнивший анкету следующим образом:
Заполните эту анкету в своей книге.
Введите данные анкеты на лист Невозвраты. При вводе не допускайте
опечаток, не используйте лишних пробелов.
18
Рисунок 2.4 Обработать эти результаты можно с помощью макросов.
Для комфортной работы с макросами выполните команду меню Сервис – Макрос – Безопасность и установите средний уровень безопасности. Сохра-
ните изменения в документе. Закройте и вновь откройте документ, не отключая макросы.
Поместите курсор в одну из ячеек с введенными данными на листе Невозвраты (в ячейку строки 2 на рисунке 2.4).
Выполните команду меню Сервис – Макрос – Макросы, введите имя макроса Обработка и нажмите кнопку Создать. Откроется редактор Visual
Basic For Applications с готовыми заголовками макроса.
Текст программы обработки анкеты должен располагаться между заголовками макроса:
При работе с объектами в VBA используется точечная нотация:
Объект.Свойство
1. Считаем в переменную nom_str номер строки, в которой расположен
курсор:
nom_str = ActiveCell.Row
Здесь:
nom_str – имя переменной, которая будет содержать номер строки, ActiveCell – объект Excel, представляющий собой выделенную в на-
стоящий момент ячейку,
Row – свойство объекта ActiveCell, хранящее номер строки выделенной
ячейки.
2. Обработаем категорию Возраст.
2.1. Определим возраст клиента в выделенной строке и запишем его в переменную vozr:
vozr = Cells(nom_str, 1).Value
Здесь:
vozr – переменная, которая будет хранить возраст клиента,
Cells – объект Excel, представляющий собой ячейку. В скобках указыва-
ется номер строки и столбца, на пересечении которых она расположена,
Value – свойство объекта Cells, хранящее значение, введенное в ячейку.
19
2.2.Для пересчета коэффициентов надо перейти на лист Скоринго-
вая таблица. Это достигается командой
Sheets("Скоринговая таблица").Select
2.3.Теперь нужно на листе Скоринговая таблица просмотреть значе-
ния в строках второго столбца со второй по пятую (возможные значения возрастов) и сравнить их с переменной vozr. Для этого воспользуемся циклом с пара-
метром i, изменяющимся от двух до пяти:
For i = 2 To 5
…
Next i
Таким образом, переменная i будет содержать номер просматриваемой
строки.
Теперь, если значение возраста во втором столбце совпадает с переменной vozr, то соответствующее значение коэффициента в третьем столбце надо умень-
шить на 0,1 («вес» анкеты одного человека), а если не совпадает, то увеличить на 0,1:3 (так как в категории возраста еще три значения). Это достигается использованием в теле цикла условного оператора, который имеет синтаксис:
If условие Then конструкции_для_обработки_истинного_условия
Else
конструкции_для_обработки_ложного_условия
End If
В данном случае условный оператор имеет вид:
If Cells(i, 2).Value = vozr Then
Cells(i, 3).Value = Cells(i, 3).Value-0.1
Else
Cells(i, 3).Value = Cells(i, 3).Value+0.1/3 End if
С учетом сказанного текст макроса будет иметь вид:
Введите текст макроса в редакторе VBA.
20 |
|
3. Теперь обработаем следующую категорию – |
Состоит ли в браке. |
Для этого после строки vozr = Cells(nom_str, |
1).Value считаем в пе- |
ременную brak текущее значение признака из второго столбца анкеты: brak= Cells(nom_str, 2).Value
После оператора Next i добавим новый цикл для обработки соответст-
вующего диапазона (строки 7 и 8 на листе Скоринговая таблица):
For i = 7 To 8
If Cells(i, 2).Value = brak Then
Cells(i, 3).Value = Cells(i, 3).Value-0.1
Else
Cells(i, 3).Value = Cells(i, 3).Value+0.1 End if
Next i
Измените текст макроса в редакторе VBA.
Аналогично добавьте обработку остальных категорий, введя переменные stage (Стаж работы),
kredit (Наличие кредита в прошлом), auto (Наличие автомобиля),
deti (Количество несовершеннолетних детей), obraz (Образование),
mesto (Место проживания).
Закройте редактор макросов.
Для того чтобы выполнить макрос, создадим управляющую кнопку.
Для вставки элемента управления отобразите с помощью команды меню Вид – Панели инструментов – Формы панель работы с элементами формы.
Выберите на панели элемент управления Кнопку 
и на листе Невозвраты в ячейке J1 нарисуйте мышью кнопку. В появившемся окне Назначить макрос объекту выберите макрос Обработка и щелкните кнопку ОК – появится кнопка Кнопка1. Для смены имени щелкните на ней правой кнопкой мыши (не выделяя!) и выберите пункт меню Изменить текст. Назовите кнопку Рассчи-
тать.
Поместите курсор в любую ячейку второй строки листа Невозвраты и щелкните кнопку Рассчитать. Перейдите на лист Скоринговая таблица.
Обратите внимание на изменение коэффициентов.
Перейдите на лист Анкета. Обратите внимание на изменение решения о
выдаче кредита: