Создание скоринговой модели в ms excel
Обновлено: 04.07.2024
На протяжении всей истории банковского дела кредиторы всегда оценивали тех, кто хотел взять деньги в долг. До начала прошлого века, когда рынок был достаточно узок, этим занимались сами банкиры. Затем появились кредитные менеджеры, которые оценивали риск невозврата кредитов заемщиками.
От «визуального» скоринга к скоринговой модели
В тридцатые годы прошлого века, во время «великой депрессии», в США были предприняты первые попытки создавать субъективные системы кредитного скоринга. Ситуация в экономике была не очень хорошая, а банкам, с одной стороны, хотелось выдать как можно больше займов, а с другой — минимизировать долю «плохих» долгов в своих кредитных портфелях. Эти системы строились на визуальной оценке потенциальных заемщиков кредитными менеджерами.Возможно, что и дальше в США активно развивалось бы направление «ручного» скоринга и профессия кредитного менеджера была бы одной из самых высокооплачиваемых в финансовой отрасли, если бы Америка не вступила во Вторую мировую войну. Большинство кредитных аналитиков было призвано в действующую армию, а их место заняли новички. Благодаря военным поставкам экономика страны пошла на подъем и возник повышенный спрос на потребительские кредиты.
Если в ситуации с БКИ все более или менее прозрачно, то с кредитными брокерами много темных мест
Перед увольнением руководство большинства американских банков заставило кредитных аналитиков написать свод правил, которыми следовало руководствоваться при принятии решения о выдаче кредита, чтобы оценку заемщика мог проводить неспециалист. Правда, вскоре выяснилось, что эти правила быстро устаревали и не учитывали всех изменений в экономике и жизни потенциальных заемщиков. Да и кредитных аналитиков требовалось слишком много.
В 1940 году Национальное бюро экономических исследований США опубликовало работу Ральфа Янга «Personal Finance Companies and Their Credit Practices», где впервые были высказаны идеи скоринга. Правда, сейчас об этой публикации знает лишь узкий круг специалистов, а «изобретателем» скоринга считается Дэвид Дюран. В большинстве публикаций, посвященных истории кредитного скоринга, упоминается это имя.
ИЗ БОТАНИКИ В БАНКОВСКИЙ СЕКТОР С точки зрения статистики кредитный скоринг — это метод классификации всей интересующей нас популяции на две группы: «хороших» и «плохих» должников. Нам неизвестна характеристика, по которой происходит распределение по группам, но зато известны другие характеристики, связанные с интересующей нас популяцией.
В статистике идея классификации популяции на группы на примере растений (вредные и полезные) была разработаны в 1936 году немецким биохимиком и лауреатом Нобелевской премии (1930 год) Хансом Фишером.
В январе 1941 года Национальное бюро экономических исследований США опубликовало исследование Дэвида Дюрана «Элементы риска потребительского кредитования в рассрочку» (Risk Elements in Consumer Installment Financing). Автор адаптировал разработанную Хансом Фишером методику для классификации кредитов на «плохие» и «хорошие». Правда, отдельные западные авторы указывают на то, что огромное влияние на Дэвида Дюрана оказали работы английского статистика, биолога-эволюциониста и генетика сэра Рональда Эйлмера Фишера, который внес колоссальный вклад в развитие современной прикладной математической статистики.
Методика Дэвида Дюрана состояла в следующем: он выделил группы факторов и их весовые значения, позволяющие определить степень кредитного риска, и установил границу выдачи ссуды как 1,25 и более.
Баллы, присваиваемые заемщикам в зависимости от конкретных значений этих факторов, были следующими:
1. Возраст: 0,1 балла за каждый год свыше 20 лет (максимум — 0,30).
2. Пол: женский (0,40), мужской (0).
3. Срок проживания в регионе: 0,042 за каждый год
4. Профессия: 0,55 за профессию с низким риском, 0 за профессию с высоким риском, 0,16 — другие профессии.
5. Работа: 0,21 на предприятиях общественной отрасли, 0 — другие.
6. Срок занятости: 0,059 за каждый год работы на данном предприятии.
7. Финансовые показатели: 0,45 за наличие банковского счета, 0,35 за наличие недвижимости, 0,19 — за наличие полиса по страхованию.
Банки оперативно начали внедрять предложенную Дэвидом Дюраном балльную систему оценки заемщиков, тем более что она не требовала от персонала высокой квалификации. Нужно было лишь провести опрос клиента, расставить баллы, а потом суммировать их.
В 1956 году на американском финансовом рынке произошло событие, которое кардинально изменило ситуацию в сфере кредитного скоринга. Американцы — инженер Бил Файр и математик Эрл Айзек, которые трудились в Стэнфордском исследовательском институте, придумали первую кредитную скоринговую модель. Партнеры разработали математический алгоритм, вычисляющий уровень кредитоспособности заемщика в цифровом выражении. Другими словами, алгоритм позволяет просчитывать кредитные риски в виде трехзначного числа, которое является кредитным рейтингом. Они организовали компанию Fair, Isaac and Company (в 2003 году она была переименована в Fair Isaac Corporation, а в 2009 году — FICO).
В 1963 году, спустя пять лет после того, как Fair, Isaac and Company продала первую скоринговую модель, в «Журнале американского общества статистиков» была опубликована статья, где утверждалось, что «система балльного рейтинга не получила широкого распространения». Спустя полвека можно утверждать, что основная причина этого — дефицит мощных компьютеров и соответствующего ПО. Банки бы и внедряли у себя системы скоринга, но вот стоимость необходимого для этого оборудования и острейший дефицит персонала для его обслуживания значительно превышали экономический эффект от использования автоматизированной системы оценки заемщиков.
Этот период продлился недолго. В семидесятые годы прошлого века, с одной стороны, началось бурное развитие средств вычислительной техники, а с другой — бум кредитования. И тогда скоринговые системы начало внедрять у себя большинство банков. Более того, некоторые из них разработали собственные системы, не прибегая к помощи сторонних компаний.
Когда в середине девяностых годов в России началось постепенное внедрение скоринговых систем, то отечественные банки столкнулись с дилеммой: разрабатывать их самостоятельно или покупать у западных производителей. Спустя 15 лет появился и третий вариант: отдать скоринг на аутсорсинг.
Мы строили, строили и…
Теоретически создать скоринговую систему сотрудники банка могут самостоятельно, используя «подручные» средства типа программ из набора Microsoft Office. Как рассказал директор по развитию бизнеса компании разработчика, «построить скоринговую модель можно и с помощью Excel, что и делают специалисты в ряде банков, но процесс этот долгий и хлопотный, требует специальных знаний, не говоря уже о дальнейшем мониторинге таких моделей и их корректировке».Правда, в последние годы Excel используется крайне редко, обычно применяется специализированное или универсально-аналитическое (различные математические и статистические пакеты) ПО.
Первое разработано и используется исключительно для создания скоринговых карт и моделей. Решить с помощью него какие-либо другие задачи в области статистики крайне сложно. В нем собраны различные «инструменты» (построения моделей, специализированные отчеты, диаграммы, различные методы построения моделей и т.п.), которые нужны для скоринга.
Российские разработчики ПО занимают на отечественном рынке довольно скромное место
С помощью второго теоретически тоже можно создать скоринговую модель, но на практике в большинстве случаев процесс этот будет очень трудоемким и потребует много времени. Например, для построения кривой финансовой эффективности на кредитном портфеле в скоринговом ПО нужно, условно говоря, нажать всего лишь две кнопки. А при использовании универсального ПО кроме того, что придется создавать весь алгоритм анализа, так нужно еще озаботиться созданием графического представления результатов.
Хотя основное различие между универсальным и специализированным ПО — в идеологии. Специализированный «софт» оперирует в основном бизнес-понятиями. Пользователям — риск-менеджерам и кредитным аналитикам — требуется лишь базовое понимание основ математики. А вот для работы с универсальным ПО требуются специалисты с серьезной математической подготовкой и навыками обращения с соответствующим «софтом».
У универсального ПО есть одно важное преимущество перед специализированным «софтом». Банк с помощью первого может создать скоринговую модель, в которой будут использоваться любые алгоритмы, а не только те, что присутствуют в специализированном ПО.
Довериться профессионалам
Хотя можно доверить разработку и внедрение скоринговой модели специализированной компании. Степень участия банка в этом случае различна, начиная от совместного «творчества» и заканчивая изготовлением системы «под ключ». Условно игроков на этом сегменте рынка можно разделить на две категории: представительства западных разработчиков скорингового ПО, которые занимаются продажей и обслуживанием продукции «родительских» структур, и компании-интеграторы. Последние с помощью западного и собственного ПО создают скоринговые модели «на заказ».Скоринг на аутсорсинге
Есть и третий вариант — использовать сервис скоринга, который предлагают БКИ и кредитные брокеры. Хотя на рынке также есть предложения IT-компаний, которые готовы на своей «площадке» создать и эксплуатировать скоринговую модель банка. В этом случае кредитному менеджеру нужно будет лишь отправлять запрос поставщику данного сервиса и получать от него ответ: выдавать кредит или нет. Разумеется, окончательное решение остается за сотрудником кредитной организации.Одно из важных преимуществ «скоринга на аутсорсинеге» — значительная экономия денег. Не нужно тратиться на покупку специализированного ПО, оплату труда специалистов и т.п.
Другое важное преимущество «скоринга на аутсорсинге» — значительное сокращение временных затрат. Вне зависимости от того, решит ли банк самостоятельно создавать скоринговую модель или поручит это специализированной компании, этот процесс может занять от недели до полугода. По словам разработчиков: «Срок разработки скоринговой модели зависит от нескольких факторов: уровня специалиста, который этим занимается; инструментария, с помощью которого разрабатывается скоринговая карта; количества и качества данных; желаемого результата».
Правда, у варианта «скоринг на аутсорсинге» есть один важный недостаток. Если в ситуации с БКИ все более или менее «прозрачно», то с кредитными брокерами и теми, кто предлагает взять на себя обязанности по созданию и функционированию скоринговой системы много «темных» мест.
Во-первых, бюро используют ПО от мировых лидеров. Например, у НБКИ это FICO, а у ОКБ — Experian Decision Analytics. Данные технологии уже несколько лет применяются во всем мире. Более того, речь идет не просто об использование данного ПО БКИ, а именно о совместных проектах с производителями. Генеральный директор Experian DA Даниэль Зеленский сообщил, что «решение NBSM бесперебойно работает на российском рынке уже более семи лет. Он внедрен в крупнейших российских банках (топ-20), и вот теперь это зарекомендованное в России решение выходит в виде готового коробочного продукта. Отработанные кредитные стратегии, лучшие модели принятия решений теперь станут доступны всем финансовым организациям и помогут использовать многолетний опыт сильнейших российских и зарубежных банков».
Во-вторых, для построения скоринговых моделей у БКИ накоплен достаточно большой объем статистики. Например, у ОКБ — более 500 банков. А «Эквифакс Кредит Сервисиз», по словам его генерального директора Сергея Лагуткина, «использовал большую выборку реальных данных из нашей базы — это миллионы кредитных историй».
По остальным участникам рынка «внешнего скоринга» не все так «прозрачно». Во-первых, непонятно насколько правильно и эффективно они используют специализированное ПО. Во-вторых, вопрос по объему и качеству накопленной статистики. В каждом конкретном случае нужно разбираться отдельно.
Все брэнды в гости к нам
Вне зависимости от того, будет банк сам создавать свою скоринговую модель, доверит решение этой задачи специализированной компании или будет использовать внешний скоринг, высока вероятность того, что ему придется воспользоваться услугами одного из мировых лидеров. Российские разработчики ПО занимают на отечественном рынке довольно скромное место. Маловероятно, что в ближайшие годы ситуация измениться.По мнению разработчиков: «так происходит из-за того, что скоринг в России появился сравнительно недавно, в то время как международные компании имеют опыт работы на разных рынках и реализуют в своих продуктах функциональность, запрашиваемую западными специалистами, давно работающими в этой сфере на профессиональном уровне».
Другая причина, ее озвучил сотрудник одной из западных компаний — лидера на рынке специализированного ПО, «мировые лидеры имеют многолетний опыт работы с ведущими банками мира и предлагают в России продукты с учетом best practice внедрений. Российские разработчики фактически находятся на этапе стартапа, так как имеют относительно небольшой опыт работы на локальном рынке, который пока находится в стадии формирования».
Евгений Штеманетян, директор в России и СНГ FICO
В 1956 году американский инженер Бил Файр и математик Эрл Айзек впервые предложили использовать систему числовых рейтингов, рассчитываемых на основе исторической информации о заемщике для предсказания его дефолта. Метод получил название скоринг, и сегодня все розничные кредиты и значительная часть займов малому и среднему бизнесу проходят численное рейтингование на этапе рассмотрения заявки.
Сами изобретатели скоринга основали компанию Fair Isaac Company, которая в настоящее время носит название FICO. Именно решения этой компании наиболее популярны в США и многих европейских странах. Так, 9 из 10 крупнейших банков Великобритании и 97 из 100 в США используют технологии FICO в управлении рисками кредитования. А простые заемщики в массовом порядке контролируют свое кредитное качество с помощью сервиса «myFICO», который позволяет им узнать свой скоринговый балл и понять перспективы получения того или иного займа на нужных им условиях.
В России массовое использование скорингов в розничном кредитовании также стало уже повсеместной практикой. Российские банки активно используют и внешние скоринги FICO и собственные скоринговые карты, разработанные в том числе и с помощью американских консультантов.
Модели реализации кредитного
скоринга
Собственные скоринговые
системы
Готовое IT-решение
Аутсорсинг
• SAS Credit Scoring,
• EGAR Scoring,
• Transact SM (ExperianScorex),
• K4Loans (KXEN),
• Experian Decision
Analytics,
• Clementine (SPSS),
• Диасофт,
• Deductor (Basegroup
Labs).
4. Задача
5. Исходные данные
Факторы кредитного скоринга:
1. Возраст: 0,01 балла за каждый год свыше 20 лет (максимум 0,3 балла)
2. Пол: женщина – 0,4 балла; мужчина – 0 баллов
3. Срок проживания в данной местности – 0,042 балла за каждый год (максимум 0,42 балла)
4. Профессия: 0,55 балла за профессию с низким риском; 0 баллов – за профессию с высоким
риском; 0,16 баллов – для других профессий
5. Работа в отрасли: 0,21 баллов – государственные учреждения. Банки и брокерские фирмы
6. Занятость: 0,059 баллов за каждый год работы на данном предприятии
7. Наличие банковского счета – 0,45 баллов
8. Владение недвижимостью – 0,35 баллов
9. Наличие полиса по страхованию жизни – 0,19 баллов
Если заемщик набирает 1,25 баллов и выше банк дает согласие на выдачу кредита.
Если ниже 1,25 баллов банк отказывает в выдаче кредита
6. Результат Скоринговая модель
7. Результат Скоринговая модель
8. Алгоритм решения задачи
Для построения скоринговой модели используются «Элементы управления», вызвать которые
можно из закладки Разработчик основного меню Excel
9. Вызов закладки Разработчик
Файл → Параметры → Настройка ленты → В окне Основные вкладки выбрать
Разработчик
10. Используемые Элементы управления формой
11. Работа с Элементом управления формой
Шаг 1 Выбор элемента управления формой
Шаг 2 Вызов команды Формат объекта из контекстного меню
Шаг 3 Описание элемента управления формой
Сейчас все очень много говорят про искусственный интеллект и его применение во всех сферах работы компании. Однако есть некоторые области, где еще с давних времён главенствует один вид модели, так называемый «белый ящик» — логистическая регрессия. Одна из таких областей – банковский кредитный скоринг.
Для этого есть несколько причин:
- Коэффициенты регрессии можно легко объяснить в отличие от «черных ящиков» вроде бустинга, куда может входить более 500 переменных
- Машинное обучение всё еще не вызывает доверия у менеджмента из-за сложности в интерпретации моделей
- Существуют неписанные требования регулятора к интепретируемости моделей: в любой момент, например, Центробанк может попросить объяснения — почему было отказано в кредите заемщику
- Компании используют внешние data mining программы (например, rapid miner, SAS Enterprise Miner, STATISTICA или любой другой пакет), которые позволяют быстро научиться строить модели, даже не имея навыков программирования
В этом посте мы расскажем о том, как при построении скоринга мы отказались от внешних data mining пакетов в пользу open source решения в виде Python, увеличили скорость разработки в несколько раз, а также улучшили качество всех моделей.
Процесс построения скоринга
Классический процесс построения скоринговых моделей на регрессии выглядит так:
Он может меняться от компании к компании, но главные этапы остаются постоянными. Нам всегда необходимо производить биннинг переменных (в отличие от парадигмы машинного обучения, где в большинстве случаев нужно лишь категориальное кодирование), их отсев по Information Value (IV), и ручную выгрузку всех коэффициентов и бинов для последующей интеграции в DSL.
Такой подход к построению скоринговых карт отлично работал в 90-е, однако технологии классических data mining пакетов сильно устарели и не позволяют использовать новые методики, такие как, например, L2-регуляризация в регрессии, которые позволяют значительно улучшить качество моделей.
В один момент в качестве исследования мы решили воспроизвести все этапы, которые аналитики делают при построении скоринга, дополнить их знаниями Data Scientist’ов, а также максимально автоматизировать весь процесс.
Улучшение в Python
В качестве инструмента для разработки мы выбрали Python за его простоту и хорошие библиотеки, и начали воспроизводить все этапы по порядку.
Первым делом необходим сбор данных и генерация переменных – этот этап составляет значительную часть работы аналитиков.
В Python загрузить из базы собранные данные можно с помощью pymysql.
Далее мы заменяем редкие и пропущенные значения отдельной категорией для предотвращения ovefitting’а, выбираем целевую, удаляем лишние колонки, а также делим на трейн и тест.
Теперь начинается самой важный этап в скоринге для регресии – необходимо написать WOE-binning для числовых и категориальных переменных. В открытом доступе мы не нашли хороших и подходящих для нас вариантов и решили написать сами. За основу числового биннинга взяли эту статью 2017 года, а также эту, категориальный написали сами с нуля. Результаты получились впечатляющими (Gini на тесте поднимался на 3-5 по сравнению с алгоритмами биннинга внешних data mining программ).
После этого можно посмотреть на графиках или таблицах (которые мы потом запишем в excel), как переменные разбились по группам и проверить монотонность:
Отдельно была написана функция для ручного биннинга, которая полезна, например, в случае с переменной «версия ОС», где все телефоны на Android и iOS были сгруппированы вручную.
Следующим этапом идёт отбор переменных по Information Value. Стандартным значением является кат офф 0.1 (все переменные ниже не имеют хорошей предсказательной силы).
После осуществлялась проверка на корреляцию. Из двух коррелирующих переменных нужно удалить ту, у которой IV меньше. Кат офф по удалению был взят 0.75.
Помимо отбора по IV мы добавили рекурсивный поиск оптимального количества переменных методом RFE из sklearn.
Как мы видим на графике – после 13 переменных качество не изменяется, а значит лишние можно удалить. Для регрессии более 15 переменных в скоринге считается плохим тоном, что в большинстве случаев исправляется с помощью RFE.
Далее строилась регрессия и оценивались её метрики на кросс-валидации и тестовой выборке. Обычно все смотрят на коэффициент Gini (хорошая статья про него тут).
Когда мы удостоверились в том, что качество модели нас устраивает, необходимо записать все результаты (коэффициенты регрессии, группы бинов, графики стабильности Gini и переменных и т.д.) в excel. Для этого удобно использовать xlsxwriter, который может работать как с данными, так и с картинками.
Примеры листов экселя:
Итоговый excel в конце еще раз смотрится менеджментом, после чего отдаётся в IT для встраивания модели в продакшен.
Как мы увидели, почти все этапы скоринга можно автоматизировать так, чтобы аналитикам не нужны были навыки программирования для построения моделей. В нашем случае, после создания данного фреймворка от аналитика требуется лишь собрать данные и указать несколько параметров (указать целевую переменную, какие колонки удалить, минимальное количество бинов, коэффициент отсечения для корреляции переменных и т.д), после чего можно запустить скрипт на python, который построит модель и выдаст excel с нужными результатами.
Конечно же, иногда приходится исправлять код под нужды конкретного проекта, и одной кнопкой запуска скрипта при моделировании не обойдешься, однако даже сейчас мы видим качество лучше, чем у применяемых на рынке data mining пакетов благодаря таким техникам как оптимальный и монотонный биннинг, проверка на корреляцию, RFE, регуляризированная версия регрессии и т.д.
Таким образом, благодаря использованию Python мы значительно сократили время разработки скоринговых карт, а также уменьшили затраты труда аналитиков.
Модель данных позволяет интегрировать данные из нескольких таблиц, эффективно построение источника реляционных данных Excel книге. В Excel данных модели данных применяются прозрачно, что обеспечивает табличные данные, используемые в сводных таблицах и на сводных таблицах. Модель данных визуализируются как набор таблиц в списке полей, и в большинстве моментов вы даже не узнаете, что она там.
Прежде чем приступить к работе с моделью данных, необходимо получить некоторые данные. Для этого мы используем power query & Transform ( Get & Transform), чтобы вы могли захотеть вернуться на шаг назад и посмотреть видео или воспользоваться учебным руководством по & Преобразование и Power Pivot.
Excel 2016 & Excel для Microsoft 365 — На ленте есть Power Pivot.
Excel 2013 — Power Pivot входит в выпуск Office профессиональный плюс версии Excel 2013, но по умолчанию не включен. Подробнее о запуске надстройки Power Pivot для Excel 2013.
Excel 2010 — скачайте надстройку Power Pivot, а затем установите надстройку Power Pivot,
Excel 2016 & Excel для Microsoft 365 — & Power Query интегрирован с Excel на вкладке Данные.
Excel 2013 : Power Query — это надстройка, которая входит в состав Excel, но ее необходимо активировать. Перейдите в > файлов > надстройки ,а затем в меню Управление в нижней части области выберите Надстройки COM > Перейти. Проверьте, нет ли в Microsoft Power Query Excel, а затем ОК, чтобы активировать его. На ленту будет добавлена вкладка Power Query.
Excel 2010— скачивание и установка надстройки Power Query.. После активации на ленту будет добавлена вкладка Power Query.
Начало работы
Во-первых, вам нужно получить некоторые данные.
В Excel 2016 и Excel для Microsoft 365 используйте data > Get & Transform Data > Get Data (Получить данные), чтобы импортировать данные из любого числа внешних источников данных, таких как текстовый файл, книга Excel, веб-сайт, Microsoft Access, SQL Server или другая реляционная база данных, которая содержит несколько связанных таблиц.
В Excel 2013 и 2010 перейдите в Power Query > Получитьвнешние данные и выберите источник данных.
Excel предложит выбрать таблицу. Если вы хотите получить несколько таблиц из одного источника данных, проверьте параметр Включить выбор нескольких таблиц. При выборе нескольких таблиц Excel автоматически создает модель данных.
Примечание: В этих примерах мы используем книгу Excel с вымышленными сведениями об учащихся в классах и оценках. Вы можете скачать образец книги модели данныхдля учащихся и следуйте этим командам. Вы также можете скачать версию с завершенной моделью данных..
Выберите одну или несколько таблиц и нажмите кнопку Загрузить.
Если вам нужно изменить исходные данные, можно выбрать параметр Изменить. Дополнительные сведения см. в обзоре редактора запросов (Power Query).
Теперь у вас есть модель данных, которая содержит все импортируемые таблицы, и они будут отображаться в списке полей таблицы.
Модели создаются неявно, когда вы импортируете в Excel несколько таблиц одновременно.
Модели создаются явно, если вы импортируете данные с помощью надстройки Power Pivot. В надстройке модель представлена в макете со вкладками так же, как Excel, где каждая вкладка содержит табличные данные. Сведения о том,как импортировать данные с помощью базы данных, см. в этой SQL Server Power Pivot.
Модель может содержать одну таблицу. Чтобы создать модель на основе только одной таблицы, выберите таблицу и нажмите кнопку Добавить в модель данных в Power Pivot. Это может понадобиться в том случае, если вы хотите использовать функции Power Pivot, например отфильтрованные наборы данных, вычисляемые столбцы, вычисляемые поля, ключевые показатели эффективности и иерархии.
Связи между таблицами могут создаваться автоматически при импорте связанных таблиц, у которых есть связи по первичному и внешнему ключу. Excel обычно может использовать импортированные данные о связях в качестве основы для связей между таблицами в модели данных.
Советы по уменьшению размера модели данных см. в Excel и Power Pivot.
Дополнительные сведения см. в учебнике Импорт данных в Excel и Создание модели данных.
Совет: Как узнать, есть ли в книге модель данных? Перейдите в Power Pivot > Управление. Если вы видите данные, похожие на таблицу, то модель существует. Дополнительные сведения см. в этой теме.
Создание связей между таблицами
Далее нужно создать связи между таблицами, чтобы можно было извлекть данные из любой из них. Каждая таблица должна иметь первичный ключ или уникальный идентификатор поля, например Код учащегося или номер класса. Проще всего перетащить эти поля, чтобы соединить их в представлении диаграммы Power Pivot.
Перейдите на > PowerPivot .
На вкладке Главная выберите представление диаграммы.
Будут показаны все импортируемые таблицы, и вам может потребоваться некоторое время, чтобы их можно было отобразить в зависимости от их количество.
Затем перетащите поле первичного ключа из одной таблицы в следующую. Ниже приведен пример представления диаграммы таблиц наших учащихся:
Мы создали следующие ссылки:
tbl_Students | ИД учащегося > tbl_Grades | ИД учащегося
Другими словами, перетащите поле "ИД учащегося" из таблицы "Учащиеся" в поле "ИД учащегося" в таблице "Оценки".
tbl_Semesters | ИД семестра > tbl_Grades | Семестр
tbl_Classes | Номер > tbl_Grades | Номер класса
Имена полей не должны быть одинаковыми для создания связи, но они должны быть одного типа данных.
Соединители в представлении диаграммы имеют 1 с одной стороны, а "*" — на другой. Это означает, что между таблицами существует связь "один-к-многим", которая определяет способ использования данных в таблицах. Дополнительные сведения см. в этой теме: Связи между таблицами в модели данных.
Соединитетели указывают только на связь между таблицами. Они не будут показывать, какие поля связаны друг с другом. Чтобы увидеть ссылки, перейдите в Power Pivot > Управление > конструктором > связи > Управление связями. В Excel вы можете перейти к data > Relationships (Отношения > данных).
Создание и создание с помощью модели данных для создания сводная диаграмма
Книга Excel может содержать только одну модель данных, но она содержит несколько таблиц, которые можно многократно использовать во всей книге. Вы можете добавить дополнительные таблицы в существующую модель данных в любое время.
В Power Pivot, перейдите вуправление .
На вкладке Главная выберите вкладку "PivotTable".
Выберите место для размещения таблицы: новый или текущий.
Затем создайте или создайтесетовую диаграмму. Если вы уже создали связи между таблицами, вы можете использовать любое из их полей в таблице. Мы уже создали связи в образце книги модели данных для учащихся.
Добавление имеющихся несвязанных данных в модель данных
Сначала выберите любую ячейку в данных, которые вы хотите добавить в модель. Это может быть любой диапазон данных, но лучше всего отформатированные как Excel таблицы.
Добавьте данные одним из следующих способов.
Щелкните Power Pivot > Добавить в модель данных.
Выберите Вставка > Сводная таблица и установите флажок Добавить эти данные в модель данных в диалоговом окне "Создание сводной таблицы".
Диапазон или таблица будут добавлены в модель как связанная таблица. Дополнительные сведения о работе со связанными таблицами в модели см. в статье Добавление данных с помощью связанных таблиц Excel в Power Pivot.
Добавление данных в Power Pivot таблицу
В Power Pivot невозможно добавить строку в таблицу, введя текст непосредственно в новой строке, как это можно сделать на листе Excel. Но вы можете добавить строки, скопируяи впав их или обновив исходные данные и обновив модель Power Pivot.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Компания Finmodel. bz разработала финансовую модель для сети кофеен Coffee Moose. В неё входят 98 заведений в России, СНГ и ряде стран Восточной Европы. В месяц открывается 10−12 заведений трех форматов: «полноценная кофейня», «островок в ТЦ, БЦ» и «торговый павильон». Количество потенциальных франчайзи — 300−400 ежемесячно. Каждый из них получает документы, включающие финансовую модель. Поскольку далеко не все франчайзи обладают специальными знаниями в финансах, основатель Coffee Moose, Алексей Миронов, предъявил следующие требования к бизнес-модели:
- простота;
- удобство использования;
- наглядность;
- формат xls (Microsoft Excel).
Мы отказались от создания сложной финмодели, включающей операционный план, механизмы привлечения финансирования, расчёт баланса и оценку стоимости бизнеса. Также ввели упрощения, не принятые среди профессиональных финансистов. Повторимся: бизнес-модель делается под конечного пользователя, а не для галочки.
По опыту работы с франчайзинговыми проектами можно сказать, что даже несмотря на подробный ликбез по финансам, зачастую у франчайзи все равно остается масса вопросов по составленным документам.
- ключевые предпосылки;
- предпосылки меню кофейни;
- переменные доходы и расходы;
- смету на открытие;
- годовую отчетность;
- помесячную отчетность.
В большинстве финмоделей предпосылки и выводы разнесены по финмодели. Пользователь меняет исходные данные, а потом ищет, на что они влияют. Для франчайзинговых проектов это неприемлемо.
Создание отдельного листа с дашбордом не подходит — франчайзи в нём непременно запутается. Поэтому в приведенном расчете графики меняются сразу. Слева — ввод данных; справа — удобные визуализации. Позитивные отзывы франчайзи подтвердили правильно выбранной структуры шаблона.
Microsoft Excel начинает безнадёжно устаревать. Понимая это, мы рекомендуем своим клиентам переходить на инструменты Google Sheets. Преимущества последнего — онлайн-редактирование, удобное одновременное использование документа и стабильность работы системы.
Тем не менее, для Coffee Moose было принципиальным использование Microsoft Excel —
франчайзи проекта — консервативные люди, не знакомые с интерфейсом Google Sheets. Знайте своих пользователей — бизнес-модель составляется для них, а не для вас!
Для подготовки финмодели можно опираться на требования ВЭБ, запросы от банков или на индивидуальные потребности. Если вы не понимаете, как работает финансовое моделирование, ищите исполнителя, который ответит на все вопросы и окажет поддержку даже после завершения проекта.
Читайте также: