Петрозаводский Государственный Университет

Математический факультет

Кафедра информатики и математического обеспечения

Промежуточный отчет

По курсовой работе

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

Выполнил: Пойкалайнен Вадим

Группа: 22405

Проверил:

Котов А.А._____________

Трофимов А.А._____________

Петрозаводск, 2004

Содержание

Введение...................................................................................................................................... 3

Постановка задачи.............................................................................................................. 3

Требования................................................................................................................................ 5

Требования к системе.............................................................................................................................................................. 5

Требования к смеси................................................................................................................................................................. 6

Математическая модель................................................................................................... 6

Состав системы..................................................................................................................... 8

Листы Excel:................................................................................................................................................................................ 8

Макросы:.................................................................................................................................................................................... 9

Заключение............................................................................................................................... 9

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

Введение

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

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

В прошлом году мною была спроектирована и реализована математическая модель решения задачи, созданы некоторые макросы подготовки модели и разработана структура системы в виде листов Excel. На этот год планируется доработка системы и внедрение ее в производство.

Постановка задачи.

Постановка данной задачи выглядит следующим образом:

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

Реализация поставленной задачи требует выполнения следующего перечня работ:

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

2. Разработать линейную оптимизационную модель, позволяющую найти оптимальный состав кормовой смеси, учитывающий требования, предъявленные к ней.

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

4. Построить в Excel математическую модель с помощью компонента “solver” (“поиск решения”).

5. Разработать в Excel макросы, способствующие созданию матрицы математической модели.

6. Получить и проанализировать решение.

7. Организовать обработку полученного решения задачи и расшифровку его к виду табуляграмм, понятных специалисту-птицеводу.

8. Выполнить модификацию модели для устранения замеченных недостатков.

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

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

Система предназначена для специалистов по кормлению птицы, а также фермеров-птицеводов.

Требования

Требования к системе формирования оптимальной кормовой смеси.

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

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

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

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

Результат должен быть представлен в понятном пользователю виде.

Зооветеринарные требования к ингредиентному составу смеси

Смесь должна содержать с минимальным отклонением от норм следующие ингредиенты: сырой протеин, сырая клетчатка, триптофан, лизин, метио-цистин, метионин, кальций, фосфор, натрий, магний, селен, линолевая кислота, витамин А, витамин D₃, витамин Е, витамин В₁, витамин В₂, витамин С. Отклонение в сторону недостатка ингредиента менее предпочтительно по сравнению с отклонением в сторону избытка. В случае недостатка витаминов в смеси, в нее добавляют витаминные добавки, которые называются премиксом. Требуется рассчитать состав премикса.

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

Математическая модель

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

Обозначения:

– множество выбранных кормов, Î;

– множество ингредиентов, Î.

- множество выбранных кормов -й группы, Î.

Управляемые факторы (переменные задачи):

- количество -го корма, принадлежащего -й группе в единице смеси;

- недостаток -го ингредиента в единице смеси,

- избыток -го ингредиента в единице смеси.

Неуправляемые факторы:

- верхняя граница доли содержания -й группы корма в единице смеси;

- нижняя граница доли содержания -й группы корма в единице смеси.

- количество -го ингредиента в единице -го корма,

- норма содержания -го ингредиента в смеси,

- цена -го корма,

- величина штрафа за единицу недостатка -го ингредиента в смеси,

- величина штрафа за единицу избытка -го ингредиента в смеси.

Целевая функция:

В целевой функции минимизируем сумму общей стоимости смеси и штрафов за дисбаланс содержания ингредиентов:

Ограничения:

Для того, что бы обеспечить потребность птицы в -м ингредиенте с учетом возможного дисбаланса запишем равенства:

Î (1)

Для определения структуры единицы смеси запишем ограничение:

(2)

Структурные ограничения на содержание кормов каждой группы в смеси:

, (3)

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

Состав системы

Листы Excel:

1. На первом листе разработана инструкция для пользователей.

2. «Справочник кормов». Этот лист содержит информацию о содержании ингредиентов в кормах, стоимость кормов, принадлежность кормов к группам кормов. Пользователь в этом справочнике отмечает, какие корма он хочет выбрать для составления рациона. На этом же листе организована таблица стоимости витаминных добавок.

3. Таблицы доли ингредиентов в единице корма для различных групп птиц расположены на листе «Нормы кормления».

4. «Поголовье птиц». Этот лист содержит таблицу численности птиц каждой группы на птицефабрике, а также их нормы кормовой смеси на день.

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

6. «Расчет». На этом листе пользователь выбирает группу птицы и производится расчет состава смеси для нее.

7. «Результат». Этот лист содержит решение задачи в виде, понятном пользователю-птицеводу.

8. «Ручной расчет». Дополнительный лист, предназначенный для автоматизации подсчета стоимости и качества смеси, выбранной пользователем. Это необходимо для оценки экономической эффективности системы и для сравнения пользователем качества смеси предложенной системой и используемой им ранее.

9. «Расчет витаминов». Проектируемый лист. Он предназначен для реализации подсчета витаминных премиксов. Данный лист будет необходим, если пользователь захочет использовать несколько типов витаминных добавок. Для реализации этого расчета понадобится разработка еще одной математической модели, которая будет являться практически копией модели, построенной для поиска решения основной задачи.

Макросы:

В системе реализованы на языке Visual Basic следующие макросы:

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

- «Shtrafi». Производит расчет «штрафов» за дисбаланс содержания каждого ингредиента и подготовку матрицы кормов, которые будут присутствовать в смеси. Пользователю необходимо его запускать при изменении набора кормов для составления смеси и изменениях в справочнике кормов. Штрафом за избыток в смеси каждого витамина служит стоимость единицы добавки данного витамина, за недостаток – двойная стоимость. Штрафы за отклонение остальных ингредиентов считаются по алгоритму, реализованному в этом макросе.

Проектируемые макросы:

- «ClrXYZ»- предназначен для инициализации (обнуления) переменных.

-.«GrKorm»- предназначен для обновления формул подсчета количества групп кормов в смеси.

Заключение

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

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