На рис. 1.5 задано пять колонок. Первая содержит наименования рассчитываемых показателей. Остальные четыре колонки содержат исходные данные для расчета, а также формулы и функции для определения питательности корма. В данной конфигурации они скрыты, а на дисплее отображаются только результаты вычислений.
Исходные данные вводятся в диапазон B4:E5. В четвертой строке задана информация о содержании в корме сырых органических веществ (г в 1 кг). В пятой строке – % переваримости органических веществ. Для удобства работы с вводимой информацией и во избежание ошибок ввода на этот диапазон наложены следующие ограничения. В ячейки B4:E4 можно ввести только целое число от единицы до тысячи. Иначе будет выведено сообщение об ошибке. Недопустим ввод символов, строк, отрицательных цифровых данных или значений больше тысячи. Аналогичным образом организован контроль ввода % переваримости в ячейках B5:E5. Сюда можно ввести любое действительное число от 1 до 100. Нижележащая область таблицы с цифровой информацией для ввода не доступна, поскольку все дальнейшие вычисления производятся автоматически. Из методики расчета питательности кормов следует, что продуктивное действие питательных веществ постоянно, за исключением жира. Константа жироотложения по жиру варьирует от 0,474 в объемистых кормах до 0,526 в концентратах и 0,598 в жмыхах и шротах. В программе предусмотрена возможность уточнения константы жироотложения по жиру в зависимости от вида корма. Для этого определен встроенный список из трех значений, ко-
торый активизируется при переходе в ячейку C7. Выбор значения из списка производится щелчком мыши на соответствующей цифре.
Расчет переваримых питательных веществ производится по формулам:
-
=B4*B5/100 – переваримый протеин;
-
=C4*C5/100 – переваримый жир;
-
=D4*D5/100 – переваримая клетчатка;
-
=E4*E5/100 – переваримые БЭВ.
Расчет ожидаемого жироотложения получаем путем произведения переваримых питательных веществ на соответствующие константы жироотложения:
=B6b7; =C6C7; =D6D7; =E6E7.
Далее рассчитывается общее ожидаемое жироотложение в ячейке
- D9 суммированием значений, полученных в восьмой строке.
Ячейка B10 содержит список:
-
сено, солома;
-
мякина;
-
зеленый корм;
-
другие.
В зависимости от выбора из списка автоматически рассчитывается скидка на клетчатку, которая записывается в ячейку D10. Эта ячейка содержит формулу
=ЕСЛИ(B10="сено, солома";143;ЕСЛИ(B10="мякина";72;_ ЕСЛИ(B10="зеленый корм";K1;0)))
При выборе первой строки списка скидка на клетчатку равна 143 г на 1 кг сырой клетчатки в корме. Выбор второй строки приведет к получению цифры 72, что соответствует скидке на клетчатку в мякине, а третья строка ссылается на ячейку K1. Дело в том, что скидка на клетчатку в зеленом корме зависит от количества в нем сырой клетчатки. Чем меньше клетчатки в корме, тем ниже ее отрицательное действие в синтезе продукции. Этот алгоритм заложен в ячейке K1, которая содержит формулу
=ЕСЛИ(D4>=140;131;ЕСЛИ(D4>=100;107;ЕСЛИ(D4>=60;82;82)))
Здесь проверяется ячейка D4, содержащая количество сырой клетчатки, в зависимости от которого и определяется скидка на клетчатку.
При выборе из списка B10 строки «Другие» управление передается списку в ячейке B11, предназначенному для определения коэффициента полноценности, который применяется к корнеклубнеплодам и концентрированным кормам. В списке выбирается конкретный вид корма и тогда заполняется ячейка D11, содержащая коэффициент полноценности. В этой ячейке записана формула
=ЕСЛИ(B10="другие";ВПР(B11;H1:I18;2;);0)
Отсюда следует, что если в первом списке выбрано «Другие», то срабатывает функция ВПР ( из раздела Ссылки и массивы) , иначе – 0. В нашем примере отражена именно эта ситуация. При внимательном рассмотрении рис. 1.6. нетрудно заметить, что цифра 0 отображается светлым тоном. Это означает, что при расчетах скидки на клетчатку коэффициент полноценности не учитывается. Функция ВПР анализирует массив H11:I18. Она ищет значение в крайнем левом столбце таблицы и возвращает значение ячейки, находящейся в указанном столбце той же строки. В нашем примере крайний левый столбец массива – ни что иное, как наименования концентрированных кормов, а правый – коэффициенты их полноценности, значения которых заносятся в ячейку D11.
В ячейке D12 записана формула
=ЕСЛИ(D10=0;D9D11/100;D9–(D10D4/1000))
Она вычисляет фактическое жироотложение с учетом скидки на клетчатку или коэффициента полноценности в зависимости от содержимого ячейки D10. Если D10 = 0, то из общего ожидаемого жироотложения вычитается скидка на клетчатку, а коэффициент полноценности игнорируется. И наоборот, если выбраны корма из второго списка, то коэффициент полноценности принимается в расчет. В последней строке диалогового окна в ячейке D12 записана простая формула расчета овсяных кормовых единиц в корме путем деления фактического жироотложения на 150, как это предписано изучаемой методикой:
=D12/150
В заключение необходимо отметить, что расчетная таблица должна быть защищена от случайного разрушения целостности заложенного в нее алгоритма. Пользователь не должен иметь возможности изменения формул или очистки ячеек, их содержащих. Для этого лист таблицы надо защитить, установив свойство Защитить лист в меню Сервис │
