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

The original title: "Let the boss look dumbfounded, colleagues use this function to sort with one click, too awesome X!" "

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

First of all, let me ask you a question: how to make a column chart arranged from high to low on the right without destroying the original data?

It's actually very simple!

Just use the SORT function to sort the original data column, get the mapping data, and then insert the column chart!

The formula entered in cell E3:

= SORT (B3VOR C8pl 2MALLY 1)

The SORT function takes four parameters:

= SORT (range, column or row, up or down, by row or column)

The 1st parameter of ❶ is required and represents the area that needs to be sorted. If other parameters are omitted, sort column 1 in ascending order.

In the following formula, the SORT function sorts the first column of the "B3:C8" area in ascending order, that is, the first name column.

= SORT (B3:C8)

The second parameter of ❷ is to specify the column to be sorted.

In the following formula, the second parameter is 2, which means to sort the second column of "B3:C8", that is, the single quantity column. The default is to sort in ascending order.

= SORT (B3Rose C8Magne2)

Parameter 3 of ❸ is the basis for sorting, ascending or descending. Enter 1 in ascending order and-1 in descending order.

In the following formula, the third parameter is-1, which means that the second column of "B3:C8", the single quantity column, is sorted in descending order.

= SORT (B3VOR C8pl 2MALLY 1)

Parameter 4 of ❹ indicates the direction of sorting, TRUE indicates sorting by row, and FALSE sorts by column.

In the following formula, the fourth parameter is 1, which is a non-zero value and represents TRUE. The formula means to sort the second line of "C2:H3" in descending order, that is, the single quantity line.

= SORT (C2Rose H3pr 2mai Lay 1Pol 1)

Of course, in the actual work, we will also encounter the problem of multi-conditional sorting.

As shown in the following picture, the grade is divided into the descending order of the first keyword and the ascending order of the second keyword. The formula is:

= SORT (B3:D8, {3jue 2}, {- 1pr 1})

The second parameter in the formula is the array {3Power2}, which sorts columns 3 and 2 in "B3:D8", and the corresponding parameter 3 is the array {- 1 ~ 1}, where-1 means descending column 3 first, and 1 means ascending column 2.

It should be noted here that the array entered in the parameters should correspond to the primary and secondary relationship one by one.

The SORT function has a sibling function, the SORTBY function.

= SORTBY (range, sort range 1, rise or fall, sort range 2, rise or fall.)

Next, let's take a look at the differences in usage between the two functions:

❶ is sorted by column as shown below, and sorted by single descending order of column C.

SORT function formula:

= SORT (B3glaze C8 Magne2 Magazine 1) uses 2 to indicate the column to be sorted, the second column, that is, the single quantity column.

SORTBY function formula:

= SORTBY (B3glaze C8MagneC3GREOMIRE1) denotes the column to be sorted by the region C3:C8, that is, the single quantity column.

❷ is sorted horizontally as shown in the following figure, sorted by single quantity line in descending order.

The function formula of SORT is:

= SORT (C2 H3jue 2mai Lili 1jue true)

The formula of SORTBY function is:

= SORTBY (C2Rose H3rect C3Glo H3mai 1)

Thus, the SORTBY function directly specifies the rows or columns that need to be sorted, rather than setting additional parameters to indicate how to sort by row or column.

❸ multi-conditional sorting is shown in the following figure, which is divided into the first keyword descending order by grade, and the second keyword ascending order by grade.

The formula of SORT function is:

= SORT (B3:D8, {3jue 2}, {- 1pr 1}})

The effect is as follows:

The formula of SORTBY function is:

= SORTBY (B3glaze D8rect D3Jing D8jue Ling 1jue C3JR C8pj1) the effect is as follows:

Obviously, the SORTBY function is easier to understand when it comes to sorting multiple conditions.

At the end of the writing:

Both the SORT function and the SORTBY function are dynamic array functions for sorting. The SORT function needs to specify the column of the sort, while the SORTBY function only needs to enter the sorted column, which is suitable for multi-conditional sorting.

However, it should be noted that these two functions can only be used in Office version 365.

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.