Автоматизация процесса выбора группы животных, для которых будет составляться рецепт

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».

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