The following is the usage of a set of commonly used Excel function formulas. Learn these routines to make work more efficient, by going up one flight of stairs. 1, conditional judgment of IF function IF function is one of the most commonly used judgment functions, which can complete the judgment of either/or. As shown in the figure below, the standard of assessment score is 9 points, and it is necessary to judge whether the assessment result in column B is qualified. = IF(B4 & gt； =9, "qualified", "unqualified") If, it is equivalent to Mandarin? What if? The general usage is: IF (the condition of judgment, the result when the condition is met, the result when the condition is not met) 2. Multi-condition judgment is shown in the figure below. If the department is mainly engaged in production and post operation, there is a high temperature subsidy. In column D, the formula =IF(AND(B2= "production", C2= "main business"), "Yes" and "No" are used to judge two conditions. If they meet at the same time, if the function returns? what's up Otherwise, it is nothing. 3. Conditional summation is as shown in the figure below. Calculate the total score of 1 class with SUMIF function: =SUMIF(D2:D5, F2, C2: C5). The usage of sumif is: =SUMIF (conditional area, specifying summation condition, summation area), which is described in popular terms: If the class in D2:D5 area is equal to the class in F2 cell? Class one? Sum the regions corresponding to C2:C5 cells. 4. Multi-conditional summation is shown in the figure below. It is necessary to count the total subsidies of production departments and posts. The formula is =SUMIFS (D2: D9, B2: B9, F2, C2: C9, G2). The usage of sumifs is = sumifs (summation area, conditional area 1, specified summation condition 1, conditional area 2, specified summation condition 2,) 5. The condition count is as follows, and the number of transactions in the designated store should be counted. That is, calculate how many specified store names are in column B. = countif (B2: B 12, E3) Countif function calculates the number of cells in the range that meet the specified conditions. The general usage is: =COUNTIF (conditional area, specifying conditions) 6. Multi-condition counting requirements: the formula of the number of people whose statistical department is production and the position is the main operation is: =COUNTIFS(B2:B9, F2, C2:C9, G2) COUNTIFS function to count the number of cells that meet multiple specified conditions in the condition area. The general usage is: =COUNTIFS (conditional area 1, specified condition 1, conditional area 2, specified condition 2) 7. The conditional lookup VLOOKUP function has always existed like a popular lover. The syntax of the function is: VLOOKUP (who to look for, where to look for, which column to return, accurate or approximate), as shown in the figure below, and find out where the employee's name is in the F5 cell. = vlookup ($ f $5, $ b $ 1: $ d $10,2,0) When using this function, you should pay attention to the following points:1,and the fourth parameter is generally searched with 0 (or FASLE) in an exact matching way. 2. The column number in the third parameter cannot be understood as the actual column number in the worksheet, but the column number of the return value within the search range. 3. If the lookup value is inconsistent with the data type of the data area keyword, the error value #N/A will be returned. 4. The search value must be in the first column of the query area. 8. Multi-standard search is shown in the figure below. It is required that the inquiry department is production and the position is the name of the minister. The formula is: =LOOKUP( 1, 0/((B2: B9 = F2) * (C2: C9 = G2)), and A2: A9) The lookup function is written as: = lookup (1, 0/ ((conditional area 1=). Calculate the text formula as follows. To calculate a text formula in a cell, click the first cell to enter the formula. Define &: calculate = EVALUATE(C2), then enter the formula in the cell: = calculate 10, and merge the contents of multiple cells. To connect and merge the contents of multiple cells, you can use&; Symbol complete. As shown below, to merge the name of column A and the phone number of column B, you can use the formula: = A2 &；; b $ 1 & amp； B2 1 1。 Merging formatted cell contents When merging formatted content, Excel will merge in normal format by default, but if it is a date, time or other formatted value, the result will be disappointing: How can I connect the required strings correctly? It's actually quite simple. C2 formula is: = A2 &；; TEXT (B2, "D day, M month, Y year") first uses the text function to change the date of column B into a string with a specific style, and then connects it with the name of column A, which becomes the final style. 12. Compare uppercase and lowercase words. If you enter uppercase and lowercase words in cells A 1 and A2 respectively, Excel will default that they are the same when judging by the following formula: =A2=B2. To distinguish case, you can use the formula: =EXACT(A2, B2) EXACT function to distinguish case, but ignore the differences in format. 13. Extract the names in the mixed content as shown below. To extract names from the A-pillar name phone, in addition to using the high-version auto-fill function, you can also use the formula: =LEFT(A2, LENB(A2)-LEN(A2)) LENB function counts the number of characters of each Chinese character (double-byte characters) by 2, while LEN function counts all characters by 1. So what? LENB(A2)-LEN(A2)？ The returned result is the number of Chinese characters in the text string. The LEFT function starts with the first character of the text string, returns a specified number of characters, and finally extracts the employee name. 14. The formula for extracting the date of birth according to the ID number is =1* text (MID (b2,7,8), "0-00-00"). First, the eight characters representing the date of birth are extracted from the 7th bit of the B2 cell by the mid function. The result is: "197802 15", and then the TEXT function is used to convert the string into the date style: "1978-02- 15", and then the actual date is converted by * 1 calculation. Finally, format it as a date. 15. Replace some phone numbers as shown below. The middle four digits of the mobile phone number should be replaced by asterisks, and the formula is: = SUBSTITUTE (b2, mid (b2, 4, 4), * *,1). The usage of the substitute function is: substitute (text to be replaced, old text, new text, [which number to replace]) first uses the mid function to get the middle four digits of the number in column B. *****? Replace this part. The last parameter uses 1, which means that only the first match is replaced. For example, the telephone number in the ninth line is1380101,and the last four digits are the same as the middle four digits. If 1 is not specified, they will all be replaced. 16. The error value returned by the mask function formula usually returns some values, such as #N/A and #NAME? Masking these error values is actually very simple, just add an IFERROR function to the original formula. The usage of the IFERROR function is: =IFERROR (original formula, what is returned when an error occurs). If the formula is correct, the original calculation result will be returned; If the formula returns an error value, the display content specified by the user will be returned. 17, the ROUND function, which everyone must often use, is to round the numerical value according to the specified number of digits. For example, = round (8/9,3) is to round the calculation result of 8/9 to three decimal places, and the result is 0.889. 18, take the whole interval hours and calculate the interval hours between two hours. Time less than one hour is ignored, which is often used when calculating overtime. If you talk too much, you will burst into tears =TEXT(B2-B 1, "[h]")19. To extract the date value from the date and time data, you can use the following formula: =INT(A2) Continue to extract the time, just do the subtraction. Generating random numbers RANDBETWEEN can generate a set of random data within a specified range, which is really a great function for the vast number of quality inspection, supervision and statisticians. The usage of the function is =RANDBETWEEN (lower numerical limit, upper numerical limit). For example, the following formula is to generate a random number between 60 and 100: =RANDBETWEEN(60, 100).