Excel Common functions

When we use Excel to organize data, we often use its function function to automatically count the data in the table. Here we have compiled the functions of the most frequently used functions in Excel, how to use them, and the examples of these functions in practical applications, with detailed descriptions.



Note: The use of the $ symbol


Example: B5

such as: $B 5 write, both fixed B, after the value variable

B$5 This write, both fixed 5, the previous value is variable

$B This write, both fixed and pre-set values, not variable



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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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).

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.

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.

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 the result of A35/b35 is displayed.



　　

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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;).

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.

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.

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. Sum function

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.

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.

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.

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.

　　

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.
　　

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.

　　

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).




36. sumif function

Function Name: SUMIF

Main function: Sum by condition

Use format: SUMIF (the area to be judged, the condition to be judged, the zone search domain to be counted after qualifying)

Application examples: SUMIF (a1:a100, "Apple", b1:b100) is the b1:b100 sum of the cells in a1:a100 that are "apples".

Special Reminders: The area range is to be in the same column or row, and the two area ranges before and after the formula need to correspond. The above formula can also be written: =sumif (a:a, "Apple", b:b)




37. & function

Function name:&

Key features: equivalent to a link character

Use format: =B1&B2

Examples of applications: for example b1= "Wang", b2= "Ying", using & later for "Wang Ying"



38. Find out if a value (P2) contains pb,px

The following formula can be used:

=count (FIND ({"PB"; " PX "},P2))

Excel Common functions