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

Original title: "Excel tables can also be automatically sorted?" These two ways give you a thumbs up!

You know what? Excel version 365 adds a new god-level function, SORT!

It is a function specifically designed for sorting.

However, the problem is that many of my friends are not using version 365 of Excel, but version 19 or even earlier.

Without the blessing of god-level functions, could automatic sorting of data be achieved?

Today, Xiaohua will share two sorting formulas dedicated to low version Excel to everyone.

LOOKUP+RANK method LOOKUP function dichotomy can solve almost all the problems related to the query, and automatic sorting, can be considered as sorting value query, naturally also no problem!

=LOOKUP(1,0/(RANK($B$2:$B$8,$B$2:$B$8)=ROW()-1),$A$2:$A$8)

① Formula Description:

RANK($B$2:$B$8,$B$2:$B$8) Obviously, this is an array formula, because LOOKUP comes with array operation properties, so do not press Ctrl+Shift+Enter to perform the operation can also make the ranking function RANK return a set of ranking values.

The sort value and sort range parameters of RANK function are both B2:B8. Through array operation, an ordinal value group {2;3;1;5;4;6;7} representing the sort size corresponding to each value in B2:B8 is returned.

② Formula Description:

0/(①=ROW()-1)ROW-1 indicates that the current row number is-1, and the order from E2 to E8 is 1-7, that is, the ranking value to be found by E2 is 1.

Compare the result {2;3;1;5;4;6;7} in ① with it, return TRUE if equal, and return FALSE if not equal, i.e.:

{FALSE;FALSE;TRUE;FALSE; FALSE;FALSE}; Divide 0 by this group of numbers. In the division operation, TRUE=1, FALSE=0, which results in:

0/{0;0;1;0; 0;0}, since 0 cannot be used as a divisor, we further obtain:

{#DIV/0!;# DIV/0!; 0;#DIV/0!;# DIV/0!;# DIV/0!;# DIV/0!}。Note: The array is only 0 at the position corresponding to the rank value 1 currently being searched for, and the rest are wrong values.

This is the core purpose of constructing the query range 0/(①=ROW ()-1) so that the LOOKUP function can find correctly.

③ Formula Description:

LOOKUP(1②,$A$2:$A$8)LOOKUP matches the query range ② with the query value 1, finds the numerical position in ② that is smaller than and closest to the query value, returns the value corresponding to the result range A2:A8, and automatically ignores the error value in ② during the process.

Because only the third value in ② is 0, the rest are the wrong value #DIV / 0!, So LOOKUP returns the third value in A2:A8, cell A4, Tao Haibo.

The core of LOOKUP+RANK method is the construction of query range (formula fragment ②). Use RANK function to generate a group of ranking values, and then apply LOOKUP dichotomy to complete query calculation. Have you learned it?

INDEX+LARGE method uses LOOKUP+RANK method for automatic sorting, there is an obvious loophole, that is, when the same ranking occurs, the formula results will be wrong.

At this point, we can use INDEX+LARGE to construct another array formula.

PS. After the array formula is entered, press Ctrl+Shift+Enter to calculate correctly.

{=INDEX($A$2:$A$8,MOD(LARGE($B$2:$B$8+ROW($1:$7)%,ROW()-1),1)*100)}

① Formula Description:

$B$2:$B$8+ROW($1:$7)%ROW ($1:$7) Returns an ordered array of values 1 through 7, representing the ordinal number of each value, which will eventually be used as the index value of INDEX.

" %" is shorthand for "/100," so $B$2:$B$8+ROW ($1:$7)% is equivalent to adding the mantissa 0.01-0.07 to each of the numbers in B2:B8, resulting in:

{64.01;74.02;74.03;37.04;46.05;19.06;2.07} Since the values in the case are integers, adding different mantissas ensures that the values do not equal each other.

PS. In fact, just make sure that the mantissa added is always less than the significant digit of the value to be sorted, and you can avoid the formula error caused by equal values.

② Formula Description:

LARGE①,ROW()-1) The LARGE function returns the value of the specified rank in the order of the largest to the smallest of the data sets.

ROW ()-1 in the E2 cell formula returns the current row number minus 1, which means that LARGE returns the first largest value in {64.01;74.02;74.03;37.04;46.05;19.06;2.07}, and so on in E3:E8 cells, taking the second to seventh largest values.

③ Formula Description:

INDEX($A$2:$A$8,MOD②1)*100 The MOD function is the remainder function, MOD (②,1), which divides ② by 1 to take the remainder, to get the mantissa we added to B2:B8 in fragment ① by ROW ($1:$7)%. Multiply this mantissa by 100 and it can be reduced to ROW ($1:$7) itself, which represents the serial number of each value in B2:B8.

In cell E2, divide 74.03 by 1 to get the remainder 0.03, multiply by 100 to get 3, which means that the largest number is the third number of B2:B8.

At this point, use INDEX to extract the third number of A2:A8.

We know that the second number B3 and the third number B4 are both 74, which is the largest.

However, since ROW ($1:$7)% is 0.02 and 0.03 respectively, B3 is arranged as the largest number in cell E2, and B4 is treated as the second largest number in cell E3.

This solves the problem that numerical equality cannot be sorted in turn. This is the secret of INDEX+LARGE method. Have you learned it?

The above is the two low version Excel special sorting formulas shared by Xiaohua. The main points are as follows:

Generate a group of ranking values through array operation of RANK function, and then construct LOOKUP 1/0 query structure to realize automatic sorting of data;

Add the mantissa representing its ordinal number to the original data by ROW%, so that the data are not equal to each other, then use LARGE to take the value of the specified order, then use MOD function to take the remainder * 100 to restore the ordinal value, and finally use INDEX to achieve sorting.

Although the above formula is a little complicated, but after detailed analysis of floret, I believe that the small partners will be able to clear through, into the bag.

This article comes from Weixin Official Accounts: Autumn Leaf Excel (ID: excel100), Author: Xiaohua

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.