Shulou(Shulou.com)11/24 Report--

Recently, there have been a lot of complaints from member customers, and the boss wants to know what aspects the customers are dissatisfied with.

So ask colleague Xiao Li to call member customers to conduct a satisfaction survey.

Xiao Li worked very quickly and had a clear train of thought, so he quickly produced a satisfaction survey form:

After a day of hard work, Xiao Li made 50 phone calls and completed the satisfaction survey. The data collected goes like this:

Of course, Xiao Li knows that the above table cannot be shown directly to the boss!

You also need to count the answers to each question (that is, each column) to get the results in the following format.

How can this defeat the witty Xiao Li?

Xiao Li immediately began to make statistics by screening them one by one and then counting them. The specific method is as follows:

❶ selects how many men and how many women there are by sex.

❷ fills in the results in the "statistical results".

For each satisfaction survey item in ❸, repeat the operations of 1 and 2 above.

▲ 's head is getting bigger!

I felt something was wrong when I finished the second investigation project.

Thinking about counting so many columns of answers, Xiao Li thought there must be a quick and easy way, so he came to me and asked me.

01 needs I first analyzed Xiao Li's needs, which can be divided into two parts:

❶ counts the number of answers to each question, as well as the proportion.

❷ repeats step 1 to count each problem.

02 the method used by Xiao Li is very cumbersome.

In fact, we can directly use the PivotTable, follow the following steps, drag, you can quickly count it out.

❶ select all tables, and select "insert" tab-"PivotTable"-"OK".

❷ puts the [gender] field with the sum value, and because the gender is a text field, it counts directly.

It took less than 20 seconds to complete the gender statistics.

Next, copy the PivotTable and select other fields for statistics.

This is really faster than screening.

But now there are only seven survey items, and we can make seven PivotTables.

What if there are dozens of investigation projects?

The efficient way is still to use PivotTables!

But you need to convert the data to a standard format-- an one-dimensional table.

Insertion of knowledge points:

An one-dimensional table is a table in which a row is a piece of data and a column is an attribute.

Let's first take a look at Xiao Li's form. Seven survey items (column C to column I) are actually the same attribute, all survey items.

This kind of table with multiple columns with the same attributes is called a two-dimensional table.

In order to convert a two-dimensional table into an one-dimensional table, it is necessary to make a "dimensionality reduction blow" to the data, converting 7 survey item columns into 2 columns (attribute column, value column).

This process of dimensionality reduction is a process of inverting the perspective column.

Insertion of knowledge points:

Inverse perspective column, the selected N column will generate attribute column and value column, the value of the attribute column is the title of the original N column (shown in green), and the value column is the value of the original N column (shown in orange).

The table on the left is a two-dimensional table, and the table on the right is an one-dimensional table.

OK, after understanding the knowledge points, how to carry out this "dimensionality reduction strike"?

At this point, you need to use a very useful tool, PowerQuery.

▋ step 1: import data into PowerQuery ❶ fully selected data, and click * * data * * tab-* get and convert data * *-> * * from Table / region * *.

❷ in the pop-up dialog box, select the table containing the title, OK.

▋ step 2: inverse perspective of the data ❶ [Ctrl+A] all columns.

❷ Select [convert] tab-inverse perspective column.

▋ step 3: filter out the unwanted columns because the member number and name are not a statistical problem, remove them through the filtering function.

▋ step 4: close and upload Click "File"-"close and upload", a new worksheet will be generated in Excel to save the result.

▋ step 5: insert PivotTable ❶ insert PivotTable, put [attribute] and [value 2] in the row in turn; put [value 2] into the value (value 2 is text and can be counted directly) and put it twice

❷ right-click on the cell of the [count item: value 2: 2] column of the PivotTable-the value display method-the percentage of the parent summary-select the properties of the basic field, and finally click "OK".

❸ beautifies the form according to your own needs.

Percentage of parent summary of ❹ knowledge points:

= the total count of the value of an item (such as 36-45 years old) / the value of the selected basic Field (the age of the property field).

It's done.

In this way, even if there are dozens of more questions, they can be easily solved!

And PowerQuery has the same synchronous update function as PivotTable, so that the next time you conduct a satisfaction survey, you can update the statistical results once and for all.

03 the case written at the end of today is actually very simple. I think like this:

There must be ways to simplify repetitive operations.

❶ uses PowerQuery for reverse perspective and collates the data source

Combination of ❷ PivotTable and PowerQuery, 1: 1 > 2

When ❸ started out using only PivotTables, there was no good way to organize them efficiently. However, with the addition of PowerQuery, the efficiency of the whole statistical process is significantly improved.

It is recommended that you also try PowerQuery, and you will open the door to a new world.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: green Water Zero Editor: Zhu Lan

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.