Вычисление интегралов в Excel. Метод Симпсона.

Опубликовано 10 Авг 2015
Рубрика: Справочник Excel | 13 комментариев

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

Формулировка в предыдущем предложении определяет сущность понятия интегрирования.

Интеграл чего-либо – это сумма всех малых частей этого чего-либо. Чем больше количество этих малых частей, тем точнее значение интеграла соответствует действительности, определяя признак изучаемого объекта.

Интегрирование применимо для изучения свойств физических и философских объектов при условии, что эти свойства остаются неизменными как для «мелкой» части, так и для всего объекта в целом.

Функция – это описание зависимости некоторого признака или свойства объекта от аргумента.

Например:

Объект – плоская фигура между графиком функции и осью абсцисс.

Признак (значение функции) – высота фигуры.

Аргумент (независимая переменная) – ширина фигуры.

Функция – описание зависимости высоты от ширины.

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

Теория.

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

Все, что требуется для понимания и применения метода трапеций и метода Симпсона на практике представлено далее на рисунке.

Площадь под кривой y = f (x) разбиваем на n-1 криволинейных трапеций, у которых три стороны – это прямые линии, а одна сторона – участок кривой y=f (x). Суммарная площадь под графиком функции на участке от x₁ до x_n – это и есть искомая величина, которая является определенным интегралом функции на этом участке и находится как сумма площадей всех криволинейных трапеций.

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

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

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

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

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

1. Разбить площадь на четное количество частей, то есть n должно быть нечетным числом!

2. Расстояния между точками по оси x должны быть одинаковыми!

Практика вычисления интегралов в Excel.

Определенной сложностью является связать вычисление интегралов с реальными задачами из жизни. Рассмотрение примеров – лучший способ устранения подобных препятствий.

Определение тепловой энергии.

Мой знакомый из города Улан-Удэ Алексей Пыкин проводит испытания  воздушных солнечных PCM-коллекторов производства КНР. Воздух из помещения подается вентилятором в коллекторы, нагревается от солнца и поступает назад в помещение. Каждую минуту измеряется и записывается температура воздуха на входе в коллекторы и на выходе при постоянном воздушном потоке. Требуется определить количество тепловой энергии полученной в течение суток.

Более подробно о преобразовании солнечной энергии в тепловую и электрическую и об экспериментах Алексея я постараюсь рассказать в отдельной статье. Многим, я думаю, это будет интересно.

Запускаем MS Excel и начинаем работу – выполняем вычисление интеграла.

Заполним таблицу.

1. В столбец B вписываем время проведения измерения τ_i.

2. В столбец C заносим температуры нагретого воздуха t_2i, измеренные на выходе из коллекторов в градусах Цельсия.

3. В столбец D записываем температуры холодного воздуха t_1i, поступающего на вход коллекторов.

4. В столбце E вычисляем разности температур dt_i на выходе и входе

dt_i=t_2i-t_1i

5. Зная удельную теплоемкость воздуха c=1005 Дж/(кг*К) и его постоянный массовый расход (измеренная производительность вентилятора) G=0,02031 кг/с, определяем мощность установки N_iв КВт в каждый из моментов времени в столбце F

N_i=c*G*dt_i

На графике ниже показана экспериментальная кривая зависимости мощности, развиваемой коллекторами, от времени.

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

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

Q_i=(N_i+1+N_i)*(τ_i+1-τ_i)/2

Q=ΣQ_i=10,395 КВт*час

7. Рассчитываем в ячейках столбца H элементарные площади по методу парабол, суммируем их и находим общее количество энергии по методу Симпсона

Q_j=(N_i+4*N_i+1+N_i+2)*(τ_i+1-τ_i)/3

Q=ΣQ_j=10,395 КВт*час

Как видим, значения не отличаются друг от друга. Оба метода демонстрируют одинаковые результаты!

Исходная таблица содержит 421 строку. Давайте уменьшим её в 30 раз и оставим всего 15 строк, увеличив тем самым интервалы между замерами с 1 минуты до 30 минут.

По методу трапеций: Q=10,220 КВт*час (-1,684%)

По методу Симпсона: Q=10,309 КВт*час (-0,827%)

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

Общие выводы.

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

Так как мы существуем в пространстве и времени, то и всё окружающее нас изменяется или в пространстве или во времени. Это означает, что аргументом x функций y интересующих нас процессов или объектов чаще всего являются длина или время. Например, пройденный путь – это интеграл функции скорости (аргумент – время), площадь плотины – это интеграл функции высоты (аргумент – длина), и т.д.

Понимание сути интегрального исчисления и умение использовать его на практике вооружает вас, как специалиста, мощным оружием в осознанном изучении окружающего мира!

Ссылка на скачивание файла с примером: vychisleniye-integralov (xls 216,0KB).

Другие статьи автора блога

На главную

Статьи с близкой тематикой

Отзывы

Численное вычисление интегралов



Численное вычисление интегралов  5.3. Численное вычисление определенных интегралов

 Технология приближенного вычисления

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

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

Пример 19.

Требуется вычислить определенный интеграл      Величина интеграла, вычисленная аналитически, равна 9.

Решение:

1. Табулируйте подинтегральную функцию в диапазоне изменения значений аргумента 0 – 3 с шагом 0,2 (рис. 30)

 Рисунок 30

2. В ячейку С2 введите формулу = (A3-A2)*B2+(A3-A2)*(B3-B2)/2, которая реализует часть приведенной выше формулы, размещенной правее знака суммы, т.е вычисляет величину элементарной площадки (трапеции).

3. Скопируйте буксировкой формулу, записанную в ячейке С2 до значения ар-гумента х = 2,8.

4. В ячейке С17 просуммируйте с помощью автосуммирования полученные ре-зультаты. Вычисленное значение в ячейке С17 и будет величиной интеграла — 9.

Технология точного вычисления

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

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

2.  Выполним команду меню Сервис ð Параметры, откроем закладку Вычисления в диалоговом окне Параметры и в поле Предельное число итераций введем число 10000. Если установлен флажок Итерации, то выключим его. Закроем диалоговое окно Параметры.

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

Рис. 31

В ячейке В6 формула =(B4-B2)/B5 вычисляет шаг интегрирования. В ячейке С3 формула = 0+C3+B6 – вычисляет текущее значение аргумента х. Значение 0 в формуле устанавливает нижний предел интегрирования. В формуле есть циклическая ссылка на эту же ячейку  — С3 +В6, она реализует накопление величины х относительно нижнего предела.

В ячейке D3 записана формула, реализующая метод трапеций и накопление суммы площадей элементарных трапеций.

4.  После ввода исходных данных и формул вновь выполним команду меню Сервис ð Параметры, откроем закладку Вычисления в диалоговом окне Параметры и установим флажок Итерации. Щелкнем на кнопке ОК. Потребуется некоторое время для того, чтобы табличный процессор выполнил заданное количество циклов итераций и вычислил результат (рис. 44).

5.  После завершения вычислений вновь вызовем диалоговое окно Параметры и выключим флажок Предельное число итераций.

  К предыдущей    К следующей    Открыть содержание темы

Функция интеграции с Excel

Функция интеграции с Excel

Синтаксис

Свернуть все

Описание

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

f(x) , если применимо.

∫abfx⋅dx

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

∫ab∫ly(x)uy(x)∫lz(x,y)uz(x,y)fx,y,z dzdydx

Входы

Требуемые входные данные

f ссылка на формулу под интегралом.

Если подынтегральная функция слишком сложна для представления формулами, вы можете закодировать ее в функции VBA (см. пример 6).

x ссылка на переменную интегрирования.

a нижний предел интеграла.

b верхний предел интеграла.

Для ввода бесконечных пределов используйте строку «INF» или «-INF» (без учета регистра).

Дополнительные входы

ctrl набор пар ключ/значение для алгоритмического управления, как описано ниже.

Описание пар ключ/значение для алгоритмического управления

Выбор алгоритма интегрирования

Key	ALGOR
Admissible Values ​​(String)	QNG,QAG,QAGS,QK15,QK21,QK31,QK41,QK51,QK61
Default Value	QAG
Remarks	Если заданы особые точки, алгоритм по умолчанию использует внутренний QAGP Если указан бесконечный предел, алгоритм по умолчанию использует внутренний QAGI

Абсолютные и относительные допуски погрешности

3 3 3

Ключи	ATOL, RTOL
допустимых значений (Реал)	> = 0
По умолчанию	ATOL = 1. 0E-7, RTOL = 1,062
Результат сообщается, когда выполняется либо ATOL, либо RTOL. Для соответствия ATOL специально установите RTOL на ноль и наоборот. ATOL и RTOL не могут быть установлены на ноль одновременно. ATOL и RTOL не применяются для фиксированного заказа QKn

Верхний предел количества разделов интервала интегрирования

0 40 5090 0 4 0 5 9 0 0 9 0 0 0 5

Ключ	MAXDIV
Допустимых значений (Integer)	> = 1
DEFAUT Правило интегрирования Гаусса-Кронрода 9001 Key GKPAIR Admissible Values ​​(Integer) (7-15) (10-21) (15-31) (20-41) (25 -51) (30-61) Значение по умолчанию 2 ЗАМЕЧАНИЯ Применимо только для Algorithm QAG . Примеры Свернуть все примеры Пример 1: Интегрирование рационального многочлена ∫122⁢x5-x+3×2⋅dx=9-ln2 Решение Мы определяем формулу подынтегральной функции в A1 , используя X1 в качестве переменной. Excel сообщает об ошибке #DIV/0! для формулы A1 с 92 2 =QUADF(A1,X1,1,2) ⇒ А 1 #ДЕЛ/0! 2 8.306852819 Примечание для пользователей ExceLab 365. Определите любое ненулевое значение для пустого X1 , чтобы разрешить начальный #DIV/0! ошибка в A1 или Excel не будет выполнять зависимую формулу в A2 . Не требуется для надстройки ExceLab 7 или Google Таблиц Пример 2: Интегрирование функции с делением на ноль ∫01lnxx⋅dx=-4 Решение Мы определяем формулу подынтегральной функции в A3 , используя X1 в качестве переменной. Excel сообщает об ошибке #ЧИСЛО! для формулы A1 поскольку X1 не определен. Эту ошибку можно игнорировать с X1 служит только фиктивной переменной для подынтегральной функции, и ее значение равно не имеет значения для интегрирования, вычисленного в A4 . Мы используем QUADF дополнительный параметр номер 5, чтобы указать алгоритм QAGS для повышения точности, учитывая наличие сингулярности в точке 0. A 3 =LN(X1)/SQRT(X1) 4 =QUADF(A3,X1,0,1,{«609}»,»90S09″,»90S00″,»90S09″ ⇒ A 3 #ЧИСЛО! 4 -4 Примечание для пользователей ExceLab 365. Определите любое положительное значение для пустого X1 , чтобы разрешить начальное значение #ЧИСЛО! ошибка в A3 или Excel не будет выполнять зависимую формулу в A4 . Не требуется для надстройки ExceLab 7 или Google Таблиц Пример 3: Интегрирование функции с известными особенностями ∫01|x-17|-17⋅|x-23|-1120⋅dx Раствор Подынтегральная функция в этом примере имеет две особенности при 17 и 1120. Мы используем необязательный аргумент 6 для передачи особых точек, определенных в векторе 9.(-11/20)	=1/7
6	=QUADF(A5,X1,0,1,B5:B6)	=11/20

	A
5	2,032940662
6	4,253687688

9 4,253687688

44. точность округления 1/7. 92) 8 =QUADF(A7,X1,0,»inf»)

⇒

	А
7	1
8	1.570796327 3 Пример 5: Несобственный интеграл с двумя бесконечными пределами ∫-∞∞e-x2⋅dx=π Решение A 9 =EXP(-X1*X1) 10 =QUADF(A9,X1,»-inf»,»inf») ⇒ А 9 1 10 1.772453851 9006 Пример 6: Интеграция определяемой пользователем функции VBA Мы демонстрируем, как интегрировать определяемую пользователем функцию VBA с КВАДФ . Вы можете определить свои собственные функции VBA в Excel, что очень удобно, когда подынтегральное выражение трудно определить с помощью стандартных формул. Мы вычислим интеграл ∫12log(x+p)⋅dx, где p — постоянный параметр. VBA поддерживается только в ExceLab 7.0. ExceLab 365, основанный на кроссплатформенной технологии Office JS, несовместим с VBA 9.0005 Решение Откройте Excel и запустите редактор VBA, нажав Alt+F11 Вставьте модуль с вкладки «Вставка», затем закодируйте следующую функцию: Сохраните книгу с расширением xlsm (с включенным макросом) и закройте редактор VBA В Sheet1 определите свою формулу подынтегрального выражения, как показано в A11 . Мы передаем переменную интегрирования и любые другие параметры. ожидает функция VBA. А 11 =vbMyFunction(X1, 1) 12 =QXUADF1(A12,3) 9,0063 0066 ⇒ A 11 0 12 0,909542505 . Ваши функции vba должны быть использованы с использованием с помощью «Способность» с использованием с помощью «vbab with» с использованием. X1 — это просто фиктивная переменная для подынтегральной функции. Его значение игнорируется. Алгоритмы Алгоритмы QUADF основаны на пакете численного интегрирования QUADPACK. Алгоритм QUADF по умолчанию — QAG. Вы можете переопределить алгоритм по умолчанию в необязательном аргументе 5 с помощью ключа ALGOR Например: =QUADF(f, x, a, b, {"algor","qags"}) . QNG Неадаптивный алгоритм, использующий фиксированные абсциссы Гаусса-Кронрода-Паттерсона для выборки подынтегральной функции максимум в 87 точках. Он подходит для быстрой интеграции гладких функций. КАГ Алгоритм адаптивного интегрирования, который делит область интегрирования на подинтервалы, и на каждой итерации подинтервал с наибольшей оценочной ошибкой делится пополам. Это быстро уменьшает общую ошибку, поскольку подинтервалы концентрируются вокруг локальных трудностей в подынтегральном выражении. Правило интеграции можно задать с помощью пары ключ/значение GKPAIR. КАГС Объединяет QAG с эпсилон-алгоритмом Винна для ускорения интеграции многих типов интегрируемых сингулярностей. Он использует правило Гаусса-Кронрода с 21 точкой. КАГП Применяет QAGS с учетом введенных пользователем положений особых точек. КАГИ Используется для неправильных интегралов. Интеграл отображается в полуоткрытый интервал (0,1] с помощью преобразования x = (1-t)/t. Затем он интегрируется с использованием алгоритма QAGS с использованием 15-точечного правила Гаусса-Кронрода QKn Процедуры интегрирования Гаусса-Лежандра с фиксированным порядком предназначены для быстрого интегрирования гладких функций. Правило Гаусса-Лежандра с n точками точно для многочленов порядка 2 * n-1 или меньше. Доступны правила для n = 15, 21, 31, 41, 51, 61. (например, QK21.) Ссылки Р. Писсенс, Э. де Донкер-Капенга, К.В. Юберхубер, Д.К. Каханер. QUADPACK Пакет подпрограмм для автоматической интеграции. Springer Verlag, 1983. Как вычислять интегралы в Excel Как вычислять интегралы в Excel Интегрирование формул и функций Используйте QUADF для вычисления правильного или неправильного интеграла любой формулы или определяемой пользователем функции VBA с использованием высокоточных адаптивных алгоритмов. Интеграция наборов дискретных точек данных Используйте QUADXY для интегрирования дискретного набора из (x,y) точек данных с использованием линейных или кубических сплайнов. Вычисление кратных интегралов Nest QUADF для вычисления нескольких интегралов любого порядка с индивидуальным управлением каждым вложенным интегралом, например выбором алгоритма. Иллюстрации к видео Как вычислять числовые интегралы с помощью QUADF() Описание В этом видеоролике показано, как использовать функции интеграции QUADF для вычисления точных интегралов в Excel и Google Sheets. Решается несколько примеров, включая фиксированные пределы, бесконечные пределы и особые точки. Видео также демонстрирует, как вложить QUADF для вычисления нескольких интегралов любого порядка. Как вычислить интеграл графа (x, y) с помощью QUADXY() Описание В этом видеоролике показано, как использовать функцию QUADXY для вычисления точного интеграла графика, сгенерированного из дискретных (x, y) наборов данных в Excel и Google Sheets с помощью сплайнов. Добавить комментарий Отменить ответ Ваш адрес email не будет опубликован. Обязательные поля помечены * Комментарий * Имя * Email * Сайт