Проведение практических занятий по вычислительной математике с использованием программного средства Microsoft Office Excel Текст научной статьи по специальности «Математика»

Сосновский Николай Николаевич

ПРОВЕДЕНИЕ ПРАКТИЧЕСКИХ ЗАНЯТИЙ ПО ВЫЧИСЛИТЕЛЬНОЙ МАТЕМАТИКЕ С ИСПОЛЬЗОВАНИЕМ ПРОГРАММНОГО СРЕДСТВА MICROSOFT OFFICE EXCEL

Аннотация

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

Так случилось, что автору данной статьи довелось изрядно поработать с программой Microsoft Office Excel. Будучи математиком, он в достаточной мере оценил достоинства этого средства для решения математических задач, с которыми сталкивается студент в период обучения.

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

© Н.Н. Сосновский, 2008

фическое изображение результатов задачи. Последнее важно, например, при решении задач по теории вероятностей и математической статистике, так как позволяет оценить, при каких исходных данных рассматриваемое событие является вполне возможным или даже практически достоверным, а при каких - практически невозможным. Таким образом, появляется возможность анализа задачи, зависимости результата от исходных данных и параметров. Сказанное справедливо в полной мере и для задач вычислительной математики, где из-за громоздких формул или трудоемких вычислений трудно порой в полной мере оценить и почувствовать достоинства того или иного метода. Автор в течение двух лет использовал указанные соображения при составлении учебных материалов и проведении на их основе практических занятий по методам вычислений, теории вероятностей и математической статистике со студентами вечерней формы обучения. Результат представляется положительным: уставшие после работы и не имеющие хоро-

шеи математическом подготовки «вечерние» студенты с интересом составляют таблицы для решения задач с помощью учебных материалов и при живом участии преподавателя. Работа по составлению вычислительных таблиц в Excel напоминает сборку изделия в детском конструкторе, и иногда студенты так увлекаются, что приходится в 22 часа их силоИ выпроваживать домоИ. Общие пояснения по сущности задачи и математическим формулам преподаватель дает с использованием переносноИ доски, на котороИ можно разместить и закрепить лист бумаги формата A3 (лучше чертежноИ, недорогоИ) и писать черным фломастером. Трудности по работе с Excel снимаются при непосредственном объяснении на компьютере как выполнить ту или иную функцию.

Далее в данноИ статье приводятся учебные пособия для решения некоторых задач по вычислительноИ математике.

МЕТОД ПРОСТОЙ ИТЕРАЦИИ ДЛЯ РЕШЕНИЯ СИСТЕМ ЛИНЕЙНЫХ АЛГЕБРАИЧЕСКИХ УРАВНЕНИЙ

Скопируем с листа Итерация пособия МетодИсключения.хк на чистый лист книги хк расширенную матрицу А _, состоящую из матрицы А и присоединенного к ней справа столбца Ь , (рис. 1). Результат копирования размещен в диапазоне ячеек А1:Е4. Элементы главной диагонали матрицы системы значительно преобладают над остальными элементами: в столбце в строк 1-4 подсчитаны суммы модулей недиагональных элементов матрицы по строкам - они существенно меньше диагональных элементов соответствующих строк. Так что итерацию можно записать в виде

= (E - DA) x + Db

где D - диагональная матрица, диагональные элементы которой равны обратным

,Ж(ПР-Г1ГН|М tp.'.d М^^НШВ

-¡Л ijM-г !Ki П0ОЬ!Л

Jfll Л Q tJ Л А * * а-

Li * А

J Ч'Р' Ш K-Ufi U « |

л 0 С D е F t : н J

1 2.13« L',H31 fl.IlM HI4L! 3.3EL3C 3,31 i3 ЭЕЗЭК

г una iHlj щвн D333I VLB 1/ЭШ i' даёт?

3 D.EM! Sin 3,433 [I.EWID

А D.I.JIJ 0.2131 ЦДШ ил Utu 3,3991 3 30112»

ъ

6 l 0 о 0 МИШ a я а

j и 1 о 0 ■s 01LE 339 а

а D Е 0 ■0 a 02074- а

9 * В 0 3 ■s a щ ^ЗЗИЗ-1

1D

11 i -с-.зят, -Р 14Й713 ■D ПХ45М у 77311711 ОД1237L

12 -CJHJ7 D -D, 1ЙЗМЗ -□ M.71I-J i «aiEi9!5 о:з4Етг ¿1= ЗЬ'. 73"! -iLJ=

13 -GJM313 -ЩЗМЛ 0 -цшдз 1J377M331 4 зл на

1+ ■GjG2313 jVHB ■4Ц2ЕИЗ 0

15

1Е Q ] 3 3 4 r. 1 т 3 S

17 1.+77L19 инш ЦШШ ЦШМ1 ОШЯ a gia ■ «137ЭИ щзюн:- ДН17Н UEMi

1Е l.UElF-l »,73*741 ЦВМП4 D537356 3j«M443 □ 575733 ДО8В» <з«знз аданп &.5ЫИ1 1

19 1Д377Ы HMSM: DE373J3 ■l!7!E3i UHfll OiMJM ццнп U3C.3ELI5

3D 1J№3 IJU443I 1,13175] 1 D35LK 3,ID97Ln L ICO ИЗ ],1НП 1 1 ]C??li Ц097Я L.K'3+LT

31

ZT J},Jy>]-?7 цнеи-i -Ц17131! vnmr -п пзизо ■10L4JT3 лшм цмзам -ЦЕНШ2 1

33 {ДО4НЗ -Ц 14673? 7.MMU Q2E7I3 -ООИЕ» ¡№074»

И 4ЛВЛ4 ДОШЕ 4НШ (№3 7»l фЛП 4ВЯ ЛДН4411 -ЩИ0Й31 ■i

35 -HM312D одиза .H№!9) адомзз ■ЩКИМ1 M03W5 драпа 1-М07И ■ 0 №13-35

л

31 ЦЙГ 5 171315 ■5,]i73fii7 ПйШЗЭ IUHHL3 Й.-Н73М ЩМ1ЭС

ЗЕ Щ.ЮШ DJ44JS3 Ш4С737 0ЖШ пшетп ■1013Е5Э аои!» 4НТЦМГ щюипм <

Рис. 1

x

значениям соответствующих элементов матрицы A, к - номер итерации.

Ниже строим единичную матрицу E и матрицу D.

Еще ниже строим матрицу B = E - DA и столбец f = Db. Матрицу B и f получаем с помощью функции МУМНОЖ(мас-сив1;массив2).

Правее строим столбец, элементы которого суть суммы модулей элементов строк матрицы A, и формулу для расчета коэффициента оценки погрешности:

I k I ^ a ik k-i i max xt - x Л <-max xt - x t ,

' ' '1 1 -a ' 1 ' ' 1

n

где a = max ^ a v / att .

' j=i j *t

Ниже, в столбцах 0, 1, 2, и т.д. построены последовательные приближения по формуле:

xk = Bxk-1 + f.

Нгг-Г

J] ifcr. ■ -JUh V-Ь y 1 1 QM ^mi

□ id d J |!кд?4 л j a - / * - r. 4 i

«a! г л Im-CiiwrjH^.

* il С г___1 Г _?_ И ! _J_ к L н

s

M A ь

У. t t L 1

X 4 1 i 1

M L 1 : 1

la

J: я И Tl-ir.ll M'-H-Ilrll TL ■ч-li

-il L m« L-НПН 1 Ни U4dï* ¿4I1LM ytx* ЫЯПТВ Г ими ЦЙ1 СДОИ VIUU

« i тал i«n< 1ШП нИп МШИ мо:о 1 ИШ

« : ■ ютн 1ЛКШ [Щ|] USUI :■ Tioiù ■ 1J4"I

jj

41 El L'iH]IirTVti

с 11ШН i [«« 3 мои -^snai ;пш очпе

H? iracu itnii i-nto ШХХЖН ; пш im«

« 41 -110ЛХ4 С ['>П( ищи [ПИ» iJuMtJ

t: Al^lL'A bJ-itlii*

n Ш-ЧШ ■I.JNJU

D ; : ним l.hrtWi мши

iJ : »Tic -:■■.! ,: ■ ЩШМй ДОМА

M

;; Uiïï ri-1 iz т! -s

« ¡кии L mm a^ihi- I [ ПММ ythrt ■JlMIÉT Г ИМ 4 :

w ■ ] ■(..::! : wmn jpÉMf -t ПММ -С ИИ

¡1 ti Л 3-ËJ LI

Й 1 ÏTifr] 1 нш ШСЛ IQOHOQ i-raxeo Г «ГШ [ПИП

И ;ïmeî ; ляс 1 .««■}:■ ■. ■:«■»:■ É^ÉÉÉÉt Еопнос ;ши

tz ykm ЮПИ* ПОЛЮ

«

M aMJi'Ai

■ -U4l4ll4 4*>П14 4|НЙ -ni du

« ими 4HT1W Ù/ÉÉÈttl PIH

M ] :>ч: 1ДПП 1ДШК1

h!

b

Рис. 2

Видим, что уже на 12 шаге погрешность становится равной 0,000167 (рис. 1).

МЕТОД ОТРАЖЕНИЙ ДЛЯ РЕШЕНИЯ СИСТЕМ ЛИНЕЙНЫХ АЛГЕБРАИЧЕСКИХ УРАВНЕНИЙ

Рассмотрим метод отражений для ре-

шения системы линейных уравнений

'1 1 1'

0 0 1 x

|VV

1 0 1

А

1

1 1

V У

(1).

Точное решение, очевидно, есть вектор (0,0,1)', где штрих обозначает операцию транспонирования. Компактная схема Гаусса для этой системы неприменима, так как второй угловой минор равен 0. Следующее далее изложение есть комментарий к рис. 2.

В строках 40-48 по шагам показано построение матрицы отражений и1 = Ех - 2 • w1w1/, переводящей вектор

Ух =

1

V У

в вектор ех =

0

V у

. Вычисляем

а

1 = У II = 42, затем вектор w1 =— • (у +ар),

Р

где рх у х +аех ||. Затем с помощью функции МУМНОЖ(массив1;массив2) вычисляем матрицу w1 w1/ (141:Ь43).

Ниже, в строках 55-62, то же показано

для векторов У2

' 0,0000000 Л - 0,707 1 07

0

V у

В строках 64-67 получена окончательная система, обусловленность которой не хуже исходной (рис. 2).

Построенная электронная таблица позволяет изучать зависимость результиру-

ющей расширенной матрицы системы (Л51:Э53) от значений элементов исходной матрицы (Л36:Э38). При этом не накладывается никаких ограничений на матрицу А _. В любом случае результирующая матрица будет приведена к правой диагональной матрице. Если при этом на диагонали окажутся нули, то это свидетельствует о вырожденности исходной системы: либо она несовместна, либо имеет бесконечно много решений. Если же результирующая система не вырождена, то ее решение легко находится для правой диагональной матрицы системы, обусловленность которой не хуже, чем матрица исходной системы.

МЕТОД ПРОСТОЙ ИТЕРАЦИИ ДЛЯ ПРОИЗВОЛЬНОЙ КВАДРАТНОЙ МАТРИЦЫ СИСТЕМЫ

Ниже показан метод простой итерации для той же самой системы (1) (рис. 3)

Матрица этой системы представляется неподходящей для применения данного ме-

Гчл ■>1 Ггатч .1 ч ■ ц-' Г~пи|г

< и ■: ж г у

А В ' С | 0 | Е —р— —Н 1 1 ■ _ "Ь С ы

ГТ1 Мпч Г^--: "Л ЯГЧ

и Л 1 1 1 [■ а |ь I 1 ] 1

н

и 1 ]

ТЬ

Ч ГТ Л]

1 в I П И ] 1 С 1

та

ГС

па ___ А|*Ь

н: Д1'Л 1 1 1 :

и 1 1 ] 1 1 г-

и

ы Е 1 1. 1 3 *

■ ¡¡ЫНЙ

К 1 гз -т т р. Э1М0 Лиопгеш^ тТоЛь ким ( ■бсгаыи^ ■иклк? 15 Г ¡"л.

вг I' 1 (Ъ'||. т-ЬгаЬ -эАттш «см ди^яш ЛГЛ. Ч1М и чоаьгю м^ыг 1

ГГ1 Б 1 ■( 4 11

■ | 1

л У!

■

щ ;

н п'й

к сивз^о гдтмт ШИЧТ 1.7К17П «чзмч

■к

М1Ц:1 1.11 И.Е11

Рис. 3

0

0

и е2 =

тода, так как диагональные элементы явно не доминируют над остальными элементами матрицы. Поэтому итерационное соотношение будем искать в виде

хк+1 = (Е - аА А)хк + аА Ь, (2) где а - число, Л - матрица, транспонированная к матрице А.

С помощью функции ТРАНСП вычисляем матрицу Л, затем с помощью функции МУМНОЖ - матрицу Л А :

ç 2 1 2

AA = 1 1 1

2 1 3

V /

Эта матрица положительно определена: (ЛЛх, х) = (Ах, Ах) > 0, х * 0, Л * 0 • Поэтому ее собственные числа 1. - положительные действительные числа, удовлетворяющие неравенству: 0 <11 < 6 . Здесь правая граница неравенства получена вычис-

лением нормы ||А'А| = 6, где норма ||А|| 1 равна максимуму сумм модулей элементов матрицы А по строкам). Эта оценка несколько завышена, но может быть использована для определения достаточных условий сходимости итерационного процесса. Собственные числа матрицы Е -аА А получаются из собственных чисел матрицы А'А по формуле: т = 1 -а11. Поэтому, если а удовлетворяет условию 0<а< 2/|АА| = 2/6 = 1/3, то собственные числа итерирующей матрицы Е -аА А удовлетворяют условию:

1 -1/3 • 6=-1 <т < 1=1 - 0.

Таким образом, если а удовлетворяет условию

0<а< 1/3 , (3)

то итерационный процесс (2) будет сходящимся.

гУаД»ter^i*""**—

^ ф4и ГНГ-* hl ton |1Ч"Ц-" Очч рввн

LL19 й lä j'-J 141-ТП A -ij Д . y:fl . rt . (lUtl ü4 ЧЧИЧ-*«* ■ n 4 1 i м Ш ЖШ

ПН г fi. Mfi'AIESMI

H а CD E pr 1 f tf 1 1 rn 1 t 1 f T- à

m !& г rj-Лг-.Л

□ ■ V4«f( Л.-.Ч1.Ч1

□ D.ajfM -D.iMir

□ 4JI|D Ф.11 i.ki': :IILI:I

Г 1 1

В I !г'_гиг I. <> ii : Hl r: low . ivimjt |П1пи: v i ■ iT~ д J| * > i T t Li] 11

EJ ft МЯЧ» QfHUT они» -MMHI [уягч ■ÏJftHiî] OLIWHH M ли*: DJÜL-t'l ясечу? 0 1ДО1J [и] 1 "-t.:-

Ш flJTH» [I.MIIH f-JMWlp -CLI юа-i ■ OSHfT i.ll JLH ад« î» pjfrl'. I2Î JJOSfi^P

О 1.1 LOI™ П.И25Ч -I275H 1.1ПЛ-11 IJ lJ(B"Jir OUflQ»! IJ'TI 1 73 M!Mf

га Ii m H 1J I» 1 1 JO JL в F4

□ 0.1 .ч- <i : U.fll 1 г-- ости +м -С'.Г»hj-l■ . IVI ' HS» -ÎJi^YlC ■1 :■»!.' : ■ l'.OOIn 11 С'.'Н.'И'-Г' -D.lH'.I-J

с: Щи jH D.№!U э.иич-ч ö.mun ^<hi21j t LUÎSILH L'.'»M L J -aujm.' jj LÙI ■ ;i Vhbtc

ЦОД* LiifMmi UHUll c.vjLiiT lijdJWj : LL^S? Ù.4SSL 1 г I.ÙLMtl ■JlwidJ Lin™ Î$>44t4

Ii 2b 21 2* H H ]] 3d я J'-

■Ей tuüiüjt 40Н-Ш i-jomj] MMibS им» -JO».!!? 'jDllTHd (l-HîilN [NJUI+Î ■JMOIlL ДОС 13

ш 1 MIHI» -tJMI I1!, +DLLI14 bJW.JTi «»ел 1 здшди OJWÎilH ДОП1 IMJOKi*

m донн п-ч>:и ■.JMHRI ÜMEUlj I.'»] ГЦ ■r.™?:. [ IJMWJj ri.W'.^v шин 1 -ЧЕ-SVfl Г1.М''»"

» jf I' ]P. * « 44- fr

га миги ГУКК.Ч г 1 [*<H11J г. :NH>II ПИУМ1 1 fl.pp: H.1 -a.cto'ùi ШНПН

Е 1 Г'.'Мг.н; -CvM»\ ■ ■j.iU.'JI'l -•irai ni -a.iK.4r-'- l'.4H 1 '4- 'l.WIIU 4.1П11Л

Ш UWIH OJfcT.Hi ЦИЛih l/KKDIt 1.YH.4. 1 ши.чь лy : 1.ТОПЫ 1 DUM!-I

Hh Щ it Si il n Si ■ ~ä И »

ггп 14mm LU>»LJ . доны game OOWLLJ ■1 ншн ЩНИИ L.ljwjrf j LÙÏrH щи щит

га DpOK» ДОНВ ■bj«ИЗЗР giiifr I.-. g H»; и Ù.WJ It LV»J . -0ИИЛ7 JptiWS 0.1»j-, У: 1

га га ЦMW » DlWIi иооогз? ЩИД' ■ SWiS ■ №0117 IJWE^ ■j 1 bÎM1«

га j идти

ГП111И_

Рис. 4

Используя правила построения формул для работы с массивами, построим матрицу В = Е - оАА и вектор свободных членов оА'Ь (рис. 3, строки 94-97). Ниже строим формулы для последовательных приближений. Формулы для 1-го приближения строим, используя абсолютные ссылки на матрицу В и относительные ссылки на вектор х°. Остальные приближения получаем «перетаскиванием» формул 1-го приближения. В качестве нулевого приближения х0 выбираем столбец свободных членов преобразованной системы, элементы которого округляем до 2 десятичных знаков после запятой.

Изучаем скорость сходимости о для различных значений, удовлетворяющих условию (3).

Можно заметить, что сходимость возрастает при приближении к 1/3. Сходимость имеет место и при а = 1/2,7 , но при (0 = 1/2,5 процесс расходится.

Построенная таблица позволяет изучать зависимость сходимости метода простой итерации от элементов исходной матрицы (Л72:С74). При их изменении происходит автоматический пересчет всех вычислений. Для обеспечения сходимости нужно задавать в ячейке В86 значение о< 2/|А'А|1, где норма ||А'А|| автоматически пересчитывается в ячейке 182.

Можно также изучать зависимость сходимости от вектора начального приближения х0. Если |А| Ф 0 , то процесс сходится при любом векторе х0, меняется только число итераций.

Abstract

Examples of instructions for implementation of practical studies on calculus mathematics are considered in this article. During two years by means of such materials the author conducted practical studies on calculus mathematics and theory of probabilities and mathematical statistics with students of «evening» form of education.

© Наши авторы, 2008. Our authors, 2008

Сосновский Николай Николаевич, кандидат технических наук, старший научный сотрудник, доцент кафедры1 высшей математики ВМ-2 СПбГЭТУ «ЛЭТН», [email protected]