What are the sorting formulas in excel

If you want to know more about excel, you can click: Excel Tutorial

##Sort is a commonly used operation in Excel. In addition to operating skills, there are also some functions and formulas that can implement the sorting function.

1. Ordinary sorting: RANK function.

Purpose: Sort sales in descending order.

Method: Enter the formula in the target cell: =RANK(C3,C$3:C$9,0).

Interpretation:

1. This function is a commonly used sorting function, which can sort data in ascending and descending order.

　2. However, the shortcoming of this function is also very obvious, that is, when the sorted data is the same, the ranking will be filled in a "jumping manner". For example: There are two 4th places in the example, and then jump directly to the 6th place. No. 5 is not shown.

2. Chinese sorting: SUMPRODUCT function.

Purpose: Sort sales in descending order.

Method: Enter the formula in the target cell: =SUMPRODUCT(($C$3:$C$9>C9)/COUNTIF($C$3:$C$9,$C $3:$C$9)) 1.

Interpretation:

1. Using the SUMPRODUCT function to implement the sorting function can effectively avoid the shortcomings of the RANK function. The sorting results are more in line with our habits. This method Often referred to as "Chinese sorting".

　2. To achieve the ascending sort function: In fact, you only need to slightly modify the above formula. Just modify the order of comparison values. The formula is:

=SUMPRODUCT((C9>$C$3:$C$9)/COUNTIF($C$3:$C$9,$C$3:$C$9)) 1.

3. Sorting within the group: IF function.

Purpose: Sort the selected subjects.

Method: Enter the formula in the target cell: =IF(B3"",1,E2 1).

Interpretation:

Determine whether the value of the name column cell is empty. If it is not empty, return 1, otherwise Returns the value 1 of the previous cell.

4. Conditional sorting: COUNTIF function.

Purpose: Sort the salespersons in the specified area.

Method: Enter the formula in the target cell: =COUNTIF(B$3:B3,B3).

Interpretation:

1. Count the number of values ​​​​equal to the second parameter in the current cell range.

　2. Use the filtering function to quickly implement the sorting function.

5. Sort merged cells: MAX function.

Purpose: Quickly fill in serial numbers for irregular merged cells.

Method: Enter the formula in the target cell: =MAX(A$2:A2) 1.

Interpretation:

1. Because the merged cells are irregular, quick filling cannot be achieved.

　2. Use the MAX function to calculate the maximum value within the current range of the previous cell, and use 1 as the auxiliary value to quickly realize the serial number filling problem of irregular merged cells.

The above is the detailed content of What are the sorting formulas in excel. For more information, please follow other related articles on the PHP Chinese website!