информатика экзамен (только 6ой вариант) / Excel-Podbor_parametra_reshenie_uravnenia
.doc
Присвоение имени ячейкам
Для удобства работы в формулах можно указывать не адреса ячеек, а конкретные обозначения. Например, в формулах часто используется значение, которое находится в ячейке B2, а при обычной математической записи это значение фигурирует как X. Присвоим ему в Excel то же имя. Для этого нужно установить курсор в ячейку B2, затем выполнить команду Вставка→Имя→Присвоить→X (или можно просто ввести имя X в поле адреса ячейки B2). Тогда при вводе формулы вместо ссылки на ячейку B2 можно использовать имя X. Такие ссылки особенно удобны при записи арифметических выражений. Например, формула =B2*3-sin(B2) будет идентична формуле =X*3-sin(X).
Разным ячейкам одного рабочего листа не может быть присвоено одно и то же имя. При повторном назначении это имя будет соответствовать ячейке, обозначенной последней. Кроме того, нельзя задавать имена, совпадающие с адресами ячеек, например, C1, Х1 и т. п. Имя «с» в Excel недопустимо, так как оно зарезервировано как служебное.
Подбор параметра
Excel позволяет легко решить уравнение с одним неизвестным. Поиск решения осуществляется методом итераций (от лат. – последовательное приближение). Сначала в изменяемой ячейке задается начальное (приближенное) значение искомого корня уравнения, а в целевую ячейку (т. е. ячейку результата) вводится решаемое уравнение. Затем необходимые данные указываются в п. Сервис→Подбор параметра. Если начальное значение параметра не дает требуемого результата в целевой ячейке, то Excel автоматически делает следующий шаг итерационного процесса и находит новое значение параметра в изменяемой ячейке. Этот процесс продолжается до тех пор, пока не будет найдено нужное значение (при условии, что решение задачи существует).
Если при решении уравнения начальное приближение корня неизвестно, то можно построить таблицу значений функции или график и определить, на каком интервале изменения аргумента функция меняет знак.
Рассмотрим пример решения трансцендентного (т.е. содержащего неалгебраические функции) уравнения x3=ex .
Для удобства преобразуем это уравнение к виду x3 – ex = 0.
Для определения начального приближения корня протабулируем функцию и построим ее график, например, на отрезке [0;3] (рис. 1).
Из таблицы значений функции и графика следует, что на отрезке xЄ[1,5; 2] функция меняет знак, т.е. на этом отрезке находится корень функции. Для уточнения его значения выберем в качестве начального приближения корня любую границу этого отрезка, например, 1,5. Введем это значение в ячейку A14. Для удобства присвоим ей имя x. В ячейку B14 введем формулу – левую часть решаемого уравнения, т. е. =x^3-EXP(x). Затем, находясь в этой ячейке, выполним команду Сервис→Подбор параметра и в диалоговом окне зададим необходимые параметры (см. рис. 1). При этом в окошке «Значение» вводится правая часть решаемого уравнения (в нашем случае 0).
Рис. 1. Вычисление корня уравнения
Установки в окне «Подбор параметра» на рис. 1 поручают Excel перебирать значения в ячейке x (A14) до тех пор, пока зависящее от x выражение, записанное в ячейке B14, не достигнет заданного значения 0.
По окончании процесса поиска получим значение корня x=1,85694 (рис. 2).
Рис. 2. Окно результата подбора параметра
По умолчанию команда Подбор параметра прекращает вычисления после выполнения 100 итераций или при получении результата, который находится в пределах 0,001 от заданного целевого значения (см. рис. 2). При необходимости изменить эти установки можно в меню Сервис→Параметры. Для этого на вкладке Вычисления надо изменить значения полей Предельное число итераций и/или Относительная погрешность. Если задача имеет несколько решений, то будет найдено одно из них.
Если найденное решение не устраивает пользователя, можно процесс поиска повторить, задав новое начальное приближение, точность вычисления и предельное число итераций.