Дерево принятия решений эксель

Самостоятельные работы / Дерево решений / Дерево решений MS Excel

Надстройка MS Excel «Дерево решений» предназначена для построения и анализа древа решений и событий для ситуаций неопределенности и риска.

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

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

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

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

1. Начинать построение дерева нужно кнопкой «Создать дерево», по нажатии которой будет создана новая страница со стандартным началом дерева («стволом»). Ствол (начало дерева) показан коричневой линией. Красная звездочка здесь и далее — возможная точка «роста» дерева, т. к. в ячейку, содержащую звездочку, можно вставить продолжение дерева. Часть ячеек скрыта, чтобы не отягощать схему служебной информации.

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

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

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

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

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

Манипуляции с деревом решений.

1. Команды Undo и Redo. Две кнопки со стрелками в панели «Операции» в левом нижнем углу окна интерфейса служат для отката последних изменений или возврата к более новой версии дерева. Следует иметь ввиду, что встроенный в MS Excel механизм Undo\Redo не поддерживает изменения, которые делают надстройки. Поэтому при работе надстройки возникают два независимых набора последних изменений: версия Excel и версия надстройки «Дерево решений». Если требуется откатить назад изменения, сделанные вне интерфейса надстройки, следует пользоваться меню Правка — Отменить\Повторить. Состояние дерева решений после каждой операции через интерфейс надстройки так же запоминается и может быть возвращено. Однако при этом будут потеряны изменения, сделанные вне интерфейса после последней операции. К сожалению, это может запутать пользователя при возврате больше чем на один-два шага.

После закрытия окна интерфейса история операций уничтожается, даже если сам файл остается открытым.

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

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

4. Команда «Выровнять» панели «Дерево».

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

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

Такую одиночную ветку можно вставить и добавляя «развилку».

5. С помощью операции «Добавить» панели «Добавить развилку событий» можно вставить дополнительную развилку в существующий узел дерева решений, если в результате анализа дерева нужно, скажем, предоставить дополнительную возможность выбора. Для этого следует выделить узел дерева (например, L2), задать тип узла и количество ветвей и нажать кнопку «Добавить». Старое продолжение дерева решений от текущего узла будет перенесено в конец первой ветки вновь вставленного узла Q2.

Переменные и вычисления.

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

1. Число переменных можно изменить с помощью панели «Переменные».

Если установить с помощью стрелки вверх у окна числа переменных величину 3 и нажать расположенную тут же кнопку «Добавить», число переменных увеличится на 3 (не забудьте отметить кнопку «Показать», иначе никаких изменений не увидите).

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

Изменить количество переменных можно на любой стадии работы с деревом решений.

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

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

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

Замечание. В узлах «Выбор решения» записана формула =МАКС(. ), выбирающая наибольшее значение в следующих узлах данной развилки. Очевидно, это правильно только если чем больше, тем лучше. Если лучшим является минимальное значение, следует заменить формулы =МАКС(. ) на =МИН(. ) (это можно сделать и через меню «Правка\Заменить»).

Источник

Решение дерева вMsExcel

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

Небольшая овощная лавка еженедельно закупает и продаёт различные овощи и фрукты, в том числе помидоры. Стоимость закупки ящика помидоров составляет 1500 руб., прибыль от продажи ящика — 2400 руб. Статистика исследования спроса приведена в таблице.

Таблица 4. Пример 2 — недельный спрос на помидоры в овощной лавке

Недельный спрос ящиков, шт.

Если закупленный ящик остался непроданным, лавка несет убыток 1500 руб. Определить размер запаса, который целесообразно формировать в начале неделе лавке. Изменится ли решение, если неудовлетворенный спрос клиента будет оценен в 1350 руб.?

Дерево решений, соответствующее задаче представлено показывает Рисунок 38.

Рисунок 38. Пример 2 — дерево решений при закупке помидоров в овощной лавке

Данное дерево можно решить, используя таблицы Excel. Итоговую таблицу решения задачи в Excel отображаетРисунок 39(см. также файл «ЛР3.Пример.xls»).

Ожидаемый чистый доход максимален при выборе альтернативы А—9900 руб. С учетом штрафов за неудовлетворенный спрос максимальный чистый доход дает альтернативаБ—9570 руб.

Рисунок 39. Пример 2 — решение дерева в MS Excel

Деревья с несколькими точками принятия решения

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

В дополнение условий примера 1, банк решает вопрос, проверять ли конкурентоспособность клиента, перед тем, как выдавать ему заём. За проверку аудиторская фирма берет с банка 80000 руб. Т.о. перед банком встают две проблемы (две задачи принятия решения): первая — проводить проверку или нет, вторая — выдавать после проверки заём или нет.

Для решения первой проблемы, банк собирает дополнительные данные: проверяет правильность выдаваемых аудиторской фирмой сведений. Для этого выбираются 1000 человек, которые были проверены аудиторами и которым впоследствии выдавались ссуды. Рекомендации аудиторской фирмы и фактический результат возврата возврат ссуды содержит Таблица 5.

Таблица 5. Пример 3 — фактический результат возврата ссуды для проверенных аудитором клиентов

Рекомендации аудитора после проверки

Источник

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник