CC BY
68
Златополъский Дмитрий Михайлович
ИСПОЛЬЗОВАНИЕ ЭЛЕКТРОННЫХ ТАБЛИЦ ДЛЯ РЕШЕНИЯ УРАВНЕНИЙ
В числе множества задач, решаемых с помощью электронных таблиц, находятся и задачи решения уравнений различных типов. По мнению автора, обучение использованию электронных таблиц для решения уравнений и ряда других задач алгоритмического характера1, является компромиссным направлением в «алгоритмической» и «пользовательской» линиях в изучении информатики.
В статье рассмотрена методика решения трех типов уравнений:
1) линейных - вида ax + b = 0;
2) квадратных - вида ax2+ bx + с = 0;
3) трансцендентных - методом деления отрезка пополам2.
В качестве инструмента для решения применяется программа Microsoft Excel.
1. решение линейных уравнений
вида ax+ b = 0
Значения коэффициентов a и b будем вводить, соответственно, в ячейки B2 и В3, а сообщения, указывающие об этом, запишем в ячейки А2 и A3 (таблица 1). В
ячейке В4 будем выводить информацию о том, имеет ли заданное пользователем уравнение корень или нет, и в случае положительного ответа значение корня укажем в ячейке Э4.
Формулы должны иметь вид: в ячейке В4:
=ЕСЛИ(В2=0; "Нет";"Есть"); в ячейке С4:
=ЕСЛИ(В3><0;"Его значение";"")3 ; а в ячейке Э4:
=ЕСЛИ(В3><0;-В4/В3;""). Вид фрагмента листа для двух возможных случаев приведен в таблицах 2-3.
Очевидно, что при представленном оформлении листа в ячейке В4 сообщение «Нет» будет выводиться даже в случае, когда коэффициент а вообще не задан, что не совсем корректно. Чтобы устранить этот недостаток, можно использо-
'еыеАие мсНейНш еерг^НеНхй...
Таблица 1
A B C D
1 Решение линейных уравнений
2 Введите значение коэффициента а -->
3 Введите значение коэффициента Ь -->
4 Есть ли корень? Его значение
5
1 Под задачами алгоритмического характера подразумеваются задачи, для решения которых необходимо выполнить однозначно определенную последовательность действий.
2 Такой метод называют также методом половинного деления, методом дихотомии или методом бисекции.
3 Учащихся следует ознакомить с оформлением «неполного» варианта функции ЕСЛИ, когда при одном из двух возможных результатов логического выражения значение в ячейке отсутствует.
Таблица 2
А В С Б
1 Решение линейных уравнений
2 Введите значение коэффициента а --> 0
3 Введите значение коэффициента Ь --> 34
4 Есть ли корень? Нет
5
Таблица 3
А В С Б
1 Решение линейных уравнений
2 Введите значение коэффициента а --> 3
3 Введите значение коэффициента Ь --> -4
4 Есть ли корень? Есть Его значение 1,333333
5
вать функцию ЕПУСТО(), возвращающую значение ИСТИНА, если в ячейке, адрес которой указан в качестве аргумента функции в скобках, значение отсутствует.
С использованием указанной функции формулы в ячейках А4, В4, С4 и Э4 будут иметь вид (соответственно):
=ЕСЛИ(ЕПУСТО(В2);""; =ЕСЛИ(ЕПУСТО(В2);"" =ЕСЛИ(ЕПУСТО(В2);"" =ЕСЛИ(ЕПУСТО(В 2);""
2. решение квадратных уравнений вида ах2 + Ьх + с =0
Здесь значения коэффициентов а, Ь и с будем задавать соответственно в ячей-
ках В2, В3 и В4, а сообщения, указывающие об этом, запишем в ячейки А2, А3, А4 (таблица 4). В ячейке В5 будем выводить информацию о том, имеет ли заданное пользователем уравнение корень или
Таблица 4
А В С Б
1 Решение квадратных уравнений
2 Введите значение коэффициента а -->
3 Введите значение коэффициента Ь -->
4 Введите значение коэффициента с -->
5 Есть ли корень? Значение первого корня:
6 Значение второго корня:
7
Есть ли корень? ), ЕСЛИ(В2=0;"Нет";"Есть")), ЕСЛИ(В2><0;"Его значение»;"")),
ЕСЛИ(В2><0'-В3/В2'"")) Феше-Аие. кбл^рлЛ-Яих
урлб-МКий...
нет, и в случае положительного ответа значения корней укажем в ячейках Э5 и Э6.
Очевидно, что для решения квадратного уравнения следует вычислять его дискриминант. Однако значение дискриминанта уравнения является вспомогательным и для пользователя может быть скрыто, например, выведено за пределы страницы, оформлено белым цветом и т.п. В статье рассмотрим вариант размещения значения дискриминанта за пределами страницы в ячейке М3. Формула для его расчета следующая:
=В3*В3-4*В2*В4,
а формулы в ячейках В5, С5, С6, Э5 и Э6, соответственно:
=ЕСЛИ(М3<0;"Нет";"Есть"), =ЕСЛИ(М3>=0;"Значение первого корня:";" =ЕСЛИ(М3>=0;"Значение второго корня:";" =ЕСЛИ(М3>=0;(-В4+КОРЕНЬ(М3))/(2*В2); =ЕСЛИ(М3>=0;(-В4-КОРЕНЬ(М3))/(2*В2);"
Примечание. Предложите ученикам разработать вариант оформления листа, использующий функцию ЕПУСТО() (см. выше).
3. решение уравнений методом деления отрезка пополам
Напомним суть метода [1], который, конечно же, следует предварительно обсудить с учащимися.
Пусть требуется найти корень уравнения /(х)=0, где /(х) - непрерывная на отрезке [а, Ь] функция и /(а) * /(Ь) < 0. Тогда / обязательно имеет корень на указан-
ном отрезке. Возьмем середину отрезка с = (а + Ь)/2 в качестве приближенного значения корня. Настоящий корень отличается от с не более чем на половину длины отрезка, то есть не более чем на (Ь - а)/2. Если такая точность нас не устраивает, то можно от отрезка [а, Ь] перейти к одной из его половин: либо к [а, с], либо к [с, Ь], а именно к той из них, где находится искомый корень:
").
если /(а) * /(с) < 0, то корень на отрезке [а, с];
если /(а) * /(с) > 0, то корень на отрезке [с, Ь].
Так как длина нового отрезка вдвое меньше старого, то, взяв в качестве приближенного значения корня середину нового отрезка, мы получим корень с точностью (Ь - а)/4. Если и эта точность нас не устраивает, можно поделить пополам новый отрезок и т. д.
Таким образом, мы можем делить отрезок пополам и переходить к одной из его половин, пока длина отрезка не станет достаточно малой, а потом в качестве корня взять середину отрезка.
Использование метода деления отрезка пополам для нахождения корня уравнения с помощью электронных таблиц проиллюстрируем на следующем примере: вычислить корень уравнения со^(х) = х на отрезке [0, 2] с точностью 0,001.
Исходные значения границ отрезка а и Ь запишем в ячейки А4 и В4 (таблица 5). В ячейке С4 получим середину заданного отрезка с, а в ячейках Э4 и Е4 -значения функции /(х) на концах отрезка
Таблица 5
А В С Б Е Б О Н I 1
1 Решение уравнения методом деления отрезка пополам
2
3 а Ь с ^а) ^с) Длина Значение корня Точность 0,001
4 0 2 1 1 -0,4597 2
5
(очевидно, что /(х) = соя(х) - х). В ячейке Б4 будем определять длину отрезка [а, Ь]. Формулы в ячейках С4, Б4, Е4 и Б4 имеют вид, соответственно:
=(А4+В4)/2, =С08(А4)-Л4, =С08(С4)-С4, =В4-А4.
Необходимую точность вычислений укажем, например, в ячейке 13. После этого мы можем провести проверку длины заданного отрезка на соответствие принятой точности. Для поиска корня уравнения используем колонку в. В ячейке 04 запишем формулу:
=ЕСЛИ(Р4/2<13 ;С4;"")
(если длина отрезка соответствует требуемой точности, то в качестве корня принимаем середину этого отрезка).
В строке 5 запишем значения, полученные после первого шага деления исходного отрезка пополам. В соответствии с рассуждениями о возможных переходах от «старого» отрезка к одной из
его половин, сделанных чуть выше, можем записать следующие формулы: в ячейку А5:
=ЕСЛИ(Б4*Е4<0;А4;С4), в ячейку В5:
=ЕСЛИ(Б4*Е4>0;В4;С4).
В ячейки С5, Б5, Е5, Б5 и 05 формулы можно скопировать из ячеек С4, Б4, Е4, Б4 и 044, соответственно.
После этого наш лист примет вид, как в таблице 6.
Таким образом, искомый корень уравнения находится на отрезке [0,1].
Скопировав все формулы из ячеек строки 5 в строку 6, мы проведем еще одно деление отрезка пополам (таблица 7).
Возникает вопрос: а сколько раз надо копировать строки с формулами, для того чтобы получить решение уравнения?
Здесь можно поступить по-разному. Можно копировать формулы в одну (очередную) строку до тех пор, пока в столбце в не появится искомое значение корня. А можно число шагов определить за-
Таблица 6
А В С Б Е Б О Н I 1
1 Решение уравнения методом деления отрезка пополам
2
3 а Ь с ад ад Длина Значение корня Точность 0,001
4 0 2 1 1 -0,4597 2
5 0 1 0,5 1 0,3776 1
6
4 Для того чтобы при копировании формулы из ячейки 04 имеющийся в ней адрес ячейки 13 не менялся, следует использовать абсолютную или смешанную ссылку на него, то есть формула в ячейке 04 должна быть уточнена: =ЕСЛИ(Б4/2<Г$3;С4;"")
Таблица 7
A B C D E F G H I J
1 Решение уравнения методом деления отрезка пополам
2
3 а b c f(a) fW Длина Значение корня Точность 0,001
4 0 2 1 1 -0,4597 2
5 0 1 0,5 1 0,3776 1
6 0,5 1 0,75 0,377583 -0,0183 0,5
7
ранее и скопировать формулы в диапазон из необходимого числа строк,
Число шагов до нахождения корня определяется по формуле:
Llog2((b-a)/(2*t))J +1, где LxJ есть целая часть числа x, t - заданная точность вычислений.
С учащимися, знакомыми с логарифмами, указанную формулу полезно получить, а остальным ученикам ее можно предъявить в качестве готовой.
Число шагов по этой формуле можно рассчитать с помощью Microsoft Excel,
"Чисмх млгаб- ftx ftaxo^fetetfr tcofeftfr...
использовав функцию LOG, позволяющую определить логарифм по любому основанию, а также функцию ОТБР, возвращающую число без его дробной части. Записав в какой-либо ячейке формулу: =4EnOE(LOG((B4-A4)/(2*J3);2))+1,
Таблица 8
A B C D E F G H I J
1 Решение уравнения методом деления отрезка пополам
2
3 а b c f(a) f^) Длина Значение корня Точность 0,001
4 0 2 1 1 -0,4597 2
5 0 1 0,5 1 0,3776 1
6 0,5 1 0,75 0,3776 -0,0183 0,5
7 0,5 0,75 0,625 0,3776 0,1860 0,25
8 0,625 0,75 0,6875 0,1860 0,0853 0,125
9 0,6875 0,75 0,7188 0,0853 0,0339 0,0625
10 0,7187 0,75 0,7344 0,0339 0,0079 0,0313
11 0,7344 0,75 0,7422 0,0079 -0,0052 0,0156
12 0,7344 0,7422 0,7383 0,0079 0,0013 0,0078
13 0,7383 0,7422 0,7402 0,0013 -0,0019 0,0039
14 0,7383 0,7402 0,7393 0,0013 -0,0003 0,0019 0,7393
15
получим число шагов, необходимых для нахождения корня заданного уравнения, равное 10.
Дважды мы уже формулы копировали (в строки 5 и 6). Поэтому скопиру-
ем формулы из строки 4 еще 8 раз. В результате получим картину, как в таблице 8.
То есть корень заданного уравнения равен 0,7393.
Литература.
1. Абрамов С.А. Гнездилова Г.Г. Капустина Г.Г. Селюн М.И. Задачи по программированию. М.: Наука, 1988.
2. Задачи по программированию. 7-11 класс / Златопольский Д.М. М.: Изд-во «Первое сентября», 2001.
© Наши авторы: 2002. Our authors, 2002,
Златополъский Дмитрий Михайлович, доцент Московского городского университета,
учителъ гимназии № 1530 г. Москвы.
