Как считать интегралы в excel

Интегрирование в MS EXCEL. Метод Симпсона

Вычислим в MS EXCEL определенный интеграл методом Симпсона (англ. Simpson’s rule). Покажем как оценить ошибку интегрирования, построим график функции.

Примечание: Основная статья про численное интегрирование — Интегрирование в MS EXCEL. Метод трапеций. В этой статье дана небольшая теория.

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

Построение модели

Для определенности вычислим интеграл для функции-многочлена f(��)=�� 3 −5�� 2 +6��+1. График этой функции в диапазоне от 0 до 4 выглядит следующим образом (см. файл примера ).

Примечание: про тонкости построения графика функции можно прочитать в этой статье https://excel2.ru/articles/grafik-vs-tochechnaya-diagramma-v-ms-excel.

В файле примера построим таблицу значений функции для 41 точки (от 0 до 40), что составляет 40 интервалов (для метода Симпсона обязательно должно быть ЧЕТНОЕ количество интервалов).

Формула для вычисления интеграла Методом Симпсона следующая:

Примечание: В файле примера вместо Δх (шаг по х) будем использовать символ h, который используется в математических формулах численного интегрирования гораздо чаще, чем Δх. Хотя для первого знакомства, конечно, Δх является более знакомым обозначением шага изменения х.

Как видно из формулы, чтобы вычислить значение интеграла достаточно сложить значения y=f(x) в узлах сетки с определенным весом:

Значения в узлах 0 и n, которые соответствуют пределам интегрирования а и b, берутся с весом 1;
Значения в узлах с нечетным индексом берутся с весом 4;
Значения в узлах с четным индексом берутся с весом 2.

В MS EXCEL вычислить веса по этому правилу можно с помощью простой формулы = ЕСЛИ(ИЛИ(A19=0;A19=$E$12);1; ЕСЛИ(ЕНЕЧЁТ(A19);4;2)) . В файле примера это реализовано в столбце D (дополнительно вес домножен на соответствующее значение y).

В итоге, значение определенного интеграла, вычисленное по методу Симпсона, можно записать простой формулой =СУММ(D19:D59)*E13/3

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

Ошибка интегрирования

Формула для оценки ошибки интегрирования основана на вычислении 4-й (!) производной, что достаточно трудоемко и совсем не удобно для реализации в MS EXCEL.

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

Для более сложных функций нахождение 4-й производной будет трудоемко, но к счастью есть много сайтов, которые помогут в этом вопросе, например https://www.derivative-calculator.net/

Настраиваемый интервал

На листе Настраиваемый интервал сделана удобная форма для вычисления интеграла при различных значениях шага интегрирования. График функции также перестраивается динамически благодаря использованию Имен и функции СМЕЩ() .

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

Лирическое отступление

Зачем оценивать ошибку интегрирования? Можно ведь взять «маленький» шаг сетки и заведомо получить «точный» результат, и не важно что потребуется сделать 1000 или более шагов интегрирования, ведь вычислительные мощности так дешевы!

На этот счет есть 2 замечания: для больших интервалов интегрирования может потребоваться слишком много шагов и если вычисление интеграла лишь часть задачи, да еще и если оно находится в цикле, то это может замедлить работу программы. И второй момент: если мы не знаем ошибки, то как мы можем быть уверены, что вычисленное значение нам подходит? Например, мы вычисляем интеграл, чтобы получить значение, которое мы будем затем сравнивать с неким критерием. Если значение больше критерия, то мы принимаем одно решение, а если нет, то другое. Из-за недостаточной точности вычисления интеграла может случиться, что будет принято неверное решение, что соответственно приведет к некорректной работе программы (в определенной ситуации).

Интегрирование в MS EXCEL. Метод трапеций

Вычислим в MS EXCEL определенный интеграл методом трапеций (англ. Trapezoidal Rule). Оценим ошибку интегрирования, построим график функции.

В интернете есть много сайтов по автоматическому вычислению интегралов аналитическими и численными методами. Но, как правило, про использованный метод численного интегрирования ничего не говорится, а корректность вычислений проверить невозможно. В данном примере все вычисления прозрачны и можно задать необходимое количество интервалов разбиения. Правда, данный метод имеет относительно невысокую точность по сравнению с другими методами (если сравнивать его с методом Симпсона и методом интерполяционного полинома Лагранжа).

Немного теории

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

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

Фактически метод трапеций основан на линейной интерполяции, т.е. график функции y = f(x) представляется в виде ломаной, соединяющей точки (x_i, y_i) прямыми линиями.

Площадь каждой трапеции можно найти следующим образом (см. рисунок ниже).

Фактически задача по нахождению интеграла сводится в основном к построению таблицы значений функции y=f(x) для заданных Х и нахождению их суммы. Интеграл можно найти с помощью вот такой простой формулы:

Построение модели

В файле примера построим таблицу значений функции для 41 точки (от 0 до 40), что составляет 40 интервалов.

Напомним, что Интеграл методом трапеций можно найти с помощью вот такой простой формулы:

В MS EXCEL формула также будет очень простой =E13/2*СУММ(D20:D59).

Примечание: При вводе функции в столбец С нужно быть очень внимательным — ошибиться очень легко, т.к. формула будет выглядеть как месиво символов =B19^3-5*B19^2+6*B19+1.

Вычисленное приближенное значение интеграла для данной функции в интервале [0;4] равно 9,340, а точное 9,333, т.е. ошибка составляет менее 0,1%. Ниже показано как ее оценить.

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

Ошибка вычисления

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

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

К сожалению, для нахождения оценки ошибки потребуется вычислить первую производную. Сделать это чаще всего не сложно, но автоматизировать это в EXCEL не получится. Поэтому при изменении подинтегральной функции приходится вносить изменения в несколько формул на листе, а точнее — в 2 ячейки С19 и G31 (в файле примера они выделены красным). После ввода формул их нужно скопировать вниз.

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

Совет: всегда оценивайте ошибку интегрирования.

Как в Excel вычислить определённый интеграл

Давайте разберёмся, как вычислить определённый интеграл таблично заданной функции с помощью программы Excel из состава Microsoft Office.

1 Инструкция по нахождению определённого интегралав программе Microsoft Excel

1 Постановка физической задачина расчёт определённого интеграла

Допустим, у нас есть таблично заданная некоторая величина. Для примера пусть это будет накопленная доза радиации при авиаперелёте. Скажем, был такой эксперимент: человек с дозиметром летел на самолёте из пункта А в пункт Б и периодически измерял дозиметром мощность дозы (единицы измерений – микрозиверт в час, мкЗв/ч). Возможно, Вас это удивит, но при обычном перелёте на самолёте человек попадает под радиоактивное излучение, превышающее фоновый уровень до 10 раз и даже больше. Но воздействие это кратковременное, и поэтому не столь опасное. По результатам измерений у нас есть таблица вот такого формата: Время – Мощность дозы.

Таблично заданная величина для расчёта определённого интеграла

Необходимо посчитать суммарную накопленную за время полёта дозу.

2 Геометрический смыслопределённого интеграла

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

График изменения мощности дозы во время полёта

3 Методика вычисленияопределённого интеграла

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

Площадь трапеции определяется как полусумма оснований, умноженная на высоту: S_трап = (A + B) / 2 × h Основания в нашем случае – это табличные измеренные значения мощности дозы за 2 последовательных промежутка времени, а высота – это разница времени между двумя измерениями.

Метод трапеций для вычисления значения определённого интеграла

4 Согласованиеединиц измерения

В нашем примере измерения мощности дозы радиации даётся в мкЗв/час, а шкала времени – с точностью до минут. Мы не можем брать интеграл по времени, измеряемому в минутах, для величины, измеряемой в часах. Поэтому необходимо перевести мкЗв/час в мкЗв/мин.

Для перевода просто разделим мощность дозы в мкЗв/час построчно на количество минут в часе, т.е. на 60. Добавим ещё один столбец в нашу таблицу. На иллюстрации это столбец «D». В столбце «D» в строке 2 вписываем =С2/60 А потом с помощью маркера заполнения распространяем эту формулу на все остальные ячейки в столбце «D», (т.е. тянем мышью чёрный прямоугольник в правом нижнем углу ячейки). Таким образом, в столбце «D» у нас появятся значения мощности дозы радиации, измеряемые в микрозивертах в минуту для каждой минуты перелёта.

Согласуем единицы измерения по шкале времени и шкале мощности дозы

5 Вычисление площадей отдельных трапеций

Теперь нужно найти площади трапеций за каждый промежуток времени. В столбце «E» будем вычислять по приведённой выше формуле площади трапеций. Полусумма оснований – это половина суммы двух последовательных мощностей дозы из столбца «D». Так как данные идут с периодом 1 раз в минуту, а мы берём интеграл по времени, выраженному в минутах, то высота каждой трапеции будет равна единице (разница времени между каждыми двумя последовательными измерениями, например, 17ч31мин — 17ч30мин = 0ч1мин = 1мин).

Получаем формулу в ячейке «E3»: =1/2*(D3+D2)*1. Понятно, что «×1» в этой формуле можно не писать. И аналогично, с помощью маркера заполнения, распространяем формулу на весь столбец. Теперь в каждой ячейке столбца «Е» посчитана накопленная доза за 1 минуту полёта.

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

Если бы данные шли не через 1 минуту, то нам нужно было бы написать формулу так:
=1/2*(D3+D2)*(МИНУТЫ(A3) – МИНУТЫ(A2)).
Правда при этом, если есть переход на следующий час, то получится отрицательное значение. Чтобы этого не произошло, впишем в формулу часы:
=1/2*(D3+D2)*(ЧАС(A3)*60+МИНУТЫ(A3)) – (ЧАС(A2)*60+МИНУТЫ(A2)).
Если переходим на следующие сутки, то нужно будет уже добавлять даты, и т.д.

5 Определение площадипод графиком функции

Осталось найти сумму вычисленных площадей трапеций. Можно в ячейке «F2» написать формулу: =СУММ(E:E) Это и будет сумма всех значений в столбце «E», т.е. численное значение искомого определённого интеграла. Но давайте сделаем вот что: определим накопленную дозу в разные моменты полёта. Для этого в ячейку «F4» впишем формулу =СУММ(E$3:E4) и маркером заполнения распространим на весь столбец «F».

Обозначение E$3 говорит программе Excel, что увеличивать индекс ячейки «3» в столбце «E» при переносе формулы на следующие строки не нужно. Т.е. в строке 4 формула будет определять сумму в ячейках с «Е3» по «Е4», в строке 5 – сумму с «Е3» по «Е5», в строке 6 – с «Е3» по «Е6» и т.д.

Построим график по столбцам «F» и «A». Это график изменения накопленной дозы радиации во времени. Наглядно видно монотонное увеличение накопленной дозы радиации за время полёта. Это говорит о том, что мы правильно рассчитали интеграл. И окончательное значение накопленной за двухчасовой полёт дозы радиации, которое получается в последней ячейке этого столбца, равно примерно 4,5 микрозиверт.

Вычисление суммарной площади всех трапеций, что численно равно искомому определённому интегралу

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

Скачать вложения: