Excel common functions:
1. ABS function.
Function name: abs
Main function: Find the absolute value of the corresponding number.
Use the format: abs(number).
Parameter description: number represents the numeric value or the referenced cell of the absolute value.
Application example: If you enter the formula: =abs(A2) in cell B2, a positive number (such as 100) will be displayed in cell B2 regardless of whether you enter a positive number (such as 100) or a negative number (such as -100).
Special reminder: If the number parameter is not a numeric value, but some characters (such as a, etc.), the wrong value "value!" is returned in b2”。
100 Helping Plan 2, and function.
Function name: and
Main function: Return logical values: If all parameter values are logical "true", the logical logic "true" is returned, otherwise the logical logic is returned "false".
Use the format: and(logical1,logical2, ..)
Parameter description: logical1, logical2, logical3......: indicates the condition value or expression to be tested, up to 30 of which are used.
Example: Enter the formula: =and(a5>=60,b5>=60 in cell C5) to 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, at least one of the values in A5 and B5 is less than 60.
Special reminder: If the specified logical condition parameter contains a non-logical value, the function returns the wrong value "value!".or "name".
3. **erage function.
Function name: **erage
Main function: Find the arithmetic mean of all parameters.
Use the format: **erage(number1,number2,......
Parameter description: number1, number2,......You need to find the average value or reference cell (region), and the number of parameters should not exceed 30.
Example: Enter the formula: =**erage(b7:d7,f7:h7,7,8) in cell b8, and then you can find the average value of the sum of the values in the b7 to d7 and f7 to h7 regions.
Special reminder: If the reference area contains a "0" value cell, it is 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 the format: column(reference).
Parameter description: reference is the referenced cell.
Application example: Enter the formula: =column(b11) in cell C11, and it will be displayed as 2 (i.e., column b) after confirmation.
Special reminder: If you enter the formula: =column() in cell b11, 2; is also displayedThere is also a function that returns the value of the row label - row(reference).
5. concatenate function.
Function name: concatenate
Main function: Concatenate data from multiple characters, text, or cells together and display them in one cell.
Use the format: concatenate(text1,text......)
Parameter description: text1, text2......Characters that need to be connected to the text or the cells that are referenced.
Example: Enter the formula =concatenate(a14,) in cell C14"@",b14,".com"), after confirmation, you can combine the characters in cell A14, @, and characters in cell B14. com is connected as a whole and is displayed in cell C14.
Special reminder: If the parameter is not a referenced cell, and it is in text format, please add double quotation marks in the English state to the parameter, if the above formula is changed to: =a14&"@"&b14&".com"can also achieve the same purpose.
6. countif function.
Function name: countif
Main function: Count the number of cells that meet the specified conditions in a cell range.
Use the format: countif(range,criteria).
Parameter description: range represents the range of cells to be countedcriteria indicates the specified condition expression.
Example: Enter the formula: =countif(b1:b13,) in cell C17">=80"After confirmation, you can count the number of cells in the cell area from B1 to B13 with a value greater than or equal to 80.
Special reminder: Blank cells appear in the referenced cell area.
7. date function.
Function name: date
Main function: Gives the date of the specified value.
Use the format: date(year,month,day).
Parameter description: year is the specified value of the year (less than 9999).month is the specified month value (can be greater than 12).day is the specified number of days.
Example: Enter the formula: =date(2003,13,35) into cell C20, and after confirmation, 2004-2-4 is displayed.
Special reminder: Since the above formula is 13, there is an extra month, which is postponed to January 2004;The number of days was 35, which was four days more than the actual number in January 2004 and was postponed to 4 February 2004.
8. Function name: datedif
Main function: Calculate the difference between the parameters that return two dates.
Use the format: =datedif(date1,date2,"y")、=datedif(date1,date2,"m")、=datedif(date1,date2,"d")
Parameter description: date1 represents the previous date, and date2 represents the next datey(m,d) requires the return of the number of years (months, days) between the two dates.
Example: Enter the formula: =datedif(A23,today(),) in cell C23"y"), which returns the difference between the current date of the system [denoted by today()) and the date in cell A23, and returns the number of years apart.
Special reminder: This is a hidden function in Excel, which cannot be found in the function wizard, and can be entered and used directly, which is very effective for calculating age, length of service, etc.
9. Day function.
Function name: day
Main function: Find the number of days for a specified date or a date in a reference cell.
Use the format: day(serial number).
Parameter description: Serial number represents the specified date or referenced cell.
Example: Enter the formula: =day(.)."2003-12-18"), after confirmation, shows 18.
Special reminder: If it is a given date, please include it in double quotation marks.
10. dcount function.
Function name: dcount
Main function: Returns the number of cells in the database or list that meet the specified conditions and contain numbers.
Use the following format: dcount(database,field,criteria).
Parameter description: database indicates the range of cells to be countedfield indicates the data column used by the function (there must be a flag item in the first row);criteria contains the range of cells for the condition.
Application example: As shown in Figure 1, enter the formula: =dcount(a1:d11,) in cell F4"Language", f1:g2), after confirmation, you can find the number of numeric cells in the "language" column, with a score greater than or equal to 70 and less than 80 (equivalent to the number of people in the score segment).
Special reminder: If you modify the above formula 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 data in a region as a vertical array.
Use the format: frequency(data array, bins array).
Parameter description: A data array represents a set of data or a range of cells that is used to calculate the frequencybins array is represented as a column of values separated by the preceding array.
Application example: As shown in Figure 2, select the cell range from B32 to B36 at the same time, enter the formula: =frequency(B2:B31,D2:D36), and press the "Ctrl+Shift+Enter" key combination to confirm after the input is completed, you can find the number of occurrence frequencies of each segment in the B2 to B31 region, separated by the D2 to D36 region (equivalent to counting the number of people in each segment).
Special reminder: The above input is an array formula, after the input is completed, you need to confirm by pressing the "Ctrl+Shift+Enter" key combination, and a pair of curly braces will appear at both ends of the formula after confirmation ({} This curly brace cannot be entered directly.
12. if function.
Function name: if
Main function: According to the true or false results of the logical judgment of the specified conditions, the corresponding content is returned.
Use the format: =if(logical,value if true,value if false).
Parameter description: logical stands for logical judgment expressionvalue if true indicates the displayed content when the judgment condition is logical "true", and returns "true" if ignoredvalue if false indicates the displayed content when the judgment condition is logically false, and returns false if ignored.
Example: Enter the formula =if(c26>=18,) in cell C29"Meets the requirements","Does not meet the requirements"After being convinced, if the value in cell C26 is greater than or equal to 18, cell C29 will display the word "Meets the requirements", and vice versa.
Special reminder: The cells in this article are similar to those specified in "Enter Formulas in C29 Cells", readers do not need to be bound by them when using them, here are just the corresponding cells given with the needs of the examples attached to this article, please refer to the attached example files.
13. Index function.
Function name: index
Main function: Returns the value of an element in a list or array, which is determined by the index values of the row and column numbers.
Use the format: index(array,row num,column num).
Parameter description: array represents a cell range or array constantrow num represents the specified row sequence number (if row num is omitted, there must be column num);column num represents the specified column number (if column num is omitted, there must be row num).
Example: As shown in Figure 3, enter the formula: =index(A1:D11,4,3) into cell F8, and after confirmation, the contents of the cell at the intersection of row 4 and column 3 (i.e., C4) in the cell range from A1 to D11 are displayed.
Special reminder: The row num and column num parameters here are relative to the referenced cell range, not the row or column num in the excel sheet.
14. int function.
Function name: int
Main function: Round down the value to the nearest integer.
Use the format: int(number).
Parameter description: number indicates the numeric value to be rounded or the reference cell containing the numeric value.
Example: Enter the formula: =int(18.89), and 18 after confirmation.
Special reminder: no rounding is done when rounding;If the input formula is =int(-18.).89), the return result is -19.
15. iserror function.
Function name: iserror
Main function: It is used to test whether the value returned by the function is wrong. If there is an error, the function returns true, otherwise it returns false.
Use the format: iserror(value).
Parameter description: value indicates the value or expression to be tested.
For example, if the B35 cell is empty or "0", then the a35 b35 error occurs, and the above function returns true and false otherwise.
Special reminder: This function is usually used with the if function, if you modify the above formula 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
results. 16. Left function.
Function name: left
Main function: Truncate a specified number of characters starting from the first character of a text string.
Use the format: left(text,num chars).
Parameter description: text represents the string of characters to be truncatednum chars represents the given number of intercepts.
Example: Suppose the string "I like Tianjiwang" is stored in cell A38, we enter the formula =left(a38,3) into cell C38, and the character "I like" will be displayed after confirmation.
Special reminder: The English meaning of this function name is "left", that is, it is intercepted from the left, and many functions in excel take its English meaning.
17. len function.
Function name: len
Main function: Count the number of characters in a text string.
Use the format: len(text).
Parameter description: text indicates the text string to be counted.
Example: Suppose the string "I am 28 years old" is stored in cell A41, we enter the formula =len(a40) into cell C40, and the statistical result "6" will be displayed after confirmation.
Special reminder: When len is to be counted, each character is counted as "1", regardless of whether it is a full-width character or a half-width character;The corresponding function, lenb, counts half-width characters as "1" and full-width characters as "2" in statistics.
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 way.
Use the format: match(lookup value,lookup array,match type).
Parameter description: lookup value indicates the value to be found in the data table
The lookup array represents a contiguous range of cells that may contain the numeric value you are looking for;
match type represents the value or 1 of the lookup method).
If the match type is -1, find the lowest value greater than or equal to the lookup value, and the lookup array must be sorted in descending order;
If the match type is 1, the lookup array must be in ascending order to find the maximum value less than or equal to the lookup value
If the match type is 0, look for the first value equal to the lookup value, and the lookup array can be arranged in any order;If the match type is omitted, it defaults to 1.
Example: As shown in Figure 4, enter the formula: =match(e2,b1:b11,0) into cell F2, and return the result "9" after confirmation.
Special reminder: The lookup array can only be one column or row.
19. max function.
Function name: max
Main function: Find the maximum value in a set of numbers.
Use the format: max(number1,number2......)
Parameter description: number1, number2,......Represents the numeric value or reference cell (region) that needs to be found for the maximum value, and the number of parameters does not exceed 30.
Example: Enter the formula: =max(e44:j44,7,8,9,10), and after confirmation, the maximum value of the cells and regions from e44 to j44 and the values 7,8,9,10 will be displayed.
Special reminder: If there is text or logical value in the parameter, ignore it.
20. MID function.
Function name: mid
Main function: Truncate a specified number of characters from the specified position of a text string.
Use the format: mid(text,start num,num chars).
Parameter description: text represents a text stringstart num indicates the specified start position;num chars indicates the number to be intercepted.
Application example: Suppose the string "I like Tianjiwang" is stored in cell A47, we enter the formula =mid(a47,4,3) into cell C47, and the characters of "Tianjiwang" will be displayed after confirmation.
Special reminder: The parameters in the formula should be separated by a comma "," in the English state.
21. min function.
Function name: min
Main function: Find the minimum value in a set of numbers.
Use the format: min(number1,number2......)
Parameter description: number1, number2,......Represents the numeric value or reference cell (range) that needs to be found with the minimum value, and the number of parameters does not exceed 30.
Example: Enter the formula: =min(e44:j44,7,8,9,10), and after confirmation, the minimum value of the unit and region and the value 7,8,9,10 from e44 to j44 will be displayed.
Special reminder: If there is text or logical value in the parameter, ignore it.
22. mod function.
Function name: mod
Main function: Find the remainder of the division of two numbers.
Use the format: mod(number, divisor).
Parameter description: number represents the number to be divideddivisor stands for divisor.
Example: Enter the formula: =mod(13,4), and the result "1" will be displayed after confirmation.
Special reminder: If the divisor parameter is zero, the error value "div 0!" is displayed”；The mod function can be represented by the 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 the date in the reference cell.
Use the format: month(serial number).
Parameter description: Serial number represents the specified date or referenced cell.
Example: Enter the formula: =month(."2003-12-18"), after confirmation, shows 11.
Special reminder: If it is a given date, please include it in double quotation marks. If you modify the above formula to: =year("2003-12-18"), the value of 2003 is returned.
24. now function.
Function name: now
Main function: Give the current system date and time.
Use the format: now().
Parameter description: This function does not require parameters.
Example: Enter the formula: =now() and the current system date and time will be displayed immediately after confirmation. If the system date and time change, simply press the F9 function key to make it change with it.
Special reminder: The displayed date and time format can be reset by cell formatting.
25. OR function.
Function name: or
Main function: Returns logical values, only when all parameter values are logical "false", otherwise the logical logic "true" is returned.
Use the format: or(logical1,logical2, ..)
Parameter description: logical1, logical2, logical3......: indicates the condition value or expression to be tested, up to 30 of which are used.
Example: Enter the formula: =or(a62>=60,b62>=60) in cell c62 to confirm. If true is returned in c62, at least one of the values in a62 and b62 is greater than or equal to 60, and if false is returned, the values in a62 and b62 are less than 60.
Special reminder: If the specified logical condition parameter contains a non-logical value, the function returns the wrong value "value!".or "name".
26. Rank function.
Function name: rank
Main function: Returns the ranking of a certain value in a column of values relative to other values.
Use the format: rank(number,ref,order).
Parameter description: number indicates the numeric value to be sortedref represents the range of cells in which the sorted value is located;order represents the sort by parameter (if it is "0" or ignored, it will be ranked in descending order, that is, the higher the value, the lower the ranking result;If it is a non-"0" value, it is ranked in ascending order, i.e., the higher the value, the higher the ranking result value;）。
Example: If you enter the formula: =rank(b2,$b$2:$b$31,0 in cell C2), you can get the ranking result of student D1's Chinese score in the class score after confirmation.
Special reminder: In the above formula, we let the number parameter take the form of relative reference, and let the ref parameter take the form of absolute reference (add a "$" sign), after setting it, select the C2 cell, move the mouse to the lower right corner of the cell, when it is in the shape of a thin crosshair (usually called "fill handle"), hold down the left button and drag down, you can quickly copy the above formula to the cell below column C, and complete the ranking statistics of other students' language scores.
27. right function.
Function name: right
Main function: Truncate a specified number of characters starting from the last character of a text string.
Use the format: right(text,num chars).
Parameter description: text represents the string of characters to be truncatednum chars represents the given number of intercepts.
Application example: Suppose the string "I like Tianjiwang" is saved in cell A65, we enter the formula =right(A65,3) in cell C65, and the character of "Tianjiwang" will be displayed after confirmation.
Special reminder: The num chars parameter must be greater than or equal to 0, if ignored, it will default to 1;If the num chars parameter is greater than the text length, the function returns the entire text.
28. subtotal function.
Function name: subtotal
Main function: Return to a list or subtotals in a database.
Use the format: subtotal(function num, ref1, ref2, ..)
Parameter description: function num is a number between 1 and 11 (including hidden values) or 101 to 111 (ignoring hidden values), which is used to specify which function to use in the list for classification and summary calculations (as shown in Figure 6).ref1, ref2,……No more than 29 areas or citations should be classified and summarized.
Application example: As shown in Figure 7, enter the formula: =subtotal(3,c2:c63) and =subtotal103,c2:c63 in b64 cells and c64 cells respectively, and hide 61 rows, after confirmation, the former is displayed as 62 (including hidden rows), and the latter is displayed as 61, excluding hidden rows.
Note: If automatic filtering is used, the subtotal function will ignore any rows that are not included in the filtering results, regardless of the type of function num parameter selectedThe subtotal function applies to columns or vertical areas, but not to rows or horizontal areas.
29. Function name: sum
Main function: Calculate the sum of all parameter values.
Use the format: sum(number1,number2......）
Parameter description: number1, number2,......Represents the value that needs to be calculated, which can be a specific numeric value, a referenced cell (region), a logical value, etc.
Application example: As shown in Figure 7, enter the formula: =sum(d2:d63) in cell d64, and then you can find the total score of the language after confirmation.
Special reminder: If the argument is an array or a reference, only the numbers in it will be calculated. Blank cells, logical values, text, or error values in arrays or references are ignoredIf you modify the above formula to: =sum(large(d2:d63,)), you can find the sum of the top 5 results.
30. sumif function.
Function name: sumif
Main function: Calculate the sum of numerical values in a range of cells that meet the specified conditions.
Use the format: sumif(range,criteria,sum range).
Parameter description: range represents the range of cells that are determined by the conditioncriteria is a specified condition expressionSum Range represents the range of cells where the numeric value needs to be calculated.
Application example: As shown in Figure 7, enter the formula: =sumif(C2:C63,) in cell D64"Male", d2:d63), after confirmation, you can find the sum of the language scores of the "male" students.
Special reminder: If you modify the above formula to: =sumif(c2:c63,"Female", d2:d63), you can find the language score of the "female" student and ;The words "male" and "female" need to be placed in double quotation marks in English because they are textual"Male"、"Female").
31. text function.
Function name: text
Main function: Convert the corresponding numbers into text form according to the specified numeric format.
Use the format: text(value,format text).
Parameter description: value represents the numeric value or the referenced cell to be convertedformat text is a numeric format in the form of specified text.
Example: If cell b68 has the value 128045, we enter the formula in cell C68: =text(b68,"$0.00"), and when confirmed, it will show "$1280."45”。
Special reminder: The format text parameter can be determined according to the type in the Number tab of the Format Cells dialog box.
32. today function.
Function name: today
Main function: give the system date.
Use the format: today().
Parameter description: This function does not require parameters.
Example: Enter the formula: =today() and the system date and time will be displayed immediately after confirmation. If the system date and time change, simply press the F9 function key to make it change with it.
Special reminder: The displayed date format can be reset by the cell format (see attachment).
33. value function.
Function name: value
Main function: Convert a text string representing a numeric value to a numeric value.
Use the format: value(text).
Parameter description: text indicates that you need to convert the value of a text-based string.
Example: If the text string in cell b74 is intercepted by a function such as left, we enter the formula: =value(b74) into cell c74, and after confirmation, we can convert it to a numeric type.
Note that if textual values do not go through the above conversion, they will often return an error when processing them with functions.
34. vlookup function.
Function name: vlookup
Main function: Find the specified value in the first column of the data table, and return the value at the specified column in the current row of the data table.
Format: vlookup(lookup value, table array, col index num, range lookup).
Parameter description: The lookup value represents the value to be searchedThe table array represents the range of cells in which the data needs to be found;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 in column 2 of the table array is returned, and if it is 3, the value of column 3 is returned......Range lookup is a logical value, and if true or omitted, it returns an approximate match value, that is, if an exact match value is not found, it returns the maximum value less than the lookup valueIf false, an exact match value is returned, and if it is not found, an error value of n a is returned.
Application example: See Figure 7, we enter the formula in cell D65: =VlookUp(B65,B2:D63,3,false), after confirmation, as long as you enter a student's name in cell B65 (such as Ding 48), cell D65 will immediately display the student's language score.
Note: The lookup value must be in the first column of the table array areaIf the range lookup parameter is ignored, the first column of the table array must be sortedIn the wizard of this function, the usage of the range lookup parameter is incorrect.
35. weekday function.
Function name: weekday
Main function: Gives the corresponding number of weeks for a specified date.
Use the format: weekday(serial number, return type).
Parameter description: serial number represents the specified date or references a cell containing a dateThe return type represents the representation of the day of the week [1 for Sunday and 7 for Saturday, this parameter is 1;When Monday is 1 and Sunday is 7, the parameter is 2 (this situation is in line with the Xi Xi of the Chinese people);When Monday is 0 and Sunday is 6, this parameter is 3].
Example: Enter the formula: =weekday(today(),2), and the number of weeks of the system date will be given after confirmation.
Special reminder: If it is a specified date, please put it in double quotation marks in the English state, such as =weekday("2003-12-18",2)。
One. Cell color effect all**, Format Conditional formatting, conditional selection "Formula", the formula is as follows, then select "Format" button, "Pattern", select the desired color.
1.Interlaced color effect (odd row color):
mod(row(),2)=1
2.Interlaced color effect (even-numbered line color):
mod(row(),2)=0
3.If you want to format the shading to apply every 3 lines, you can use the formula:
mod(row(),3)=1
4.If you want to set different shading for odd-even columns, just change row() in the formula to column(), for example:
mod(column(),2)
5.If you want to set the chess board shading (white custom color):
mod(row()+column(),2)
Note: This conditional formula is used to determine whether the remainder of the sum of the row number and column number divided by 2 is 0. If it is 0, it means that the parity of the number of rows and the number of columns is the same, then the filled cell is the specified color, otherwise it is not filled. In conditional formatting, when a formula result returns a number, a non-0 number is true, a 0 number is false, and an error value is false. Therefore, the above formula can also be written as:
mod(row()+column(),2)<>0
6.If you want to set the chess board shading (custom color custom color):
Added Style 2:
mod(row()+column(),2)=0
Two. Filter by color.
Excel2007 can be sorted by cell color, font color, or icon.
If it is excel2003, you need to add auxiliary columns, use the method of defining the name to get the corresponding color number, and then sort the auxiliary columns:
The color cell is in column A, check B1, insert -> name -> definition, enter a, and enter the formula =getcell(24,$a 1), which can obtain the serial number of the font color. B1 Enter =a to copy the formula down to the corresponding line.
Sort the entire table by column B. If you want to get the background color serial number, change 24 to 63.
To clarify:1There is no corresponding function in excel2003, but you can use excel4The one with 0 is called getcell, but the macro function cannot be used directly in Excel2003, and must be defined as a name to be used.
2.get.cell(num,cell) is the format of this function, get.cell can get a variety of information in a cell, such as border format, etc., num is specified to get that kind of information, 24 is to let this function take out the color information of the cell, and after taking it out, it is a number of 1 56, which represents the number of a certain color.
3.If you enter the name you defined in column B1 and fill it in, then the number in B1 will be the color in A1, the number in B2 will be the color in A2, and so on.
4.Now if you sort by column B, it's actually sorted by the color of column A.