Программы оптимизации рационов кормления сельскохозяйственных животных (стр. 4 из 5)
fForm = 0
cCount = 3
While Sheets("Расчет").Cells(cCount, 1) <> 0
cCount = cCount + 1
Wend
If cCount = 4 Then
Else
cCount = cCount - 2
Sheets("Расчет").Rows("3:" & cCount).Delete Shift:=xlUp
End If
'загружаем название кормов в которых будут расчеты
For iman = 3 To 2000
If Sheets("Корма").Cells(iman, 2) = 1 Then
Sheets("Расчет").Rows("3:3").Insert Shift:=xlDown
Sheets("Расчет").Rows("3:3").Interior.ColorIndex = xlNone
Sheets("Расчет").Rows("3:3").Font.ColorIndex = 0
b = Sheets("Корма").Cells(iman, 3)
Sheets("Расчет").Cells(3, 1) = b
Else
End If
Next iman
' загружаем теже корма с данными
For i = 3 To 2000
If Sheets("Корма").Cells(i, 2) = 1 Then
Sheets("Расчет").Rows("3:3").Insert Shift:=xlDown
Sheets("Расчет").Rows("3:3").Interior.ColorIndex = xlNone
Sheets("Расчет").Rows("3:3").Font.ColorIndex = 0
For k = 3 To 60
a = Sheets("Корма").Cells(i, k)
If k = 3 Then
Sheets("Расчет").Cells(3, k - 2) = a
Else
Sheets("Расчет").Cells(3, k + 1) = a
End If
Next k
fForm = fForm + 1
Else
End If
Next i
sPer = fForm
'заносим формулу расчета количества питательного элемента в 1 кг комбикорма
For form = 5 To 60
For form1 = fForm + 3 To fForm + fForm + 3
Sheets("Расчет").Cells(form1, form).FormulaR1C1 = "=R[-" & fForm & "]C*RC2"
Sheets("Расчет").Cells(form1, form).NumberFormat = "0.00"
Next form1
Next form
' сумма
If fForm = 0 Then
For kkk = 2 To 60
Sheets("Расчет").Cells(3, kkk) = 0
Next kkk
Else
l = fForm + fForm + 3
For kk = 2 To 60
Sheets("Расчет").Cells(l, kk).FormulaR1C1 = "=SUM(R[-" & fForm & "]C:R[-1]C)"
Sheets("Расчет").Cells(l, kk).NumberFormat = "0.00"
Next kk
Sheets("Расчет").Cells(l, 3) = ""
Sheets("Расчет").Cells(l, 4) = ""
End If
'переносим питательность в колонку факт
Call pitatel
'присваиваем 0 колнке с минимальным значением
Fornol = fForm + 3 TofForm + fForm + 2
Sheets("Расчет").Cells(nol, 3) = 0
Sheets("Расчет").Cells(nol, 3).NumberFormat = "0.00"
Sheets("Расчет").Cells(nol, 4).NumberFormat = "0.00"
Sheets("Расчет").Cells(nol, 2).NumberFormat = "0.00"
Next nol
'скрываем строки кормов с данными
Sheets("Расчет").Rows("3:" & fForm + 2).EntireRow.Hidden = True
Sheets("Расчет").Select
End If
End Sub
Public Sub pitatel()
Dim i
Dim s
Dim g
For i = fForm + fForm + 6 To 225
s = i - (fForm + fForm + 3)
g = i - (fForm + fForm + 4)
Sheets("Расчет").Cells(i, 3).FormulaR1C1 = "=R[-" & s & "]C[" & g & "]"
Next i
End Sub
Макрос «feed» является основным в программе. Перенос значений из листа с кормами осуществляется в область ячеек, которые этим же макросом скрываются на листе «Расчет» так как их не нужно видеть в процессе расчета. Список кормов дублируется для ввода расчетных формул. Эти формулы также заносятся данным макросом, формула представляет собой произведение содержания кормового компонента в корме на количество этого компонента в смеси. В строку «Итого…» заносится сумма содержания питательного фактора во всех кормовых компонентов. Эта сумма также отражается в колонке «Факт» для расчета отклонения от нормы.
Так же макрос форматирует значения ячеек до двух знаков после запятой, что облегчает восприятие информации. В столбец «min» заносятся значения 0, так как при начале оптимизации нижний предел корма как правило выставляют минимальным.
На листе «Корма» вставляем элемент управления «Кнопка»
, и назначаем ему макрос «feed».После того как перенос данных был осуществлен, пользователь должен определить максимально и минимально возможное количество введения каждого кормового компонента.
2.2.4 Отладка оптимизации рецепта
Программа MS EXCEL включает надстройку «Поиск решений», которая позволяет решать оптимизационные задачи с высокой скоростью и точностью. Вначале нужно убедиться, что надстройка подключена, в случае если она отключена ее необходимо подключить.
Для использования данного пакета вначале необходимо определить целевую ячейку. В нашем случае целевой ячейкой будет модуль суммы отклонений содержания питательных веществ в рационе от нормы их введения. Целью оптимизации является снижение данной величины, т.к. чем меньше отклонение от нормы, тем более сбалансирован рацион. Поэтому в окне надстройки устанавливаем переключатель в положение «минимальному значению» (рис. 20).
Оптимизация будет осуществляться путем изменения содержимого ячеек столбца «Количество корма». Следовательно, в поле «Изменяя ячейки» указываем диапазон ячеек столбца «Количество корма».
Также необходимо наложить ограничения на изменение ячеек. Сумма всех компонентов в колонке «Количество корма» должна составлять единицу (т.е. 100%). Поэтому добавляем ограничение, в котором ячейка «Итого» равна единице. Также существуют ограничения на ввод отдельных компонентов в состав рациона. Они указываются в колонках «max» и «min». Далее добавляется два ограничения в которых значения столбца «Количество корма» должны быть больше либо равны значений колонки «min» и меньше либо равны значений колонки «max».
Рисунок 20. Надстройка «Поиск решений»
После того как все параметры оптимизации заданы, нажимаем кнопку выполнить. Если решение, удовлетворяющее заданные требования найдено, то сохраняем его. Если решение не найдено, то необходимо изменить, либо компоненты рецепта, либо пределы ввода отдельных кормовых средств.
После оптимизации рецепт будет иметь вид, который представлен на рисунке 21.
Рисунок 21. Оптимизированный рецепт комбикорма
Для того чтобы облегчить восприятие сбалансированности рецепта, отклонения от нормы в случае недостатка питательного элемента выделяются красным цветом, а в случае избытка – зеленым. Для этого применим к ячейкам данного столбца условное форматирование, указав при этом два условия:
- значение больше нуля;
- значение меньше нуля.
Отображение ячейки при выполнении условия в первом случае будет зеленого цвета, во втором – красного.
2.2.5 Анализ рассчитываемых рецептов
Чтобы продемонстрировать насколько гибкая данная программа по сравнению с аналогичными приложениями продемонстрируем пример анализа содержания питательного элемента в сырье. Данная функция фигурирует в программе «Корм Оптима» как одно из преимуществ над продуктами других производителей.
Для начала отобразим скрытую область ячеек с данными о питательности кормовых компонентов. Затем выполним команду вставить диаграмму. Определим тип «Круговая». Далее укажем два диапазона данных в режиме столбцы. Первым диапазоном будет столбец с названиями кормовых компонентов, вторым – столбец с питательностью данных компонентов по необходимому элементу (в нашем примере Лизин). Далее ставим флажок в подписях данных напротив значений и получаем готовый результат (рис. 22).
Рисунок 22. Содержание питательного элемента в сырье
Таким же образом можно сделать график сбалансированности рецепта, который наглядно демонстрирует насколько хорошо данный рацион соответствует требованиям питательности рациона. Для этого выполняем команду вставить диаграмму, выбираем тип «Гистограмма», далее указываем диапазон данных из двух столбцов: столбец с названиями питательных элементов и столбец «Откл.», далее добавляем таблицу данных и нажимаем готово (рис. 23).
Рисунок 23. Сбалансированность рецепта
В результате мы получаем график, на котором отклонения от нуля отражают недостаток либо избыток питательного элемента в рационе. Составление данного графика можно автоматизировать путем записывания макроса. Выполним данное действие и полученный макрос назовем graph.
Данные примеры показали, что при грамотном использовании среды MSEXCEL, можно добиться высокой производительности программы, повторить и даже превзойти возможности коммерческих аналогов.
Заключение
1. Для более интенсивного развития отрасли кормления сельскохозяйственных животных необходимо применять автоматизацию всех расчетных процессов, что позволит существенно повысить производительную способность специалистов в данной области.
2. Одним из самых трудоемких процессов, является составление и оптимизация сбалансированных, научно-обоснованных рационов кормления свиней, поэтому внедрение высококлассного программного обеспечение крайне необходимо для динамичного развития отрасли.
3. На сегодняшний день в Республике Беларусь ощущается нехватка отечественного специализированного программного обеспечения в сфере расчета рационов кормления, в то время как зарубежные аналоги имеют высокую стоимость.
4. Использование российских приложений позволяет устранить существующий дефицит программных продуктов, однако недостатки программ сложно устранить в процессе работы, что существенно замедляет развитие данного направления.
5. Использование электронных таблиц MSEXCELпредоставляет зооинженерам широкие возможности в реализации функциональных и дешевых решений в сфере расчетных процессов, что отражено в практической части данной работы. Однако недостатком данного решения является необходимость наличия знаний по разработке программ у специалистов.
6. Выбор того или иного решения должен осуществляться в соответствии с финансовыми и кадровыми возможностями сельскохозяйственных предприятий, т.к. эти пункты являются лимитирующими.
Список использованных источников
1. Бражникова О.Д. Оценка программных средств автоматизации планирования и экономического анализа на сельскохозяйственных предприятиях/ О.Д. Бражникова, А.М. Дьяков// Экономика сельского хозяйства России. – 2006. – 10. – С. 24.
2. www.korall-agro.ru/articles/agrorinok_07_3.doc дата доступа 02.04.2008
3. Каширский А.И. База данных и программно-алгоритмические средства для управления селекцией в свиноводстве/ А.И. Каширский, М.К. Ландина// Агроинфо. – 2000. – С.133 – 136