Excel常用函数大全

We often use Excel to organize and analyze data by creating tables. In order to automate the process of calculating and summarizing data in the tables, we frequently utilize the functions in Excel. Here, we have compiled the most commonly used functions in Excel, their functions, usage, and examples of their practical applications, along with detailed explanations.

1. ABS Function

Function Name: ABS

Main Function: Returns the absolute value of a number.

Usage Format: ABS(number)

Parameter Explanation: 'number' represents the number or cell reference for which the absolute value needs to be calculated.

Application Example: If the formula '=ABS(A2)' is entered in cell B2, then regardless of whether a positive number (e.g., 100) or a negative number (e.g., -100) is entered in cell A2, B2 will display the positive value (e.g., 100).

Special Note: If the 'number' parameter is not a numerical value but a character (e.g., A), B2 will return the error value "#VALUE!".

2. AND Function

Function Name: AND

Main Function: Returns a logical value: TRUE if all parameter values are TRUE, FALSE otherwise.

Usage Format: AND(logical1, logical2, ...)

Parameter Explanation: 'Logical1, Logical2, Logical3,...' represent the conditions or expressions to be tested, up to a maximum of 30.

Application Example: If the formula '=AND(A5>=60, B5>=60)' is entered in cell C5, and the result is TRUE, it means that both A5 and B5 have values greater than or equal to 60. If the result is FALSE, it means that at least one of the values in A5 and B5 is less than 60.

Special Note: If the specified logical condition parameters contain non-logical values, the function will return the error value "#VALUE!" or "#NAME".

3. AVERAGE Function

Function Name: AVERAGE

Main Function: Calculates the arithmetic mean of all parameters.

Usage Format: AVERAGE(number1, number2, ...)

Parameter Explanation: 'Number1, Number2, ...' represent the values or cell references for which the average needs to be calculated, up to a maximum of 30.

Application Example: If the formula '=AVERAGE(B7:D7, F7:H7, 7, 8)' is entered in cell B8, it will calculate the average of the values in the ranges B7 to D7, F7 to H7, and the numbers 7 and 8.

Special Note: If the referenced range contains cells with a value of "0," they will be included in the calculation. If the referenced range contains blank or text cells, they will not be included in the calculation.

4. COLUMN Function

Function Name: COLUMN

Main Function: Displays the column number of the referenced cell.

Usage Format: COLUMN(reference)

Parameter Explanation: 'Reference' is the reference to the cell.

Application Example: If the formula '=COLUMN(B11)' is entered in cell C11, it will display "2" (which is the column B).

Special Note: If the formula '=COLUMN()' is entered in cell B11, it will also display "2". There is also a corresponding function ROW(reference) that returns the row number value.

5. CONCATENATE Function

Function Name: CONCATENATE

Main Function: Concatenates multiple text characters or data from cells and displays them in a single cell.

Usage Format: CONCATENATE(Text1, Text...)

Parameter Explanation: 'Text1, Text2, ...' are the text characters or cell references to be concatenated.

Application Example: If the formula '=CONCATENATE(A14, "@", B14, ".com")' is entered in cell C14, it will combine the characters from cell A14, the symbol "@", the characters from cell B14, and the string ".com" into one complete text in cell C14.

Special Note: If the parameters are not cell references and are in text format, they should be enclosed in double quotation marks. If we modify the above formula to '=A14&"@"&B14&".com"', we will achieve the same result.

6. COUNTIF Function

Function Name: COUNTIF

Main Function: Counts the number of cells in a range that meet a specific condition.

Usage Format: COUNTIF(Range, Criteria)

Parameter Explanation: 'Range' represents the range of cells to be counted; 'Criteria' represents the specified condition expression.

Application Example: If the formula '=COUNTIF(B1:B13, ">=80")' is entered in cell C17, it will count the number of cells in the range B1 to B13 that have values greater than or equal to 80.

Special Note: Blank cells in the referenced range are allowed.

7. DATE Function

Function Name: DATE

Main Function: Returns the date based on the specified numerical values.

Usage Format: DATE(year, month, day)

Parameter Explanation: 'Year' is the specified year value (less than 9999); 'Month' is the specified month value (can be greater than 12); 'Day' is the specified day number.

Application Example: If the formula '=DATE(2003, 13, 35)' is entered in cell C20, it will display "2004-2-4" (the date has been adjusted due to the values provided).

Special Note: In the above formula, since the month is 13 (one month too many), it is adjusted to February 4th, 2004 (1 month into the next year); the day is 35 (4 days too many in February), so it is adjusted accordingly.

8. DATEDIF Function

Function Name: DATEDIF

Main Function: Calculates and returns the difference between two date parameters.

Usage Format: =DATEDIF(date1, date2, "y"), =DATEDIF(date1, date2, "m"), =DATEDIF(date1, date2, "d")

Parameter Explanation: date1 represents the earlier date, date2 represents the later date; 'y' ('m' or 'd') requests the number of years (months or days) difference between the two dates.

Application Example: If the formula '=DATEDIF(A23, TODAY(), "y")' is entered in cell C23, it will calculate and return the number of years between the date in cell A23 and the current system date.

Special Note: This is a hidden function in Excel and cannot be found in the function wizard. It can be used directly and is very effective for calculating ages, lengths of service, etc.

9. DAY Function

Function Name: DAY

Main Function: Returns the day of the month for a specified date or cell with a date.

Usage Format: DAY(serial_number)

Parameter Explanation: serial_number represents the specified date or cell with a date.

Application Example: If the formula '=DAY("2003-12-18")' is entered, the result will be "18".

Special Note: If a specific date is provided, it should be enclosed in double quotation marks.

10. DCOUNT Function

Function Name: DCOUNT

Main Function: Returns the number of numeric cells in a column of a database or list that meets specified criteria.

Usage Format: DCOUNT(database, field, criteria)

Parameter Explanation: 'Database' represents the range of cells to be counted; 'Field' represents the data column to be used by the function (with a header in the first row); 'Criteria' contains the criteria for the cells to be counted.

Application Example: As shown in the example, in cell F4, the formula '=DCOUNT(A1:D11,"语文",F1:G2)' is entered to count the number of cells in the "语文" column with scores greater than or equal to 70 and less than 80 (equivalent to counting the number of students within a specific score range).

11. FREQUENCY Function

Function Name: FREQUENCY

Main Function: Returns an array of values in a vertical column that represents the frequencies of values in a data set.

Usage Format: FREQUENCY(data_array,bins_array)

Parameter Explanation: 'Data_array' represents the data or range of cells to calculate frequencies for; 'Bins_array' represents the one-column array of numerical values that the data array will be divided into.

Application Example: As shown in the example, by selecting cells B32 to B36 and entering the formula '=FREQUENCY(B2:B31,D2:D36)', and then confirming by pressing Ctrl+Shift+Enter, the frequencies of values in the range B2 to B31 divided by the values in range D2 to D36 will be calculated.

12. IF Function

Function Name: IF

Main Function: Returns a value based on the evaluation of a condition.

Usage Format: =IF(Logical, Value_if_true, Value_if_false)

Parameter Explanation: 'Logical' represents the logical expression to be evaluated; 'Value_if_true' represents the value to be returned if the logical condition is TRUE, or it will return "TRUE" if omitted; 'Value_if_false' represents the value to be returned if the logical condition is FALSE, or it will return "FALSE" if omitted.

Application Example: If the formula '=IF(C26>=18,"符合要求","不符合要求")' is entered in cell C29, it will display "符合要求" if the value in cell C26 is greater than or equal to 18, and "不符合要求" if otherwise.

Special Note: The specified cell mentioned in "cell C29" in the example is just for reference. When using the function, there is no need to be restricted by it. The specific cells will depend on the example file attached.

13. INDEX Function

Function Name: INDEX

Main Function: Returns the value of an element in a list or array based on its row and column index.

Usage Format: INDEX(array, row_num, column_num)

Parameter Explanation: 'Array' represents the range of cells or an array constant; 'Row_num' indicates the specified row number (if 'Row_num' is omitted, 'Column_num' must be specified); 'Column_num' represents the specified column number (if 'Column_num' is omitted, 'Row_num' must be specified).

Application Example: As shown in the example, in cell F8, the formula '=INDEX(A1:D11,4,3)' is entered to display the content at the intersection of row 4 and column 3 (cell C4) in the range A1 to D11.

14. INT Function

Function Name: INT

Main Function: Rounds a number down to the nearest integer.

Usage Format: INT(number)

Parameter Explanation: 'Number' represents the numeric value to round down or the cell reference containing a numeric value.

Application Example: If the formula '=INT(18.89)' is entered, it will display "18".

Special Note: The function does not round up; it always rounds down. If the formula is changed to '=INT(-18.89)', it will return "-19".

15. ISERROR Function

Function Name: ISERROR

Main Function: Tests if a value returned by a formula is an error. Returns TRUE if it is an error, FALSE otherwise.

Usage Format: ISERROR(value)

Parameter Explanation: 'Value' represents the value or expression to be tested.

Application Example: If the formula '=ISERROR(A35/B35)' is entered, it will return TRUE if cell B35 is empty or contains "0", indicating an error in the division operation; it will return FALSE if the division operation is successful.

Special Note: This function is often used in conjunction with the IF function. If the formula is changed to '=IF(ISERROR(A35/B35),"",A35/B35)', it will display nothing if there is an error in the division, and the result of the division if there is no error.

16. LEFT Function

Function Name: LEFT

Main Function: Extracts a specified number of characters from the beginning of a text string.

Usage Format: LEFT(text, num_chars)

Parameter Explanation: 'Text' is the string from which characters need to be extracted; 'Num_chars' is the number of characters to extract.

Application Example: If cell A38 contains the string "我喜欢天极网", and the formula '=LEFT(A38, 3)' is entered in cell C38, it will display "我喜欢".

Special Note: The function name "LEFT" corresponds to extracting characters from the left side of a string, following the English meanings of Excel functions.

17. LEN Function

Function Name: LEN

Main Function: Counts the number of characters in a text string.

Usage Format: LEN(text)

Parameter Explanation: 'Text' is the text string to be counted.

Application Example: If cell A41 contains the string "我今年28岁", and the formula '=LEN(A40)' is entered in cell C40, it will display "6" as the character count.

Special Note: Each character, whether full- or half-width, is counted as "1" using the LEN function. There is another function LENB that assigns "1" to half-width characters and "2" to full-width characters.

18. MATCH Function

Function Name: MATCH

Main Function: Returns the relative position of an item in an array that matches a specified value based on a specified match type.

Usage Format: MATCH(lookup_value, lookup_array, match_type)

Parameter Explanation: 'Lookup_value' is the value to search for in the data table; 'Lookup_array' is the range of cells that may contain the values to be found; 'Match_type' specifies how to find the value (choose -1 for less than, 0 for exact match, 1 for greater than).

Application Example: As shown in the example, the formula '=MATCH(E2,B1:B11,0)' is entered in cell F2 to return the result "9".

Special Note: Lookup_array must be a single column or row.

19. MAX Function

Function Name: MAX

Main Function: Returns the largest number from a group of numbers.

Usage Format: MAX(number1, number2, ...)

Parameter Explanation: 'Number1, Number2, ...' represent the values or cell references to find the maximum value from, up to a maximum of 30.

Application Example: If the formula '=MAX(E44:J44, 7, 8, 9, 10)' is entered, it will return the largest value from the range E44 to J44 and the numbers 7, 8, 9, and 10.

Special Note: Text or logical values in the parameters will be ignored.

20. MID Function

Function Name: MID

Main Function: Extracts a specified number of characters from a text string starting at a specified position.

Usage Format: MID(text, start_num, num_chars)

Parameter Explanation: 'Text' is the string from which characters need to be extracted; 'Start_num' is the starting position for extraction; 'Num_chars' is the number of characters to extract.

Application Example: If cell A47 contains the string "我喜欢天极网", and the formula '=MID(A47, 4, 3)' is entered in cell C47, it will display "天极网".

Special Note: Commas (",") should separate the parameters in English.

21. MIN Function

Function Name: MIN

Main Function: Returns the smallest number from a group of numbers.

Usage Format: MIN(number1, number2, ...)

Parameter Explanation: 'Number1, Number2, ...' represent the values or cell references to find the minimum value from, up to a maximum of 30.

Application Example: If the formula '=MIN(E44:J44, 7, 8, 9, 10)' is entered, it will return the smallest value from the range E44 to J44 and the numbers 7, 8, 9, and 10.

Special Note: Text or logical values in the parameters will be ignored.

22. MOD Function

Function Name: MOD

Main Function: Returns the remainder of a division operation.

Usage Format: MOD(number, divisor)

Parameter Explanation: 'Number' is the dividend; 'Divisor' is the divisor.

Application Example: If the formula '=MOD(13, 4)' is entered, it will display the remainder as "1".