Хорошим упражнением по работе с MS Excel является программирование на рабочем листе алгоритма нахождения корня уравнения F(x) = 0 методом деления отрезка пополам. Пусть непрерывная функция F(x) имеет значения разных знаков на концах отрезка [a; b], т. е. F(a)*F(b)=0.
Тогда уравнение F(x)=0 имеет корень внутри этого отрезка. Отрезок [a; b] называется отрезком локализации корня. Пусть c = (а + b) / 2 - середина отрезка [a; b]. Если F(а)*F(с)<=0, то корень находится на отрезке [a; с], который берем за новый отрезок локализации корня. Если F(а)*F(с)=0, то за отрезок локализации корня берем [c;b]. Отметим, что новый отрезок локализации корня в два раза меньше первоначального. Процесс деления локализации корня продолжаем до тех пор, пока его длина не станет меньше, точности нахождения корня. В этом случае любая точка локализации отличается от корня не более чем на ε/2.
На рисунке приведены результаты нахождения корня с точностью до 0.001 методом деления отрезка пополам уравнения x2 —2 = 0. За первоначальный отрезок локализации корня выбран [0; 2].
|
|
Для реализации этого метода введите в ячейки рабочего лист формулы либо значения (табл. 1).
Таблица 1. Формулы для нахождения корней уравнения
Ячейка | Формула либо значение |
B1 | 0,001 |
A3 | |
B3 | |
C3 | =(A3+B3)/2 |
D3 | =(A3^2-2)*(C3^2-2) |
E3 | =C3^2-2 |
F3 | =ЕСЛИ(B3-A3<$B$1;"Корень найден и равен"&ТЕКСТ(C3;0,0000”);"") |
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,0000");"") |
Примечание. На самом деле в диапазон C4:E4 не надо вводить формулы с клавиатуры. Выберите диапазон C3: F3, расположите указатель мыши на маркере заполнения, и протяните его на одну строку вниз.
Рис. 3 Нахождение корня уравнения методом деления отрезка пополам
Теперь осталось только выбрать диапазон A4:F4, расположить указатель мыши на маркере его заполнения и протянуть указатель вниз до тех пор, пока в столбце F не появится сообщение о том, что корень найден. В данном случае сообщение появится в ячейке F14, а значение корня с точностью до 0.001 равно 1.414.
В заключение отметим, что в рассмотренном примере использовались:
- Операция конкатенации строк, которая объединяет несколько строк в одну. Операция конкатенация обозначается символом амперсанда &. При объединении двух строк вторая строка добавляется непосредственно в конец первой. Результатом является строка большего размера, содержащая целиком обе исходные строки. Например, “Корень “ & “уравнения” равно одной строке “ Корень уравнения ”. Обратите внимание, что пробел в конце первой строки используется для того, чтобы в результирующей строке отделить первое слово от второго. Вместо символа конкатенации можно также использовать функцию СЦЕПИТЬ(CONCATENATE). Тот же самый результат дает формула =СЦЕПИТЬ(“Корень “;“уравнения”). Если значение Корень введено в ячейку A1, а значение уравнения — в ячейку B1 то тот результат получается формулой = СЦЕПИТЬ (A1;” “;B1) или A1&“ “&B1:
- Функция рабочего листа из категории функций по работе с текстом ТЕКСТ (ТЕХТ). Данная функция преобразует значение в текст в заданном числовом формате.
Синтаксис функции текст:
|
|
текст (значение; формат)
- значение — либо числовое значение, либо формула, вычисление которой дает числовое значение, либо ссылка на ячейку, содержащую числовое значение;
- формат — числовой формат с вкладки Число диалогового окна Формат ячеек, отображаемого на экране выбором команды Формат - Ячейки. Формат не может содержать звездочку * и не может принадлежать к категории Общий.
Приведем три примера работы функции ТЕКСТ:
- =ТЕКСТ(2,7153;"0,00р.") возвращает 2.72 р.
- =ТЕКСТ(2,7153;"0,000") возвращает 2.715
- =ТЕКСТ("17.5.03";"Д МММ ГГГГ") возвращает 17 май 2003