Решение задач оптимизации в excel реферат

Обновлено: 03.07.2024

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

Содержание работы

Файлы: 1 файл

KURSOVAYa222.doc

МИНИСТЕРСТВО КУЛЬТУРЫ И ТУРИЗМА УКРАИНЫ

ХАРЬКОВСКАЯ ГОСУДАРСТВЕННАЯ АКАДЕМИЯ КУЛЬТУРЫ

Кафедра информационных технологий

студентка III к. I гр.

канд. техн. наук, доц.

ВВЕДЕНИЕ

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

Объект курсовой роботы — типовые задачи оптимизации процессов современного предприятия.

Предмет исследования — технологии решение задач оптимизации в среде табличного процессора Excel.

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

Задачи курсовой работы: изучить классы задач оптимизации, математические методы их решения и особенности реализации этих методов табличным процессором Excel.

Метод исследования: сравнительный анализ литературных источников и электронных ресурсов сети Интернет.

Опредиление задач оптимизации подробно рассматривается в источнике [ 13], функции и классификации задач оптимизации описаны в работах [1, 13, 14]. Технологии решения задач в MS Excel подробно рассмотрено в работах [4, 6, 7]. Описания технологии решения задач средствами Excel рассмотрено в работах [1, 3, 8, 9, 12, 15], способ решения транспортных задач в источнике [10]. Примеры и задачи оптимизации рассмотрены в источнике [11].

1. ЗАДАЧИ ОПТИМИЗАЦИИ

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

Оптимизация - целенаправленная деятельность, заключающаяся в получении наилучших результатов при соответствующих условиях[13].

Поиски оптимальных решений привели к созданию специальных математических методов и уже в 18 веке были заложены математические основы оптимизации (вариационное исчисление, численные методы и др.). Однако до второй половины 20 века методы оптимизации во многих областях науки и техники применялись очень редко, поскольку практическое использование математических методов оптимизации требовало огромной вычислительной работы, которую без ЭВМ реализовать было крайне трудно, а в ряде случаев - невозможно. Особенно большие трудности возникали при решении задач оптимизации процессов в химической технологии из-за большого числа параметров и их сложной взаимосвязи между собой. При наличии ВМ задача заметно упрощается[14].

Постановка задачи оптимизации в области управления предприятиями предполагает существование конкурирующих свойств процесса, например: количество продукции - "расход сырья", количество продукции - "качество продукции"

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

При постановке задачи оптимизации необходимо следующее.

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

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

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

1.1. Классификация задач оптимизации

Математическое абстрагирование позволяет, казалось бы, различные задачи привести к общей форме. Все задачи оптимизации можно классифицировать как задачи минимизации вещественнозначной функции f(x) N-мерного векторного аргумента x=(x₁, x₂. x_n), компоненты которого удовлетворяют системе уравнений h_k(x)=0, набору неравенств g_i(x) 0, а также ограничены сверху и снизу, т.е. x_i (u) x_i x_i (l) . Функция f(x) - называется целевой функцией. Уравнения h_k(x)=0 – называеются ограничениями в виде равенств, а неравенства g_i(x) 0 - ограничениями в виде неравенств. При этом предполагается, что все фигурирующие в задаче функции являются вещественнозначными, а число ограничений конечно[14].

Задача общего вида:

Минимизировать f(x) при ограничениях

называется задачей оптимизации с ограничениями или задачей условной оптимизации.

Задача, в которой нет ограничений, т.е.

называется оптимизационной задачей без ограничений или задачей безусловной оптимизации.

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

Также один из классификационных признаков делит оптимизационные задачи на два класса: задачи безусловной оптимизации и задачи условной оптимизации. Первые из них характеризуются тем, что минимум функции f: R m ® R ищется на всем пространстве:

f(x) ® min, x О R m .

В задачах же второго класса поиск минимума идет на некотором собственном подмножестве W пространства R m :

Множество W часто выделяется ограничениями типа равенств

где g₀: R m ® R k , и/или ограничениями типа неравенств

где g₁: R m ® R l .

Другой классификационный признак задач оптимизации — свойства функций f и множеств W. Например, задачи (2) и (3) называются линейными (часто говорят о задачах линейного программирования), если функция f — аффинная, а множество W — многогранное (множество W называется многогранным, если оно выделяется ограничениями вида (4) и (5) с аффинными функциями g₀ и g₁)[13].

2. ТЕХНОЛОГИЯ РЕШЕНИЯ ЗАДАЧ ОПТИМИЗАЦИИ

Задача оптимизации – поиск экстремума, то есть, максимального или минимального значения определенной функции, которую называют целевой функцией, например, это может быть функция прибыли – выручка минус затраты. Так как и всё в мире ограничено (время, деньги, природные и человеческие ресурсы), в задачах оптимизации всегда есть определенные ограничения, например, количество металла, рабочих и станков на предприятии по изготовлению деталей[4].

При решении задач оптимизации чаще всего применяются следующие команды.

1. Подбор параметров – один из самых мощных средств, которым располагает табличный процессор Excel. С помощью сравнительно простых приёмов можно находить оптимальные решения многих задач. Этот процесс подробно описан в 2.2.

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

Решения задач оптимизации состоит в поиске оптимального плана с использованием математических моделей и вычислительных методов. Эти методы реализуются с помощью компьютеров и специальных программ-оптимизаторов. В курсовой работе предлагается для выполнения расчетов воспользоваться оптимизационной программой Solver (Поиск решений), встроенной в табличный процессор MS Excel в качестве команды.

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

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

2.1. Создание таблиц

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

нажать клавишу ввода ;
щёлкнуть на кнопке с галочкой в строке формул;
щёлкнуть мышью на другой ячейке;
клавишей управления курсором перевести активную клетку в другое место.

При использовании программы MS Excel существенным является соблюдение следующих правил:

символьный текст по умолчанию выравнивается по левому краю ячейки, а числа - по правому;
если содержимое превышает ширину клетки и соседняя справа пустая, то оно видимо на экране полностью, а если соседняя справа клетка заполнена, в левой клетке на экране присутствует только часть содержимого, поместившееся в этой клетке[8].

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

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

Табличный процессор позволяет созданную таблицу оформить рамками различного типа. Для этого следует выделить нужный фрагмент таблицы, выбрать пункты меню Формат, Ячейка. и выбрать вкладку Рамка. В списке Рамка отметить, где в выделенном фрагменте должны проходить линии рамки, в списке Тип линии выбрать нужный тип, если необходимо, в раскрывающемся списке Цвет: задать цвет линии и щёлкнуть на кнопке OK.

Успех в современном бизнесе и менеджменте во многом опирается на оперативный анализ экономической ситуации и выбор оптимального решения из возможных альтернатив. Одним из современных компьютерных средств для решения подобных задач является программа MicrosoftExcel.

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

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

Цель работы

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

I Финансовые функции.

Функция ПЛТ вычисляет величину постоянной периодической ренты (например, регулярных платежей по займу) при постоянной процентной ставке.

ПЛТ (ставка ; кпер ; пс ; бс; тип)

Ставка – процентная ставка по ссуде.

Кпер – общее число выплат по ссуде.

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

Бс – требуемое значение будущей стоимости, или остатка средств после последней выплаты. Если аргумент бс опущен, то он полагается равным 0 (нулю), т. е. для займа, например, значение бс равно 0.

Тип – число 0 (нуль) или 1, обозначающее, когда должна производиться выплата.

Очень важно быть последовательным в выборе единиц измерения для задания аргументов Ставка и Кпер . Например, если производятся ежемесячные выплаты по четырехгодичному займу из расчета 12% годовых, то для задания аргумента ставка используется 12%/12, а для задании аргумента Кпер – 4*12. Если производятся ежегодные платежи по тому же займу, то для задания аргумента ставка используется 12%, а для задания аргумента Кпер – 4.

Для нахождения обшей суммы, выплачиваемой на протяжении интервала выплат, необходимо умножить возвращаемое функцией ПЛТ значение на величину Кпер . Интервал выплат – это последовательность постоянных денежных платежей, осуществляемых за непрерывный период. Например, заем под автомобиль или заклад являются интервалами выплат. В функциях, связанных с интервалами выплат, выплачиваемые вами деньги, такие как депозит на накопление, представляются отрицательным числом, а деньги, которые вы получаете, такие как чеки на дивиденды, представляются положительным числом. Например, депозит в банк на сумму 1000 руб. представляется аргументом -1000, если вы вкладчик, и аргументом 1000, если вы – представитель банка.

Задача № 1 (Вариант 2 Задача № 1)

Для приведенного на рис 1.1 расчета в соответствующие ячейки введены формулы, показанные на рис. 1.2.

Задача № 2 (Вариант 2 Задача № 2)

Функция ПС возвращает приведенную (к текущему моменту) стоимость инвестиции. Приведенная (нынешняя) стоимость представляет собой общую сумму, которая на настоящий момент равноценна ряду будущих выплат. Например, когда вы занимаете деньги, сумма займа является приведенной (нынешней) стоимостью для заимодавца. Синтаксис ПС (ставка ; кпер ; плт ; бс; тип)

Для приведенного на рис 2.1 расчета в соответствующие ячейки введены формулы, показанные на рис. 2.2.

Задача № 3 (Вариант 2 Задача № 3)

ЧПС (ставка ; значение1 ; значение2; …)

Ставка – ставка дисконтирования за один период.

Значение1, значение2 ,… – от 1 до 29 аргументов, представляющих расходы и доходы.

Значение1, значение2 , … должны быть равномерно распределены во времени, выплаты должны осуществляться в конце каждого периода.
ЧПС использует порядок аргументов значение1, значение2 , … для определения порядка поступлений и платежей. Необходимо, чтобы платежи и поступления введены в правильном порядке.
Аргументы, которые являются числами, пустыми ячейками, логическими значениями или текстовыми представлениями чисел, учитываются; аргументы, которые являются значениями ошибки или текстами, которые не могут быть преобразованы в числа, игнорируются.
Если аргумент является массивом или ссылкой, то учитываются только числа. Пустые ячейки, логические значения, текст или значения ошибок в массиве или ссылке игнорируются.

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

Для приведенного на рис 3.1 расчета в соответствующие ячейки введены формулы, показанные на рис. 3.2.

Задача № 4 (Вариант 2 Задача № 4)

Для приведенного на рис 4.1 расчета в соответствующие ячейки введены формулы, показанные на рис. 4.2.

Задача № 5 (Вариант 2 Задача № 5)

Для приведенного на рис 5.1 расчета в соответствующие ячейки введены формулы, показанные на рис. 5.2.

Из результатов расчета видно, что для фирмы предпочтительнее приобрести облигацию.

II Решение оптимизационных задач линейного программирования

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

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

1. Переменные, которые следует определить.

2. Целевая функция, подлежащая оптимизации.

3. Ограничения, которым должны удовлетворять переменные.

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

Задача № 6 (Вариант 6 Задача № 1)

В данной задаче необходимо спланировать объем производства так, чтобы максимизировать прибыль. Обозначим через , , – объемы производства трельяжа, трюмо и тумбочки под телевизор соответственно. Суммарная прибыль от производства равна:

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

Расход древесно-стружечных плит для производства всех видов мебели не может превосходить максимально возможный запас, следовательно:

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

Таким образом, математическая модель данной задачи имеет следующий вид:

при следующих ограничениях:

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

Задача решается в MicrosoftExcelпри помощи команды Сервис , Поиск решения . Ячейки А11, В11, С11 отведены под значения переменных , , . В ячейку Е10 введена целевая функция.

Для приведенного на рис 6.1 расчета в соответствующие ячейки введены формулы, показанные на рис. 6.2.

В диалоговом окне Поиск решения введены данные, показанные на рис 6.3 и рис 6.4.

В поле Установить целевую ячейку диалогового окна Поиск решения дается ссылка на ячейку с функцией, для которой будет находиться максимум, минимум или заданное значение. Для данной задачи в поле Установить целевую ячейку вводится $Е$10 (рис. 6.3).

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

В поле Изменяя ячейки указываются ячейки, которые должны изменяться в процессе поиска решения задачи, т. е. ячейки отведенные под переменные задачи. В нашем случае введем в поле Изменяя ячейки диапазон $А$11:$С$11.

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

Цель курсового проекта – раскрыть понятие “оптимизация” и научиться применять ее методы в решении задач.
Актуальность состоит в том, что в современном обществе методы оптимизации применяются повсеместно, принося существенную экономическую выгоду и предупреждая финансовые крахи. Они позволяют принимать разнообразные управленческие решения в условиях риска и неопределенности. За своей сущностью задача оптимизации – это математическая модель определенного процесса производства продукции, его распределение, хранение, переработки, транспортирования, покупки или продажи, выполнение комплекса сервисных услуг, а значит ее знания необходимы для ведения любого бизнеса и руководства предприятия.

Прикрепленные файлы: 1 файл

Курсовая работа по информатике.doc

Российская международная академия туризма

ЦЕНТРАЛЬНЫЙ ИНСТИТУТ УПРАВЛЕНИЯ И

ЭКОНОМИКИ ТУРИСТСКОГО БИЗНЕСА

Тема: Решение задач оптимизации с помощью MS Excel

Выполнил: студент группы 070138УП

Специальность: управление персоналом

Проверил преподаватель: Дядьков О.Н.

Тема курсовой работы – “Решение задач оптимизации с помощью MS Excel”.

Объем работы – 30 листов.

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

Цель курсового проекта – раскрыть понятие “оптимизация” и научиться применять ее методы в решении задач.

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

Объект исследования: табличный процессор MS Excel.

Предмет исследования: оптимизация в табличном процессоре MS Excel.

изучение литературных источников;
анализ;
синтез.

В работе были использованы труды следующих авторов:

А. Леоненков, С. Бондаренко, С. Минаев.

В соответствии с целью, объектом и предметом исследования были поставлены следующие задачи:

изучить основные сведения об оптимизации;
рассмотреть решение задачи оптимизации с помощью табличного процессора Excel.

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

поставка сырья;
оптимальный выпуск продукции;
оптимальное управление запасами ;
оптимальное распределение ресурсов;
планирования инвестиций;
оптимальный рацион (смесь, сплав);
назначение на должность.

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

Так же подробно раскрывается функция “поиск решений” в Excel.

Во второй главе проделана практическая работа в табличном процессоре MS Excel. Так же рассмотрено применения оптимизации на собственной задаче.

В заключении сделаны выводы и даны рекомендации.

Введение

Тема курсовой работы – “Решение задач оптимизации с помощью MS Excel”.

Объем работы – 30 листов.

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

Цель курсового проекта – раскрыть понятие “оптимизация” и применить ее методы в решении задач.

Объект исследования: табличный процессор MS Excel.

Предмет исследования: оптимизация в табличном процессоре MS Excel.

изучение литературных источников;
анализ;
синтез.

В работе были использованы труды следующих авторов:

А. Леоненков, С. Бондаренко, С. Минаев.

В соответствии с целью, объектом и предметом исследования были поставлены следующие задачи:

изучить общие сведения об оптимизации;
рассмотреть решение задачи оптимизации с помощью табличного процессора Excel.

поставка сырья;
оптимальный выпуск продукции;
оптимальное управление запасами ;
оптимальное распределение ресурсов;
планирования инвестиций;
оптимальный рацион (смесь, сплав);
назначение на должность.

Так же подробно раскрывается функция “поиск решений” в Excel.

Во второй главе проделана практическая работа в табличном процессоре MS Excel. Так же рассмотрена и полностью проработана собственная модель оптимизации в туристской сфере.

В заключении сделаны выводы и даны рекомендации.

1 Основные сведения об оптимизации

1.1 Задачи и функции оптимизации

Это основная причина, которая обусловливает необходимость научного принятия управленческих решений. Одним из направлений научно- технического прогресса стало математическое программирование, которое тесно связанное с практическими проблемами оптимального распределения ресурсов в различных отраслях производства и сферы услуг.

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

поставка сырья;
оптимальный выпуск продукции;
оптимальное управление запасами ;
оптимальное распределение ресурсов;
планирования инвестиций;
оптимальный рацион (смесь, сплав);
назначение на должность;
оптимальная замена оборудования и т. д.

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

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

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

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

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

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

1.2 Методы, средства оптимизации в Excel

Решения задач оптимизации состоит в поиске оптимального плана с использованием математических моделей и вычислительных методов, которые реализуются с помощью компьютеров и специальных программ-оптимизаторов. Все расчёты сделаны популярной оптимизационною программою Поиск решений, встроенной в табличную программу MS Excel.

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

Программа Поиск решений (в оригинале Excel Solver) – дополнительная надстройка табличного процессора MS Excel, которая предназначена для решения определенных систем уравнений, линейных та нелинейных задач оптимизации, используется с 1991 года.

Рисунок 1 - Контекстное меню “надстройки”

Разработчик программы Solver компания Frontline System уже давно специализируется на разработке мощных и удобных способов оптимизации, встроенных в среду популярных табличных процессоров разнообразных фирм-производителей (MS Excel Solver, Adobe Quattro Pro, Lotus 1-2-3).

Высокая эффективность их применения объясняется интеграцией программы оптимизации и табличного бизнес-документа. Благодаря мировой популярности табличного процессора MS Excel встроенная в его среду программа Solver есть наиболее распространенным инструментом для поиска оптимальных решений в сфере современного бизнеса.

По умолчанию в Excel надстройка Поиск решения отключена. Чтобы активизировать ее в Excel 2007, щелкните значок Кнопка Microsoft Office , щелкните Параметры Excel, а затем выберите категорию Надстройки. В поле Управление выберите значение Надстройки Excel и нажмите кнопку Перейти. В поле Доступные надстройки установите флажок рядом с пунктом Поиск решения и нажмите кнопку ОК.

Использование возможностей MS Excel с целью решения экономических задач. План выпуска продукции для получения максимальной прибыли. Основные условия подбора параметров. Создание расчетной таблицы в режиме показа формул. Параметры поиска решения.

Рубрика	Программирование, компьютеры и кибернетика
Вид	контрольная работа
Язык	русский
Дата добавления	15.03.2018
Размер файла	284,2 K

Студенты, аспиранты, молодые ученые, использующие базу знаний в своей учебе и работе, будут вам очень благодарны.

Содержание

Задание

1. Используя возможности EXCEL по подбору параметра, решите следующую задачу: определите, сколько денег необходимо поместить в банк на 3 года, чтобы получить 100000 при 10% годовых.

2. Пусть для изготовления двух видов продукции Р1 и Р2 используют три вида сырья SI, S2 и S3. Запасы сырья, количество единиц сырья, затрачиваемых на изготовление единицы продукции, а также величина прибыли от реализации единицы продукции приведены в таблице. Необходимо составить такой план выпуска продукции, чтобы при его реализации получить максимальную прибыль.

Количество единиц сырья, идущих на изготовление единицы продукции

Прибыль от реализации единицы продукции

Задача 1

Используя возможности EXCEL по подбору параметра, решите следующую задачу: определите, сколько денег необходимо поместить в банк на 3 года, чтобы получить 100000 при 10% годовых.

Ход работы.

1. Создадим новую Книгу в Excel 2010.

2. Создадим расчетную таблицу и введем в нее формулы. Вид таблицы в режиме показа формул представлен на рисунке 1.

Рисунок 1 Таблица в режиме показа формул

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

- выберем Данные - Анализ что-если - Подбор параметра

- установим условия в диалоговом окне как на рис.2:

Рисунок 2 Условия подбора параметра

- после нажатия ОК получим результат (рис.3)

Рисунок 3 Результат подбора параметра

Вывод:

Таким образом, при данных условиях необходимо поместить в банк 75131 руб.48 коп.

Задача 2

Пусть для изготовления двух видов продукции Р1 и Р2 используют три вида сырья SI, S2 и S3. Запасы сырья, количество единиц сырья, затрачиваемых на изготовление единицы продукции, а также величина прибыли от реализации единицы продукции приведены в таблице. Необходимо составить такой план выпуска продукции, чтобы при его реализации получить максимальную прибыль.

Количество единиц сырья, идущих на изготовление единицы продукции

Прибыль от реализации единицы продукции

1. Создадим расчетную таблицу. Вид таблицы в режиме показа формул на рисунке 4.

Ячейки C4 и D4 - количество единиц продукции (переменные), в столбце Е вычисляется суммарное количество каждого вида сырья, в строке 9 - прибыль по каждому виду продукции. В ячейке Е9 располагается целевая функция, значение которой необходимо максимизировать.

Рисунок 4 Расчетная таблица в режиме показа формул

2. Выберем Данные - Поиск решения.

Установим параметры поиска решения как на рисунке 5.

Рисунок 5 Параметры поиска решения

4. Результат представлен на рисунке 6.

Рисунок 6 Результат поиска решения

Вывод: таким образом, оптимальный план выпуска продукции Р₁=0, Р₂=10, максимальная прибыль 300 ед.

оптимизация excel таблица параметр

Список литературы

1. Божко В.П., Власов Д.В., Гаспариан М.С. Информационные технологии в экономике и управлении: Учебно-методический комплекс. - М.: Изд. центр ЕАОИ. 2012. - 120 с.

2. Грошев А.С. Информатика: учеб. для вузов. - Архангельск: Арханг. гос. техн. ун-т. - 2012.

3. Симонович С.В. Информатика. Базовый курс: Учебник для вузов. 3-е изд. Стандарт третьего поколения. - СПб.: Питер, 2014. - 640 с.

Подобные документы

Краткие сведения о системах принятия решения в режиме показа формул и в режиме пользователя. Принципы решения задач оптимизации. Построение математической модели. Диаграмма "Оптимизация плана перевозок". Создание таблицы БД в Access: база данных, запросы.

курсовая работа [482,3 K], добавлен 12.08.2012

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

курсовая работа [3,7 M], добавлен 07.06.2010

лабораторная работа [4,5 M], добавлен 03.08.2011

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

курсовая работа [64,5 K], добавлен 21.11.2012

Обработка информации в электронных таблицах Excel или списках, основные понятия и требования к спискам, экономико-математические приложения Excel. Решение уравнений и задач оптимизации: подбор параметров, команда "Поиск решения", диспетчер сценариев.

реферат [704,3 K], добавлен 08.11.2010

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

контрольная работа [88,7 K], добавлен 28.05.2009

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

Фирма рекламирует свою продукцию с использованием четырех средств: телевизора, радио, газет и рекламных плакатов. Маркетинговые исследования показали, что эти средства приводят к увеличению прибыли соответственно на 10, 5, 7 и 4 доллара в расчете на 1 доллар, затраченный на рекламу. Распределение рекламного бюджета по различным видам рекламы подчинено следующим ограничениям:

а) Полный бюджет составляет 500000 долларов;

b) Следует расходовать не более 40% бюджета на телевидение и не более 20% бюджета на рекламные щиты;

с) Вследствие привлекательности для молодежной части населения различных музыкальных каналов на радио по этой позиции следует расходовать по крайней мере половину того, что планируется на телевидение.

1. Сформулировать и решить задачу распределения средств по различным источникам для получения максимальной прибыли от рекламы;

2. Объяснить смысл данных отчета по устойчивости;

3. Определить изменится ли оптимальный план распределения средств, если увеличение прибыли от газетной рекламы снизится до 5 долларов в расчете на 1 доллар, затраченный на рекламу;

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

Составим математическую модель задачи, выбрав в качестве переменных , х₄ - количество средств, затраченных на телевидение, рекламные плакаты, радио и газеты соответственно. Тогда ожидаемая прибыль от рекламы может быть подсчитана по формуле

(1)

Переменные задачи удовлетворяют ограничениям

(2)

в левых частях которых вычислены затраты денежных ресурсов на телевидение, радио, газеты и рекламные плакаты, а в правых частях записаны максимально возможные запасы средств на эти ресурсы. Учитывая, что переменные задачи по своему экономическому смыслу не могут принимать отрицательные значения, получаем математическую модель задачи оптимального распределения денежных ресурсов с целью получения максимального дохода от рекламы.

.

Очевидно, что построенная модель имеет линейную структуру и, следовательно, является задачей линейного программирования.

Создание электронной модели

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

Сначала заносим в таблицу неизменяемые данные, а затем заполняем изменяемые ячейки, в которых расположены компоненты плана . На этапе ввода исходных данных сюда заносятся любые числа, например, единицы. После решения в этих ячейках будут находиться оптимальные значения переменных. Целевая функция (суммарный доход) и левые части ограничений (Затраты по видам реклам) подсчитываются в соответствие с составленной моделью по формуле (1) и левым частям (2).

Отчет состоит из трех таблиц, расположенных на одном листе книги Excel.

В первой таблице выводятся сведения о целевой функции. В столбце Исходное значение приведено значение целевой функции до начала вычислений, в столбце Результат - после оптимизации.

Следующая таблица содержит значения искомых переменных (изменяемых ячеек) до и после решения задачи.

оптимизация математическая электронная модель

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

Из отчета по результатам применительно к данной задаче видим, что оптимальный план распределения средств состоит в еженедельных затратах на телевидение в размере 200 000$, на газеты в размере 100 000$ и на радио в размере 200 000$, а вкладывать денежные средства в рекламные щиты не выгодно. Таким образом , и максимальный суммарный доход 000$. При этом полный бюджет на рекламу продукции используются полностью, т.е. является дефицитным, а также денежные средства отпущенные на телевидение и газеты расходуется полностью и также являются дефицитными, а денежные средства на рекламные щиты расходовать нецелесообразно.

Отчет по устойчивости

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

Отчет состоит из двух таблиц, расположенных на одном листе книги Excel.

В первой таблице (Изменяемые ячейки) приводится следующая информация о переменных:

· результирующее значение - оптимальные значения переменных;

· нормированная стоимость - ее величина равна значению соответствующей симплексной оценки с противоположным знаком. Для невыпускаемой продукции нормированная стоимость показывает, на сколько изменится целевая функция при принудительном включении единицы этой продукции в оптимальное решение;

· коэффициенты целевой функции;

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

Во второй таблице (ограничения) приводятся аналогичные значения для ограничений задачи:

· величины использованных ресурсов (левые части ограничений) при оптимальном плане выпуска продукции;

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

· исходные запасы ресурсов (правые части ограничений);

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

Используем результаты отчета по устойчивости для проведения постоптимального анализа в данной задаче:

Исследуем сначала влияние на оптимальный план изменений коэффициентов целевой функции - прибыль от 1 затраченного $ на рекламу определенного вида.

Из первой таблицы следует, что оптимальный план затрат на рекламу не изменится, если первоначальная прибыль долл. рекламы Х1 возрастет на 1Е+30 доллара или уменьшится на 5 доллара. Другими словами, условие сохранения оптимального плана при изменении прибыли от рекламы Х1 имеет вид: или .

Аналогично, условие сохранения оптимального плана при изменении прибыли рекламы Х3 имеет вид: или , и условие сохранения оптимального плана при изменении прибыли рекламы Х4 имеет вид: или .

Наконец, при изменении прибыли от рекламы Х2 ранее найденный план останется оптимальным, если исходная цена возрастет не более чем на 1 доллар. В то же время любое уменьшение цены не влияет на оптимальный план , так как число равно , т.е. практически является бесконечно большим числом. Таким образом, условие сохранения оптимальности плана при изменении цены примет вид . Это означает, что рекламу Х2 не выгодно запускать (), если прибыль от нее будет не выше 5 долларов. Если же прибыль превысит 5 долларов от использования рекламных щитов, то план перестанет быть оптимальным, и в новом оптимальном решении будет положительным т.е. использование рекламы в виде рекламных щитов станет выгодным.

Отчет по пределам

Третий отчет для данной задачи, называемый отчетом по пределам, состоит из двух таблиц.

Читайте также:


Реферат клод луи бертолле

Правила оформления документов реферат

Участники уголовного судопроизводства со стороны защиты реферат

Физическое развитие важный показатель состояния здоровья реферат

Погода и самочувствие человека реферат