Решение задач оптимизации (Подбор параметра, Поиск решения, Сценарии "что если" и другие задачи)




Скачать 266.65 Kb.
НазваниеРешение задач оптимизации (Подбор параметра, Поиск решения, Сценарии "что если" и другие задачи)
страница3/3
Дата публикации21.03.2013
Размер266.65 Kb.
ТипРешение
vbibl.ru > Математика > Решение
1   2   3

^ 2.2.6. Экономико - математические приложения Excel
К типичным экономико-математическим приложениям Excel относятся:

  • структуризация и первичная логическая обработка данных;

  • статистическая обработка данных, анализ и прогнозирование;

  • проведение финансово-экономических расчетов;

  • решение уравнений и оптимизационных задач.

Структуризация и первичная логическая обработка данных

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

Статистическая обработка данных, анализ и прогнозирование

Функции, реализующие статистические методы обработки и анализа данных, в Excel реализованы в виде специальных программных средств - надстройки Пакета анализа, которая входит в поставку Microsoft Office и может устанавливаться по желанию пользователей. Установка надстройки Пакет анализа осуществляется так же, как и установка других надстроек с помощью команды Сервис/Надстройка. Далее необходимо установить флажок перед пунктом Пакет анализа и нажать ОК.

надстройки в excel - www.lessons-tva.info


После успешной установки надстройки в меню Сервис появится пункт: Анализ данных, а в окне мастера функций становится доступной категория функций - Статистические.

анализ данных в excel - www.lessons-tva.info


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

  • описательный стстистический анализ (описательная статистика);

  • ранжирование данных (Ранг и персентиль);

  • графический анализ (Гистограмма);

  • прогнозирование данных (Скользящее среднее. Экспоненциальное сглаживание);

  • регрессионный анализ (Регрессия) и т.д.

    Статистические функции для регрессионного анализа из категории Статистические в окне мастера функций:

  • ЛИНЕЙН(знач. У; знач. Х; константа; стат.) - Определяет параметры линейного тренда для заданного массива;

  • ТЕНДЕНЦИЯ(знач. У; знач. Х; новые знач. Х; константа;) - Определяет предсказанные значения в соответствии с линейным трендом для заданного массива (метод наименьших квадратов) и многие другие.

статистические функции в excel - www.lessons-tva.info
Проведение финансово-экономических расчетов

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

В Excel реализованы встроенные и дополнительные финансовые функции. Для применения дополнительных функций необходимо установить надстройку Пакет анализа.

По типу решаемых задач все финансовые функции Excel можно разделить на условные группы:

  • функции для анализа инвестиционных проектов;

  • функции для анализа ценных бумаг;

  • функции для расчета амортизационных платежей;

  • вспомогательные функции.

    Применение функций для финансовых расчетов осуществляется с помощью мастера функций (Вставка/Функция, выбрать категорию Финансовые)

функции для финансовых расчетов - www.lessons-tva.info

^ 2.2.7. Решение уравнений и задач оптимизации

Для решения задач оптимизации широкое применение находят различные средства Excel.

В этом разделе рассмотрим команды:

  • Подбор параметров для нахождения значения, приводящего к требуемому результату.

  • Надстройку Поиск решения для расчета оптимальной величины по нескольким переменным и ограничениям;

  • Диспетчер сценариев для создания и оценки наборов сценариев «что – если» с несколькими вариантами исходных данных.

^ Подбор параметров

Основной командой для решения оптимизационных задач в Excel является команда Сервис/Подбор параметра. Эта команда определяет неизвестную величину, приводящую к требуемому результату.

Если команда Подбор параметра отсутствует в меню Сервис, выполните команду Сервис/Надстройка и установите флажок Пакет анализа в окне диалога Надстройка

Для работы с командой Подбор параметра необходимо подготовить лист, чтобы в листе находились:

  • формула для расчета;

  • пустая ячейка для искомого значения;

  • другие величины, которые используются в формуле.

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

Такой процесс называется итерацией, и продолжается он до тех пор, пока редактор не выполнит 100 попыток или не найдет решения, лежащее в пределах точности 0,001 от точного значения (настройка этих параметров осуществляется с помощью  команды Сервис/Параметры, вкладка Вычисления)

Оптимизация с помощью команды Подбор параметров выполняется так:

1. Создайте лист, например, с формулой =B1*B2 в ячейке B3, пустой (переменной) ячейкой (B2) и другими данными (B1), которые могут понадобиться при вычислениях. Например, необходимо определить количество книг по цене 23,75 грн., которые необходимо продать, чтобы объем продаж составил 10000,00 грн.
подбор параметров, лист с формулой word - www.lessons-tva.info
2. Выделите ячейку листа (B3), в которой содержится формула (эта ячейка появится в поле "Установить в ячейке" в окне диалога Подбор параметра). Выполните команду Сервис/Подбор параметра. Открывается окно диалога Подбор параметра..
подбор параметров, выделение ячейки word - www.lessons-tva.info
3. Введите в текстовое поле Значение число, соответствующее объему продаж - 10000. Переместите курсор в текстовом поле Изменяя значения ячейки. Выделите ту ячейку, в которой должен содержаться ответ (переменная ячейка). Ее содержимое будет подобрано и подставлено в формулу командой Подбор параметра. Выделенная ячейка (B2) выделяется на листе рамкой. Нажмите кнопку ОК, чтобы найти решение.
подбор параметров, ввод текстового поля word - www.lessons-tva.info
После завершения итерационного цикла в окне диалога Результат подбора параметра появляется сообщение, а результат заносится в ячейку листа. Решение показывает, что для достижения объема продаж 10000 грн. необходимо продать 421 книгу  по цене 23,75 грн. Для закрытия окна диалога Результат подбора параметра щелкните на кнопке ОК.
^ Команда Поиск решения

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

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

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

Общие свойства, которые характерны для задач, решаемых с помощью надстройки Поиск решения:

  • Существует единственная целевая ячейка, содержащая формулу, значение которой должно быть сделано максимальным, минимальным или же равным, какому-то конкретному значению.

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

  • Может быть задано некоторое количество ограничений — условий или соотношений, которым должны удовлетворять некоторые из изменяемых ячеек.

Постановка задачи

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

Кроме того, лист может включать другие значения и формулы, использующие значения целевой и переменных ячеек. Формула в целевой ячейке должна опираться в вычислениях на значения переменных ячеек.

После того, как задача оптимизации будет подготовлена на листе, можно приступать к работе.
1. Выделите на листе целевую ячейку, в которую введена формула.
2. Выполните команду Сервис/Поиск решения. Открывается окно диалога Поиск решения. Поскольку была выделена ячейка, в текстовом поле «Установить целевую ячейку» появится правильная ссылка на ячейку. В группе «Равной» переключатель по умолчанию устанавливается в положение «Максимальному значению».

поиск решений word - www.lessons-tva.info


3. Перейдите к полю "Изменяя ячейки" и введите переменные ячейки листа
4. Добавьте ограничения на переменные в изменяемых ячейках. Для ввода ограничений нажмите кнопку Добавить, чтобы задать первое ограничение в окне диалога, затем можно ввести второе, третье и т.д.
5. Когда оптимизационная задача будет готова к выполнению, можно нажать кнопку Выполнить для получения ответа. Появится окно диалога с описанием результатов процесса оптимизации.
6. Чтобы отобразить найденное решение в ячейках листа, установите переключатель "Сохранить найденное решение" и нажмите кнопку ОК. Найденная максимальная величина помещается в целевую ячейку, а переменные ячейки заполняются оптимальными значениями переменных, которые удовлетворяют установленным ограничениям.

^ Диспетчер сценариев «что – если»

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

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

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

Создание сценариев происходит следующим образом:

• Выполните команду Сервис/Сценарии. Открывается изображение окна диалога Диспетчер сценариев.

диспетчер сценариев
• Нажмите кнопку Добавить, чтобы создать первый сценарий. Откроется окно диалога Добавление сценария.

добавление сценария
• Введите Лучший вариант (или любое другое имя) в поле Название сценария, затем с помощью окон диалога введите изменяемые ячейки. Когда этот сценарий будет готов, введите следующий.

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

• Закройте окно диалога Диспетчер сценариев кнопкой Закрыть.

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

• Выполните команду Сервис/Сценарии. Открывается окно диалога:

• Выберите из списка сценарий для просмотра.

• Нажмите кнопку Вывести. Excel заменяет содержимое ячеек листа значениями из сценария и отображает результаты на листе.

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

Создание отчетов по сценарию

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

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

• Выполните команду Сервис/Сценарии. Откроется окно диалога Диспетчер сценариев.

• Нажмите кнопку Отчет. Открывается окно диалога Отчет по сценарию, в котором предлагается выбрать ячейки, входящие в отчет, а также его тип. Отчет типа структура представляет собой форматированную таблицу, которая выводится на отдельном листе. Отчет сводная таблица является специальной таблицей, которую можно настраивать за счет перестановки столбцов и строк.

http://www.lessons-tva.info/edu/e-inf2/m2t2_1.html
1   2   3

Похожие:

Решение задач оптимизации (Подбор параметра, Поиск решения, Сценарии \"что если\" и другие задачи) iconЭлементы решения задачи оптимизации
Решение задачи оптимизации с помощью ЭВМ включает следующие обязательные элементы: постановку задачи; математическую модель; алгоритм...

Решение задач оптимизации (Подбор параметра, Поиск решения, Сценарии \"что если\" и другие задачи) iconЛекция Этапы решения педагогической задачи
В связи с этим, рассматривая процедуру решения педагогической задачи, необходимо исходить из того, что ее цель достигается в результате...

Решение задач оптимизации (Подбор параметра, Поиск решения, Сценарии \"что если\" и другие задачи) iconEquation Chapter 1 Section 1Бифуркации Андронова-Хопфа (двухмерная динамическая система)
Часто исследование состояний равновесия некоторой системы в зависимости от параметра может быть сведено к решению уравнения, причем....

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

Решение задач оптимизации (Подбор параметра, Поиск решения, Сценарии \"что если\" и другие задачи) iconВиды задач оптимизации
Система (1) представляет собой общий случай математической постановки задачи оптимизации. Она включает целевую функцию

Решение задач оптимизации (Подбор параметра, Поиск решения, Сценарии \"что если\" и другие задачи) iconЛогические и эвристические методы решения задач
Очевидно, что творческий процесс предполагает решение неординарных, не типовых, но творческих задач

Решение задач оптимизации (Подбор параметра, Поиск решения, Сценарии \"что если\" и другие задачи) iconСистема решения задач на графах
Маркова в теории вероятностей, различные задачи в программировании, в проектировании электронных систем, в экономике, в социологии...

Решение задач оптимизации (Подбор параметра, Поиск решения, Сценарии \"что если\" и другие задачи) iconПри этом (здесь есть своеобразный парадокс) решение задачи частично...
Ариз. Человек сознательно управляет процессом решения, подключая знание тех или иных закономерностей, приемов, методов и т д. Каждая...

Решение задач оптимизации (Подбор параметра, Поиск решения, Сценарии \"что если\" и другие задачи) iconМетодическое пособие «Решение задач по теме «Растворы»
В условиях сокращения количества часов на изучение химии необходимо отметить нехватку времени на отработку навыков решения расчетных...

Решение задач оптимизации (Подбор параметра, Поиск решения, Сценарии \"что если\" и другие задачи) iconО надстройке «Поиск решения»
Ограничения. Ограничения на значения изменяемых ячеек, конечных ячеек или других ячеек, прямо или косвенно связанных друг с другом,...

Вы можете разместить ссылку на наш сайт:
Школьные материалы


При копировании материала укажите ссылку © 2013
контакты
vbibl.ru
Главная страница