Use Excel to generate a frequency distribution table and a frequency distribution histogram

 

Fujian Nanping High School Zheng dinghua 353000 mobile phone 13859389247

Abstract: relying on the "analysis tool" provided by Excel, the actual operation and solution analysis are carried out through specific examples to solve the statistical problem perfectly and efficiently, this frees teachers and students from complicated statistical operations and drawing to gain a sense of accomplishment.

Keywords: Excel statistical histogram generation

In statistical teaching and learning, statistical analysis of data and drawing of statistical charts involve complicated computation and drawing processes. If you do this manually, it will be tedious, monotonous, and error-prone. Excel provides many powerful statistical functions and analysis tools. They help solve the same problem, saving time, efficiency, and perfection. This article takes the generation of frequency distribution table and frequency distribution histogram as an example to introduce the specific process of using the "analysis tool.

I. Methods for calling analysis tools

The "analysis tool library" includes the following tools: variance analysis, description analysis, correlation analysis, histogram, random function generator, sampling analysis, regression analysis, and Z-test. To access these tools, click data analysis in the Tools menu ". For the first call, you must first load the macro "analysis tool library ". The procedure is as follows:

(1) On the "Tools" menu, click "load macro ".

(2) In the "useful macro loading" list, select the "analysis tool library" box and click "OK ".

(3) Select "Data Analysis" from the "Tools" menu. In the "Data Analysis" dialog box, click the name of the analysis tool to be used, and then click "OK ". In the selected analysis tool dialog box, set the required analysis options.

Ii. Steps for generating a frequency distribution table and a frequency distribution histogram

1. Use the textbook method to group data

For example, for the average monthly water consumption of 100 residents in P66, which is a compulsory mathematics textbook of the New High School Curriculum Standard 3 "Statistics" (people teach a2007 edition), they are divided into the following nine groups at a distance of 0.5: [0, 0.5], (0.5, 1],…, (4, 4.5]

2. input data and shard Value

(1) For convenience, enter 100 pieces of data in square form into an appropriate area in the worksheet of Excel;

(2) input the value of the right endpoint of each group range to the same column (such as column A) in the table ).

3. Generate the frequency distribution table (histogram) and the cumulative frequency distribution table (histogram)

(1) Open "tool/Data Analysis" and select "histogram" in the analysis tool window ";

(2) In the "input area" in the histogram pop-up window (as shown in), use mouse or keyboard to input the data matrix "average monthly water usage area of 100 residents": $ B $2: $ K $12;

In the "receiving area", enter the "shard data" area in the same way: $ A $2: $ A $10;

(3) In the output option, click "output area" and enter the area of three columns and ten rows, for example, $ M $16: $ o $25;

(4) In the output option, click "chart output ".

After completing the preceding four steps, click "OK". The following frequency distribution table (histogram) and cumulative frequency distribution table (histogram or line chart) appear immediately)

In use, pay special attention to the following three points:

(1) do not set the frequency as a frequency. It will be easy to verify. The "Frequency" in the above chart actually represents the frequency, which is very likely a translation error in the Chinese excel. Therefore, we should change the "Frequency" in the table to "Frequency ", the data in the receiving area indicates the right end value of each group;

(2) Excel uses the left-open and right-closed method to calculate the frequency of data falling in each interval;

(3) when Excel calculates the frequency of sample data in the input area by segment, the system automatically skips when an empty cell is encountered. Therefore, if you enter any square matrix that contains all the sample data in the "input area", no statistical error of frequency occurs.

4. Generate the "" distribution table

In the Excel worksheet, change "receive" to "group" in the frequency distribution table, enter the range expression of each group in this column, and add a column "" on the right. According, in the first cell of the column, enter the calculated expression, for example, "= n17/(100*0.5 )", n17 is the address of the first cell in the "Frequency" column. The sample size is 100, and the group distance is 0.5. Press enter to get the value of the first group, select the cell, and use the mouse's "Cross" to align with the "small square" in the lower right of the cell. Press and hold the mouse's left button to hide it, drag down until the values of the groups are obtained, and the "" distribution table is obtained (as shown in the following table ).

Group	Frequency	Cumulative %	Frequency/Group Interval
[0, 1, 0.5]	6	6.00%	0.12
(0.5, 1]	10	16.00%	0.2
(1, 1.5]	16	32.00%	0.32
(1.5, 2]	21	53.00%	0.42
(2, 2.5]	25	78.00%	0.5
(2.5, 3]	11	89.00%	0.22
(3, 3.5]	6	95.00%	0.12
(3.5, 4]	3	98.00%	0.06
(4, 4.5]	2	100.00%	0.04

5. Complete the histogram of frequency distribution

Hold down "Ctrl" and select "group", "", and "cumulative frequency" from top to bottom in the above distribution table, click "chart wizard" in the Excel toolbar, select a column chart type, select a column chart type for ink recognition, and click "Next" twice in a row, enter the corresponding information in the chart title, X axis, and Y axis dialog box under the title and click "finish" to obtain the following frequency (cumulative) distribution histogram.

Use the cursor to select the histogram of cumulative frequency distribution (shown in blue), right-click mouse, and select "chart type" to change it to "line chart", as shown in.

Place the cursor on any rectangle of the frequency distribution histogram, right-click the mouse, and the "data series format" dialog window appears, enter the corresponding information in the chart title, X axis, and Y axis dialog box under the title, click "finish", and adjust the "category spacing" in the "options" column to zero, click "color by data point" and click "OK" to form the following histogram.

6. Make sure that excel can accurately calculate the frequency of data segments in the left-closed and right-open mode.

The textbook divides the average monthly water usage of 0.5 residents into nine groups, which are left closed and right open:

[0, 0.5), [0.5, 1 ),..., [4, 4.5]

To enable EXCEL to accurately calculate the data frequency of each left-closed and right-open interval, you only need to take one decimal place and reduce the value of the right endpoint of each region segment than the sample data, then you can group them by left-open, right-closed mode. For example:

[0, 0.45], (0.45, 0.95],…, (3.95, 4.45]

Complete step 2 ~ In step 5, the histogram effect is exactly the same as that of the textbook (see the chart below ).

Excel can be used to solve statistical problems such as variance analysis, description analysis, correlation analysis, sampling analysis, regression analysis, and Z-test, this frees us from some mechanical repetitive and complex computations and creates a sense of success. We will not expand this article because it is limited by space.