9618

километры) был наименьшим.

m– количество складов;

n– количество хозяйств;

Сij – расстояние между i-тым складом и j-тым хозяйством;

Хij – количество удобрений, перевозимых с i-того склада в j-тое хозяйство; ai – общее количество удобрений на i-том складе;

bj – общая потребность в удобрениях j-того хозяйства. Условие задачи: ∑=1 ∑=1 ∙ → min. Ограничения:

1.∑=1 = ;

2.∑=1 = ;

3.∑=1 = ∑=1 ;

4.>0.

I этап. Ввод информации

Для решения поставленной задачи создадим в Microsoft Excel следующие таблицы, как показано на рисунке 7.

В первой таблице в диапазон ячеек С4:F5 мы заносим количество удобрений, имеющихся на

1-ом и 2-ом складе. В диапазон ячеек С12:G12 заносится количество удобрений, которое нужно для каждого хозяйства. Далее, установив курсор в ячейку С11, мы записываем формулу (=С9+С10),

которая будет показывать количество взятых удобрений для первого хозяйства с 1-го и 2-го склада.

После этого скопируем данную формулу в ячейки D11, E11,F11, используя метод протаскивания. В

ячейку G9 тоже введем формулу суммы (=С9+D9+E9+F9), показывающую, сколько взято удобрений с первого склада для 1-го, 2, 3 и 4-го хозяйства. Далее скопируем эту формулу в ячейку G10 также методом протаскивания.

Установив курсор в ячейку С13, мы занесем формулу (=С4*С9), показывающую сумму тонно-

километров при перевозке пока не известного количества удобрений с 1-го склада в первое хозяйство.

После этого, также используя метод протаскивания, копируем данную формулу в диапазон ячеек С13:F14.

141

Рисунок 7 – Ввод информации

В ячейке G13 мы находим сумму тонно-километров при перевозке запасов удобрений из первого склада в хозяйства (=С13+D13+E13+F13). Скопировав эту формулу в ячейку G14, мы найдем сумму тонно-километров при перевозке удобрений всех запасов удобрений, сложим тонно-километры при перевозке всех запасов с 1-го и 2-го склада (Н14=G13+G14), данная ячейка будет целевой при поиске нашего решения.

Как видно, ячейки С9:F10 остались незаполненными, в них ЭВМ будет подставлять значения при поиске решений, а после того как решит, будут внесены значения, показывающие, сколько нужно перевезти удобрений из i-го склада в j-тое хозяйство при минимальных объемах тонно-километров.

II этап решения

Для решения задачи воспользуемся в команде «Данные» функцией «Поиск решения». Вызвав данную функцию, введем следующие параметры (рисунок 8).

В поле, где нужно установить целевую функцию, вводим ячейку Н14, устанавливаем значение целевой функции, равной минимальному значению, изменяя ячейки диапазона С9:А10, в которые и будут внесены искомые значения.

142

Рисунок 8 – Ввод параметров решения

Далее начинаем записывать ограничения С11=С12, D11=D12, E11=E12, F11=F12, которые означают, что потребности удобрений в хозяйствах должны быть полностью удовлетворены. Введя в ограничениях значения G9=H9 и G10=H10, мы указываем, что ресурсы складов должны быть полностью использованы. Далее в параметрах решения мы устанавливаем метод решения «Поиск решения линейных задач симплекс-методом» и после этого нажимаем клавишу «Найти решение».

Решение готово (рисунок 9).

Из анализа видно, что из первого склада 1500 тонн удобрений нужно поставить в 1-е

хозяйство и 2000 тонн в 4-е хозяйство. Из второго склада 1500 тонн нужно поставить в 1-е хозяйство, 1500 во 2-е и 1500 в третье. Таким образом, все удобрения со складов израсходованы, и хозяйства полностью удовлетворены при минимальных затратах тонно-километров, что и требовалось найти.

143

Рисунок 9– Решение Данное решение полностью совпадает с ручным методом потенциалов.

Нашим примером служила задача закрытого типа, т.е. количество удобрений на складах совпадало с требуемым количеством в хозяйствах. Однако часто бывает, когда производство и потребление не равны (задача открытого типа). Для рассмотрения данного варианта воспользуемся нашей предыдущей задачей, только немного изменим условия (на втором складе имеется 3500 тонн удобрений), т.е. потребность в удобрениях стала превышать их количество на складах.

Первый этап, ввод информации практически полностью совпадает с вышеизложенным примером, только в ячейку Н10 нужно поставить 3500.

Во втором этапе решения есть изменения, которые касаются ограничений при вводе функции

«Поиск решения». Здесь ограничения уже будут иметь следующий вид С11≤С12, D11≤D12, E11≤E12, F11≤F12, которые означают, что потребности удобрений в хозяйствах будут частично удовлетворены из-за нехватки их на складах. Дальнейший алгоритм решения остается прежним (рисунок 10).

144

Рисунок 10 – Новое решение Рассматривая решение задачи, видим, что удобрений недополучит первое хозяйство, которое

обусловлено самым невыгодным месторасположением (наибольшее расстояние от складов).

Задание 11

Некоторое хозяйство на одной и той же пашне площадью 1000 га ежегодно засевает 400 га пшеницей, 250 га - сахарной свеклой, 200 га подсолнечником и 150 га оставляет под черный пар.

Технологическая эффективность культур и предшественников

Предшественник

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

урожайность подсолнечника после пшеницы составит 1,06*12=12,72 ц/га.)

Известна нормативная урожайность (ц/га), материально-денежные затраты - МДЗ на возделывание каждой культуры (т.р. на 1 га) и цена реализации (т.р. за 1 т). Все эти данные приведены в таблице.

Требуется определить план посева очередного года (т.е. что на каком поле сеять),

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

Построение модели:

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

Х11, Х12, Х13, Х14 - площади посева пшеницы по пшенице, свекле, подсолнечнику и пару соответственно;

Х21, Х22, Х23, Х24 - площади посева сахарной свеклы по пшенице, свекле, подсолнечнику и пару соответственно;

Х31, Х32, Х33, Х34 - площади посева подсолнечника по пшенице, свекле, подсолнечнику и пару соответственно;

Х41, Х42, Х43, Х44 - площади занимаемые паром после пшеницы, свеклы, подсолнечника и пара соответственно.

Определимся с целевой функцией. На каждом из 16-ти полей мы имеем с одной стороны выручку, определяемую как произведение урожайности на цену данной культуры и на площадь, а с другой стороны - затраты. Обозначим цену i-й культуры через Сi, нормативную урожайность - через Уi , коэффициенты влияния - через Кij, а затраты на возделывание - Мi. Тогда выгода (прибыль) на поле, выделенном под i-ю культуру с j-м предшественником, очевидно, составит

Wij = (Ci * Кij * Уi - Mi ) * Xij. (1)

Следовательно, целевая функция нашей задачи определяется как сумма выгод по всем 16-и

площадям:

Σi Σj(Ci * Кij * Уi - Mi ) * Xij ==> max (2)

146

3.Условие неотрицательности: Хij > 0 для всех i и j .

4.Баланс по занимаемым площадям

Х11 + Х12 + Х13 + Х14= 400 (посев пшеницы должен составить 400 га)

Х21 + Х22 + Х23 + Х24 =250 (посев сахарной свеклы должен составить 250 га)

Х31 + Х32 + Х33 + Х34= 200 (посев подсолнечника должен составить 200 га)

Х41 + Х42 + Х43 + Х44= 150 (площадь под паром должна составить 150 га).

5. Баланс по площади предшественников Х11 + Х21 + Х31 + Х41= 400 (пшеница как предшественник занимала 400 га)

Х12 + Х22 + Х32 + Х42= 250 (свекла как предшественник занимала 250 га)

Х13 + Х23 + Х33 + Х43= 200 (подсолнечник как предшественник занимал 200 га)

Х14 + Х24 + Х34 + Х44= 150 (пар как предшественник занимала 150 га)

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

Организация расчетной таблицы в EXCEL

1.В строках 1-12 разместим исходные данные.Выделим совокупность ячеек В16 - Е19 под значения управляемых переменных Х11 - Х44 и заполним ее начальными значениями - "1". В таблице эта совокупность затемнена. Для проверки балансовых соотношений

2.занесем в ячейки В20-Е20 и F16-F19 формулы расчета соответствующих левых частей неравенств по пункту 5 и пункту 4.

3.Наконец, в нижней части таблицы (строки 24-27) занесем формулы (1) для расчета удельной

(на 1 га) прибыли от возделывания каждой культуры по каждому предшественнику. В ячейке Е28

организуем расчет целевой функции по формуле (2).

4. "ПОИСК РЕШЕНИЯ".

1) По неотрицательности переменных $B$16:$E$19 >= 0

2 и 3) По соответствию занимаемым площадям и по площади предшественников $B$20:$E$20 = $E$9: $E$12

$F$16:$F$19 = $E$9: $E$12

Задание самостоятельной практической работы

147

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

Необходимо заполнить параметры выбора объекта недвижимости, как на рисунке 1.1, выбирая свой район. Видом объекта недвижимости является земельный участок, категорией – земли населенных пунктов, дата регистрации сделки 01.01.2012 – 31.12.2012 гг. Затем нажать – получить объекты. Итог: сформировалась таблица, с которой необходимо работать.

Рисунок 11 – Заполнение вкладки «Мониторинг рынка недвижимости» (rosreestr.ru)

1.Используя данные АИС «Мониторинг рынка недвижимости» заполнить таблицу 6 Выбрать

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

Таблица 8 – Пример заполнения таблицы «Продажи земельных участков населенных пунктов на территории N-го района Нижегородской области»

148

004кая область,

Арзамасски й район, д.

Князевка,у

л. 9 Мая

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

(виду разрешенного использования). Заполните столбец с удельной ценой продажи самостоятельно

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

диапазоном удельной цены продажи (мин. значение – максимальное значение цены в группе).

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

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

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

4.Используя данные, приведенные в таблице 8, представить графически изменения удельной цены продаж земельных участков для ИЖЗ и ведения ЛПХ.

5.Построить графики по количеству продаж (сделок) земельных участков на территории района для ИЖЗ и ЛПХ по месяцам в году. Использовать данные таблицы 8. Динамики сделок отразить на одном графике.

149

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

7.Представить график динамики средней заработной платы по выбранному району за

2005-2013 гг. с использованием сайта gks.ru.

Ссылка : http://www.gks.ru/scripts/db_inet2/passport/munr.aspx?base=munst22

8. Самостоятельно представить 2 графика по развитию рынка земли в Нижегородской области с указанием ссылок на источник. Желательно наличие графика с двумя осями ординат.

Список источников

1.Волков, С.Н. Экономико-математические методы в землеустройстве [Текст]:

Землеустройство. Экономико-математические методы в землеустройстве. В 6 т. Т. 4 / С.Н. Волков. –

М. : Колос, 2001. – 696 с.

2.Общая теория статистики: учебник. Под ред. Елисеевой И.И. [Электронный ресурс]. –

uchebnik_pod_red_eliseevoj_i_i/glava_8_korreljacionno_regressionnyj_analiz_i_modelirovanie_1/227-1-0-

3924.

3.Экономико-математические методы [Электронный ресурс]. – Режим доступа :

http://emm.ostu.ru/lect/lect6.html.

4.Методы линейного программирования [Электронный ресурс]. – Режим доступа :

http://math.immf.ru/lections/302.html.

5.Симплекс-метод линейного программирования [Электронный ресурс]. – Режим доступа :

http://matmetod-popova.narod.ru/theme24.htm.

6.Применение корреляционно-регрессионного анализа в задачах землеустройства

[Электронный ресурс]. – Режим доступа: http://proizvodim.com/primenenie-korrelyacionno-

regressionnogo-analiza-v-zadachax-ocenki.html

150