Использование электронных таблиц MS Excel для компьютерного моделирования

Цели мероприятия:

1. Дидактические:

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

2. Развивающие:

• развитие познавательного интереса, воображения;
• развитие умений применять знания на практике.

3. Воспитательные:

• расширение научного кругозора;
• воспитание самостоятельности в работе.

Учебно-методическое обеспечение: презентация (Презентация), ПО MS Excel, ПО MS PowerPoint, методические указания.

Оборудование: мультимедийная установка, персональные компьютеры.

Ход конференции

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

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

Теоретическая часть

Преподаватель: Рассмотрим этапы информационного моделирования.

1. Модель задачи.

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

2. Понятие математической модели.

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

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

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

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

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

Простой пример. Представьте, что нужно определить площадь поверхности письменного стола. Как обычно поступают в таком случае? Измеряют длину и ширину стола, а затем перемножают полученные числа. Это фактически означает, что реальный объект – поверхность стола – заменяется абстрактной математической моделью – прямоугольником. Площадь этого прямоугольника и считается искомой величиной.

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

Предположив, что поверхность стола – прямоугольник, мы легко указываем исходные данные и находим результат. Они связаны соотношение S = a * b. (Презентация. Слайд 5)

Сделанное предположение позволило «перевести» нашу задачу на язык чисел: и исходные данные, и результат – числа, а соотношение между ними задается математической формулой.

Анализировать математические модели проще и быстрее, чем экспериментально определять поведение реального объекта. Кроме того, анализ математической модели позволяет выделить наиболее существенные свойства данного объекта (процесса), на которые надо обратить внимание при принятии решения.

3. Этапы решения задач на компьютере.

1 этап. Постановка задачи – точная формулировка условий и целей решения, описание наиболее существенных свойств объекта. (Презентация. Слайд 6)

2 этап. Построение математической модели – описание наиболее существенных свойств объекта с помощью математических формул. (Презентация. Слайд 6)

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

1. Построение алгоритма решения задачи и его кодирование на одном из языков программирования.
2. Построение компьютерной модели и использованием ПО компьютера (приложений Windows – электронных таблиц, СУБД и пр.). (Презентация. Слайд 7)

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

5 этап. Анализ полученных результатов и корректировка модели – в случае различия результатов, полученных при исследовании модели, с измеряемыми параметрами реальных объектов можно сделать вывод, что на предыдущих этапах построения модели были допущены ошибки или неточности. В этом случае необходимо провести корректировку модели, причём уточнение модели может проводиться многократно, пока анализ результатов не покажет их соответствие изучаемому объекту. (Презентация. Слайд 9)

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

Практическая часть

Студент 1:

Задача 1. Необходимо покрасить краской стены кухни. Сколько потребуется банок краски, если известно, что

• размеры кухни 405 × 310 × 285 см;
• 88% площади стен занимает кафельная плитка;
• 1 банка краски предназначена для покраски площади 5 м²? (Презентация. Слайд 10)

Решение.

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

Дано:

a = 405 см – длина комнаты,
b = 310 см – ширина комнаты,
c = 285 см – высота комнаты,
1 – 0,88 = 0,12 – часть комнаты для покраски (без кафеля),
5 м² – площадь покраски при использовании 1 банки краски.

Найти: необходимое для покраски стен кухни количество банок краски. (Презентация. Слайд 11)

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

S_{стен с кафелем} =2(a + b)c.
S_{стен для покраски} = 2(a + b)c * 0,12.

Чтобы определить, сколько потребуется банок краски, надо площадь для покраски разделить на 5 м², т. е. S_{стен для покраски} /5 и результат округлить до целых.

Моделирование в среде ЭТ.

Заносим данные задачи в электронную таблицу, вводим формулы.
Электронная таблица в режиме отображения формул. (Приложение 1. Презентация. Слайд 12)
Электронная таблица в режиме отображения значений. (Приложение 2. Презентация. Слайд 13)
С помощью MS Excel мы определили, что для покраски стен кухни необходима 1 банка краски.

Студент 2:

Задача 2. Через иллюминатор корабля требуется вытащить сундук с драгоценностями. Удастся ли это сделать?

Решение.

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

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

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

Пусть r – радиус иллюминатора,
a, b, c – размеры сундука,
d1, d2, d3 – диагонали боковых поверхностей сундука. (Презентация. Слайд 15)

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

ЕСЛИ((2*R>КОРЕНЬ(a^2+b^2));1;0)
ЕСЛИ((2*R>КОРЕНЬ(a^2+c^2));1;0)
ЕСЛИ((2*R>КОРЕНЬ(с^2+b^2));1;0)

(Презентация. Слайд 16)

Моделирование в среде ЭТ.

Заносим данные задачи в электронную таблицу, вводим формулы.
Электронная таблица в режиме отображения формул. (Приложение 3. Презентация. Слайд 17)
Электронная таблица в режиме отображения значений. (Приложение 4.Презентация. Слайд 18)

Компьютерный эксперимент.

В электронной таблице находим сумму трех условий. Если сумма равна 0, делаем вывод «Сокровища недоступны», иначе «Сокровища доступны» (Слайд 19 Презентация).

Студент 3:

Задача 3. Решить уравнение х4-4х3-10х2+37х-14=0 (Слайд 20 Презентация).

Решение.

Необходимо построить график функции у = х⁴ – 4х³ – 10х² + 37х – 14. Точки пересечения графика с осью Х будут решениями данного уравнения. Составляем в MS Excel таблицу значений функции. (Приложение 5. Презентация. Слайд 21)

Построим график функции (диаграмму). (Приложение 5. Презентация. Слайд 22)

Мы видим, что график четырежды пересекает ось ОХ, значит уравнение х⁴ – 4х³ – 10х² + 37х –14 = 0 имеет четыре корня.

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

х₁ [–3,5; –3], х₂ [0; 0,5], х₃ [2; 2,5], х₄ [4,5; 5].

(Презентация. Слайд 23)

Затем с помощь анализа «что-если»/Подбор параметра можно уточнить значения корней. Для этого следует активизировать ячейку со значением функции у = 55,56, соответствующим значению аргумента х = -3,5, или ячейку со значением у = -26, соответствующим х = -3, и выполнить команду Данные/группа Работа с данными/Анализ «что-если»/Подбор параметра. Появится одноименное диалоговое окно с тремя строками (Слайд 23 Презентация).

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

Аналогично проверяются корни из других промежутков.

Из результирующей таблицы выбираем корни уравнения. (Приложение 5. Презентация. Слайд 24)

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

Поясним это на примере решения уравнения.

Студент 4:

Задача 4. Решить уравнение log₂(x(1 – x)) – sin(π/x) + 2 = 0, область определения которого: x принадлежит промежутку (0;1). (Презентация. Слайд 25)

Решение.

Если построить график соответствующей функции в области ее определения с шагом h = 0,04, то получится один результат (Приложение 6. Презентация. Слайд 27), но если построить тот же график с меньшим шагом h = 0,01, то мы получим иной результат. (Приложение 6. Презентация. Слайд 27) Сравнение этих графиков показывает, что в первом случае из-за слишком большого шага «потеряны» два первых корня. Всего же рассматриваемое уравнение имеет шесть корней, которые уточняются с помощью Подбора параметра. (Презентация. Слайд 28)

Вывод. (Презентация. Слайд 29)

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

Литература:

1. О.К. Мясникова. Моделирование и формализация в курсе информатики. // Информатика и образование, №11-2003.
2. В.П. Кудинов. Решение уравнений с помощью MS Excel. // Информатика и образование, №3-2004.
3. Информатика и информационные технологии. Учебник для 10-11 классов / Н.Д. Угринович. – М.:Бином. Лаборатория знаний, 2003.