14104 (Программы оптимизации рационов кормления сельскохозяйственных животных), страница 3
Описание файла
Документ из архива "Программы оптимизации рационов кормления сельскохозяйственных животных", который расположен в категории "". Всё это находится в предмете "ботаника" из , которые можно найти в файловом архиве . Не смотря на прямую связь этого архива с , его также можно найти и в других разделах. Архив можно найти в разделе "курсовые/домашние работы", в предмете "ботаника и сельское хоз-во" в общих файлах.
Онлайн просмотр документа "14104"
Текст 3 страницы из документа "14104"
Рисунок 17. Лист «Корма» с базой кормов
Б) На листе «Нормы» создаем базу норм питательности для животных различных половозрастных групп. В строках располагается список половозрастных групп животных, а в столбцах значение потребности животных в данном питательном факторе (рис. 18). После создания заполняем таблицу данными из справочника. Эти данные являются исходными для оптимизации рациона.
Рисунок 18. Лист «Нормы» с базой норм
В) На листе «Расчет» создаем две области:
- область структуры рациона, включающая список кормов;
- область питательности рациона, которая отображает сумму рациона по каждому питательному элементу, расположенному в области структуры рациона. В данной области имеется три колонки «Факт», «Норма» и «Отклонение», которые отображают фактическое содержание данного питательного фактора в рационе, нормативный показатель и отклонение первого от второго (рис. 19).
Рисунок 19. Лист «Расчет»
На всех трех листах для удобства работы при поиске необходимого элемента, закрепим шапку таблиц командой «Закрепить область».
2.2.2 Автоматизация процесса выбора группы животных, для которых будет составляться рецепт
Для расчета рациона на листе «Нормы» необходимо определить половозрастную группу животных, для которых будет составляться рацион, для этого в столбце «Да/нет» напротив соответствующей группы необходимо поставить цифру 1, также нужно проследить, чтобы напротив остальных групп стояла цифра 0.
Далее в среде Visual Basic for Applications напишем макрос переноса данных из листа «Нормы» в лист «Расчет», назовем его Norm, листинг 1.
Листинг 1.
Sub norm()
'проверяем есть ли выбранная группа
For ii = 3 To 250
If Sheets("Нормы").Cells(ii, 2) = 1 Then
Else
nul1 = nul1 + 1
End If
Next ii
' если группа определена запускаем цикл
Dim wword As String
wword = "НОРМА"
If nul1 = 248 Then
MsgBox "Выберите группу"
nul1 = 0
Else
For it = 5 To 500
If Sheets("Расчет").Cells(it, 4) = wword Then
numb = it + 1
For ik = numb To 1000
Sheets("Расчет").Range("D" & numb & ":D250").ClearContents
Next ik
End If
Next it
For i = 3 To 250
If Sheets("Нормы").Cells(i, 2) = 1 Then
For k = 4 To 60
a = Sheets("Нормы").Cells(i, k)
Sheets("Расчет").Cells(numb + k - 4, 4) = a
Next k
Else
End If
Next i
Sheets("Расчет").Select
End If
End Sub
На листе «Нормы» вставляем элемент управления «Кнопка» , и назначаем ему макрос «norm».
Также напишем макрос, автоматического изменения цвета цифр столбца «Да/нет», в зависимости от значения и устранение возможности введения какого либо другого значения кроме 1 или 0 листинг 2, назовем его Check. Данный макрос вызывается при изменениях на рабочем листе, листинг 3.
Листинг 2. Макрос Check
Public Sub check()
'изменение цевета в зависимости от значения
For i = 3 To 1000
If Range("B" & i) = 0 Then
With Range("B" & i).Font
color = -16776961
End With
Else
If Range("B" & i) = 1 Then
With Range("B" & i).Font
color = -11480942
End With
Else
' предупреждение о допустимых значениях
MsgBox "Может быть 0-нет или 1- да"
Range("B" & i) = 0
End If
End If
Next i
End Sub
Листинг 3. Вызов макроса Check
Private Sub Worksheet_Change(ByVal Target As Range)
Call check
End Sub
В случае если необходимо добавить питательный элемент по которому будет производиться балансирование рецепта, его нужно добавить в конце первой строки на листе «Корма». На остальные листы данный питательный элемент и единицы его измерения перенесутся автоматически, поскольку на данные ячейки существуют ссылки в других листах.
2.2.3 Автоматизация процесса определения структуры рациона
Для продолжения расчета на листе «Корма» необходимо определить кормовые компоненты, которые будут входить в состав рациона. По аналогии с предыдущими действиями определяются корма. Столбцу «Да/нет» также назначаем макрос Check.
Далее пишем макрос переноса выбранных кормовых средств с листа «Корма» в лист «Расчет». Данный макрос также будет выполнять скрытие не используемых ячеек на расчетном листе и перенос в расчеты значений по умолчанию, а также ввод расчетных формул, листинг 4. Данный макрос назовем Feed.
Листинг 4.
Public fForm
Sub feed()
'проверяем выбранные корма
For iman1 = 3 To 2000
If Sheets("Корма").Cells(iman1, 2) = 1 Then
Else
nul = nul + 1
End If
Next iman1
' запускаем цикл если выбраны корма
If nul = 1998 Then
MsgBox "Выберите корм"
nul = 0
Else
Cells.EntireRow.Hidden = False
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 колнке с минимальным значением
For nol = fForm + 3 To fForm + 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. Оптимизированный рецепт комбикорма
Для того чтобы облегчить восприятие сбалансированности рецепта, отклонения от нормы в случае недостатка питательного элемента выделяются красным цветом, а в случае избытка – зеленым. Для этого применим к ячейкам данного столбца условное форматирование, указав при этом два условия:
- значение больше нуля;
- значение меньше нуля.