Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
Тема 6. Анализ деловых данных
Excel содержит ряд инструментов анализа, планирования и моделирования по алгоритму «что-если» для решения некоторых специфических экономических задач, таких, как:
Основной командой для решения оптимизационных задач в Excel является команда Подбор параметра. Эта команда определяет неизвестную величину, приводящую к требуемому результату.
Пример. Вычислить количество проданных чашек кофе по 1.75 руб., необходимое для достижения объема продаж в 30000 руб.
Простота работы с этой командой является следствием ее ограниченных возможностей — для завершения итерационного цикла меняется всего одна переменная. Если задача подразумевает изменение дополнительных переменных (например, эффект от рекламы или скидки постоянным клиентам), следует воспользоваться командой Поиск решения (См. ниже).
Во время подбора параметра в т.н. переменную ячейку непрерывно заносятся новые значения, пока не будет найдено решение поставленной задачи. Этот процесс - итерация, и продолжается он до тех пор, пока Excel не выполнит 100 попыток или не получит решение в пределах 0,001 от точного значения
Чтобы настроить оба параметра, необходимо выполнить:
Кнопка Office – кнопка Параметры Excel - соответствующее диалоговое окно - категория Формулы – раздел Параметры вычислений -здесь задаются значения попыток и шагов итераций (См. Рис. 1).
Рис. 1. Диалоговое окно Параметры Excel - категория Формулы – раздел Параметры вычислений.
Для работы с командой Подбор параметра необходимо, чтобы в листе находились:
Ссылка на переменную (пустую) ячейку в формуле - обязательна. Она является той самой переменной, значение которой ищет Excel.
Оптимизация для вышеуказанного примера с помощью команды Подбор параметра выполняется следующим образом:
Рис. 2. Для команды Подбор параметра необходима формула и пустая ячейка с переменной величиной
Вкладка Данные – Группа Инструментов Работа с данными – кнопка раскрывающегося списка Анализ «Что-если» - команда Подбор параметра.
Рис. 3. Диалоговое окно Подбор параметра
В результате прогноз показывает, что для достижения выручки в 30000 руб. необходимо продать 17 143 чашки кофе по 1.75 руб.
Рис. 4. Команда Подбор параметра выводит результат в пустой переменной ячейке листа, указанной в числе параметров команды
В тех случаях, когда оптимизационная задача содержит несколько переменных величин, для анализа сценария необходимо воспользоваться надстройкой Поиск решения.
Пример: продажа кофе трех разновидностей:
Необходима полная информация относительно размеров потенциальной прибыли и того, на какой напиток следует делать основную ставку (хотя особые виды кофе приносят больше прибыли, их компоненты стоят дороже, к тому же приготовление порции занимает больше времени).
Команда Поиск решения позволяет представить данные в виде листа Excel, чтобы периодически пополнять его новой информацией и анализировать.
Команда Поиск решения является надстройкой, поэтому перед началом работы с ней следует убедиться в ее установке, и при необходимости установить.
Кнопка Office – кнопка Параметры Excel - соответствующее диалоговое окно - категория Надстройка – команда Поиск решения (См. Рис. 5)
Неустановленная надстройка Поиск решения
Рис. 5. Диалоговое окно Параметры Excel - категория Надстройка – команда Поиск решения
Первым шагом при работе с командой Поиск решения является создание специализированного листа. Для этого необходимо создать целевую ячейку, в которой определяется суть задачи, например, формула определения общего дохода, который необходимо максимизировать.
Также необходимо создать одну или несколько переменных ячеек, значения которых могут изменяться для достижения поставленной цели. Кроме того, Лист может включать другие значения и формулы, использующие значения целевой и переменных ячеек. Для успешного поиска решения необходимо, чтобы каждая из переменных ячеек влияла на целевую ячейку - формула в целевой ячейке должна опираться в вычислениях на значения переменных ячеек. В противном случае при выполнении команды Поиск решения появляется сообщение об ошибке (Результаты целевой ячейки не сходятся).
На Листе (См. Рис. 6) структурируются данные и формулы, по которым можно оценить еженедельный доход от кафетерия и узнать, сколько чашек кофе необходимо продавать:
Вкладка Формулы - Группа Инструментов Зависимости формул - кнопка Влияющие ячейки
Если решаемая задача содержит несколько переменных и ограничений, для облегчения ввода данных следует задавать имена для ключевых ячеек и диапазонов Листа. Использование имен ячеек также поможет позднее разобраться с ограничениями.
Рис. 6. Перед выполнением команды Поиск решения необходимо создать Лист с одной Целевой ячейкой и одной или несколькими Переменными ячейками и установить Зависимости между ними.
Ячейки D5, D9 и D13 – переменные (выделены оранжевым цветом) - будут содержать искомые значения, при которых должен быть достигнут оптимальный размер еженедельного дохода.
Ячейки D6, D10, D14; G4; G6, G7, G8 (выделены желтым цветом) должны содержать соответствующие формулы.
После того как задача оптимизации подготовлена на Листе, можно воспользоваться командой Поиск решения, например, для определения максимального еженедельного дохода кафетерия при наличии следующих ограничений, о которых говорилось выше:
Помимо размера максимальной прибыли, команда Поиск решения подскажет оптимальное распределение продаж кофе по всем трем видам.
Выполнить следующие действия:
Вкладка Данные - Группа Инструментов Анализ - команда Поиск решения.
Рис. 7. Диалоговое окно Поиск решения
Внимание! При нажатии кнопки Предположить приложение Ехсеl пытается самостоятельно угадать состав переменных ячеек, входящих в задачу. Для этого он просматривает формулу и выбирает ячейки, на которые имеются ссылки. Довольно часто его предложение оказывается неверным.
Рис. 8 Диалоговое окно Добавление ограничения
Рис. 9. Диалоговое окно Поиск решения с добавленными ограничениями
Рис. 10. Диалоговое окно Результаты поиска решения и итоговая таблица
Большим достоинством решений, находимых с помощью команды Поиск решения, является простота, с которой они могут пересчитываться для учета новых целей и непредвиденных обстоятельств.
Можно изменить условия задачи, например, для получения определенного целевого значения - в диалоговом окне Поиск решения опция Значению переключателя Равной задает для целевой ячейки в своем текстовом поле конкретное числовое значение. Поиск решения состоит в определении набора переменных, при которых это значение будет достигнуто.
Команда Поиск решения определяет оптимальное соотношение переменных, начиная работу с чисел в переменных ячейках, и увеличивает их до тех пор, пока не будет найдено приемлемое решение, т.к. предварительно не были наложены ограничения на выбор. По этой причине в нелинейных задачах при различных наборах начальных условий можно получить различные решения.
Это свойство можно использовать, если необходимо увидеть в решении определенное сочетание переменных. Перед выполнением команды в таком случае необходимо задать в переменных ячейках те значения, которые соответствуют требованиям, и Excel при поиске решения начнет именно с них.
Если же необходимо найти настоящее оптимальное решение, следует перед поиском указать в диалоговом окне Поиск решения дополнительные ограничения. Например, можно задать по каждой категории определенное минимальное значение или потребовать сведения к минимуму количества продаваемых продуктов. Для каждой переменной ячейки можно задать по два ограничения (верхняя и нижняя границы значения) и таким образом повлиять на ход решения.
Команды Подбор параметра и Поиск решения очень полезны, однако после решения нескольких задач легко забыть, какими же были исходные значения. Еще важнее то обстоятельство, что при работе с этими командами не существует удобного способа сравнения результатов — при каждом изменении данных необходимо сохранять полученные результаты на отдельных Листах или Книгах. Чтобы устранить эти ограничения, разработчики Excel создали Диспетчер сценариев, помогающий работать с несколькими моделями «что-если». С его помощью можно создавать новые и просматривать существующие сценарии для решения задач, работать с общими командами управления и отображать консолидированные отчеты.
Сценарием называется именованная модель «что-если», в которую входят переменные ячейки, связанные одной или несколькими формулами. Перед созданием Сценария необходимо спроектировать Лист так, чтобы на нем была хотя бы одна формула, зависящая от ячеек, которые могут принимать различные значения.
Пример. Сравнение лучшего и худшего сценариев для недельных продаж кофе в кафетерии.
Лист с тремя переменными ячейками и несколькими формулами (См. Рис. 11) - основа для создания нескольких Сценариев - будет использоваться для создания сценариев продажи Лучший вариант и Худший вариант.
Рис. 11. Перед созданием сценария необходимо иметь Лист с одной или несколькими формулами, зависящими от переменных ячеек
Создание Сценария происходит следующим образом:
Вкладка Данные – Группа Инструментов Работа с данными – кнопка раскрывающегося списка Анализ «Что-если» - команда Диспетчер сценариев.
Рис. 12. Диалоговое окно Диспетчер сценариев
Рис. 13. Диалоговое окно Добавление сценария
При задании переменных ячеек желательно пользоваться создаваемыми именами. Это помогает распознавать их при создании сценария и при последующем вводе аргументов.
Рис. 14. Диалоговое окно Значения ячеек сценария
Рис. 15. Диалоговое окно Диспетчер сценариев с двумя вариантами сценария
10. Закрыть диалоговое окно Диспетчер сценариев кнопкой Закрыть.
Впоследствии, нажатие кнопки Сохранить сценарий в диалоговом окне Результаты поиска решения, открывающемся после решения задачи оптимизации, позволяет сохранить найденное решение в виде Сценария. Требуется только указать название, которое позднее будет использоваться для просмотра в Диспетчере сценариев.
Excel сохраняет Сценарии вместе с Листом текущей Книги, и просмотр их возможен только при открытии данного Листа. Перед просмотром Сценария полезно сохранить Книгу чтобы позднее можно было при необходимости восстановить исходное состояние.
Просмотр Сценария выполняется следующим образом:
Вкладка Данные – Группа Инструментов Работа с данными – кнопка раскрывающегося списка Анализ «Что-если» - команда Диспетчер сценариев.
Рис. 16. Кнопка Вывести позволяет сравнить результаты различных Сценариев «что-если»
Хотя в диалоговом окне Диспетчер сценариев можно сравнивать различные Сценарии, иногда возникает необходимость в создании отчета с обобщенной информацией о различных сценариях Листа. Эта задача выполняется с помощью кнопки Отчет в окне Диспетчер сценариев. Созданный Сводный отчет будет автоматически отформатирован и скопирован на новый Лист текущей Книги.
Внимание! Для улучшения наглядности Отчета желательно присвоить целевой ячейке G4 и переменным ячейкам D5, D9 и D13 соответствующие имена (если это не было сделано ранее).
Создание отчета по сценарию происходит следующим образом:
Вкладка Данные – Группа Инструментов Работа с данными – кнопка раскрывающегося списка Анализ «Что-если» - команда Диспетчер сценариев.
Отчет типа Структура представляет собой форматированную таблицу, которая выводится на отдельном Листе.
Отчет Сводная таблица является специальной таблицей, которую можно настраивать за счет перестановки столбцов и строк.
Рис. 17. Диалоговое окно Отчет по сценарию
Рис. 18. Кнопка Отчет выводит на отдельном Листе текущей Книги сводную информацию о Сценарии
После того как Сценарий создан, он не обязан всегда оставаться неизменным. Для редактирования и удаления Сценариев предназначаются кнопки Изменить и Удалить в диалоговом окне Диспетчер сценариев: