Метод трапеций в Excel

Обновлено: 21.11.2024

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

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

Пример — трапециевидная линейка Excel

Функция Excel с трапециевидным правилом

Создавайте мощные макросы с помощью нашего бесплатного комплекта разработчика VBA

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

Учебное пособие. Трапециевидное правило Excel

  1. Учебное пособие по макросам Excel
    1. Открыть редактор Visual Basic для приложений
      Два варианта
      1. Нажмите «Alt+F11»
        или
      2. Нажмите «Visual Basic» на вкладке «Разработчик».
        1. При желании вы можете переименовать модуль в окне свойств.
          Имя по умолчанию — Module1

        В Excel введите =TrapIntegration( KnownXs , KnownYs ) в пустую ячейку, где KnownXs и KnownYs — это диапазоны данных по оси X и оси Y.

        В приведенном выше примере =TrapIntegration(A2:A4,B2:B4) выполняет численное интегрирование данных, чтобы получить результат 3.

        Идеи по применению — трапециевидное правило Excel

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

        Одним из примеров того, как я использовал эту функцию Excel в своей инженерной работе, была аппроксимация интеграла откликов LPRM (Local Power Range Monitor) для атомной электростанции. Я использовал это в качестве альтернативного средства характеристики работоспособности наших лезвий управления — термин, который мы назвали «Ватт-дни».

        На этом уроке все. Когда вы будете готовы вывести свой VBA на новый уровень, подпишитесь, заполнив форму ниже.

        Готовы делать больше с VBA?
        Мы собрали гигантский PDF-файл с более чем 300 готовыми макросами и хотим, чтобы вы получили его бесплатно. Введите свой адрес электронной почты ниже, и мы вышлем вам копию вместе с нашим комплектом разработчика VBA, полным советов, приемов и ярлыков VBA.

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

        Добавить записи в существующую таблицу доступа из Excel с помощью VBA

        Получить информацию об устройстве USB (VBA и WMI)

        3 функции FSO VBA: определение диска, папки и существования файла (+1 бонус)

        Рисование кругов в AutoCAD с использованием Excel и VBA

        сообщить об этом объявлении

        Основы

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

        Согласно Википедии: «Правило трапеций — это метод аппроксимации определенного интеграла:

        Правило трапеций работает путем аппроксимации области под графиком функции f(x) в виде трапеции и вычисления ее площади. Отсюда следует, что:”

        Вычислить площадь под кривой/интеграл функции

        1-й метод: вычисления в электронной таблице

        Если известно n точек (x, y) на кривой, вы можете применить предыдущее уравнение n-1 раз и просуммировать результаты.Например, в примере книги у нас была функция y = 4*x^2; мы знали 10 точек, поэтому применили формулу 9 раз. Для первой точки результат был (1 – 0)*(4 + 0)/2 = 2, для второй (2 – 1)*(16 + 4)/2 = 10 и так далее. На картинке выше представлен весь набор расчетов.

        2-й метод: формула СУММПРОИЗВ

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

        СУММПРОИЗВ(массив1, [массив2], [массив3], …)

        Функция СУММПРОИЗВ умножает соответствующие компоненты в заданных массивах и возвращает сумму этих произведений. Массив1, массив2… — это диапазоны ячеек или массивов, которые вы хотите умножить. Все массивы должны иметь одинаковое количество строк и столбцов, и вы должны ввести как минимум 2 массива (вы можете иметь до 30 массивов).

        Самая сложная часть — это определение массива/диапазона. Если известно n точек кривой (x, y), функция может быть записана:

        В примере рабочей книги функция СУММПРОИЗВ используется со следующими диапазонами:

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

        Без сомнения, второй метод намного проще, чем первый.

        3-й способ: пользовательская функция VBA

        В файле Excel переключитесь в редактор VBA (ALT + F11), перейдите в меню «Вставить модуль» и добавьте следующие строки кода.

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

        Ограничения правила трапеций

        Как было сказано в начале, правило трапеций — это приблизительный метод вычисления площади под кривой/выполнения численного интегрирования. В образце рабочей книги вы заметите, что для конкретной кривой все 3 разных способа, описанных выше, дают одно и то же значение (978). Вы можете подумать, что, поскольку 3 метода согласуются с окончательным значением, это правильный. К сожалению, это не так!

        Точность правила трапеций тесно связана с количеством известных точек кривой. Чем больше точек вы знаете, тем больше трапеций, тем лучше аппроксимация (для определенного диапазона). Если бы в конкретном примере мы знали 20 точек (шаг 0,5) вместо 10 (шаг 1) , то результат с тремя методами будет 973,5.

        Если бы мы знали 901 точку (шаг 0,01), результат был бы около 972, что является правильным. Откуда мы знаем, что 972 правильный? Ответ прост: для конкретного примера кривая была основана на функции y = 4* x^2. Для диапазона x от 0 до 9 интеграл этой функции будет:

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

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

        Загрузки

        Файл можно открыть в Excel 2007 или более поздней версии. Перед использованием включите макросы.

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

        Начнем с вашего графика

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

        Начнем с вашего набора данных

        Далее у нас есть набор данных, с которым мы работаем. В этом случае месяц — это ось X, а продажи — ось Y.

        Создание формулы площади

        1. Введите следующую формулу, которая вычисляет площадь под линией. Как показано ниже, каждая отдельная формула показывает площадь под графиком между точками данных. В этом случае мы вводим ((Значение Y 2) + (Значение Y 1)) / 2 * (Значение X 2 – Значение X 1)

        <р>2. Перетащите эту формулу вниз для данных кроме последней точки данных

        <р>3. В пустой ячейке ниже просуммируйте все отдельные формулы площади, которые вы только что вычислили, чтобы найти площадь всей кривой.

        Конечная область под кривой

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

        Как рассчитать площадь под кривой (правило трапеции) в Google Таблицах

        В этом случае вычисление площади под кривой с использованием правила трапеций в Google Sheets выполняется так же, как и в Excel. Используйте ту же формулу для значений X и Y, и вы сможете рассчитать площадь под кривой.

        Этот тип численного интегрирования в основном используется для экспериментальных данных.

        Это полезно, когда вы хотите увидеть, как некоторый интеграл экспериментальных данных изменяется во времени.

        2. Интеграция с Excel с помощью VBA

        Этот метод лучше всего работает, когда вы хотите интегрировать уравнение с большим количеством точек интегрирования и хотите вернуть только одно значение.

        Его можно настроить с помощью определяемой пользователем функции (UDF).

        Интегралы в Excel: методы расчета

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

        Правило середины в Excel

        Правило средней точки оценивает площадь под кривой как серию чистых прямоугольников (с центром в точке данных).

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

        Лучше не использовать этот метод, если количество точек интеграции ограничено.

        Правило трапеций в Excel

        Правило трапеций оценивает площадь под кривой как ряд трапеций.

        Это значительно повышает точность независимо от изменения подынтегрального выражения.

        По мере увеличения количества точек интеграции результаты этих методов будут сходиться.

        Как интегрировать в Excel: пример проблемы

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

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

        Можно предположить, что объект, который здесь ускоряется, изначально находится в состоянии покоя, поэтому его скорость и положение равны «0» в момент времени t=0.

        Интеграция в Excel для расчета скорости на основе данных об ускорении

        Далее мы можем рассчитать скорость. Мы знаем, что в общем случае скорость связана с ускорением следующим уравнением:

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

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

        В ячейку C5 (первое значение скорости после начальной скорости, 0, которую мы ввели выше) введите формулу для расчета площади трапеции под кривой.

        =(A5–A4)*((B4+B5)/2)+C4

        Заполнение этой формулы до конца дает нам следующий результат скорости:

        Результат скорости имеет смысл, учитывая данные об ускорении.У нас есть область постепенного увеличения скорости от 0 до 0,1 секунды. Увеличение скорости с разной скоростью от 0,1 до ~0,45 секунды и от ~0,45 до 0,7 секунды. И постоянная скорость (нулевое ускорение) от 0,7 до 1 секунды.

        Интеграция с Excel для расчета позиции по скорости

        Теперь мы можем перейти к данным о позиции.

        Мы введем ту же формулу для площади трапеции под кривой скорости, чтобы вычислить положение:

        =(A5–A4)*((C4+C5)/2)+D4

        Еще раз заполним это уравнение до конца, чтобы получить положение как функцию времени:

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

        Читайте также: