1, ABS function

Function name: ABS

Main function: find the absolute value of the corresponding number.

Usage format: ABS ($ NUMBER)

Parameter Description: Numbers represent cells that need absolute values or reference values.

Application example: If the formula =ABS(A2) is entered in cell B2, then whether a positive number (such as 100) or a negative number (such as-100) is entered in cell A2, cell B2 will display a positive number (such as100).

Special reminder: If the number parameter is not a numerical value, but some characters (such as a), the error value is "#VALUE!" Will be returned in B2. .

Step 2 act with discretion

Function names: and

Main function: return logical value: if all parameter values are logical "true", return logical "true", otherwise return logical "false".

Usage format: AND(logical 1, logical2, ...)

Parameter description: logic 1, logic 2, logic 3. ........................................................................................................................................................

Application example: enter the formula in C5 cell: = and (a5 >; =60, B5 & gt=60), and confirm. If C5 returns TRUE, the values in A5 and B5 are both greater than or equal to 60; If false, at least one of A5 and B5 is 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 value

Main function: Find the arithmetic average of all parameters.

Usage format: average (number 1, number 2, ...)

Parameter description: number 1, number 2, ...: average value or reference cell (area) is required, and there are no more than 30 parameters.

Application example: Enter the formula =AVERAGE(B7:D7, F7:H7, 7, 8) in cell B8, and after confirmation, you can get the values in B7-D7 and F7-H7 and the average value of 7 and 8.

Special reminder: If the reference area contains cells with a value of "0", it will be counted; If the reference area contains blank or character cells, it is not counted.

4. Column function

Function Name: Column

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

Use format: column (reference)

Parameter Description: The reference is the referenced cell.

Application example: enter the formula =COLUMN(B 1 1) in the cell C 1, and it will be displayed as 2 (that is, column B) after confirmation.

Special reminder: If you enter the formula =COLUMN () in cell B 1 1, 2 will also be displayed; Accordingly, there is a function that returns the row label value-row (reference).

5. Connection function

Function name: CONCATENATE

Main function: connect multiple character texts or data in a cell and display them in a cell.

Usage format: CONCATENATE(Text 1, Text ...)

Parameter Description: Text 1, Text2…… ...........................................................................................................................................................

Application example: input formula = concatenate (A 14, "@", B 14, ". Com "), and after confirmation, cells A 14, @, b 14 and. ..

Special reminder: If the parameter is not a referenced cell, but a text format, please put double quotes around the parameter in English. If the above formula is changed to = a14&; “@”& amp； b 14 & amp； ".com" can achieve the same purpose.

6.COUNTIF function

Function name: COUNTIF

Main function: count the number of cells in a cell area that meet the specified conditions.

Usage format: COUNTIF (scope, condition)

Parameter Description: Range indicates the range of cells to be counted; Criteria represents the specified conditional expression.

Application example: enter the formula in cell C 17: = countif (B 1: B 13, "> =80"), and after confirmation, you can count the number of cells with a value greater than or equal to 80 in the cell area of B 1 to B 13.

Special reminder: blank cells are allowed in the referenced cell area.

7. Date function

Function name: date

Main function: Give the date of the specified value.

Usage format: date (year, month, day)

Parameter Description: year is the specified year value (less than 9999); Month is the specified month value (which can be greater than12); Day is the specified number of days.

Application example: Enter the formula =DATE(2003,13,35) in C20 cell, and confirm to display 2004-2-4.

Special reminder: As the month in the above formula is 13, it was extended to 1 month in 2004, which is one month more; The number of days is 35 days, which is 4 days more than the actual number of days in June 2004, so it is postponed to February 4, 2004.

8. function name: DATEDIF

Main function: Calculate and return the difference between two date parameters.

Usage format: =DATEDIF(date 1, date2, "y"), =DATEDIF(date 1, date2, "m"), =DATEDIF(date 1, date2, "d").

Parameter description: date 1 indicates the previous date, and date2 indicates the latter date; Y(m, d) requires the number of years (months, days) that differ between two dates.

Application example: enter the formula =DATEDIF(A23, TODAY (), "y") in the C23 cell, and return the difference between the current date of the system (with TODAY ()) and the date in the A23 cell after confirmation, and return the number of years of the difference.

Special reminder: This is a hidden function in Excel, which can't be found in the function wizard. It can be directly input and used, which is very effective for calculating age and length of service.

9. Day function

Function Name: Day

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

Usage format: day (serial number)

Parameter description: serial_number indicates the specified date or the referenced cell.

Application example: enter the formula = day ("2003- 12- 18"), and confirm to display 18.

Special reminder: If it is a given date, please enclose it in English double quotation marks.

10, DCOUNT function

Function name: DCOUNT

Main function: Returns the number of cells in a column that meets the specified conditions and contains numbers in a database or list.

Usage format: DCOUNT (database, field, condition)

Parameter Description: The database indicates the cell area to be counted; Field represents the data column used by the function (the first row must have a flag item); Criteria is a range of cells containing conditions.

Application example: as shown in figure 1, enter the formula = dcount (a1:d1,"Chinese", F 1:G2) in cell F4, and then confirm that the score in the column "Chinese" is greater than or equal to 70.