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 numeric 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) is displayed in B2 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.), the error value "#VALUE is returned in B2! ”。 2. and function
Function Name: and
Main function: Return logical value: Returns the logical true (TRUE) if all parameter values are logical "true", and returns the logical "false" instead.
Use format: and (Logical1,logical2, ...)
Parameter description: Logical1,logical2,logical3 ... : Represents the condition value or expression to be tested, up to these 30.
Application example: In C5 cell input formula: =and (a5>=60,b5>=60), confirm. If True is returned in C5, the values in A5 and B5 are greater than or equal to 60, and if False is returned, the values in A5 and B5 are at least one less than 60.
Special reminder: If you specify a logical condition parameter that 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 numeric value or a reference cell (region) that requires an average value, and 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 H7 region of the values and 7, 8 average.
Special reminder: If the reference range contains a value of 0 cell, it is counted, and if the reference range contains blank 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), and confirm that it appears as 2 (that is, column B).
Special reminder: If you enter a formula in cell B11: =column (), it also shows 2, and corresponds to a function--row (reference) that returns the row label value. 5. Concatenate function
Function Name: concatenate
Key Features: Connect multiple character text or data in a cell to display in a single cell.
Use format: Concatenate (Text1,text ...)
Parameter description: Text1, Text2 ... The character text or referenced cell that you want to connect to.
Application Example: Enter the formula in cell C14: =concatenate (A14, "@", B14, ". com"), and confirm that the characters in the A14 cell, the characters in the @, B14 cells, and. com are concatenated into a single whole, displayed in the C14 cell.
Special reminder: If the parameter is not a referenced cell, and is in text format, give the argument a double quotation mark in the English state, if you change the above formula to:=a14& "@" &B14& ". com", you can achieve the same purpose. 6. countif function
Function Name: COUNTIF
Main function: Counts the number of cells in a range of cells that meet the specified criteria.
Usage format: COUNTIF (Range,criteria)
Parameter Description: Range represents the range of cells to be counted, and criteria represents the specified conditional expression.
Application Example: Enter the formula in cell C17: =countif (b1:b13, ">=80"), after confirming, you can count the number of cells in the B1 to B13 cell range, with a value greater than or equal to 80.
Special reminder: Blank cells appear in the range of cells that are allowed to be referenced. 7. Date function
Function Name: DATE
Main function: gives the date of the specified value.
Use format: DATE (Year,month,day)
Parameter description: Year is the specified years value (less than 9999), month is the specified month value (can be greater than), and day is the specified number of days.
Application Example: Enter the formula in cell C20: =date (2003,13,35), confirm, show 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, more than the actual number of days in January 2004 and 4 days, it was postponed to February 4, 2004. 8. Function Name: DATEDIF
Main function: Calculates the difference between the two date parameters returned.
Use format: =datedif (date1,date2, "Y"), =datedif (Date1,date2, "M"), =datedif (Date1,date2, "D")
Parameter Description: Date1 represents a previous date, Date2 represents a subsequent date, and Y (M, d) requires that the number of years (months, days) of two dates be returned.
Application Example: Enter the formula in cell C23: =datedif (A23,today (), "Y"), confirm the difference between the current date of the system [with today () and the date in the A23 cell, and return the number of years of difference.
Special reminder: This is a hidden function in Excel, is not found in the Function Wizard, can be entered directly, for the calculation of age, length of service and so very effective. 9. Day function
Function Name: Day
Main feature: Find the number of days in a specified date or 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"), after confirmation, showing 18.
Special reminder: If it is a given date, enclose it in double quotation marks. 10. DCount function
Function Name: DCOUNT
Primary function: Returns the number of cells in a column of a database or list that meet the specified criteria and contain numbers.
Usage format: DCOUNT (Database,field,criteria)
Parameter Description: Database represents the range of cells that need to be counted; field represents the data column used by the function (the first row must have a flag entry); The criteria contains the range of cells for the condition.
Example of application: 1, enter the formula in the F4 cell: =dcount (A1:d11, "language", F1:G2), after confirmation can be found in the "Language" column, the result is greater than or equal to 70, and less than 80 of the number of cell numbers (equivalent to the number of fractional segments). Special reminder: If the above formula is modified to: =dcount (A1:D11,,F1:G2), you can also achieve the same purpose. 11. Frequency function
Function Name: FREQUENCY
Main function: Returns the frequency distribution of the data in an area in a vertical array of columns.
Usage format: FREQUENCY (Data_array,bins_array)
Parameter Description: Data_array represents a set of data or range of cells used to calculate the frequency, and Bins_array represents a column of values that is delimited for the preceding array.
Application Example: 2, select the B32 to B36 cell range, enter the formula: =frequency (B2:B31,D2:D36), after the input is completed, press the "Ctrl+shift+enter" key to confirm, you can find the B2 to the B31 region, The number of occurrences of the values of each segment separated by the D2 to D36 area (equivalent to the number of people in each fractional segment).
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 confirming that the formula ends with a pair of curly braces ({}), this curly brace cannot be entered directly. 12. If function
Function Name: IF
Main function: Returns the corresponding content according to the true and false result of the logical judgment of the specified condition.
Usage format: =if (Logical,value_if_true,value_if_false)
Parameter Description: Logical represents the logical judgment expression; Value_if_true represents the display when the condition is determined to be logical true (true), and if "true" is omitted, value_if_false indicates that the condition is logical "false (False)". Returns "FALSE" if the display content is ignored.
Application Example: Enter the formula in cell C29: =if (c26>=18, "Meet Requirements", "non-compliant"), and be sure that later, if the value in the C26 cell is greater than or equal to 18, the C29 cell displays the word "compliant" and the word "non-conforming" is displayed instead.
Special reminder: This article is similar to the "Enter formula in C29 cell" in the cell specified in the reader when using, do not need to be constrained by it, here is only to match the example attached to this article to give the corresponding cell, please refer to the attached instance file. 13. Index function
Function Name: INDEX
Main function: Returns the value of an element in a list or array, determined by the index value of the row ordinal and column ordinal.
Use format: INDEX (Array,row_num,column_num)
Parameter Description: Array represents a range of cells or array constants, Row_num represents the specified row ordinal (if omitted row_num, you must have Column_num), column_num represents the specified column ordinal (if you omit column_num, you must have Row_ NUM).
Example of application: 3, enter the formula in cell F8: =index (a1:d11,4,3), and confirm that the contents of the cell (that is, D11) in the intersection of the 4th and 3rd columns in the A1 to C4 cell range are shown.
Special Reminder: The row ordinal argument (row_num) and column ordinal parameter (column_num) Here are relative to the range of cells referenced, not the row or column ordinal in the Excel worksheet. 14, int function
Function Name: INT
Main function: The value is rounded down to the nearest integer.
Use format: INT (number)
Parameter Description: Number indicates a value that needs to be rounded or a reference cell that contains a numeric value.
Application Example: Input formula: =int (18.89), after confirmation shows 18.
Special reminder: Rounding is not done when rounding, if the input formula is =int (-18.89), the result is 19.
15. IsError function
Function Name: ISERROR
Main function: Used to test whether the value returned by the function is wrong. If there is an error, the function returns True, and conversely returns false.
Use format: ISERROR (value)
Parameter Description: value represents the values or expressions that need to be tested.
Application Example: Enter the formula: =iserror (A35/B35), after confirmation, if the B35 cell is empty or "0", then a35/b35 an error, the aforementioned function returns a true result, reverse returns false.
Special reminder: This function is usually used with the IF function, if the above formula is modified to: =if (ISERROR (a35/b35), "", a35/b35), if the B35 is empty or "0", then the corresponding cell display is empty, and vice versa a35/b35 The result. 16. Left function
Function Name: Left
Main function: Starts from the first character of a text string and intercepts a specified number of characters.
Use format: Left (text,num_chars)
Parameter Description: Text represents the string to be truncated; Num_chars represents the given number of intercepts.
Example of application: Assuming that the "I like Yesky" string is saved in cell A38, we enter the formula in the C38 cell: =left (a38,3), and the character "I like" is displayed after confirmation.
Special reminder: This function name in English means "left", that is, from the left to intercept, Excel many functions are to take their English meaning. 17. Len function Function Name: LEN
Key Features: Count the number of characters in a text string.
Use format: LEN (text)
Parameter Description: Text represents the literal string to be counted.
Example of application: Assuming that the "I am 28 years old" string is saved in cell A41, we enter the formula in cell C40: =len (A40), the statistical result "6" is displayed after confirmation.
Special reminder: Len to statistics, regardless of the full-width characters, or half-width characters, each word nonspacing is counted as "1", and corresponding to a function--lenb, in the statistical time half-width word counted "1", the full-width word counted "2". 18. Match function
Function Name: MATCH
Main function: Returns the corresponding position of the element in the array that matches the specified value in the specified mode.
Use format: MATCH (Lookup_value,lookup_array,match_type)
Parameter Description: Lookup_value represents the value that needs to be found in the data table;
Lookup_array represents a contiguous range of cells that may contain the numeric value you want to find;
The match_type represents the value of the lookup method (-1, 0, or 1).
If Match_type is-1, find the smallest value greater than or equal to lookup_value, Lookup_array must be sorted in descending order;
If Match_type is 1, find the maximum value less than or equal to lookup_value, Lookup_array must be arranged in ascending order;
If Match_type is 0, find the first value equal to lookup_value, Lookup_array can be sorted in any order, or 1 if Match_type is omitted.
Example of application: 4, enter the formula in cell F2: =match (e2,b1:b11,0), and then return the found result "9" after confirmation.
Special reminder: Lookup_array can only be one column or row. 19. Max function
Function Name: MAX
Main function: Find out the maximum value in a group of numbers.
Use format: MAX (Number1,number2 ...)
Parameter description: Number1,number2 ... Represents a number or reference cell (region) that requires a maximum value, with no more than 30 parameters.
Application Example: Enter the formula: =max (e44:j44,7,8,9,10), the maximum value of E44 to J44 cell and range and value 7,8,9,10 can be displayed after confirmation.
Special reminder: If there are text or logical values in the arguments, they are ignored. 20. Mid function
Function Name: MID
Main function: Intercepts a specified number of characters starting at the specified position of a text string.
Use format: MID (Text,start_num,num_chars)
Parameter Description: Text represents a text string; Start_num represents the specified starting position; Num_chars indicates the number to intercept.
Example of application: Assuming that the "I like Yesky" string is saved in cell A47, we enter the formula in the C47 cell: =mid (a47,4,3), and the character "Yesky" is displayed after confirmation.
Special reminder: In the formula between the parameters, to use the English state of the comma "," separated. 21. Min function
Function Name: MIN
Main function: Find the minimum value in a set of numbers.
Use format: MIN (Number1,number2 ...)
Parameter description: Number1,number2 ... Represents a numeric value or reference cell (region) that requires a minimum value, with no more than 30 parameters.
Application Example: Enter the formula: =min (e44:j44,7,8,9,10), the minimum value of E44 to J44 cell and range and value 7,8,9,10 can be displayed after confirmation.
Special reminder: If there are text or logical values in the arguments, they are ignored. 22. mod function
Function Name: MOD
Main function: Find the remainder of two number division.
Use format: MOD (number,divisor)
Parameter Description: number represents the divisor; divisor represents the divisor.
Application Example: Enter the formula: =mod (13,4), confirm that the result "1" is displayed.
Special reminder: If the divisor parameter is zero, the error value "#DIV/0!" is displayed The MoD function can be represented by a function INT: The above formula can be modified to: =13-4*int (13/4). 23. Month function
Function Name: MONTH
Main function: Find the month of the specified date or date in the reference cell.
Use format: MONTH (Serial_number)
Parameter Description: Serial_number represents the specified date or referenced cell.
Application Example: Input formula: =month ("2003-12-18"), after confirmation, showing 11.
Special reminder: If it is a given date, enclose it in double quotation marks, and if you modify the above formula to: =year ("2003-12-18"), the value of the year corresponds to "2003". 24. Now function
Function Name: Now
Main function: gives the current system date and time.
Use format: Now ()
Parameter description: The function does not require parameters.
Application Example: Enter the formula: =now (), the current system date and time are displayed immediately after confirmation. If the system date and time has changed, just press the F9 function key to change it.
Special reminder: The date and time format that is displayed can be reset by cell format. 25, or function
Function Name: OR
Main function: Return logical value, return the function result logic "false" only if all parameter values are logical "false", otherwise all return logical "true".
Use format: OR (Logical1,logical2, ...)
Parameter description: Logical1,logical2,logical3 ... : Represents the condition value or expression to be tested, up to these 30.
Application example: In C62 cell input formula: =or (a62>=60,b62>=60), confirm. If True is returned in C62, the values in A62 and B62 are at least one greater than or equal to 60, and if False is returned, the values in A62 and B62 are less than 60.
Special reminder: If you specify a logical condition parameter that contains a non-logical value, the function returns the error value "#VALUE!" or "#NAME". 26. Rank function
Function Name: RANK
Main function: Returns the rank of a value in a column of values relative to other values.
Use format: RANK (Number,ref,order)
Parameter Description: Number represents the value that needs to be sorted, ref represents the range of cells in which the value is sorted, the order represents the sort parameter (if "0" or omitted, the rank is descending, that is, the higher the value, the smaller the rank result, or the higher the value if it is not the "0"). The higher the value of the ranking result;).
Example of application: If you enter a formula in C2 cell: =rank (B2, $B $: $B $31,0), you can get the results of Grade 1 students in the class.
Special reminder: In the above formula, we let the number parameter take a relative reference form, and let the ref parameter take absolute reference form (added a "$" symbol), so that after setting, select C2 cell, move the mouse to the lower right corner of the cell, into a fine cross-line (often referred to as "fill handle"), By holding down the left button, you can quickly copy the above formula into the cell below column C and complete the ranking statistics of other students ' Chinese scores. 27. Right function
Function Name: Right
Main function: Starts with the last character of a text string and intercepts a specified number of characters.
Use format: Right (Text,num_chars)
Parameter Description: Text represents the string to be truncated; Num_chars represents the given number of intercepts.
Example of application: Assuming that the "I like Yesky" string is saved in cell A65, we enter the formula in the C65 cell: =right (a65,3), and the character "Yesky" is displayed after confirmation.
Special reminder: The Num_chars parameter must be greater than or equal to 0, and if omitted, the default is 1, and if the num_chars argument is greater than the length of the text, the function returns the entire text. 28. Subtotal function
Function Name: SUBTOTAL
Key features: Returns subtotals in a list or database.
Use format: SUBTOTAL (function_num, REF1, Ref2, ...)
Parameter description: Function_num is a number between 1 and 11 (containing hidden values) or 101 to 111 (ignoring hidden values) that specifies what function to use in the list for subtotal calculations (6); Ref1, Ref2,...... Represents a subtotal area or reference to be made, no more than 29.
Example of application: 7, enter the formula in B64 and C64 cells: =subtotal (3,c2:c63) and =subtotal103,c2:c63), and hide 61 rows, and confirm that the former display as 62 (including hidden rows), the latter display as 61, Hidden rows are not included.
Special Reminder: If you take an AutoFilter, the SUBTOTAL function ignores any rows that are not included in the filter results, regardless of the type of function_num parameter, and the SUBTOTAL function is suitable for data columns or vertical areas, not for data rows or horizontal areas. 29. Function Name: SUM
Main function: Calculate the value of all parameters and.
Use format: SUM (Number1,number2 ... ）
Parameter description: Number1, Number2 ... Represents the value that needs to be computed, which can be a specific numeric value, a referenced cell (region), a logical value, and so on.
Example of application: 7, enter the formula in D64 cell: =sum (d2:d63), the total score of the language can be obtained after confirmation.
Special reminder: If the parameter is an array or reference, only the number in it will be evaluated. Blank cells, logical values, text, or error values in an array or reference are ignored, and if the above formula is modified to: =sum (LARGE (d2:d63,{1,2,3,4,5})), the first 5 scores can be calculated. 30. sumif function
Function Name: SUMIF
Main function: Calculates the values in the range of cells that meet the specified criteria.
Usage format: SUMIF (Range,criteria,sum_range)
Parameter description: Range is the range of cells that the criteria determines, the criteria is the specified conditional expression, and the Sum_range represents the range of cells in which the value needs to be computed.
Example of application: 7, enter the formula in the D64 cell: =sumif (c2:c63, "male", d2:d63), after confirmation can find the "male" students of the language scores and.
Special reminder: If the above formula is modified to: =SUMIF (c2:c63, "female", d2:d63), you can find the "female" students of the language scores and; among them "male" and "female" because is the text type, needs to put in the English state double quotation mark ("Male", "female"). 31. Text function
Function Name: TEXT
Main function: Converts the corresponding number to textual form according to the specified numeric format.
Use format: TEXT (Value,format_text)
Parameter Description: value represents the numeric or referenced cell that needs to be converted; Format_text is the number format for the specified text form.
Example of application: If you save a value of 1280.45 in cell B68, we enter the formula in cell C68: =text (B68, "$0.00"), confirming that it appears as "$1280.45".
Special reminder: The Format_text parameter can be determined based on the type in the Number tab of the Cell Format dialog box. 32. Today function
Function Name: TODAY
Main function: Give the system date.
Use format: Today ()
Parameter description: The function does not require parameters.
Application Example: Enter the formula: =today (), the system date and time are displayed immediately after confirmation. If the system date and time has changed, just press the F9 function key to change it.
Special reminder: The date format that is displayed can be reset by cell format (see attachment). 33. Value function
Function Name: VALUE
Main function: Converts a text-type string representing a numeric value to a numeric type.
Use format: VALUE (text)
Parameter Description: Text represents the need to convert literal string values.
Example of application: If the B74 cell is a text string that is intercepted by a function such as left, we enter the formula in cell C74: =value (B74), and when confirmed, it can be converted to a numeric type.
Special Note: If the text type value does not pass the above conversion, when processing these values with the function, often returns an error. 34. Vlookup function
Function Name: VLOOKUP
Main function: Finds the specified value in the first column of the data table and returns the value at the specified column in the current row of the datasheet.
Usage format: VLOOKUP (lookup_value,table_array,col_index_num,range_lookup)
Parameter Description: Lookup_value represents the value that needs to be looked up; Table_array represents the range of cells in which the data is to be found; Col_index_num is the column ordinal of the matching value to be returned in the Table_array area (when Col_index_ Num is 2 o'clock, returns the value in column 2nd of table_array, 3 o'clock, returns the 3rd column ... ); range_lookup is a logical value, and if TRUE or omitted, an approximate match value is returned, that is, if the exact match value is not found, the maximum value less than lookup_value is returned, and if False, the exact match value is returned, and if not found, an error value # N/A.
Example of application: See Figure 7, we enter the formula in cell D65: =vlookup (b65,b2:d63,3,false), after confirmation, as long as the name of a student is entered in the B65 cell (such as Ding,), the student's language score is immediately displayed in the D65 cell.
Special reminder: Lookup_value See must be in the first column of the table_array area; If you omit the range_lookup parameter, the first columns of table_array must be sorted; In this function's wizard, the Range_ The use of the lookup parameter is incorrect. For details see:http://blog.csdn.net/qq1987924/article/details/7927829 35. Weekday function
Function Name: WEEKDAY
Main function: give the corresponding week number of the specified date.
Usage format: WEEKDAY (Serial_number,return_type)
Parameter Description: Serial_number represents the specified date or refers to a cell containing a date, return_type represents the day of the week [when Sunday (Sunday) is 1, Saturday (Saturday) is 7 o'clock, the parameter is 1; when Monday (Monday) is 1 , Sunday (Sunday) is 7 o'clock, this parameter is 2 (this situation conforms to the Chinese habit); Monday (Monday) is 0, Sunday (Sunday) is 6 o'clock, this parameter is 3].
Example of application: Enter the formula: =weekday (Today (), 2), and confirm the number of days of the system date.
Special reminder: If the date is specified, put in double quotes in English state, such as =weekday ("2003-12-18", 2). |
