контрольные работы

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

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

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

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

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

Просмотр сценария

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

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

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

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

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

активного сценария остаются в ячейках листа.

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

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

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

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

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

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

1.3MathCad: реализация линейной регрессии общего вида

С помощью системы Mathcad можно проводить наиболее распространенные статистические расчеты с данными.

Одной из широко распространенной задачей обработки данных является представление их совокупности некоторой функцией у(х) [7].

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

Чаше всего используется линейная регрессия, при которой функция у(х) имеет вид y(x)= a + b•х и описывает отрезок прямой.

К линейной регрессии можно свести многие виды нелинейной регрессии при зависимостях вида у(х). Для проведения линейной регрессии в систему встроен ряд приведенных ниже функций [8]:

corr(VX, VY)— возвращает скаляр — коэффициент корреляции Пирсона;

intercrpt(VX, VY) — возвращает значение параметра а (смещение линии регрессии по вертикали);

slope(VX, VY) — возвращает значение параметра b (угловой коэффициент линии регрессии).

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

В Mathcad реализована возможность выполнения линейной регрессии общего вида.

При ней заданная совокупность точек приближается функцией вида:

F(x, K1, K2, ..., Kn)=K1•F1(x)+K2•F2(x)+...+Kn•Fn(x)

Таким образом, функция регрессии является линейной комбинацией функций F1(x), F2(х), ..., Fn(x), причем сами эти функции могут быть нелинейными. Для реализации линейной регрессии общего вида используется функция linfit(VX, VY, F). Она возвращает вектор коэффициентов линейной регрессии общего вида К, при котором среднеквадратичная погрешность приближения ―облака‖ исходных точек, координаты которых хранятся в векторах VX и VY, оказывается минимальной. Вектор F должен содержать функции F1(x), F2(x), ..., Fn(x), записанные в символьном виде [4].

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

2.Практическая часть

2.1Решения средствами Microsoft Exsel

Задача 1

Линейная оптимизационная задача. Требуется распределить имеющиеся денежные средства по четырѐм альтернативным вариантам. Игра имеет три исхода. В таблице приведены размеры выигрыша (или проигрыша) на каждый доллар, вложенный в соответствующий альтернативный вариант, для каждого из трѐх исходов. У игрока имеется 500$, причѐм их использовать можно только один раз. Точный исход игры заранее не известен. Учитывая эту неопределенность, распределить эти деньги так, чтобы максимизировать минимальную отдачу этой суммы.

Таблица – возможные выигрыши и проигрыши

	Выигрыши и проигрыши на каждый доллар, вложенный
Исход		на данный момент
	1	2	3	4
1	-3	4	-7	15
2	5	-3	9	4
3	3	-9	10	-10

Решение:

1)Оформим таблицу, содержащую исходные данные.

-шапка таблицы: в ячейку А19 введем общее название для столбца «Исход», в ячейку В19 название нескольких столбцов «Выигрыш и проигрыш на каждый доллар», в ячейки В20:Е20 номер альтернативного вклада. В ячейки А21:А23 номер исхода. Далее в ячейки В21:Е21 водим значения выигрыша или проигрыша на один вложенный доллар. После чего вводим значения и для исхода2 и для исхода 3.

. 2) Далее создадим таблицу переменных и ограничений:

Для создания таблицы переменных выделим ячейки из области А27:Е27 и объединим их, записав в ячейке «переменные». Аналогично выделим и объединим ячейки G31:I31 и запишем в ячейке «ограничения».

Далее:

-в ячейку А28 введем фразу «значение»;

-для введения ограничительных функций сделаем таблицу: в ячейке G31 запишем фразу «левая часть», в ячейке Н31 – фразу «знак», в ячейке I31

– фразу «правая часть».

Затем выделим ячейки G25:Н25и после их объединения запишем в них фразу «направление функции).

В указанные ячейки внесем формулы определения выигрыша в каждом конкретном исходе. Так в ячейку G26 запишем: =СУММПРОИЗВ(B21:E21;B28:E28); в ячейку G27 =СУММПРОИЗВ(B22:E22;B28:E28); в ячейку G 28

=СУММПРОИЗВ(B23:E23;B28:E28).

3) Коэффициенты целевой функции:

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

4)Определим целевую функцию.

-в ячейке I26 введем искомую целевую функцию, подлежащую максимизации. В нашем случае: =МИН(G26:G28)/

Таким образом, на листе MS Excel должны быть введены данные и формулы, как на рисунке ниже.

Далее при помощи команды меню <Сервис>-<Поиск решения> вводим следующие данные в диалоговое окно данного сервиса:

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

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

Достигать максимального минимума значения в искомой ячейке будем путем изменения целевых ячеек В28: Е28.

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

Для верного распределения денег по альтернативным вариантам введем ограничение G32=I32 т.е. у игрока имеется 500$, которые он может использовать только 1 раз.

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

Тогда получим оставшиеся ограничения:

G33>=I33; G34<=I34; G35>=I35; G36<=I36; G37>=I37; G38<=I38; G39>=I39; G40<=I40.

Выполняем введенные данные нажатием на кнопку «Выполнить», и получаем оптимальное решение данной задачи, представленное ниже

Задача 2.

Условие:

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

неделя	1	2	3	4	5	6	7	8	9
количество	22	33	44	71	21	22	33	31	23

Решение:

На новом листе в ячейках B11, С11 запишите соответственно X, Y. В ячейки B12, С12 вводим соответствующие данные, как показано на рисунке.

Выделяем мышкой диапазон ячеек С12:С20 и вызоваем Мастер диаграмм через Вставка/Диаграмма... Выбираем среди стандартных диаграмм График, затем нажмите кнопку Далее, проверяем флажок в поле Ряды в столбцах, затем переходим на вкладку Ряд. В строке Имени присвойте для Ряда 1 имя Значение Y.

Задаѐм подписи по Y: = $С$12:$С$20.

Корректируем значения столбца X.

Выделяем на графике ряд значений Y, выбираем закладку Макет, затем нажимаем на меню Линия тренда, выбираем Дополнительные параметры линии тренда…

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

Вызовите функцию Данные/Анализ данных... В появившимся окне выберите пункт Регрессия. На появившимся контекстном меню отражены основные параметры линейной модели, выбираем необходимые и нажимаем ОК.

Коэффициенты уравнения модели указанные на графике совпадают с результатами регрессионного анализа, проведенного средствами Excel и показанного на Листе1.