If you want to improve your Microsoft Excel skills, don't rush into studying various complex functions, but first understand Excel's built-in "pivot analysis". This project is simple to operate and easy to use, but its functions are very powerful. It can be said to be the most important essence of Excel! Wang Zuohuan, the author of "Excel Worksite Practical Guide" pointed out that mastering pivot analysis skills can solve almost 80% of Excel analysis needs and help you gain insight into the truly meaningful information in the data. The following uses market survey questionnaire data as an example to illustrate the practice of pivot analysis.
1. Set the data analysis scope and create a pivot analysis table
Suppose that "THE INTERN" wants to launch a new magazine for people who are willing to spend more money to buy magazines. In order to meet the needs of potential customers, it specially asks the marketing department to conduct a market survey to determine the field it should enter. As a project leader, if you want to quickly summarize recommended strategies from this batch of questionnaire data, the most suitable processing tool is the pivot analysis table.
1.1 Click "Pivot Analysis Table" in the "Insert" index tab
Please create a new pivot analysis table in Excel.
1.2 In the dialog box of "Create Pivot Analysis Table", confirm the table range "Pivot! $A$1:$I$151"
Tell Excel where the data you want to analyze is. We set "Pivot" as the name of this worksheet, and "$A$1:$I$151" refers to all the data from A1 to I151 in this worksheet. Most of the time, Excel will pre-fetch the data range, but if there are blank columns in your data, you must re-enter the data range to set it yourself.
1.3 Select the location "New Worksheet" where the pivot analysis table will be placed.
You can also choose to put it in an existing worksheet, but unless the original data content is very small, in order to avoid clutter, it is recommended to put the pivot analysis in a new worksheet.
2. Adjust the composition of the pivot analysis table and put the fields you want to analyze.
Before using pivot analysis, let us first explain the layout of the pivot analysis worksheet. The left side is the area where the final report is presented, and the "pivot analysis table field" on the right is used to control the content of the report presentation. As long as the settings are adjusted, the report on the left will be updated immediately.
The pivot analysis table fields are divided into upper and lower blocks. The upper half is for checking the data fields that you want to appear in the report (a friendly reminder: in order to display the data fields, the first column of the original data list must have fields. Name), the lower half is where you want the data to appear in the report (filter, row, column, value), drag the upper half of the data fields to the corresponding area:
Filter: The fields dragged to this area will be used as the basis for filtering the entire report data.
For example, if you put "monthly income" here, you can filter it so that the report only displays data above 30,000.
Row: The row dragged to this area will become the row data of the pivot analysis table.
If you fill in data in more than one field, Excel will group the report. For example, put "gender" first and then "how much money you spend on magazines every month." The report will show statistics of different spending amounts for the same gender. As a result, if you want to change the order of the groups and change the statistical results of different genders with the same amount of money spent on magazines every month on the previous page, you have to drag and drop "How much does it cost to buy magazines every month" on " After sex".
Column: The column dragged to this area will become the column data of the pivot analysis table.
The column dragged to this area will become the row data of the pivot analysis table. Like column labels, as long as you fill in data in more than one column, Excel will help group the data to collect statistics. If you want to change the grouping method, you need to change the order in which the fields are placed in the column labels.
Value: Drag to the field in this area to indicate that you want Excel statistics to be summarized.
Drag to the field in this area to request Excel statistical summary. Click the left button on the arrow to the right of the items placed in this area, click "Value Field Settings", and set whether you want Excel to calculate the sum, average, number of items, maximum value, or minimum value, etc.
You can interpret this analysis table like this:
The original sales strategy is to launch new magazines for people who are willing to spend more money to buy magazines. So where should the channel be set? Assume that people who are willing to spend more money refer to people who "spend more than 400 yuan per month" to buy magazines.
You will find that if you want to focus on boys, online bookstores will be one of the indispensable channels, but girls who are willing to spend higher amounts on magazines do not rely so much on the Internet. General bookstores are a channel for both men and women to purchase magazines.
3. Numerical data such as time and amount can be merged
If the original questionnaire design asked respondents to directly fill in the "actual age" instead of checking the age range, when the data is thrown into the pivot analysis, the analysis results for each age will appear, which is too detailed and difficult to judge.
For this kind of digital data, Excel can help you use the group function to merge the data in the same section into one group. In terms of age, you can ask Excel to merge the ages into 11~20, 21~30, 31~ 40, 41~50, 51~60 to present. This method is also particularly useful when analyzing data related to "time". The data can be grouped and presented by year, month, quarter, and day, making it easier for you to make comparisons.
Here's how:
- Select any cell in the pivot analysis table with the age column label
- Right-click and select "Group"
- Set the start point to 11, the end point to 60, and the spacing to 10
- Click "OK" to complete
