На рабочем листе №4 построить таблицу значений функции согласно варианта задания и ее график.
Определите среднее, минимальное и максимальное значение функции и вывести эти данные на графике.
Используя логическую формулу, вычислить сумму значений функций, если среднее, минимальное и максимальное значения имеют одинаковые знаки и произведение в противном случае.
Произвольной ячейке присвоить имя и сгенерировать в ней случайное число. В таблице значений функции добавить еще один столбец, полученный умножением у на случайное число. Добавить на графике функции второй график, соответствующий полученному столбцу данных.
Провести статистический анализ с использованием функций 1-6 методических указаний к работе.
Построить гистограмму распределения данных.
Таблица 5.1 – Варианты задания № 3
| Y = excos2 2x+/x/ | -1 <= x <=1.5, Dx = 0.2 | ||
| Y = /x+ex/+tg3x*lg x2 | -10 <= x <= 10, Dx = 1 | ||
| Y = (x3-cos x2)/(e4x)-tg x | -5 <= x <= 5, Dx = 0.75 | ||
| Y = /x+ex/1/2 +ln/xsin x/ | -1.8 <= x <= 1.5, Dx = 0.4 | ||
| Y = xcos x/(/x+ex/+tg x) | -5.2 <= x <= 1.5, Dx = 0.7 | ||
| Y = lg x2 esin 2x /lg3x | 1 <= x <= 100, Dx = 5 | ||
| Y = ex+2 ln2 2x/(x+10ex) | 1 <= x <= 50, Dx = 2.5 | ||
| Y = /sin 2x+tg 3x/1/2+e4x | -2.5 <= x <= 1.5, Dx = 0.4 | ||
| Y = 1-/sin x/+eln 2x+lg x | 1 <= x <= 10, Dx = 0.1 | ||
| Y = (-1)x esin x cos x2 | 1 <= x <= 15, Dx = 1 | ||
5.3 Контрольные вопросы
Для чего предназначен Пакет анализа и каков порядок доступа к его инструментам?
В задании 2 своего варианта вычислите коэффициент вариации.
В чем заключаются особенности построения гистограммы распределения данных?
Напишите логическую формулу, которая выводит текстовое сообщение ”Вычислена сумма” или ”Вычислено произведение” в зависимости от того, что было вычислено на рабочем листе в п. 3 задания 1.
Задание № 4 - Решение задач линейного программирования
Теоретические сведения
Таблица данных - это интервал ячеек, показывающий результаты подстановки различных значений в одну или более формулы. Есть два типа таблиц данных: таблицы данных с одним параметром и таблицы данных с двумя параметрами. В случае таблицы данных с одним параметром вводятся различные значения для одной переменной и рассматриваются результаты одной или более формул. В случае таблицы данных с двумя параметрами вводятся различные значения для двух переменных и рассматривается результат одной формулы.
Сценарий - это именованный набор изменяемых значений, представляющих некоторое множество параметров модели “что-если”.Модель “что-если” - это рабочий лист, на котором различным параметром (например, число покупателей, затраты и т. д.) можно задавать различные значения с тем, чтобы определить их влияние на другие параметры (например, чистый доход), вычисляемые по формулам, зависящим от этих параметров. Для создания сценария необходимо выполнить следующие действия. Из меню Сервис выберите команду Сценарии. В открывшемся окне Диспетчер сценариев нажмите кнопку Добавить. Введите имя сценария. В поле Изменяемые ячейки задайте те ячейки, которые Вы собираетесь изменить. Нажмите кнопку ОК. В открывшемся диалоговом окне Значения сценария для каждой изменяемой ячейки ведите новое значение или формулу. Нажмите кнопку ОК. Нажмите кнопку Закрыть.
Исходную модель “что-если” желательно сохранить в виде сценария, присвоив ему, например, имя Стартовое значение. В противном случае при задании новых изменяемых ячеек исходные данные будут потеряны. Для просмотра сценария необходимо воспользоваться кнопкой Показать в окне Диспетчера сценариев. При этом текущие значения изменяемых ячеек на рабочем листе заменяется значениями, определенными в сценарии. Щелкнув кнопку Итоги в диалоговом окне Диспетчер сценариев, можно получить итоговый отсчет на отдельном рабочем листе, показывающий влияние разных сценариев на одну или несколько результирующих ячеек. В случаях, когда необходимо найти значение параметра, приводящее к заданному результату, используется команда Подбор параметра из меню Сервис.
Задача линейного программирования в общем случае формулируется следующим образом:
Определить максимум (минимум) целевой функции F max(min) при заданной системе ограничений (2) и граничных условий (3):
Fmax(min) =A1*X1+A2*X2+...+An*Xn (1)
{B11*X1+B12*X2+...+B1n*Xn<=C1
{B21*X1+B22*X2+...+B2n*Xn<=C2 (2)
{Bn1*X1+Bn2*X2+...+Bnn*Xn<=Cn
Xi>=0, i=1...n (3)
Программа Поиск решения является инструментом оптимизации. С помощью этой программы можно найти оптимальное или заданное значение некоторой ячейки путем подбора значений нескольких ячеек, удовлетворив нескольким граничным условиям.
Целевая ячейка — это ячейка, для которой нужно найти максимальное, минимальное или заданное значения.
Изменяемые ячейки — это ячейки, от которых зависит значение целевой ячейки. Целевая ячейка должна содержать формулу, прямо или косвенно зависящую от изменяемых ячеек. Поиск решения подбирает значения изменяемых ячеек до тех пор, пока не будет найдено решение.
Ограничение — это условие, накладываемое на некоторую ячейку. Ограничения могут быть наложены на любые ячейки таблицы, включая целевую ячейку и изменяемые ячейки.
Чтобы запустить процедуру поиска решения, надо:
- в меню Сервис выбрать команду Поиск решения. Откроется диалоговое окно Поиск решения (рисунок 6.1);
- в поле Установить целевую ячейку ввести ссылку на ячейку, в которой нужно получить максимальное, минимальное или заданное значения;
- в поле Изменяя ячейки ввести ссылки на изменяемые ячейки. (Если щелкнуть по кнопке Предположить, то Поиск решения самостоятельно определит изменяемые ячейки);
- для задания ограничений щелкнуть по кнопке Добавить;
Рисунок 6.1 - Диалоговое окно Поиск решения
- в открывшемся диалоговом окне (рисунок 6.2) следует в поле Ссылка на ячейку ввести ссылку на ячейку, содержащую формулу, которая определяет ограничение; формула должна прямо или косвенно зависеть от одной или нескольких изменяемых ячеек;
- во втором поле выбрать оператор ограничения (>,<,= и т. д.);
- в поле Ограничение ввести значение ограничения;
Рисунок 6.2 - Диалоговое окно Добавление ограничения
- для задания следующего ограничения щелкнуть по кнопке Добавить;
- когда все ограничения будут заданы, щелкнуть по кнопке ОК, чтобы вернуться в диалоговое окно Поиск решения;
- изменять и удалять ограничения можно с помощью кнопок Изменить и Удалить;
- с помощью кнопки Параметры можно задать максимальное время решения, предельное число итераций, относительную погрешность, допустимое отклонение, сходимость, метод поиска.
Если известно, что решаемая задача линейная (т. е. зависимости между переменными линейны), то следует включить режим Линейная модель: процесс решения значительно ускорится. Для возврата в диалоговое окно Поиск решения щелкнуть по кнопке ОК. Для инициализации процедуры поиска решения щелкнуть по кнопке Выполнить. Полученные результаты будут выведены на рабочий лист. После завершения процедуры решения в диалоговом окне Результаты поиска решения можно выполнить один из следующих вариантов:
сохранить найденное решение или восстановить исходные значения на рабочем листе;
сохранить параметры поиска решения в виде модели;
сохранить решение в виде сценария;
просмотреть любой из встроенных отчетов.
Текущие установочные параметры для поиска решения можно сохранить в виде модели. Для этого надо в диалоговом окне Параметры поиска решения щелкнуть по кнопке Сохранить модель и указать на рабочем листе область для сохранения модели (можно указать только верхнюю ячейку области). При сохранении модели запоминаются целевая ячейка, изменяемые ячейки, ограничения и параметры поиска решения.
Чтобы впоследствии загрузить модель, надо щелкнуть по кнопке Загрузить модель в диалоговом окне Параметры поиска решения. (Диалоговое окно Параметры поиска решения открывается при щелчке по кнопке Параметры в диалоговом окне команды Сервис >• Поиск решения).
Найденные решения (значения изменяемых ячеек) можно сохранить в качестве сценария. Для этого нужно выполнить следующие действия. В диалоговом окне Результаты поиска решения выбрать Сохранить сценарий. В поле Название сценария ввести имя сценария. Просмотреть сценарии можно с помощью команды Сервис > Сценарии.
С помощью программы Поиск решения можно создать три типа отчетов по результатам, полученным при успешном завершении процедуры решения.
Каждый отчет создается на отдельном листе текущей рабочей книги.
Для создания отчета надо в диалоговом окне Результаты поиска решения выбрать нужный тип отчета в поле Тип отчета. Можно выбрать сразу несколько типов (при выделении нескольких строк используется клавиша <Ctrl>).
Типы отчетов:
результаты (отчет содержит целевую ячейку, список изменяемых ячеек, их исходные и конечные значения, ограничения и сведения о них);
устойчивость (отчет содержит сведения о степени зависимости модели от изменений величин, входящих в формулы, применяемые в задаче (формулы модели и формулы ограничений);
пределы (выводится целевая ячейка и ее значение, а также список изменяемых ячеек, их значений, нижних и верхних пределов и целевых результатов).
Модель линейного программирования дает возможность определить наиболее выгодную производственную программу выпуска нескольких видов продукции при заданных ограничениях на ресурсы.
Рассмотрим применение процессора Excel для решения ЗЛП.
Пример - МП выпускает товары Х1,Х2,Х3,Х4, получая от реализации каждого прибыль в 60,70,120,130 рублей соответственно. Затраты на производство приведены в таблице.
Определить:
- максимум прибыли в зависимости от оптимального распределения затрат;
- минимум ресурсов, необходимых для получения максимальной прибыли.
Таблица 6.1 – Таблица затрат
| Затраты | X1 | X2 | X3 | X4 | Всего |
| Трудовые | |||||
| Сырьевые | |||||
| Финансы |
Составим математическую модель процесса по описанию задачи:
60Х1+70Х2+120Х3+130Х4 = Fmax – целевая функция прибыли.
{Х1+Х2+Х3+Х4 <= 16
{6Х1+5Х2+4Х3+Х4 <= 110 - ограничения модели
{4Х1+6Х2+10Х3+13Х4 <= 100
Хj >=0 - граничные условия модели, так как количество производимых товаров не может быть отрицательной величиной.
Решение задачи средствами Excel состоит из 3 этапов:
создание формы для ввода условий задачи, ввод в неё исходных данных и зависимостей из математической модели;
ввод данных из формы в окно Excel Поиск решения из меню Сервис;
задание параметров поиска и решение задачи.
1 этап. Создание формы
Таблица 6.2 – Форма данных задачи
| A | B | C | D | E | F | G | H | |
| Переменная | X1 | X2 | X3 | X4 | Формула | Знак | Св.член | |
| Значение | ||||||||
| Коэф. ЦФ | =СУММПРОИЗВ (В$2:Е$2;В3:Е3) | max | ||||||
| Трудовые | =СУММПРОИЗВ (В$2:Е$2;В4:Е4) | <= | ||||||
| Сырьевые | =СУММПРОИЗВ (В$2:Е$2;В5:Е5) | <= | ||||||
| Финансы | =СУММПРОИЗВ (В$2:Е$2;В6:Е6) | <= |
Запись в ячейки В3:Е3 коэффициентов целевой функции F (1), в В4:Е6 коэффициентов из системы ограничений (2) и в ячейки Н4:Н6 - свободных членов из системы (2).
Ввод формул с помощью fx - Мастера функций.
Для ввода формулы в целевую ячейку (целевой функции): щелкнуть левой клавишей мыши по ячейке F3, затем по значку Мастера функций fx на панели инструментов, в появившемся окне "Мастер функций, Шаг 1" выбрать категорию "Математические", далее выбрать функцию СУММПРОИЗВ, нажать клавишу ОК, в окне "Мастер функций Шаг 2" в поле Массив 1 ввести с клавиатуры В2:Е2 (ячейки, в которых будут варьироваться Х1..Х4), в поле Массив 2 ввести В3:Е3 (коэффициенты целевой функции ЦФ).
Примечание. Можно вводить В2:Е2 не с клавиатуры, а поставить курсор в окно Массив 1, а затем протащить курсор при нажатой левой клавише мыши по ячейкам В2:Е2, имена ячеек сами запишутся в окно. Аналогично поступить с полем Массив 2.
Нажать клавишу ОК, в ячейку F3 запишется формула 60*Х1+70*Х2+120*Х3+ 130*Х4
в виде СУММПРОИЗВ(В2:Е2;В3:Е3). Чтобы не вводить формулы в другие ячейки, необходимо изменить тип адресации для ячеек В2:Е2 с относительной на абсолютную $B$2:$E$2, установив курсор перед нужным адресом B2 и нажав функциональную клавишу F4, затем повторить эти действия для адреса E2. Формула примет следующий вид:
СУММПРОИЗВ($В$2:$Е$2;В3:Е3)
После внесенных изменений необходимо скопировать формулу в ячейки F4:F6 c помощью маркера заполнения. Для этого необходимо выделить ячейку F3, содержащую нужную формулу, установить указатель мыши на черный квадратик в правом нижнем углу ячейки (он примет форму черного крестика) и протащить с помощью левой кнопки мыши на весь требуемый диапазон.
В результате копирования мы увидим следующие формулы:
- в ячейке F4 - СУММПРОИЗВ($В$2:$Е$2;В4:Е4),
- в ячейке F5 - СУММПРОИЗВ($В$2:$Е$2;В5:Е5),
- в ячейке F6 - СУММПРОИЗВ($В$2:$Е$2;В6:Е6).
2 этап. Заполнение окна Поиск решения
Выбрать в пункте меню Сервис команду Поиск решения, поставить курсор в поле целевой функции, выделить ячейку F3 в форме (или ввести F3 с клавиатуры), поставить переключатель в положение "Максимальному значению" (см. Рис. 23).
В поле "Изменяя ячейки" ввести $В$2:$Е$2 (с клавиатуры или протащив мышью).
Нажать клавишу "Добавить", в окне "Добавление ограничения» в поле "Ссылка на ячейку" ввести F4, выбрать через "стрелка вниз" знак "<=", в поле справа ввести Н4 (Рис. 24).
Аналогично через "Добавить" ввести F5<=H5, F6<=H6 для системы ограничений (2), а также B2>=0, C2>=0, D2>=0 и Е2>=0 для граничных условий Хi>=0.
Также необходимо добавить ограничения для получения целочисленных величин по количеству товаров: B2=цел, C2=цел, D2=цел и Е2=цел.
После ввода последнего граничного условия вместо "Добавить" нажать клавишу ОК, появится окно "Поиск решения".
Для изменения или удаления ограничений и граничных условий используются клавиши Изменить, Удалить.
3 этап. Параметры поиска.
В окне "Поиск решения" нажать клавишу "Параметры", выбрать по умолчанию Максимальное время - 100 с.(может быть до 2^15=32767 c.> 4 час.), число итераций- 100 (для большинства задач это количество просчётов подходит с большим запасом), установить флажок в строке "Линейная модель", нажать ОК, в появившемся окне Поиск Решения нажать Выполнить (рисунок 6.3).
Рисунок 6.3 - Диалоговое окно Параметры поиска решения
Результаты поиска решения в таблице 6.3:
Таблица 6.3 – Итоги поиска решения
| A | B | C | D | E | F | G | H | |
| Переменная | X1 | X2 | X3 | X4 | Формула | Знак | Св.член | |
| Значение | ||||||||
| Коэф. ЦФ | max | |||||||
| Трудовые | <= | |||||||
| Сырьевые | <= | |||||||
| Финансы | <= |
т. е. оптимальный план Х(Х1,Х2,Х3,Х4)=(10,0,6,0)
при минимальном использовании ресурсов
-Трудовые - 16 (У1)
-Сырьевые - 84 (У2)
-Финансы - 100 (У3)
даёт максимум прибыли F в 1320 руб.
Вывод: Максимальная прибыль F в 1320 руб. получается при выпуске только товаров Х1 и Х3 в количестве 10 и 6 штук соответственно, товары Х3 и Х4 выпускать не нужно (это приведёт к снижению прибыли). Трудовые (У1) и финансовые (У3) ресурсы используются полностью, по сырьевым ресурсам (У2) есть запас в 110-84=26 ед.
Кроме того, это означает, что изменение трудовых (У1) и финансовых (У3) ресурсов приведёт к изменению прибыли F, а изменение сырьевых ресурсов (У2) - нет. Разности между плановыми ресурсами и использованными являются двойственными переменными У1, У2 и У3 сопряжённой задачи линейного программирования. В данном случае У1=У3=0, а У2=26 ед. Таким образом, ресурс У2 можно уменьшить на 26 ед., тогда план по сырью тоже будет оптимальным.