Далее пишем макрос переноса выбранных кормовых средств с листа «Корма» в лист «Расчет». Данный макрос также будет выполнять скрытие не используемых ячеек на расчетном листе и перенос в расчеты значений по умолчанию, а также ввод расчетных формул, листинг 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.