The following functions are commonly used.
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, a function 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.
Special reminder: if the above formula is modified to = dcount (a1:d11,f 1: G2), the same purpose can be achieved.
1 1, frequency function
Function Name: Frequency
Main function: Use a column of vertical arrays to return the frequency distribution of data in a certain area.
Usage format: frequency (data _ array, bins _ array)
Parameter description: Data_array represents a group of data or cell ranges used to calculate frequency; Bins_array represents a list of values separated for the previous array.
Application example: As shown in Figure 2, select cell areas B32 to B36 at the same time, enter the formula: =FREQUENCY(B2:B3 1, D2:D36), and then press the key combination of "Ctrl+Shift+Enter" to confirm, so as to find out the occurrence of the values of each segment separated by D2 to D36 in the areas B2 to B3 1.
Special reminder: the above input is an array formula. After input, you need to press "Ctrl+Shift+Enter" to confirm. After confirmation, a pair of braces ({}) appear at both ends of the formula, so you cannot enter directly.
12, IF function
Function name: IF
Main function: judge whether the result is true or false according to the logic of the specified condition, and return the corresponding content.
Usage format: = if (logic, value _ if _ true, value _ if _ false)
Parameter Description: Logical stands for logical judgment expression; Value_if_TRUE indicates the content displayed when the judgment condition is logically "true", and returns "true" if it is ignored; Value_if_FALSE indicates the display content when the judgment condition is logical "false", and returns "false" if ignored.
Application example: Enter the formula in cell C29: = if(C26 >；; = 18, "meeting the requirements" and "not meeting the requirements"). After confirmation, if the value in cell C26 is greater than or equal to 18, cell C29 will display "meets the requirements", otherwise it will display "does not meet the requirements".
Special reminder: Readers are not bound by the cells specified in "Input Formula in C29 Cell" in this article. The corresponding cells are given here to meet the needs of the examples attached to this article. For details, please refer to the attached sample file.
13, exponential function
Function Name: Index
Main function: Returns the value of an element in a list or array, which is determined by the index value of the row number and column number.
Usage format: INDEX (array, row number, column number)
Parameter description: Array represents a cell range or an array constant; Row_num indicates the specified line number (column _ num); Required if row_num is omitted); Column_num indicates the specified column number (row_num is required if column_num is omitted).
Application example: As shown in Figure 3, enter the formula: = index (a 1: d 1 1, 4, 3) in the F8 cell, and after confirmation, the number from A1to D1will be displayed.
Special reminder: The line number parameter (row_num) and column number parameter (column_num) here are relative to the referenced cell range, not the line number or column number in the Excel worksheet.
14, INT function
Function name: INT
Main function: Rounds the value down to the nearest integer.
Use format: INT (number)
Parameter Description: number indicates the value to be rounded or the reference cell containing the value.
Application example: enter the formula: =INT( 18.89), and confirm the display 18.
Special reminder: rounding is not allowed when rounding; If the input formula is =INT(- 18.89), the returned result is-19.
15 and ISERROR function
Function name: ISERROR
Main function: used to test whether the return value of the function is wrong. If there is an error, the function returns TRUE, otherwise it returns FALSE.
Usage format: ISERROR (value)
Parameter Description: Value indicates the value or expression to be tested.
Application example: Input formula: =ISERROR(A35/B35). After confirmation, if cell B35 is empty or "0", then A35/B35 will generate an error. In this case, the above function returns a true result, and vice versa.
Special note: this function is usually used with the IF function. If the above formula is modified to = if (iserror (A35/B35), "",A35/B35), if B35 is empty or "0", the corresponding cell is displayed as empty, otherwise A35/B35 is displayed.
Result.
16, left function
Function Name: Left
Main function: Intercept a specified number of characters from the first character of the text string.
Usage format: LEFT(text, num_chars)
Parameter Description: text indicates the string to be truncated; Num_chars represents a given number of interceptions.
Application example: suppose the string of "I like the celestial pole network" is saved in cell A38, and we enter the formula = left (A38,3) in cell C38, and the "I like" character will be displayed after confirmation.
Special reminder: the English meaning of this function name is "left", that is, it is taken from the left, and many Excel functions take its English meaning.
17, lens function
Function name: LEN
Main function: Count the number of characters in the text string.
Use format: LEN (text)
Parameter Description: text indicates the text string to be counted.
Application example: suppose the string "I am 28 years old" is saved in cell A4 1, and we enter the formula =LEN(A40) in cell C40, and the statistical result "6" will be displayed after confirmation.
Special reminder: When LEN counts, whether it is a full-width character or a half-width character, each character is counted as "1"; The corresponding function LENB counts half-width characters as "1" and full-width characters as "2" in statistics.
18, matching function
Function Name: Match
Main function: returns the corresponding position of the element matching the specified value in the array in a specified way.
Usage format: match (find value, find array, match type)
Parameter Description: Lookup_value indicates the value to be found in the data table;
Lookup_array represents a contiguous range of cells that may contain the value you want to find;
Match_type indicates the value of the search method (-1, 0 or 1).
If match_type is-1, the Lookup_array must be arranged in descending order to find the minimum value greater than or equal to lookup_value;
If match_type is 1, find the maximum value less than or equal to lookup_value, and Lookup_array must be arranged in ascending order;
If match_type is 0, find the first value equal to lookup_value, and Lookup_array can be arranged in any order; If match_type is omitted, it defaults to 1.
Application example: As shown in Figure 4, enter the formula = match (E2, b1:b1,0) in the F2 cell, and return the search result "9" after confirmation.
Special reminder: Lookup_array can only be one column or one row.
19, maximum function
Function name: MAX
Main function: find the maximum value in a set of numbers.
Usage format: max (number 1, number2 ...)
Parameter description: the number 1, the number 2 ... indicates the value or reference cell (area) that needs the maximum value, and there are no more than 30 parameters.
Application example: enter the formula =MAX(E44:J44, 7, 8, 9, 10), and confirm that the maximum value of E44-J44 unit and region and the value of 7, 8, 9, 10 are displayed.
Special reminder: If there are text or logical values in the parameters, they will be ignored.
20. Intermediate function
Function name: MID
Main function: Intercept a specified number of characters from the specified position of the text string.
Usage format: MID(text, start_num, num_chars)
Parameter description: text represents a text string; Start_num indicates the specified starting position; Num_chars represents the number to be truncated.
Application example: suppose the string "I like the celestial pole net" is saved in cell A47, and we enter the formula = mid (A47,4,3) in cell C47, and the character "celestial pole net" will be displayed after confirmation.
Special reminder: the parameters in the formula should be separated by English commas.
2 1, minimum function
Function name: MIN
Main function: find the minimum value in a set of numbers.
Usage format: min (number 1, number 2 ...)
Parameter description: the number 1, the number 2 ..... indicates the value or reference cell (region) that needs the minimum value, and there are no more than 30 parameters.
Application example: enter the formula: =MIN(E44:J44, 7, 8, 9, 10). After confirmation, the minimum values of E44-J44 units and areas and the values of 7, 8, 9, 10 can be displayed.
Special reminder: If there are text or logical values in the parameters, they will be ignored.
22, MOD function
Function name: MOD
Main function: Find the remainder of the division of two numbers.
Usage format: MOD (number, divisor)
Parameter description: Numbers represent dividends; Divider stands for divisor.
Application example: enter the formula = mod (1 3,4), and the result "1"will be displayed after confirmation.
Special reminder: If the divisor parameter is zero, the error value is "#DIV/0!" Will be displayed. ; The MOD function can be represented by the function INT: the above formula can be modified as: = 13-4*INT( 13/4).
23. Monthly function
Function Name: Month
Main function: find the month of the specified date or reference the date in the cell.
Usage format: month (serial number)
Parameter description: serial_number indicates the specified date or the referenced cell.
Application example: enter the formula = month ("2003- 12- 18"), and confirm to display 1 1.
Special reminder: If it is a given date, please enclose it in English double quotation marks; If the above formula is modified to = year ("2003-12-18"), the corresponding value of "2003" will be returned.
24. Now function
Function Name: Now
Main function: give the current system date and time.
Use format: Now ()
Parameter Description: This function does not need parameters.
Application example: enter the formula: =NOW (), and immediately display the current system date and time after confirmation. If the system date and time have been changed, just press the F9 function key to change it.
Special reminder: the displayed date and time format can be reset by cell format.
25 or function
Function name: or
Main function: Returns the logical value, and only when all parameter values are logically false, returns the logical "false" of the function result, otherwise returns the logical "true".
Use format: or (logical 1, logical2, ...)
Parameter description: logic 1, logic 2, logic 3 ......................................................................................................................................................
Application example: Enter the formula = or (A62 & gt=60, B62 & gt=60) in cell C62 and confirm. If C62 returns TRUE, at least one of A62 and B62 is greater than or equal to 60; If FALSE is returned, the values in A62 and B62 are both 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".
26. Grade function
Function name: RANK
Main function: Returns the ranking of one value in a list of values relative to other values.
Usage format: grade (number, reference, order)
Parameter description: Number indicates the numerical value to be sorted; Ref represents the cell area where the sort value is located; Order indicates the sorting method parameter (if it is "0" or omitted, it will be sorted in descending order, that is, the larger the value, the smaller the sorting result value; If it is not a "0" value, it will be sorted in ascending order, that is, the greater the value, the greater the sorting result value; )。
Application example: if you enter the formula =RANK(B2, $B$2:$B$3 1, 0) in the cell, you can get the ranking result of Ding 1 in the whole class after confirmation.
Special reminder: In the above formula, we let the Number parameter take the form of relative references, and let the ref parameter take the form of absolute reference (plus a "$" symbol). After this setting, select the C2 cell, move the mouse to the lower right corner of the cell, and when it becomes a thin crosshair (usually called "fill handle"), hold down the left button and drag it down, and you can quickly copy the above formula to the cell below column C to complete the Chinese scores of other students.
27. Right function
Function Name: Right
Main function: Intercept a specified number of characters from the last character of the text string.
Usage format: RIGHT(text, num_chars)
Parameter Description: text indicates the string to be truncated; Num_chars represents a given number of interceptions.
Application example: suppose the string "I like the celestial pole net" is saved in cell A65, and we enter the formula = right (A65,3) in cell C65, and the character "celestial pole net" will be displayed after confirmation.
Special reminder: Num_chars parameter must be greater than or equal to 0; If omitted, it defaults to1; If the num_chars parameter is greater than the text length, the function will return the entire text.
28. Subtotal function
Function Name: Subtotal
Main function: return the classified summary in the list or database.
Usage format: subtotal (function _ num, ref 1, ref2, ...)
Parameter description: Function_num is a number from 1 to 1 1 (including hidden values) or10/to1(ignoring hidden values), which is used to specify the list. Ref 1, ref2, ..., ... represent the regions or references to be classified and summarized, with no more than 29 references.
Application example: As shown in Figure 7, enter the formulas =SUBTOTAL(3, C2:C63) and =SUBTOTAL 103, C2:C63) in cells B64 and C64, and hide 6 1 row. After confirmation, the former is displayed as 62 (including hidden lines) and the latter as 665438.
Special reminder: If automatic filtering is used, regardless of the type of function_num parameter, the SUBTOTAL function will ignore any lines that are not included in the filtering result; The SUBTOTAL function applies to data columns or vertical areas, but not to data rows or horizontal areas.
29. Function name: SUM
Main function: Calculate the sum of all parameter values.
Usage format: sum (number 1, number 2 ...)
Parameter Description: the number 1, and the number 2 ........................................................................................................................................................
Application example: As shown in Figure 7, enter the formula =SUM(D2:D63) in cell D64, and you can get the total Chinese score after confirmation.
Special reminder: If the parameter is an array or a reference, only the numbers in it will be counted. Blank cell; Logical value; Text or incorrect values in arrays or references will be ignored; If the above formula is modified to = sum (large (D2: D63, {1, 2, 3, 4, 5}), the sum of the top five scores can be obtained.
30.SUMIF function
Function name: SUMIF
Main function: Calculate the sum of the values in the cell range that meet the specified conditions.
Usage format: SUMIF(Range, Criteria, Sum_Range)
Parameter Description: Range indicates the cell area for conditional judgment; Criteria is the specified conditional expression; Sum_Range represents the cell range where the value to be calculated is located.
Application example: As shown in Figure 7, enter the formula =SUMIF(C2:C63, "male", D2:D63) in cell D64, and you can get the sum of Chinese scores of male students after confirmation.
Special reminder: If the above formula is modified to =SUMIF(C2:C63, "female", D2:D63), the sum of the Chinese scores of "female" students can be obtained; Among them, "male" and "female" need to be put in double quotation marks in English ("male" and "female") because they are textual.
3 1, text function
Function Name: Text
Main function: Convert the corresponding number into text form according to the specified number format.
Use format: text (value, format _ text)
Parameter Description: value indicates the value to be converted or the referenced cell; Format_text is a number format that specifies the text format.
Application example: If the value of 1280.45 is stored in cell B68, we enter the formula =TEXT(B68, "0.00") in cell C68, and it will be displayed as "$ 1280.45" after confirmation.
Special reminder: the format_text parameter can be determined according to the type in the number tab of the cell format dialog box.
32. Today's function
Function Name: Today
Main function: Give the date to the system.
Format: Today ()
Parameter Description: This function does not need parameters.
Application example: Enter the formula: =TODAY (), and the system date and time will be displayed immediately after confirmation. If the system date and time have been changed, just press the F9 function key to change it.
Special reminder: the displayed date format can be reset by cell format (see attachment).
33. Value function
Function Name: Value
Main function: Convert a text string representing a numerical value into a numerical value.
Use Format: Value (Text)
Parameter Description: text indicates the text string value to be converted.
Application example: If cell B74 is a text string intercepted by functions such as LEFT, we can enter the formula: =VALUE(B74) in cell C74, and then it can be converted into numerical value after confirmation.
Special note: If text values are not converted in the above way, errors will often be returned when processing these values with functions.
34, VLOOKUP function
Function name: VLOOKUP
Main function: find the specified value in the first column of the data table, and then return the value of the specified column in the current row of the data table.
Usage format: vlookup (lookup _ value, table _ array, col _ index _ num, range _ lookup).
Parameter Description: Lookup_value indicates the value to be searched; Table_array represents the range of cells for which you need to find data; Col_index_num is the column number of the matching value to be returned in the table_array area (when Col_index_num is 2, the value of the second column of table_array is returned, and when it is 3, the value of the third column is returned ...); Range_lookup is a logical value. If TRUE or omitted, an approximate matching value is returned, that is, if an exact matching value cannot be found, the maximum value less than lookup_value is returned. If FALSE, the exact match value is returned, and if it is not found, the error value #N/A is returned.
Application example: See Figure 7. We enter the formula =VLOOKUP(B65, B2: D63,3, FALSE) in cell D65. After confirmation, just enter a student's name (such as Ding 48) in cell D65, and the student's language score will be displayed immediately.
Special reminder: the Lookup_value reference must be in the first column of the Table_array area; If the Range_lookup parameter is omitted, the first column of Table_array must be sorted; In the wizard of this function, the use of the Range_lookup parameter is wrong.
35, workday function
Function name: workday
Main function: give the number of weeks corresponding to the specified date.
Usage format: weekday (serial _ number, return _ type)
Parameter description: serial_number indicates the specified date, or is a cell containing a date; Return_type indicates the week [when Sunday is 1 and Saturday is 7, this parameter is1; When Monday (Monday) is 1 and Sunday (Sunday) is 7, the parameter is 2 (this is in line with the habit of China people); Monday is 0, Sunday is 6, and the parameter is 3].
Application example: enter the formula: =WEEKDAY(TODAY (), 2), and give the week number of the system date after confirmation.
Special reminder: If you specify the date, please put it in double quotation marks in English, such as = weekday ("2003-12-18", 2).