supra skytop purple patent-Office , clerical , finance will be !

Word trick:
First, enter the three , enter the three (WEEKDAY (I3, 2), show week
Excel common functions Daquan

1, ABS function
function name: ABS
main functions: find the absolute value of the corresponding figures.
using the format: ABS (number)
Parameters: number of representatives to request the absolute value or reference cell.
Example: If cell B2 enter the formula: = ABS (A2), in cell A2 enter either positive (such as 100) or negative (eg -100), B2 are shown in the positive (eg, 100).
Special note: If number is not numeric, but some characters (such as A, etc.), then returns the error value B2
2, AND function
function name: AND
main function: Returns the logical value: if all parameters are the logical logical
using the format: AND (logical1, logical2, ...)
Parameter Description: Logical1, Logical2, Logical3 ... ...: that the conditions to be tested value or expression, up to the 30.
Application examples: In cell C5 enter the formula: = AND (A5> = 60, B5> = 60), confirmed. If the C5 return TRUE, shows the value in A5 and B5 are greater than or equal to 60, if you return FALSE, A5 and B5 illustrate at least one value in less than 60.
Special note: If you specify a logical condition parameter contains non-logical value, the function returns the error value
3, AVERAGE function
function name: AVERAGE
main functions: find the arithmetic mean of all parameters.
using the format: AVERAGE (number1, number2, ... ...)
Parameter Description: number1, number2, ... ...: to request the average value or reference cell (area), not greater than 30.
Application examples: In cell B8, enter the formula: = AVERAGE (B7: D7, F7: H7, 7,8), to confirm, you can find the area B7 to D7, F7 to H7 area values ​​and average of 7,8.
to remind: If the reference area contains
4, COLUMN function
function name: COLUMN
main functions: display the column label reference value for the cell.
using the format: COLUMN (reference)
Parameters: reference is a reference cell.
Application examples: In cell C11 enter the formula: = COLUMN (B11), confirmation is displayed as 2 (ie, column B).
to remind: If cell B11 enter the formula: = COLUMN (), also shows that 2; Correspondingly, there is a return value of row label function - ROW (reference).
5, CONCATENATE function
function name: CONCATENATE
main functions: the number of characters in the text or data in a cell together, displayed in a cell.
using the format: CONCATENATE (Text1, Text ... ...)
Parameter Description: Text1, Text2 ... ... need to link text or character reference cell.
Application examples: In cell C14 enter the formula: = CONCATENATE (A14 ,character and. com connection as a whole, appears in cell C14.
Special note: If the parameter is not referenced cell, and the text format, please give the English state parameters with double quotes, if the above formula to: = A14 &. com
6, COUNTIF function
function name: COUNTIF
main features: a range of cells that meet specified statistical criteria for the number of cells.
using the format: COUNTIF (Range, Criteria)
Parameter Description: Range on behalf of a range of cells to statistics; Criteria that specify the conditional expression.
Application examples: In cell C17 enter the formula: = COUNTIF (B1: B13, the number of cells.
remind: to allow a range of cells referenced in the blank cells appear.
7, DATE function
function name: DATE
main functions: given the value of the specified date.
using the format: DATE (year, month, day)
Parameters: year value for the specified year (less than 9999); month for a specified number of months (can be greater than 12); day for a specified number of days .
Application examples: In the cell C20 enter the formula: = DATE (2003,13,35), confirmation, show 2004-2-4.
Special note: Because of the above formula, for 13 months, more than a month, postponed to January 2004; 35 days, compared with actual number of days in January 2004, the addition of four days, it is also postponed to 2004 February 4, 2010.
8 women supra shoes, function name: DATEDIF
main functions: calculate the parameters of the two dates to return the difference.
using the format: = DATEDIF (date1, date2, a date supra skytop purple patent, date2 on behalf of a later date; y (m, d) requires the annual return difference between two dates (month, days).
Application examples: In cell C23 enter the formula: = DATEDIF (A23, TODAY (), , and returns the number of years difference.
Special note: This is a hidden function in Excel, in the Function Wizard is not found, can be used directly for computing age, seniority and other very effective.
9, DAY function
function name: DAY
main functions: find the specified date or the date of the referenced cell the number of days.
using the format: DAY (serial_number)
Parameter Description: serial_number behalf of the specified date or reference cell.
Example: enter the formula: = DAY (
Special note: If the date is given, include double quotation marks in English.
10, DCOUNT function
function name: DCOUNT
main functions: to return a database or list of columns that satisfy the specified conditions and the number of cells that contain numbers.
using the format: DCOUNT (database, field, criteria)
Parameter Description: Database statistics said the need for a range of cells; Field said that the data used in the function list (must be marked in the first line item); Criteria include conditions of cells.
Application examples: Figure 1, in cell F4, enter the formula: = DCOUNT (A1: D11, than or equal to 70, while the value is less than 80 the number of cells (equivalent to the number of fractional section).

Special note: If you modify the above formula: = DCOUNT (A1: D11,, F1: G2), can achieve the same purpose.
11, FREQUENCY function
function name: FREQUENCY
main functions: to return to a vertical array data in an area frequency distribution.
using the format: FREQUENCY (data_array, bins_array)
Parameter Description: Data_array that used to calculate the frequency of a set of data or range of cells; Bins_array represented as an array in front separated by a value.
Application Example: Figure 2, while selected range of cells B32 to B36, enter the formula: = FREQUENCY (B2: B31, D2: D36), enter the complete press key for confirmation, you can find the B2 to B31 area, according to area D2 to D36 paragraphs separated the number of frequency values ​​(equivalent to the number of statistical segments of the score).

Special note: The input is an array formula, you're finished, need to press ({}), the braces can not be directly input.
12, IF function
function name: IF
main functions: logic based on specified criteria to determine the true and false results, return the corresponding content.
using the format: = IF (Logical, Value_if_true, Value_if_false)
Parameter Description: Logical behalf of logic expressions; Value_if_true conditions that determine when a logic TRUE
Application examples: In C29 enter the formula in a cell: = IF (C26> = 18, the C29 cell shows
Special note: this article, similar to the the appropriate cell, please refer to the specific instance of the file attached.
13, INDEX function
function name: INDEX
main functions: to return a list or array element values, this element by the row number and column number of the index values ​​determined.
using the format: INDEX (array, row_num, column_num)
Parameter Description: Array on behalf of a range of cells or an array constant; Row_num that the specified line number (if omitted row_num, you must have column_num); Column_num that specified column number (if omitted column_num, you must have row_num).
Application Example: Figure 3 shows, the F8 cell enter the formula: = INDEX (A1: D11, 4,3), confirmation is displayed in a range of cells A1 to D11, and line 4 3 cell at the intersection (ie, C4) in the content.

Special note: here's the line number parameter (row_num) and column number parameter (column_num) is the range of cells relative to the terms of reference, not an Excel worksheet rows or columns in the order number.
14, INT function
function name: INT
main functions: the value rounded down to the nearest whole number.
using the format: INT (number)
Parameters: number expressed the need for rounding numeric values ​​or contains a reference to cell.
Example: enter the formula: = INT (18.89), confirmed show 18.
remind: when in the rounded, without rounding; If you enter the formula = INT (-18.89), then returns the result of -19.

15, ISERROR function
function name: ISERROR
main functions: used to test the functional return value for errors. If there is an error the function returns TRUE, otherwise returns FALSE.
using the format: ISERROR (value)
Parameter Description: Value that need to test the value or expression.
Example: enter the formula: = ISERROR (A35/B35), recognized, if the B35 cell is empty or
Special note: This function is typically used with the IF function matching, if the above formula changes to: = IF (ISERROR (A35/B35 ),shows the corresponding cell is empty, otherwise display A35/B35
results.
16, LEFT function
function name: LEFT
main functions: a text string from the first character, the interception of a specified number of characters.
using the format: LEFT (text, num_chars)
Parameter Description: text to be cut on behalf of the character string; num_chars interception on behalf of a given number.
Example: assume that A38 cells to save the characters.
remind: this function name in English means
17, LEN function
function name: LEN
main functions: the number of characters in text string statistics.
using the format: LEN (text)
Parameters: text, said text string to statistics.
Example: assume that the cell holds the A41
reminded: LEN to statistics radii 420 tops, whether in full-width characters, or half-width characters, each character are counted as
18, MATCH function
function name: MATCH
main functions: to return in the specified mode value matches the specified array of elements in the corresponding position.
using the format: MATCH (lookup_value, lookup_array, match_type)
Parameter Description: Lookup_value representatives need to look at the data sheet value;
Lookup_array that may contain the values ​​you want to find a continuous range of cells;
Match_type find the way that the value of (-1, 0 or 1).
If match_type is -1, greater than or equal to lookup_value find minimum value, Lookup_array must be sorted in descending order;
If match_type is 1, less than or equal to lookup_value find the maximum value, Lookup_array must be 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 omitted match_type, the default is 1.
Application examples: as shown in Figure 4, in cell F2 enter the formula: = MATCH (E2, B1: B11, 0), confirmed the results returned to find

remind: Lookup_array only for a column or row.
19, MAX function
function name: MAX
main functions: find a set number of maximum.
using the format: MAX (number1, number2 ... ...)
Parameter Description: number1, number2 ... ... representatives to request the maximum value or the reference cell (area), not greater than 30.
Example: enter the formula: = MAX (E44: J44, 7,8,9,10), confirmed to display the E44 to J44 cells and in the region and the maximum value 7,8,9,10 .
Special note: If the parameters are text or logical values, it is ignored.
20, MID function
function name: MID
main functions: the text string from a specified location, the interception of a specified number of characters.
using the format: MID (text, start_num, num_chars)
Parameter Description: text represents a text string; start_num that the specified starting position; num_chars indicated that the number of intercepts.
Example: assume that A47 cells to save the
remind: the formula of the parameters, the use of the English state comma
21, MIN function
function name: MIN
main functions: find a set number of minimum.
using the format: MIN (number1, number2 ... ...)
Parameter Description: number1, number2 ... ... representatives to request the minimum reference value or a cell (area), not greater than 30.
Example: enter the formula: = MIN (E44: J44, 7,8,9,10), confirmed to display the E44 to J44 cells and in the region and the minimum value 7,8,9,10 .
Special note: If the parameters are text or logical values, it is ignored.
22, MOD function
function name: MOD
main functions: find two numbers divided by the remainder.
using the format: MOD (number, divisor)
Parameters: number representative of the dividend; divisor on behalf of the divisor.
Example: enter the formula: = MOD (13,4), confirmed the results show
to remind: If divisor parameter is zero, an error value .
23, MONTH function
function name: MONTH
main functions: find the specified date or the date of the referenced cell the month.
using the format: MONTH (serial_number)
Parameter Description: serial_number behalf of the specified date or reference cell.
Example: enter the formula: = MONTH (
Special note: If the date is given, include double quotation marks in English; if you modify the above formula: = YEAR (
24, NOW function
function name: NOW
main functions: given the current system date and time.
using the format: NOW ()
Parameter Description: This function requires no arguments.
Example: enter the formula: = NOW (), immediately after the show to confirm the current system date and time. If the system date and time has changed, just click the F9 function key, you can let it change.
Special note: the date and time display format, cell formatting can be reset.
25, OR function
function name: OR
main function: Returns the logical value, only if all parameter values ​​are logical otherwise returns logical
using the format: OR (logical1, logical2, ...)< br> Parameters: Logical1, Logical2, Logical3 ... ...: that the conditions to be tested value or expression, up to the 30.
Application examples: the cell enter the formula in C62: = OR (A62> = 60, B62> = 60), confirmed. If C62 is returned TRUE, shows the value of A62 and B62 in at least one greater than or equal to 60, if the returns FALSE, A62 and B62 in the description of the values ​​are less than 60.
Special note: If you specify a logical condition parameter contains non-logical value, the function returns the error value
26, RANK function
function name: RANK
main functions: to return a value in a value relative to other values ​​in the ranking.
using the format: RANK (Number, ref, order)
Parameter Description: Number on behalf of the values ​​to be sorted; ref on behalf of a range of cells in which the sort value; order on behalf of sort parameters (if ignored, according to descending rank, that is, the greater the value, ranking the results with smaller values; if other than
Application example: If in cell C2 enter the formula: = RANK (B2, $ B $ 2: $ B $ 31,0), confirmed the students can come to the small one in the class scores in language performance ranking results.
remind: In the above formula, we make reference to the Number parameter to a relative form, and let the ref parameter to the absolute reference form (add a mouse over the lower right corner of the cell, into thin cross-linear (usually called the complete ranking scores of other students of language statistics.
27, RIGHT function
function name: RIGHT
main functions: a text string from the last character, the interception of a specified number of characters.
using the format: RIGHT (text, num_chars)
Parameter Description: text to be cut on behalf of the character string; num_chars interception on behalf of a given number.
Example: assume that A65 cells to save the characters.
remind: Num_chars parameter must be greater than or equal to 0, if omitted, its default is 1; If num_chars parameter is greater than the length of the text, the function returns the entire text.
28, SUBTOTAL function
function name: SUBTOTAL
main functions: to return a list or database in the subtotals.
using the format: SUBTOTAL (function_num, ref1, ref2, ...)< br> Parameters: Function_num 1 to 11 (includes hidden values) or 101 to 111 (ignores hidden values) between the numbers used to specify what function in calculating subtotals within a list (Figure 6); ref1, ref2, ... ... on behalf of the region you want to subtotal or references, no more than 29.
Application Example: Figure 7 shows, in the B64 and C64 cells, respectively, enter the formula: = SUBTOTAL (3, C2: C63) and = SUBTOTAL103, C2: C63), and will hide the line 61, after confirmation The former is displayed as 62 (including hidden lines), which appears as 61, not including the hidden rows.


Special note: If you take automated screening, no matter what type function_num parameter selection, SUBTOTAL function ignores any screening results are not included in the line; SUBTOTAL function or vertical columns of data for the region, does not apply to data line or level area.
29, function name: SUM
main functions: calculate all the parameters and values.
using the format: SUM (Number1, Number2 ... ...)
Parameter Description: Number1, Number2 ... ... representatives need to calculate the value that can be of specific value, a reference cell (area), logical values.
Application Example: Figure 7 shows, in cell D64 enter the formula: = SUM (D2: D63), confirmation can be obtained language scores.
to remind: If parameter is an array or reference, only one of the figures will be calculated. Array or reference empty cells, logical values, text or error values ​​are ignored; if you modify the above formula: = SUM (LARGE (D2: D63, {1,2,3,4,5})), you can find the top 5 results and.
30, SUMIF function
function name: SUMIF
main functions: calculate the range of cells that meet specified criteria and the value.
using the format: SUMIF (Range, Criteria, Sum_Range)
Parameter Description: Range on behalf of the conditions to determine the range of cells; Criteria for the specified conditional expression; Sum_Range need to calculate the value representative of where the range of cells.
Application Example: Figure 7 shows, in cell D64 enter the formula: = SUMIF (C2: C63, .
Special note: If you modify the above formula is: = SUMIF (C2: C63, Because it is text-based, needs to be placed under the state's English double quotes (
31, TEXT function
function name: TEXT
main functions: According to the format specified by the corresponding digital value converted to text.
using the format: TEXT (value, format_text)
Parameters: value representative of the value or need to convert the cell references; format_text text form for the specified number format.
Example: If the B68 cell preservation value 1280.45, the cells in C68 enter the formula: = TEXT (B68,
remind: format_text parameters under the
32, TODAY function
function name: TODAY
main functions: given the system date.
using the format: TODAY ()
Parameter Description: This function requires no arguments.
Example: enter the formula: = TODAY (), immediately after the show to confirm the system date and time. If the system date and time has changed, just click the F9 function key, you can let it change.
Special note: The date display format, you can re-set the cell formatting (see Annex).
33, VALUE function
function name: VALUE
main functions: the value of a representative text string into a numeric type.
using the format: VALUE (text)
Parameter Description: text-based representatives need to convert the text string value.
Example: If the B74 cell function is the interception by the LEFT and other text-based string in cell C74 enter the formula: = VALUE (B74), to confirm, you can convert it to numeric.
Special note: If the text type value without the conversion, in these values ​​when dealing with functions often return an error.
34, VLOOKUP function
function name: VLOOKUP
main functions: the first column in the data table to find the specified value, and thus returns the current row in the table at the specified column value.
using the format: VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)
Parameter Description: Lookup_value representatives need to find value; Table_array representatives need to find data in one range of cells; Col_index_num in the area to be returned table_array match the value of the column number (when Col_index_num = 2, column 2 table_array returned values ​​of 3, the return value of the first three ... ...); Range_lookup is a logical value, if it is TRUE or omitted, returns approximate match, that is, if not find an exact match, it returns the maximum value is less than lookup_value; if FALSE, then return exact match, if found, return an error value # N / A.
Example: see Figure 7, we enter the formula in cell D65: = VLOOKUP (B65, B2: D63, 3, FALSE), confirmed, as long as the cell B65 enter a student's name (such as small 48), D65 cells showed immediate results of the student's language.
remind: Lookup_value see Table_array area must be the first column; if ignored Range_lookup parameters, Table_array the first column must be sorted; in this function wizard, use the Range_lookup argument is wrong.
35, WEEKDAY function
function name: WEEKDAY
main functions: the specified date given in the corresponding number of weeks.
using the format: WEEKDAY (serial_number, return_type)
Parameter Description: serial_number behalf of the specified date or dates of that contains the cell references; return_type representation on behalf of week [when the Sunday (Sunday) to 1, Saturday (Saturday ) 7, this parameter is 1; when Monday (Monday) 1, Sunday (Sunday) to 7, this parameter is 2 (this is in line with the Chinese people's habits); When Monday (Monday) 0 , Sunday (Sunday) to 6, this parameter is 3].
Example: enter the formula: = WEEKDAY (TODAY (), 2), confirm the system date is given after the number of weeks.
Special note: If the specified date, please state on the English double quotes, such as = WEEKDAY (
one. Select color effect cell form, format - conditional formatting, conditions, select the


1. interlacing color effect (odd row colors):
= MOD (ROW (), 2) = 1
2. interlacing color effect (even row color):
= MOD (ROW (), 2) = 0
3. If you want to set the format is applied once every 3 line shading, use the formula:
= MOD (ROW (), 3) = 1
4. If you want to set out a different shading parity, as long as the formula of the ROW () to COLUMN () can, such as:
= MOD (COLUMN (), 2)
5. If you want to set Chess checkerboard shading (white custom color):
= MOD (ROW () + COLUMN (), 2)
Description: The conditional formatting formula used to determine the line number and column number and The remainder is divided by 2 to 0. If 0, indicating the number of rows and columns of the same parity, then fill the cell to the specified color, or do not fill. In the Conditional Formatting, the formula returns a numeric result, the non-zero number is TRUE, 0, and the error value is FALSE. Therefore, the above formula can also be written as:
= MOD (ROW () + COLUMN (), 2) <> 0
6. If you want to set up chess checkerboard shading (custom colors + custom color):
adding style 2:
= MOD (ROW () + COLUMN (), 2) = 0
two. Color filter
excel2007 can by cell color, font color, or icon to sort.
If the excel2003, need to add auxiliary column, with the defined name to get the corresponding color number, and then sort of supporting columns:
color cell in column A, select B1, Insert -> Name - > Define, enter a, enter the formula = get.cell (24, $ a1),
this method obtained the serial number of the font color. B1 enter = a copy the formula down to the appropriate line.
The full table can be sorted by column B. If the serial number to get the background color will change 63 to 24.
== into a name to use
2.Get.Cell (Num, Cell) is the function of the format, Get.Cell can get a cell in a variety of information, such as frame format, Num is specified to obtain the kind of information, this function is to allow 24 units of color information removed, taken out after a 1-56 figure of a certain color number
3. B1 column if you enter your name and the definition of fill, then A1 B1 in the figure is the color code, B2 is A2 in the figure will be the color code, and so
4. Now you press the B column sort, in fact, according to color sort column A the.
attached Get.Cell the meaning of each parameter:
1 refer to the absolute address cell reference cells
2 No.
3 column reference column number cell
4 similar TYPE function
5 with reference to the text contents of the address
6 shows the light formula
7 address address reference format, the text display text display
8 format
9 with reference to the address returned extra storage box left style, figures show
10 return save extra box on the right style, figures show
11 return save extra box on the style side, digital display
12 return extra storage box under the square style, digital Returns the internal display
13 drawings, digital display
14 if the cell is set to locked return True
15 is hidden if the formula returns True
16 returns the cell width
17 points for the unit returned to the cell height
18 point font name
19 to return the font size for the unit cell
20 if all or the first character bold return True
21 If all or a cell for the first character italic return True
22 If all or a cell first character is a single bottom-line returns True
23 if the cell or the first character fonts for all the middle of a horizontal line return True
24 returns the first character color cell number, 1-56. If set to Auto, return 0
25 MS Excel does not support the outline format
26 MS Excel does not support the shadow format
27 figures set manually inserted page breaks out the outline
28 level
29 column outline level
30 if the range is a summary outline of the column was True
31 if the range is the outline of the summary column was True
32 shows the workbook and worksheet name of the
33 If the cell format for multi-line text was returned True
34 extra storage box on the left color digital display. If set to Auto, return 0
35 return save extra box on the right color, digital display. If set to Auto, return 0
36 return extra storage box above the color digital display. If set to Auto, return 0
37 return save extra box below the color digital display. If set to Auto, return 0
38 returns the cell foreground color shadows, figures show. If set to Auto, return 0
39 returns the cell back color shadows, figures show. If set to Auto, the text returned 0
40 shows the cell reference style
41 return address of the original formula
42 in points in the window using the left returned to the level of the cell on the left distance
43 in points, return to the top of the window using the vertical distance above the cell
44 in points, returns the window on the left to use the cell to the right horizontal distance
45 points unit returns to the top of the window using the vertical distance between the bottom of the cell if the cell has
46 comments returned insert True
47 if the cell has to insert a sound prompt return True
48 into the formula if the cell has return True
49 if the cell is an array formula returns the range of returns True
50 cell vertical alignment, digital display
51 cell vertical return, figures show
52 pass back to the cell prefix character
53 text display the returned content
54 cell displays the cell returns the name of the PivotTable cell
55 returns the position in the pivot table pivot
56 < br> 57 or if all cells for the first character superscript return True
58 returns the cell text show the first characters of all or font style
59 returns the cell bottom line style, digital display
60 If all or a cell for the first character subscript return True
61 pivot
62 shows the workbook and worksheet name
63 returns the cell's fill color
64 pass back to the drawing foreground color
65 pivot
66 shows the name of the workbook