Информатика (стр. 1 )

Федеральное агентство по образованию РФ
Государственное образовательное учреждение
высшего профессионального образования
»Самарский государственный архитектурно-строительный университет»
Кафедра прикладной математики и вычислительной техники

ИНФОРМАТИКА
Решение инженерных задач с использованием электронных таблиц
Microsoft Excel

Учебное пособие

Утверждено редакционно – издательским
советом университета

Самара, 2009

Составители: Радомский ВМ.,

УДК 681.3.06

Информатика. Решение инженерных задач с использованием электронных таблиц Microsoft Excel: учебное пособие / авторы , ; Самарский государственный архитектурно-строительный университет. Самара, 20с.

Приведены алгоритмы решения некоторых инженерных и экономических задач (в частности, задач строительного производства) с использованием электронных таблиц Microsoft Excel. Учебное пособие предназначено для студентов, изучающих дисциплину «Информатика», и соответствует квалификационной характеристике инженера по направлению «Строительство».

Номер лицензии на издательскую деятельность ЛР № 000 от 01.01.01 г.

Данное учебное пособие не может быть полностью или частично воспроизведено, тиражировано (в том числе ксерокопировано) без разрешения авторов.

Редактор

Технический редактор

Корректор

Подписано в печать 7.02.03.

Формат 6084 1/16

Бумага офсетная. Печать оперативная.

Уч.-изд. л. Усл. печ. л.

Тираж 200 экз. Заказ №

©Самарский государственный архитектурно-строительный университет , 2009

  Введение

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

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

MS Excel – мощный профессиональный пакет с большими возможностями, который можно применять для упрощения и автоматизации инженерных и экономических расчетов. Он позволяет хранить в табличной форме большое количество исходных данных, результатов и математических связей между ними и содержит большое количество встроенных функций, использование которых упрощает выполнение математических, статистических и финансовых операций. При изменении исходных данных результаты могут автоматически пересчитываться, что приближает хорошо разработанный лист MS Excel к программному продукту и позволяет решать достаточно сложные задачи, не прибегая к услугам программиста. Он позволяет ставить численный эксперимент и подбирать оптимальные параметры, решать задачи моделирования и проводить анализ результатов, строить диаграммы и графики различного типа, форматировать и красочно оформлять таблицы, подготавливать и распечатывать итоговые документы. MS Excel имеет встроенную программу проверки орфографии и графический редактор, поддерживает связи на уровне импорта и экспорта данных с большинством программ, работающих в операционной среде Windows. Но самым привлекательным свойством MS Excel является доступность. Благодаря простоте и интуитивно-понятному интерфейсу новички быстро осваиваются и начинают эффективно использовать ее для решения своих задач.

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

В основном данное учебное пособие ориентировано на версию MS Office 2003, что, впрочем, не мешает его использованию при работе с другими версиями, поскольку основные концепции и подходы к решению задач слабо зависят от версии используемого программного продукта, а средства, предлагаемые для их решения достаточно универсальны. В основу учебного пособия положена работа [5], исправленная и доработанная.

Автор выражают благодарность студенту 325 группы заочного экономического факультета за помощь в подготовке рукописи к печати.

1.  Назначение и использование электронных таблиц Microsoft Excel для Windows

1.1.  Документ Microsoft Excel

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

Документ MS Excel называется книгой и содержит несколько рабочих листов. По умолчанию новая книга содержит 3 листа. При необходимости пользователь может добавлять листы в книгу.

Основным средством управления является панель главного меню, расположенная, как правило, в верхней части экрана. Набор пунктов меню в совокупности с дополнительными «выпадающими» меню обеспечивают доступ ко всем возможностям программы MS Excel. Для упрощения доступа используются панели инструментов. Непосредственно над рабочей (табличной) областью расположена строка формул, ее удобно использовать для просмотра и редактирования формул.

В нижней части экрана расположены ярлычки листов. Они содержат имена листов, которые можно менять, и удобны для перехода от одного листа к другому в произвольном порядке. Для последовательного перемещения по листам при помощи клавиатуры используется сочетание клавиш «Ctrl-PgDn». Изменение размеров и закрытие окон выполняется стандартным для MS Windows образом.

1.2.  Работа с меню

Меню MS Excel представляет собой совокупность всех команд, используемых при работе с программой и позволяющих максимально полно задействовать возможности пакета. Для удобства пользования все команды сгруппированы по нескольким тематическим блокам – пунктам меню. Меню Excel включает в себя пункты Файл, Правка, Вид, Вставка, Формат, Сервис, Данные, Окно и Справка. Меню MS Excel организовано по принципу иерархической древовидной структуры, т. е. пункт меню может содержать в себе ряд подпунктов. Такой пункт меню, содержащий вложенные подпункты, обозначается треугольником справа от своего названия. При наведении на него курсора мыши открывается меню более низкого уровня. Далее в тексте подробные действия по работе с меню будут описываться в краткой форме, например:

Меню: Сервис®Параметры®Вид®Строка формул.

Следует отметить, что многие команды меню дублированы кнопками на панелях инструментов. Как правило, на экране постоянно установлены две панели: «Стандартная» и «Форматирование», остальные устанавливаются по мере надобности. Наличие на экране лишних панелей инструментов нежелательно, т. к. они загромождают экран и уменьшают рабочее пространство.

1.3.  Строки, столбцы, ячейки

Рабочее поле электронной таблицы состоит из строк и столбцов. Максимальное количество строк равно 16384, столбцов – 256. Каждое пересечение строки и столбца образует ячейку, в которую можно вводить данные (текст, число или формулу).

Номер строки определяет ряд в электронной таблице. Он обозначен на левой границе рабочего поля.

Буква столбца определяет колонку в электронной таблице. Буквы находятся на верхней границе рабочего поля. Колонки нумеруются в следующем порядке: A – Z, затем AA – AZ, BA – BZ и т. д.

Ячейка – первичный элемент таблицы, содержащий данные. Каждая ячейка имеет уникальный адрес, состоящий из буквы столбца и номера строки. Например, адрес B3 определяет ячейку на пересечении столбца B и строки номер 3.

Указатель ячейки – прямоугольник, определяющий текущую ячейку. Его можно перемещать по таблице как при помощи клавиш со стрелками на клавиатуре, так и мышью.

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

Блок представляет собой прямоугольную область смежных ячеек. Блок может включать в себя одну или несколько ячеек, строк или столбцов.

Адрес блока состоит из координат противоположных углов, разделенных двоеточием. Например, B13:C19, A12:D27 или D:F.

Блок можно задать при выполнении различных команд или вводе формул посредством указания координат или выделения мышью на экране.

1.4.  Выделение столбцов, строк, блоков

Для выделения с помощью мыши:

столбца – щелкнуть мышью на букве-имени столбца;

нескольких столбцов – не отпуская кнопку после щелчка, протянуть мышь;

строки – щелкнуть мышью на числе-имени строки;

нескольких строк – не отпуская кнопку после щелчка, протянуть мышь;

блока – щелкнуть мышью на начальной ячейке блока и, не отпуская кнопку, протянуть мышь на последнюю ячейку.

1.5.  Ввод формул

Ввод формул в MS Excel может осуществляться как непосредственно в текущей ячейке, так и в строке формул. При этом необходимо учитывать несколько основных положений.

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

Во-вторых, MS Excel оперирует не именами переменных, а адресами ячеек, поэтому в формуле следует указывать не символьное обозначение какой-либо величины (x, y и т. д.), а адрес ячейки на листе книги MS Excel, в которой расположено заранее присвоенное данной переменной значение.

В-третьих, в формулах при написании адресов ячеек недопустимы русские буквы (символы кириллицы), даже совпадающие с теми или иными буквами латинского алфавита.

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

Формулу, записанную в ячейке MS Excel можно копировать в другую ячейку (команды «копировать» – «вставить») или в группу ячеек. Например, для копирования формулы, написанной в верхней ячейки столбца во все остальные ячейки данного столбца достаточно установить курсор мыши на правый нижний угол ячейки, в которой и записана формула, (при этом курсор приобретает вид тонкого крестика) и, удерживая нажатой левую клавишу мыши, растянуть выделяемую область на нужное количество ячеек, после чего отпустить клавишу мыши.

1.6.  Абсолютная, относительная и смешанная адресация ячеек и
блоков

При написании формул используется два вида адресации: относительная и абсолютная.

Относительная адресация используется по умолчанию и идеально подходит для выполнения табличных расчетов. Адрес формируется как смещение относительно ячейки, в которой записана формула. Например, если в ячейке А1 записана формула =B1+В2, это означает, что в ячейку А1 будет помещена сумма числа, расположенного в той же строке, на одну ячейку правее и числа, расположенного на 1 ячейку правее и на 1 строку ниже. Такая форма адресации позволяет легко копировать формулы для заполнения таблицы. Однако, для человека запись адресов в виде смещений неудобна, поэтому для отображения на экране используется запись в виде адресов, но при изменении настроек листа можно увидеть, как выглядят эти адреса в виде смещений. Таким образом, если вы скопировали формулу =B1+B2 из ячейки А1 в ячейку А2 (т. е на одну строку ниже), она будет выглядеть как =В2+В3, хотя в виде смещений точной копией исходной формулы. Для того, чтобы посмотреть, как выглядят формулы в режиме смещений следует выбрать меню сервис ® параметры ® закладка «Общие» ® стиль ссылок «R1C1».

Абсолютная адресация используется в тех случаях, когда при копировании формулы необходимо сохранить ссылку на конкретную ячейку или область. Для преобразования относительной адресации в абсолютную нужно перед буквой колонки и (или) перед номером ряда напечатать символ «$». Например: $B$4 или $C$2:$F$48 и т. д. При копировании формул, адреса, оформленные как абсолютные, будут выглядеть неизменными. Например, при умножении каждого числа в столбце таблицы на одно и тоже число, записанное в конкретной ячейке, адрес этого числа следует оформить, как абсолютный.

Примечание: для быстрого изменения вида адресации с относительной на абсолютную (и обратно) достаточно установить курсор около адреса, входящего в состав формулы, и нажать клавишу «F4».

Смешанная адресация. Если требуется установить режим абсолютной адресации только для одного из двух элементов адреса (столбца или строки), символ $ ставится только там, где он необходим, например B$4 или $C2. Тогда при копировании один параметр адреса изменяется, а другой нет.

1.7.  Встроенные функции пакета MS Excel

Очень часто в состав формул входят функции. В MS Excel включена библиотека, содержащая около 400 функций. Для удобства поиска функции подразделяются на категории: математические, статистические, логические, финансовые, текстовые, инженерные и т. д. Для упрощения работы с функциями имеется так называемый мастер построения функций (Меню: Вставка®Функция). С его помощью можно вводить в формулу любую функцию, поддерживаемую библиотекой MS Excel. Заметим, что многие часто употребляемые и достаточно простые в написании функции целесообразно вводить прямо с клавиатуры. При этом, разумеется, нужно знать, как та или иная функция записывается в Excel.

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

Аргументы функции заключается в круглые скобки, поэтому необходимо следить, чтобы в формуле число открывающих скобок соответствовало числу закрывающих. Количество аргументов функции может быть ноль, один или несколько. Даже если функция не имеет аргументов, обращение к ней все равно содержит скобки, например, функция «сегодня()», возвращающая в ячейку текущую дату. Если список аргументов более одного, лучше воспользоваться мастером функций, который вызывается автоматически после выбора функции из библиотеки.

Некоторые функции имеют определенные особенности использования, например, для ввода функций, возвращающих не одно числовое значение, а массив значений (например, функции МОБР (вычисление обратной матрицы) или МУМНОЖ (вычисление произведения матриц)), после задания их аргументов следует нажимать не кнопку OK и не клавишу Enter, а одновременно комбинацию клавиш Ctrl-Shift-Enter на клавиатуре. Особенности некоторых функций, описаны в разделах, посвященных, задачам, в которых они используются, в остальных случаях следует пользоваться встроенной помощью (клавиша F1).

1.8.  Построение диаграмм.

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

Для упрощения процесса создания диаграмм MS Excel имеет в своем составе так называемый «Мастер диаграмм» (Меню: ВставкаДиаграмма). Работа мастера позволяет в диалоговом режиме задать основные параметры элементов. Весь процесс построения состоит из четырех шагов, на каждом из которых открывается диалоговое окно, состоящее из нескольких закладок.

На первом шаге определяется тип диаграммы, на втором – диапазон данных для построения диаграммы и некоторые элементы формирования рядов данных и легенды.

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

В одной области построения могут быть сформированы несколько зависимостей, например, две или три функции от одного и того же аргумента в виде графиков разного цвета. Совокупность числовых значений (элементов данных), по которым строится одна диаграмма, т. е. в данном случае график одного цвета называется «рядом». Чаще всего ряды располагаются в столбцах таблицы, но могут быть и в строках. В большинстве случаев мастер за счет заложенной в программе логики правильно определяет положение рядов данных, но, тем не менее, на втором шаге работы с мастером следует проверить правильность сделанного по умолчанию выбора и исправить его в случае необходимости.

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

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

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

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

Редактирование существующей диаграммы.

Следует понимать, что диаграмма состоит из отдельных объектов: оси, ряды и элементы данных, область построения, линии сетки, легенда, надписи, и каждый из них может быть отредактирован. Формы для редактирования вызываются через контекстное меню, привязанное к конкретному элементу или пункт «Диаграмма» верхнего меню, который появляется вместо пункта «Данные», когда выделенным объектом является диаграмма.

1.9.  Специальные средства.

MS Excel является очень мощным инструментом для решения инженерных задач и, кроме стандартных возможностей (формулы, диаграммы и т. д.), предоставляет в распоряжение пользователя ряд специальных средств, знание которых позволяет решать многие задачи гораздо эффективнее. В рамках данного пособия будет показана возможность использования надстроек «Поиск решения» и «Подбор параметра».

«Поиск решения» – это программа, предназначенная для решения простых задач оптимизации. Для формализации условий конкретной задачи служит диалоговое окно, содержащее возможности определения типовых параметров задачи оптимизации:

ü  адрес ячейки, содержащей выражение для вычисления значений целевой функции;

ü  возможность указать направление оптимизации (минимальное, максимальное или конкретное значение целевой функции);

ü  массив адресов варьируемых переменных (заголовок «изменяя ячейки»);

ü  список ограничений;

ü  возможности настройки некоторых параметров решения задачи (точность, количество итераций и т. д.)

«Подбор параметра» – более простое средство, т. к. может подобрать значение только одного параметра для достижения конкретного значения функции. Однако этого может быть вполне достаточно для решения или определения тенденций решения многих задач. В данном пособии, например, показана возможность использования этого средства для решения нелинейного уравнения.

Вызов этих программ: меню: СервисПоиск решения (или Подбор параметра)

Точность решения для «Подбора параметра» определяется точностью вычислений, установленной в настройках MS Excel. Точность решения для «Поиска решений» устанавливается в самой программе – кнопка «параметры» диалогового окна программы «Поиск решения».

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

2.  Методическое руководство к выполнению контрольной работы

2.1.  Решение нелинейных уравнений

Постановка задачи.

Найти корень нелинейного уравнения методом деления отрезка пополам с заданной точностью E

Рассмотрим пример.

Найдем отрицательный корень нелинейного уравнения

0,3x-4,9*x-3,46=0 (1) с точностью E=0,01.

Решение задачи состоит из двух основных этапов:

ü  отделение корня, которое заключается в нахождении на числовой оси Х отрезка, содержащего один корень уравнения;

ü  уточнение корня с учетом точности, заданной в условии задачи.

Этап 1. Отделение одного из отрицательных корней уравнения (1).

Рассчитаем таблицу значений функции F(X)=0,3x-4,9*x-3,46 на произвольно выбранном отрицательном отрезке числовой оси Х.

Для выполнения этой работы на листе MS Excel формируем таблицу расчета значений функции (табулирование функции). Расчетная формула пишется только в ячейке В2, а затем копируется во все остальные ячейки столбца В. Ячейка В2 на рисунке является активной, ее содержимое – формула отражено в строке формул.

Для построения графика функции используется мастер построения диаграмм. Тип диаграммы – «точечная диаграмма». В качестве диапазона исходных данных следует указать ячейки А2:В8, далее следовать указаниям мастера. На диаграмме видно, что в выбранном диапазоне график дважды пересекает ось Х, т. е. уравнение имеет 2 корня, один из отрицательных корней находится на отрезке [-4; -3], а другой – на отрезке [-1; 0]. Для дальнейшего уточнения значения корня необходимо выбрать отрезок на оси Х, содержащий один корень, например отрезок [-4; -3].

Рисунок 1 - Отделение корня уравнения

Этап 2. Уточнение значения корня уравнения, находящегося на отрезке
[-4; -3].

Аналитически условие нахождения хотя бы одного корня уравнения на отрезке [A; B] имеет вид: F(A)*F(B)<0 (т. е. функция F(Х) на данном отрезке меняет знак). Это условие является необходимым и, в случае, когда корень единственный, - достаточным для установления факта наличия корня на отрезке. Для организации дальнейшей работы уточним некоторые условные обозначения:

А – левая граница текущего отрезка числовой оси Х;

В – правая граница текущего отрезка числовой оси Х;

С – середина текущего отрезка числовой оси Х;

D – текущее значение точности вычисления корня уравнения;

F(A) – значение функции в точке А;

F(B) – значение функции в точке В;

F(C) - значение функции в точке С.

Как правило при уточнении корня используются методы последовательных приближений, на каждом шаге значением корня является точка С, координаты которой вычисляются по формуле: . В качестве текущего значения точности принимается длина половины текущего отрезка и вычисляется по формуле: . Признаком окончания расчета является достижение заданной точности, т. е. D=E,

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

Схема уточнения корня методом табулирования функции.

Рисунок 2 – Метод табулирования (режим формул)

Формула для расчета значений функции полностью видна в строке формул, она аналогична той, что мы писали при отделении корня и может быть оттуда скопирована. Шаг вычисляется из расчета, что отрезок делится на 10 частей – это значение произвольное, его можно изменить. Исходными данными для выполнения очередного расчета являются числа в ячейках D21 и Е21. Для перехода к новому (меньшему) отрезку достаточно ввести другие числовые значения в эти ячейки. Например, при выполнении первого расчета таблица будет выглядеть так:

Рисунок 3 – Метод табулирования (режим значений)

Как видно из таблицы корень находится на отрезке [-3,7; -3,6]. Поскольку точность очень низкая, следует выполнить следующее приближение. Для этого достаточно просто внести эти значения в ячейки D21 и Е21. Все остальные значения будут пересчитаны автоматически. Процесс продолжается до достижения нужной точности. При использовании данного алгоритма потребуется 3 итерации. При этом точность будет иметь значение 0,005, значение корня равно -3,627

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

п.1 А=-4; В=-3

п.2 Находим середину отрезка по формуле ,

п.3 Если F(C)0 и F(C) и F(B) имеют разные знаки, то установить A=C и перейти к п. 2.

п.4 Если F(C) и F(A) имеют разные знаки, то установить B=C и перейти к п. 2.

Условие окончания вычислений: D=E,

где D - погрешность вычислений корня нелинейного уравнения.

Для решения задачи в MS Excel можно построить следующий алгоритм расчета:

Рисунок 4 - Метод деления отрезка пополам (режим формул)

В первой строке (не считая заголовка таблицы) в ячейки A4 и C4 заносятся значения левого и правого концов отрезка в первом приближении. Формула для расчета значений функции F(A) (ячейка D4) полностью показана в строке формул. Эту формулу можно скопировать в ячейки Е4 и F4, затем все три формулы скопировать в следующую строку. Формулы в ячейках В4 (вычисление середины отрезка) и G4 (точность) также следует скопировать в следующую строку.

В ячейках А5 и С5 программируется логическая функция «ЕСЛИ» (Меню: ВставкаФункцияЛогические) предназначенная для выбора следующего отрезка. При написании функции «ЕСЛИ» следует использовать мастер построения функций, который вызывается автоматически после выбора функции из библиотеки. Для быстрого вызова библиотеки встроенных функций можно использовать кнопку «fx», расположенную в начале строки формул.

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

Рисунок 5 - Метод деления отрезка пополам (режим значений)

Итерационный процесс обрывается тогда, когда очередное значение в столбце «точность» становится меньшим, чем заданный показатель точности E. При этом значение середины отрезка в последнем приближении (столбец С) принимается в качестве приближенного значения искомого корня нелинейного уравнения.

Итак, одним из двух отрицательных корней нелинейного уравнения 0.3x-4.9x-3.46=0, находимых с точностью E=0,01, является x = –3,63. Как мы видим, он действительно лежит на отрезке [-4; -3].

Вариант 3. Использование надстройки «Подбор параметра» для уточнения значения корня нелинейного уравнения 0.3x-4.9x-3.46=0 .

С помощью этой команды можно определить неизвестное значение, которое будет давать желаемый результат. Команда «Подбор параметра» вызывается через меню «Сервис». Данные, необходимые для решения задачи можно разместить на листе MS Excel следующим образом:

Рисунок 6 - Диалоговое окно команды "Подбор параметра"

В ячейке А2 в качестве начального значения для поиска корня следует записать значение левой границы отрезка, содержащего единственный корень. После нажатия кнопки «ОК» программа найдет и поместит в ячейку А2 значение корня с точностью, установленной в MS Excel (по умолчанию 0.001).

Для поиска второго корня, обнаруженного на отрезке [-1 ; 0] достаточно изменить значение в ячейке А2 на «-1» и еще раз выполнить команду «Подбор параметра».

Значение точности (относительной погрешности) можно посмотреть или изменить в пункте «параметры» меню «Сервис».

2.2.  Численное интегрирование

Формулы численного интегрирования позволяют найти определенный интеграл с заранее заданной точностью.

Постановка задачи. Вычислить определенный интеграл с помощью формул численного интегрирования (формул прямоугольников, трапеций, парабол) с использованием табличного процессора MS Excel. Оценить погрешность каждого из трех указанных выше методов (прямоугольников, трапеций, парабол), разбив отрезок интегрирования на 10 и 20 частей. Сделать выводы по результатам вычислений о пригодности каждого метода.

Относительную погрешность (в процентах) каждого метода определить по формуле:

, где

Iточное – точное значение определенного интеграла, которое, для возможности оценки относительной погрешности выполненных Вами расчетов, включено в задание. Iвычисленное – вычисленное Вами приближенное значение интеграла.

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

Рисунок 7 - Графическая интерпретация определенного интеграла

Отрезок интегрирования [a;b] и вся фигура разбивается на n частей (например, на 10) и площадь криволинейной трапеции вычисляется как сумма площадей получившихся элементов. Каждый элемент приближенно представляется как более простая геометрическая фигура, площадь которой несложно вычислить по формулам, это:

ü  Прямоугольник;

ü  Трапеция (точки Уi и Уi+1 соединяются прямой линией);

ü  Криволинейная парабола (точки Уi и Уi+1 соединяются параболой второго порядка).

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

для метода прямоугольников

,

для метода трапеций

,

для метода парабол

.

Рассмотрим пример.

Дан определенный интеграл . Найдем его приближенное значение по формулам прямоугольников, трапеций и парабол с числом отрезков разбиения отрезка интегрирования n=10 и n=20 (для данного примера шаг h=0.1 и h=0.05 соответственно), используя электронные таблицы MS Excel.

В столбцах A и B построим таблицу значений узловых точек x на отрезке разбиения и значений подынтегральной функции Y=f(xi) в этих точках. Значения и формулы, необходимые для решения задачи можно представить следующим образом:

Рисунок 8 – Расчет значений функции (режим формул)

Обозначения, принятые в примере: i – номер точки (изменяется от 0 до n), Х – текущее значение аргумента, У – значение подинтегральной функции в этой точке, а и b – пределы интегрирования, n – количество интервалов на которые разбивается отрезок [a, b], h – полученная длина отрезка (шаг интегрирования).

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

Таблица 1 - Значения подинтегральной функции

i	X	Y		Пределы и шаг интегрирования
0	0	0		a	b	n	h
1	0,1	0,1		0	1	10	0,1
2	0,2	0,1997
3	0,3	0,2976
4	0,4	0,39
5	0,5	0,4706
6	0,6	0,5312
7	0,7	0,5645
8	0,8	0,5675
9	0,9	0,5434
10	1	0,5

Далее вычисляем значения определенного интеграла тремя способами по приведенным выше формулам.