Mathematical models can be used to analyze and solve many problems in the actual work, but many people mention “mathematics” on the headache, let alone the application. In fact, we see every day in Excel, has a variety of built-in analysis tools, without the need to go into the details of the internal mathematical model, you can make it work for us.
Find out Excel’s analysis tool library
In order to invoke the built-in mathematical modeling tools in Excel (this article takes the 2016 version as an example), we need to import a library of analytical tools. First of all, start Excel and create a new blank document, and then click the menu “File → Options” to open the Options dialog box, select the left side of the “Add-ins”, and then the right side of the “Management ” set to “Excel Add-in” (Figure 1), click the “Go To” button, and then open the Add-in macro window to check the “Analysis Tool Library “and other items (Figure 2).
Figure 1 The Options dialog box of Excel 2016
Figure 2 Available loading macros
Click “OK” to return, then switch to the “Data” tab, when you see the “Analysis” column on the right side of the “Data Analysis When you see the “Data Analysis” item in the “Analysis” column on the right, it means that the loading is successful.
Describe the mathematical model and analysis of statistics
Statistical analysis of a given sample of data (or aggregate) to outline the summary characteristics of this data is known as descriptive statistics. In the following example, the data from a class of students’ language test scores are counted in order to understand how well the class has mastered the language course.
Start Excel and enter the raw data of the students’ test scores, here we assume that there are 66 students and the data is stored in the area A1 to A66. Click “Data Analysis” on the toolbar, select “Description Statistics” from the pop-up dialog box and click “OK”, then set the input and output areas in the new window that opens In the new window that opens, set the location of the input and output areas, check the items that need to be counted (Figure 3), and click “OK” to display the results soon (Figure 4).
Figure 3 Setting the description statistics option
Figure 4 Analytical results describing statistics
The analysis results are divided into two main categories, one is convergent information, which describes the central position of the distribution of the data set, such as mean, median, and plural. The other category is the variability information, which describes the discrete data of the data set, such as variance, minimum, maximum, kurtosis and skewness.
Behind each analysis result there is a corresponding mathematical model, such as the mathematical model of the mean (mod01.png), the mathematical model of the standard deviation (mod02.png), etc. We do not need to understand the details behind these mathematical models, Excel has already done the calculation for us.
Quickly draw a histogram model
The histogram is a way to represent the mathematical model, which makes the data look more visual and intuitive.
Following the above example, select “Histogram” in the data analysis window, and then you can bring up the Histogram dialog box, where the “input area” is the location of the original data, in this case, A1 to A66 area. “Receiving area” can not be filled in, Excel will automatically fill in. “Output area” can choose any blank cells, in addition to check the “Plot”, “cumulative percentages” and “chart output “and other options (Figure 5). Click “OK”, you can get the histogram and the related statistical results (Figure 6).
Figure 5 Set histogram options
Figure 6 Statistical results and histogram of data
Regression Analysis and Prediction
Regression Analysis is a mathematical model that is often used when we need to make predictions about the development of something. For example, whether there is a relationship between product sales and price and advertising, and whether sales can be predicted on the basis of known price and advertising, this model can be applied to analyze.
First, collect past data and enter it into Excel. Next, open the data analysis window and select “Regression” (Figure 7).
Figure 7 Simulating sales data and invoking the regression analysis tool
In the regression options dialog box, the “Y-value input area” selects the column for sales volume (A1:A8), which is the variable to be predicted in this case, also called the dependent variable. For the “X-value input area”, select the columns for price and advertising (B1:C8), which are the explanatory variables and are also called independent variables. Note that these columns must be adjacent to each other. The “output area” is set to a blank cell (e.g. A12). Finally, check the “sign”, “residual” and other items (Figure 8), click “OK” to get the detailed regression analysis results (Figure 9).
Figure 8 Regression analysis parameter settings
Figure 9 Regression analysis results
In the regression statistics of this example, the correlation coefficient R value (Multiple R) is as high as more than 0.9, which indicates that sales have a very strong positive correlation with price and advertising. The R-squared coefficient (R Square) is 0.85, which means the model fits well (generally more than 70% is good).
More data, you can refer to the regression analysis-related books to learn to understand, this article is limited to space, not to explain one by one.