Использование Excel в научных и инженерных расчетах

ПОСТРОЕНИЕ ГРАФИКА ФУНКЦИИ С ОДНИМ УСЛОВИЕМ

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

при .

Этот график строится так же, как в предыдущем разделе, за одним исключением – в ячейку В1 вводится формула:

=ЕСЛИ(А1<0.5; (1+ABS(0.2-A1))/(1+A1+A1^2); A1^(1/3))

	1,2
0,1	0,990991
0,2	0,806452
0,3	0,791367
0,4	0,769231
0,5	0,793701
0,6	0,843433
0,7	0,887904
0,8	0,928318
0,9	0,965489

Синтаксис логической функции ЕСЛИ:

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

Функция ЕСЛИ используется для проверки значений формул и организации переходов в зависимости от результата этой проверки.

ПОСТРОЕНИЕ ГРАФИКА ФУНКЦИИ С ДВУМЯ УСЛОВИЯМИ

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

при


0,1	1,09531
0,2	1,206011
0,3	1,190556
0,4	1,16604
0,5	1,138071
0,6	1,109123
0,7	1,080388
0,8	1,05246
0,9	0,330598
	0,270671

График строится та же, как в предыдущем разделе, только В1 вводится формула:

=ЕСЛИ(А1<0.2;1+LN(1+A1); ЕСЛИ(И (A1>=0.2;A1<=0.8); (1+A1^(1/2))/(1+A1); 2*EXP(-2*A1)))

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

=ЕСЛИ(А1<0.2; 1+LN(1+A1); ЕСЛИ(A1<=0.8; (1+A1^(1/2))/(1+A1); 2*EXP(-2*A1)))

ПОСТРОЕНИЕ ДВУХ ГРАФИКОВ В ОДНОЙ СИСТЕМЕ КООРДИНАТ

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

В диапазон ячеек А2:А17 введем значения переменной от –3 до 0 с шагом 0,2. В ячейки В1 и С1 введем и , соответственно. В ячейки В2 и С2 введем формулы

=2*SIN(A2)

=3*COS(2*A2)-SIN(A2)

	Y	Z
-3	-0,28224	3,021631
-2,8	-0,66998	2,661686
-2,6	-1,031	1,921051
-2,4	-1,35093	0,93796
-2,2	-1,61699	-0,1135
-2	-1,81859	-1,05163
-1,8	-1,9477	-1,71643
-1,6	-1,99915	-1,99531
-1,4	-1,9709	-1,84122
-1,2	-1,86408	-1,28014
-1	-1,68294	-0,40697
-0,8	-1,43471	0,629758
-0,6	-1,12928	1,651716
-0,4	-0,77884	2,479538
-0,2	-0,39734	2,961852
3,89E-16	7,77E-16

Выделите диапазон В2:С2, установив указатель мыши на маркере заполнения этого диапазона и перетащив его вниз так, чтобы заполнить диапазон В2:С17. Выделите диапазон ячеек А1:С17, в который внесены: таблица значений двух функций, их общий аргумент и заголовки столбцов В и С, и вызовите мастер диаграмм. С помощью мыши на рабочем листе выделите прямоугольную область, где будет построен график. Проверьте, правильно ли введен в первом диалоговом окне Мастер диаграмм диапазон ячеек, по которому строится график. На втором шаге мастера диаграмм выберете тип диаграммы – График. На третьем шаге мастера диаграмм выберете тип графика. На четвертом шаге мастера диаграмм заполните диалоговое окно следующим образом. В группе Ряды данных находятся установите переключатель в положение В столбцах. В поле Считать метками оси Х введите 1 (номер столбца), а в поле Считать метками легенды – 1 (номер строки). На пятом шаге мастера диаграмм заполните диалоговое окно следующим образом. В группе Добавить легенду установите переключатель в положение Да. В поле Название диаграммы введите Графики функций, а в группе Название по оси в поле Категорий (Х) введите Х, а в поле Значений (Y) введите и . Нажатие кнопки Готово завершает построение графика.

Для того, чтобы графики функций y и z различались для наглядности по типу линий, необходимо сделать следующее: график, внешний вид которого надо изменить, выделяется и с помощью контекстного меню вызывается диалоговое окно Форматирование элемента данных, которое позволяет изменять тип, толщину и цвет линии, а также тип, фон и цвет маркера.

ПОСТРОЕНИЕ ПОВЕРХНОСТИ

Рассмотрим пример построения поверхности при .

В диапазон ячеек В1:L1 введите последовательность значений: -1, -0.8, …, 1 переменной , а в диапазоне ячеек А2:А12 – последовательность значений: -1, -0.8, …, 1 переменной . В ячейку В2 введите формулу =$A2^2-B$1^2. Выделите эту ячейку так, установив указатель мыши на ее маркере заполнения и протащив его так, чтобы заполнить диапазон В2:L12. Знак $, стоящий перед буквой в имени ячейки, дает абсолютную ссылку на столбец с данным именем, а знак $, стоящий перед цифрой – абсолютную ссылку на строку с этим именем. Поэтому при протаскивании формулы из ячейки В2 в ячейки диапазона В2:L12 в них будет найдено значение при соответствующих значениях и . Таким образом, таблица значений функции при различных значениях переменных создана.

	-1	-0,8	-0,6	-0,4	-0,2		0,2	0,4	0,6	0,8
-1		0,36	0,64	0,84	0,96		0,96	0,84	0,64	0,36
-0,8	-0,36		0,28	0,48	0,6	0,64	0,6	0,48	0,28		-0,36
-0,6	-0,64	-0,28		0,2	0,32	0,36	0,32	0,2		-0,28	-0,64
-0,4	-0,84	-0,48	-0,2		0,12	0,16	0,12		-0,2	-0,48	-0,84
-0,2	-0,96	-0,6	-0,32	-0,12		0,04		-0,12	-0,32	-0,6	-0,96
	-1	-0,64	-0,36	-0,16	-0,04		-0,04	-0,16	-0,36	-0,64	-1
0,2	-0,96	-0,6	-0,32	-0,12		0,04		-0,12	-0,32	-0,6	-0,96
0,4	-0,84	-0,48	-0,2		0,12	0,16	0,12		-0,2	-0,48	-0,84
0,6	-0,64	-0,28		0,2	0,32	0,36	0,32	0,2		-0,28	-0,64
0,8	-0,36		0,28	0,48	0,6	0,64	0,6	0,48	0,28		-0,36
		0,36	0,64	0,84	0,96		0,96	0,84	0,64	0,36

Перейдем к построению поверхности. Выделите диапазон ячеек А1:L12, содержащий таблицу значений функции и ее аргументов, и вызовите мастер диаграмм. С помощью мыши на рабочем листе выделите прямоугольную область, где будет построен график. Проверьте, правильно ли введен в первом диалоговом окне Мастер диаграмм диапазон ячеек, по которому будет строиться поверхность. На втором шаге мастера диаграмм выберите тип диаграммы - Поверхность. На третьем шаге мастера диаграмм выберите вид поверхности. На четвертом шаге мастера диаграмм заполните диалоговое окно следующим образом. В группе Ряды данных находятся установите переключатель в положение В столбцах. В поле Считать метками оси Х введите 1 (номер столбца), а в поле Считать метками оси Значений – 1 (номер строки). На пятом шаге мастера диаграмм заполните диалоговое окно следующим образом. В группе Добавить легенду? установите переключатель в положение Нет. В поле Название диаграммы введите Поверхность, а в группе Название по оси в поля Категорий (Х), Значений (Z) и Рядов (Y) введите

соответственно. Нажатие кнопки Готово завершает построение графика.

НАХОЖДЕНИЕ КОРНЕЙ УРАВНЕНИЙ

Рассмотрим пример нахождения всех корней уравнения

Следует отметить, что у полинома третьей степени имеется не более трех вещественных корней. Для нахождения корней их предварительно нужно локализовать. С этой целью необходимо построить график функции или ее протабулировать. Например, протабулируем полином на отрезке [-1,1] с шагом 0.2. В ячейку В2 надо ввести формулу A2^3-0.01*A2^2-0.7044*A2+0.139104

X	Y
-1	-0,1665
-0,8	0,184224
-0,6	0,342144
-0,4	0,355264
-0,2	0,271584
	0,139104
0,2	0,005824
0,4	-0,08026
0,6	-0,07114
0,8	0,081184
1	0,424704

Из графика функции видно, что полином меняет на интервале [-1, -0.8], [0.2,0.4], [0.6,0.8]. Это означает, что на каждом из них имеется корень данного полинома. Поскольку полином третьей степени имеет не более трех действительных корней, значит, локализованы все его корни.

Найдем корни полинома методом последовательных приближений с помощью команды Сервис Подбор параметра. Относительная погрешность вычислений и предельное число итераций задаются на вкладке Вычисления диалогового окна Параметры, открываемого командой Сервис Параметры. Зададим относительную погрешность и предельное число итераций, равными 0.00001 и 1000, соответственно. В качестве начальных значений приближений к корням можно взять любые точки из отрезков локализации корней. Возьмем, например, их средние точки: -0.9, 0.3 и 0.7 и введем их в диапазон ячеек С2:С4. В ячейку D2 введем формулу: =C2^3-0.01*C2^2-0.7044*C2+0.139104. Выделите эту ячейку и с помощью маркера заполнения протащите введенную в нее формулу на диапазон D2:D4. Таким образом, в ячейках D2:D4 вычисляются значения полинома при значениях аргумента, введенного в ячейки С2:С4, соответственно. Теперь выберите команду Сервис Подбор параметра и заполните диалоговое окно Побор параметра следующим образом. В поле Установить в ячейке введите D2. Следует отметить, что в этом поле дается ссылка на ячейку, в которую введена формула, вычисляющая значение левой части уравнения. Для нахождения корня уравнения с помощью средства подбора параметров надо записать уравнение так, чтобы его правая часть не содержала переменную. В поле Значение введите 0 (в этом поле указывается правая часть уравнения). В поле Изменяя значение ячейки введите С2 (в этом поле дается ссылка на ячейку, отведенную под переменную). Вводить ссылки на ячейки в поля диалогового окна Подбор параметра удобнее не с клавиатуры, а щелчком на соответствующей ячейке. При этом Excel автоматически будет превращать их в абсолютные ссылки (в данном случае $D$2 и $C$2). После нажатия кнопки ОК средство подбора параметров находит приближенное значение корня, которое помещает в ячейку С2. В данном случае оно равно –0.919999. Аналогично в ячейках С3 и С4 находятся два оставшихся корня. Они равны 0.210000684 и 0.7.

Приближение	Значение функции
-0,919999997	5,35264E-09
0,210000684	-3,94221E-07
0,7	-0,015876

НАХОЖДЕНИЕ КОРНЕЙ УРАВНЕНИЯ МЕТОДОМ ДЕЛЕНИЯ ОТРЕЗКА ПОПОЛАМ

Рассмотрим алгоритм нахождения корня уравнения F(x)=0 методом деления отрезка пополам. Пусть непрерывная функция F(x) имеет значения разных знаков на концах отрезка , т.е. F(a)F(b)<0, тогда уравнение F(x)=0 имеет корень внутри этого отрезка, который называется отрезком локализации корня. Пусть - середина отрезка [a,b]. Если F(a)F(c) 0, то корень находится на отрезке [a,c], который принимается за новый отрезок локализации корня. Если F(a)F(c)>0, то за новый отрезок локализации корня возьмем [c,b]. Следует отметить, что новый отрезок локализации корня в два раза меньше первоначального. Процесс деления отрезка локализации корня продолжается до тех пор, пока его длина не станет меньше - точности нахождения корня. В этом случае любая точка отрезка локализации отличается от корня не более чем на .

Рассмотрим нахождение корня уравнения с точностью до 0.001 методом деления отрезка по пополам. В ячейку Е7 введем погрешность нахождения корня. За первоначальный отрезок локализации корня выберем отрезок [0,2]. В ячейки D10, E10, F9, G9, H9 и I10 введем, соответственно, формулы =ЕСЛИ(G9<=0;D9;F9)

=ЕСЛИ(G9<=0;F9;E9)

=(D9+E9)/2

=(D9^2-2)*(F9^2-2)

=F9^2-2

=ЕСЛИ(E9-D9<$E$7;”Корень найден и равен “&ТЕКСТ(F9;”0.0000”);” “)

которые протаскиваете вниз по столбцам до тех пор, пока не будет найден корень.

Точность нахождения корня	0,001
a	b	C	Проверка знака	Значение функции в средней точке
				-1
		1,5	-0,25	0,25
	1,5	1,25	0,4375	-0,4375
1,25	1,5	1,375	0,047852	-0,109375
1,375	1,5	1,4375	-0,00726	0,06640625
1,375	1,4375	1,40625	0,002457	-0,022460938
1,40625	1,4375	1,421875	-0,00049	0,021728516
1,40625	1,421875	1,414063	9,6E-06	-0,000427246
1,4140625	1,421875	1,417969	-4,5E-06	0,010635376
1,4140625	1,41796875	1,416016	-2,2E-06	0,00510025
1,4140625	1,416015625	1,415039	-1E-06	0,002335548
1,4140625	1,415039063	1,414551	-4,1E-07	0,000953913
					Корень равен 1,4146

Функция ТЕКСТ преобразует число в текстовую строку по указанному формату и имеет следующий синтаксис:

ТЕКСТ (значение; формат)

Аргументы:

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

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

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

ТЕКСТ(2.7153; “0.00p.”) возвращает 2.72р.

ТЕКСТ(2.7153; “0.000’) возвращает 2.715

ЗАНЯТИЕ № 5.

РАБОТА С МАССИВАМИ

Часто при работе с таблицами возникает необходимость применить одну и ту же операцию к целому диапазону ячеек или произвести расчеты по формулам, зависящим от большого массива данных. Для решения подобных задач Excel предоставляет простые и удобные средства.

1. ПРОСТЕЙШИЕ ОПЕРАЦИИ НАД МАССИВАМИ

В качестве примера простой операции над массивами рассмотрим умножение массива А1:В2 на число 5. Необходимо выделить на рабочем листе область, например D1:E2, такого же размера, как и массив-множимое. Следует ввести формулу =А1:В2*5. Для этого надо установить курсор в строке формул и закончить ввод не как обычно, нажатием клавиши <Enter>, нажатием <Ctrl>+<Shift>+<Enter>. Таким образом будет сообщено программе, что необходимо выполнить операцию над массивом. При этом Excel заключит формулу в строке формул в фигурные скобки: {=A1:B2*5}.

При работе с массивами формула действует на все ячейки диапазона. Нельзя изменять отдельные ячейки в операндах формулы. Аналогично можно вычислить:

§ сумму (разность) массивов;

§ поэлементное произведение (деление) массивов;

§ массив, каждый элемент которого связан посредством некоторой функции с соответствующим элементом первоначального массива.

2. ВСТРОЕННЫЕ ФУНКЦИИ ДЛЯ РАБОТЫ С МАТРИЦАМИ

В Excel имеются следующие специальные функции для работы с матрицами:

МОБР	Обратная матрица
МОПРЕД	Определитель матрицы
МУМНОЖ	Матричное произведение двух матриц
ТРАНСП	Транспонирование матрицы

Во всех случаях при работе с матрицами перед вводом формулы надо выделить область на рабочем листе, куда будет выведен результат вычислений.

Рассмотрим в качестве примера решение системы линейных уравнений с двумя неизвестными, матрица коэффициентов которой записана в ячейки F1:G2, а свободные члены – в ячейки I1:I2. Для решения этой задачи необходимо вспомнить, что решение линейной системы АХ=В, где А – матрица коэффициентов, В – столбец (вектор) свободных членов, Х – столбец (вектор) неизвестных, имеет вид Х=А^-1В, где А^-1 – матрица обратная по отношению к А. Поэтому для решения системы уравнений надо выделить под вектор решений диапазон К1:К2 и ввести в него формулу {=МУМНОЖ(МОБР(F1:G2);I1:I2)}.

					2,166667
					-1,33333

Рассмотрим систему линейных уравнений А²Х=В, где . Для решения этой системы надо ввести в диапазон ячеек А1:В2 элементы матрицы А, а в диапазон ячейки D1:D2 – элементы столбца свободных членов В. Следует выбрать диапазон F1:F2, куда будут помещены элементы вектора решения, надо ввести следующую формулу:

{=МУМНОЖ(МОБР(МУМНОЖ(А1:В2;А1:В2));D1:D2)}

					0,02071
					0,042899

Рассмотрим пример вычисления квадратичной формулы Z=X^TAX, где А – квадратная матрица, введенная в диапазон А2:В3, Х – вектор, введенный в диапазон D2:D3, а символ (^Т) обозначает операцию транспонирования. Для вычисления Z надо ввести в ячейку F2 формулу

{=МУМНОЖ(МУМНОЖ(ТРАНСП(D1:D2);A1:B2);D1:D2)}

A	B	D	F

3. РЕШЕНИЕ СИСТЕМ НЕЛИНЕЙНЫХ УРАВНЕНИЙ

Положим, имеется система двух нелинейных уравнений:

которые надо решить, т.е. найти точки их пересечения.

Как видно на графике, такая система имеет две точки пересечения. Для решения системы надо построить таблицу, где в клетках В2 и В3 ввести обе функции, которые в качестве аргумента Х ссылаются на ячейку А2. Кроме того, для контроля вычислений в С2 вводится целевая функция, которая вычисляет среднее отклонение значений функций друг от друга. Очевидно, если эти функции пересекаются (т.е. имеются решения), С2=0. Для розыска корня в окно Поиск решения вводятся необходимые параметры процесса. Результат вычислений существенно зависит от начального значения, заданного в качестве решения. Ниже приведен исходный и конечный вид таблицы, если задать его равным +10.

Начальное значение	Формулы	Целевая функция		Начальное значение	Формулы	Целевая функция
		53,5	1,302776	0,697224	2,07E-08
	-8			0,697224

Если начальное значение задать равным –10, то исходный и конечный вид таблицы будет иметь вид:

Начальное значение	Формулы	Целевая функция		Начальное значение	Формулы	Целевая функция
-10		43,5	-2,30278	4,302776	2,44E-08
				4,302776

Таким образом, в обоих случаях найдены два разных решения.

4. РЕШЕНИЕ СИСТЕМ ЛИНЕЙНЫХ УРАВНЕНИЙ

Нужно найти решение (корни) следующей системы линейных алгебраических уравнений:

Внести коэффициенты системы в таблицу в столбцы А, В и С. Свободные члены внести в столбец Е. В столбец D внести формулы вычисления свободных членов (D2=СУММПРОИЗВ($A$6:$C$6;A2:C2)). Задача состоит в том, чтобы добиться совпадения значений вычисленных и фактических значений столбцов D и Е. В качестве изменяемых значений используем ячейки А6, В6,С6. Первоначально они остаются пустыми, т.е. равными нулю. В окне Поиск решения вводятся значения только параметров: Изменяемые ячейки ($A$6:$C$6;A2:C2) и Ограничения ($D$2:$D$4=$E$2:$E$4).

X1	X2	X3	Левая часть	Свободные члены
	-1
		-2

Корни:

Получены три корня: Х1=1, Х2=2, Х3+3.

5. ПОШАГОВОЕ РЕШЕНИЕ СИСТЕМЫ ЛИНЕЙНЫХ УРАВНЕНИЙ МЕТОДОМ ГАУССА

Рассмотрим решение системы линейных уравнений методом Гаусса. Пусть дана следующая система линейных уравнений:

2x₁+3x₂+7x₃+6x₄=1

3x₁+5x₂+3x₃+ x₄=3

5x₁+3x₂+ x₃+3x₄=4

3x₁+3x₂+ x₃+6x₄=5

В диапазоны ячеек А1:D4 и E1:E4 введем матрицу коэффициентов и столбец свободных членов, соответственно. Содержимое ячеек A1:E1 скопируем в ячейки А6:Е6, А11:Е11, А16:Е16. В диапазон ячеек А7:Е7 введем формулу

{=A2:E2-$A$1:$E$1*(A2/$A$1)}

обращающая в нуль коэффициент при x₁ во втором уравнении системы. Выделим диапазон А7:Е7 и протащим маркер заполнения этого диапазона так, чтобы заполнить диапазон А7:Е9. Это обратит в нуль коэффициенты при x₁ в третьем и четвертом уравнениях системы. Скопируем значения из диапазона ячеек А7:Е7 в диапазон А12:Е12 и А17:Е17. Для копирования значений без формул следует воспользоваться командой Правка Специальная вставка и в открывшемся диалоговом окне Специальная вставка в группе Вставить надо установить переключатель в положение Значения.

В диапазон ячеек А13:Е13 ввести формулу

{=A8:E8-$A$7:$E$7*(B8/$B$7)}

Выделите диапазон А13:Е13 и протащите маркер заполнения этого диапазона так, чтобы заполнить диапазон А13:Е14. Это обратит в нуль коэффициент при x₂ в третьем и четвертом уравнениях системы. Скопируйте значения из диапазона ячеек А13:Е13 в диапазон Ф18:Е18. В диапазон ячеек А19:Е19 ввести формулу {=A14:E14-$A$13:$E$13*(C14/$C$13)}

Которая обращает в нуль коэффициент при x₃четвертого уравнения системы. Прямая прогонка метода Гаусса завершена. Обратная прогонка заключается в вводе в диапазоны G4:K4, G3:K3, G2:K2, G1:K1, соответственно, следующих формул: {=A19:E19/D19}

{=(A18:E18-G4:K4*D18)/C18}

{=(A17:E17-G4:K4*D17-G3:K3*C17)/B17}

{=(A16:E16-G4:K4*D16-G3:K3*C16-G2:K2*B16)/A16}

В диапазоне ячеек К1:К4 получено решение системы.

Прямая прогонка метода Обратная прогонка метода Гаусса

Гаусса Решение