Shulou(Shulou.com)05/31 Report--

Today, I would like to talk to you about office skills, which may not be well understood by many people. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something from this article.

Word's trick:

First, enter three "=", enter, and get a double straight line

Enter three "~", enter, and get a wavy line.

Enter three "*" or "-" or "#", enter, and there will be many surprises.

Enter = now () to display the date in the cell

Enter = CHOOSE (WEEKDAY (I3p2), "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday") in the cell to show the day of the week

A complete Collection of Common functions in Excel

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 numerical value or a referenced cell that requires an absolute value.

Application example: if you enter the formula = ABS (A2) in B2 cell, a positive number (e.g. 100) is displayed in B2 cell whether you enter a positive number (e.g. 100) or a negative number (e.g.-100) in 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 values: if all parameter values are logical "TRUE", then return logical "TRUE", otherwise return logical "FALSE".

Use format: AND (logical1,logical2,...)

Parameter description: Logical1,Logical2,Logical3... Represents the conditional value or expression to be tested, up to 30

Application example: enter the formula in C5 cell: = 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, at least one of the values in A5 and B5 is less than 60.

Special reminder: if the specified logical condition parameter contains an non-logical value, the function returns the error value "# VALUE!" Or "# NAME".

3. AVERAGE function

Function name: AVERAGE

Main function: find out the arithmetic average of all parameters.

Use format: AVERAGE (number1,number2, …)

Parameter description: number1,number2,... Require an average value or reference cell (range) with no more than 30 parameters.

Application example: enter the formula = AVERAGE in the B8 cell: = B7, D7, F7, H7, H7, 8). After confirmation, the values in B7 to D7, F7 to H7 and the average values of 7 and 8 can be obtained.

Special reminder: if the reference range contains a "0" value cell, it will be counted; if the reference range contains blank or character cells, it will not be 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: = COLUMN (B11) in C11 cell, and display as 2 (i.e. B column) after confirmation.

Special reminder: if you enter the formula: = COLUMN () in the B11 cell, it also shows 2; corresponding to it is a function that returns the row label value-ROW (reference).

5. CONCATENATE function

Function name: CONCATENATE

Main function: connect the data in multiple character text or cells and display them in one cell.

Use format: CONCATENATE (Text1,Text...)

Parameter description: Text1, Text2... Is the character text or referenced cell that needs to be connected.

Application example: in the C14 cell, enter the formula: = CONCATENATE (A14, "@", B14, ".com"), after confirmation, you can connect the characters in the A14 cell, @, B14 cell and .com as a whole, displayed in the C14 cell.

Special reminder: if the parameter is not a referenced cell and is in text format, please add double quotation marks to the parameter in English state. If you change the above formula to: = A14 & "@" & B14 & ".com", you can also achieve the same purpose.

6. COUNTIF function

Function name: COUNTIF

Main function: count the number of cells in a range that meet the specified conditions.

Use format: COUNTIF (Range,Criteria)

Parameter description: Range represents the range of cells to be counted; Criteria represents the specified conditional expression.

Application example: enter the formula in C17 cell: = COUNTIF (B1:B13, "> = 80"). After confirmation, you can count the number of cells whose value is greater than or equal to 80 in the range of B1 to B13 cells.

Special reminder: blank cells are allowed in the range of referenced cells.

7. DATE function

Function name: DATE

Main function: give the date of the specified value.

Use format: DATE (year,month,day)

Parameter description: year is the specified year value (less than 9999); month is the specified month value (can be greater than 12); day is the specified number of days.

Application example: enter the formula in the C20 cell: = DATE (2003 ~ 13 ~ 35). After confirmation, it shows 2004-2-4.

Special reminder: as in the above formula, the month is 13, one more month, which is extended to January 2004; the number of days is 35, which is 4 more days than the actual number of days in January 2004, so it is postponed to February 4, 2004.

8. Function name: DATEDIF

Main function: calculate the difference between two date parameters.

Use 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 date; y (m, d) requires the number of years (months, days) in which the two dates differ.

Application example: enter the formula: = DATEDIF (A23dai (), "y") in the C23 cell, and return the difference between the current date of the system [represented by TODAY ()) and the date in the A23 cell after confirmation, and return the number of years of the difference.

Special reminder: this is a hidden function in Excel, which can not be found in the function wizard and can be entered directly. It is very effective for calculating age, seniority, etc.

9. DAY function

Function name: DAY

Main function: find out the number of days of a specified date or a date in a referenced 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, 18 is displayed.

Special reminder: if it is a given date, please include it in English double quotes.

10. DCOUNT function

Function name: DCOUNT

Main function: returns the number of cells in the columns of the database or list that meet the specified conditions and contain numbers.

Use format: DCOUNT (database,field,criteria)

Parameter description: Database represents the range of cells to be counted; Field represents the data column used by the function (there must be a flag in the first row); Criteria contains the conditional range of cells.

Application example: as shown in figure 1, enter the formula in F4 cell: = DCOUNT (A1:D11, "Chinese", F1:G2), after confirmation, you can find out the "Chinese" column, the score is greater than or equal to 70, and the number of numerical cells less than 80 (equivalent to the number of fractional segments).

Special reminder: the same purpose can be achieved if the above formula is modified to = DCOUNT.

11. FREQUENCY function

Function name: FREQUENCY

Main function: returns the frequency distribution of data in an area as a vertical array.

Use format: FREQUENCY (data_array,bins_array)

Parameter description: Data_array represents a set of data or cell ranges used to calculate the frequency; Bins_array represents a column of values separated by the previous 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 B31Magi D2RV D36), press the "Ctrl+Shift+Enter" key combination to confirm after the input is completed, you can calculate the frequency of each segment of values separated by D2 to D36 in the B2 to B31 area (equivalent to counting the number of fractional segments).

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. After confirmation, a pair of curly braces ({}) appear at both ends of the formula, which cannot be entered directly.

12. IF function

Function name: IF

Main function: return the corresponding content according to the true or false result of the logical judgment of the specified condition.

Use 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 judgment condition is logical "TRUE", and returns "TRUE" if ignored; Value_if_false represents the display when the judgment condition is logical "FALSE", and returns "FALSE" if ignored.

Application example: enter the formula in the C29 cell: = IF (C26 > = 18, "meet the requirements", "do not meet the requirements"), be sure, if the value in the C26 cell is greater than or equal to 18, then the C29 cell displays the word "meet the requirements", otherwise it displays the word "does not meet the requirements".

Special reminder: this article is similar to the cell specified in the "enter the formula in the C29 cell", the reader does not need to be constrained by it when using it, here is just the corresponding cell given to meet the needs of the example attached to this article. Please refer to the attached example file.

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 row and column numbers.

Use format: INDEX (array,row_num,column_num)

Parameter description: Array represents the range of cells or array constants; Row_num represents the specified row sequence number (if row_num is omitted, there must be column_num); Column_num represents the specified column sequence number (if column_num is omitted, there must be row_num).

Application example: as shown in figure 3, enter the formula: = INDEX in the F8 cell: = C4 (A1pura D11Magin4). After confirmation, it shows the contents of the cell (that is, C4) at the intersection of row 4 and column 3 in the range of A1 to D11 cells.

Special reminder: the row number parameter (row_num) and column number parameter (column_num) here are relative to the range of cells referenced, not the row or column number in the Excel worksheet.

14. INT function

Function name: INT

Main function: round the value down to the nearest integer.

Use format: INT (number)

Parameter description: number represents the value to be rounded or the reference cell that contains the value.

Application example: enter the formula: = INT (18.89), and show 18 after confirmation.

Special reminder: when rounding, it is not rounded; if the formula entered is = INT (- 18.89), the return 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 vice versa, FALSE.

Use format: ISERROR (value)

Parameter description: Value represents the value or expression to be tested.

Application example: enter the formula: = ISERROR (A35/B35), after confirmation, if the B35 cell is empty or "0", then A35/B35 has an error, then the aforementioned function returns TRUE results, and vice versa, FALSE.

Special reminder: this function is usually used in conjunction with the IF function. If the above formula is modified to: = IF (ISERROR (A35/B35), "", A35/B35), if B35 is empty or "0", the corresponding cell is displayed as empty, and vice versa, A35/B35 is displayed.

As a result.

16. LEFT function

Function name: LEFT

Main function: intercept a specified number of characters starting from the first character of a text string.

Use format: LEFT (text,num_chars)

Parameter description: text represents the string to be truncated; num_chars represents the given number of interceptions.

Application example: suppose that the string of "I like Tianji net" is saved in the A38 cell, we enter the formula: = LEFT (A38p3) in the C38 cell, and the character "I like" is 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 of Excel take its English meaning.

17. LEN function

Function name: LEN

Main function: count the number of characters in the text string.

Use format: LEN (text)

Parameter description: text represents the text string to be counted.

Application example: suppose that the string "I am 28 years old" is saved in the A41 cell, we enter the formula: = LEN (A40) in the C40 cell, and the statistical result "6" is displayed after confirmation.

Special reminder: LEN to count, whether full-width characters or half-width characters, each character is counted as "1"; the corresponding function-LENB, half-width characters count as "1", full-width characters count as "2".

18. MATCH function

Function name: MATCH

Main function: returns the corresponding position of the elements in the array that match the specified value in the specified way.

Use format: MATCH (lookup_value,lookup_array,match_type)

Parameter description: Lookup_value represents the value to be found in the data table

Lookup_array represents a contiguous range of cells that may contain the values you are looking for

Match_type represents the value of the lookup method (- 1, 0, or 1).

If match_type is-1, look for the minimum value greater than or equal to lookup_value, and Lookup_array must be sorted in descending order

If match_type is 1 and find the maximum value less than or equal to lookup_value, the Lookup_array must be sorted in ascending order

If match_type is 0, look for the first value equal to lookup_value, and Lookup_array can be arranged in any order; if match_type is omitted, the default is 1.

Application example: as shown in figure 4, enter the formula in F2 cell: = MATCH (E2MagneB1purB11P0), and then return the search result "9" after confirmation.

Special reminder: Lookup_array can only be one column or one row.

19. MAX function

Function name: MAX

Main function: find the maximum value in a set of numbers.

Use format: MAX (number1,number2...)

Parameter description: number1,number2... Represents the numerical value or reference cell (range) that requires the maximum value, with no more than 30 parameters.

Application example: input formula: = MAX (E44purJ4re7, 8, 9, 10), after confirmation, you can show the maximum value in E44 to J44 units and regions and the numerical value 7, 8, 9, and 10.

Special warning: if there is a text or logical value in the parameter, it is ignored.

20. MID function

Function name: MID

Main function: intercept a specified number of characters from a 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; and num_chars represents the number to be intercepted.

Application example: suppose that the string of "I like Tianji net" is saved in A47 cell, and we enter the formula: = MID (A47jin4jin3) in C47 cell. After confirmation, the character of "Tianji net" is displayed.

Special reminder: the parameters in the formula should be separated by commas in English.

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 the numerical value or reference cell (range) that requires a minimum value, with no more than 30 parameters.

Application example: input formula: = MIN (E44purJ4re7, 8 and 9). After confirmation, you can show the minimum values in the units and regions of E44 to J44 and the numerical values of 7, 8, 9 and 10.

Special warning: if there is a text or logical value in the parameter, it is ignored.

22. MOD function

Function name: MOD

Main function: to find the remainder of the division of two numbers.

Use format: MOD (number,divisor)

Parameter description: number represents the divisor; divisor represents the divisor.

Application example: enter the formula: = MOD (13Jing 4), and the result "1" is displayed after confirmation.

Special reminder: if the divisor parameter is zero, the error value "# DIV/0!" is displayed; the MOD function can be expressed by borrowing the function INT: the above formula can be modified to: = 13-4*INT (13 4*INT 4).

23. MONTH function

Function name: MONTH

Main function: find the specified date or the month that refers to the date in the 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, 11 is displayed.

Special reminder: if it is a given date, please include it in English double quotation marks; if you modify the above formula to: = YEAR ("2003-12-18"), the value "2003" corresponding to the year will be returned.

24. NOW function

Function name: NOW

Main function: give the current system date and time.

Use format: NOW ()

Parameter description: this function does not require parameters.

Application example: enter the formula: = NOW (), and immediately show the current system date and time after confirmation. If the date and time of the system changes, just press the F9 function key to change it.

Special reminder: the date and time format displayed can be reset through the cell format.

25. OR function

Function name: OR

Main function: return logical values, return function result logical "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 conditional value or expression to be tested, up to 30

Application example: enter the formula in C62 cell: = OR (A62 > = 60 B62 > = 60), 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 both A62 and B62 are less than 60.

Special reminder: if the specified logical condition parameter contains an non-logical value, the function returns the error value "# VALUE!" Or "# NAME".

26. RANK function

Function name: RANK

Main function: returns the ranking of a value in a column of values relative to other values.

Use format: RANK (Number,ref,order)

Parameter description: Number represents the values that need to be sorted; ref represents the range of cells in which the sorting values are located; order represents the sorting mode parameters (if it is "0" or omitted, it is ranked in descending order, that is, the larger the value, the smaller the value of the ranking result; if it is a non-"0" value, it is ranked in ascending order, that is, the larger the value, the greater the value of the ranking result.

Application example: for example, if you enter the formula = RANK in C2 cell, you can get the ranking result of Ding 1 student's Chinese score in the whole class grade after confirmation.

Special reminder: in the above formula, we let the Number parameter take the relative reference form, and let the ref parameter take the absolute reference form (adding a "$" symbol). After this setting, select the C2 cell, move the mouse to the lower right corner of the cell, and drag down when forming a thin cross (commonly known as the "filling handle"). The above formula can be quickly copied to the cell below column C to complete the ranking statistics of other students' Chinese scores.

27. RIGHT function

Function name: RIGHT

Main function: intercept a specified number of characters starting from the last character of a text string.

Use format: RIGHT (text,num_chars)

Parameter description: text represents the string to be truncated; num_chars represents the given number of interceptions.

Application example: suppose that the string of "I like Tianji net" is saved in the A65 cell, and we enter the formula: = RIGHT (A65prime3) in the C65 cell. After confirmation, the character of "Tianji net" is displayed.

Special reminder: the Num_chars parameter must be greater than or equal to 0, if omitted, it defaults 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 the classified summary in the list or database.

Use format: SUBTOTAL (function_num, ref1, ref2,...)

Parameter description: Function_num is a number between 1 and 11 (including hidden values) or between 101and 111( ignore hidden values), which is used to specify which function is used for categorization and summary calculation in the list (see figure 6); ref1, ref2,... There are no more than 29 representative areas or references to be classified and summarized.

Example of application: as shown in figure 7, enter formulas in B64 and C64 cells: = SUBTOTAL (3grained C2 C63) and = SUBTOTAL103,C2:C63), 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.

Special reminder: if automatic filtering is taken, no matter what type of function_num parameter is selected, the SUBTOTAL function ignores any rows that are not included in the filter results; the SUBTOTAL function applies to data columns or vertical regions, not to data rows or horizontal areas.

29. Function name: SUM

Main function: calculate the sum of all parameter values.

Use format: SUM (Number1,Number2... )

Parameter description: Number1, Number2... Represents the value to be calculated, which can be a specific value, referenced cell (range), logical value, and so on.

Application example: as shown in figure 7, enter the formula: = SUM (D2:D63) in the D64 cell, and the total score of the language can be obtained after confirmation.

Special reminder: if the parameter is an array or reference, only the numbers in it will be calculated. Blank cells, logical values, text or error values in an array or reference will be ignored; if you modify the above formula to: = SUM (D2:D63, {1-D2:D63, 3-4-5}), you can find the sum of the top 5 scores.

30. SUMIF function

Function name: SUMIF

Main function: calculate the sum of values in the range of cells that meet the specified conditions.

Use format: SUMIF (Range,Criteria,Sum_Range)

Parameter description: Range represents the range of cells for conditional judgment; Criteria is the specified conditional expression; Sum_Range represents the range of cells where the value to be calculated is located.

Application example: as shown in figure 7, enter the formula: = SUMIF (C2:C63, "male", D2:D63) in the D64 cell, and you can find out the Chinese score and sum of the "male" student after confirmation.

Special reminder: if the above formula is modified to: = SUMIF (C2:C63, "female", D2:D63), the language scores of "female" students can be obtained; because "male" and "female" are text-based, they need to be placed in double quotation marks ("male" and "female") in English.

31. TEXT function

Function name: TEXT

Main function: convert the corresponding number to text form according to the specified numerical format.

Use format: TEXT (value,format_text)

Parameter description: value represents the numerical value or referenced cell to be converted; format_text is the specified text format.

Application example: if there is a value of 1280.45 in the B68 cell, we enter the formula: = TEXT (B68, "$1280.45") in the C68 cell and display it as "$1280.45" after confirmation.

Special reminder: the format_text parameter can be determined according to the type in the number label of the Cell format dialog box.

32. TODAY function

Function name: TODAY

Main function: give the system date.

Use format: TODAY ()

Parameter description: this function does not require parameters.

Application example: enter the formula: = TODAY (), and immediately show the date and time of the system after confirmation. If the date and time of the system changes, just press the F9 function key to change it.

Special reminder: the date format displayed can be reformatted through the cell format (see attachment).

33. VALUE function

Function name: VALUE

Main function: convert a text string that represents a numeric value into a numeric string.

Use format: VALUE (text)

Parameter description: text represents the need to convert text string values.

Application example: if the B74 cell is a text string intercepted by functions such as LEFT, we enter the formula: = VALUE (B74) in the C74 cell, and after confirmation, it can be converted into numerical type.

Special warning: if text-based values do not undergo the above conversion, errors are often returned when dealing with these values 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.

Use format: VLOOKUP (lookup_value,table_array,col_index_num,range_lookup)

Parameter description: Lookup_value represents the value to be found; Table_array represents the range of cells in which the data needs to be found; Col_index_num is the column sequence number of the matching value to be returned in the table_array range (when Col_index_num is 2, the value in column 2 of table_array is returned, and the value of column 3 is returned. Range_lookup is a logical value, if TRUE or omitted, the approximate match value is returned, that is, if no exact match value is found, the maximum value less than lookup_value is returned; if FALSE, the exact match value is returned, or the error value # N An is returned if it cannot be found.

Application example: see figure 7, we enter the formula in the D65 cell: = VLOOKUP (B65MagneB2 D63people false). After confirmation, as long as you enter a student's name (such as Ding 48) in the B65 cell, the D65 cell immediately shows the student's language score.

Special reminder: Lookup_value must be in the first column of the Table_array area; if you omit the Range_lookup parameter, the first column of Table_array must be sorted; in the wizard for this function, the use of the Range_lookup parameter is incorrect.

35. WEEKDAY function

Function name: WEEKDAY

Main function: give the corresponding number of weeks for a specified date.

Use format: WEEKDAY (serial_number,return_type)

Parameter description: serial_number represents the specified date or refers to the cell containing the date; return_type represents the day of the week [when Sunday (Sunday) is 1, Saturday (Saturday) is 7, this parameter is 1; when Monday (Monday) is 1, Sunday (Sunday) is 7, the parameter is 2 (this is in line with Chinese habits) When Monday (Monday) is 0 and Sunday (Sunday) is 6, this parameter is 3].

Application example: enter the formula: = WEEKDAY (TODAY (), 2), and give the week of the system date after confirmation.

Special reminder: if it is a specified date, please put it in double quotes in English, such as = WEEKDAY ("2003-12-18", 2).

one. Cell color effect full selection table, format-conditional format, conditional selection "formula", the formula is as follows, and then select the "format" button, "pattern", select the desired color.

After reading the above, do you have any further understanding of office skills? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.