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

Hi, I am the uncle Mr Zhao who prefers function formulas and likes to use Excel charts to manage warehouses.

In practical work, we often need to transform the structure of the data.

For example, in order to make it more intuitive and easy to view the data, you need to convert the one-dimensional table on the left of the following figure into a two-dimensional table like the one on the right:

Or for better statistical analysis of the data, you need to convert the two-dimensional table data on the left to the one-dimensional table format on the right:

The following is a detailed description of how to use function formulas to achieve the conversion of these two data structures.

1. Convert one-dimensional table to two-dimensional table ❶ extract grade into title row

Enter the formula in D2:

= TRANSPOSE (UNIQUE (A2:A34)) first use the UNIQUE function to extract the grades of column A that do not repeat into a column, and then use the TRANSPOSE function to transpose the resulting column of data into a row.

The effect is shown in the following figure:

❷ extracts the corresponding list

Enter the formula in cell D2:

= FILTER ($B2VRO / B34 / D1A) the corresponding list is screened out by using FILTER function and grade as the screening condition. Then drag the formula to the right to fill, and get the effect of the table on the right of the following figure:

2. Convert a two-dimensional table to an one-dimensional table ❶ first uses the IF function to determine whether the list area "A2:D15" is empty; if it is empty, it returns an error value (# NAME?), otherwise it returns the grade corresponding to the first row "A1:D1".

Enter the formula in the F1 cell:

The result returns an array of multiple rows and four columns. The effect is shown in the "F1:I14" area of the figure below:

❷ then uses the TOCOL function to convert the array of multiple rows and four columns into one column.

= TOCOL (IF (A2:D15 = ", x, A1:D1), 2, 1) the effect is shown in column F of the following figure:

TOCOL is a new function in Office version 365. it is very practical and powerful, and it can convert most groups into a column of data.

The syntax for this function is:

= TOCOL (array, [ignore], [scan_by_column])

The first parameter is the array that needs to be converted into columns, and the first parameter IF (A2:D15 = "", x, A1:D1) of the TOCOL function in the formula is the array to be converted.

The second parameter can choose whether to ignore whitespace or error. The second parameter in the formula is 2, which indicates the error value in the ignored area.

The third parameter indicates the scanning mode. You can set whether to scan the array in row direction or column direction. By default, scan by row. If you want to scan by column, the value is TRUE or 1.

Finally, ❸ uses the TOCOL function to convert the name area "A2:D15" into a column.

Enter the formula in the G1 cell:

The second parameter in the formula is 1, which means to ignore the white space in the area "A2:D15" and convert it into a column. The effect is shown in column G of figure below:

3. Finally, the summary of ❶ one-dimensional table into two-dimensional table: first, the UNIQUE function is used to extract the unrepeated values of a column as the title row, and then the FILTER function is used to extract the corresponding content with the title as the filter condition.

❷ two-dimensional table into one-dimensional table: when the second parameter of the TOCOL function is 2, ignore the error value, convert the title row into a column, and then use the TOCOL function when the second parameter is 1, ignore the blank, and convert the corresponding area into a column.

All right, that's all for today.

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

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.