What are the commonly used Excel function formulas? In the workplace, Excel is most commonly used for statistical analysis of data. If you don’t master a certain amount of skills, you will definitely turn to others for help during the office process. Today, the editor will give you It brings 43 Excel function formulas to help you stop asking for help in the workplace.
It is a functional module that is predefined within Excel and performs calculations, analysis and other data processing tasks in a specific order and structure. Therefore, functions are called "special formulas". Like formulas, the final return result of an Excel function is a value. A function has a unique name that is not case-sensitive and determines its function and purpose.
To give a simple example - when processing a table, how to change the first letter of the name to all uppercase?
If you don’t understand functions, do you manually modify them one by one? If you know the Proper function, you won't modify it one by one. Enter the function formula and get it done in 3 seconds!
Formulas are calculations designed by users and combined with constant data, cell references, operators and other elements for data processing and calculation. Users use formulas to calculate results for a purpose, so Excel's formulas must (and can only) return values.
The structure of the formula: =(C2+D2)*5 From the perspective of the formula structure, the elements that make up the formula usually include elements such as equal signs, constants, references, and operators. Among them, the = sign is indispensable. But in actual applications, formulas can also be operated using arrays, Excel functions, or names (named formulas).
Normally, Excel performs formula operations in order from left to right. When multiple operators are used in the formula, Excel will perform operations according to the priority of each operator. For operators of the same level, operations will be performed from left to right. And the sequential operation on the right. The specific priority order is as follows:
When using Excel formulas for calculations, the correct result may not be obtained for some reason and an error value is returned. Common error values and their meanings are shown in the table below.
When the result of a formula returns an error value, the cause of the error should be found promptly and the formula should be modified to solve the problem.
Excel functions usually consist of function name, left parenthesis, parameters, half-width comma and right parenthesis.
Function formula structure: =IF(A1>0,"positive number", IF(A1<0,negative number,"")) For the parameters of the function, it can be composed of elements such as numerical values, dates, and text, or constants can be used , array, cell reference or other function.
When a function's parameters are also functions, Excel calls it nesting of functions. There are 11 types of functions in total, including database functions, date and time functions, engineering functions, financial functions, information functions, logical functions, query and reference functions, mathematical and trigonometric functions, statistical functions, text functions and user-defined functions.
The content of this article is in catalog format, introducing what each function does, which function can be used to solve a certain problem, etc. You can learn the specific usage method on Baidu.
For functions, you don’t need to memorize them by rote. You only need to know what type of function should be selected, and what parameters are required and how to use them! For example, select fields, use LEFT/RIGHT/MID functions...leave other details to the almighty Baidu!
The following is a classification and introduction of these commonly used necessary functions according to different application scenarios.
1. Association matching class
The required data is not in the same Excel sheet or the same Excel sheet in different sheets. Too much data is troublesome to copy and error-prone. How to integrate it? The following functions are used for multi-table association or row-row comparison. The more complex the table, the more fun it is to use!
01.VLOOKUP
Function: Used to find elements in the first column that meet the conditions.
Syntax: =VLOOKUP (lookup_value,table_array, col_index_num, [range_lookup])
*Remarks: [ ] are optional parameters, the rest are required parameters, the same applies below. =VLOOKUP (item to look for, location to look for, column number in the range containing the value to be returned, return an approximate or exact match - indicated as 1/TRUE or 0/FALSE). Example: Query the position of the employee whose name is in cell F5.
02.HLOOKUP
Function: Search for a value in the first row of a table or a numeric array, and then return the value in the column of the specified row in the table or array. The H in HLOOKUP stands for "line".
Syntax: =HLOOKUP(lookup_value,table_array, row_index_num, [range_lookup])
Example: =HLOOKUP("Axle",A1:C4, 2, TRUE) finds the axle in the first row and returns the value in row 2 in the same column (column A).
The difference between LOOKUP and HLOOKUP: When the comparison value is located in the first row of the data table, if you want to view the specified number of rows downwards, you can use HLOOKUP. VLOOKUP can be used when the comparison value is located in the left column of the data to be found.
03.INDEX
Function: Returns a value or a reference to a value in a table or range.
Syntax: =INDEX(array,row_num, [column_num])
Example: =INDEX(B2:D11,3,3) is the value located at the intersection of the third row and the third column in the range A2:B3.
04.MATCH
Function: Used to return the position of the specified content in the specified area (a certain row or column).
Syntax: =MATCH(lookup_value,lookup_array, [match_type])
Example: =MATCH(41,B2:B5,0) The position of the value 41 in the cell range B2:B5.
match_type：
1 or omitted: MATCH finds the maximum value less than or equal to lookup_value.
0: MATCH to find the first value that is exactly equal to lookup_value.
-1: MATCH to find the smallest value greater than or equal to lookup_value.
05.RANK
Function: Find the ranking of a certain value among a group of values in a certain area.
Syntax: =RANK(number,ref,[order])
Example: =RANK(A3,A2:A6,1) The ranking method of A3 in A2:A6 in the above table: 0 is descending, 1 is ascending, the default is 0
06.ROW
Function: Return the referenced line number.
Syntax: = ROW([reference])
Example: = ROW() The row number of the row where the formula is located
07.COLUMN
Function: Return the column where the cell is located.
Syntax=COLUMN(reference)
Example: =COLUMN (D10) returns 4 because column D is the fourth column.
08.OFFSET
Function: Returns a reference to a cell or range of cells with a specified number of rows and columns. The reference returned can be a single cell or a range of cells. You can specify the number of rows and columns to return.
Syntax: =OFFSET(reference, rows, cols,[height], [width])
Example: =OFFSET(D3,3,-2,1,1) displays the value in cell B6, where 3 is the three rows below, -2 is the two rows on the left, and 1 is the row height and column width.
Before data processing, the extracted data needs to be initially cleaned, such as clearing string spaces, merging cells, replacing, intercepting strings, and finding the location where strings appear.
Intercept string: use MID /LEFT/ RIGHT
Replace the content in a cell: SUBSTITUTE /REPLACE
Merge cells: use CONCATENATE
Clear string spaces: use TRIM/LTRIM/RTRIM
Find the location of text in a cell: FIND/SEARCH
09.MID
Function: intercept the string from the middle
Syntax: =MID(text,start_num, num_chars)
Example: =MID(A2,1,5) Starting from the first character in the string in A2, 5 characters are returned.
Extract the year and month based on the ID number.
10.LEFT
Function: intercept the string from the left.
Syntax: =LEFT(text,[num_chars])
Example: =LEFT(A2,4) The first four characters in the first string.
11.RIGHT
Function: intercept the string from the right.
Syntax: =RIGHT(text,[num_chars])
Example: =RIGHT(A2,5) the last 5 characters of the first string
12.SUBSTITUTE
Function: Replace old_text with new_text in a text string.
Syntax: =SUBSTITUTE(text,old_text, new_text, [instance_num])
Example: =SUBSTITUTE(A2, "Sales", "Cost") replaces "Sales" with "Cost" (cost data) and replaces part of the phone number.
13.REPLACE
Function: Replace the string in the cell.
Syntax: =REPLACE(old_text,start_num, num_chars, new_text)
Example: =REPLACE(A2,6,5,"*") In A2, starting from the sixth character (f), replace five characters with a single character *.
The difference between REPLACE and SUBSTITUTE: The two functions are very close. The difference is that REPLACE implements replacement according to the position, and needs to provide the replacement starting from which position, the number of replacements, and the new text after replacement; while SUBSTITUTE replaces according to the text content and needs to provide the replacement. Old text and new text, and which old text is replaced, etc. Therefore, REPLACE implements fixed-position text replacement, and SUBSTITUTE implements fixed-position text replacement.
14. CONCATENATE
Function: Concatenate two or more text strings into one string.
Syntax: =CONCATENATE(text1,[text2], ...)
Another way to merge the contents in cells is &. When there is too much content to be merged, CONCATENATE is more efficient.
Example: =CONCATENATE(B2, " ", C2) joins three parts: the string in cell B2, the space character, and the value in cell C2.
15.TRIM
Function: Removes all spaces in text except single spaces between words.
Syntax: =TRIM(text)
Text is the text to remove spaces.
Example: =TRIM("First Quarter Earnings ") removes leading and trailing spaces from the text of the formula.
16.LTRIM
Function: Remove spaces or other predefined characters from the left side of a string.
Syntax: =LTRIM (string, [charlist])
17.RTRIM
Function: Remove spaces or other predefined characters from the right side of a string.
Syntax: = LTRIM(string, [charlist])
18.FIND
Function: Find text position
Syntax: =FIND(find_text,within_text, [start_num])
Example: =FIND("M",A2) The position of the first "M" in cell A2
19.SEARCH
Function: Returns the position where a specified character or text string first appears in the string, searching from left to right.
Syntax: =SEARCH(find_text,within_text,[start_num])
Example: =SEARCH("e",A2,6) In the string in cell A2, starting from the 6th position, the position of the first "e".
The difference between FIND and SEARCH: The functions of these two functions are almost the same, and they can find the location of characters. The difference is that the FIND function searches accurately and is case-sensitive; the SEARCH function searches fuzzy and is not case-sensitive.
20.LEN
Function: Returns the number of characters in the text string.
Syntax: =LEN(text)
Example: =LEN(A1) The length of the string in cell A1
21.LENB
Function: Returns the number of bytes used to represent characters in a text string.
Syntax: =LENB(text)
Example: =LEN(A1)The number of bytes in cell string A1.
Logic, as the name suggests, does not go into details, just go to the function.
22. IF
Function: When using the logical function IF function, if the condition is true, the function will return a value; if the condition is false, the function will return another value.
Syntax: =IF(Logical,Value_if_true,Value_if_false)
The IF function returns a value if the specified condition evaluates to true and another value if the condition evaluates to false.
23.COUNTIF
Function: Used to count the number of cells that meet a certain condition; for example, count the number of times a specific city appears in the customer list.
Syntax: =COUNTIF(cell 1: cell 2, condition)
Count the number of times a specific store appears in the list.
24.AND
Function: logical judgment, equivalent to "union".
Syntax: If all parameters are True, True will be returned. It is often used for multi-condition judgment.
Example: =AND(A2>1,A2<100) If A2 is greater than 1 and less than 100, TRUE is displayed; otherwise, FALSE is displayed.
25.OR
Function: logical judgment, equivalent to "or".
Syntax: As long as the parameter has a True, Ture will be returned, often used for multi-condition judgment.
Example: =OR(A2>1,A2<100) If A2 is greater than 1 or less than 100, TRUE is displayed; otherwise, FALSE is displayed.
When using Excel table statistics, it is often necessary to use various formulas that come with Excel, which is also the most commonly used type. (For these, Excel comes with shortcut functions)
26.MIN
Function: Find the minimum value in a certain area.
Syntax: =MIN(number1, [number2], ...)
Example: =MIN(D2:D11) The minimum number in the range D2:D11.
27.MAX
Function: Find the maximum value in a certain area.
Syntax: =MAX(number1, [number2], ...)
Example: =MAX(A2:A6) The maximum value in the area A2:A6.
28.AVERAGE
Function: Calculate the average value in a certain area.
Syntax: =AVERAGE(number1, [number2], ...)
Example: =AVERAGE(D2:D11) The average of the numbers in the cell range D2 to D11.
29.COUNT
Function: Count the number of cells containing numbers.
Syntax: =COUNT(value1, [value2], ...)
Example: =COUNT(A2:A7) Counts the number of cells containing numbers in the cell range A2 to A7.
30.COUNTIFS
Function: Count the number of cells specified by a given set of conditions.
Syntax: COUNTIFS(criteria_range1,criteria1, [criteria_range2, criteria2],…)
Example: =COUNTIFS(A2:A7,"<6",A2:A7,">1") calculates how many numbers between 1 and 6 (excluding 1 and 6) are contained in cells A2 to A7.
31.SUM
Function: Calculate the sum of all values in the cell range.
Syntax: =SUM(cell 1:cell 2)
Example: =SUM(A2:A10) adds the values in cells A2:10 together.
32.SUMIF
Function: Find the sum of cells that meet the conditions.
Syntax: =SUMIF(range,criteria, [sum_range])
Example: =SUMIF(A2:A7,"Fruit",C2:C7) The sum of sales of all foods under the "Fruit" category.
32.SUMIFS
Function: Sum a group of cells that meet the specified conditions.
Syntax: =SUMIFS(sum_range,criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Example: =SUMIFS(A2:A9, B2:B9, "=香*", C2:C9, "LUNING") Calculate the total quantity of products starting with "香" and sold by "LUNING".
33.SUMPRODUCT
Function: Returns the sum of the corresponding array or area products.
Syntax: =SUMPRODUCT (array1, [array2], [array3], ...)
Example: =SUMPRODUCT(Table1!A1:Table1!A100,Table2!B1:Table2!B100) Calculate the sum of the products of A1 to A100 of Table 1 and B1 to B100 of Table 2, that is, A1*B1+A2*B2+A3* B3+…
34.STDEV
Function: Estimate the standard deviation based on the sample.
Syntax: STDEV(number1,[number2],...)
Example: =STDEV(D2:D17) standard deviation of column
35.SUBSTOTAL
Function: Returns the subtotal in the list or database.
Syntax: =SUBTOTAL(function_num,ref1,[ref2],...)
Example: =SUBTOTAL(9,A2:A5) uses 9 as the first parameter to calculate the sum of the subtotal values in cells A2:A5.
http://36.INT／ROUND
Function: The ROUND function rounds a number to a specified number of digits.
Syntax: =ROUND(A1, 2)
Example: =ROUND(2.15, 1) rounds 2.15 to one decimal place
Function: INT rounds a number down to the nearest integer.
Syntax:=INT(8.9) Rounds 8.9 down to the nearest integer.
Specially designed to handle time formats and conversions.
37.TODAY
Function: Returns the serial number of the current date.
Syntax: =TODAY()
li'z=TODAY()+5 returns the current date plus 5 days. For example, if the current date is 1/1/2012, this formula returns 1/6/2012.
38.NOW
Function: Returns the serial number of the current date and time.
Syntax: =Now()
=NOW()+7 returns the date and time 7 days later.
39.YEAR
Function: Returns the year corresponding to a certain date.
Syntax: =YEAR(serial_number)
=YEAR(A3) The year of the date in cell A3
40.MONTH
Function: Returns the month in the date.
Syntax: =MONTH(serial_number)
=MONTH(A2) The month of the date in cell A2
41.DAY
Function: Returns the number of days in a date expressed as a serial number.
Syntax: =DAY(serial_number)
=DAY(A2) The number of days in the date in cell A2
42.WEEKDAY
Function: Returns the day of the week corresponding to a certain date. By default, the number of days is an integer in the range 1 (Sunday) to 7 (Saturday).
Syntax: =WEEKDAY(serial_number,[return_type])
=WEEKDAY(A2) The day of the week from 1 (Sunday) to 7 (Saturday)
=WEEKDAY(A2, 2) The day of the week from 1 (Monday) to 7 (Sunday).
43.DATEDIF
Function: Calculate the number of days, months or years between two dates.
Syntax: =DATEDIF(start_date,end_date,unit)
=DATEDIF(Start_date,End_date,"Y")The number of years in a period
=DATEDIF(Start_date,End_date,"D")The number of days in a period
=DATEDIF(Start_date,End_date,"YD") ignores the year in the date and the number of days in a period
Attached:
