Численное моделирование в среде MS EXCEL

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

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

Задача 1. Определение максимального объема коробки.

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

В задаче рассматривается процесс преобразования одного объекта (картонного листа) в другой (коробку). Исходный объект – картонный лист – имеет заданные геометрические размеры: длина стороны а. Созданный объект – коробка характеризуется объемом, а вырезы – размером стороны и площадью.

Рисунок 1

Геометрическая модель.
Математическая модель.
Расчетные формулы:
С = а-2b – длина стороны дна;
S = c² – площадь дна;
V = Sb – объем.
Здесь а – длина стороны картонного листа, b – длина выреза.

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

Заполнить вниз пока длина выреза не будет равна 20, а длина стороны равна 0.

Задание для самостоятельного решения:

1. Проведите расчеты с шагом увеличения 0,5см.
2. Определите по столбцу Объем наибольший объем коробки для выреза с шагом увеличения 1 см.; с шагом увеличения 0,5 см. Сравните их.
3. Решите следующую задачу.
   В прямоугольном треугольнике задана длина гипотенузы с. Найти размеры катетов, при которых треугольник имеет наибольшую площадь. Составьте геометрическую и математическую модели. Провести расчеты.

Задача 2. Определение наибольшей выручки.

Несколько человек решили организовать видео кафе на 6 столиков по 4 места за каждым. С каждого посетителя будет взиматься плата за сеанс видеофильма и ужин (всем посетителям будет предлагаться один и тот же набор блюд). Администрация города постановила, что плата за вход не должна превышать 500 рублей. Требуется определить такую входную плату, при которой будет получена наибольшая выручка.

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

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

Обозначим входную плату через X. Тогда среднее число посетителей кафе является функцией от Х. Обозначим эту функцию через Р(Х). В задаче требуется найти такое значение X, при котором выручка, равная произведению входной платы на количество посетителей Х * Р(Х), достигает максимума. Если бы функция Р(Х) была известна, то найти требуемый максимум не составило бы особого труда. Но эта функция неизвестна, поэтому попробуем найти хотя бы общий вид функции. Его можно указать, обобщив опыт работы подобных кафе: P(x) = ax² + bx + c (1).

Коэффициенты а, b и с для каждого кафе свои. Как же их определить? Проще всего найти значение с. Представьте себе невообразимое – в кафе пускают бесплатно (т. е. X = 0). Ясно, что свободных мест не будет. Следовательно, Р(0) равно числу мест в кафе. С другой стороны, подставив 0 вместо X, получим P(0) = с. Значит, с равно количеству мест. В нашем случае с = 24 (6 столиков по 4 места за каждым).

Определить а и b так же просто не удается. Справочников по посещаемости кафе еще нет. Поэтому здесь требуется эксперимент.

Достаточно открыть кафе и установить на некоторый срок (дней на десять) определенную плату за вход. Среднее число посетителей и даст нам (приближенное!) значение функции. Установив другую плату за вход, найдем приближенное значение Р(Х) при новом X, и так несколько раз.

Зависимость посещаемости от входной платы (на основе экспериментальных данных для конкретного кафе):

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

Составьте следующую таблицу.

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

I. В столбце С подсчитайте выручку на основе экспериментальных данных:

1. В ячейке С3 наберите формулу = А3*В3;
2. Скопируйте формулу в ячейки С4:С10.

II. Подбираем приближенные значения коэффициентов a и b, выполнив следующие действия:

1. Построим диаграмму по экспериментальным данным типа Х-У (Х – входная плата, У – данные по количеству посетителей).
2. Аппроксимируйте полученную кривую. Для построения линии тренда:
   • выделить линию графика (щелкнуть мышью по линии графика);
   • вызвать контекстно-зависимое меню и выполнить команду Добавить линию тренда.
     Откроется окно Линия тренда. Далее:
     • выбрать полиномиальный тип линии тренда;
     • выбрать вкладку Параметры;
     • установить параметр Показывать уравнение на диаграмме ;
     • указать У – пересечение = 24;
     • нажать кнопку OK.

Рисунок 2

3. Внесем полученные значения коэффициентов а и b в ячейки G3 и H3 и присвоим им имена: G3 имя а, H3 имя b.

III. Вычислим теоретическое количество посетителей и теоретическую выручку, причем С = 24:

1. В ячейке D3 запишем формулу (1): = a*(A3*A3) – b*A3 + 24;
2. Скопируйте формулу в ячейки D4:D10;
3. В ячейке E3 запишем формулу = D3*A3;
4. Скопируйте формулу в ячейки E4:E10.

IV. Вычислим отклонение между экспериментальной и теоретической выручкой и погрешность:

1. В ячейку FЗ занести формулу = ABS(EЗ – C3);
2. Скопировать формулу в ячейки F4:F11;
3. Затем вычислим погрешность – это максимальное отклонение. Для этого в ячейку F11 заносим формулу, содержащую функцию определения максимального из чисел этого столбца = МАКС(F3:F11).

V. Подберем коэффициенты a и b, стараясь минимизировать погрешность. Для этого:

1. Выберем команду Сервис | Поиск решения.
2. Настройте параметры в окне диалога Поиск решения:
   • в поле Установить целевую ячейку укажите адрес ячейки $F$11 (в ней погрешность);
   • установите переключатель Минимальному значению;
   • в поле Изменяя ячейки укажите $G$3:$H$3;
   • нажмите на кнопку Выполнить, начнется поиск решения;
   • установите кнопку Сохранить найденное решение, чтобы сохранить предложенные значения;
   • нажмите на кнопку ОК.

Задания для учащихся:

1. Постройте на одной диаграмме два графика (экспериментальный и теоретический) зависимости количества посетителей от входной платы;
2. Постройте на одной диаграмме два графика (экспериментальный и теоретический) зависимости выручки от входной платы;
3. Определите, при какой входной плате выручка будет максимальна;
4. Каково среднее число посетителей сеанса при найденной оптимальной входной плате.

Смоделируем еще одну ситуацию. Цель моделирования – эффективность управления объектом.

Задача 3. Оптимизационная задача.

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

К таким задачам, например, относятся:

• задача нахождения наиболее рационального использования сырья и материалов;
• задача определения наиболее выгодных производственных режимов;
• задача повышения эффективности работы транспорта и т.п.

Рассмотрим следующую задачу: цех молокозавода выпускает эскимо и другой вид мороженого (назовем его просто "мороженое"). Эскимо в 2 раза дороже мороженого. За 1 мин выпускается 90 порций мороженого или 30 порций эскимо, возможен одновременный выпуск двух видов продукции. Из-за ограничения срока реализации продукции и недостаточного объема холодильных камер в течение часа на хранение может быть принято не более 3600 штук изделий. Определите наибольшую стоимость выпускаемой продукции молокозавода и оптимальный план выпуска мороженого и эскимо за 1 мин.

Решим задачу с применением надстройки Поиск решения приложения Microsoft Excel.

Формализация задачи.

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

Обозначим число выпускаемых за 1 мин порций эскимо x, мороженого – у, t₁ – время, необходимое для производства одного эскимо; t₂ – время, необходимое для производства одного мороженого.

Из условия задачи следует, что за 1 мин производится 90 порций мороженого или 30 порций эскимо, то есть времени на производство одного эскимо затрачивается в 3 раза больше, чем на производство одного мороженого: t₁= 3*t₂.

За 1 мин соотношение времени при одновременном выпуске каждого из двух видов продукции x и y составит: t₁x +t₂y 1 или, подставляя t₁ = 3*t₂, получаем 3t₂x +t₂y 1. Отсюда

t2(3x + y) 1;

Ho величина 1/t₂ – это максимальный выпуск мороженого за 1 мин, т. е. она равна 90.

Итак, возможности производства определяют условие 3x + у 90.

Еще одно условие – ограниченная емкость холодильника. B течение 1 ч холодильник может принять 3600 штук продукции, то есть за одну минуту 3600/60 = 60 порций: x + у 60.

Обозначив цену одного эскимо C₁ (руб.), а цену одного мороженого – C₂ (руб.), можно записать в соответствии с условием задачи следующее соотношение цен на продукцию: C₁= 2C₂.

Общая стоимость продукции, выпускаемой цехом за 1 мин: S = C₁x + C₂y.

Заменяя С₁ на С₂ получим: S =2C₂x + C2y или S = C₂(2x + y).

Поскольку C₂ – заданная положительная константа, то для упрощения задачи можно принять C₂ = 1. По условию задачи необходимо найти возможную наибольшую стоимость выпускаемой продукции. Таким образом, следует добиваться максимального значения целевой функции S = 2x + y.

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

Итак, учитывая все условия задачи, приходим к ее математической модели: Среди целочисленных решений системы линейных неравенств

найти такое, при котором достигается максимум линейной функции S = 2x + y.

Решение с помощью надстройки "Поиск решения".

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

Таблица 1.

B описанной модели необходимо максимизировать значение в ячейке B11.

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

Таблица 2.

Выполните следующие действия:

1. Выделите ячейку с оптимизируемым значением B11.
2. Выберите команду Сервис | Поиск решения. Загрузится надстройка, и появится диалоговое окно Поиск решения.
3. В поле Установить целевую ячейку уже находится ссылка на выделенную на первом шаге ячейку (при необходимости эту ссылку можно изменить).
4. Установите переключатель Равной равным максимальному значению (ищется максимальное значение целевой ячейки B11).
5. Перейдите в поле Изменяя ячейки и укажите диапазон ячеек (или введите ссылки на них), которые должны изменяться в процессе поиска наилучшего решения. B данном примере это ячейки $B$4:$B$5.
6. Щелкните на кнопке Добавить, чтобы ввести первое ограничение задачи. Откроется диалоговое окно Добавление ограничения.
7. Введите первое ограничение: $B$4:$B$5 >= O. Для этого, находясь в поле, Ссылка на ячейку, укажите ячейки мышью или введите диапазон с клавиатуры. Нажмите клавишу Tab или щелкните на стрелке раскрывающегося списка и выберите знак отношения (>=). Щелкните на кнопке OK. B поле Ограничение введите 0.

   Щелкните на кнопке Добавить, введите следующее ограничение и щелкните на кнопке OK.

8. Щелкните на кнопке Выполнить.
9. Установите кнопку Сохранить найденное решение, чтобы сохранить предложенные значения. С помощью этого диалогового окна можно сформировать отчет.
10. Щелкните на кнопке OK. Получится решение, представленное ниже.

Примечание. С помощью клавиши ЕSC можно прервать слишком затянувшийся процесс поиска решения.

Итак, при выпуске 15 штук эскимо и 45 штук мороженного максимальная прибыль составит 75 единиц за минуту.

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

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

Литература:

1. Информатика в школе / Под ред. Макаровой Н. В. – СПб: Питер Ком, 1999.- 304 с.
2. Символоков Л. В. Решение бизнес задач в Microsoft Office – М.: ЗАО "Издательство БИНОМ", 2001.- 512 с.
3. Шохолович В. Ф. Информационные технологии обучения. Информатика и образование. 1998, №2.
4. Игнекова Г. С. Методические аспекты подготовки учителя информатики. Информатика и образование. 1998, №3.
5. Сулейманов Р. Р. Решение математических задач на уроках информатики. Информатика и образование. 1999, №6.