Решение уранений в среде MS EXCEL

Одна из наиболее актуальных проблем компьютерного обучения – проблема отбора и использования педагогически целесообразных обучающих программ.

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

Нахождение корней уравнения с помощью подбора параметра

Пример 1.
Пусть известно, что в штате больницы состоит 6 санитарок, 8 медсестер, 10 врачей, 3 заведующих отделениями, главный врач, заведующий аптекой, заведующая хозяйством и заведующий больницей. Общий месячный фонд зарплаты составляет 1000 000 условных единиц. Необходимо определить, какими должны быть оклады сотрудников больницы.
Решение такой задачи можно искать методом перебора. Однако в лучшем случае на это уходит много времени. Можно предложить другой способ решения. В EXCEL он реализован как поиск значения параметра формулы, удовлетворяющего ее конкретному значению.

Построим модель решения этой задачи. За основу возьмем оклад санитарки, а остальные оклады будем вычислять, исходя из него: во столько-то раз или на столько-то больше. Говоря математическим языком, каждый оклад является линейной функцией от оклада санитарки: Ai*С+Вi, где С - оклад санитарки; Аi и Вi - коэффициенты, которые для каждой должности определяют следующим образом:

• медсестра получает в 1,5 раза больше санитарки (А2=1,5; В2=0);
• врач - в 3 раза больше санитарки (А3=3; В3=0);
• заведующий отделением - на 30 y.e. больше, чем врач (А4=3; B4=30);
• заведующий аптекой - в 2 раза больше санитарки (А5=2; В5=0);
• заведующий хозяйством - на 40 y.e. больше медсестры (А6=1,5; В6=40);
• заведующий больницей - на 20 y.e. больше главного врача (А8=4; В8=20);
• главный врач - в 4 раза больше санитарки (А7=4; В7=0);

Зная количество человек на каждой должности, нашу модель можно
записать как уравнение: N1*(A1*C+B1)+N2*(A2*C+B2)+...+N8*(A8*C+B8) = 1000000,
где N1 - число санитарок, N2 - число медсестер и т.д.
В этом уравнении нам известны A1...A8, B1...B8 и N1...N8, а С неизвестно. Анализ уравнения показывает, что задача вычисления заработной платы свелась к решению линейного уравнения относительно С. Предположим, что зарплата у санитарки 150,00 y.e.
Введите исходные данные в рабочий лист электронной таблицы, как показано ниже.
A B C D E F
Оклад мед. Работников
Должность Коэф. A Коэф. B Зарплата Количество
сотрудников Суммарная
зарплата
Санитарка 1 0,00 150,00 6
Медсестра 1,5 0,00 8
Врач 3 0,00 10
Зав. отделением 3 30,00 3
Зав. аптекой 2 0,00 1
Завхоз 1,5 40,00 1
Главврач 4 0,00 1
Зав. больницей 4 20,00 1
Общий фонд равен

В столбце D вычислите заработную плату для каждой должности. Например, для ячейки D4 формула расчета имеет вид =B4*$D$3+C4.
В столбце F вычислите заработную плату всех работников данной должности. Например, для ячейки F3 формула расчета имеет вид =D3*E3.
В ячейке F11вычислите суммарный фонд заработной платы больницы. Рабочий лист электронной таблицы будет выглядеть, как показано ниже.
A B C D E F
Оклад мед. Работников
Должность Коэф. A Коэф. B Зарплата Количество
сотрудников Суммарная зарплата
Санитарка 1 0,00 150,00 6 900,00
Медсестра 1,5 0,00 225,00 8 1800,00
Врач 3 0,00 450,00 10 4500,00
Зав. отделением 3 30,00 480,00 3 1440,00
Зав. аптекой 2 0,00 300,00 1 300,00
Завхоз 1,5 40,00 265,00 1 265,00
Главврач 4 0,00 600,00 1 600,00
Зав. больницей 4 20,00 620,00 1 620,00
Общий фонд равен 10425,00

Чтобы определите оклад санитарки так, чтобы расчетный фонд был равен заданному надо:

1. Активизировать команду Подбор параметра во вкладке Данные / Работа с данными /Анализ «Что, если»;
2. В поле "Установить в ячейке" появившегося окна ввести ссылку на ячейку F11, содержащую формулу;
3. В поле "Значение" набрать искомый результат 1000000;
4. В поле "Изменяя значение ячейки" ввести ссылку на изменяемую ячейку D3 и щелкните на кнопке ОК.

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

Приложение 1

Задание для учащихся:
Составить несколько вариантов штатного расписания с использованием функции Подбор параметра и оформить их в виде таблицы:

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

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

Пусть дано уравнение, записанное в виде x=F(x). Выбирают некоторое начальное приближение x1 и подставляют его вместо x в F(x). Полученное значение x2=F(x1) этой функции считают вторым приближением. Далее находят третье приближение по формуле x3=F(x2) и так далее. Таким образом, получаем последовательность x1, x2, x3,…, xn,… чисел, имеющая предел α. Тогда если функция F(x) непрерывна, из равенства x n+1=F(xn) получаем α=F(α). Это означает, что α является решением уравнения x=F(x).
Пример 2.
Пусть нам дан многочлен третьей степени:
x³-0,01x²-0,7044x+0,139104=0.

Так как мы ищем корни полинома третьей степени, то имеются не более трех вещественных корней. Для нахождения корней их первоначально надо локализовать, то есть найти интервалы, на которых они существуют. Такими интервалами локализации корней могут служить промежутки, на концах которых функция имеет противоположный знак. С целью нахождения интервалов, на концах которых функция изменяет знак, необходимо построить ее график или протабулировать ее. Составим таблицу значений функции на интервале [-1;1] с шагом 0,2. Для этого необходимо:

1. Ввести в ячейку A2 значение -1, а в ячейку A3 значение -0,8.
2. Выбрать диапазон A2:A3, расположить указатель мыши на маркере заполнения этого диапазона и протянуть его на диапазон A4:A12, аргумент протабулирован.
3. В ячейку B2 ввести формулу:=A2^3-0,01*A2^2-0,7044*A2+0,139104
4. Выбрать ячейку B2. Расположить указатель мыши на маркере заполнения этой ячейки и протянуть его на диапазон B3:B12. Функция также протабулирована.

Из таблицы видно, что полином меняет знак на интервалах [-1; -0,8], [0,2; 0,4] и [0,6; 0,8], и поэтому на каждом из этих интервалов имеется свой корень. Так как полином третьей степени имеет не более трех корней, то они все локализованы.
Прежде чем приступить к нахождению корней при помощи подбора параметра, необходимо выполнить некоторую подготовительную работу:

• Установить точность, с которой находится корень. Корень при помощи подбора параметра находится методом последовательных приближений. Для этого в Настройке панели быстрого доступа / Другие команды, и на вкладке Формулы диалогового окна Параметры Exel задайте в Параметрах вычислений относительную погрешность и предельное число итераций равными 0,00001 и 1000, соответственно.

• Отвести на рабочем листе ячейку, например С2, под искомый корень. Эта ячейка будет играть двойную роль. До применения подбора параметра в ней находится начальное приближение к корню уравнения, а после применения – найденное приближенное значение корня.

• Корень при помощи подбора параметра находим методом последовательных приближений. Поэтому в ячейку C2 надо ввести значение, являющееся приближением к искомому корню. В нашем случае, первым отрезком локализации корня является [-1;-0,8]. Следовательно, за начальное приближение к корню разумно взять среднюю точку этого отрезка -0,9.

• Отвести ячейку, например D2, под функцию, для которой ведется поиск корня, причем вместо неизвестной у этой функции должна указываться ссылку на ячейку, отведенную под искомый корень. Таким образом, в ячейку D2 введите формулу:=C2^3-0,01*C2^2-0,7044*C2+0,139104

Аналогично надо поступить с двумя другими искомыми корнями:

• Отвести ячейку C8 под второй корень, ввести в нее начальное приближение 0,3, а в ячейку D8 ввести следующую формулу:
=C8^3-0,01*C8^2-0,7044*C8+0,139104

• Отвести ячейку C10 под второй корень, ввести в нее начальное приближение 0,7, а в ячейку D10 ввести следующую формулу:
=C10^3-0,01*C10^2-0,7044*C10+0,139104

Результаты выполненных действий приведены в таблице.
Значение х Значение у Начальное приближение до применения метода Значение функции
-1,00 -0,1665 -0,9 0,0360
-0,8 0,1842
-0,60 0,3421
-0,4 0,3553
-0,20 0,2716
0 0,1391
0,20 0,0058 0,3 -0,0461
0,4 -0,0803
0,60 -0,0711 0,7 -0,0159
0,8 0,0812
1,00 0,4247

Теперь можно переходить к нахождению первого корня уравнения:
Выберете команду Подбор параметра. На экране отобразится диалоговое окно Подбор параметра.
1. В поле Установить в ячейке введите ссылку на ячейку D2. В этом поле дается ссылка на ячейку, в которой введена формула, вычисляющая значение левой части уравнения. Для нахождения корня с помощью подбора параметра уравнение надо представить в таком виде, чтобы его правая часть не содержала переменную.
2. В поле Значение введите 0. Здесь указывается значение из правой части уравнения.
3. В поле Изменяя значение ячейки введите C2. В данном поле приводится ссылка на ячейку, отведенную под переменную.
4. Нажмите кнопку OK.
На экране отображается окно Результат подбора параметра с результатами работы команды Подбор параметра. Кроме того, рассматриваемое средство помещает найденное приближенное значение корня в ячейку C2. В данном случае оно равно -0,920. Аналогично в ячейках C8 и C10 находятся два оставшихся корня. Они равны 0,210 и 0,721.

Значение х Значение у Корень уравнения Значение функции
-1,00 -0,1665 -0,920 0,00
-0,8 0,1842
-0,60 0,3421
-0,4 0,3553
-0,20 0,2716
0 0,1391
0,20 0,0058 0,210 0,00
0,4 -0,0803
0,60 -0,0711 0,721 0,00
0,8 0,0812
1,00 0,4247
Приложение 2
Задание для учащихся:

Найти все корни уравнений
1. Х3-2,92Х2+1,4355Х+0,791136=0
2. Х3-2,56Х2-1,3251Х+4,395006=0
3. Х3+2,84Х2-5,6064Х-14,766336=0

Нахождение корней уравнения методом деления отрезка пополам
Краткая теория метода. Пусть непрерывная функция F(x) имеет значения разных знаков на концах отрезка [a;b], то есть F(a)F(b)<0.Тогда уравнение F(x)=0 имеет корень внутри этого отрезка. Отрезок [a;b] отрезком локализации корня. Пусть c=(a+b)/2 - середина отрезка [a;b]. Если F(a)F©<=0, то корень находится на отрезке [a;c], который берем за новый отрезок локализации корня. Если F(a)F©>0, то за новый отрезок локализации корня берем [c;b].Отметим, что новый отрезок локализации корня в два раза меньше первоначального. Процесс деления отрезка для локализации корня продолжаем до тех пор, пока его длина не станет меньше ε, точности нахождения корня. В этом случае любая точка отрезка локализации отличается от корня не более чем на ε/2.
Найдем корни уравнения x²–2=0 с точностью до 0,001 методом деления отрезка пополам. За первоначальный отрезок локализации корня выбран [0;2]. Для реализации этого метода введите в ячейки рабочего листа формулы либо значения, приведенные ниже в таблице:
Ячейка Формула или значение
B1 0,001
A3 0
B3 2
C3 =(A3+B3)/2
D3 =(A3^2-2)*(C3^2-2)
E3 =C3^2-2
F3 =ЕСЛИ(B3–A3<$B$1;''Корень найден и равен '' & текст (C3;''0,000''); '' '')
A4 =ЕСЛИ (D3<=0; A3;C3)
B4 = ЕСЛИ(D3<=0; C3; B3)
C4 =(A4+B4)/2
D4 =(A4^2-2)*(C4^2-2)
E4 C4^2-2
F4 =ЕСЛИ(B4-A4<$B$1; ''Корень найден и равен '' & текст(C4; ''0,000''); '' '')

Теперь осталось только выбрать диапазон A4:F4, расположить указатель мыши на маркере его заполнения и пробуксировать его вниз до тех пор, пока в столбце F не появится сообщение о том, что корень найден. В данном случае сообщение появится в ячейке F14, а значение корня с точностью до 0,001 равно 1,415.
Число шагов можно определить заранее и скопировать формулы в диапазон из необходимого числа строк. Число шагов до нахождения корня определяется по формуле: [log2((b-a)/(2*t))]+1 (1), где [x] есть целая часть числа х, t - заданная точность.

В заключение отмечу, что в рассмотренном примере использовались:
• Операция конкатенации строк, которая объединяет несколько строк в одну (обозначается символом амперсанта &). При объединении двух строк вторая строка добавляется непосредственно в конец первой строки.
• Функция рабочего листа из категории функций по работе с текстом ТЕКСТ (TEXT). Данная функция преобразует значение в текст в заданном числовом формате.
Приложение 3
Задание для учащихся:
Вычислить корень уравнения Cosx=x на отрезке [0;2] с точностью до 0,001. Число шагов для определения корня вычислить при помощи формулы (1).

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

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