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

The writer: bin Laden Dony

Editor of this article: satellite paste

I'm Laxiaden, an Excel teacher who can design forms.

Yesterday, I listened to an introductory course on hand-drawn original painting; the teacher first introduced the career development route of the original painting industry, which impressed me very much.

❶ hand-painted rookie. Draw your own facial expression, draw an avatar or something.

❷ is outsourced. In the painting cute king, rice painter, art box and other outsourcing websites, take over the painting customization, 50 ~ 200 yuan per piece.

❸ enterprise customization. "Onmyoji" official choice poster, original painting designer 15 in case, then customized by the enterprise.

After listening to this development route, I was so impulsive that I immediately closed all the Excel windows, opened PS, plugged in the digital board, and began to draw.

I still have a gift, right?

After reading my paintings, I calmed down a lot.

And habitually open Excel, began to think, what is the career path of making forms?

An introduction to ❶ Learning the basics of Excel

Quick Statistics of ❷ Learning PivotTable

❸ learning function formula

❹ learns Power query and M language

❺ learns DAX and SQL

Visualization of ❻ Learning PBI Chart

Is that it? I'm still thinking.

1. Problem description put aside this topic, let's solve the practical problem first.

I'll feel more relaxed when I make a form.

The above is the waybill statistics of our sales colleagues, and the waybill numbers are filled in a cell, so the statistics are not convenient.

How do you split these order numbers into the same column and look like the following?

2. Cause analysis ▋ WHAT, what is the problem

This is a problem of data cleaning.

Data cleaning refers to the last program to find and correct identifiable errors in data files, including checking data consistency, dealing with invalid and missing values, etc. Unlike the questionnaire review, the data cleaning after input is generally done by computer rather than manually.

Cell data, split into multiple cells, is also data cleaning.

▋ WHY, why do you want to clean?

The data saved in the computer are all one-dimensional data lists, so it is convenient to use PivotTable, function formula, SQL, Python and other technologies for statistical analysis.

▋ HOW, how to do data cleaning

Excel rookie

Xiaobai's method must be copied and pasted manually.

A novice to Excel

If you have an Excel basis, you will think of using the sorting function, and then transpose and paste.

Power Query

Now that Excel has grown up, you can split this data in one step using Power query.

3. The solution will stop talking nonsense and look directly at the operation steps.

Import ▋ data into Power query

Select any cell in the data, and on the data tab, click "from Table / region" to import the data into Power query.

Next you will see a new window, which is Power Query.

▋ disassembly

❶ sets the waybill number type

By default, the waybill number will be in a numeric format (scientific counting) and needs to be formatted as text.

In the dialog box, click replace current conversion

After the revision, the original format of scientific counting has been revised.

❷ disassembly

Using the "split" function of Power query, split the data directly into a column by splitting it by comma.

❸ uploads the form. Done.

Finally, on the Home tab, click "close and upload" to complete the operation.

The effect of the conversion is as follows:

4. Summary

The "split" in Power Query can be understood as an advanced version of the "split" function. It has many more powerful functions than ordinary "divisions":

❶ can split the data into rows

❷ supports splitting by case

❸ supports split from digital to non-digital

❹ supports splitting according to custom characters (formula needs to be modified)

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.