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

Original title: "still staying up late for Excel?" Come and try this "omnipotent" formula, simple, efficient and easy to use! "

We usually use Excel, in addition to recording data, analysis and statistics, sometimes we also need to convert one data structure into another data display mode according to the requirements of leaders.

We showed how to use Super PivotTable (Power Pivot) and Power Query to perform the following data structure transformations.

However, my friends, if you are using WPS or Microsoft Office below version 2013, you will not be able to use the advanced methods mentioned above.

What shall I do?

Don't worry.

Today, I'm going to tell you about the methods that can be used in all versions.

Data conversion An is shown in the following figure. Now we need to convert data region 1 on the left to data area 2 on the right.

We can use the advanced screening + universal oil formula to complete the conversion.

The steps are as follows:

❶ extracts non-duplicates from a large class.

Click the [data] tab = "[Advanced] = =" in the list area, select column A = = "check [Select records that do not repeat], and finally click [OK].

The result is shown in the following figure:

Finally, copy the non-duplicates in the [A] column and transpose them.

The following is a reference diagram:

❷ uses the universal oil formula.

The one-size-fits-all formula is a very easy to use formula routine, learn it well, everything is easy to say!

Enter the following formula in the [D3] cell:

The formula can be divided into three parts: INDEX ($Baud1VlVO12 Magnum small (IF ($Aq1VlVO12) DLRW2) (ROW (A1) this formula can be divided into three parts:

First: IF conditional function

❶ IF ($1VR / 12 / 12 / 12 / 12))

If the content in [A1:A12] is equal to the content in [D2], its line number is returned, otherwise, FALSE is returned.

The results are as follows:

{FALSE;2;3;4;5;6;7;FALSE;FALSE;FALSE;FALSE;FALSE}

Second: SMALL function

❷ SMALL (❶, ROW (A1))

This step is to take the result value obtained in the first step and take out the first minimum value, where ROW (A1) returns the result as 1, that is, the first minimum value is: 2.

The PS:SMALL function ignores FALSE logical values. Take only the minimum value of the number.

Third: INDEX function

❸ INDEX ($B$1:$B$12, ❷)

This step is to take the result value in the second step: 2 as the second parameter of the INDEX function, which means: return the second value in the first parameter. That is, the value in the cell [B2] (chart).

At this point, the formula is decomposed and completed, it is not very difficult!

Note:

If the formula is copied and dragged down, only the second parameter of the SMALL function, the ROW function, changes.

From ROW (A1) to ROW (A2), that is, from the number: 1 to the number: 2. Returns the second minimum of the SMALL function: 3

The INDEX function returns the value in the [B3] cell (PivotTable).

If the formula copies and drags to the right. Only the first parameter in the IF condition area is changed.

From the original [D2] to [E2]. Everything else is exactly the same.

In addition: please pay attention to the absolute citation and relative citation in the formula!

The error values in the formula in the above figure can be masked by using IFERROR or IF+ISERROR functions.

The formula is as follows:

= IFERROR (INDEX ($Bamboo 1, ROW (A1)),) the formula routine is to set an IFERROR function on the outside of the original formula. Namely: IFERROR (original formula, ""). The second parameter, enter two English double quotation marks to indicate that it is displayed as: empty text.

In this way, we have completed the transformation of the first data structure.

In addition, let me remind you:

This formula is an array formula. In the lower version, you need to press three keys [Ctrl+Shift+Enter] to end the formula input.

Data conversion B is transformed from data structure 2 on the right to data structure 1 on the left.

This kind of data structure transformation is changed from column direction to row direction.

We can use the data conversion function of PivotTable.

The steps are as follows:

❶ select the [B2] cell, then press [Alt] + [D] + [P] to call up the PivotTable and PivotView Wizard.

Select [multiple merge calculation data region]. Click [next]

❷ keeps the [create single page field] checked by default. Click [next].

❸ in [step 2b of 3], select the area [A2:D8], click "add", and finally click "finish".

❹ will now create a new worksheet to display the results of the PivotTable report.

Double-click the [E6] cell with the mouse, which is the last cell in the total row.

A new worksheet will be created with the following result:

At this point, the original data structure of the column direction is displayed as the row direction!

Finally, the filter removes the whitespace in the value column, and the result comes out.

At the end of today, we learned how to convert between two different data structures:

❶ advanced screening + universal oil formula method

❷ PivotTable method

In the actual work, there may be other forms of data structure conversion, the use of the above methods combined with their own constant learning accumulation, I believe you can easily solve this kind of problem.

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