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

The original title: "the design of the questionnaire was lazy, and the result was as if the statistics were in the head." "

I usually don't answer the question description unless the person who asks the question is very beautiful.

No, unless the question is very representative.

For example, the classmate of the operator asked a question about the statistics of the "questionnaire star". I believe many people have also encountered it, so we must see the end!

Every post in the hospital should check the situation of statisticians going out.

When designing the questionnaire, it is set as multiple-choice topics in order to fill in the questionnaire conveniently.

It's good to think about it, but it's always like this when the data is collected.

Did not expect to lift the stone hit their own feet, now want to count the number of people in each post, the head suddenly confused.

In fact, it's not you who's out of your mind, it's the questionnaire website.

Exactly why, we'll talk about it later.

First of all, how to solve your problem?

The solution to this data is relatively complex. If you write a function formula, it is estimated to be longer than the Great Wall.

We use Power Query + PivotTable, we can easily achieve data summation.

The general steps are as follows:

❶ loads data into any cell in the Power Query ① selection data.

② in the "data" tab, click "from the table area" to load the data into PQ.

After the ❷ split the statistical results of each post into the Power Query, we split the data of each position into separate lines.

The "split" function on the home page is used here.

The general steps are as follows:

① selects columns to split

② is on the home page, click "separate"

③ paste the delimiter. In the advanced options, check "Row" and split the data to the row.

The task of ❸ extracting numbers is too easy for Power Query.

Use the Text.Select function to extract numbers with one click.

The steps are as follows:

① in add column, click the custom column.

The ② setting column is named "number".

③ adds the following formula and uses the Text.Select function to extract numbers with one click.

About the Text.Select function to extract numbers, please refer to the following article, there is a detailed video explanation.

Power Query:Text.Select-M function manual

❹ extracts the job name using the "split" function on the home page to split the job out.

❺ PivotTable counts the number of people for each post. Finally, on the home page, click "close and upload to" and select "PivotTable".

Then, the number of people can be counted quickly according to the position.

To sum up this classmate, he has a sense of responsibility.

Data statistics are difficult, the first thing that comes to mind is that you are out of your mind.

Even if it's the truth, you can't push all the problems up.

In fact, because of the diversity of "questionnaire topics", especially multiple topics, different options, that is, a different data record, the data structure of the questionnaire is very complex.

My commonly used Tencent questionnaire also has similar questions.

I did an Excel test questionnaire, and after the data were exported, there was a column for each question, and each row was a different option.

What is the correct rate of each question in ❶?

Which question in ❷ has the highest error rate?

What is the average score of ❸ as a whole?

What is the average score of each question in ❹?

It's impossible to count.

I thought that I would directly send the difference in the group. As a result, when I looked at the data, I silently worked an extra two hours and screened it out one by one.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: bin Laden Dony

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.