Дерево принятия решения excel

Для принятия решений в ситуации неопределенности широко используется метод «таблиц выигрышей и потерь». Однако в сложных случаях приходится выбирать между альтернативами, каждая из которых представляет собой «многошаговый» процесс принятия решений. Такие шаги могут быть разнесены во времени, на каждом шаге может возникать новый набор альтернатив и сценариев будущего, усложняется расчет вероятностей различных событий. В этом случае визуализировать процесс выбора из рассматриваемых альтернатив удобно с помощью схемы или графа, называемых деревом (древом) решений.
Дерево решений — это необходимый инструмент при стратегическом планировании и в инвестиционном анализе. Это один из методов автоматического анализа данных. Он помогает в решении задач по классификации и регрессии. Надстройка MS Excel «Дерево решений» предназначена для построения и анализа древа решений и событий в ситуациях неопределенности и риска. Древо решений и событий строится в рабочей книге MS Excel. При построении автоматически добавляются стандартные формулы для выбора, оценки среднеожидаемого результата и расчета вероятности.

Описание надстройки «Дерево решений»

стpоит на листе MS Excel дерево возможностей;
на основании данных и экспертных оценок, которыми вы располагаете, расчитывает возможный выигрыш или убыток при выборе того или иного решения;
оценивает вероятности принимаемых решений;
дает ответ на вопрос «Что будет, если ситуация немного изменится?»

Запустите файл надстройки «Decision Tree NN.nn.xlam».

При открытии файла появится сообщение об опасности макросов, ответьте «Включить макросы» (вся рабочая часть надстройки и есть макросы, т.е. программа на языке VBA).

После запуска файла в MS Excel появится панель управления надстройкой:

Блок Создать

В меню «HCXL| Дерево решений» кликнте по кнопке «Анализ древа решений. Создать» на панели инструментов.

При этом надстройка создает новую страницу со стандартным началом дерева («стволом»).

Часть ячеек скрыта, чтобы не отягощать схему служебной информаци.

Your browser does not support the HTML5 canvas tag.

На листе автоматически выделяется ячейка G2 со звездочкой.
Красная звездочка здесь и далее — это возможная точка «роста» дерева, т. к. в ячейку, содержащую звездочку, можно вставить продолжение дерева. Это место, с которого дерево начнет расти, поэтому никогда не затирайте такие звездочки до завершения построения дерева решений .

Блок Добавить развилку событий

Будущее, которое наступит с некоторой вероятностью (вероятность мы как-то можем оценить сами);
Будущее, которое наступит после того, как мы примем определенное решение. Принятое решение сулит нам в последствии определенный выигрыш или проигрыш, который можно оценить конкретной суммой в денежных единицах.

Читайте также: Поделки дерева из шишек

Развилка Выбор решения используется, если необходимо выбрать конкретное решение из ряда возможных.
Для конструирования дерева решений в этом случае нужно определиться, из скольких вариантов решений придется выбирать главное решение. Число вариантов выбирается из выпадающего списка в ячейке Из : _ ветвей. Возможны варианты от 1 до 10. По умолчанию указывается 2. При выборе 1 строится одиночная ветка.

Your browser does not support the HTML5 canvas tag.

Развилка Варианты будущего выбирается, если рассматриваются различные варианты будущего, на которые вы не можете влиять .
Для конструирования Вариантов Будущего выбирается число возможных вариантов из выпадающего списка в ячейке Из : _ ветвей. Возможны варианты от 1 до 10. По умолчанию указывается 2.

В чем различие между Выбором решения и Вариантом будущего?

Выбор решения

или

Варианты будущего

Последовательность действий:

Выбрать число вариантов решений

GO AND UPDATE YOUR BROWSER BEFORE YOU READ ON!

Выбрать Максимум или Минимум на панели Разное

GO AND UPDATE YOUR BROWSER BEFORE YOU READ ON!

Последовательность действий:

Выбрать число вариантов будущего

GO AND UPDATE YOUR BROWSER BEFORE YOU READ ON!

Результат действий:

Развилка Выбор решения всегда зеленая.

Результат действий:

Развилка Варианты будущего всегда оранжевая.

Что с этим можно делать:

Писать комментарии в ячейках «Решение х».

В каждой появившейся звездочке дерево может быть продолжено. Продолжением может быть как зеленая развилка (Выбор решения), так и оранжевая (Варианты будущего).

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

Что с этим можно делать:

Писать комментарии в ячейках «Вариант х».

В каждой появившейся звездочке дерево может быть продолжено. Продолжением может быть как зеленая развилка (Выбор решения), так и оранжевая (Варианты будущего).

В столбцах вероятностей с числами 0% и 100% вводить с клавиатуры экспертные оценки вероятностей наступления того или иного варианта будущего. Ячейку с 100% менять не нужно. Число в этой ячейке изменится автоматически, исходя из соображения, что какое-нибудь будущее обязательно наступит, поэтому сумма вероятностей всех вариантов будущего равна 1.

Замечания:

Вероятности, указанные для развилок Варианты будущего, учитываются далее по дереву решений. Поэтому в столбцах p(i) для каждой ветви фигурируют правильные полные вероятности попасть в данную ветвь.

В ячейке развилки, обозначенной и стоящей перед зеленой развилкой «Выбор решения», автоматически записывается формула =МАКС(L2:L6) , с помощью которой позднее будет сделан выбор, какое из решений наиболее привлекательно.

В ячейке развилки, обозначенной и стоящей перед оранжевой развилкой «Варианты будущего» , автоматически генерирована формула =СУММПРОИЗВ(N2:N4;Q2:Q4) . Это оценка средневзвешенного результата для этой вероятностной развилки событий.

Блок Ветви дерева

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

Когда дерево решений построено, то часто концы ветвей оказываются в разных столбцах. Для лучшего восприятия дерева бывает удобно их выровнять. При нажатии кнопки «Выровнять» все ветви дерева доращиваются до самой длинной ветки путем добавлений одиночных веток, повторяющих предшествующую информацию.

Кнопка «Вставить» становится доступной после нажатия кнопки «Копировать». Содержимое буфера обмена можно вставить только в ячейку со звездочкой. При попытке вставить данные в другое место листа вы получите уведомление о том, что «Ячека не является узлом дерева».

Your browser does not support the HTML5 canvas tag.

Если дерево решений серьезно перестраивалось и старые подравнивания только загромождают структуру и затрудняют ориентировку в событиях и решениях, то все одиночные ветви-спейсеры можно убрать командой «Сжать».

Если выделить ячейку с узлом дерева и нажать кнопку «Удалить», то все продолжение дерева со всеми разветвлениями, начиная от выделенного узла, будет удалено. Если выделена ячейка, не являющаяся узлом дерева, никакой реакции не последует, появится модальное окно «Ячейка не является узлом дерева». Пример работы команды Удалить смотрите здесь

Иногда возникает необходимость временно заблокировать какие-то ветви развилки «Выбор решения». Для этого используется кнопка «Блокировать». Если выделить ячейку с узлом дерева и нажать кнопку «Блокировать», то вся эта ветвь будет заблокирована, формула из ячейки переносится в примечание, а на ее место записывается уведомление STOP и выбор лучшего переходит к другой ветви. Повторное нажатие кнопки «Блокировать» приводит к снятию блокировки.

Блок Переменные

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

Команда Изменить число переменных добавляет или удаляет переменные.
Допустимые значения от +5 до -5. То есть, возможно добавить (знак +) или удалить (знак -) до 5 переменных. Меньше одной переменной оставить нельзя по техническим (программистским) причинам, так что при попытке убрать все переменные все равно останется одна. Изменить количество переменных можно на любой стадии работы с деревом решений.

Your browser does not support the HTML5 canvas tag.

При нажатии Показать переменные на рабочем листе MS Excel с деревом решений переменные становятся видными, а поле «Изменить число переменных» доступным.

После завершения ввода данных или на этапе построения дерева бывает удобно видеть дерево в возможно более компактном виде. Для этого используют команду Скрыть переменные. При этом к столбцам, содержащим переменные, применяется команда Excel Скрыть столбцы. Кнопка Показать переменные возвращает столбцам с переменными видимость.

Блок Разное

Команда Все дерево увеличивает масштаб построенного дерева решения.

Команды Отменить и Восстановить служат для отмены последних изменений, сделанных через интерфейс надстройки или возврата к более новой версии дерева. Операции, сделанные через интерфейс надстройки невозможно изменить с помощью встроенного в MS Excel механизма Undo\Redo.

При работе с ветвью «Выбор решения» то или иное решение приносит выигрыш или убыток. Поэтому необходимо отметить Максимум, если вы хотите выбрать решение, которое принесет максимальную прибыль, или Минимум, если выбор делается между решениями, который принесут минимальный убыток.

Your browser does not support the HTML5 canvas tag.

Этот блок служит для переключения языка интерфейса настройки и вывода данных.

Для сохранения состояния проекта используются скрытые листы рабочей книги. Эти листы сильно увеличивают размер файла. Поэтому рекомендуется сохранять файл командой Сохранить из интерфейса надстройки. Эта команда очищает файл от временных данных.

Команда Справка создает новый лист в рабочей книге MS Excel. На этом листе выводится подробная информация о том, как работает надстройка «Дерево решений».

Пример использования надстройки «Дерево решений» при решении конкретной задачи:

Скачать надстройку «Дерево решений»

Основная версия надстройки работает в 32-bit и 64-bit версиях MS Excel начиная с MS Office 2007. Более старые версии MS Excel не поддерживаются.
Файл надстройки имеет название: «Decision Tree уу. mm. xlam» (в зависимости от версии), может запускаться как обычный файл MS Excel (при этом нужно согласиться с запуском макросов надстройки).

Источник

Создание дерева решений (блок-схемы) в Excel. Инструмент «SmartArt».

Добрый день.
Как Вы наверняка знаете «Excel» — это не только инструмент для математических расчетов и аналитики числовых данных, но и прекрасный инструмент для оформления документов и создания схем взаимодействия (бизнес-схем).
В поздних версиях программы «Excel» созданы специальные опции для создания и редактирования древовидных схем (план-схем, алгоритмов). Таких как, так называемое, дерево решений.
Дерево решений – это схема, представляющая собой набор связанных между собой в виде дерева блоков. Каждому из блоков в дереве решений присваивается действие (решение). Соответственно, при выполнении того или иного действия появляются несколько вариантов новых решений и т.д.

Пример дерева решений: «Поход в магазин за покупками»

Создать такое дерево решений довольно просто при помощи кнопки SmartArt («умное творчество»).

Кнопка SmartArt находится во вкладке «Вставка». После клика по указанной кнопке появляется окно, в котором предложены десятки готовых решений (структур) схемы.

Для дерева решений оптимально подходят готовые структуры и раздела «Иерархия».

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

При помощи кнопки «Добавить фигуру» можно добавлять новые блоки.

Кнопками «повысить уровень» и «понизить уровень» можно менять место блока в структуре.

Кнопка «Область текста» открывает окно для работы с надписями в блоках.

Так же раздел «конструктор» позволяет редактировать внешний вид блоков:

Способ отображения блоков, в том числе и 3D вид дерева решений.

Добавить комментарий Отменить ответ

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.

Источник