Пример 5. Оптимизация кормового рациона средствами Excel

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

Для решения оптимизационных задач необходимо, чтобы был установлен инструмент "Поиск решения", который не устанавливается при стандартной установке MS Office, а только при выборочной. Для этого в Excel выполнить команду Сервис – Надстройки и в появившемся меню Надстройки поставить флажок Поиск решения.

Задача.

На ферме в качестве корма для животных используются два продукта – M и N. Сбалансированное питание предполагает, что каждое животное должно получать в день не менее 200 килокалорий, причем потребляемое при этом количество жира не должно превышать 14 единиц.

Подсчитано, что в 1кг каждого продукта содержится:

• в продукте M – 150 ккалорий и 14 единиц жира;

• в продукте N – 200 ккалорий и 4 единицы жира.

Разработать максимально дешевый рацион откорма животных, отвечающий этим условиям, если стоимость 1кг продукта М составляет 1,5руб, а 1кг продукта N – 2,3руб.

Экономико-математическая постановка задачи:

• x1 – количество продукта М в рационе;

• x2 – количество продукта N в рационе.

• Ограничение по количеству ккалорий: 150x1 + 200x2 >= 200.

• Ограничение по количеству жира: 14x1 + 4x2 <= 14.

• Неотрицательность переменных: x1 >= 0; x2 >= 0.

• Целевая функция – минимум стоимости рациона: 1,5x1 + 2,3x2 >min.

Пример ввода исходных данных в ячейки Excel приведен на рисунке 312

Рисунок 312

Итак, в ячейки А2 и А3 вводим начальные значения x1 и x2 – нули.

В ячейки А4 и А5 вводим левые части ограничений (первоначально получатся нули), в ячейки В4 и В5 - правые части соответствующих ограничений.

В ячейку А6 вводим целевую функцию.

Ввод исходных данных завершен.

Решение задачи.

Последовательностью команд меню Сервис – Поиск решения вызываем инструмент "Поиск решения" рисунок 313.

Рисунок 313

Итак, Устанавливаем целевую ячейку - $A$6 Равной минимальному значению в поле Изменяя ячейки - $A$2:$A$3 с использованием кнопки Добавить последовательно добавляем три исходных ограничения. Нажимаем кнопку Выполнить.

Интерпретация результатов.

После вычислений на рабочем листе получили следующие результаты рисунок 314

Рисунок 314

При кормлении 0,909кг продукта М и 0,318кг продукта N потребности животного в питании будут удовлетворены, при этом стоимость рациона будет минимальной и составит 2,10руб.

Задача решена.

Контрольные задания Обработка числовой информации.Задачи без использования функций

Замечание. При решении задач данного раздела функции КОРЕНЬ, СТЕПЕНЬ и другие не использовать.

1. В ячейку В1 введите число 1, а в ячейку В2 формулу =–В1^{^}2. Проанализируйте полученный результат. Соответствует ли он результатам расчета значения x² при х = 1 по правилам алгебры? Почему? Как получить правильный результат?

2. Оформить лист для расчета площади квадрата по известной длине его стороны рисунок 315:

Рисунок 315

3. Оформить лист для определения длины окружности по известному радиусу (C = 2πR) Решение оформить в виде, представленном на (рис. 1).

4. Оформить лист для расчета площади круга по известному диаметру (К =). Решение оформить в виде, представленном на (рис. 1).

5. Оформить лист для определения объема куба по известному ребру (x = аbс). Решение оформить в виде, представленном на (рис. 1).

6. Оформить лист для определения объема шара по известному радиусу (¹/₆πD³). Решение оформить в виде, представленном на (рис. 1).

7. Подготовить лист для расчета:

а) значения функции у = 7х² – 3х + 6 при данном значении х;

б) значения функции x = 12а² + l6 при данном значении а.

Решение оформить в виде, представленном на рисунке 316.

Рисунок 316

8. Немецкий физик Д.Г. Фаренгейт в 1724г. предложил шкалу температур, отличающуюся от шкалы температур Цельсия, которая принята у нас в стране. Перевод температуры по шкале Фаренгейта в температуру по шкале Цельсия и обратно осуществляется по формулам:

где Т_ф — температура по шкале Фаренгейта. Подготовить лист для определения температуры по шкале Цельсия при заданной температуре по шкале Фаренгейта и наоборот.

9. Дано ребро куба. Найти объем куба и площадь его боковой поверхности. Решение оформить в виде, показанном на рисунке 317.

Рисунок 317

10. Дан радиус окружности. Оформить лист для определения длины этой окружности (C = 2πR) и площади ограниченного ею круга (К =).

11. Оформить лист для расчета среднего арифметического двух заданных целых чисел ().

12. Оформить лист для определения площади треугольника по известным размерам его основания и высоты ().

13. Подготовить лист для определения площади кольца по заданным внешнему и внутреннему радиусам (K =).

14. Даны катеты прямоугольного треугольника. Оформить лист для определения его гипотенузы ().

15. Оформить лист для расчета среднего геометрического двух заданных целых чисел.

16. Оформить лист рисунок 318 для нахождения корня линейного уравнения ах + b = 0 (а0) (). Проверка 2∙(–3,5) + 7 = 0.

Рисунок 318

Для того чтобы исключить появление в ячейке В4 сообщения об ошибке в случае, когда значения коэффициента а еще не задано, в ячейку В2 условно введите 1.

17. Оформить лист рисунок 319 для расчета значения функции z = х³ – 2,5ху + 1,78х² – 2,5у + 1 при любых значениях х и у.

Переменные	x	y
Значения
Значения функции z

Рисунок 319

18. Оформить лист рисунок 320 для определения значения функции х = 3,56(a + b)³ – 5,8b² + 3,8а – 1,5 при любых значениях а и b.

Переменные	a	b
Значения
Значения функции x

Рисунок 320

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

20. В ячейке В2 рисунок 321 будет записано количество информации в байтах. Получить в ячейках В3:В5 количество информации в единицах измерения, указанных в соответствующих ячейках столбца А. 1 – байт (8 бит), 1 – килобайт (1.024 бит), 1 – мегабайт (1.048.576 бит).

Рисунок 321

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

Координаты точек	x	y
Координата точки 1
Координата точки 2
Угловой коэффициент равен:

Рисунок 322

22. Подготовить лист для расчета n-го члена арифметической прогрессии по известным значениям первого члена прогрессии, ее разности и номера n. ().Оформить лист согласно рисунку 323.

a1 первый член ряда
d разность
n номер члена ряда который нужно узнать
Значение n-го члена ряда:

Рисунок 323

23. Подготовить лист рисунок 324 для расчета k-го члена геометрической прогрессии по известным значениям первого члена прогрессии, ее знаменателя и номера k. ().

a1 первый член ряда
q знаменатель прогрессии
n номер члена ряда который нужно узнать
Значение n-го члена ряда:

Рисунок 324

24. Информация о распределении суши и воды на земном шаре приведена в таблице рисунок 325:

Рисунок 325

Используя электронную таблицу, заполнить данными пустые клетки приведенной таблицы ().

25. На листе рисунок 326 приведены данные о странах, входящих в объединение Бенилюкс:

Рисунок 326

В ячейке Е6 указана средняя плотность населения по объединению в целом. Заполнить пустые клетки таблицы.

26. Оформить лист для расчета периметра прямоугольного треугольника по известным катетам.

27. Известна площадь квадрата. Оформить лист для расчета его диагонали ().

28. Даны стороны треугольника. Оформить лист рисунок 327 для определения его площади.

Дано	a	b	c
Полупериметр равен
Площадь равна

Рисунок 327

29. Подготовить лист рисунок 328 для расчета возраста Москвы в каждом из годов с 1990-го по 2002-й, если известно, что год основания города Москвы — 1147-й.

Рисунок 328

30. Получить на листе последовательность чисел 1, –2, 3, –4, 5,..., –16 рисунок 329.

Рисунок 329

Вручную значения вводить только в ячейки А1 и А2, в остальных ячейках получить их по формулам, введенным в две ячейки, которые затем скопировать на другие ячейки;

31. После решения предыдущей задачи получить в столбце B значения, представленные на рисунке 330.

A	B
1
-2	-1
3	1
-4	-1
5	1
	…
-16	1

Рисунок 330

32. Подготовить таблицу рисунок 331 соответствия между весом в фунтах и весом в килограммах для значений 1, 2,..., 40 фунтов (1 фунт = 453г).

Фунты	1	2	3	4	5	6	7	8	9	10	20	30	40
кгр.

Рисунок 331

33. Подготовить таблицу рисунок 332 перевода расстояний в дюймах в сантиметры для значений 10, 11,..., 60 дюймов (1 дюйм = 25,4мм).

Дюймы	10	20	30	40	50	60
См.

Рисунок 332

34. Последовательность Фибоначчи образуется так: первое и второе число последовательности равны 1, каждое следующее равно сумме двух предыдущих (1, 1, 2, 3, 5, 8, 13,...). Оформить лист для нахождения 233-го числа в последовательности Фибоначчи.

35. Треугольник Паскаля рисунок 333 устроен так: по левой и правой сторонам расставляются единицы, остальные элементы вычисляются как сумма “соседей сверху”: 4 = 3 + 1, 6 = 3 + 3.

Рисунок 333

Получить треугольник Паскаля на листе в двух вариантах рисунки 334 и 335:

Рисунок 334

Рисунок 335

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

36. Одноклеточная амеба каждые 3 часа делится на 2 клетки. Подготовить лист для определения количества клеток через 3, 6, 9,..., 24 часа, если первоначально была одна амеба.

37. Оформить лист для вычисления цепных дробей рисунок 336 в виде показанном на рисунке 337

Рисунок 336

1/(10+1/(9+1/(8+1/(7+1/(6+1/(5+1/(4+1/(3+1/(2+1/1))))))))) = 0,098916.

1	1	1	1	1	1	1	1	1	1
10	9	8	7	6	5	4	3	2	1	0,098916

Рисунок 337

38. Получить на листе матрицу рисунок 336 вводом одного числа, двух формул и копированием формул:

Рисунок 338