News

### You want to use and you don't even know WORD,excel skills

Word ACE:A, and entered three a "=", carriage return, get a article double line; II, and entered three a "~", carriage return, get a article wave line; three, and entered three a "*" or "-" or "#", carriage return, surprise more; in unit grid within entered =now () displayed date in unit grid within entered =CHOOSE (WEEKDAY (I3,2), "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday ") &Nbsp; display the week book 1, Excel function ABS function function name: ABS function: calculate the absolute value of a number.

Use the format: ABS (number) parameter: number represents cells that require the absolute value of a value or reference.

Example: If you enter a formula in cell B2: =ABS (A2), in cell A2, enter a positive number (for example, 100) or negative (such as -100), B2 are indicative of a positive number (100).

Special Note: If the number argument are not numeric, but some characters (such as a), B2 returns incorrect values in "#VALUE! ”。

2, the function name AND function: AND main function: Returns the logical value: If all parameter values are logical "true (TRUE)", returns the logical "true (TRUE)" otherwise logical "fake (FALSE)".

Use the format: AND (Logical1,logical2, ...) Arg: Logical1,Logical2,Logical3 ... ... : Indicates that the criteria value or expression to be tested, up to 30.

Example of application: enter a formula in cell C5: =AND (A5>=60,B5>=60) to confirm. Returns TRUE if C5, A5 and B5 's value is greater than or equal to 60, if it returns FALSE, the A5 and B5 value in at least one less than 60.

Special Note: If the specified logical condition parameter contains a logical value, the function returns an error value of "#VALUE!" Or "#NAME".

3, the AVERAGE function function name: AVERAGE function: find the arithmetic mean of all the parameters.

Use the format: AVERAGE (Number1,number2, ... ...) Arg: Number1,number2, ... ... : Required an average value or reference cell (region), the parameter is not more than 30.

Example of application: enter a formula in cell B8: =AVERAGE (B7:D7,F7:H7,7,8), after confirming, obtained B7 to D7, F7 and H7 the values and the average of 7, 8.

Special Note: If the reference area contains "0" value cell, count; if a reference contains white space or character cell in the range, are not counted.

4, the COLUMN function function name: COLUMN function: marking of the column referenced by the displayed cell value.

Use the format: COLUMN (reference) parameter: a reference for referenced cells.

Example of application: enter a formula in cell C11: =COLUMN (B11), confirmation is displayed as 2 (column b).

Special Note: If you enter a formula in cell B11: =COLUMN (), also showed 2; and also have a return label that corresponds to the value of the function--ROW (reference).

5, CONCATENATE function function name: CONCATENATE function: multiple character data in a text or cells connected together, are displayed in a cell.

Use the format: CONCATENATE (Text1,Text ... ...) Arg: Text1, Text2, ... ... Need to connect a character literal or a reference cell.

Example of application: enter a formula in cell C14: =CONCATENATE (A14, "@", B14. "com") is confirmed, to cell A14 characters, @, and characters in cell B14. com into a whole, displayed in cell C14.

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

6, COUNTIF function function name: COUNTIF function: statistical regions of a cell number in the cells that match the specified criteria.

Use the format: COUNTIF (Range,Criteria) parameter: Range represents the range of cells that you want to count; Criteria indicates that the specified condition expression.

Example of application: enter a formula in cell C17: =COUNTIF (B1:B13, ">=80"), confirmation, can be counted in the cell range of B1 to B13, values greater than or equal to 80 per cent of the number of cells.

Special reminder: allow reference to appear there are blank cells in a range of cells.

7, the DATE function function name: DATE function: to specify a numeric date.

Use the format: DATE (year,month,day) Description: the year for the specified year value (less than 9999) and month for the specified number of months (can be greater than 12), day for the specified number of days.

Example of application: enter a formula in cell C20: =DATE (2003,13,35), confirmation, showing 2004-2-4.

Special Note: due to the above formula, the month is 13, more than a month, will be rescheduled for January 2004; is 35 in January 2004, the actual number of days more than 4 days, so it is also extended to February 4, 2004.

8, function name: DATEDIF function: calculation returns the difference between two date parameters.

Use the format: =DATEDIF (Date1,date2, "y"), =DATEDIF (Date1,date2, "m"), =DATEDIF (Date1,date2, "d") parameter description: Date1 represents a previous date, date2 represents a later date; y (m, and d) request returns the difference between two dates in years (months, days).

Example of application: enter a formula in cell C23: =DATEDIF (A23,TODAY (), "y"), confirmed to return current system date [TODAY ()) and A23 date value in a cell, and returns the number of years.

Special Note: this is a hidden function in Excel, is not found in the function wizard, can be entered directly using, for calculating age, length of service, are very effective.

9, the DAY the function function name: main functions DAY: find a specific date or reference of the date in the cell.

Use the format: DAY (Serial_number) arg: Serial_number specified or the date of the referenced cell.

Example of application: enter the formula: =DAY ("2003-12-18"), confirm, showed 18.

Special Note: If the date is given, include double quotation marks in English.

10, the DCOUNT function function name: DCOUNT function: returns in a column of a list or database that match conditions you specify and the number of cells that contain numbers.

Use the format: DCOUNT (database,field,criteria) Description: Database need to count cells; Field that represents the function used in the data column (must have labels in the first row), Criteria range of cells that contains the conditions.

Example of application: as shown in Figure 1, enter the formula in cell F4: =DCOUNT (A1:D11, "Chinese", F1:G2), confirmation can be worked out "Chinese" column, scores greater than or equal to 70, and less than the number 80 for the number of cells (the equivalent of number score).

Special Note: If the above formula is revised as follows: =DCOUNT (A1:D11,,F1:G2), you can also achieve the same purpose.

11, the FREQUENCY function function name: FREQUENCY main functions: one returns a vertical array of regional data in the frequency distribution.

Use the format: FREQUENCY (data_array,bins_array) Description: Data_array represents a set of data used to calculate the frequency range or cells; Bins_array as separated array in front of a column of numbers.

Example of application: as shown in Figure 2, select between B32 and B36 range, enter the formula: =FREQUENCY (B2:B31,D2:D36), when the input is complete press the "Ctrl+Shift+Enter" key combination to confirm, obtained from B2 to B31 area, separated by D2 to D36 area number of the numerical frequency (the equivalent of statistical figures in each score).

Reminder: the input is an array formula, when the input is complete, needs by pressing the "Ctrl+Shift+Enter" key combination to confirm, confirm both ends of the equation to appear after a pair of curly braces ({}), this brace cannot be entered directly.

12, IF function function name: IF functions: logic true or false according to the specified conditions results, returns the corresponding content.

Use the format: =IF (Logical,Value_if_true,Value_if_false) Description: Logical represents a logical expression; Value_if_true represents the judgment condition for the logical "true (TRUE)" show content when, if you ignore the return "TRUE"; Value_if_false represents the judgment condition for the logical "fake (FALSE)," a display, If you ignore the return "FALSE".

Example of application: enter a formula in cell C29: =IF (C26>=18, "satisfactory", "do not meet the requirements"), convinced that the future if C26 cell value is greater than or equal to 18, C29 cells display "meets expectations", otherwise display "does not meet the requirements".

Special reminder: this article is similar to "enter a formula in cell C29" is specified in the cell, when readers use, does not need to be bound by it here just to make the attached instance of the corresponding cell is given to the specific needs, please refer to the attached instance-specific documents.

13, the INDEX function function name: INDEX function: Returns value of the element in the list or array, this element is selected by the row and column number indexes value is determined.

Use the format: INDEX (array,row_num,column_num) parameter: Array represents the range of cells or an array constant; Row_num specifies the row number (If row_num is omitted, you must have column_num); Column_num for the specified column ordinal (If column_num is omitted, you must have the row_num).

Example of application: as shown in Figure 3, enter the formula in the cell F8: =INDEX (A1:D11,4,3), confirmation is displayed in the cell range of A1 through D11, 4th row and 3rd column of the cell at the intersection (C4) in the content.

Special remind: here the row parameter (row_num) and the column ordinal argument (column_num) is relative to the reference in terms of a range of cells, not the number of rows or columns in the Excel worksheet.

14, INT function function name: INT main function: numeric round down to the nearest integer.

Use the format: INT (number) parameter: number expressed the need for rounding values, or reference a cell containing a numeric value.

Example of application: enter the formula: =INT (18.89 per cent), confirmation showed that 18.

Special reminder: when the rounded, not round; if you enter a formula for =INT (-18.89 per cent), the return result is-19.

15, the ISERROR function function name: ISERROR function: to test the function return value for errors. If there is any mistake, the function returns TRUE, otherwise returns FALSE.

Use the format: ISERROR (value) parameter: Value value or expression that represents the need to test.

Example of application: enter the formula: =ISERROR (A35/B35), confirmation, if cell B35 is empty or "0", the A35/B35 error, results of previous function returns TRUE, otherwise returns FALSE.

Special Note: this function is typically used with the IF function, if the above formula is revised as follows: =IF (ISERROR (A35/B35), "", A35/B35), if the B35 is empty or "0", then the corresponding cell appears empty, instead shows the result of A35/B35.

16, LEFT function function name: LEFT main functions: from a text string starting at the first character, interception of a specified number of characters.

Use the format: LEFT (text,num_chars) parameter: text represents the stop character string num_chars a given number of intercepts.

Example of application: assumes that the cell A38 save "I like tianjiwang" string, we enter the formula in the cell C38: =LEFT (A38,3), confirmed that shows "I love" characters.

Special reminder: this function name in English means "left", that is captured from the left, many Excel functions are taking the English meaning.

17, LEN function function name: LEN function: counts the number of characters in a text string.

Use the format: LEN (text) parameter: text string that represents the text to be statistical.

Example of application: assumes that the cell A41 saved "I'm 28 years old" strings, we enter a formula in cell C40: =LEN (A40), after confirming that show statistical results "6".

Special reminder: LEN will statistics, regardless of full-width characters, or half-width characters, invalidate every word counted as "1"; corresponding to a function with the--LENB, half-width characters in your statistics as "1", full-width characters to "2".

18, the MATCH function function name: MATCH function: returns in the specified mode and the corresponding position of the elements in an array that matches a specified value.

Format: MATCH (lookup_value,lookup_array,match_type) Description: Lookup_value representatives need to find values in the data table; Lookup_array can contain the values you want to find cells in a row; Match_type finds such value (-1, 0 or 1).

If match_type is-1, find the minimum numeric value greater than or equal to lookup_value, Lookup_array must be sorted in descending order; If match_type is 1, find the largest number less than or equal to lookup_value, Lookup_array must be sorted in ascending order; If match_type is 0, find the first value equal to lookup_value, Lookup_ Array can be arranged in any order If match_type is omitted, it defaults to 1.

Example of application: as shown in Figure 4, enter the formula in cell F2: =MATCH (E2,B1:B11,0), confirmed the search result of "9".

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

19, MAX function function name: MAX function: find the largest value in a set of numbers.

Use the format: MAX (Number1,number2 ... ...) Arg: Number1,number2 ... ... Representative required or maximum value of the referenced cell (region), the parameter is not more than 30.

Example of application: enter the formula: =MAX (E44:J44,7,8,9,10), after confirming to show the E44 to J44 units and regions and maximum numerical 7,8,9,10.

Special Note: If the parameter has text or logical values are ignored.

Function name 20, MID function: MID function: from a text string starting at the specified position, interception of a specified number of characters.

Use the format: MID (text,start_num,num_chars) parameter: text represents a single text string, start_num specifies the starting position of num_chars represents the number to interception.

Example of application: assumes that the cell A47 save "I like tianjiwang" string, we enter a formula in cell C47: =MID (A47,4,3), confirmed that shows "tianjiwang" character.

Special reminder: in a formula, parameter, use commas in English State "," separated.

21, the MIN function function name: MIN function: find the smallest value in a set of numbers.

Use the format: MIN (Number1,number2 ... ...) Arg: Number1,number2 ... ... Representative requires a minimum value or the value of precedents (region), the parameter is not more than 30.

Example of application: enter the formula: =MIN (E44:J44,7,8,9,10), after confirming to show the E44 to J44 units and regional and the minimum value in a numeric 7,8,9,10.

Special Note: If the parameter has text or logical values are ignored.

22, the MOD function function name: MOD function: find the remainder after dividing two numbers.

Use the format: MOD (number,divisor) parameter: number represents the dividend; representative of divisor divisor.

Example of application: enter the formula: =MOD (13,4), confirm results show after "1".

Special Note: If the divisor parameter is zero, it displays an error value of "#DIV/0!" ; MOD function you can use function INT says the formula is modified to: =13-4*INT (13/4).

23, the MONTH function function name: MONTH function: given a specific date or date of the month in the reference cell.

Use the format: MONTH (Serial_number) arg: Serial_number specified or the date of the referenced cell.

Example of application: enter the formula: =MONTH ("2003-12-18"), confirmation, showing 11.

Special Note: If the date is given, enclosed in double quotation marks if the above formula is amended as: =YEAR ("2003-12-18"), then returns the year corresponding to the value of the "2003".

24, NOW function function name: NOW main function: given the current system date and time.

Use the format: NOW () Description: this function does not require arguments.

Example of application: enter the formula: =NOW (), show the current system date and time immediately after confirmation. If the system date and time changes, just click the F9 function key, you can let it change.

Special reminder: displays the date and time format, you can reset the cell formatting.

25, OR function name: OR main function: Returns the logical value only if all parameter values are logical "fake (FALSE)," a returning function results logic "fake (FALSE)", otherwise returns the logical "true (TRUE)".

Use the format: OR (Logical1,logical2, ...) Arg: Logical1,Logical2,Logical3 ... ... : Indicates that the criteria value or expression to be tested, up to 30.

Example of application: C62 cell, enter the formula: =OR (A62>=60,B62>=60) to confirm. Returns TRUE if C62, the A62 and B62 value in at least one greater than or equal to 60, if it returns FALSE, the A62 and B62 values are less than 60.

Special Note: If the specified logical condition parameter contains a logical value, the function returns an error value of "#VALUE!" Or "#NAME".

26, the RANK function function name: RANK function: Returns a numeric value in a column of numbers relative to other values in the row.

Using format: RANK (Number,ref,order) parameter description: Number representative need sort of numerical; ref representative sort numerical by at of unit grid regional; order representative sort way parameter (if for "0" or ignored, is by descending ranking, that numerical more big, ranking results numerical more small; if for non-"0" value, is by ascending ranking, that numerical more big, ranking results numerical more big;).

Example of application: in cell C2, enter the formula: =RANK (B2,$B$2:$B$31,0), confirmed that Ding 1 scores of students in the class ranking in the results.

Special reminded: in above formula in the, we let Number parameter take has relative reference form, and let ref parameter take has absolute reference form (increased has a "$" symbol), such set Hou, selected C2 unit grid, will mouse moved to the unit grid right Xia angle, into fine cross line-like Shi (usually called "fill handle"), press left key down drag, can will above formula fast copy to c column following of unit grid in the, completed other students language results of ranking statistics.

27, the RIGHT function name: RIGHT function: start from the last character in a text string, interception of a specified number of characters.

Use the format: RIGHT (text,num_chars) parameter: text represents the stop character string num_chars a given number of intercepts.

Example of application: assumes that A65 cell holds "I like tianjiwang" string we C65 cell, enter the formula: =RIGHT (A65,3), confirmed that shows "tianjiwang" character.

Special reminder: Num_chars must be greater than or equal to 0, if omitted, the default is 1 If num_chars is greater than the length of the text, the function returns the entire text.

28, the SUBTOTAL function function name: SUBTOTAL function: Returns a subtotal in a list or database.

Use the format: SUBTOTAL (function_num, Ref1, ref2, ...) Arg: Function_num is 1 to 11 (includes hidden values) or 101 to 111 (ignores hidden values) between the numbers, is used to specify what function calculates the subtotal in the list (Figure 6), Ref1, ref2, ... ... Representative to conduct regional subtotals or references, not more than 29.

Example of application: as shown in Figure 7, B64 and C64 respectively, enter the formula in a cell: =SUBTOTAL (3,C2:C63) and =SUBTOTAL103,C2:C63) and 61 row hidden, after confirmation, the former showed 62 (including hidden lines), which showed 61, not including the hidden rows.

Special reminder: If AutoFilter function_num parameters selected, what type, any is not included in the SUBTOTAL function ignores the rows in the filter results; SUBTOTAL function for data columns or vertical section, does not apply to rows or horizontal area.

29, function name: SUM function: all parameter values are calculated.

Use the format: SUM (Number1,Number2 ... ... ) Arg: Number1, Number2, ... ... Represents a value you want, can be a specific value, reference cell (regional), logical value, and so on.

Example of application: as shown in Figure 7, D64, enter the formula in a cell: = sum (D2:D63), confirmation can be worked out language scores.

Special reminder: If an argument is an array or reference, only numbers are counted. In the array or reference are blank cells, logical values, text, or error values are ignored; if the above formula is revised as follows: =SUM (LARGE (D2:D63,{1,2,3,4,5})), and you can find the top 5 accomplishments.

30, SUMIF function function name: SUMIF function: calculating cells that match the specified criteria values and in the region.

Use the format: SUMIF (Range,Criteria,Sum_Range) parameter: Range range of cells that represent the conditions; Criteria for the specified conditional expressions; Sum_Range representatives need to calculate the range of cells that contains the value.

Example of application: as shown in Figure 7, D64, enter the formula in a cell: =SUMIF (C2:C63, "man", D2:D63), confirmation is obtained "male" raw scores and.

Special Note: If the above formula is revised as follows: =SUMIF (C2:C63, "female", D2:D63), you can find the "female" raw scores and which "male" and "female" because it is a text, you need to put double quotation marks in the English State ("male", "female").

31, the TEXT function function name: TEXT functions: according to specified number format will be the corresponding number is converted to text.

Use the format: TEXT (value,Format_text), arg: value or reference value represents the need to convert the cell Format_text number formatting for the specified text.

Example of application: you save a value cell B68 1280.45, C68 we enter a formula in a cell: =TEXT (B68, "$0.00"), confirmation is displayed as "$1280.45".

Reminder: the Format_text argument based on "format cells" dialog box "digital" label type is determined.

32, the TODAY function function name: TODAY the main functions: System date is given.

Use the format: TODAY () Description: this function does not require arguments.

Example of application: enter the formula: =TODAY (), shows that the system date and time immediately after confirmation. If the system date and time changes, just click the F9 function key, you can let it change.

Special reminder: displayed date format, can be reset by cell format (see annex).

33, VALUE function function name: VALUE function: Converts a text string representing the value to numeric.

Use the format: VALUE (text) parameter: text values need to convert the text strings are represented.

Example of application: if cell B74 LEFT function to intercept text type, such as string, we enter the formula in the cell C74: =VALUE (B74), confirmation, you can convert it to a numeric type.

Special Note: If the text value does not pass through the same cast, in function when working with these values, returns an error.

34, the VLOOKUP function function name: VLOOKUP function: locates the specified values in the first column of the table, and then returns the value at the specified column in the current row of the data table.

Use the format: VLOOKUP (lookup_value,table_array,col_index_num,range_lookup) Description: Lookup_value representatives need to find values; Table_array represents the cell range you want to find data; in table_array Col_index_num returns matching values in the range column ordinal (when Col _Index_num to 2 o'clock, the returned number in the table_array the 2nd column, 3 o'clock, returns the value of the 3rd column ... ... ) And Range_lookup is a logical value, if TRUE or omitted, then the approximate match is returned, that is, if no exact match is found, then the maximum value is less than the lookup_value is returned if FALSE, return exact match, if not found, it returns an error value # n/a.

Example of application: see Figure 7, we enter a formula in cell D65: =VLOOKUP (B65,B2:D63,3,FALSE), after confirming, as long as the cell B65, enter a student's name (for example, d 48), D65 cell instantly showed the students ' language proficiency.

Special reminder: see Lookup_value in the first column of Table_array must if the Range_lookup argument is omitted, the first column of Table_array must be sorted in the function wizard and usage about the Range_lookup argument is false.

35, the WEEKDAY function function name: WEEKDAY function: corresponds to the number of weeks to the specified date.

Use the format: WEEKDAY (Serial_number,Return_type) Description: Serial_number specified dates or reference cells containing dates; Return_type representative representation of the week [when Sunday (Sunday) 1, Saturday (Saturday) to 7 o'clock, the parameter is 1; when the Monday (Monday) 1, Sunday (Sunday) to 7 o'clock, This parameter is 2 (this is consistent with the habits of the Chinese people); Monday (Monday) for 0, Sunday (Sunday) to 6 o'clock, this parameter is 3].

Example of application: enter the formula: =WEEKDAY (TODAY (), 2), after confirming that the week of the given date.

Special Note: If the date specified, double quotation marks in English, please state such as =WEEKDAY ("2003-12-18", 2).

One. Cell color effect select all tables, format-conditional formatting, select "formula", as follows, and then select the "format" button, the "pattern", select the color that you want.

1. the interlace color effects (color odd rows):

=MOD (ROW (), 2) =12. interlaced color effects (even colors):

=MOD (ROW (), 2) =03. If you want the shading formatting applied once for every 3 rows, you can use the formula:

=MOD (ROW (), 3) =14. If you want to set different odd column shading, as long as the formula in the ROW (s) to COLUMN (), like so:

=MOD (COLUMN (), 2), 5. If you want to set it a chess shading (white + custom color):

=MOD (ROW (), +COLUMN (), 2) Note: the conditional formatting formula is used to determine the row number and the column number of the remainder divided by 2 is 0. If it is 0, indicating parity of the number of columns and rows of the same, then fill the cell for the specified color, otherwise it does not populate. In the conditional formatting and formulas when the results return a number, a number other than 0 TRUE,0 and error value is FALSE. Therefore, the above formula can also be written as:

=MOD (ROW (), +COLUMN (), 2) <>06. If you want to set it a chess shading (custom colors + custom color):

Add style 2:

=MOD (ROW (), +COLUMN (), 2) = 02. Excel2007 color to filter by cell color, font color, or icon to sort.

If excel2003, you need to add a secondary column, using defined names to get the corresponding color, and then on the secondary sort column:

Color the cells in column a, B1 is selected, insert-> name-> definition, type a, enter the formula =get.cell (24,$A1), this method could get the serial number of the font color. B1 input =a to copy the formulas down to row.

The table can be sorted by column b. If you want to get the background color serial number, 24 to 63.

BACK