Построение графиков функции и решение нелинейных уравнений в Excel

1 ПОСТРОЕНИЕ ГРАФИКОВ

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

o построение графиков;

o решение уравнений с одной неизвестной.

Для построения графиков в MS Excel имеется прекрасное средство - мастер диаграмм (Chart Wizard), который предлагает пользователю большой набор типов графиков и диаграмм, позволяющих наглядно представить данные, в наиболее выгодном свете.

Даже удивительно, как часто мы сталкиваемся с необходимостью решить то или иное уравнение, например, определить процентную ставку, при которой предлагаемая сделка выгодна, или вычислить скорость оборота капиталовложений. Подбор параметра (Goal Seek) как раз и является тем средством MS Excel, которое позволяет элементарно просто решать подобные задачи.

1.1 Построение графика функции

Рассмотрим технологию построения графика на примере функции

у = cos² (nx) x[0; 1].

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

Таблица значений функции. Для построения графика функции необходимо первоначально построить таблицу ее значений при различных значениях аргумента, причем аргумент изменяют обычно с фиксированным шагом. Шаг выбирают небольшим, так чтобы таблица значений функции отражала ее поведение на интервале табуляции. В нашем случае возьмем в качестве шага изменения аргумента, например, 0.1. Нам надо найти у(0), у(0.1), у(0.2), ..., у(1). С этой целью в ячейки диапазона A1:A1l последовательно введем 0, 0.1 ... 1, т. е. значения переменной x. Отметим, что эта последовательность значений представляет собой арифметическую прогрессию. Ввести в ячейки диапазона ряд последовательных значений, образующих арифметическую профессию, можно двумя способами.

Первый способ заключается в следующем:

1. В ячейки A1 и А2 введите первый и второй члены арифметической профессии.

2. Выделите диапазон ячеек А1:A2.

3. Расположите указатель мыши на маркере заполнения выделенного диапазона (рис. 2.1) и протяните его вниз (в данном случае на диапазон A3:A11) до тех пор, пока не получится числовой ряд нужной длины

Рисунок 2.1

Второй способ позволяет пользоваться диалоговым окном Прогрессия. Для этого:

1. В ячейку A1 введите первый член арифметической профессии.

2. Выберите команду Правка >Заполнить >Прогрессия.

3. В появившемся диалоговом окне Прогрессия в группе Расположение выбираем переключатель по столбцам, а в группе Тип - переключатель арифметическая. В поле Шаг введите значение 0,1, а в поле Предельное значение - 1.

4. Нажмите кнопку ОК.

Диалоговое окно Прогрессия закроется, а на рабочем листе автоматически будет построена требуемая прогрессия.

Рисунок Диалоговое окно Прогрессия также позволяет создавать геометрические прогрессии. На рисунке приведен пример построения арифметической и геометрической прогрессии по столбцам, начальное значение обеих прогрессий равно 1, шаг- 1.2, а предельное значение - 4.

Вернемся к рассматриваемому примеру построения графика. В ячейку B1 введите формулу:

=COS(ПИ()*A1)^2

Ввод формул в ячейку можно производить либо с клавиатуры, либо с помощью диалогового окна Мастер функций, которое отображается на экране либо выбором команды Вставка Функция, либо нажатием кнопки Вставка функции панели инструментов Стандартная. Мастер функций содержит СПИСОК всех функций рабочего листа, справки по их синтаксису и примеры применения.

Продемонстрируем работу с мастером функций на примере ввода упомянутой выше формулы.

1. Выберите ячейку B1.

2. Нажмите кнопку Вставка функции панели инструментов Стандартная, либо выберите команду Вставка> Функция.

На экране отобразится диалоговое окно Мастер функций - шаг 1 из 2 , которое состоит из двух частей:

o левая - Категория со списком категорий функций;

o правая - Функция со списком имен функций, входящих в выбранную категорию.

Категория Полный алфавитный перечень содержит все встроенные функций и их имена, упорядоченные в алфавитном порядке, категория 10 недавно использовавшихся содержит имена десяти последних примененных функций. Эта категория ускоряет вызов функций, постоянно используемых пользователем.

3. Функция COS относится к категории Математические. Выберите эту функцию и нажмите кнопку ОК.

На экране отобразится панель формул. В поле Число вводится аргумент функции - в рассматриваемом примере это ПИ()*A1.

С помощью клавиатуры в это поле введите только ПИ() а ссылку на ячейку A1 в формулу добавьте, щелкнув по ячейке A1на рабочем листе. После нажатия кнопки ОК в ячейку B1 будет введена формула:

=COS(ПИ()*A1)

4. С помощью клавиатуры добавьте в формуле =COS(ПИ()*A1) операцию возведения в квадрат функции COS. После всех описанных действий в ячейкеB1 должна появиться формула:

=COS(ПИ()*A1)^2

Таким образом, пока найдено значение функции COS²(x) для значения x из ячейки A1. Теперь нам осталось найти значения этой функции для диапазонов ячеек A2 :А11. Для этого:

1. Выберите ячейку B1.

2. Расположите указатель мыши на маркере заполнения выделенной ячейки и протяните его вниз на диапазон B2: B11.

Процесс создания таблицы значений функции завершен.

Рисунок

На левом рабочем листе рисунка приведены формулы, введенные в ячейки рабочего листа. Для того чтобы в ячейках рабочего листа отображались не значения, а формулы, надо выбрать команду Сервис> Параметры и на вкладке Вид появившегося диалогового окна Параметры в группе Параметры окна установить флажок формулы.

На среднем рабочем листе приведен результат табуляции функции. На правом рабочем листе значения аргумента выводятся в числовом формате с точностью до одного знака после десятичной точки, а значения функции - с точностью до трех знаков после десятичной точки. Отформатируем, например, значения аргумента. Для этого надо выбрать диапазон A1:А11. Выберите команду Формат - Ячейки. В появившемся диалоговом окне Формат ячеек на вкладке Число в списке Числовые форматы выберите Числовой, а в поле Число десятичных знаков введите 1. Нажмите кнопку ОК. Данные в A1:А11 будут отформатированы, как показано на правом рабочем листе. Аналогичным образом отформатировать ячейки диапазона B1:В11.

1.2 Построение графика

Мы создали таблицу значений функции COS²(x) с отформатированными данными. Перейдем теперь к конструированию графика этой функции по существующей таблице значений аргументов и соответствующих значений функции. Для этого:

1. Выберите команду Вставка >Диаграмма.

2. В появившемся диалоговом окне Мастер диаграмм на вкладке Стандартные в списке Тип выберите вариант График, а в списке Вид укажите стандартный график. Нажмите кнопку Далее.

3. В появившемся диалоговом окне Мастер диаграмм (шаг 2 из 4) на вкладке Диапазон данных выберите переключатель Ряды в столбцах, т. к. данные располагаются в столбцах. В поле ввода Диапазон приведите ссылку на диапазон ячеек B1:B11, значения из которых откладываются по оси ординат. Во избежание ошибок, ссылку на этот диапазон в поле ввода диапазон вводите путем его выбора на рабочем листе. Например, выделите ячейку B1, а затем при нажатой клавише <Shift> выберите ячейку B11. В результате в поле диапазон автоматически будет введена ссылка на этот диапазон в абсолютном формате. В данном случае, =Лист1 $B$1: $B$11. Обратите внимание на полученный график функции, отображаемый на вкладке Диапазон данных. В нем по оси ординат откладываются значения функции, а вот по оси абсцисс порядковые номера точек, а не значения аргумента. Для того чтобы по оси абсцисс откладывались значения аргумента, надо воспользоваться вкладкой Ряд.

4. На вкладке Ряд в поле ввода Подписи оси X приведите ссылку на диапазон ячеек A1:A11, значения из которых откладываются по оси. В результате в поле Подписи оси Х автоматически будет сформирована ссылка на этот диапазон в абсолютном формате; В данном случае, =Лист1 $А$1: $А$11. Если в этом поле ввода не указывать ссылку на диапазон, то данные, откладываемые по оси ординат, нумеруются в естественном порядке, начиная с 1, а вдоль оси абсцисс будут размещаться их порядковые номера.

В списке Ряд приводятся ряды данных, откладываемых по оси ординат (в нашем случае имеется только один ряд данных). Эти ряды автоматически определяются на основе ссылки, указанной в поле ввода диапазон предыдущего шага алгоритма. В поле Значения автоматически выводится ссылка на диапазон, соответствующий выбранному ряду из списка Ряд. В поле ввода Имя отображается ссылка на ячейку, в которой содержится заголовок соответствующего ряда.

Этот заголовок в дальнейшем используется мастером диаграмм для создания легенды. Легенда в диаграмме требуется для того, чтобы различать несколько рядов данных откладываемых по оси ординат. В нашем случае имеется только один ряд данных, поэтому легенда нам не потребуется. Следовательно, в поле ввода Имя вводить ничего не надо. Нажмите кнопку Далее.

5. В появившемся диалоговом окне Мастер диаграмм (шаг З из 4): параметры диаграммы на вкладке Заголовки в поле Название диаграммы введите График, в поле Ось Х (категорий) введите x, в поле Ось Y (значений) введите y. На вкладке Легенда снимите флажок Добавить легенду. Нажмите кнопку Далее.

6. В появившемся диалоговом окне Мастер диаграмм (шаг 4 из 4): размещение диаграммы выберите переключатель Поместить диаграмму на листе имеющемся. Диаграмма будет внедрена в рабочий лист, имя которого указывается в соответствующем списке. Если выбрать переключатель Поместить диаграмму на листе отдельном, то диаграмма появится на листе диаграмм. Нажмите кнопку Готово.

Итак, диаграмма построена. Теперь, используя маркеры изменения размеров, можно поменять ее размер, а также разместить диаграмму в нужном месте рабочего листа. Кроме того, допустимо редактирование и исправление внешнего облика любого элемента диаграммы. Для этого достаточно его выделить, нажать на правую кнопку мыши и из появившегося контекстного меню выбрать команду редактирования этого элемента.

1.3 Математические функции рабочего листа

Наиболее часто употребляемые стандартные математические функции рабочего листа приведены в табл. 2.1.

Таблица 2.1 - Математические функции

Функция (рус.)	Функция (англ.)	Описание
ABS(число)	ABS(number)	Возвращает абсолютную величину аргумента
AСОS (число)	AСОS (number )	Возвращает арккосинус аргумента
ASIN(число)	ASIN (number )	Возвращает арксинус аргумента
ATAN (число)	ATAN(number )	Возвращает арктангенс аргумента
COS (число)	COS(number )	Возвращает косинус аргумента
ЕХР (число)	ЕХР(number)	Возвращает экспоненту аргумента, т. е. результат возведения основания натурального логарифма в степень, равную значению аргумента
LN(число)	LN(number)	Возвращает натуральный логарифм аргумента
LOG (число, основание)	LOG(number, base)	Возвращает логарифм аргумента по данному основанию, если основание опущено, то оно полагается равным 10
LOG10(число)	LOG10(number)	Возвращает десятичный логарифм аргумента.
SIN(число)	SIN(number)	Возвращает синус аргумента
TAN(число)	TAN(number)	Возвращает тангенс аргумента
ЗНАК (число)	SGN(number)	Возвращает знак аргумента
ОСТАТ (число; делитель)	MOD(number; number)	Возвращает остаток от деления числа на делитель
ПИ()	PI()	Возвращает значение числа с точностью до 15 знаков (в данном случае З.141592б5З589 Обратите внимание на то, что функция ПИ() не имеет аргумента
СЛЧИС()	RND()	Возвращает случайное число между 0 и 1. Чтобы получить случайное вещественное число между a и b, можно использовать следующую формулу: СЛЧИС()*(b-a)+a
ЦЕЛОЕ (число)	INT(number)	Округляет число до ближайшего меньшего целого

1.4 График функции с двумя условиями

Рассмотрим пример построения графика функции при x [0:1]

	x<0.5
	x>=0.5

График строится по представленным ранее принципам, за исключением того, что в ячейку B1 вводится формула:

=ЕСЛИ(A1<0,5;1+ABS(0,2-A1)/(1+A1+A1^2);A1^(1/3))

Обратите внимание на то, что для возведения в степень в коде используется соответствующая операция, т. е. A1^(1/З), а не функция рабочего листа СТЕПЕНЬ, т. е. СТЕПЕНЬ (A1; 1/3). В обоих случаях получается один и тот же результат, но, применение операции возведения в степень делает код более очевидным.

В коде фигурирует функция логического ветвления, ЕСЛИ(IF), которая имеет синтаксис:

ЕСЛИ (лог_ выражение; значение _ если_ истина; значение_ если_ ложь)

Рассмотрим ее аргументы.

лог_ выражение - это любое значение или выражение, принимающее значения истина или ложь. Например, А10=100 - это логическое выражение; если значение в ячейке A10 равно 100, то выражение принимает значение ИСТИНА. В противном случае - ложь. Данный аргумент может быть использован в любом операторе сравнения.

значение_ если_ истина - это значение, которое возвращается, если лог_ выражение равно ИСТИНА. Например, если этот аргумент - строка «План реализации выполнен” и лог_ выражение равно ИСТИНА, тогда функция ЕСЛИ отобразит текст План реализации выполнен. Если лог_ выражение равно ИСТИНА, а значение_ если_ истина пусто, то возвращается значение 0. Чтобы отобразить слово ИСТИНА, необходимо использовать логическое значение ИСТИНА для этого аргумента. Значение_если_истина может быть формулой.

значение_ если_ ложь - это значение, которое возвращается, если лог_ выражение равно ложь. Например, если этот аргумент - строка “План реализации перевыполнен” и лог_ выражение равно ложь, то функция ЕСЛИ отобразит текст План реализации перевыполнен. Если лог_ выражение равно ложь, а значение _если_ ложь опущено (т. е. после значение_ если_ истина нет точки с запятой), то возвращается логическое значение ложь. Если лог_выражение равно ложь, а значение_ если_ ложь пусто (т: е. после значение_ если_ истина стоит точка с запятой с последующей закрывающей скобкой), то возвращается значение 0. Значение если ложь может быть формулой.

1.5. Логические функции

МS Excel имеются функции логических условий, перечисленные в таблице 1.

Таблица 1 - Функции логических условий Кроме функции ЕСЛИ в MS Excel имеются еще две функции, использующие логически условия. Это функции:

СЧЁТЕСЛИ(COUNTIF) - подсчитать количество ячеек внутри диапазона, удовлетворяющих заданному критерию;

СУММЕСЛИ (SUMIF) - суммирует значения ячеек диапазона, удовлетворяющих заданному критерию.

Функция (рус.)	Функция (англ.)	Описание
И (лог_знач1; лог_знач2; ..)	AND(arg1;arg2; ..)	Возвращает значение ИСТИНА, если все аргументы имеют значение истинА; возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ. Например, И(2 + 2 = 4; 2 + З = 5) воз вращает значение ИСТИНА, если ячейка В4 содержит число из интервала от 1 до 100, то функция И (1<В4; В4<100) возвращает значение ИСТИНА, а в противном случае - ложь.
ИЛИ(лог_знач1; лог_знач2; ..)	OR(arg1; arg2; …)	Логическое сложение. Возвращает ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА; возвращает ложь, если все аргументы имеют значение ЛОЖЬ. Например, И(2 + 2 = 4; 2 + З =6) возвращает значение ИСТИНА. Если ячейка В4 содержит число меньше 1 или больше 100, то ИЛИ(B4<1; B4>100) возвращает значение ИСТИНА, а в противном случае - Ложь
НЕ (лог_ знач.)	NOT(arg)	Логическое отрицание. Изменяет на противоположное значение логическое значение своего аргумента. Например, НЕ(2 + 2 = 5) воз вращает значение ИСТИНА. Если ячейка B4 содержит число меньше 1 или больше 100, то НЕ (ИЛИ(B4<1; В4>100)) возвращает Ложь, а в противном случае -лОжь

1.6 График функции с тремя условиями

Рассмотрим пример построения графика функции y при x [0; 1]:

График строится, как описано ранее в этой главе, за исключением того, что в ячейку B1 вводится формула

=ЕСЛИ(A2<0,2;1+LN(1+A1);ЕСЛИ(И(A1>=0,2;A1<=0,8);(1+A1^(1/2))/(1+A1);2*EXP(-2*A1)))

Тот же результат можно получить, введя в ячейку B1 простую формулу:

=ЕСЛИ(A1<0,2;1+LN(1+A1);ЕСЛИ(A1<=0,8;(1+A1^(1/2))/(1+A1);2*EXP(-2*A1)))

1.7 Два графика в одной системе координат

Рассмотрим пример построения в одной системе координат при x [-3; 0] графиков следующих двух функций:

o y=2*sin(x)

o z=3*cos(2x)-sin(x)

Начнем процесс построений.

1. В ячейки A1, B1 и C1 введите соответственно x, y и z.

2. Выделите диапазон A1:C1. Выберите команду Формат >Ячейки. На вкладке Выравнивание появившегося диалогового окна Формат ячеек в группе Выравнивание в списке по горизонтали укажите значение по правому краю. Нажмите кнопку ОК. Заголовки столбцов окажутся выровненными по правому краю.

3. В диапазон ячеек A2:A17 введите значения аргумента x от -3 до 0 с шагом 0.2.

4. В ячейки B2 и C2 введите формулы

=2*SIN(A2)

=3*СОS(2*A2)-SIN(A2)

5. Выделите диапазон B2:C2, расположите указатель мыши на маркере заполнения этого диапазона и протяните его вниз так, чтобы заполнить диапазон BЗ:C17.

6. Выберите команду Вставка > Диаграмма

7. В появившемся диалоговом окне Мастер диаграмм (шаг 1 из 4): тип диаграммы на вкладке Стандартные в списке Тип выберите значение График, а в списке Вид укажите стандартный график. Нажмите кнопку Далее.

8. В появившемся окне Исходные данные на вкладке диапазон данных вы берите переключатель Ряды в столбцах, т. к. данные располагаются в столбцах. В поле ввода диапазон приведите ссылку на диапазон данных В2:С17, значения из которого откладываются вдоль оси ординат.

9. На вкладке Ряд диалогового окна Исходные данные в поле ввода Подписи оси Х укажите ссылку на диапазон А2:А17, значения из которого откладываются по оси абсцисс (рис. 2.14).

Рисунок 2.14

В списке Ряд приводятся ряды данных, откладываемых по оси ординат (в нашем случае имеется два ряда данных). Эти ряды автоматически определяются на основе ссылки, указанной в поле ввода Диапазон предыдущего шага алгоритма. В поле Значения автоматически выводится ссылка на диапазон, соответствующий выбранному ряду из списка Ряд. В поле ввода Имя отображается ссылка на ячейку, в которой содержится заголовок соответствующего ряда. Этот заголовок в дальнейшем используется мастером диаграмм для создания легенды.

10. Выберите в списке Ряд элемент Ряд1. В поле ввода Имя укажите ссылку на ячейку B1, значение из которой будет использоваться в качестве идентификатора данного ряда. Это приведет к тому, что в поле Имя автоматически будет введена ссылка на ячейку в абсолютном формате. В данном случае, =Лист1!$B$1. Теперь осталось только щелкнуть на элементе Ряд1 списка Ряд. Это приведет к тому, что элемент Ряд1 поменяется на y, т. е. на то значение, которое содержится в ячейке B1. Аналогично поступите с элементом Ряд2 списка Ряд. Сначала выберите его, затем в поле ввода Имя укажите ссылку на ячейку C1, а потом щелкните на элементе Ряд2. На рис.2.15 показана вкладка Ряд диалогового окна Исходные данные после задания имен рядов. Теперь можно нажать кнопку Далее.

Рисунок 2.15

11. В появившемся диалоговом окне Мастер диаграмм (шаг З из 4): Параметры диаграммы на вкладке Заголовки в поле Название диаграммы введите график двух функций, в поле Ось Х (категорий) введитеx, в поле Ось Y(значений) введите y и z . На вкладке Легенда установите флажок Добавить легенду. Нажмите кнопку Далее.

12. В появившемся диалоговом окне Мастер диаграмм (шаг 4 из 4): размещение диаграммы выберите переключатель Поместить диаграмму на листе имеющемся. Диаграмма будет внедрена в рабочий лист, имя которого указывается в соответствующем списке. Если выбрать переключатель Поместить диаграмму на листе отдельном, то диаграмма будет помещена на листе диаграмм. Нажмите кнопку Готово.

Для большей презентабельности построенной диаграммы в ней были произведены следующие изменения по сравнению с оригиналом.

o Изменена ориентация подписи оси ординат с вертикальной на горизонтальную. Для этого выберите подпись оси ординат. Нажмите правую кнопку мыши и в появившемся контекстном меню укажите команду Формат названия оси. На вкладке Выравнивание диалогового окна Формат названия оси в группе Ориентация установите горизонтальную ориентацию. Нажмите кнопку ОК.

o Для того чтобы пользователю было легче отличить, какая линия является графиком функции у, а какая z - , изменен вид графика функции. С этой целью выделите график функции. Нажмите правую кнопку мыши и в появившемся контекстном меню выберите команду Формат рядов данных. На вкладке Вид диалогового окна Формат ряда данных, используя элементы управления групп, Маркер и Линия, установите необходимый вид линии графика. Нажмите кнопку ОК.

o Изменен фон графика. С этой целью выделите диаграмму (но не область построения). Нажмите правую кнопку мыши и в появившемся контекстном меню выберите команду Формат области диаграммы. На вкладке Вид диалогового окна Формат области диаграммы установите флажок скругленные углы, а, используя, элементы управления группы Заливка, установите цвет и вид заливки фона. Нажмите кнопку ОК.

1.8 Построение поверхности

Продемонстрируем технологию построения поверхностей на примере следующей функции, зависящей от двух аргументов:

z=x²-y² при x [-2; 2], y[-1; 1].

Прежде, чем воспользоваться мастером диаграмм, надо построить таблицу значений функции z по обоим ее аргументам, например, по аргументу x от -2 до 2 с шагом 0.2, а по y от -1 до 1 с шагом 0.2. Для этого:

1. Введите в ячейку A2 значение - 2, а в ячейку A3 - значение -1.8. Выберите диапазон ячеек A2 :A3. Расположите указатель мыши на маркере заполнения этого диапазона и протяните его на диапазон A4 :А22. Таким образом, значения аргумента x протабулируем от -2 до 2 с шагом 0.2.

2. Введите в ячейку B1 значение -1, а в ячейку C1 значение -0.8. Выберите диапазон ячеек B1:C1. Расположите указатель мыши на маркере заполнения этого диапазона и протяните его на диапазон D1:L1 Значения аргумента y протабулированы от -1 до 1 с шагом 0.2.

3. В ячейку B2 введите формулу: =$A2^2-B$1^2

4. Выберите ячейку В2, расположите указатель мыши на маркере ее заполнения и протяните его вниз на диапазон В2:L22.

На левом рабочем листе рис. 2.17 показан результат табуляции функции, зависящей от двух аргументов, а на правом - часть формул, которые были введены в ячейки при копировании формулы, введенной в ячейку B2, перемещением маркера заполнения на диапазон B2:L22.

Перейдем к конструированию поверхности по результатам табуляции. Для этого:



Рисунок 2.17 Использование в формуле абсолютной ссылки на строку и столбец существенно. Напомним, что знак $ в имени ячейки, стоящий перед номером строки, создает абсолютную ссылку на строку, а перед именем столбца - абсолютную ссылку на столбец. Поэтому при копировании формулы из ячейки B2 на диапазон B2:L22, в ячейках этого диапазона будут найдены значения функции z при соответствующих значениях аргументов x и y.

1. Выберите команду Вставка > Диаграмма.

2. В проявившемся диалоговом окне Мастер диаграмм (шаг 1 из 4): тип диаграммы на вкладке Стандартные в списке Тип выберите значение Поверхность, а в списке Вид укажите стандартную поверхность. Нажмите кнопку Далее.

3. В следующем диалоговом окне мастера диаграмм на вкладке Диапазон данных выберите переключатель Ряды в столбцах, т. к. данные располагаются в столбцах. В поле ввода Диапазон приведите ссылку на диапазон данных A1:L22, т. е. диапазон, который содержит в себе значения аргументов и значения функции. Нажмите кнопку Далее.

4. В появившемся диалоговом окне Мастер диаграмм (шаг 3 из 4): в параметры диаграммы на вкладке Заголовки в поле Название диаграммы введите Поверхность, в поле Ось X (категорий) укажите x, в поле Ось Y (рядов данных) задайте y, в поле Ось Z. (значений) введите z. На вкладке Легенда сбросьте флажок Добавить легенду. Нажмите кнопку Готово. MS Excel разрешает пользователю не только изменять размеры рабочей области диаграммы посредством маркеров изменения размеров, но и ориентацию поверхности в пространстве. Для этого достаточно выделить диаграмму, а затем выбрать команду Диаграмма > Объемный вид. На экране отобразится диалоговое окно Формат трехмерной проекции, элементы которого позволяют изменять ориентацию поверхности в пространстве.

2 РЕШЕНИЕ НЕЛИНЕЙНЫХ УРАВНЕНИЙ

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

Рассмотрим, как на рабочем листе при помощи подбора параметра можно находить корни уравнения с одним аргументом. В качестве базового примера рассмотрим следующее уравнение:

x³ - 0.01х² - 0.7044х + 0.139104 =0.

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

1. Введите в ячейку A2 значение -1, а в ячейку A3- значение -0,8.

2. Выберите диапазон A2:А3, расположите указатель мыши на маркере заполнения этого диапазона и протяните его на диапазон A4 :А12. Аргумент протабулирован.

3. В ячейку B2 введите формулу:

=A2^3-0,01*A2^2-0,7044*A2+0,139104

Выберите ячейку B2. Расположите указатель мыши на маркере заполнения этой ячейки и протяните его на диапазон B3:B12. Функция также протабулирована. Полином меняет знак на интервалах [-1; -0.8], [0.2; 0.4] и [0.6; 0.8], и поэтому на каждом из этих интервалов имеется свой корень. Так как полином третьей степени имеет не более трех корней, то они все локализованы. Прежде чем приступить к нахождению корней при помощи подбора параметра, необходимо выполнить некоторую подготовительную работу:

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

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

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

o Отведите ячейку, например, D2, под функцию, для которой ведется поиск корня. Причем, вместо неизвестного, у этой функции должна указываться ссылка на ячейку, отведенную под искомый корень. Таким образом, в ячейку D2 введите формулу

=C2^3- 0,01*C2^2-0,7044*C2+0,139104

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

o Отвести ячейку C3 под второй корень, ввести в нее начальное приближение 0,3. а в ячейку D3 ввести следующую формулу

=C3^3-0,01*C3^2-0,7044*C3+0,139104

o Отвести ячейку C4 под второй корень, ввести в нее начальное приближение 0,7, а в ячейку D4 ввести следующую формулу

=C4^3-0,01*C4^2- 0,7044*C4+0,139104

Теперь можно переходить к нахождению первого корня уравнения:

1. Выберите команду Сервис > Подбор параметра. На экране отобразится диалоговое окно Подбор параметра.

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

3. В поле Значение введите 0. Здесь указывается значение из правой части уравнения.

4. В поле Изменяя значение ячейки введите С2. В данном поле приводится ссылка на ячейку, отведенную под переменную.

5. Нажмите кнопку ОК. На экране отображается окно Результат подбора параметра с результатами работы команды Подбор параметра. Кроме того, рассматриваемое средство помещает найденное приближенное значение корня в ячейку C2. В данном случае оно равно -0.919999. Аналогично в ячейках C3 и C4 находятся два оставшихся корня. Они равны 0.21000 и 0.71999 Вводить ссылки на ячейки в поля диалогового окна Подбор параметра удобнее не с клавиатуры, а выбором соответствующей ячейки на рабочем листе. При этом MS Excel автоматически будет превращать их в абсолютные ссылки - в нашем случае $D$2 и $C$2..

2.2 Нахождение корней уравнения методом деления отрезка пополам

Хорошим упражнением по работе с 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 методом деления отрезка пополам уравнения x² -2 = 0. За первоначальный отрезок локализации корня выбран [0; 2].

Для реализации этого метода введите в ячейки рабочего лист формулы либо значения (табл. 1).

Таблица 1 - Формулы для нахождения корней уравнения На самом деле в диапазон C4 :E4 не надо вводить формулы с клавиатуры. Выберите диапазон C3: F3, расположите указатель мыши на маркере заполнения, и протяните его на одну строку вниз.

Ячейка	Формула либо значение
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,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");"")

Теперь осталось только выбрать диапазон A4:F4, расположить указатель мыши на маркере его заполнения и протянуть указатель вниз до тех пор, пока в столбце F не появится сообщение о том, что корень найден. В данном случае сообщение появится в ячейке F14, а значение корня с точностью до 0.001 равно 1.414.

В заключение отметим, что в рассмотренном примере использовались:

o Операция конкатенации строк, которая объединяет несколько строк в одну. Операция конкатенация обозначается символом амперсанда &. При объединении двух строк вторая строка добавляется непосредственно в конец первой. Результатом является строка большего размера, содержащая целиком обе исходные строки. Например, “Корень“ & “уравнения” равно одной строке “Корень уравнения”. Обратите внимание, что пробел в конце первой строки используется для того, чтобы в результирующей строке отделить первое слово от второго. Вместо символа конкатенации можно также использовать функцию СЦЕПИТЬ(CONCATENATE). Тот же самый результат дает формула

=СЦЕПИТЬ(“Корень“;“уравнения”).

Если значение Корень введено в ячейку A1, а значение уравнения - в ячейку B1 то тот результат получается формулой = СЦЕПИТЬ (A1;” “;B1) или A1&“ “&B1:

o Функция рабочего листа из категории функций по работе с текстом ТЕКСТ (ТЕХТ). Данная функция преобразует значение в текст в заданном числовом формате.

2.3 Синтаксис функции текст

текст (значение; формат)

o значение - либо числовое значение, либо формула, вычисление которой дает числовое значение, либо ссылка на ячейку, содержащую числовое значение;

o формат - числовой формат с вкладки Число диалогового окна Формат ячеек, отображаемого на экране выбором команды Формат - Ячейки. Формат не может содержать звездочку * и не может принадлежать к категории Общий.

Приведем три примера работы функции ТЕКСТ:

o =ТЕКСТ(2,7153;"0,00р.") возвращает 2.72 р.

o =ТЕКСТ(2,7153;"0,000") возвращает 2.715

o =ТЕКСТ("17.5.03";"Д МММ ГГГГ") возвращает 17 май 2003