VI Международный конкурс научно-исследовательских и творческих работ учащихся
Старт в науке
Решение задачи по организации диетического питания средством анализа данных Поиск решения MS Excel
Автор работы награжден дипломом победителя III степени
Полная версия работы доступна во вкладке "Файлы работы" в формате PDF
Введение
Проблема лишнего веса в наше время стоит очень остро. Один из мудрецов сказал: «Мы - это то, что едим и пьем...». У людей с лишним весом страдает, прежде всего, энергетика: от любой работы быстро наступает усталость, которая со временем переходит в безразличие к жизни. Кроме того, страдают от повышенной нагрузки все внутренние органы. Увеличение веса тела наблюдается при неумеренном питании, малоподвижном образе жизни [3].
Один из древних лекарей отметил, что необходимо есть еду как лекарство, чтобы не принимать лекарства как еду. Пища - источник белков, жиров, углеводов, витаминов, микроэлементов и других полезных веществ, также пища является своеобразным топливом для человека. Калории, содержащиеся в продуктах питания - эквивалент энергии, которую человек получает, употребляя тот или иной продукт. Если питание правильное, сбалансированное, то человек получает полный объем необходимых ему веществ и энергии, и чувствует при этом себя здоровым, бодрым. А если режим питания нарушен, нет баланса между употребляемыми продуктами, то это чревато не только плохим самочувствием, а и серьезными заболеваниями, лишним весом, нарушением обмена веществ [3].
Рассчитанные нами данные по калорийности продуктов питания в пересчете на среднесуточные показатели для одного человека соответствуют опубликованным в литературе нормам среднесуточного потребления для людей умственного труда: 2550-2800 ккал для мужчин и 2200-2400 ккал для женщин [4,5,6].
Различные аспекты оптимизации занимают очень важное место в деятельности по организации здорового питания. Решение задач оптимизации состоит в поиске оптимального плана с использованием математических моделей и вычислительных методов, которые реализуются с помощью компьютеров и специальных программ.
Применение компьютеров позволяет заниматься исследовательской работой при решении задач из различных областей, при этом учащиеся учатся четко формулировать задачу, решать ее и оценивать полученный результат.
Использование информационных технологий позволяет решать задачи нетрадиционными способами, а также решать прикладные задачи, которые ранее не могли рассматриваться в силу сложности математического аппарата. Причем, это становится доступным учащимся, владеющим программированием недостаточно хорошо. Главным этапом становится не разработка программы, а постановка задачи (запись ограничений, задание точности решения) и исследование полученных результатов.
Тема моей исследовательской работы «Решение задачи по организации диетического питания средством анализа данных Поиск решения MS Excel».
Выбранная тема была актуальна во все времена. Но в настоящее время актуальность диетического питания особенно возросла. Изменения в сложившемся рационе питания молодых людей, характере нагрузок на организм провоцируют негативные сдвиги в состоянии их здоровья. И мое исследование направлено на создание условий для улучшения состояния здоровья современного молодого поколения.
Задачи исследования можно сформулировать следующим образом:
1) выявить возможности использования информационных технологий, в частности, прикладных программ MS Excel для расчета оптимального рациона питания;
2) разработать модель и алгоритм расчета оптимального рациона;
3) использовать результаты исследования в просветительской работе по пропаганде здорового и рационального питания.
Все расчёты выполнены при помощи средства анализа данныхПоиск решения, встроенного в программу MS Excel.
1 Средства Анализа данных
Особенностью электронных таблиц является то, что в них структурирование информации начинается непосредственно на этапе ввода данных: с самого начала своего существования в машинной форме они привязываются к структурным подразделениям таблиц - ячейкам. Статистическая обработка данных и прогнозирование, функции, реализующие статистические методы обработки и анализа данных, в Excel реализованы в виде специального программного расширения - надстройки Пакет анализа.
В рамках Excel с помощью команд, доступных из окна Анализ данных, можно провести:
- описательный статистический анализ (Описательная статистика);
- ранжирование данных (Ранг и персентиль);
- графический анализ данных (Гистограмма);
- прогнозирование данных (Скользящее среднее, Экспоненциальное сглаживание);
- регрессионный анализ (Регрессия) и др.
Широкое применение на практике находят функции и режимы Excel, предназначенные для поиска решения уравнений и оптимизационных задач.
В Excel имеются два мощных средства для анализа данных:
1. ПОДБОР ПАРАМЕТРА – служит для определения входной величины, обеспечивающей определенное значение функции. Подбор параметра - удобный и простой для понимания инструментом решения уравнений. Он реализует алгоритм численного решения уравнения, зависящего от одной или нескольких переменных. Процесс решения с помощью данного метода распадается на два этапа: 1. Задание на рабочем листе ячеек, содержащих переменные решаемого уравнения (так называемых влияющих ячеек), и ячейки, содержащей формулу уравнения (зависимой или целевой ячейки). 2. Ввод адресов влияющих и целевой ячеек в диалоговое окно Подбор параметра и получение ответа (или сообщения о его отсутствии/невозможности найти).
ПОИСК РЕШЕНИЯ – позволяет определить величину или группу величин, обеспечивающих оптимальное в некотором смысле значение функции при заданных ограничениях [2].
Для средства «Поиск решения»ячеек-параметров может быть несколько.
Иными словами, если значение в целевой ячейке зависит от содержимого нескольких ячеек-параметров, или на входные параметры накладываются ограничения, потребуется использование средств Поиск решения.
Вызов Поиска решения: кнопка «Office» - параметры Excel – надстройки – перейти… - Поиск решения [1].
Так в моей работе необходимо определить группу величин, то я использую средство анализа Поиск решения.
1.1 Поиск решения
ПОИСК РЕШЕНИЯ – позволяет определить величину или группу величин, обеспечивающих оптимальное в некотором смысле значение функции при заданных ограничениях [1].
Обычными задачами, решаемыми с помощью средства Поиск решения, являются задачи оптимизации. Задачи, которые решаются с помощью средства "Поиск решения", имеют три свойства:
имеется КРИТЕРИЙ, показывающий в каком смысле принимаемое решение должно быть оптимальным (максимальное значение, минимальное значение или заданное какому-то конкретному значению);
имеются ОГРАНИЧЕНИЯ, выражающиеся в виде неравенств. Под ограничениями понимаются соотношения типа А1<=B1, А1=B1, А1> = 0;
имеется НАБОР ВХОДНЫХ ЗНАЧЕНИЙ - переменных, непосредственно или косвенно влияющих на ограничения и на оптимизирующие величины [1].
1.2 Этапы решения задач оптимизации
При решении задач оптимизации следует придерживаться следующего алгоритма действий:
постановка задачи, построение математической модели;
создание формы для ввода условий задачи;
ввод исходных данных в созданную форму;
ввод зависимостей из математической модели (для целевой функции, для ограничений);
РАБОТА в диалоговом окне "Поиск решения" (м. Сервис – Поиск решения): заполнение соответствующих полей
В диалоговом окне Поиск решения необходимо выполнить следующие действия:
5.1. задание адреса целевой ячейки в поле
5.2. задание критерия оптимизации в поле Равной: (установить переключатель в зависимости от требуемого критерия:
Например: )
5.3. задание адресов поисковых переменных (ячеек - параметров) в поле
5.4. задание ограничений. Чтобы определить набор ограничений кнопку Добавить в поле Ограничения.
В появившемся диалоговом окне Добавление ограничения необходимо задать ограничения.
При задании ограничений необходимо ввести а) адрес ячейки ограничения, б) оператор > = или <= и т.д., в) ограничивающее значение или адрес ячейки этого значения
Решение задачи оптимизации ↓ Выполнить в окне Поиск решения (в диалоговом окне Поиск решения – кнопка Параметры служит для уточнения параметров).
Сохранение найденного решения (в диалоговом окне Результаты поиска решения установить переключатель Сохранить найденное решение - ↓ ОК).
Примечание: в случае необходимости в диалоговом окне Результаты поиска решения возможны другие варианты при работе с полученными результатами (отмена, сохранение как сценария), а также создание отчета
Компьютер является единственно возможным средством решения сложных задач оптимизации. Для решения задач оптимизации пользуются математическим аппаратом электронных таблиц Excel [2].
2 Решение задачи по организации диетического питания с помощью электронных таблиц
2.1 Цель моделирования
В ходе выполнения работы происходит практическое знакомство с электронными таблицами Microsoft Excel, приобретение навыков работы с данными различных типов, различными видами ссылок на ячейки электронной таблицы, формулами, математическими функциями рабочего листа Microsoft Excel, приемами заполнения таблиц; умений пользоваться средством анализа данных Поиск решения; решается задача по организации диетического питания.
2.2 Формализация задачи
Задача формулируется следующим образом: решить задачу по организации диетического питания средством анализа данных Поиск решения MSExcel. Найти количество каждого продукта, при котором общая калорийность продуктов питания в пересчете на среднесуточные показатели для одного человека не превышает 2400 ккал, в среднем соотношение количества употребляемых белков, жиров и углеводов должно составлять 1:1:4 (набор продуктов может варьироваться) [8].
Для решения задачи необходимо составить формулы. Формулы представляют собой выражения, по которым выполняются вычисления на странице. Формула начинается со знака равенства (=). В формулах используются операции: арифметические, сравнения, логические.
Порядок выполнения арифметических действий и определенных операций представлен в таблице (рис. 1).
Рис.1 Порядок выполнения арифметических действий и операций.
Для изменения порядка выполнения операций используются круглые скобки.
Пример: =(А2+В2)/(1-3,5*В2^2).
2.2.1 Виды адресации в электронных таблицах
При обращении к ячейкам используется относительная, абсолютная и смешанная адресация [1, 2].
Адрес ячейки образуется на пересечении столбца и строки.
1) Относительная адресация (примеры: А6, В3:Н5): при использовании относительной адресации в формулах приложение MS Excel запоминает расположение относительно текущей ячейки.
Относительный адрес – это адрес, который ссылается на ячейку, основываясь на ее текущее местоположение.
Например: при вводе в ячейку В4 формулу = В1+В2. Excel понимает формулу, как "прибавить" содержимое ячейки, расположенное тремя рядами выше, к содержимому ячейки двумя рядами выше.
Если скопировать формулу = В1+В2 из ячейки В4 в С4, Excel также понимает формулу как "прибавить" содержимое ячейки, расположенное тремя рядами выше, к содержимому ячейки двумя рядами выше. Таким образом, формула в ячейке С4 примет вид = С1+С2
2) Абсолютная адресация (примеры: $A$6, $В$3:$Н$5): если при копировании формул необходимо сохранить ссылку на "конкретную" ячейку или область, то нужно воспользоваться абсолютной адресацией.
Абсолютный адрес - это адрес, который ссылается на ячейку, используя ее точный адрес, и не изменяется при копировании ячейки с формулой.
Для задания абсолютного адреса перед именем столбца и перед номером строки необходимо ввести знак $. Например: $В$4, $В$3:$Н$5
3) СМЕШАННАЯ АДРЕСАЦИЯ (примеры: В$4, $Н5)
Символ $ ставится только перед неизменяемой частью адреса, при копировании один параметр адреса изменяется, а другой – нет:
В$4 – адрес столбца при копировании меняется, а номер строки всегда четвертый;
$Н5 – адрес столбца не меняется при копировании, а адресация строк меняется.
Примечание: Для замены вида ссылки на адрес ячейки в строки формул выделить нужный адрес, нажать F4, пока ссылка не отобразится в нужном виде.
2.3 Разработка модели
Постановка задачи: найти массу продуктов: мяса, рыбы, молока, масла, сыра, крупы, картофеля, чтобы общая калорийность продуктов питания в пересчете на среднесуточные показатели для одного человека не превышала 2400 ккал, соотношение количества употребляемых белков, жиров и углеводов составляло приблизительно 1:1:4.
Алгоритм работы:
1 этап. Заполнение в электронной таблице диапазона ячеек данными (числовыми, формульными) для нахождения оптимального значения.
СОЗДАТЬ файл в приложении MSExcel с произвольным именем и расширением *.xls.
СОЗДАТЬ таблицу на листе 1 книги (файла), начало которой начало которой представлено на рис. 2.
Рис.2 Рацион питания
ЗАПОЛНИТЬ диапазон В3:В5, В7 и D3:J5 числами, указанными на рис. 3;
Рис.3 Рацион питания
ЗАПОЛНИТЬ диапазон D7:J7, значениями калорийности заявленных продуктов
Примечание: данные взяты из диетологических таблиц [4, 8].
ЗАНЕСТИ в ячейку С3 формулу расчета значений функции массы белков в заявленных продуктах, употребляемых за день.
Формула и результат для ячейки С3 представлен в таблице:
Таблица 1. Формула в Excel
|
Формула |
Результат |
|
=СУММПРОИЗВ(D3:J3;$D$6:$J$6) |
85 |
Примечание: при записи функции использовали соответствующую адресацию ячеек.
СКОПИРОВАТЬ формулу из ячейки С3 для остальных ячеек диапазона С3:С5, используя маркер заполнения.
СРАВНИТЬ результат с фрагментом (на рисунке 4 представлено окончание таблицы):
Рис.4 Фрагмент таблицы
2 этап. Нахождение оптимального решения: массы потребляемых продуктов (мяса, рыбы, молока, масла, сыра, крупы, картофеля).
ЗАПОЛНИТЬ диапазон D7:J7 нулями (0) - Ячейки параметр
РАЗМЕСТИТЬ курсор в ячейке С7 – целевая ячейка
Запустить средство Поиск решения (MSExcel 2007) (кнопка Office – параметры – надстройки – перейти… - Поиск решения)
ЗАПОЛНИТЬ диалоговое окно Поиск решения согласно рисунку 5.
Рис.5 Окно средства анализа данных Поиск решения
НАЖАТЬ на кнопку Выполнить.
НАЖАТЬ на кнопку ОК.
Результат представлен на рисунке 6.
Рис.6 Результат расчета
Результат решения задачи: масса продуктов: мяса (0,07 кг), рыбы (0,06 кг), молока (0,04 кг), масла (0,08 кг), сыра (0,10 кг), крупы (0,38 кг), картофеля (0,11 кг), общая калорийность продуктов питания в пересчете на среднесуточные показатели для одного человека не превышает 2400 ккал, соотношение количества употребляемых белков, жиров и углеводов составляет приблизительно 1:1:4.
Результаты исследования
Перед проведением исследования были сформулированы его задачи, в ходе выполнения исследовательской работы задачи были решены и реализованы на практике:
1) выявлены возможности использования информационных технологий, в частности, прикладной программы MS Excel для решения задачи по организации диетического питания средством анализа данных Поиск решения MS Excel;
2) разработана модель и алгоритмы решения задачи в среде прикладной программы MS Excel;
3) результаты исследования рекомендованы к использованию в просветительской работе по пропаганде здорового и рационального питания. В таблицу для расчетов можно добавить потребляемые продукты, можно удалить. И произвести индивидуальный подбор количества потребляемых продуктов.
Полученные результаты были предложены одноклассникам для ознакомления (18 человек — 10 девушек и 8 юношей). Оказалось: 5 (28%) впервые слышат о существовании баланса в потребляемых продуктах ; 9 человек(50%) знают о сбалансированном питании, но не придают этому значение; 4 человека (девушки) (22%) стараются придерживаться сбалансированного питания.
Все участники опроса отметили, что будут пользоваться предложенной таблицей для расчета необходимого количества продуктов.
Использование информационных технологий позволяет решать задачи нетрадиционными способами, а также решать прикладные задачи, которые ранее не могли рассматриваться в силу сложности математического аппарата.
Заключение
Использование информационных технологий позволяет решать задачи нетрадиционными способами, а также решать прикладные задачи, которые ранее не могли рассматриваться в силу сложности математического аппарата. Причем, это становится доступным учащимся владеющим программированием недостаточно хорошо. Главным этапом становится не разработка программы, а постановка задачи (запись ограничений, задание точности решения) и исследование полученных результатов.
В данной работе показана возможность применения информационных технологий для решения повседневных задач, в частности применения MSExcel для расчета калорийности диетического питания. В современном обществе эта тема является актуальной: изменения в сложившемся рационе питания молодых людей, характере нагрузок на организм провоцируют негативные сдвиги в состоянии их здоровья. С молодыми людьми необходимо вести просветительскую работу по правильному питанию, сбалансированности потребляемых продуктов, о их пользе и вреде. Научить молодежь самим следит за качеством потребляемых продуктов и их количеством. Помощником в этом вопросе могут быть информационные технологии, в частности результат исследований данной работы.
Ход проведения исследования показал, что использование прикладной программы MS Excel при решении задач оптимизации способствует:
Повышению интереса к информационным технологиям;
Решению повседневных задач нетрадиционными способами.
Список используемой литературы
Киселев С.В. Оператор ЭВМ: учебник для нач. проф. образования / С.В. Киселев. – М.: Издательский центр «Академия», 2006. – 352 с.
Угринович Н.Д. Информатика и информационные технологии. Учебник для 10-11 классов / Н.Д. Угринович. – 2-е изд. – М.: БИНОМ. Лаборатория знаний, 2010. – 511 с.: ил.
Гущо Юрий. 12 ключей от сейфа долголетия. Редакция журнала «Партнер. Dortmund, 2013.
http://www.poedim.ru/pp/
http://www.babyeda.ru/colory.html
http://www.poedim.ru/pp_energy/
http://exsolver.narod.ru/LM/LM_eat.html
http://tele-conf.ru/teoreticheskaya-i-prikladnaya-biologiya-i-meditsina/individualnyiy-raschet-pischevogo-ratsiona.html
Приложение
Глоссарий
ЦЕЛЕВАЯ ЯЧЕЙКА содержит целевую функцию (формулу), для которой отыскивается оптимальное значение (максимальное, минимальное или заданное какому-то конкретному значению).
ЦЕЛЕВАЯ ФУНКЦИЯ показывает, в каком смысле решение должно быть оптимальным, т.е. наилучшим.
ЯЧЕЙКА-ПАРАМЕТР – та ячейка, для которой средствами Excel, подбирается нужный параметр. ЯЧЕЙКА-ПАРАМЕТРвлияет на целевую ячейку, но сама не содержит формулы.
ФОРМУЛА – выражение, состоящее из констант, ссылок, функций, соединенных знаками арифметических и/или логических операций.
КОНСТАНТА представляет собой готовое (невычисляемое) значение.
ССЫЛКА – адрес объекта (ячейки, строки, столбца, диапазона), используемый при записи формулы.
ФУНКЦИЯ - это переменная величина, значение которой зависит от значений аргументов.