Контрольная работа по "Программированию"

Можно отдельно также выделить использование баз данных, для  хранения и удобства использования  экономической информации, которые  также позволяют производить  некоторые операции по работе с данными, и создавать необходимые отчеты и документацию. Среди наиболее ярких  представителей систем управления базами данных можно отметить: Lotus Approach, Microsoft Access, Borland dBase, Borland Paradox, Microsoft Visual FoxPro, Microsoft Visual Basic, а также баз данных Microsoft SQL Server и Oracle, используемые в приложениях, построенных по технологии «клиент- сервер».

 

3. Поиск оптимальных решений средствами Excel: планирование штатного расписания.

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

На рис. 3.1 показан рабочий лист, который позволяет провести элементарный анализ потребности в работниках.

Рис. 3.1. Модель планирования штатного расписания

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

Лист состоит из следующих  частей:

День. В столбце В находятся сокращенные названия дней недели.

 

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

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

D3:              =ЕЗ+Е9+Е8+Е7+Е6

D4:              =Е4+Е3+Е9+Е8+Е7

D5:              =Е5+Е4+Е3+Е9+Е8

D6:              =Е6+Е5+Е4+Е3+Е9

D7:              =Е7+Е6+Е5+Е4+Е3

D8:              =Е8+Е7+Е6+Е5+Е4

D9:              =Е9+Е8+Е7+Е6+Е5

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

Разница. В столбце F содержатся формулы, по которым вычитается количество необходимого персонала из планируемого количества людей. Это позволяет  определить избыток или недостаток персонала. В ячейку F3 введена формула = D3 - C3, которая копируется в шесть  остальных ячеек столбца.

Общая потребность в персонале. По формуле в ячейке Е11 суммируется  количество служащих, которые должны работать на протяжении всей рабочей  недели. Формула для вычисления следующая: = СУММ(E3:E9). Это значение и будет минимизироваться.

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

F3 >= 0        F4 >= 0        F5 >= 0        F6 >= 0

F7 >= 0        F8 >= 0       F9 >= 0

После ввода данных и формул выберите команду Сервис | Поиск  решения и укажите, что необходимо минимизировать значение, находящееся  в ячейке E11, с помощью изменяемых ячеек ЕЗ:Е9. Затем введите ограничения и нажмите кнопку Выполнить.

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

Рис. 3.2. Результат выполнения процедуры поиска решения

для задачи планирования штатного расписания

 

Если рассмотреть результаты, то можно обратить внимание на несколько  неточностей:

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

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

Исправить данные ошибки можно  путем введения ограничений:

Для каждой ячейки диапазона E3:E9 нужно указать ограничение цел (рис. 3.3).

Рис. 3.3. Диалоговое окно Добавление ограничения

для определения целочисленного ограничения

 

Для каждой ячейки диапазона E3:E9 нужно указать ограничение >= 0.

После добавления ограничений  получим следующий результат (рис. 3.4):

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

Задача 1

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

 

 

Решение задачи в Microsoft Excel

Создадим таблицу, куда внесём данные:

 

Для решения задачи используем встроенную функцию Excel – БС.

Описание

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

Синтаксис

БС(ставка,кпер,плт,[пс],[тип])

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

Ставка— обязательный аргумент. Процентная ставка за период.

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

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

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

Тип — необязательный аргумент. Число 0 или 1, обозначающее срок выплаты. Если аргумент "тип" опущен, предполагается значение 0.

 

Формула для расчёта чистой текущей стоимости проекта в ячейке B4 выглядит следующим образом:

{=БС(B2/12;B3*12;-B1)}

В итоге получаем:

 

Следовательно текущая стоимость обычных ежемесячных платежей составляет 1 431 676,04р.

 

 

Решение задачи в MathCAD

Современная (текущая) стоимость  потока платежей рассчитывается по формуле:

 

где:

S- текущая стоимость платежей;

R-ряд платежей;

i-ставка наращения процентов;

n-срок.

Инициализируем переменные и введем в формулу:

 

 

Задача 2

Имеются n пунктов производства и т пунктов распределения продукции. Стоимость перевозки единицы продукции с і-го пункта производства в j-й центр распределения c_ij приведена в таблице, где под строкой понимается пункт производства, а под столбцом - пункт распределения. Кроме того, в этой таблице в i-й строке указан объем производства в і-м пункте производства, а в j-м столбце указан спрос в j-м центре распределения. Необходимо составить план перевозок по доставке требуемой продукции в пункты распределения, минимизирующий суммарные транспортные расходы.

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

 

 

Решение задачи в Microsoft Excel

Проверим сбалансированность модели задачи. Модель является несбалансированной, т.к. суммарный объем производимой продукции в день больше суммарного объема потребности в ней. (22+16+17+21+23 > 15+21+15+21). Поэтому на предприятиях останется 27 единиц продукции. Для решения задачи сверх имеющихся n пунктов потребления добавим ещё один, фиктивный. Стоимость перевозок в фиктивный пункт потребления будем считать равной нулю. Введением фиктивного пункта потребления сего заявкой мы сравняли баланс транспортной задачи и теперь её можно решать как обычную транспортную задачу с правильным балансом.

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

Составим следующие таблицы  в Excel.

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

 

При помощи надстройки «Поиск решения» оптимизируем план перевозки.

 

В параметрах поиска решений ставим галочки «Линейная модель» и «Неотрицательные значения».

 

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

Решение задачи в MathCAD

Специальной переменной ORIGINAL присваивается значение 1. Задаем начальное значение x_i, общую стоимость перевозок F(x), условия. Используя встроенную функцию Minimize, находим минимальные значения х₁..х₂₅. Находим минимальную стоимость перевозки.

 

Задача 3

Предприятие электронной промышленности выпускает  две модели телевизоров, причем каждая модель производится на отдельной технологической линии. Суточный объем производства первой линии - 2300 изделий, второй линии - 1075 изделий. На телевизор первой модели расходуется 120 однотипных элементов электронных схем, на телевизор второй модели - 85 таких же элементов. Максимальный суточный запас используемых элементов равен 300000 единицам. Прибыль от реализации одного телевизора первой и второй моделей равна 50 и 70 долларов, соответственно. Определить оптимальный суточный объем производства первой и второй моделей.

 

 

Анализ

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

Экономическо-математическая модель задачи имеет вид:

max f = 50x₁+70x₂

120x₁+85x₂≤300000

x₁≤2300

x₂≤1075

x₁,x₂ 0

где переменные (x₁,x₂) обозначают объемы производства соответствующих моделей телевизоров.

f – выручка от реализации продукции при заданных ценах (50; 70) в $. и заданных ограничениях на запас используемых элементов расходы (300000) в ед. и ни суточный объем производства (2300; 1075) в ед.

 

Решение в Microsoft Excel

Составим следующую таблицу  в Excel.

 

 

Осуществим абсолютную адресацию к блоку (диапазону) переменных (М1, М2), которому надо дать уникальное имя (например «Переменные»). Вычислим значения прибыли. Выделим ячейку D5, в которую нужно занести расчетное значение прибыли. Нажмем вставку функции (кнопка f_x) и далее выберем функцию  СУММПРОИЗВ среди математических функций пакета.

В качестве первого аргумента  выделим указателем диапазон переменных (М1, М2), в качестве второго аргумента будут вставлены адреса соответствующих ячеек В5:C5. Далее запишем формулы для вычисления расчетных значений расхода ресурсов надо скопировать формулу для расчета прибыли в ячейки,  предназначенные для расчета расхода однотипных элементов электронных схем аналогичным способом. В ячейку D4 внесем формулу {=СУММПРОИЗВ(Переменные;B4:C4)}. Ограничения на суточный объем производства введем с помощью надстройки «Поиск решения»

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

 

В параметрах поиска решений  ставим галочки «Линейная модель»  и «Неотрицательные значения».

 

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

Решение задачи в MathCAD

Задаем начальное значение x₁ и x₂, выручку от реализации продукции f(x), условия. Используя встроенную функцию Maximize, находим максимальное значения х₁ и x₂, т.е. оптимальное суточное производство. А также f(x) – максимальную прибыль.

 

 

 

Задача 4

Построить поверхность  при _.

 

Решение задачи в Microsoft Excel

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

 

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

{=2*СТЕПЕНЬ(A4;2)*СТЕПЕНЬ(SIN(A4);2)-5*EXP(2*B3)*B3}

Растянем формулу этой ячейки вниз до значения x=1,4, т.е до ячейки B32, далее размножим её до значения y=1,4, т.е. до ячейки AD3.

 

Выделим весь массив имеющихся  решений B4:AD32, затем выберем вкладку Вставка, вызовем Мастер диаграмм и выберем в нём Поверхность, затем ОК. Заходим в параметр «Выбрать данные».  Изменяем элементы легенды (ряд).  В поле «Имя ряда» вносим значения , a в «Значение» - диапазон значений соответствующий каждому x. Например значению -1,1 соответствует диапазон {='4'!$B$7:$AD$7}. Изменяем подпись горизонтальной оси (категории) вносим в диапазон подписи оси {='4'!$A$4:$A$32}, т.е. наш диапазон значений по x учитывая, что .