Calculate points with Excel? How does it work?

In Excel, only the function with the integral symbol can be displayed, but the integration operation cannot be realized.

Display functions can be displayed using insert formulas.

Professional formulator can have all the formula functions. It can be used in Word, Excel, or PowerPoint.

Exit the formulator to see the full formula.

At the same time, it should be noted that the formulator is an option of office during installation, and if this item is not selected during installation, formulas cannot be performed. If you encounter a situation where there is no installation, you can solve it by changing the installation of Office again.

Integration is the inverse of differentiation, that is, knowing the derivative of the function, the original function is reversed. In terms of application, the integral effect is not only that, it is widely used in summation, in layman's terms, to find the area of the curved triangle, this ingenious solution method is determined by the special properties of the integral.

1 public function trapezoidal integral (r as string, a as double, b as double, n as integer) as double 2 dim dx as double, i as integer 3 dx = (b - a) n 4 for i = 1 to n 5 trapezoidal integral = trapezoidal integral + fx(r, (a + dx * i - 1 2))))dx 6 next i 7 end function 8 public function Compound Simpsen integral (r as string, a as double, b as double, n as integer) as double 9 dim dx as double, i as integer, m as integer 10 m = 2 * n 11 dx = (b - a) m 12 compound Simpsen integral = fx(r, a) +fx(r, b) 13 for i = 2 to m step 2 14 compound simpson integral = compound simpson integral + 4 * fx(r, (a + dx * i - 1)))2 * fx(r, (a + dx * i)) 15 next i 16 compound simpson integral = (compound simpson integral - 2 * fx(r, (a + dx * m)))dx 3 17 end function 18 public function fx(f as string, x as double) as double 19 f = lcase(f) 20 fx = evaluate(replace(f,"x", x)) 21 end function The argument of both functions is 1Integral function, the independent variable is represented by x, x is not case sensitive2The minimum number of points is 3

The maximum number of points is 4The number of divisions cannot be greater than 32766, for trapezoidal integrals, the larger the closer to the true value, for the complex Simpson integral, there is no need to use a large number of divisions The integral interval contains singularities (points that tend to infinity), the complex Simpson formula will not be able to integrate correctly, and may be corrected in the future I also hope that the master will make a move to call the method: = trapezoidal integral ("sin(x)^3-ln(x+1)^3+x^5-x", 0, 2, 2000)

After learning the course of numerical calculation methods, you will find that the first floor is casually talked. There is no ready-made method for finding integrals in Excel for the time being. There are many software on the Internet that can be used to find points.

Take, for example, Wolfram Mathematica 8. If you insist on using excel to find integrals, you can only write VBA**. However, according to the numerical calculation method, it is very difficult to write the program according to the so-called compound trapezoidal formula, or the compound Xin Fusheng formula.

1. First of all, enter the monthly consumption amount in excel**, and you need to calculate points according to the amount.

2. Calculate the total consumption amount first, and enter the formula in the total cell: =sum(A2:D2).

3. After getting the total result, enter the conversion formula between points and consumption amount in the "Points" cell, for example, 100 yuan corresponds to 1 point, then enter: =E2 100.

4. Click Enter to get the total points corresponding to the total consumption amount.

The steps are as follows: Hui Tuan.

1. First of all, in excel**.

Enter the monthly consumption amount in , and you need to calculate points based on the amount.

2. Calculate the total consumption amount first, and enter the formula in the total cell: =sum(A2:D2).

3. After getting the total result, enter the conversion formula between the points and the consumption amount in the "Points" cell, for example, 100 yuan for 100 yuan to take the orange, then enter: =E2 100.

4. Click Enter to get the total points corresponding to the total consumption amount.

There is no ready-made method for finding integrals in Excel for the time being.

1. There are many software on the Internet that can be used to find points. For example: Wolfram, Mathematica 8, etc.

2. Integration is the inverse of differentiation, that is, the derivative function of the vertical lead branch function is known.

Inverse the original function.

3. In the application, the integral function is not only that, it is used to sum by a large number of residual sensitive excitations, and in layman's terms, it is to find the area of the curved triangle, which is determined by the special properties of the integral.

The geometric meaning of the definite integral is to find the area under the curve, which can be found in excel

Use Excel charts to plot discrete points with XY scatter plots;

The trend upward selling line of Excel is used to draw the approximate fitting curve where the discrete points are located.

The trend line of Excel is used to approximate the fitting curve formula.

Use the indefinite integral in calculus to find the original function (this step cannot be replaced by Excel);

Use Excel's ** and formula to calculate the definite integral value.

Example 1: Figure 1 is drawn from a set of data in Table 1Find the area under the curve in Figure 1 (in purple):

In fact, <>, the key to this example is to find the formula of the curve, so it is necessary to plot the data in Table 1 as a scatter plot, and draw the trend line and the trend line equation accordingly, so that it can be solved using the definite integral.

Step 1]: Select the data unit in Table 1, enter the [Chart Wizard-4 Step 1-Chart Type] dialog box, select "X Y Scatter Chart", cancel the legend in "Next", and get the XY scatter plot after completion, as shown in Figure 2.

<>Step 3]: In the [Add Trend Line] dialog box, switch to the "Type" tab, and in the "Trend** Regression Analysis Type", you can select "Polynomial" according to the meaning of the topic and the convenience of definite integral calculation, and the "Order" can be adjusted to 2 (the degree of fitting between the curve and the point can be adjusted), as shown in Figure 4

For more information on how to understand the characteristics of trend** and regression curve types, and how to better select data points for better fitting, see [Appendix 2].

Step 4]: Switch to the "Options" tab, select the "Show Formula" checkbox, and "Set Intercept=0" can also be selected as appropriate, as shown in Figure 5. Shows the value of r squared, can also be selected, in order to observe the degree of fit of the curve, the closer the square of r is to 1, the higher the degree of fit, the value of r square of this sail comma is 1, that is, the perfect fit, is the best trend line.

Step 5]: Find the original function (x) of the curve equation using indefinite integrals

Step 6]: Use Excel** and the drag of the formula to find the value of the original function: as in the following table 2, select the cell c2, type the equal sign in the upper laughing bar and insert the formula "=", after the press enter is confirmed, place it in the lower right corner of c2 with the mouse, and when "+" appears, drag it from c2 to c12 to obtain the value of the original function, that is, f(0), f( .

Note: The formula in cell C1 is just the header of column C, and the specific calculation must refer to the cell. 】

Step 7]: Find the area under the curve of the interval [0,1], as can be seen from Table 2.

f(1)-f(0)

In mathematics, an indefinite integral of 1 x 2 is calculated like this:

1/x²dx

x^(-2)dx

1/3x^(-3)+c

Introduction to Points. Intuitively, for a given positive real value function, the definite integral over a real number interval can be understood as the area value (a definite real value) of a curved trapezoid, surrounded by curves, lines, and axes on the coordinate plane.

A strict mathematical definition of an integral is given by Bonhard Riemann (see the entry "Riemann integral"). Riemann's definition uses the concept of limit, imagining a curved trapezoid as the limit of a series of rectangular combinations. From the nineteenth century onwards, more advanced definitions of integrals emerged, with the integration of various types of functions on various integral domains.

The way points are calculated varies from company to company or organization, but in most cases they are calculated based on the performance of a certain behavior. For example, points can be calculated based on the number of products purchased by customers, **, or the number of times they participate in related activities.

Excel often needs to be used to calculate the credit product, how to calculate the credit puss hole product? The following is the method of calculating the credit product in excel that I bring to you for your reference.

Excel Calculation Credit Product Method:

Step 1: Open the student's grade data table in Excel and know what the score is for each subject.

Step 2: Place the cursor in the blank space at the end of the first line of grades, enter "=" after "fx", and then enter the formula for calculating the credit product "=(b2*4+c2*4+d2*2+e2*6) (4+4+2+6)".

Step 3: After the input is complete, click the "Enter" button to complete the output of the first row of results. You can see that the result is calculated successfully.

Step 4: Then, place the cursor in the bottom right corner of the first result to become a cross.

style, scroll down to the end of the grade list.

Definite integration in Excel can be achieved by using numerical integration methods, among which the more commonly used methods are the trapezoidal method and the Simpson method. Here's how to use these two methods to perform definite integrals:

Trapezoidal method: Suppose we want to perform a definite integration of the function y=f(x) on the interval [a,b], we can divide the interval into n small intervals with a width of h=(b-a) n, and then use the following formula for numerical integration:

a,b]f(x)dx ≈ h/2 * f(a) +2f(a+h) +2f(a+2h) +2f(a+(n-1)h) +f(b)]

In Excel, you can calculate the definite integral using the trapezoidal method by following these steps:

1) Determine the function and integral interval that needs to be solved.

2) Divide the integral interval [a,b] into n intervals, which can be achieved by setting cells, for example, set cell a1 as a, cell a2 as b, cell a3 as n, cell a4 as h.

3) Calculate the value of H with the formula "=(B2-B1) B3" (without quotation marks), and display the result in cell A4.

4) Calculate the value of the function between each cell, the formula is "=F(A1+IA4)" (without quotation marks), where F is the function to be solved, and i is the number of the cell (starting from 0), Songzhou displays the result in cells B1 to BN+1.

5) Calculate the value of the trapezoidal numerical integral, the formula is "=A4 2(B1+BN+1+2*SUM(B2:BN))" without quotation marks), and display the result in cell C1.

Simpson's method: Similar to the trapezoidal method, the Simpson method also divides the integral interval into several small intervals, but it uses a more complex formula for numerical integration, namely:

a,b]f(x)dx ≈ h/3 * f(a) +4f(a+h) +2f(a+2h) +4f(a+3h) +4f(a+(2n-1)h) +f(b)]

In Excel, you can calculate the definite integral using the Xinzhe Sakura Pusen method by following these steps:

1) Determine the function and integral interval that needs to be solved.

2) Divide the integral interval [a,b] into n intervals, which can be achieved by setting cells, for example, set cell a1 as a, cell a2 as b, cell a3 as n, cell a4 as h.

3) Calculate the value of H with the formula "=(B2-B1) B3" (without quotation marks), and display the result in cell A4.

4) Calculate the function value between each cell, the formula is "=f(a1+i*a4)" (exclusive.