Office hacks: Excel 2019 new functions to speed up the office

We know that Excel functions can greatly improve our work efficiency, and from the beginning of Excel 2019 in a number of new functions, let us take a look at these new functions have what function.

Do not fear multiple conditions – IFS function and SWITCH function

When we use the IF function to determine the conditions of multiple data, often need to nest multiple IF functions, so that the function is more complex to write, and not easy to understand. For example, teachers in the judgment of student performance to use the excellent, good, medium, failing, etc., the conventional method is through “= IF (A1>=90, “excellent”, IF (A1>=80, “good”, IF (A1>=60, “medium”, “failing”)))” to make a judgment (Figure 1).

201923xhs1

Figure 1 traditional IF function multi-level nesting

Excel 2019 the new IFS function is much simpler, its format is “IFS (condition 1, result 1, [condition 2], [result 2], … [condition 127], [result 127])”. Therefore, for the above multiple conditions of judgment, now just enter the function “=IFS (B2<60, “failed”, B2<80, “in”, B2<90, “good”, B2>=90, “excellent”)” in cell C2, you can complete the judgment (Figure 2).

201923xhs2

Figure 2 IFS function judgment

Of course, there are many similar multi-conditional judgments in actual work, such as clothing model is indicated by S, M, L, XL, XXL from to large, in order to facilitate customer identification, it is now necessary to automatically add remarks after the original model number to indicate the respective Chinese model number (Figure 3).

201923xhs3

Figure 3 Example data

For this fixed name of the multi-conditional judgment, with the new SWITCH function can be quickly achieved. In cell B2, enter the function “=SWITCH(MID(A2,FIND(“/”,A2)+1,FIND(“-“,A2)-1-FIND(“/”,A2)), “S”, “Small(S)”, “M”, “Medium(M)”, “L”, “Large(L)”, “XL”, “Large(+)” , “XXL”, “Large (++)”, “XXXL”, “Large (+++)”)”, and then fill down (Figure 4).

201923xhs4

Figure 4 SWITCH function multi-conditional judgment

Tip: The SWITCH function can be used to judge multiple expressions side by side, as you can see in the example to automatically judge in column B. Here it involves S → XXL five conditions, and the corresponding model number needs to be extracted from the middle of the name and added to the Chinese description. Therefore, here first use the MID function to extract the characters between “/” and “-” in the model characters (that is, similar to S, L characters), character extraction is achieved through the nested Find function, followed by “S”, “small (S)” means that the juxtaposition of multiple conditions The actual content is filled in according to the requirements of the notes.

Data concatenation is simpler – TEXTJOIN function

In daily operations, we often need to integrate multiple data together due to statistical needs. For example, in the year section results statistics, as a section leader often need to count the number of excellent scores in each class, and need to bring these lists together to facilitate teachers to read out the statistics, the effect is similar to the following table (Figure 5).

201923xhs5

Figure 5 sample table

From the sample data can be seen, here the first condition is to filter the 80 points (including) or more students, but also according to the class for the breakdown, and finally use “,” to summarize them in a cell. The conventional method is to use the VLOOKUP function to extract the names of people, then use COUNTIF to the range of data statistics, and finally use the “&” hyphen to connect, the operation is very tedious. Now with the TEXTJOIN function nested IF function can quickly find the answer.

Position to cell E2, enter the formula “= TEXTJOIN (“,”, TRUE, IF (($ B $ 2: $ B $ 10 = D2) * ($ C $ 2: $ C $ 10) >= 80, $ A $ 2: $ A $ 10, “”))”, after the input is complete, press Ctrl + Shift + Enter key to confirm the input, you can see that the function will automatically list each class to meet the requirements of the students, and the use of “,” for segmentation, drop-down function can complete the statistics of other class data (Figure 6).

201923xhs6

Figure 6 TEXTJOIN function to connect characters

Tip: the first parameter “,” that the joint character connection symbol, the second parameter “TRUE” that ignore the null value. The third parameter is a nested IF function, the first parameter “($B$2:$B$10=D2)” that when D2 cell conditions exist in B2:B10 (that is, belong to a class of students), while using “($C$2:$C$10)>=80 ” for judgment, if the score meets the conditions, then the name is displayed in cell E2 (otherwise displayed as empty), and finally use “,” joint in a cell.

Extreme values to find more efficient – MAXIFS/MINIFS function

Daily work often to meet certain conditions of the data to obtain the maximum or minimum value, but the previous version does not have a direct function can be obtained. For example, in order to improve the treatment of technicians, the company now needs to find the title of “Assistant Engineer” in the salary table, the lowest wage employees to see the minimum wage of assistant workers, in order to make adjustments. For this type of conditional extremes to obtain, most of the previous use of MIN nested IF function to obtain, the operation is more complex, now use MINIFS function can quickly obtain. Positioned to cell D2, enter the formula “= MINIFS (C2: C8, B2: B8, “Assistant Engineer”)”, that in the salary column, according to the title of Assistant Engineer conditions in column B to find and display the minimum value. If the maximum value is required, MINIFS can be replaced with MAXIFS (Figure 7).

201923xhs7

Figure 7 MINIFS for extreme values

QQ图片20210420165237

Leave a Comment