Имеются следующие исходные данные

Средства, выделенные для проведения мероприятий.

Мероприятия	Средства
Недвижимость
Благоустройство
Дорожная сеть
Электроэнергия
Водоснабжение
Газификация
Телефонизация

Увеличение коммерческой стоимости земельного участка после проведения комплекса мероприятий.

Удаленность земельных участков	Увеличение коммерческой стоимости
0-30 км от МКАД
31-60 км от МКАД
61-100 км от МКАД

Коэффициент привлекательности вложения инвестиций.

Мероприятия	Недвижимость	Благоустройство	Дорожная сеть	Электроэнергия	Водоснабжение	Газификация	Телефонизация
Удаленность земельных участков
0-30 км от МКАД	1.10	1.50	1.40	1.22	1.66	1.78	1.99
31-60 км от МКАД	0.29	0.89	0.49	0.69	0.79	0.89	0.99
61-100 км от МКАД	0.44	0.72	0.36	0.55	0.69	0.62	0.74

На пересечении столбца конкретного мероприятия со строкой участка находится информация о привлекательности инвестиций для каждого участка по данным мероприятиям. Например, коэффициент привлекательности инвестиций по благоустройству на участке 31-60 км от МКАД будет равен 0.89.

Для решения задачи подготовим необходимые таблицы (рис. 1).

Рис.1 Изменяемые ячейки.

Значения ячеек по столбцу В с четвертой по шестую строку определяются суммированием данных ячеек соответствующих строк начиная со столбца С до столбца I.

Например, значение ячейки B4=СУММ(C4:I4).

Значения ячеек по 7 строке по столбцам от С до I определяются суммированием данных ячеек соответствующих столбцов с 4 по 6 строки.

Например, значение ячейки С7=СУММ(C4:C6)

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

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

Рис.2 Исходная информация.

В строке 12 по столбцам C-I определим инвестиции по каждому мероприятию. К примеру, для недвижимости (ячейка С12) это рассчитывается с помощью формулы

С12=С4*С9+С5*С10+С6*С11

либо можно использовать функцию СУММПРОИЗВ

С12=СУММПРОИЗВ(C4:C6;C9:C11)

В ячейке С4 находится количество средств, выделяемых на недвижимость с первого участка (0-30 км от МКАД), а в ячейке С9 – коэффициент привлекательности вложения данных средств. Соответственно первое слагаемое в формуле означает сумму инвестиций по данному маршруту. Вся же формула вычисляет общую сумму инвестиций на данное мероприятие.

В ячейке В12 по формуле В12=СУММ(С12:I12) будет вычисляться общая сумма инвестиций по всем мероприятиям.

Таким образом, информация на рабочем листе примет следующий вид (рис. 3)

Рис. 3. Рабочий лист, подготовленный для решения транспортной задачи

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

После выбора данной команды появится диалоговое окно (рис. 4).

Рис. 4. Диалоговое окно Поиск решения

Поскольку в качестве критерия оптимизации нами выбрана максимизация эффективности капиталообразующих инвестиций в инфраструктуру, в поле Установить целевую ячейку введите ссылку на ячейку, содержащую формулу расчета общего объема инвестиций. В нашем случае это ячейка $B$12 Чтобы максимизировать значение конечной ячейки путем изменения значений влияющих ячеек (влияющими, в данном случае это и изменяемые ячейки, являются ячейки, которые предназначены для хранения значений искомых неизвестных), переключатель установите в положение максимальному значению;

В поле Изменяя ячейки введите ссылки на изменяемые ячейки, разделяя их запятыми; либо, если ячейки находятся рядом, указывая первую и последнюю ячейку, разделяя их двоеточием ($С$4:$I$6). Это означает, что для достижения максимальной эффективности инвестирования будут меняться значения в ячейках с С4 по I6, то есть будут изменяться количество средств, выделенных по конкретному маршруту.

В группе полей Ограничения нажмите кнопку Добавить. Появится диалог Добавление ограничения (рис. 5)

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

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

Рис. 6. Диалоговое окно Поиск решения с заполненными полями

Для изменения и удаления ограничений в списке Ограничения диалогового окна Поиск решения укажите ограничение, которое требуется изменить или удалить. Выберите команду Изменить и внесите изменения либо нажмите кнопку Удалить.

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

Первое условие $B$4:$B$6 <=$B$9:$B$11. Оно означает, что значение в ячейке В4 должно быть меньше или равно значению в В9, в В5 меньше или равно, чем в В10, и так далее до В6 и В11.

В ячейках с В4 по В6 на листе находятся – объемы инфраструктуры, размещаемой на земельных участках.

В ячейках с В9 по В11 - объемы увеличения коммерческой стоимости земельного участка после проведения комплекса мероприятий

Второе условие $С$4:$I$6>=0. Оно означает, что объем перевозок не может быть отрицательным, то есть, если на данном участке не хватает инвестиций, то они не могут быть отправлены с какого-либо мероприятия обратно на участок. Поток инвестиций имеет только одно направление - с участков на мероприятия.

И наконец, третье, и последнее условие $С$7:$I$7>=$C$8:$I$8. Оно означает, что значения в ячейках седьмой строки должны быть больше или равны значениям в ячейках восьмой строки, то есть потребности в инвестировании должны быть выполнены полностью. Перевыполнение объема инвестиций допустимо, а недовыполнение - нет.

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

После нахождения решения появляется диалог Результаты поиска решения (рис. 7)

Рис. 7. Диалоговое окно Результаты поиска решения

Нажав кнопку ОК, вы занесете вариант решения на рабочий лист (рис. 7).

Рис. 7. Решенная транспортная задача

Максимальные инвестиции при соблюдении всех условий равны 46088 усл.ед.