Как составить рацион в экселе

Расчетная таблица MS Excel для проектирования рациона: электронный ресурс

Кафедра кормления и гигиены животных УГАВМ, 2010 г А.И. Дворницын, И.В. Шарыгин, О.М. Иващенко 

Предлагаемый программный продукт предназначен для студентов 2-3 курса факультетов ветеринарной медицины и биотехнологии для работы на практических занятиях по курсу «Кормление сельскохозяйственных животных». Он повышает эффективность практических занятий и может так же быть использован при выполнении заданий в процессе самостоятельной работы и расчетов при выполнении курсовой работы.

Другие материалы на тему

ХОД УРОКА

1. Ввод в проблему (10 мин)

Я: Доброе утро!

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

Много факторов влияет на наше здоровье, одним из основных является питание.

Давайте обратимся к народным поговоркам (прочитать поговорки).

• Какой смысл Вы видите в этих мудрых словах?

Ответ: питаться, не причиняя вреда здоровью.

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

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

Я: известно, что в теле взрослого человека, массой около 70 кг, содержится примерно 40 кг воды, 15 кг белка, 7 кг жира, 0,7 кг углеводов.

Подсчитано, что в среднем за 70 лет жизни человек выпивает 50 т воды, съедает 2,5 т белка, 2,3 т жира, свыше 10 т углеводов. 

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

• Как мы с вами можем узнать, сколько белков, жиров и углеводов употребили в пищу в течение суток?

Ответ: Необходимо произвести расчеты.

Я: Но делать это вручную или с помощью калькулятора трудоемко и нерационально.

• Какая программа может помочь нам в этом?

Ответ: ТП Excel

Я: Вот мы и подошли с вами к теме урока. Помогите мне ее сформулировать.

«Расчет суточного рациона питания в MS Excel» 833

Эпиграфом будут слова амер. политика

• Как вы думаете,   что будет являться целями нашего урока?

1. Познакомится с алгоритмом решения задач с использованием табличного процессора MS Excel;
2. Оценить возможность применения табличного процессора в профессиональной деятельности;
3. Научиться сопоставлять и анализировать результаты вычислений с использованием табличного процессора;
4. Раскрыть необходимость рационального режима питания, сбалансированного по составу белков, жиров и углеводов.

Я: Существуют физеологические нормы суточной потребности в пещевых веществах и энергии.

Поэтому основной задачей сегодня на уроке для нас будет – расчет суточного рациона питания с учетом физеологических норм.

Для решения основной задачи нашего занятия нам необходимо: 

1.        Составить суточное меню;

2.        Изучить алгоритм расчета энергетической ценности рациона;

3.        Автоматизировать расчет суточного потребления с помощью Excel;

4.        Исследовать полученный результат;

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

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

2 этап: Рассмотрим алгоритм расчета энергетической ценности рациона.

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

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

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

Я: мы можем изменять количество белков, жиров и углеводов, которое содержится в 100 г. продукта? НЕТ!  Это исходные неизменяемые данные.

• Какие данные еще нам известны? МАССА. Мы можем выбрать любую массу продукта? ДА! Это тоже исходная величина, но изменяемая.
• Какие данные будут результатом?

Ответ: Энергетическую ценность, масса белков, жиров и углеводов в составленном нами меню. Это результат и эта величина является расчетной.

• Какие ещё данные в нашей задаче являются расчетными?

Ответ: общее количественно белков, жиров и углеводов и энергетическая ценность всего дневного рациона –  это тоже расчетные величины.

• Найденные данные будут являться окончательным результатом?

Ответ: Нет. Нам нужно проверить, будут ли полученные данные соответствовать нормам, и если нет, то продолжить поиск верного решения. Это и будет исследованием полученного результата.

3 этап. Мы с вами определились, что справиться с поставленной задачей нам поможет ТП Excel. 840

Я: перед вами подготовленная таблица с исходными данными, в которой указаны продукты и их состав на 100 гр. Указано также, какие величины исходные, изменяемые, а какие расчетные.

В ячейки столбца Е вводится масса продукта, согласно составленного вами меню.

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

Аналогично рассчитываем содержание жиров и углеводов.

• Формула обязательно начинается со знака =
• в формуле указываем адреса ячеек, в которых находятся исходные данные.
• Как будем заносить формулы в ячейки по каждому столбцу. Писать вручную? Копируем с помощью маркера автозаполнения.

Я: существуют справочные данные. Они указаны в вашем раздаточном материале, и сейчас мы их видим на экране.

Энергетическая ценность 1 гр. белков составляет 17,2 кДж (1калория=4,1868 Дж).

Энергетическая ценность 1 гр. углеводов составляет 17,2 кДж.

Энергетическая ценность 1 гр. жиров составляет 39,1 кДж.

По этим данным энергетическая ценность продукта, записанного в ячейку A4, рассчитывается по формуле:

17,2*(белки + углеводы) +39,1*жиры, т.е.

в ячейку I4 заносится формула     =17,2*(F4+H4) +39,1*G4

Копируем формулы вниз.

В ячейки диапазона F87:I87 записываем формулы для нахождения суммарных величин.

1. Самостоятельная практическая работа учащихся за компьютером. (20 мин)

Приступаем к выполнению практической части урока. Технология работы расписана в вашем раздаточном материале. Не забываем о правилах ТБ. В ходе выполнения заданий, необходимые данные заносим в лист отчета. На работу 20 мин. По окончании работы возвращаемся на свои места.

1. Гимнастика для глаз (1 мин.)
2. Обсуждение и анализ результатов вычислений (5 мин).

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

1. Защита работ по предложенному плану (5 мин.)  
2. Подведение итогов (3 мин).

Давайте поведем итог нашему занятию.

• Реализовали ли знания и навыки решения задач с помощью ЭТ?
• Какие затруднения и ошибки возникли в ходе работы, как справлялись с трудностями?
• Остались ли какие-нибудь нерешённые вопросы?
• Как вы оцениваете возможность применения ЭТ в профессии?

Выводы

1. Домашнее задание (1 мин.)

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

В практике
зооинженера и специалиста сельского
хозяйства приходится решать множество
оптимизационных задач – это и оптимизация
оборота стада, оптимизация севооборота,
и т.д. Рассмотрим решение простейшей
задачи оптимизации кормового рациона
средствами 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 в рационе.

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

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

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

• Целевая
  функция – минимум стоимости рациона:
  1,5×1 + 2,3×2
  >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