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

Hello, everyone. I'm a cool duck dealing with data.

At the beginning of every quarter, Lao Ye, the head of the company's project department, scores the performance of the previous quarter in order to pay out the performance bonus of the previous quarter.

He will score the performance of the members involved in the project according to the projects that have been completed. For the sake of convenient registration, he made the following form.

Lao Ye knew that this kind of table was not a standard data table and could not use the PivotTable to further analyze the performance, so he came to me and asked me to help convert it into a standard one-dimensional data table.

So, today, Xiao Shuang will take you to take a look at the specific practices. The operation is not difficult, it is more basic, and the focus is on understanding ideas.

1. Word replacement method is applicable to any version.

Difficulty index: ★

When we look at the data, we can see that the data is concentrated in cells and merged with commas.

It's not hard to imagine what if the comma itself is a carriage return? Isn't that a direct separation?

So how do you replace the comma with a carriage return and split it into other cells in Excel?

Use Word Duck!

Specific steps:

❶ copies the data into Word ([Ctrl+C / V] copy and paste is not taught by me).

Test everyone, what is the Word replacement window shortcut key?

Yes! It's [Ctrl+H]!

❷ holds down the shortcut key [Ctrl+H] to open the replacement window for Word.

Find what:, (comma)

Replace it with a manual newline character (also known as soft carriage return), which we can add in [more] through a special format.

Select special format-Manual newline character, at this time, replace with the input box, a ^ l appears.

^ l is the symbol for the manual newline character. Click the replace all button.

The replaced data is shown in the following figure.

❸ We re-copy and paste the data table of Word into Excel.

Select the entire table and hold down the shortcut key [CTRL+C] to copy.

Select a cell, press and hold [Ctrl+V] to paste, as shown in the following image, you can see that the comma has been split into lines.

Split and merge cells and populate them. In the case of WPS, the system comes with this feature. As shown in the following picture.

Here, it's done!

Through the way of mouse clicks and shortcut keys, we have finished the arrangement of the table.

However, although this method can be an emergency, but if, like Lao Ye, we have to do this data every quarter, we still have to use methods such as function or M function.

2. Function extension applicable version: Office 365

Difficulty index: ★★★

If you are Office 365 or WPS (the latest version), you can use the Textsplit function and Hstack function.

= HSTACK (CONCAT (SUBSTITUTE ("," & B1:B5, ";" & A5 & ",")), ";", 1), TEXTSPLIT (TEXTJOIN (",", C1:C5), ",")

Some friends may feel a little strange to these two new functions.

No, we need to know before we think of using it and searching.

The Textsplit function is a very powerful function.

For those who are interested, click the link at the end of the article to jump to the Textsplit series of articles.

= TEXTSPLITtext, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with]) = TEXTSPLIT text column separator [row separator], [whether to ignore empty cells], [case sensitivity], [value filled in error]) HSTACK function, is a column splicing function, with this function, many problems become more and more simple.

For example: merge the three regions of A1pura A2MagneC1RO C2MagneE1Rose E3 according to columns.

Understand these two functions, let's take a look at this formula, it is not difficult to understand!

= HSTACK (/ / column concatenation TEXTSPLIT (CONCAT (SUBSTITUTE ("," & B1:B5, ";" & A1CONCAT A5 & ","), ";", 1), / / get the name and the data column TEXTSPLIT (TEXTJOIN (",", C1:C5), ",")) / / get the score column Textsplit function tutorial reference:

Textsplit, here comes a super powerful new function!

This super powerful text processing function can help you finish your day's work in 30 minutes!

These three Excel text functions are undervalued again! (recommended collection)

3. Finally, this paper explains how to transform a non-standard table into an one-dimensional data table through operation or function method.

After the data table is converted, it can be analyzed through the PivotTable report.

As shown in the following figure: what is the total score of the number of projects that each project member participated in last quarter? How many people participate in each project and who are the participants?

Through further analysis of the data, in addition to solving the problem of performance calculation, leaders can also make further decisions, such as more reasonable planning for the current quarter.

See here, do you feel that Excel is very powerful?

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Xiao Shuang

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.