Прикладные системы обработки данных

 

	Стоимость перевозки единицы продукции				Объем   производства
	5	8	8	8	14
	6	5	7	9	19
	7	4	12	4	15
	8	7	15	6	21
	7	9	18	12	9
Объем   потребления	12	21	22	31

 

2.1.1 Анализ задачи

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

14+19+15+21+9 < 12+21+22+31 (78 < 86)

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

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

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

 

 

 

 

2.1.2 Решение задачи (Excel)

Введем информацию о стоимости перевозки единицы продукции в ячейки B4:F8 (столбцы соответствуют пунктам потребления, строки – пунктам производства).

Введем информацию о стоимости перевозки единицы продукции из фиктивного пункта производства в ячейки F4:F8.

Рассчитаем объем производства в фиктивном пункте производства. В ячейку F9 введем формулу:

=СУММ(B10:E10)-СУММ(F4:F8)

Таблица с исходными данными в Excel имеет вид, представленный на рисунке 26.

 

 

Рисунок 26 − Таблица с исходными данными

 

Создадим в Excel таблицу вида, представленного на рисунке 27.

 

 

Рисунок 27 − Промежуточная таблица

 

 

Введем формулу вычисления суммарного количества продукции, требуемого от пункта производства A в ячейку F16:

=СУММ($B16:$E16)

Копируем формулы из ячейки F16 в ячейки F17:F21.

Вводим формулу вычисления в ячейку В22:

=СУММ(B$16:B$21)

Копируем формулу из ячейки В22 в ячейки С22:E22.

Результаты выполнения этих действий представлены на рисунке 28.

 

 

Рисунок 28 − Промежуточная таблица

 

Целевая функция – это общие затраты на перевозку необходимого количества продукции согласно плана перевозок.

Зададим целевую  функцию. Для этого используем функцию  
СУММПРОИЗВ(массив1, [массив2], [массив3], …), которая перемножает соответствующие элементы заданных массивов и возвращает сумму произведений.

В ячейку B24 введем формулу:

=СУММПРОИЗВ(B4:E9;B16:E21)

Далее при помощи надстройки «Поиск решения» оптимизируем план перевозки. Запускаем надстройку «Поиск решения» (Данные / Анализ / Поиск решения).

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

В качестве целевой функции выбираем ячейку B24.

Выбираем оптимизацию целевой функции до минимума.

В качестве ячеек переменных устанавливаем диапазон ячеек B16:E21.

Далее установим ограничения. Для этого нажимаем на кнопку «Добавить» и в окне «Добавление ограничения» устанавливаем нужные значения.

Выбираем метод решения «Поиск решения линейных задач симплекс-методом».

Результаты выполнения этих действий представлены на рисунке 29.

 

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

 

Нажимаем кнопку «Найти решение». Оптимизированный план перевозки представлен на рисунке 30.

 

 

Рисунок 30 − Оптимизированный план перевозки

 

2.1.3 Решение задачи (MathCAD)

Щелкнем по свободному месту в рабочем документе. Решаем задачу посредством последовательного ввода.

Специальной переменной ORIGIN присваиваем значение 1. Значением ORIGIN является номер первого элемента строки или столбца в матрице. По умолчанию ORIGIN=0.

В меню Math выбираем строку Options или ORIGIN:=1.

Вводим исходные данные задачи в матричной форме (рисунок 31).

 

 

Рисунок 31 − Исходные данные задачи в матричной форме

 

Введем линейную целевую функцию (рисунок 32).

 

 

Рисунок 32 − Линейная целевая функция

 

Зададим начальные значения переменных задачи (рисунок 33).

 

 

Рисунок 33 − Начальные значения переменных задачи

 

Введем ограничения задачи в математической форме (рисунок 34).

 

 

Рисунок 34 − Ограничения задачи в математической форме

 

Определяем оптимальное решение задачи с помощью встроенной функции Minimize() (рисунок 35).

 

 

Рисунок 35 − Оптимальное решение задачи

 

Анализируя данные, полученные при решении задачи посредством Microsoft Excel и MathCad, можно судить о правильности решения транспортной задачи.

 

2.2 Задача 2

Построить в одной системе координат при x Î [-1,6; 2,2] графики следующих двух функций:

y = 2sin(2px)·cos(px)+sin(3px),

z = cos(2px)·sin2(px)-cos(4px).

 

2.2.1 Решение задачи (Excel)

Для построения графика функции необходимо сперва построить таблицу ее значений при различных значениях аргумента. Аргумент по условию задачи изменяется в диапазоне от -1,6 до 2,2 с шагом 0,1.

Создадим в Excel таблицу вида, представленного на рисунке 36.

 

 

Рисунок 36 − Промежуточная таблица

 

Введем в ячейку B2 формулу:

=2*SIN(2*ПИ()*$A2)*COS(ПИ()*$A2)+SIN(3*ПИ()*$A2)

В результате вычисления получим -0,22451.

Заполним весь столбец значений Y. Растянем формулу до ячейки В40 включительно.

Введем в ячейку C2 формулу:

= COS(2*ПИ()*$A2)*(SIN(ПИ()*$A2))^2-COS(4*ПИ()*$A2)

В результате вычисления получим -1,04078.

Заполним весь столбец значений Z. Растянем формулу до ячейки C40 включительно.

 

 

 

Рисунок 37 − Результат вычислений

 

Для построения графика функции выделим диапазон ячеек B2:C4. Вызовем мастера диаграмм Вставка / Диаграммы / График. Выбираем тип диаграммы «График с маркерами».

Далее вызываем диалоговое окно «Выбор источника данных», которое заполним в соответствии с рисунком 39.

 

 

Рисунок 39 − Выбор источника данных

 

Изменяем подписи горизонтальной оси. В качестве диапазона подписей оси выбираем диапазон ячеек A2:A40. Изменим элементы легенды (ряды) «Ряд 1» и «Ряд 2», нажав на кнопку «Изменить». В качестве имени ряда вместо «Ряд 1» укажем «функция Y», а вместо «Ряд 2» - «функция Z» (рисунок 40).

 

 

Рисунок 40 − Изменение ряда

 

Нажимаем «OK» и видим результат построения графиков функций Y и Z (рисунок 41).

 

 

Рисунок 41 − Графики функций Y и Z

 

 

2.2.2 Решение задачи (MathCAD)

 

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

 

 

Рисунок 42 − Диапазон значений x

 

Введем функции y(x),  z(x), графики которых следует построить (рисунок 43).

 

 

Рисунок 43 − Функции y(x) и z(x)

 

Для построения графиков функций Y и Z выберем на панели инструментов «График». Выберем график X-Y. Появится поле для построения графика. В нижнюю помеченную позицию введем имя аргумента x, затем нажмем на помеченную позицию с левой стороны, и введем y(x).

Графики функций представлены на рисунке 44.

.

 

 

Рисунок 45 − Графики функций Y и Z

 

 

2.3 Задача 3

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

 

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

 

2.3.1 Решение задачи (Excel)

Внесем в таблицу Excel исходные данные (рисунок 46).

 

 

Рисунок 46 − Исходные данные

 

Создадим в Excel таблицу в виде, представленном на рисунке 47.

 

 

Рисунок 47 − Промежуточная таблица

 

Введем следующие формулы:

• в ячейку B9: =B3*$B$8+C3*$C$8+D3*$D$8+E3*$E$8
• в ячейку B10: =B4*$B$8+C4*$C$8+D4*$D$8+E4*$E$8
• в ячейку B11: =B5*$B$8+C5*$C$8+D5*$D$8+E5*$E$8
• в ячейку B12: =МИН(B9:B11)

Вызовем надстройку «Поиск решения» и зададим необходимые условия:

• выберем целевую функцию (ячейка B12);
• выберем направление оптимизации функции (до максимума);
• в поле «Изменяя ячейки» внесем диапазон ячеек B8:E8;
• добавим необходимые ограничения;
• сделаем переменные без ограничений неотрицательными;
• выберем метод решения (поиск решения нелинейных задач методом ОПГ).

Параметры поиска решения показаны на рисунке 48.

 

 

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

 

В результате появится решение данной оптимизационной задачи (рисунок 49).

 

Рисунок 49 − Решение задачи

 

Оптимальный вариант использования денег – $396.55 на первый вариант и $103.45 на четвертый вариант.

 

2.3.2 Решение задачи (MathCAD)

Введем функции вычисления исходов игры и целевую функцию (рисунок 50).

 

2.4 Задача 4

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

 

2.4.1 Анализ задачи

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

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

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

Fv = Sv · ( 1 + (R / Ny) )Nd, где

Fv - итоговая сумма;

Sv - начальная сумма;

R - годовая процентная ставка;

Ny - количество периодов капитализации в году;

Nd - количество периодов капитализации за весь период вклада.

 

2.4.2 Решение задачи (Excel)

Внесем в таблицу Excel исходные данные (рисунок ).

 

 

Рисунок − Исходные данные

 

Для расчета итоговой суммы воспользуемся функцией БС(ставка,кпер,плт,[пс],[тип]).

Функция БС() имеет аргументы, указанные ниже:

1. ставка - обязательный аргумент, процентная ставка за период;
2. кпер - обязательный аргумент, общее количество периодов платежей по аннуитету;
3. плт - обязательный аргумент, выплата, производимая в каждый период; это значение не может меняться в течение всего периода выплат. Если он опущен, аргумент «пс» является обязательным;
4. пс - обязательный аргумент, приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей. Если аргумент «пс» опущен, предполагается значение 0. В этом случае аргумент «плт» является обязательным;
5. тип - необязательный аргумент. Число 0 (в конце периода) или 1 (в начале периода), обозначающее срок выплаты. Если аргумент «тип» опущен, предполагается значение 0.