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

Today, our sharing has to do with data structures.

The optimization of data structure is the basic work of data analysis and processing.

Sometimes, the table we get, its data structure, is not conducive to the subsequent data processing work.

At this time, we need to adjust and optimize the data structure appropriately.

For example, we sometimes encounter the data structure shown on the left side of the following figure, which is suitable for reading, but the subsequent analysis work will be very inconvenient.

So, we need to convert it to the structure shown on the right.

So, how do we complete the transformation of this data structure?

Copy and paste? This is the rhythm of working overtime!

But don't worry, there are still many solutions.

Function method We can achieve the desired effect through the combination of IF, SMALL, RIGHT, TEXT, ROW, COLUMN, INDIRECT and other functions:

Let's first solve the last name column and enter the formula in G2 cell:

= INDIRECT (RIGHT (RIGHT (SMALL (IF ($A$2:$D$6 "", ROW ($A$2:$D$6 6) * 10+COLUMN ($Amodine D) * 10001), ROW (A1), 3), "r0c0") ▲ swipe left and right Note: this is an array formula, you need to press [Ctrl+Shift + enter] to complete the input.

The department is listed in cell F2 to enter the formula:

= INDEX ($A$2:$D$6=G2 (--($A$2:$D$6=G2)), ROW ($A$1:$A$5) ^ 0)) ▲ swipe left and right Note: this is also an array formula, you need to press [enter + enter] to complete the input.

In this way, we have achieved the desired effect.

In this method, there are many functions and complex formulas, especially in the case of large amount of data, because it is an array formula, the amount of operation will be very large, there will be stutter phenomenon.

We will not explain these two formulas today, because they will take up a lot of space.

Next, there is a better way!

Misplaced quotation ❶ enter the formula in cell A7, then drag right and down

= B2 ❷ copies the data of A2:A21 to G2:G21, note that you should use selective paste-numeric value when pasting.

❸ at this time, the name is not connected from end to end, there are a lot of 0 in the middle, we press the [Ctrl+G] key combination, open the positioning function, click the positioning condition, select "constant", go to all the ticks other than the number, and click OK.

❹ at this time, all the cells with content 0 have been selected, move the mouse over any cell with a value of 0, and click:

Right mouse button-delete-move the lower cell up-OK, complete the operation of deleting the 0-value cell, and make appropriate formatting adjustments.

For the department column, the above formula is still used, that is:

= INDEX ($A$2:$D$6=G2 (--($A$2:$D$6=G2)), ROW ($A$1:$A$5) ^ 0)) ▲ swipe left and right Note: this is an array formula, you need to press [enter + enter] to complete the input.

The reason for the inclusion of a 0 value in the data is that there are empty cells in our original data source:

This method is much simpler than the formula method, but it is still not suitable for dealing with a large amount of data.

So is there a way to deal with large amounts of data quickly, efficiently and calmly?

The answer is yes, that is our Power Query.

Power Query method ❶ mouse to any cell in the data area (in this case, A1:A6), click "Power Query"-"from Table / range", check "Table contains title" in the pop-up dialog box, and click OK.

At this point, ❷ opens the main interface of Power Query.

Click the title of the first column, hold down [Shift], and click the title of the last column again, so that we can quickly select all the columns.

❸ clicks "transform"-"inverse perspective column", and selects "inverse perspective column" in the drop-down list to complete the conversion of the data structure.

❹ at this time, we see that the same departments are not grouped together, and the corresponding headings are "attribute" and "value" in the "department" and "name" columns.

This is the default title name for Power Query, and it's not what we want.

We renamed the attribute to Department and the value to name, respectively, and then click the drop-down button to the right of the name, select ascending or descending order, and sort the department columns to bring the same departments together.

❺ Click File-close and upload to, in the pop-up dialog box, display mode selection-Table.

Here we choose to put it in the F1 cell of the existing worksheet, of course, you can also choose to create a new worksheet according to your own needs.

Finally, ❻ can further adjust and beautify its format and font according to its own needs.

Very efficient, isn't it?

Finally, I secretly tell you that although the table converted by this method does not use any function, it can also be dynamically updated.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: eldest cousin, 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.