Excel Common function Encyclopedia

When we use Excel to make table data, we often use its function function to automatically statistic the data in the table. In this paper, the functions of the most frequently used functions in Excel, and the application of these functions in practical applications are analyzed, and the detailed introduction is given.

　　1. Abs function 　　

Function Name: ABS

Main function: Find out the absolute value of the corresponding number.

Use format: ABS (number)

Parameter Description: number represents a value or a referenced cell that requires an absolute value.

Application Example: If you enter a formula in cell B2: =abs (A2), a positive number (such as 100) appears in B2 regardless of whether you enter a positive number (such as 100) or a negative number (such as -100) in the A2 cell.

Special reminder: If the number parameter is not a numeric value, but some characters (such as a, etc.), then the error value is returned in B2 "#VALUE! ”。

　　2, and function

Function Name: and

Main function: Returns the logical value: if all parameter values are logical true (TRUE), return the logical True (TRUE) and return the logical false (FALSE) instead.

Use format: and (Logical1,logical2, ...)

Parameter description: Logical1,logical2,logical3 ... : Represents the condition value or expression to be tested, up to 30.

Application example: In C5 cell input formula: =and (A5>=60,B5>=60), confirmed. If C5 returns True, the values in A5 and B5 are greater than or equal to 60, and if False, the values in A5 and B5 are at least one less than 60.

Special reminder: If the specified logical condition parameter contains a non logical value, the function returns the error value "#VALUE!" or "#NAME".

　　3. Average function 　　

Function Name: AVERAGE

Main function: Calculate the arithmetic mean value of all parameters.

Use format: AVERAGE (Number1,number2,......)

Parameter description: Number1,number2,...... : A value or reference cell (range) that requires an average value or no more than 30 parameters.

Application Example: Enter the formula in cell B8: =average (b7:d7,f7:h7,7,8), after confirmation, you can find the B7 to D7 region, F7 to the H7 area of the value and the average of 7, 8.

Special reminder: If the reference range contains a "0" value cell, it is counted, and if the reference range contains white space or character cells, it is not counted.

　　4, COLUMN function 　　

Function Name: COLUMN

Main function: Displays the column label value of the referenced cell.

Use format: COLUMN (Reference)

Parameter description: Reference is the referenced cell.

Application Example: Enter the formula in cell C11: =column (B11), which is displayed as 2 (that is, column B).

Special reminder: If you enter a formula in cell B11: =column (), it also shows 2, and a function--row (reference) that returns the label value of the row.

　　5. Concatenate function 　　

Function Name: concatenate

Main function: Connect multiple character text or data in a cell together, and display it in a cell.

Use format: Concatenate (Text1,text ...)

Parameter description: Text1, Text2 ... is the character text or reference cell that you want to connect to.

Application Examples: Enter the formula in cell C14: =concatenate (A14, @, B14,. com), after confirmation, you can connect the characters in the A14 cell, the characters in the @, B14 cells, and. com into a whole, and appear in the C14 cell.

Special reminder: If the parameter is not a referenced cell and is in text format, add double quotes in the English state to the parameter, and you can achieve the same goal if you change the formula to:=a14& @ &B14&. com.

　　6. countif function　

Function Name: COUNTIF

Main function: Counts the number of cells in a range that meet the criteria you specify.

Use format: COUNTIF (Range,criteria)

Parameter Description: Range represents the range of cells to be counted, and the criteria represents the specified conditional expression.

Application Example: Enter the formula in cell C17: =countif (b1:b13, ">=80"), after confirmation, you can count the number of cells in the B1 to B13 range of cells that are greater than or equal to 80.

Special reminder: There are blank cells appearing in the range of cells that are allowed to reference.

　　7, Date function 　　

Function Name: DATE

Main function: gives the date of the specified number.

Use format: DATE (Year,month,day)

Parameter description: Year is the specified number of years (less than 9999), month is the specified number of months (can be greater than);

Application Example: Enter the formula in cell C20: =date (2003,13,35), after confirmation, shows 2004-2-4.

Special reminder: As the above formula, the month is 13, more than one months, postponed to January 2004; The number of days is 35, 4 days more than the actual days of January 2004, and it is postponed to February 4, 2004.

　　8, Function name: DATEDIF

Main function: Calculates the difference that returns two date parameters.

Use format: =datedif (date1,date2, "Y"), =datedif (Date1,date2, "M"), =datedif (Date1,date2, "D")

Parameter Description: Date1 represents the previous date, Date2 represents a later date, and Y (M, d) requires that the number of years (months, days) of the difference between two dates be returned.

Application Example: Enter the formula in cell C23: =datedif (A23,today (), "Y"), confirm the difference between the system's current date [in today () and the date in the A23 cell, and return the number of years that are different.

Special reminder: This is a hidden function in Excel, is not found in the Function Wizard, you can directly input the use of computing age, seniority, etc. very effective.

　　9, Day function

Function Name: Day

Main function: Find the number of days in a specified date or a date in a reference cell.

Use Format: Day (Serial_number)

Parameter Description: Serial_number represents the specified date or referenced cell.

Application Example: Input formula: =day ("2003-12-18"), confirm, show 18.

Special reminder: If it is a given date, enclose it in double quotes in English.

　　10. DCount function

Function Name: DCOUNT

Main function: Returns the number of cells in a column in a database or list that meet specified criteria and contain numbers.

Use format: DCOUNT (Database,field,criteria)

Parameter Description: The database represents the range of cells that need to be counted; field represents the data column used by the function (must have a flag item in the first row); The criteria contains the range of cells for the condition.

Example: As shown in Figure 1, enter the formula in cell F4: =dcount (A1:d11, "language", F1:G2), after confirmation, you can find out in the "Language" column, the result is greater than or equal to 70, and less than 80 of the number of cells (equivalent to the number of fractional segments).

　

Special reminder: If the above formula is modified to: =dcount (A1:D11,,F1:G2), you can achieve the same purpose.