40023

Анализ данных в MS Excel

Лекция

Информатика, кибернетика и программирование

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

Русский

2013-10-13

573 KB

129 чел.

Тема: «Анализ данных в MS Excel»

1. Прогнозирование в Microsoft Excel

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

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

Рисунок   Исходные данные для экстраполяции

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

 B3 <Ctrl>+<Shift>+<>

- именно так проще всего выделить нужный диапазон. Затем установите

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

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

  B3:B18
 
Правка  Заполнить  Прогрессия
 
Тип  автозаполнение
 OK

Кстати, можно таким же образом заполнить и диапазон A15:A18 нужными текстовыми значениями. Повторите те же самые действия для столбца A — и благодаря наличию стандартных списков Excel ячейки окажутся заполненными нужными текстовыми значениями. На самом деле, можно было бы воспользоваться автозаполнением для двух столбцов одновременно — как с помощью протаскивания маркера, так и с помощью диалогового окна Прогрессия.

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

 B3:B14
 
Правка  Копировать
 C3
 
Правка  Вставить
 C3:C18
 
Правка  Заполнить  Прогрессия
  
Автоматическое определение шага
 OK

Рисунок

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

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

Для решения этой задачи можно воспользоваться функциями рабочего листа ТЕНДЕНЦИЯ (TREND) для линейной экстраполяции или РОСТ (GROWTH) для экспоненциальной экстраполяции, а также техникой формул массив. Формулы массива способны использовать в качестве аргументов массивы, т.е. диапазоны ячеек рабочего листа, и могут возвращать в качестве результата как одно конкретное значение, так и целый массив значений. Ниже мы приведем решение для случая линейной экстраполяции с использованием формул массива.

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

 E3
 
Вставка функции {на панели инструментов Стандартная}
 Категория
:  Статистические
 
Функция:  ТЕНДЕНЦИЯ  ОК
 
Изв_знач_y := B3:B14
 ОК
 E3:E14
<F2>; <Ctrl>+<Shift>+<Enter>

Обратите внимание на способ, которым мы осуществляем ввод формулы массива в диапазон ячеек. После завершения работы с Мастером функций в первой ячейке выделенного диапазона оказывается введена обычная формула, а не формула массива для всех выделенных ячеек. Нажатие клавиши <F2> вновь переводит нас в режим редактирования, после чего и используется специальная комбинация клавиш <Ctrl>+<Shift>+<Enter>, которой требуется заканчивать ввод формулы массива.

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

  D3
 
D3 := 1
 D3:D18
 
Правка  Заполнить  Прогрессия…
  по столбцам
  арифметическая
 Шаг :=
1
 OK

Наконец, зададим еще одну формулу массива, вычисляющую прогнозируемые значения. На сей раз, поскольку мы хотим заняться прогнозированием, для функции ТЕНДЕНЦИЯ необходимо использовать три аргумента. Кроме известных значений исследуемой зависимости (известные значения y), необходимо также указать те точки, в которых она принимает эти значения (известные значения x), и те точки, для которых строится прогноз (новые значения x):

  E15:E18
 
Вставка функции
 Категория
:  Статистические
 
Функция:  ТЕНДЕНЦИЯ  ОК
 
Изв_знач_y := B3:B14
 Изв_знач_x := D3:D14
 
Нов_знач_x := D15:D18
 ОК
<F2>; <Ctrl>+<Shift>+<Enter>

Как нетрудно видеть, отображаемые формулами значения совпадают с уже имеющимися численными значениями в диапазоне C3:С18. Вся разница лишь в том, что при изменении исходных значений в столбце B числа в столбце C останутся прежними, в то время как формулы в столбце E автоматически изменят свои значения.

Рисунок  Окончательный вид рабочего листа с результатами прогнозирования

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

 Вставка  Диаграмма 
 Стандартные
Тип:
 График  Далее
 Диапазон данных
Диапазон:
B3:B18  Далее
 Заголовки
Название диаграммы := Линейный тренд 
 Легенда
 
 (снять флажок) Добавить легенду
 Готово

Хотя кнопка Готово уже нажата, работа над диаграммой еще не закончена. Прежде всего, отформатированный стандартным образом график не слишком нагляден — все значения попали в верхнюю часть диаграммы, а нижняя часть не используется. Это можно поправить с помощью форматирования оси значений. Щелкните по этой оси, а затем выполните следующие действия:

 Формат  Выделенная ось…
  Шкала
 
минимальное значение := 200000
 OK

Теперь займемся горизонтальной осью. Выделите ее, а затем отформатируйте следующим образом:

 Формат  Выделенная ось…
  Шкала

 
 Число категорий между делениями := 4
  Вид

  Основные
 деления  пересекают ось
  Промежуточные
 деления  наружу
  Метки делений  нет
 OK

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

 Диаграмма  Добавить линию тренда
  
Тип
 
 Линейная
  
Параметры
 
 показывать уравнение на диаграмме
 OK

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

Рисунок Окончательный вид рабочего листа с результатами прогнозирования

2. Подбор параметра

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

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

Для решения этой задачи необходимо выполнить следующие действия:

  1.  Выделить ячейку, содержащую формулу, для которой нужно найти определенное решение.
  2.  Выбрать команду  Сервис  Подбор параметра
  3.  В поле Установить в ячейке ввести ссылку на ячейку, содержащую формулу.
  4.  В поле Значение ввести значение, которое нужно получить по заданной формуле.
  5.  В поле Изменяя ячейку ввести ссылку на ячейку, содержащую значение изменяемого параметра (эта ячейка называется изменяемой).
  6.  Щелкнуть по кнопке ОК

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

Пример1.

Вы хотите открыть счет в коммерческом банке под 8% годовых и накопить на этом счету за пять лет сумму в 150 000 руб., необходимую вам для обновления компьютерного парка вашего малого предприятия. Вы собираетесь производить ежегодные отчисления на ваш счет, и хотите узнать необходимые размеры этого ежегодного отчисления.

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

Введем необходимые данные на рабочий лист Excel, взяв в качестве начального приближения для ежегодной выплаты ровно одну пятую часть нужной суммы: 30 000 руб. Поскольку это выплата, то введенное число должно быть отрицательным. В ячейку В6 введем функцию:

=БС(B9;B10;B11)

Рисунок

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

 Сервис  Подбор параметра

Рисунок

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

Рисунок

Пример 2.

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

Для этого на рабочем листе введем данные:

Поместим в ячейку A3 некоторое начальное значение, например единицу2, а затем присвоим этой ячейке имя X:

  Вставка  Имя  Присвоить…
 
Имя := X 

Теперь введем в ячейку B3 формулу преобразованного уравнения, перенеся все его элементы в одну часть:

=(2*X^2+3)*(1-SIN(X))-LN(X)-2 

Рисунок

Теперь можно воспользоваться командой Подбор параметра для проведения итерационных вычислений и поиска корня:

 Сервис  Подбор параметра…

Рисунок

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

 Сервис  Параметры  Вычисления
 
Относительная погрешность := 1E-6  

После этого следует повторить итерационные вычисления с помощью Подбора параметра, и вы получите в ячейке A3 корень, равный 0,6357434.

Пример 3.

Составление штатного расписания хозрасчетной больницы

Постановка задачи. Заведующий хозрасчетной больницей должен составить штатное расписание, т.е. определить, сколько сотрудников, на каких должностях и с каким окладом он должен принять на работу. Общий месячный фонд зарплат составляет $10 000. Требуется получить такое штатное расписание, в котором предусмотрено оптимальное количество работников и достойная зарплата каждому.

Решение. Построим модель решения этой задачи.

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

5-7 санитарок,

8-10 медсестер,

10-12 врачей,

3 заведующих отделениями,

1 главный врач,

1 заведующий хозяйством,

1 заведующий аптекой,

1 заведующий больницей.

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

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

Допустим, совет решил, что:

медсестра должна получать в 1,5 раза больше санитарки (А=1,5; В=0);

врач – в 3 раза больше санитарки (А=3; В=0);

зав. отделением – на $30 больше, чем врач (А=3; В=30);

зав. аптекой – в 2 раза больше санитарки (А=2; В=0);

завхоз – на $40 больше медсестры (А=1,5; В=40);

главврач – в 4 раза больше санитарки (А=4; В=0);

зав. больницей – на $20 больше главного врача (А=4; В=20);

Задав количество человек на каждой должности, можно составить уравнение:

где М1 – количество санитарок,

М2 – количество медсестер и т.д.

В этом запросе нам известны А1…А8 и В1…В8, а неизвестны С и М1…М8.

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

Построение таблицы штатного расписания

Постройте таблицу по образцу (рис.6).

Отвести для каждой должности одну строку и вписать название должностей в столбец В.

В столбцах C и D указать соответственно коэффициенты А и В.

В какую-нибудь ячейку (например, в F18) занести минимальную заработную плату (в формате с фиксированной точкой и двумя знаками после нее) – множитель С.

Рядом подписать поясняющий текст – «Минимальная заработная плата». В столбце Е вычислить заработную плату для каждой должности по формуле .

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

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

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

.

Рисунок Пример таблицы – результат построения таблицы штатного расписания

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

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

Выполнить команду  Сервис  Подбор параметра…. Появится диалоговое окно (Рис.11). Адрес выделенной ячейки ($G$14) уже содержится в верхнем его поле – Установить в ячейке (его можно изменить).

В поле Значение ввести результат, который необходимо получить. В нашем примере 10000.

В третьем поле Изменяя ячейку указать адрес ячейки, числовое значение в которой программа должна изменить для достижения нужного нам результата, или просто щелкнуть по ней курсором мыши. В нашем примере, ячейка $F$18.

Рисунок  Диалоговое окно Подбор параметра

  •  Нажать кнопку [OK]. Это приведет к изменению значения в ячейке F18. Одновременно появится окно, отображающее состояние подбора параметра.
  •  В случае, если найденное решение устраивает, нажать [OK].

Рисунок

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

СОВЕТ

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

Пример 4.

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

х3 – 0,01х2 – 0,7044х + 0,139104 = 0

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

[-1,1] с шагом 0,2. Результат табуляции приведен на (рис.13.), где в ячейку В2 введена следующая формула:

=x^3 – 0,01*x^2-0,7044*x+0,.139104

Рисунок  Локализация корней полинома

На (рис.13.) видно, что полином меняет знак на интервалах: [-1, -0.8], [0.2, 0.4] и [0.6, 0.8]. Это означает, что на каждом из них имеется корень данного полинома. Поскольку полином третьей степени имеет не более трех действительных корней, значит, мы локализовали все его корни.

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

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

Рисунок  Вкладка Вычисления диалогового окна Параметры

Возьмем, например, их средние точки: -0.9, 0.3 и 0.7 и введем их в диапазон ячеек С2:С4. В ячейку D2 введем формулу

=С2^3 – 0.01*C2^2 – 0.7044*C2 + 0.139104

Выделим эту ячейку и с помощью маркера заполнения протащим введенную в нее формулу на диапазон D2:D4. Таким образом, в ячейках D2:D4 вычисляются значения полинома при значениях аргумента, введенного в ячейки С2:С4, соответственно.

Теперь выберем команду Сервис, Подбор параметра  и заполним диалоговое окно Подбора параметра (рис. 15) следующим образом.

Рисунок  Диалоговое окно Подбор параметра

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

В поле Значение вводим 0 (в этом поле указывается правая часть уравнения). В поле Изменяя значение ячейки введем С2 (в этом поле дается ссылка на ячейку, отведенную под переменную). Вводить ссылки на ячейки в поля диалогового окна Подбор параметра удобнее не с клавиатуры, а щелчком на соответствующей ячейке. При этом Excel автоматически будет превращать их в абсолютные ссылки (в нашем примере $D$2 и $C$2). После нажатия кнопки ОК средство подбора параметров находит приближенное значение корня, которое помещает с ячейку С2. В данном случае оно равно –0,919999. Как выглядит диалоговое окно Результат подбора параметра после успешного завершения поиска решения, показано на (рис.16).

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

Аналогично в ячейках С3 и С4 находим два оставшихся корня.

Они равны 0.20999 и 0.71999.

Чтобы построить график нужно выбрать Мастер диаграмм, График и выбрать вид графика. Нажимаем Далее. Выбираем диапазон данных. Для этого выделяем столбец Y  и нажимаем Enter. Далее выбираем вкладку Ряд и в диапазоне Подписи по оси Х выделяем столбец Х. Нажимаем Далее. Снимаем галочку с Добавить легенду и нажимаем Далее и Готово.

Задания для самостоятельной работы:

Найти корни уравнения:

  1.  
  2.  
  3.  


3. Поиск решения

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

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

"Поиск решения" позволяет использовать одновременно большое количество (в общей сложности до 200) изменяемых ячеек;

"Поиск решения" позволяет задавать ограничения для изменяемых ячеек. Например, при поиске решения, обеспечивающего максимальную прибыль, вы можете задать дополнительные условия, скажем, потребовать, чтобы при этом общий доход находился в диапазоне между 20% и 30% , или чтобы расходы не превосходили 1 000 000 рублей. Подобного рода условия называются ограничениями для решаемой задачи.

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

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

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

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

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

Кроме того, может быть задано некоторое количество ограничений — условий или соотношений, которым должны удовлетворять некоторые из изменяемых ячеек. Например, можно потребовать, чтобы общие затраты не превосходили 100 000 000 рублей или чтобы затраты на рекламную кампанию составляли от 10 до 15 % от общих расходов.

Какие же задачи могут быть сформулированы подобным образом? Круг подобных задач широк. Приведем лишь несколько классических примеров.

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

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

Задача о назначениях. Имеется несколько должностей и соответствующее количество претендентов на эти должности. Назначение i-того претендента на j-тую должность связано с затратами C[i,j]. Требуется распределить претендентов по должностям так, чтобы суммарные затраты были бы минимальны.

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

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

Пример 1. Планирование штатного расписания

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

В январе – 8000

В феврале – 9000

В марте – 8000

В апреле – 10000

В мае – 9000

В июне – 12000.

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

Каждая полностью обученная стюардесса в течение месяца может иметь налет до 150 часов. Авиакомпания в начале января уже имеет 60 опытных стюардесс. При этом ни одну из них не снимают с работы. Установлено также, что приблизительно 10% обучаемых стюардесс по окончании обучения увольняются по каким-либо причинам. Опытная стюардесса обходится авиакомпании в 800, а обучаемая – в $400 в месяц. Необходимо спланировать штат авиакомпании таким образом, чтобы минимизировать издержки за отчетные шесть месяцев.

Построим таблицу. Отведем диапазон ячеек В3:В8 под число новых стюардесс, принимаемых на работу с января по июнь. В ячейку В2 введем число стюардесс, работающих в декабре (рис.17.).

Рисунок

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

=В2  (1)

= D3+0,9*В3   (2)

Формулу (2) скопируем в диапазон D5:D8. В диапазоне Е3:Е8 вычислим налет по месяцам, введя в ячейку Е3 формулу

= D3*$G$12 + B3*$F$12

и протаскивая ее на диапазон Е4:Е8, где в ячейки F12 и G12 введены допустимый налет обучаемой и работающей стюардесс. В диапазоне F3:F8, вычислим затраты по месяцам, введя в ячейку F3 формулу

=D3*$E$12 + B3*$D$12

протаскивая ее на диапазон F3:F8, где в ячейки D12 и E12 введены затраты на обучение и работу стюардесс. Вычислим суммарные затраты за планируемый период в ячейке F9 по формуле

=СУММ(F3:F8)

Выберем команду:

Сервис, Поиск решения.

Заполним открывшееся окно Поиск решения, как показано на (рис.18).

Рисунок

После заполнения окна Поиск решения, надо нажать кнопку , в открывшемся окне, установить параметры:

Рисунок

Результаты расчета оптимального штата стюардесс приведены на рис.20.

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

Рисунок Результаты расчета с помощью средства поиска решений в задаче о планировании штатного расписания

Предположим, что авиакомпания по какой-либо причине решила не брать в июне на обучение новых стюардесс. Тогда в поле Ограничения диалогового окна Поиск решения надо добавить B8=0.Оптимальное решение при таком дополнительном ограничении представлено на (рис.21), из которого видно, что оно приведет к временному повышению текущих затрат.

Рисунок . Решение задачи о планировании штатного расписания в том случае, если набор стюардесс на обучение в июне не производится.

Задание 1. 

  1.  Создать таблицу, отображающую результаты хозяйственной деятельности предприятия.

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

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

Формулы и константы для расчетов:

Объём сбыта = 35хСезонный фактор х Затраты на рекламу + 3000

Доход от оборота = Объём сбыта х Цена.

Себестоимость от реализованной продукции = Объём сбыта х  Себестоимость.

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

Накладные расходы = 15% Дохода от оборота.

Валовые издержки = Затраты на зарплату + Затраты на рекламу + Накладные расходы.

Прибыль = Валовая прибыль - Валовые издержки.

Коэффициент прибыльности = Прибыль / Доход от оборота.

Сезонный фактор: для 1 квартала – 0,9; для 2 квартала -1,1; для 3 квартала -0,8; для 4 квартала -1,2.

Затраты на зарплату: для 1 квартала – 8000р.; для 2 квартала -8000р.; для 3 квартала -9000р.; для 4 квартала -9000р..

Затраты на рекламу для каждого квартала – по 10000р.

Цена – 40 р.; себестоимость – 25 р.

  1.  Отформатировать таблицу: ячейкам, содержащим денежные величины, назначить денежный формат; ячейкам строки Коэффициент прибыльности назначить процентный формат.
  2.  С помощью надстройки Поиск решения определить величину затрат на рекламу, обеспечивающую максимальную прибыль в 1 квартале. Сохранить установки задачи в виде модели. После просмотра результата восстановить исходные значения.
  3.  Определить величину затрат на рекламу для каждого квартала, обеспечивающую максимальную прибыль за год. Сохранить установки задачи в виде модели. После просмотра результата восстановить исходные значения.
  4.  Определить величину затрат на рекламу для каждого квартала, обеспечивающую максимальную прибыль за год при ограничении суммарной величины расходов на рекламу за год 40000 р. Сохранить установки задачи в виде модели. После просмотра результата восстановить исходные значения.
  5.  Изменить ограничение, установив верхний предел расходов на рекламу за год 50000 р. Сохранить установки задачи в виде модели. После просмотра результата восстановить исходные значения.
  6.  Сохранить в качестве сценария первоначальные значения величин затрат на рекламу в каждом квартале.
  7.  Загрузить каждую модель и создать отчеты по результатам поиска решения.
  8.  Восстановить первоначальные значения с помощью первого сценария.

1 итерации, т. е. повторяющиеся циклические вычисления

2 Это число должно по возможности не слишком отличаться от ожидаемого корня уравнения (чтобы итерации сходились быстрее) и при этом должно находиться в области допустимых значений — годится все-таки не совсем произвольное число. Например, в данном случае ноль или отрицательные числа не годятся — они не входят в ОДЗ.

3 Главное, не забудьте о сбалансированности —  а то может оказаться, что "оптимальное" меню состоит из 3 литров уксуса в день…

PAGE  9


 

А также другие работы, которые могут Вас заинтересовать

19267. Физическая постановка задачи, алгоритм метода Монте-Карло в задачах переноса излучений. Генератор случайных чисел. Получение локальных и интегральных характеристик поля нейтронов и гамма-квантов 38.5 KB
  Лекция 15. Физическая постановка задачи алгоритм метода МонтеКарло в задачах переноса излучений. Генератор случайных чисел. Получение локальных и интегральных характеристик поля нейтронов и гаммаквантов. 15.1. Особенности метода МонтеКарло. Метод МонтеКарло п
19268. Понятие информационной системы. Классификация ИС. Понятие проекта и проектирования 254.06 KB
  Лекция 1. Понятие информационной системы. Классификация ИС. Понятие проекта и проектирования. Введение в методологию построения информационных систем. Объекты и субъекты проектирования ИС. Классификация методов и средств проектирования ИС. Основные задачи курса 1.1. ...
19269. Понятие жизненного цикла и модели жизненного цикла. Каскадная модель ЖЦ. Поэтапная модель с промежуточным контролем 311.49 KB
  Лекция 2. Понятие жизненного цикла и модели жизненного цикла. Каскадная модель ЖЦ. Поэтапная модель с промежуточным контролем. Спиральная модель ЖЦ. Процессы ЖЦ ПО. Rapid Application DevelopmentRAD. Extreme Programming XP. Rational Unified Process RUP. Microsoft Solution Framework MSF. Custom Development Method методика Oracle. 2.1...
19270. Каноническое проектирование. Типовое проектирование ИС. Параметрически-ориентированное проектирование. Модельно-ориентированное проектирование 280.39 KB
  Лекция 3. Каноническое проектирование. Типовое проектирование ИС. Параметрическиориентированное проектирование. Модельноориентированное проектирование. 3.1. Каноническое проектирование Организация канонического проектирования ИС ориентирована на использов...
19271. Работа с матрицами. Формирование матриц третьего порядка 17.02 KB
  В ходе лабораторной работы были сформированы две матрицы третьего порядка, с ними были выполнены указанные в задании операции. Результаты выполнения команд представлены в коде
19272. Системный подход к проектированию ИС. Структурные методы анализа и проектирования ИС. Объектно-ориентированная методика проектирования ИС 228.76 KB
  Лекция 4. Системный подход к проектированию ИС. Структурные методы анализа и проектирования ИС. Объектноориентированная методика проектирования ИС. Cравнение объектноориентированного и структурного подхода. Модели деятельности предприятия. Проведение обследования.
19273. Средства структурного анализа. Метод функционального моделирования IDEF0. Метод моделирования процессов IDEF3 255.24 KB
  Лекция 5. Средства структурного анализа. Метод функционального моделирования IDEF0. Метод моделирования процессов IDEF3. Моделирование потоков данных Модели сущностьсвязь ERмодели. Графические нотации ERмодели 5.1. Метод функционального моделирования IDEF0 Метод IDEF0 с...
19274. Методология ARIS. Диаграммы переходов состояний (State Transition Diagram, STD). Структурные карты Константайна 196.42 KB
  Лекция 6. Методология ARIS. Диаграммы переходов состояний State Transition Diagram STD. Структурные карты Константайна. Структурные карты Джексона. Метод EricssonPenker. Метод моделирования используемый в технологии Rational Unified Process 6.1. Методология ARIS Методология ARIS реализует принцип...
19275. История UML Описание UML. Сущности UML. Отношения UML. Диаграммы UML. Расширения языка UML. Диаграммы классов 290.15 KB
  Лекция 7. История UML Описание UML. Сущности UML. Отношения UML. Диаграммы UML. Расширения языка UML. Диаграммы классов. Диаграммы использования usecase диаграммы прецедентов. Диаграмма последовательности. Диаграмма кооперации. Диаграмма состояний. Диаграмма деятельности. ...