Файл: Лабораторная работа ms excel Ввод и форматирование данных Цель работы отработка базовых навыков работы в ms.doc

Важно отметить, что данная задача должна быть сбалансирована, то есть запасы горючего и потребность в нем равны (т.е. 160+70+110=150+190). В этом случае не нужно учитывать издержки, связанные как со складированием, так и с недопоставками. В противном случае в модель нужно ввести:

• 
  в случае превышения объема запасов – фиктивного потребителя; стоимость перевозок единицы продукции этому фиктивному потребителю полагается равной стоимости складирования, а объемы перевозок этому потребителю равны объемам складирования излишек продукции у поставщиков;
  
• 
  в случае дефицита – фиктивного поставщика; стоимость перевозок единицы продукции от фиктивного поставщика полагается равной стоимости штрафов за недопоставку продукции, а объемы перевозок от этого поставщика равны объемам недопоставок продукции потребителям.
  

2. 
   Теоретические сведения.
   

Для решения данной задачи построим ее математическую модель. Неизвестными здесь являются объемы перевозок. Пусть x_ij – объем перевозок от i-того поставщика j-тому потребителю. Функцией цели являются суммарные транспортные расходы, т.е.

,

где c_ij – стоимость перевозки единицы продукции от i-того поставщика j-тому потребителю. Кроме того, неизвестные должны удовлетворять следующим ограничениям:

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

Таким образом, мы имеем следующую модель:

,

где a_i – запасы горючего у i- того поставщика; b_j – спрос у j-того потребителя.

3. 
   Выполнение работы:
   

Выполните следующую подготовительную работу для решения транспортной задачи с помощью средства Поиск решения в табличном процессоре Microsoft Excel:

1. Введите в ячейки диапазона B4:D5 стоимости перевозок (см. рис. 2.1).

---

Рис. 1. Вид рабочего окна

2. Отведите ячейки диапазона B8:D9 под значения неизвестных (объемов перевозок). Ячейки должны быть пустыми! (см. рис. 2.1).

3. Введите в ячейки диапазона F8:F9 объемы запасов горючего у поставщиков.

4. Введите в ячейки диапазона B11:D11 потребность в горючем у потребителей.

5. В ячейку B14 введите функцию цели:

=СУММПРОИЗВ(B4:D5;B8:D9)

Сделать это можно при помощи Мастера функций (Вставка  Функция), выб­рав в категории Математические функции СУММПРОИЗВ и указав необходимый диапазон.

6. В ячейки диапазонов E8:E9 введите формулы вычисляющие объемы запасов у поставщиков, в ячейки диапазона B10:D10 – формулы расчета объемов доставляемого топлива к потребителям (табл. 2). При этом на экране должны отображаться данные, как показано на рис. 2.

Таблица 2

Формулы для расчета

Ячейка	Формула
E8	=СУММ(B8:D8)
E9	=СУММ(B9:D9)
B10	=СУММ(B8:B9)
C10	=СУММ(C8:C9)
D10	=СУММ(D8:D9)

7. Выберите в меню Сервис команду Поиск решения и заполните диалоговое окно Поиск решения, как показано на рис. 2.2.


Рис. 2. Окно «Поиск решения»

8. Нажмите кнопку <Выполнить>. Средство Поиск решения найдет оптималь­ный план поставок горючего и соответствующие ему транспортные расходы.

В результате получаем распределение горючего между поставщиками и потребителями (табл. 3).

Таблица 3

Результат решения задачи

Поставщики	Потребители
		1		2		3
A	150		0		0
B	10		70		110

---

Значение целевой функции составило 20400 денежных единиц.

При этом, экономическая интерпретация результатов будет следующая: поставщик A перевозит потребителю 1 – 150 т горючего, поставщик В – потребителям 1, 2 и 3 – 10, 70 и 110 т горючего соответственно. При этом затраты на перевозку продукции будут минимальными и составят 20400 денежных единиц.

Задача № 4. Использование команды «Подбор параметра» для расчетных задач

Пусть известно, что в штате автомастерской состоит 6 разнорабочих, 8 слесарей, 10 мастеров, 3 заведующих блоками, бухгалтер, сторож, дворник и директор. Общий месячный фонд зарплаты составляет 100000 рублей. Необходимо определить, какими должны быть оклады сотрудников автомастерской.

Технология работы:

Теоретические сведения.

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

слесарь получает в 1,5 раза больше разнорабочего (А2=1,5; В2=0);

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

заведующий блоком – на 300 рублей больше, чем мастер (А4=3; B4=300);

сторож – в 2 раза больше разнорабочего (А5=2; В5=0);

дворник – на 200 рублей больше слесаря (А6=1,5; В6=200);

бухгалтер – в 4 раза больше разнорабочего (А7=4; В7=0);

директор – на 500 рублей больше бухгалтера (А8=4; В8=500);

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

,

где N1 – число разнорабочих, N2 – число слесарей и т.д.

В этом уравнении нам известны A1...A8, B1...B8 и N1... N8, а С – неизвестно. Анализ уравнения показывает, что задача составления расписания свелась к решению линейного уравнения относительно С.

Выполнение работы.

Введите исходные данные в рабочий лист электронной таблицы, как показано на рис. 1.

---

Рис. 1. Таблица с исходными данными
В столбце D вычислите заработную плату для каждой должности. Например, для ячейки D3 формула расчета имеет следующий вид:

=B3*$B$12+C3

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

=D3*E3

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

=СУММ(F3:F10)

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



Рис. 2. Таблица c расчетными данными

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

Активизируйте команду Подбор параметра из меню Сервис;

В поле «Установить в ячейке» появившегося окна введите ссылку на ячейку F12, содержащую формулу;

В поле «Значение» наберите искомый результат 100000;

В поле «Изменяя значение ячейки» введите ссылку на изменяемую ячейку В12 и щелкните на кнопке <ОК>.

Сохраните таблицу в личном каталоге.

Задание для самостоятельного выполнения

Определите оклад разнорабочего, если месячный фонд заработной платы увеличится на 20%.

---