엑셀을 이용한 적분

https://myengineeringworld.net/2013/06/integration-trapezoidal-rule.html

Numerical Integration In Excel Using The Trapezoidal Rule - 3 Methods - My Engineering World

 

1st method: Spreadsheet calculations

If n points (x, y) from the curve are known, you can apply the previous equation n-1 times and sum the results. For example, in the sample workbook, we had the function y = 4*x^2; we knew 10 points, so we applied the formula 9 times. For the first point the result was (1 – 0)*(4 + 0)/2 = 2, for the second (2 – 1)*(16 + 4)/2 = 10 and so on. The picture above contains the entire set of calculations.

---

2nd method: SUMPRODUCT formula

With this method, you avoid the intermediate calculations, and by using only one function, you get the result. However, the difficulty level is a little bit higher than the first method (especially if you are new to Excel). The method involves the SUMPRODUCT function, the syntax of which is given below:

SUMPRODUCT(array1, [array2], [array3], …)

The SUMPRODUCT function multiplies the corresponding components in the given arrays and returns the sum of these products. Array1, array2… are the ranges of cells or arrays that you wish to multiply. All arrays must have the same number of rows and columns, and you must enter at least 2 arrays (you can have up to 30 arrays).

The tricky part is the array/range definition. If n curve points (x, y) are known, the function can be written:

In the sample workbook, the SUMPRODUCT function is used with the following ranges:

=SUMPRODUCT(A5:A13-A4:A12;(B5:B13+B4:B12)/2).

In reality, we applied the same function as in method 1, but instead of single cells, we had multiple cells/arrays. The function performs the following calculation:

Without any doubt, the second method is much more straightforward than the first one.

---

3rd method: Custom VBA function

At your Excel file, switch to VBA editor (ALT + F11), go to the menu Insert Module and add the following code lines.

 

 

Option Explicit
Function CurveIntegration(KnownXs As Variant, KnownYs As Variant) As Variant
'---------------------------------------------------------------
'Calculates the area under a curve using the trapezoidal rule.
'KnownXs and KnownYs are the known (x, y) points of the curve.
'Written By:    Christos Samaras
'Date:          12/06/2013
'Last Updated:  21/06/2020
'E-mail:        xristos.samaras@gmail.com
'Site:          https://www.myengineeringworld.net
'---------------------------------------------------------------
'Declaring the necessary variable.
Dim i   As Integer
'Check if the X values belong to a range.
If Not TypeName(KnownXs) = "Range" Then
CurveIntegration = "Xs range is not valid"
Exit Function
End If
'Check if the Y values belong to a range.
If Not TypeName(KnownYs) = "Range" Then
CurveIntegration = "Ys range is not valid"
Exit Function
End If
'Check if the number of X values is equal to the number of Y values.
If KnownXs.Rows.Count <> KnownYs.Rows.Count Then
CurveIntegration = "Number of Xs <> Number of Ys"
Exit Function
End If
'Start with zero.
CurveIntegration = 0
'Loop through all the values.
For i = 1 To KnownXs.Rows.Count - 1
'Check for non-numeric values.
If IsNumeric(KnownXs.Cells(i)) = False Or IsNumeric(KnownXs.Cells(i + 1)) = False _
Or IsNumeric(KnownYs.Cells(i)) = False Or IsNumeric(KnownYs.Cells(i + 1)) = False Then
CurveIntegration = "Non-numeric value in the inputs"
Exit Function
End If
'Apply the trapezoid rule: (y(i+1) + y(i)) * (x(i+1) - x(i)) * 1/2.
'Use the absolute value in case of negative numbers.
CurveIntegration = CurveIntegration + Abs(0.5 * (KnownXs.Cells(i + 1, 1) _
- KnownXs.Cells(i, 1)) * (KnownYs.Cells(i, 1) + KnownYs.Cells(i + 1, 1)))
Next i
End Function