Summary of commonly used functions in excel

Summary of commonly used excel functions

The first category: text processing functions

● Trim function :

Function: Remove all spaces in the text except single spaces between words.

Syntax: TRIM(text), Text is required and is the text to remove spaces.

● Concatenate function:

Function: Concatenate two or more text strings into one string.

Syntax: CONCATENATE(text1, [text2], ...), contains at least one item, up to 255 items, and supports up to 8192 characters. The items can be text values, numbers, or cell references.

Note: You can use the connector & to achieve the same function.

● Replace function:

Function: Replace the string at a specific position with a different text character.

Syntax: REPLACE(old_text, start_num, num_chars, new_text), old_text is the text that needs to be replaced, start_num replaces the character position, num_chars uses the number of characters replaced by new_text, and new_text replaces the new text of old_text.

● Substitue function:

Function: Replace the specified text in a certain text string

The difference between

and Replace: Substitue replaces based on the text content, and Replace based on the character position Make a substitution.

Syntax: SUBSTITUTE(text, old_text, new_text, [instance_num]), text contains the text that needs to be replaced, old_text is the text that needs to be replaced, new_text is the text that replaces old_text, instance_num is an optional parameter, specified If the number is missing, only the old_text in the corresponding sequence will be replaced, otherwise all will be replaced.

● Left function:

Function: Returns the specified number of characters starting from the first character of the text string.

LEFT(text, [num_chars]), text contains the characters to be extracted, num_chars is the specified number to be extracted, and must be ≥ 0. If it is greater than the length of the text, the entire text will be returned. If omitted, it will be assumed The value is 1.

● Right function:

The usage is the same as Left, except that the direction of taking numbers is opposite, starting from the right side.

● Mid function:

Function: extract a specific number of characters starting from the specified position

Syntax: MID (text, start_num, num_chars), text contains the characters to be extracted Text, start_num The position of the first character to be extracted in the text, num_chars The number of characters expected to be extracted.
Recommended related articles: 1.
Summary of all formulas of Excel functions 2.
excel financial functions3 .
excel statistical function4.excel string function

The second category: information feedback function

● Exact function:

Function: Compare two text strings. If they are exactly the same, return TRUE, otherwise return FALSE. The EXACT function is case-sensitive but ignores formatting differences. Use EXACT to verify text entered in a document.

Syntax: EXACT(text1, text2), text1 and text2 are two strings that need to be compared.

● Len function:

Function: Returns the number of characters in the text, generally used in conjunction with other functions.

Syntax: LEN(text), text is the text whose length needs to be queried, and spaces will be counted as characters.

● IS function:

Function: This type of function can check the specified value and return TRUE or FALSE according to the result. You can use the IS function to obtain information about a value before performing calculations or other operations on it.

Grammar: ISBLANK(value), ISERR(value), ISERROR(value), ISLOGICAL(value), ISNA(value), ISNONTEXT(value), ISNUMBER(value), ISREF(value), ISTEXT( value). value refers to the value to be tested. The value parameter can be blank (an empty cell), an error value, a logical value, text, a number, a reference value, or a name that refers to any of the above values ​​to be tested.

Category 3: Find reference function

● Vlookup function:

Function: Find the corresponding content by row in the table area.

Syntax: VLOOKUP (value to look for, range to look for value in, column number in range containing return value, exact match or approximate match - specified as 0/FALSE or 1/TRUE).

Note: The value to be found must always be in the first column of the area

● Hlookup function:

Function: Find the corresponding content by column in the table.

Syntax: HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]), the parameters correspond to VLOOKUP, the third parameter is the row number, and the third parameter of VLOOKUP is the column number.

● Index function:

Function: Return the value or value reference in the table or range

###Syntax: INDEX(array, row_num, [column_num]), array unit Grid area or array constant, row_num is a row of the array, column_num is a column in the array. In addition, there are also citation forms. If you are interested, you can use Baidu or use Microsoft Help. ###

Note: If the parameters row_num and column_num are used, the INDEX function returns the value of the cell at the intersection of the row and column numbers; if row_num is set to 0, the value of the entire column value is returned, which is also applicable to column; if you want to enter an array formula, You need to press Ctrl Shift Enter after entering the formula.

● Match function:

Function: Search for a specific item in the range cell, and then return the relative position of the item in this area.

Syntax: MATCH(lookup_value, lookup_array, [match_type]), lookup_value is the value to be found, look_arrary cell range, match_type=1 or omitted, find the maximum value ≤ lookup_value, lookup_array needs to be sorted in ascending order; =0 , find the first value that is exactly equal to lookup_value; =-1, find the minimum value ≥ lookup_value, lookup_array needs to be sorted in descending order.

Note: MATCH does not distinguish between uppercase and lowercase letters. Can it be used with lookup_value? or*,? Matches any single character, * matches any string of characters, if you want to find the actual question mark or asterisk, you need to add ~ before the character.

● Search function:

Function: The function can find the first text string in the second text string and return the number of the starting position of the first text string , the number is calculated from the first character of the second text string.

Syntax: SEARCH(find_text,within_text,[start_num]), find_text is the text to be found, with_text contains the text to be found, start_num is the character number to start searching from.

Note: SEARCH is not case-sensitive, and the FIND function is case-sensitive. Does SEARCH support the use of wildcards? and *, which FIND does not support.

● Find function:

The FIND function is case-sensitive and cannot use wildcards. Other usage is consistent with the SEARCH function.

● Choose function:

Function: Return the value in the numerical parameter list according to the parameters.

Syntax: CHOOSE(index_num, value1, [value2], ...), index_num refers to the selected numerical parameter, between 1 and 254. If index_num is 1, value1 is returned, and if it is 2, value1 is returned. value2...

Example: SUM(CHOOSE(2,A1:A10,B1:B10,C1:C10))=SUM(B1:B10)

● Row / Column function:

ROW([reference]) returns the referenced row number, COLUMN([reference]) returns the referenced column number, if reference is omitted, returns the row/column number where the function is located.

● Offset function:

Function: Returns a reference to a cell or range of cells with a specified number of rows and columns.

Syntax: OFFSET(reference, rows, cols, [height], [width]), reference is the reference position of the offset, the number of rows offset by rows, the number of columns offset by cols, height and Width specifies the row height and column width returned.

Note: The reference position cannot exceed the edge of the worksheet; if height and width are omitted, the height and width will be the same as the reference.

● Indirect function:

Function: Return the reference specified by the text string

Syntax: INDIRECT(ref_text, [a1]), ref_text’s reference to the cell, if To reference another workbook, the referenced workbook must be open. If a1 is true or omitted, it is A1 style, otherwise it is R1C1 style.

● Address function:

Function: Get the address of a cell in the worksheet based on the specified row number and column number, such as ADDRESS(2,3) returns $C$2.

Syntax: ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text]), row_num is the row number, column_num is the column number, abs_num can be used to specify the returned reference type, =1 or If omitted, the absolute value is returned; =2 returns the absolute row number and relative column number; =3 returns the relative row number and absolute column number; =4 returns the relative value. The use of a1 is the same as the parameter introduction in the INDIRECT function. sheet_text is used to specify the name of the externally referenced worksheet.

Category 4: Logical operation function

● If function:

Function: Logical comparison of value and expected value

Syntax: IF(logical_test, value_if_true, [value_if_false]), when logical_test is established, return value_if_true, when logical_test is not established, return value_if_false. IF functions can be nested up to 64 times.

● Iferror function:

Function: If the calculation result of the formula is wrong, return the value you specify; otherwise, return the result of the formula. Use the IFERROR function to catch and handle errors in formulas.

Syntax: IFERROR(value, value_if_error), value is the value to be checked, value_if_error is the value returned when value is an error, error types include: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? or #NULL!.

● Ifna function:

Function: If the formula returns the error value #N/A, the result returns the value you specify; otherwise, the result of the formula is returned.

Syntax: IFNA(value, value_if_na), the same syntax as IFERROR, except that the error value range checked is different.

● And function:

is used to determine whether all conditions in the test are TRUE.

● Or function:

is used to determine whether any condition in the test is TRUE.

● Not function:

The logical negation of its parameters.

Fifth Category: Mathematical Statistics Function

● Sum function:

Function: You can add single values, cell references or ranges , or a combination of the three is added.

Syntax: SUM(number1,[number2],...)

● Sumif function:

Function: To sum the values ​​that meet the conditions, for example, to sum the values ​​greater than 5 in cells B2~B25, you can use the formula =SUMIF(B2:B25,">5")

Syntax: SUMIF(range, criteria,[sum_range]), range is the area to be calculated, and the number of characters cannot exceed 255; can wildcards be used for criteria summation conditions? and *; sun_range is an optional condition, specifying the actual summing area.

Video: Microsoft SUMIF Function Training Course

● Sumifs Function:

Function: Used to calculate the total amount of all parameters that meet multiple conditions.

Syntax: SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...), sum_range requires the sum of the areas, criteria_range1 is the condition area 1, criterion1 is the condition 1 that limits area 1 , and so on for subsequent parameters.

● Sumproduct function:

Function: In the given sets of arrays, multiply the corresponding elements between the arrays and return the sum of the products.

Syntax: SUMPRODUCT(array1, [array2], [array3], ...), array1 / array2... are several sets of array parameters that multiply and sum their corresponding elements, array parameters Need to have the same dimension, non-numeric array elements will be treated as 0.

● Count function:

Function: Count the number of cells containing numbers and the number of numbers in the parameter list.

Syntax: COUNT(value1, [value2], ...), value1 is the first item, cell application or area to calculate the number of numbers, value2 is an optional parameter and has the same function as value1.

Note: Parameters that are numbers, dates, text representing numbers (such as "1"), logical values ​​and numbers directly typed into the parameter list will be counted.

● Countif function:

Function: Used to count the number of cells that meet a certain condition

Syntax: COUNTIF (range, criteria), similar to the primary use of SUMIF

● Countifs function:

Function: Apply conditions to cells across multiple areas, and then count the number of times all conditions are met.

Syntax: COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2],…), criteria_range1 is the condition area 1, criterion1 is the limiting condition 1 specified for criteria_range1, and so on.

● Counta function:

Function: Count the number of cells that are not empty.

Syntax: COUNTA(value1, [value2], ...), value1 indicates the area to be counted, value2 is an optional parameter, and its function is the same as value1.

● Countblank function:

COUNTBLANK(range) counts the number of empty cells in the selected area.

● Max / Min function:

MAX(number1, [number2], ...), MIN(number1, [number2], ...) calculates the maximum sum of the selected area minimum value.

● Rank function:

Function: Returns the numerical ranking of a column of numbers. The ranking of a number is its size relative to other values ​​in the list.

Syntax: RANK(number,ref,[order]), number needs to be ranked; ref is an array of numbers; order optional parameter, =0 or omit descending order, = is not zero, ascending order .

● Rand function:

Function: Returns a uniformly distributed random real number greater than or equal to 0 and less than 1. A new random real number will be returned each time the worksheet is calculated.

Syntax: RAND(), if you want to generate a random real number between a and b, you can use the formula RAND()*(b-a) a

● Randbetween function:

Function : Returns a random integer between two specified numbers. A new random integer will be returned each time the worksheet is calculated.

Syntax: RANDBETWEEN(bottom, top), bottom will return the smallest integer, top will return the largest integer. For example, RANDBETWEEN(1,100)=RAND()*99 1 will return a random number between 1-100.

● Average function:

Function: Returns the average value (arithmetic mean) of the parameters.

Syntax: AVERAGE(number1, [number2], ...), number1 is the average area, number2 is an optional parameter, and its function is the same as number1.

● Subtotal function:

Function: Returns the subtotal in the list or database.

Syntax: SUBTOTAL(function_num,ref1,[ref2],...), function_num is a number 1-11 or 101-111, used to specify the function to be used for classification summary. If you use 1-11, manually hidden rows will be included; if you use 101-111, manually hidden rows will be excluded; filtered cells will always be excluded.

Category Six: Date and Time Function

● Datedif function:

Function: Calculate the number of years, months, and days between two dates, Often used in formulas for calculating age.

Syntax: DATEDIF(start_date,end_date,unit), start_date represents the starting date, end_date represents the end date. Date values ​​can be entered in various ways: as quoted text strings (for example: "2001/1/30"), serial numbers (for example, 36921, which means January 30, 2001 when using the commercial 1900 date system), or other formulas Or the result of a function (such as DATEVALUE("2001/1/30")).

Description: Dates are stored as serial numbers that can be used for calculations. By default, the serial number for December 31, 1899 is 1, and the serial number for January 1, 2008 is 39448, because it is 39448 days from January 1, 1900.

● Networkdays function:

NETWORKDAYS(start_date, end_date, [holidays]) returns the number of working days between two dates.

● Now function:

Function: Return the current date and time, and the time will be updated every time the worksheet is opened.

Syntax: NOW(), no parameters.

Notes: 1.Excel can store dates as serial numbers so that they can be used in calculations. By default, January 1, 1900 has a serial number of 1, and January 1, 2008 has a serial number of 39,448 because it is 39,447 days before January 1, 1900. 2. The number to the right of the decimal point in the serial number represents the time, and the number to the left represents the date. For example, a sequence number of 0.5 indicates that the time is 12:00 noon. The result of the 3.NOW function only changes when a worksheet is calculated or a macro containing the function is run.

● Today function:

Function: Return the current date, automatically update the date when opening the workbook, often used to calculate age, etc.

Syntax: TODAY(), no parameters.

● Weekday function:

Function: Return the day of the week for the corresponding date

Syntax: WEEKDAY(serial_number,[return_type]), Serial_number is a serial number, representing The date of the day you are trying to find. Dates should be entered using the DATE function or as the result of another formula or function. For example, use the function DATE(2008,5,23) to enter May 23, 2008. Return_type optional parameter, a number used to determine the return value type.

● Weeknum function:

Function: Return the week number of the date

Syntax: WEEKNUM(serial_number,[return_type]), Serial_number is required. Represents the day of the week. Dates should be entered using the DATE function or as the result of another formula or function. For example, use the function DATE(2008,5,23) to enter May 23, 2008. Return_type optional parameter, determines the day on which the week starts. The default value is 1.

● Date function:

Function: Combine three independent values ​​into one date

Syntax: DATE (year, month, day), year year, month month , day

● Year / Month / Day function:

The parameter is the date, and the year, month and day information can be obtained respectively.

● Hour / Minute / Second function:

The parameter is time, and you can get hours, minutes, and seconds respectively.

● Time function:

Combines three independent values ​​into one time, similar to the DATE function.

Category 7: Format display function

● Text function:

Function: Display numbers in a specified way, often used in conjunction with other functions , for example, when merging text values, the values ​​need to be displayed in a specific format. In this case, the TEXT function can be used.

Grammar: TEXT(Value you want to format, "Format code you want to apply")

● Upper / Lower functions:

UPPER(text), LOWER( text) can output text in uppercase and lowercase letters respectively.

● Proper function:

Convert the first letter of the text string to uppercase and the remaining letters to lowercase.

● Roud function:

Function: Rounds a number to a specified number of digits.

Syntax: ROUND(number, num_digits), number is the number to be rounded, num_digits is the number of digits to be rounded, >0 is rounded to the specified number of decimal places, =0 is rounded to the nearest integer,

● Roudup function:

RANDUP syntax is the same as RAND, except that the number is rounded up instead of rounded.

● Rouddown function:

RANDDOWN syntax is the same as RAND, except that the number is rounded down instead of rounded.

● Rept function:

Function: Repeat text a specified number of times, generally used to fill text strings in cells.

Syntax: REPT(text, number_times), text needs to be displayed repeatedly, number_times needs to be repeated the number of times.

● Fixed function:

Function: Round the number to the specified number of decimal places, use periods and commas, format the number in decimal number format, and return it in text form result.

Syntax: FIXED(number, [decimals], [no_commas]), number should be rounded and converted to the number in this article, decimals (optional) number of digits to the right of the decimal point, no_commas (optional) logical value , if TRUE will prevent the text returned by FIXED from containing commas.

If decimals is a negative number, number is rounded to the left by the corresponding number of digits from the decimal point.

If decimals are omitted, its value is assumed to be 2.

For more Excel related tutorials, please visit the Excel Basic Tutorial column!

The above is the detailed content of Summary of commonly used functions in excel. For more information, please follow other related articles on the PHP Chinese website!