Summarize the usage of several Excel2021 special functions

This article brings you relevant knowledge about excel, which mainly introduces the typical usage of several special functions, including SEQUENCE function, SORT function, SORTBY function, UNIQUE function, FILTER function and so on, I hope it will be helpful to everyone.

Related learning recommendations: excel tutorial

Today I will share with you the typical usage of several special functions in Excel 2021. With These functions make originally complex calculations increasingly simpler.

1. SEQUENCE function

The function of the SEQUENCE function is to generate a serial number based on the specified number of rows and columns. The commonly used writing method is:

=SEQUENCE(number of rows, [number of columns], [Start number], [Increment])

Application example:

As shown in the figure below, in order to facilitate printing, the names in column A need to be converted into multiple rows and columns.

Enter the following formula in cell D6 and press Enter:

=INDEX(A:A,SEQUENCE(E3,E4,2))&””

First use the SEQUENCE function to get a multi-row and multi-column serial number starting from 2 based on the number of rows and columns specified in cells E3 and E4. Then use the INDEX function to return the content of the corresponding position in column A.

2. SORT function

The function of the SORT function is to sort the specified rows and columns in the data area. The commonly used writing method is:

=SORT(data area,[pair no. Sort by several columns (rows)], [Ascending or descending order], [Sort by row or column])

Application example:

As shown in the figure below, it is based on the number of sales units in column C , use the formula to get the sorted sales records. Enter the following formula in cell E2 and press Enter.

=SORT(A2:C17,3,-1)

In this example formula, the third parameter uses -1, indicating descending order, use 1 means sorting in ascending order.

The fourth parameter is omitted, indicating sorting in the column direction.

3. SORTBY function

The function of SORTBY is to sort a certain area according to multiple sets of conditions. The commonly used writing method is:

=SORTBY (data area, to be sorted Column 1, [Ascending order/Descending order], [Column 2 to be sorted], [Ascending order/Descending order]..)

Application example:

As shown in the figure below, it should be based on the sales office and The two indicators of sales sets are used to obtain the sorted sales records using formulas. E2 enter the following formula and press Enter.

=SORTBY(A2:C17,A2:A17,1,C2:C17,-1)

4、UNIQUE function

The UNIQUE function is used to extract unique values ​​from the data table. The working method is similar to the function of deleting duplicate values. The commonly used writing method is:

=UNIQUE(data source,[by row /Sort by column], [Extract only content that appears once or extract all records])

Application example:

As shown in the figure below, it is necessary to extract a list of unique personnel from the duty records .

Enter the following formula in cell E2 and press Enter:

=UNIQUE(B2:C8)

##5, FILTER function

The function of the FILTER function is to extract records from the list according to the specified conditions. The conventional usage is: =FILTER(data area, condition area=specified conditions, content returned when the conditions are not met)

Application example:

As shown in the figure below, it is a detailed list of Qingming duty fees for a company. It is necessary to specify the department according to the G2 cell and return all records of the department.

Enter the following formula in cell F6 and press Enter:

=FILTER(A1:D11,A1:A11=G2)

6. Extract unique values ​​of specified conditions

As shown in the figure below, based on the specified area in cell G1, extract a list of unique products in the area from the table on the left.

Enter the following formula in cell F4 and press Enter:

=UNIQUE(FILTER(D2:D17,A2:A17=G1))

The meaning of the formula is to use UNIQUE to obtain unique records based on the extraction by the FILTER function.

7. Extract multiple records that meet multiple conditions

As shown in the figure below, it is necessary to specify the department according to the G2 cell and the position specified in the G3 cell. Returns all records that match these two criteria.

Enter the following formula in cell F6 and press Enter:

=FILTER(A1:D11,(A1:A11=G2)*(B1:B11=G3))

This formula is similar to the fifth example. Multiplying the two conditions means they are met at the same time. Using this method, we can add multiple sets of different conditions as needed.

Related learning recommendations:

excel tutorial

The above is the detailed content of Summarize the usage of several Excel2021 special functions. For more information, please follow other related articles on the PHP Chinese website!