Summary of the use of common functions in Excel

This article brings you relevant knowledge about excel, which mainly organizes issues related to the use of commonly used functions, including the AND function, AVERAGE function, COLUMN function, etc. The following is Let's take a look, hope it helps everyone.

Related learning recommendations: excel tutorial

1. AND function

Function:

Return logical value: If all parameter values ​​are logical "TRUE", then return logical "TRUE", otherwise return logical "FALSE".

Syntax: AND(logical1,logical2,…)

The parameters represent the condition value or expression to be tested.

Application example:

Enter the formula in cell C5:

=AND(A5>=60,B5>=60)

If C5 Returning TRUE means that the values ​​in A5 and B5 are both greater than or equal to 60. If it returns FALSE, it means that at least one of the values ​​in A5 and B5 is less than 60.

Special reminder:

If the specified logical condition parameter contains a non-logical value, the function returns an error value.

2. AVERAGE function

Function:

Find the arithmetic average of all parameters.

Syntax: AVERAGE(number1,number2,...)

The parameter is the value or reference cell range that needs to be averaged.

Application example:

Enter the formula in cell B8: =AVERAGE(B7:D7,F7:H7,7,8), you can find the area from B7 to D7, F7 to The value in the H7 area and the average of 7 and 8.

Special reminder:

If the reference range contains "0" value cells, they will be counted; if the reference range contains blank or character cells, they will not be counted.

3. COLUMN function

Function:

Display the column label value of the referenced cell.

Syntax: COLUMN(reference)

The parameter reference is the referenced cell.

Application example:

Enter the formula in cell C5: =COLUMN(B5). After confirmation, it will be displayed as 2 (i.e. column B).

Special reminder:

If you enter the formula: =COLUMN() in cell B3, 2 will also be displayed; correspondingly, there is a function that returns the row label value—— ROW(reference).

4. COUNTIF function

Function:

Count the number of cells in a certain cell range that meet the specified conditions.

Syntax: COUNTIF(Range,Criteria)

Parameter description: The first parameter is the cell range to be counted; the second parameter represents the specified conditional expression.

Application example:

Enter the formula in cell C2: =COUNTIF(B2:B5,”>=80″)

Can count cells B2 to B5 In the cell range, the number of cells with a value greater than or equal to 80.

Special reminder:

The second parameter allows the use of wildcards, and there are many application methods. You can click on the avatar in the upper right corner to view historical messages, or you can visit weixin.sogou.com on the PC. Search the official account iexcelhome to view historical messages more conveniently and quickly.

5. DATEDIF function

Function:

Calculate and return the difference between two date parameters.

Syntax: =DATEDIF (start date, end date, return type)

Application example:

Enter the formula in cell C2: =DATEDIF(A2, TODAY(),"y")

Returns the difference between the current date of the system and the date in cell A2, and returns the number of years.

If M is used as the third parameter, the number of months between the two dates will be returned.

Special reminder:

This is a hidden function in Excel, which can be input and used directly. It is very effective for calculating age, length of service, etc.

6. FREQUENCY function

Function:

Returns the frequency distribution of data in a certain area in a vertical array.

Syntax: FREQUENCY(data_array,bins_array)

Parameter description: Data_array represents a set of data or cell range used to calculate frequency; Bins_array represents a column of values ​​separated by the previous array.

Application example:

As shown in the figure, select the cell range and enter the formula:

=FREQUENCY(A3:A9,C3:C5)

Press the Ctrl Shift Enter key combination to count the number of people in each segment.

The four values ​​returned by the formula respectively indicate that in the cell area A3:A9, there are three values ​​less than or equal to 50, and one value between 51 and 60. , 2 are between 61 and 80, and 1 is greater than 80.

Special reminder:

The above input is an array formula. After the input is completed, you need to press the Ctrl Shift Enter key combination to confirm. After confirmation, a pair of curly brackets { } will appear at both ends of the formula. , this brace cannot be entered directly.

7. IF function

Function: Return the corresponding content based on the true or false result of the logical judgment of the specified condition.

Grammar:

=IF (judgment condition, content returned if the condition is true, content returned if the condition is false)

Application example:

in Enter the formula in cell C2: =IF(C3="Am I handsome?", "Meets the requirements", "Does not meet the requirements")

If cell C3 equals "Am I handsome?", cell C2 will display the words "Meets the requirements", otherwise it will display the words "Does not meet the requirements".

8. INDEX function

Function:

Returns the element value in the list or array. This element is determined by the index value of the row number and column number.

Common syntax: INDEX(array,row_num,column_num)

Parameter description: Array represents a cell range or array constant; Row_num represents the specified row number (if row_num is omitted, column_num is required ); Column_num represents the specified column number (if column_num is omitted, row_num must be present).

Application example:

1. Enter the formula in cell F2: =INDEX(A1:D20,9,5), then the 9th cell range from A1 to D20 will be displayed. The contents of the cell at the intersection of row and column 5.

2. Enter the formula =INDEX(A1:A20,9) in cell E2 to display the contents of the 9th row of cells in the range of cells A1 to A20.

Special reminder:

The row number parameter (row_num) and column number parameter (column_num) here are relative to the referenced cell range, not in the Excel worksheet Row or column number.

There are many commonly used functions. Only after we are familiar with a single function can we use the nesting of functions to make the function more powerful and improve our work efficiency.

Related learning recommendations: excel tutorial

The above is the detailed content of Summary of the use of common functions in Excel. For more information, please follow other related articles on the PHP Chinese website!