Вычисление интегралов в Excel. Метод Симпсона.
Опубликовано 10 Авг 2015
Рубрика: Справочник Excel | 13 комментариев
При стремлении постичь нечто сложное, громоздкое, непонятное следует «разбить» его на как можно большее количество простых, мелких, понятных частей, изучить их с помощью существующих инструментов, а затем «сложить» эти результаты и получить итоговый ответ.
Формулировка в предыдущем предложении определяет сущность понятия интегрирования.
Интеграл чего-либо – это сумма всех малых частей этого чего-либо. Чем больше количество этих малых частей, тем точнее значение интеграла соответствует действительности, определяя признак изучаемого объекта.
Интегрирование применимо для изучения свойств физических и философских объектов при условии, что эти свойства остаются неизменными как для «мелкой» части, так и для всего объекта в целом.
Функция – это описание зависимости некоторого признака или свойства объекта от аргумента.
Например:
Объект – плоская фигура между графиком функции и осью абсцисс.
Признак (значение функции) – высота фигуры.
Аргумент (независимая переменная) – ширина фигуры.
Функция – описание зависимости высоты от ширины.
Определенный интеграл функции – площадь фигуры. Площадь тоже является признаком фигуры, но зависит от двух переменных – высоты и ширины – и представляет собой качественно иной новый признак.
Теория.
Подробно рассмотрим два наиболее точных метода численного интегрирования функции одной переменной – метод трапеций и метод парабол или метод Симпсона. Есть еще метод прямоугольников, но мы его проигнорируем из-за невысокой точности.
Все, что требуется для понимания и применения метода трапеций и метода Симпсона на практике представлено далее на рисунке.
Площадь под кривой y = f (x) разбиваем на n-1 криволинейных трапеций, у которых три стороны – это прямые линии, а одна сторона – участок кривой y=f (x). Суммарная площадь под графиком функции на участке от x1 до xn – это и есть искомая величина, которая является определенным интегралом функции на этом участке и находится как сумма площадей всех криволинейных трапеций.
Точно вычислить аналитически площадь криволинейной трапеции бывает сложно или даже невозможно.
Для приближенного вычисления площади криволинейной трапеции можно заменить участок кривой прямой линией и, получив простую фигуру – обычную трапецию, найти по известной формуле ее площадь. В этом суть метода трапеций.
Если участок кривой линии над двумя криволинейными трапециями заменить параболой, проведенной через три характерные точки, то получим новую криволинейную трапецию с одной из сторон в виде параболы. Количество новых фигур будет в два раза меньше, чем количество исходных трапеций. Площадь этих новых фигур вычисляется по простой формуле. В этом смысл метода Симпсона.
Идею замены участка любой кривой участком параболы высказывал Исаак Ньютон, но первым вывел формулу английский математик Томас Симпсон. Метод Симпсона для вычисления интегралов является самым точным из приближенных численных методов.
Если вычисление интегралов методом трапеций не имеет ограничений, то для того, чтобы реализовать метод Симпсона необходимо выполнить два условия.
1. Разбить площадь на четное количество частей, то есть n должно быть нечетным числом!
2. Расстояния между точками по оси x должны быть одинаковыми!
Практика вычисления интегралов в Excel.
Определенной сложностью является связать вычисление интегралов с реальными задачами из жизни. Рассмотрение примеров – лучший способ устранения подобных препятствий.
Определение тепловой энергии.
Мой знакомый из города Улан-Удэ Алексей Пыкин проводит испытания воздушных солнечных PCM-коллекторов производства КНР. Воздух из помещения подается вентилятором в коллекторы, нагревается от солнца и поступает назад в помещение. Каждую минуту измеряется и записывается температура воздуха на входе в коллекторы и на выходе при постоянном воздушном потоке. Требуется определить количество тепловой энергии полученной в течение суток.
Более подробно о преобразовании солнечной энергии в тепловую и электрическую и об экспериментах Алексея я постараюсь рассказать в отдельной статье.
Многим, я думаю, это будет интересно.
Запускаем MS Excel и начинаем работу – выполняем вычисление интеграла.
Заполним таблицу.
1. В столбец B вписываем время проведения измерения τi.
2. В столбец C заносим температуры нагретого воздуха t2i, измеренные на выходе из коллекторов в градусах Цельсия.
3. В столбец D записываем температуры холодного воздуха t1i, поступающего на вход коллекторов.
4. В столбце E вычисляем разности температур dti на выходе и входе
dti=t2i-t1i
5. Зная удельную теплоемкость воздуха c=1005 Дж/(кг*К) и его постоянный массовый расход (измеренная производительность вентилятора) G=0,02031 кг/с, определяем мощность установки Niв КВт в каждый из моментов времени в столбце F
Ni=c*G*dti
На графике ниже показана экспериментальная кривая зависимости мощности, развиваемой коллекторами, от времени.
Количество тепловой энергии, выработанной за промежуток времени – это интеграл этой функции, и значение интеграла – это заштрихованная площадь под кривой.
6. Вычисляем в ячейках столбца G площади трапеций, суммируем их и находим общее количество энергии, выработанной за день
Qi=(Ni+1+Ni)*(τi+1-τi)/2
Q=ΣQi=10,395 КВт*час
7. Рассчитываем в ячейках столбца H элементарные площади по методу парабол, суммируем их и находим общее количество энергии по методу Симпсона
Qj=(Ni+4*Ni+1+Ni+2)*(τi+1-τi)/3
Q=ΣQj=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).
Другие статьи автора блога
На главную
Статьи с близкой тематикой
Отзывы
Интегрирование в MS EXCEL. Метод трапеций
history 20 ноября 2022 г.
- Группы статей
file_download Файл примера
Вычислим в MS EXCEL определенный интеграл методом трапеций (англ. Trapezoidal Rule). Оценим ошибку интегрирования, построим график функции.
В интернете есть много сайтов по автоматическому вычислению интегралов аналитическими и численными методами. Но, как правило, про использованный метод численного интегрирования ничего не говорится, а корректность вычислений проверить невозможно. В данном примере все вычисления прозрачны и можно задать необходимое количество интервалов разбиения. Правда, данный метод имеет относительно невысокую точность по сравнению с другими методами (если сравнивать его с методом Симпсона и методом интерполяционного полинома Лагранжа).
Так как функция, стоящая под знаком интеграла в общем случае может быть любая, то значение интеграла не всегда можно вычислить аналитически.
Однако, можно воспользоваться тем фактом, что согласно теории, значение интеграла численно равно площади фигуры образованной графиком функции и осью Х (фигура выделена цветом).
Таким образом, задача нахождения интеграла сводится к нахождению площади этой фигуры. Площадь фигуры в общем случае можно найти численными методами, разбивая ее на простые однотипные фигуры, например трапеции. Т.к. площадь каждой трапеции найти легко, то простым суммированием площадей можно найти и интеграл. Платой за универсальность является ошибка интегрирования, которую впрочем можно оценить (будет показано далее).
Фактически метод трапеций основан на линейной интерполяции, т.е. график функции y = f(x) представляется в виде ломаной, соединяющей точки (xi, yi) прямыми линиями.
Площадь каждой трапеции можно найти следующим образом (см. рисунок ниже).
Фактически задача по нахождению интеграла сводится в основном к построению таблицы значений функции y=f(x) для заданных Х и нахождению их суммы._m.jpg)
2+6*B19+1.
Вычисленное приближенное значение интеграла для данной функции в интервале [0;4] равно 9,340, а точное 9,333, т.е. ошибка составляет менее 0,1%. Ниже показано как ее оценить.
В файле примера на листе «настраиваемый интервал» сделана форма для работы с разными количествами интервалов разбиения. При изменении количества интервалов график перестраивается автоматически, формулы для вычисления интеграла не нужно переписывать (как, впрочем, и расширять/ убавлять таблицу значений).
На рисунке ниже показано откуда появляется ошибка интегрирования. Для первых 2-х трапеций (образованы красными линиями) площадь меньше чем у истинной функции (синяя линия). Для следующих 2-х — площадь больше. Из этого следует, что метод трапеций хорошо работает для осциллирующих функций, когда ошибки компенсируют друг друга.
Простой многочлен был выбран в качестве демонстрационной функции, чтобы можно было вычислить интеграл точно и потом найти истинную ошибку, чтобы иметь возможность сравнить ее с оценкой.
К сожалению, для нахождения оценки ошибки потребуется вычислить первую производную. Сделать это чаще всего не сложно, но автоматизировать это в EXCEL не получится. Поэтому при изменении подинтегральной функции приходится вносить изменения в несколько формул на листе, а точнее — в 2 ячейки С19 и G31 (в файле примера они выделены красным). После ввода формул их нужно скопировать вниз.
В наем случае полученная оценка ошибки совпала с истинной ошибкой, что говорит о том что мы не ошиблись при вычислении производной и вводе формул на лист.
Совет: всегда оценивайте ошибку интегрирования.
Функция интеграции с Excel
Функция интеграции с ExcelСинтаксис
Свернуть все
Описание
Используйте QUADF для вычисления правильного или неправильного интеграла.
с использованием высокоточных адаптивных алгоритмов.
С необязательными аргументами вы можете переопределить алгоритм интегрирования по умолчанию, а также указать особые точки для подынтегральной функции f(x) , если применимо.
∫abfx⋅dx
QUADF может быть вложен для вычисления нескольких интегралов любого порядка.
∫ab∫ly(x)uy(x)∫lz(x,y)uz(x,y)fx,y,z dzdydx
Входы
Требуемые входные данные
Если подынтегральная функция слишком сложна для представления формулами, вы можете закодировать ее в функции 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 |
|
Абсолютные и относительные допуски погрешности
| Ключи | ATOL, RTOL |
| Допустимых значений (Реал) | > = 0 |
| По умолчанию | ATOL = 1. 0E-7, RTOL = 1,062 |
|
Верхний предел количества разделов интервала интегрирования
| Ключ | MAXDIV | |||||||||||||||||||||||||||||
| Допустимых значений (Integer) | > = 1 | |||||||||||||||||||||||||||||
| DEFAUT Правило интегрирования Гаусса-Кронрода
⇒
Примечание для пользователей ExceLab 365. Определите любое ненулевое значение для пустого X1 , чтобы разрешить начальный #DIV/0! ошибка в A1 или Excel не будет выполнять зависимую формулу в A2 . Пример 2: Интегрирование функции с делением на ноль ∫01lnxx⋅dx=-4 Решение
⇒
Примечание для пользователей ExceLab 365. Пример 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 |
Не требуется для надстройки ExceLab 7 или Google Таблиц
Определите любое положительное значение для пустого X1 , чтобы разрешить начальное значение #ЧИСЛО! ошибка в A3 или Excel не будет выполнять зависимую формулу в A4 . Не требуется для надстройки ExceLab 7 или Google Таблиц| A | |
| 5 | 2,032940662 |
| 6 | 4,253687688 |
2536876887687688
4. точность округления 1/7. 92)
⇒
| А | ||||||||||||||||||||||||||
| 7 | 1 | |||||||||||||||||||||||||
| 8 | 1.570796327 3 Пример 5: Несобственный интеграл с двумя бесконечными пределами ∫-∞∞e-x2⋅dx=π Решение
⇒
|
Вы можете определить свои собственные функции VBA в Excel, что очень удобно, когда подынтегральное выражение трудно определить с помощью стандартных формул. Мы вычислим интеграл
Мы передаем переменную интегрирования и любые другие параметры.
ожидает функция VBA.
Он использует правило Гаусса-Кронрода с 21 точкой.
Писсенс, Э. де Донкер-Капенга, К.В. Юберхубер, Д.К. Каханер. QUADPACK Пакет подпрограмм для автоматической интеграции. Springer Verlag, 1983.
Решается несколько примеров, включая фиксированные пределы, бесконечные пределы и особые точки. Видео также демонстрирует, как вложить QUADF для вычисления нескольких интегралов любого порядка.