Программы оптимизации рационов кормления сельскохозяйственных животных

Программы оптимизации рационов кормления сельскохозяйственных животных

Курсовая работа

по курсу “Основы информационных технологий”

на тему: Программы оптимизации рационов кормления сельскохозяйственных животных

Введение

Стабильное функционирование и развитие экономики сельскохозяйственных организаций в современных условиях предполагает внедрение и использование информационных технологий [1].

Использование информационных достижений позволяет увеличить производительность предприятий и  рациональное использование ресурсов, что является основой интенсификации.

Интенсификация труда работников является важной составляющей интенсификации производства в целом. Интенсификация труда на базе информационных технологий заключается в повышении качества управленческих решений и точности их исполнения [2].

Отсутствие эффективных методов, моделей и программно-алгоритмических средств, отражающих специфику продукционных процессов в животноводстве, не позволяет использовать информацию в качестве производственной силы отрасли. И, как следствие, недостаточный уровень информационного обеспечения приводит к слабой организационной деятельности управляющей системы отрасли [3].

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

Сейчас зооинженер, наряду с серьезными познаниями в области кормления, содержания, разведения, селекции животных, должен лично свободно владеть персональным компьютером (ПК), который становится основным рабочим инструментов интеллектуальной деятельности. Эта проблема постепенно нарастает и в ближайшее время станет одной из основных, по мере формирования баз данных, отражающих многочисленные аспекты деятельности хозяйства [4].

К числу задач компьютерной технологии промышленного животноводства можно отнести организацию исходных данных, их обработку при помощи современной вычислительной техники, получение и представление результатов для оперативного контроля и перспективного прогнозирования производственного процесса. Это будет способствовать принятию решений, направленных на повышение эффективности реализации существующих материальных и трудовых ресурсов [5].

Сегодня успехи в области средств и систем автоматизации позволяют достичь любого ее уровня на сельскохозяйственном производстве. Отсюда задачей специалиста становится выбор из многообразия предложений на рынке средств, наиболее подходящих в конкретных производственных условиях по критерию цена – качество.

Для успешного развития животноводства важное значение имеет организация кормовой базы, поскольку от этого во многом зависят здоровье и продуктивность животных, себестоимость производимой продукции. Определение состава оптимальных кормовых смесей, рационов – одна из первых экономических задач, к решению которой были применены методы нелинейного программирования. Первая попытка составления набора продуктов, который был бы самым дешевым и содержал бы необходимое количество питательных веществ была сделана Дж. Стиглером в 1945 г. В 50-е годы задача оптимизации кормовых рационов получает распространение [6].

В настоящее время далеко не все комплексы и фермы промышленного типа по производству свинины работают эффективно, что объясняется слабой кормовой базой, низким уровнем кормления животных во многих хозяйствах, необеспеченностью кадрами, слабой их квалификацией, недостатками в техническом оснащении отрасли животноводства [7].

Это дает основание для дальнейшего развития указанного направления применения информационных технологий в животноводстве.

1 Применение программ оптимизации рецептов кормления свиней

Прежде всего, специалист племенного и промышленного животноводства, приступающий к организации компьютерной технологии в отрасли, должен иметь четкое представление об алгоритме решаемой задачи и иметь объем специальных знаний, обеспечивающий правильную интерпретацию и эффективное использование полученных результатов. При этом от зооинженера требуется понимание возможностей компьютерной техники и программного обеспечения. Персональный компьютер в области животноводства следует рассматривать не только как средство облегчения интеллектуального труда, но и в качестве исключительно мощного инструмента познания и воздействия на процессы, осуществляемые при разведении, селекции, кормлении, содержании, обслуживании поголовья [8].

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

В связи с этим существует необходимость в автоматизации расчетных процессов, связанных с составлением и балансированием рационов кормления сельскохозяйственных животных. В настоящий момент в Республике Беларусь происходит широкое внедрение в сельскохозяйственные предприятия персональных компьютеров для решения подобных задач, однако, программное обеспечение не отвечает требованиям отрасли [9].

Оптимизация рационов для сельскохозяйственных животных традиционно выполняется на основе линейной экономико-математической модели, целевой функцией в которой является минимум стоимости рациона, а требования к качеству рациона задаются в виде жестких ограничений на содержание в рационе компонентов питания и ряд соотношений [2].

Сегодня каждый производитель самостоятельно решает вопрос оптимального расчета рецептов кормов, либо разрабатывая программное обеспечение собственными силами, либо покупая его за немалые деньги у западных производителей. При этом остается вопросом, удовлетворят ли такие программы требования, как мелких фермеров, так и крупных животноводческих хозяйств [10]. 

Однако остается бесспорным то, что применение компьютерных технологий в животноводстве послужит толчком к более эффективному развитию отрасли.

Общая схема компьютеризации в животноводстве представлена на схеме (рис. 1). Мероприятие осуществляется на основе трех взаимосвязанных и взаимообусловленных компонент, с участием специалистов разного профиля. Так, постановка задач, анализ результатов и принятие решения входят в компетенцию зооинженеров и ветеринарных врачей. Компьютерное решение задач до последнего времени выполняли профессионалы в области вычислительной техники. С появлением новых поколений персональных компьютеров и программного обеспечения стало возможным решение поставленных задач при помощи ПЭВМ непосредственно специалистами животноводства. Такой подход к компьютерной технологии, особенно в племенном животноводстве, следует признать наиболее перспективным.

Последнее связано с тем, что существует довольно актуальная проблема, заключающаяся в недостаточном "взаимопонимании" специалистов, имеющих биологическую и техническую подготовку. Это, при внедрении компьютерной технологии, приводит к непроизводительным потерям времени, неполному использованию вычислительной техники [5].

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

Рисунок 1.  Структурно-логическая схема компьютеризации в животноводстве.

1.1 Программа «Рецепт Плюс» компании «АгроОптим»

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

Программа отвечает всем требованиям, предъявляемым к современным программам подобного класса:

- Предусмотрено ведение справочников сырья, цен.

- Заложены справочники нормативов кормления различных групп животных, причем справочник охватывает весь диапазон требований к рецепту: по питательности; по соотношению показателей питательности (например: энерго-протеиновое, кальциево-фосфорное и прочие соотношения).

- Ограничения на использование сырья.

- Автоматический (с оптимизацией) режим расчета комбикорма найдет сбалансированное решение при наименьшей стоимости, проанализирует рентабельные цены на сырье, укажет стоимость всевозможных ограничений.

- Ручной режим расчета позволит ввести готовые рецептуры и проанализировать их питательность.

- Из готовых рецептов можно выделить и сохранить в базе данных предварительные смеси (премиксы, БВМД, зерновые смеси), с сохранением связи рецептуры и данной смеси.

- Учитывается влияние ферментных препаратов.

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

- Расчет потребности в сырье по выбранному ассортименту рецептур комбикормов.

- Можно создать несколько баз данных и использовать их раздельно друг от друга.

- Возможен вывод на печать любой информации из расчетов и базы данных [11].

Окно расчета рецепта имеет вид, представленный на рисунке 2. В нем имеется две основные зоны, которые характерны для всех программ подобного класса. Первая область отображает структуру рецепта и корма, которые в ходе оптимизации не были включены в рецепт, вторая отображает питательность данного рецепта. 

Рисунок 2. Главное рабочее окно

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

Рисунок 3. Справочник нормативов

После того как была определена группа животных для которых будет балансироваться рацион, в справочнике сырья (рис. 4) определяются кормовые компоненты, которые войдут в состав рациона. Справочник включает список сырья, а также питательность каждого корма.

Рисунок 4. Справочник сырья

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

После завершения расчетов имеется возможность распечатать отчет, который составляется автоматически. Причем существует несколько вариаций отчетов, в зависимости от требований: текущий рецепт, рецепт (коммерческое предложение); рецепт с учетом выработки; развернутый отчет по рецепту.

Программы компании АгроОптим благодаря современным методикам расчета и оптимизации решений являются гибким и мощным инструментом в руках специалиста, ответственного за расчет рационов для КРС, комбикормов или премиксов. При разработке каждого программного продукта учитывались особенности отечественных производителей комбикормов и премиксов, пожелания российских специалистов в области кормления, а также мировой опыт экономической оценки получаемых решений. При разработке интерфейса учитывалось, что будущие пользователи имеют самый минимальный навык в общении с компьютером. Простота и удобство программ позволяют начать работу при минимальных затратах времени на обучение, а встроенная справочная система содержит ответы на большинство вопросов [12].

1.2 Разработка «ВНИИКП» программа «Комбикорм Оптимум v.5»

Основным и наиболее ответственным этапом компьютеризации животноводства является формирование баз данных. Помимо MS Excel для этой цели используются различные системы управления базами данных (СУБД) [13].

Программа Комбикорм Оптимум v.5, является клиент-серверным приложением на основе СУБД Firebird. Достоинством подобных решений является то, что СУБД Firebird – приложение открытого типа, распространяется бесплатно, занимает немного места на жестком диске. Также базы данных созданные при помощи данной СУБД построены по схеме «клиент – сервер». При чем «клиент» и «сервер» могут находиться как на разных компьютерах так и на одном.

Программа имеет многооконный интерфейс, что позволяет одновременно производить несколько операций. Однако вести расчет одновременно по нескольким рецептам исключена, т.к. активным может быть только один рецепт (рис. 5). В своем составе приложение имеет несколько справочников, которые представляют собой базу с данными необходимыми для расчета рациона.

Приложение дает возможность расчета рецептов комбикормов, концентратов и премиксов. Первоначально рассчитывается полная кормовая смесь с учетом ввода всех кормовых добавок. Далее определяется какие компоненты войдут в состав концентрата, для этого ставится флажок в столбце «Конц.». Для определения компонентов, которые войдут в состав  премикса в столбце «Тип» необходимо квадратами различного цвета определить соответствующее сырье, после чего вызвать команду

Рисунок 5. Окно расчета рецепта программы Комбикорм Оптимум v.5

В справочнике сырья (рис. 6) находится все сырье, которое может быть использовано при составлении рациона. Оно классифицировано по группам. Также в базе имеется информация о питательности каждого кормового элемента. Программа позволяет добавить, удалить либо скорректировать любой элемент базы, используя соответственно команды ,  и .

Рисунок 6. Окно справочника сырья

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

Рисунок 7. Окно нормативов

В программе также есть возможность запрограммировать способ расчета цены 1 тонны продукции с учетом всех затрат и платежей (рис. 8.).

Рисунок 8. Окно расчета цены

1.3 Программа «Корм Оптима» компании «АгроРесурс»

 Программа «Корм Оптима» также является клиент-серверным приложением на основе СУБД Firebird. Программа позволяет рассчитывать рецепты комбикормов, БВМД, премиксов для всех половозрастных групп сельскохозяйственных животных, а также рационы кормления крупного рогатого скота.

Окно расчета рецепта показано на рисунке 9. Интерфейс программы интуитивно понятен, для облегчения восприятия информации при расчете рецепта структура рациона выделяется различными цветами, в зависимости от требований к рациону.

Рисунок 9. Окно расчета рецепта

В окне расчета рецептов можно выполнить следующие операции:

- рассчитать оптимальный рецепт комбикорма или БМВД;

- добавить или удалить сырье из рецепта;

- корректировать цену сырья и ограничения по вводу в рецепт;

- добавить, удалить требования к питательности комбикорма;

- исправить показатели питательности сырья, принятого к расчету;

- задавать алгоритм отпускной цены

- выделять часть сырья из рассчитанного рецепта в концентрат и превращать ее в новый рецепт;

- получать адресные концентраты заданного процента ввода;

- анализировать минимальную и максимальную цены рентабельности сырья [14].

Для начала расчета рецепта в классификаторе продукции определяется вид рассчитываемого продукта. Удачным решением разработчиков было выделение разными цветами разной продукции, что облегчает поиск в большом списке (рис. 10).

Рисунок 10. Классификатор продукции

Определение структуры рациона производится в окне расчета рецепта. Удобно осуществляется быстрый поиск необходимого компонента с помощью команды . Поиск осуществляется без учета регистра и в любом месте названия кормов, что облегчает поиск компонента, в случае если не помнишь точное его название.

Базу кормов можно редактировать в классификаторе сырья (рис. 11.). Можно добавлять/удалять кормовые компоненты, редактировать содержание питательных веществ в каждом из них.

Рисунок 11. Классификатор сырья

Для расчета рецепта используют команду «оптимизация» . В случае если сырье не удовлетворяет потребность животных, есть возможность поиска лимитирующего показателя при помощи соответствующей команды .

После того как лимитирующий показатель найден, можно изучить содержание данного элемента в различных кормовых источниках при помощи структуры питательности рецепта, (рис. 12). Данный график наглядно иллюстрирует содержание какого питательного элемента рациона необходимо увеличить, чтобы достичь сбалансированности смеси.

Рисунок 12. Содержание питательных элементов в различных кормовых компонентах.

После расчета рецепта в данном приложении имеется возможность проанализировать сбалансированность рецепта, используя соответствующий график (рис. 13). Чем меньше отклонение величин от единицы, тем рацион ближе к необходимой питательности.

Рисунок 13. График сбалансированности рецепта

Для оформления отчета с рассчитанным рецептом в приложении есть окно с множеством настроек (рис. 14). Здесь автоматически сформированному отчету можно придать любой вид путем редактирования. Имеется возможность работы со шрифтами, изображениями и их настройкой. Это очень удобно для добавления эмблемы и информации о предприятии.

Рисунок 14. Подготовка отчета к печати.

Отредактированный отчет можно экспортировать в документ Word, Excel, рисунок BMP, JPEG, TIFF.

Приложение имеет развернутую справочную систему. Особенностью данной системы является наличие двух блоков под названием «Компоненты комбикормов» и «Компоненты премиксов», которые имеют в своем составе справочную информацию по всем питательным компонентам комбикормов и добавок. Данные блоки являются незаменимыми помощниками специалиста при составлении рецепта кормления, они систематизированы по видам кормовых компонентов, удобны в использовании (рис. 15). 

Рисунок 15. Справка «Компоненты комбикормов»

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

1.5 Сравнительная характеристика программ

Программы «Корм Оптима» и «Комбикорм Оптимум» имеют схожий графический интерфейс и очень близки по функциональности. Также нужно отметить, что оба продукта были разработаны программистами г. Воронеж.

Продукт разработчиков г. Самара «Рецепт Плюс» уступает воронежским аналогам по ряду функций:

- у нее, субъективно менее дружественный интерфейс;

- поиск компонентов баз справочников зависит от регистра символов, что усложняет работу;

- отсутствует гибкая подготовка отчета к печати.

В свою очередь приложение «Корм Оптима» превосходит «Комбикорм Оптимум» по следующим пунктам:

- наличие возможности анализа сбалансированности рецепта при помощи графиков;

- в окне расчета рецептов более простой и понятный графический интерфейс, что облегчает осваивание и привыкание к продукту;

- большее количество справочников и классификаторов, что позволяет более гибко настраивать балансируемый рецепт;

- возможность планирования закупок сырья и составления отчетов;

- наличие в составе справочной системы специальной литературы, которая оказывает существенную помощь при грамотном составлении рациона.

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

Во всех программах хорошо структурированы базы кормов и норм кормления животных. 

2 РАСЧЕТ РЕЦЕПТОВ КОРМЛЕНИЯ СВИНЕЙ СРЕДСТВАМИ MS EXCEL

2.1 Обоснование работы

В связи с дороговизной программных продуктов, описанных в аналитической части (рыночная стоимость одной копии программы данного класса варьирует в пределах от 800 до 2000 у.е.), есть смысл поиска более дешевых и доступных способов расчета рационов кормления сельскохозяйственных животных. Существенным недостатком специализированных программ является невозможность в случае необходимости быстрого совершенствования приложения, что снижает гибкость среды. В тоже время программа Lisa не обладает необходимыми функциями для быстрого составления рациона.

В связи с этим стоит проанализировать возможность самостоятельного составления программного продукта удовлетворяющего потребности специалиста. Широкие возможности, в этой сфере предоставляют электронные таблицы MS EXCEL.

Электронная таблица Microsoft Excel 97 и других модификаций является мощным программным средством для работы с таблицами, позволяющим упорядочивать, анализировать и графически представлять различные виды данных.

В племенном и промышленном животноводстве как правило, существующие показатели можно представить в виде различных таблиц и списков. Поэтому MS Excel является очень удобным средством для использования в деятельности специалистов – зооинженеров.[15]

Используя данный продукт можно создать программу для расчета рецептов с возможностью дальнейшего совершенствования  и развития данного продукта в соответствии с возникающими необходимостью. Гибкость данной среды позволяет совершать индивидуальные настройки каждому пользователю в зависимости от цели работы и поставленных задач.

Применение языка программирования Visual Basic for Applications в сочетании с электронными таблицами дает широкие возможности не только для составления и оптимизации рецепта, но и для анализа полученных результатов, составления отчетов, планирования расхода сырья.

2.2 Выполнение работы

Целью данной работы является создание программы для расчета рецептов кормления свиней средствами MS EXCEL.

При выполнении работы были поставлены следующие задачи:

1.                 Создание первоначальной базы кормов и норм кормления животных, среды расчета рациона;

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

3.                 Автоматизация процесса определения структуры рациона;

4.                 Отладка оптимизации рецепта;

5.                 Провести анализ рассчитанного рецепта.

2.2.1 Создание первоначальной базы кормов и норм кормления животных, среды расчета рациона

А) На листе «Корма» создаем базу в форме таблицы. В строках располагается список доступных кормовых средств. Столбцами данной базы, являются показатели, которые будут оптимизироваться при расчете рецепта, также слева располагается столбец выбора корма в который содержит значения либо 0 либо 1 (рис. 17). После создания заполняем таблицу данными из справочника. Эти данные будут исходными при создании структуры рациона.

Рисунок 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. Оптимизированный рецепт комбикорма

Для того чтобы облегчить восприятие сбалансированности рецепта, отклонения от нормы в случае недостатка питательного элемента выделяются красным цветом, а в случае избытка – зеленым. Для этого применим к ячейкам данного столбца условное форматирование, указав при этом два условия:

- значение больше нуля;

- значение меньше нуля.

Отображение ячейки при выполнении условия в первом случае будет зеленого цвета, во втором – красного.

2.2.5 Анализ рассчитываемых рецептов

Чтобы продемонстрировать насколько гибкая данная программа по сравнению с аналогичными приложениями продемонстрируем пример анализа содержания питательного элемента в сырье. Данная функция фигурирует в программе «Корм Оптима» как одно из преимуществ над продуктами других производителей.

Для начала отобразим скрытую область ячеек с данными о питательности кормовых компонентов. Затем выполним команду вставить диаграмму. Определим тип «Круговая». Далее укажем два диапазона данных в режиме столбцы. Первым диапазоном будет столбец с названиями кормовых компонентов, вторым – столбец с питательностью данных компонентов по необходимому элементу (в нашем примере Лизин). Далее ставим флажок в подписях данных напротив значений и получаем готовый результат (рис. 22).

Рисунок 22. Содержание питательного элемента в сырье

Таким же образом можно сделать график сбалансированности рецепта, который наглядно демонстрирует насколько хорошо данный рацион соответствует требованиям питательности рациона. Для этого выполняем команду вставить диаграмму, выбираем тип «Гистограмма», далее указываем диапазон данных из двух столбцов: столбец с названиями питательных элементов и столбец «Откл.», далее добавляем таблицу данных и нажимаем готово (рис. 23).

Рисунок 23. Сбалансированность рецепта

В результате мы получаем график, на котором отклонения от нуля отражают недостаток либо избыток питательного элемента в рационе. Составление данного графика можно автоматизировать путем записывания макроса. Выполним данное действие и полученный макрос назовем graph.

Данные примеры показали, что при грамотном использовании среды MS EXCEL, можно добиться высокой производительности программы, повторить и даже превзойти возможности коммерческих аналогов.

Заключение

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

2.                 Одним из самых трудоемких процессов, является составление и оптимизация сбалансированных, научно-обоснованных рационов кормления свиней, поэтому внедрение высококлассного программного обеспечение крайне необходимо для динамичного развития отрасли.

3.                 На сегодняшний день в Республике Беларусь ощущается нехватка отечественного специализированного программного обеспечения в сфере расчета рационов кормления, в то время как зарубежные аналоги имеют высокую стоимость.

4.                 Использование российских приложений позволяет устранить существующий дефицит программных продуктов, однако недостатки программ сложно устранить в процессе работы, что существенно замедляет развитие данного направления.

5.                 Использование электронных таблиц MS EXCEL предоставляет зооинженерам широкие возможности в реализации функциональных и дешевых решений в сфере расчетных процессов, что отражено в практической части данной работы. Однако недостатком данного решения является необходимость наличия знаний по разработке программ у специалистов.

6.                 Выбор того или иного решения должен осуществляться в соответствии с финансовыми и кадровыми возможностями сельскохозяйственных  предприятий, т.к. эти пункты являются лимитирующими.

Список использованных источников

1.                 Бражникова О.Д. Оценка программных средств автоматизации планирования и экономического анализа на сельскохозяйственных предприятиях/ О.Д. Бражникова, А.М. Дьяков// Экономика сельского хозяйства России. – 2006. – 10. – С. 24.

2.                 www.korall-agro.ru/articles/agrorinok_07_3.doc дата доступа 02.04.2008

3.                 Каширский А.И. База данных и программно-алгоритмические средства для управления селекцией в свиноводстве/ А.И. Каширский, М.К. Ландина// Агроинфо. – 2000. – С.133 – 136

4.                 Введение в компьютерную технологию животноводства // Новосибирский Государственный аграрный университет [Электронный ресурс]. – Режим доступа: #"#">#"#">#"#">http://www.kombikorm.ru/kombikorm.php#KK. – Дата доступа: 02.04.2008

15.            Введение в компьютерную технологию животноводства // Новосибирский Государственный аграрный университет [Электронный ресурс]. – Режим доступа: http://www.nsau.edu.ru/spravki/uchps/07-ZooIn.htm. - Дата доступа: 06.04.2008