Hello friends, today I would like to share with you the typical usage of several commonly used function formulas.
1. Extract the unique list with specified conditions
As shown in the figure below, a company organizes sports competitions, and the same employee has multiple competitions.
I would like to extract the list of participants from the sales department from the list on the left.
F2 cell, enter the following formula, press enter.
=UNIQUE(FILTER(A2:A11,C2:C11=E2))
In this example, the FILTER function is used to extract all records that meet the condition C2:C11=E2, and then the UNIQUE function is used to extract the unique records.
2. Custom sorting
As shown in the figure below, you want to sort the employee information on the left side according to the job comparison table in column F.
Enter the following formula in cell H2 and press enter.
=SORTBY(A2:B21,MATCH(B2:B21,F:F,))
The MATCH(B2:B21,F:F,) part of the formula calculates the position of each job title in column F in cell B2:B21 respectively.
Next, use the SORTBY function to sort the content in A2:B21 based on these location information.
3. Extract the list of employees in multiple rows and columns
As shown in the figure below, you want to extract the list of unique personnel in the multi-row and multi-column duty schedule.
Enter the following formula in cell G2 and press enter:
=UNIQUE(TOCOL(B2:E8,1))
TOCOL(B2:E8,1) part, on the premise of ignoring empty cells, converts the names in the B2:E8 cell range into a column, and then uses UNIQUE to obtain the unique value.
4. Repeat the content as many times as you specify
As shown in the figure below, you want to repeat the label name of column A according to the number of repetitions in column B, and the final effect is shown in column D.
Enter the following formula into cell D2 and press enter.
=TOCOL(IF(B2:B5>=COLUMN(A:Z),A2:A5,0/0),2)
IN THIS PART OF THE FORMULA, IF(B2:B5>=COLUMN(A:Z),A2:A5,0/0), COMPARE THE VALUE IN B2:B5 WITH THE COLUMN NUMBER 1~26 OBTAINED BY COLUMN(A:Z), IF IT IS GREATER THAN OR EQUAL TO THE COLUMN NUMBER, THE LABEL NAME IN A2:A5 IS RETURNED, OTHERWISE 0/0 IS RETURNED, THAT IS, THE ERROR VALUE #DIV/0!.
The result of this part of the calculation is shown in the figure below:
The white number on the left side of the figure is the number of repetitions of column B, and the yellow number at the top is the column number of 1~26.
Next, use the TOCOL function, ignore the wrong values, and convert the above array to a column.
5. Automatic increase or decrease of serial number
As shown in the figure below, enter the following formula in cell A2 to generate the ordinal number that changes with the increase of data.
=SEQUENCE(COUNTA(B:B)-1)
COUNTA(B:B)-1 PART, COUNT THE NUMBER OF NON-EMPTY CELLS IN COLUMN B. Subtract 1 to get the actual number of records excluding the header row.
The SEQUENCE function is used to generate the sequence numbers of the specified rows and columns. In this example, the number of rows that generate the sequence number is specified by the result of COUNTA(B:B)-1. That is, how many rows of data there are in column B, the SEQUENCE function will generate the sequence number of the corresponding number of rows.
Okay, that's all for today's content, I wish you all a good mood for the day~~
Graphic production: Zhu Hongzhong