prevalent bureau functions and tricks

Word trick:
one, enter the three , enter the three Enter the cell
= CHOOSE (WEEKDAY (I3, 2), Excel prevalent functions Daquan

1, ABS function
function name: ABS
main functions: the absolute value of the corresponding figures obtained.
using the format: ABS (number)
Parameter Description: number to be requested on behalf of the absolute value or reference cell.
Example: If cell B2 enter the formula: = ABS (A2), in either cell A2 enter a assured number (eg 100) or negative (eg -100), B2 in both show positive (eg 100).
to remind: If the number parameter is not numeric, yet some characters (such as A, etc.), then the error value is returned B2
2, AND function
function name: AND
main functions: to return the logical value: if all parameters are the logical logical
using the format: AND (logical1, logical2, ...)
Parameter Description: Logical1, Logical2, Logical3 ... ...: the conditions to be tested, said the value or wording, up to the 30.
Example: enter the formula in cell C5: = AND (A5> = 60, B5> = 60), confirmed. If the C5 return TRUE, shows the value in A5 and B5 were greater than or equal to 60, if return FALSE, that the values ​​in the A5 and B5 at least one of less than 60.
to remind: If you clarify a rational condition parameter contains non-logical values, then the function returns the mistake 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 reference value or the average cell (area), the parameter does not exceed 30.
Application examples: in B8 cells enter the formula: = AVERAGE (B7: D7, F7: H7, 7,8), is confirmed, 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 of the cell.
using the format: COLUMN (reference)
Parameter Description: reference to reference cell.
Application examples: In the cell C11 enter the formula: = COLUMN (B11), confirmation is displayed as 2 (the B column).
to remind: If cell B11 enter the formula: = COLUMN (), also shows that 2; with the corresponding tag value and a return line of the function - ROW (reference).
5,GHD Blue Straighteners, CONCATENATE function
function name: CONCATENATE
main functions: the number of characters in text or data in a cell attach, displayed in a cell.
using the format: CONCATENATE (Text1, Text ... ...)
Parameter Description: Text1, Text2 ... ... the need to connect the roles of txt or reference cell.
Application examples: In the cell C14 enter the formula: = CONCATENATE (A14 ,characters and. com linkage as a whole, appears in cell C14.
to remind: If the argument is not a reference cell, and the text format, please give the English state parameters with double quotes, if you change the formula: = A14 &. com
6, COUNTIF function
function name: COUNTIF
main features: a range of cells that meet specified statistical criteria 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 the cell C17 enter the formula: = COUNTIF (B1: B13, the number of cells.
remind: to allow a range of cells referenced in the empty cells seem.
7, DATE function
function name: DATE
main functions: the values ​​given in the specified date.
using the format: DATE (year,GHD Hair, month, day)
Parameter Description: 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.
reminded: As the above formula, the month is 13, more than a month, procrastinated to January 2004; 35 days, compared with January 2004 the tangible number of days the appending of 4 days, it is also extended to 2004 on February 4.
8, the function name: DATEDIF
cardinal functions: reckon the feud between returns 2 date parameters.
using the format: = DATEDIF (date1, date2, a date, date2 on behalf of a date later; y (m, d) asked to return to the anniversary difference between the two dates (month, day) number.
Application examples: In the cell C23 enter the formula: = DATEDIF (A23, TODAY (), , and returns the number of years difference.
reminded: This namely a secluded function in Excel, is found in the Function Wizard, you can use direct input for the measurement of age, length of service and so quite forcible.
9, DAY function
function name: DAY
main functions: Find the reference to a specified date or the date the cell number of days.
using the format: DAY (serial_number)
Parameter Description: serial_number on behalf of the specified date, or reference cell.
Example: enter the formula: = DAY (
to remind: If the given date, include double quotation marks in English.
10, DCOUNT function
function name: DCOUNT
main functions: to return a database or menu of columns to encounter the specified conditions and the number of cells that contain mathematics.
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 marks on the first line item); Criteria include Conditions range of cells.
Application examples: Figure 1 shows, in cell F4, enter the formula: = DCOUNT (A1: D11, or equal to 70, while the value is less than 80 the number of cells (equivalent to the number of fractional segment).

to remind: If you modify the above formula: = DCOUNT (A1: D11,, F1: G2), can also achieve the same purpose.
11, FREQUENCY function
function name: FREQUENCY
main functions: to return to a vertical series of file in one area frequency delivery.
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 expressed as the front separated by a great many values.
Application examples: as shown in Figure 2, meantime elected scope of cells B32 to B36, enter the formula: = FREQUENCY (B2: B31, D2: D36), enter the complete reception key to confirm, you tin find the B2 to the B31 area, along to D2 to D36 district separated values ​​of the frequency of the digit of segments (equivalent to the digit of statistical segments of the score).
reminded: The input is an array formula, you're achieved, you need to by urgent The clamp can not be directly input.
12, IF function
function name: IF
main functions: According to the logic of the conditions specified in the true and artificial results, return the corresponding content.
using the format: = IF (Logical, Value_if_true, Value_if_false)
Parameter Description: Logical representative of logic expressions; Value_if_true that determine the conditions when the logical TRUE
Application examples: In the cell C29 enter the formula: = IF (C26> = 18, the C29 cell displays
remind: this article, similar to the the corresponding cell, please refer to the specific example of the document attached.
13, INDEX function
function name: INDEX
main functions: to return a list or array element value, 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, there must be column_num); Column_num that the 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), confirmed the show range of cells A1 to D11, the first 4 rows and the crossing of three cells (ie, C4) in the content.
remind: line number argument here (row_num) and column number parameter (column_num) is comparative to the terms of reference of the range of cells, not Excel worksheet row or column number.
14, INT function
function name: INT
main functions: the value rounded down to the nearest integer.
using the format: INT (number)
Parameter Description: number expressed the need for rounding numeric values ​​or include a reference cell.
Example: enter the formula: = INT (18.89), confirmation shows 18.
remind: when in the rounded, without rounding; If you enter the formula = INT (-18.89), the result is returned to -19.

15, ISERROR function
function name: ISERROR
main functions: to test the functional return value for errors. If wrong, 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), confirmation, if the B35 cell is vacant or
reminded: This function is usually related with supporting the use of IF functions, if you modify the formula: = IF (ISERROR (A35/B35 ),the cell appears 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 representative of the character string to be mow; num_chars interception of a given number of representatives.
Application example: assume that the cell holds the A38 characters.
reminded: This function name in English method
17, LEN function
function name: LEN
main functions: the number of characters in text string Statistics.
using the format: LEN (text)
Parameter Description: text, said text string to statistics.
Application example: assume that the cell holds the A41
reminded: LEN to statistics, 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 style that matches the specified value of the corresponding units in the array 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 perennial range of cells;
Match_type find the direction that the value of (-1,0 or 1).
match_type to -1 if, find the smallest greater than or equal to lookup_value value, Lookup_array must be in descending order;
If match_type to 1, fewer than or equal to lookup_value ascertain the most value, Lookup_array must be arranged in rising order ;
If match_type 0, find the first value is equal to lookup_value, Lookup_array can be arranged in any order; If omitted match_type, the default is 1.
Application examples: Figure 4 shows, in cell F2 enter the formula: = MATCH (E2, B1: B11, 0),GHD Purple Butterfly 2011, confirmed the results returned to find
reminded: Lookup_array can only be one column or row.
19, MAX function
function name: MAX
main functions: Find the maximum value in a set of numbers.
using the format: MAX (number1, number2 ... ...)
Parameter Description: number1, number2 ... ... representatives to request the maximum value or a reference cell (area), the parameter does not exceed 30.
Example: enter the formula: = MAX (E44: J44, 7,8,9,10), confirmed to display the E44 to the J44 cells and in the region and the maximum value 7,8,9,10 .
to remind: If the parameters are text or logical values, is ignored.
20, MID function
function name: MID
main functions: a text string from the specified position, and 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 specifies the starting position; num_chars that number to be intercepted.
Application example: assume that the cell holds the A47
remind: the formula of the parameters, use the English state of the comma
21, MIN function
function name: MIN
main functions: Find the minimum value in a set of numbers.
using the format: MIN (number1, number2 ... ...)
Parameter Description: number1, number2 ... ... representatives to request the minimum value or a reference cell (area), the parameter does not exceed 30.
Example: enter the formula: = MIN (E44: J44, 7,8,9,10), confirmed to display the E44 to the J44 cells and in the region and the minimum values ​​7,8,9,10 .
to remind: If the parameters are text or logical values, is ignored.
22, MOD function
function name: MOD
main functions: Find the remainder of dividing two numbers.
using the format: MOD (number, divisor)
Parameter Description: number representative of the bonus; divisor on behalf of the divisor.
Example: enter the formula: = MOD (13,4), accustomed the results show that
to remind: If the divisor parameter is zero, then the error value .
23, MONTH function
function name: MONTH
main functions: Find the reference to a specified date or the date cell of the month.
using the format: MONTH (serial_number)
Parameter Description: serial_number on behalf of the specified date, or reference cell.
Example: enter the formula: = MONTH (
apt remind: If the given appointment, include twice quotation marks in English; if you modify the 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 takes no arguments.
Example: enter the formula: = NOW (), instantly after confirmation showing the new system date and time. If the system date and time changed, fair press the F9 function key, you can let it change.
reminded: display the date and time format, you can re-set the cell formatting.
25, OR function
function name: OR
main functions: to return a logical value, only if all parameter values ​​are logical Otherwise, return a logical
using the format: OR (logical1, logical2, ...)
Parameter Description: Logical1, Logical2, Logical3 ... ...: the conditions to be tested, said the value or expression, up to the 30.
Application examples: In the cell C62 enter the formula: = OR (A62> = 60, B62> = 60), confirmed. If C62 is returned TRUE, A62 and B62 in that at least one value greater than or equal to 60, if the return FALSE, that the values ​​in A62 and B62 are less than 60.
to remind: If you specify a logical condition parameter contains non-logical values, then the function returns the error value
26, RANK function
function name: RANK
main functions: to return a numeric value in a column in the ranking relative to other values.
using the format: RANK (Number, ref, order)
Parameter Description: Number values ​​to sort the representative; ref numerical order in which the representatives of a range of cells; order on behalf of sort parameters (if it is ignored, according ranked in descending order, the larger the number, the smaller the value of ranking results; if non-
Example: If cell C2 enter the formula: = RANK (B2, $ B $ 2: $ B $ 31,0), D 1 can be derived after confirmation of language students in his class ranking in the results results.
remind: In the upon formula, we let the Number parameter to a relative reference to the form,GHD, and let the ref parameter to the utter reference in the form (joining a mouse over the lower right edge of the cell, into thin cross-linear (commonly called the complete ranking scores of other students language statistics.
27, RIGHT function
function name: RIGHT
main functions: a text string from the last character in the beginning, the interception of a specified number of characters.
using the format: RIGHT (text, num_chars)
Parameter Description: text representative of the character string to be cut; num_chars interception of a given number of representatives.
Application example: suppose that the cell holds the A65 characters.
reminded: Num_chars parameter must be greater than or equal to 0, if disregarded, the default it is 1; num_chars parameter is greater than if the length of the text, the function returns the whole text.
28, SUBTOTAL function
function name: SUBTOTAL
main functions: to return a list or database subtotals.
using the format: SUBTOTAL (function_num, ref1, ref2, ...)
Parameter Description: Function_num 1 to 11 (includes hidden values) or 101-111 (ignores hidden values) between the figures for What is the function specified in the list with subtotals calculated (Figure 6); ref1, ref2, ... ... on behalf of local or subtotals to be a reference to not more than 29.
Application examples: as shown in Figure 7, the cells in the B64 and C64, respectively, enter the formula: = SUBTOTAL (3, C2: C63) and = SUBTOTAL103, C2: C63), and will conceal the line 61, after confirmation The former appear as 62 (including the hidden rows), which appears as 61, not including the hidden rows.

to remind: If you take automated screening, no matter what type function_num parameters are chosen, SUBTOTAL function ignores anyone screening results are not included in the line; SUBTOTAL function is applied to the data columns or vertical area, does not apply to data rows or level area.
29, the function name: SUM
main functions: the calculation of all parameters and values.
using the format: SUM (Number1, Number2 ... ...)
Parameter Description: Number1, Number2 ... ... need to calculate the representative value may be a specific value, a reference cell (area),GHD NZ, logical values ​​and so on.
Application examples: as shown in Figure 7, in the cell D64 enter the formula: = SUM (D2: D63), confirmed the language of the score can be calculated.
to remind: If the argument is an array or reference, merely an of the numbers will be calculated. Blank the array or reference cells, logical values, text or error values ​​are ignored; if you modify the formula: = SUM (LARGE (D2: D63, 1,2,3,4,5)), you can find the altitude 5 results and.
30, SUMIF function
function name: SUMIF
main functions: to meet specified conditions for calculating the value and the range of cells.
using the format: SUMIF (Range, Criteria, Sum_Range)
Parameter Description: Range to determine the range of cells representative of conditions; Criteria for the specified conditional expression; Sum_Range need to calculate the value representative of where the range of cells.
Application examples: as shown in Figure 7, in the cell D64 enter the formula: = SUMIF (C2: C63, .
to remind: If you modify the above formula: = SUMIF (C2: C63, Because it is text-based, needs to be placed under the English double quotes (
31, TEXT function
function name: TEXT
main functions: the value specified format according to the corresponding number into text form.
using the format: TEXT (value, format_text)
Parameter Description: value to convert the value on behalf of or reference to the cell; format_text for the specified text in the form of digital format.
Example: If the B68 cell conservation values ​​1280.45, we enter the formula in cells C68: = TEXT (B68,
reminded: format_text parameters below the
32, TODAY function
function name: TODAY
main functions: given the system date.
using the format: TODAY ()
Parameter Description: This function takes not arguments.
Example: enter the formula: = TODAY (), confirm that immediately after the system date and time display. If the system date and time changed, just click the F9 function key, you can let it change.
remind: the date format exhibited, you can re-set the cell format (penetrate annex).
33, VALUE function
function name: VALUE
main functions: the value of a text on behalf of the string is converted to 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 LEFT and other text-based strings, we enter the formula in cells C74: = VALUE (B74), confirmed, can be converted to numeric.
to remind: If the text type value is not a result of these conversions, the use of the function to handle with these values, often returns error.
34, VLOOKUP function
function name: VLOOKUP
main functions: the premier column in the data chart to find the specified value, and accordingly returns the current row in the table by 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 which the range of cells; Col_index_num in the region to be returned table_array match the value of the column number (when Col_index_num is 2, the return table_array values ​​in column 2, 3, the return value of column 3 ... ...); Range_lookup is a logical value, if it is TRUE or omitted, the return near mate, that is, if can not find an exact mate, it returns the maximum value of less than lookup_value; If FALSE, it returns an exact match, if I were you, it returns the error value # N / A.
Application example: see Figure 7, we enter the formula in cell D65: = VLOOKUP (B65, B2: D63, 3, FALSE), confirmed, at the peak of the cell B65, enter a student's name (such as D 48), D65 cells showed immediate results of the student's language.
reminded: Lookup_value must Table_array see the first column of the region; if ignored Range_lookup parameters, Table_array the first column must be sorted; the Wizard in this function, the Range_lookup parameter usage is bad.
35, WEEKDAY function
function name: WEEKDAY
main functions: the date given corresponds to the specified number of weeks.
using the format: WEEKDAY (serial_number, return_type)
Parameter Description: serial_number on behalf of the specified date or dates of that contains the cell references; return_type performance on behalf of weeks [when the Sunday (Sunday) to 1, Saturday (Saturday ) is 7, this parameter is 1; when Monday (Monday) 1, Sunday (Sunday) to 7, this parameter is 2 (this is in line with the Chinese habit of); when Monday (Monday) to 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.
to remind: If the specified date, please put the English under the double quotes, such as = WEEKDAY ( one. Cell color effect
Select manner, format - conditional formatting, conditions, select the


1. interlacing color effect (odd row colors):
= MOD (ROW (), 2) = 1
2. wove color effect (even row colors):
= MOD (ROW (), 2) = 0
3. If you want to set the format for each application of a shading line 3, you can use the formula:
= MOD (ROW (), 3) = 1
4. If you want to set different odd and even column shading, at a time the formula of the ROW () to COLUMN () can, such as:
= MOD (COLUMN (), 2)
5. If you want to set the chess checkerboard shading (white + custom color):
= MOD (ROW () + COLUMN (), 2)
Description: The conditional formatting formula accustomed to determine the line number and column number, and the remainder is divided at 2 to 0. If 0, indicating the number of rows and columns of the same equality, then fill the cell to the specified color, or not fraught. In the conditional formatting, the formula results to return a number, non-0 numbers is the TRUE, 0, and the error value is FALSE. Therefore, the above formula can be written as:
= MOD (ROW () + COLUMN (), 2) 0
6. If you want to set the chess checkerboard shading (custom colors + custom color):
add style 2:
= MOD (ROW () + COLUMN (), 2) = 0
II. Color-filter
excel2007 can cell color, font color, or icon to be sorted.
If excel2003, need to add assistant column, the name of the usage defined by the color of the corresponding number, then the secondary sort column:
color cell in column A, select B1, Insert -> Name -> definitions, input a, input the following formula = get.cell (24, $ a1),
This method is accessible
the serial number of the font color. B1 enter = a duplicate the formula down to the fitting line.
the full table can be sorted by column B. If want to get the backdrop color of the reg code number will be changed to 63 to 24.
==
1.Excel2003 no corresponding function, but can be called Get.Cell Excel4.0 a macro function, but the macro function can not be directly used directly in the Excel2003, the name must be defined to use
2. Get.Cell (Num, Cell) is the function of the format, Get.Cell can obtain a cell in a kind of message, such as skeleton format, etc., Num is to get the specified type of information namely, out of 24 elements is to make this function color information, taken out afterward a 1-56 number to represent color number
3. If you're out in B1 enter your name and the elucidation of fill, then B1 is A1 in the figures in 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, the color of the column by A sort of.
attached Get.Cell the meaning of every parameter:
1 refer to the absolute address cell reference cells
2 No.
3 column reference column number cell
4 alike to the TYPE function
5
6 with reference to the text contents of the address reference to the address shown with reference to the formula
7 address format, text display text display
8 format
9 with reference to the address stored more carton above the left to return the neatness, the figures show
10 extra box store returns the right style, the figures show
11 store extra returns on the style side box, digital display
12 frame side return extra storage under the style,GHD Purple Straighteners, the figures show the inner pattern returned
13, figures show
14 If the cell is set to locked return True
15 is hidden if the formula returns True
16 returns the compartment breadth
17 points as the unit returned to the cell elevation
18 words Type the name of
19 in points font size
20 return all or if the cell first character is striking return True
21 If all or a cell in italics the first character returns True
22 If all or a cell first character is a unattached bottom line returns True
23 or if the cell of a character font of all the middle of a horizontal line returns True
24 returns the first character cell color number, 1 to 56. If set to automatic, returns 0
25 MS Excel does not support the outline format
26 MS Excel format does not advocate the shadow figures
27 insert page wrecks manually set the profile of the column
28 Outline of the field-level
29
30 if the level of the outline summary of the range compared to True
31 if the range is the outline of the synopsis column was True
32 shows the workbook and worksheet name of the
33 If the cell formatting for multiple lines of text compared to True
34 extra storage box on the left to return the color, figures show. If set to automatic, returns 0
35 extra storage box on the right to return the color, figures show. If set to automatic, returns 0
36 extra storage box at the top to return color, figures show. If set to automatic, returns 0
37 return retention extra box beneath the color, figures show. If set to automatic, returns 0
38 cells hereafter returns shadow colors, figures show. If set to automatic, returns 0
39 returns the cell back eclipse color, figures show. If set to automatic, returns 0
40 text display cell style
41 returns the address of the incipient equation reference
42 points as the unit returned to the use of the left window on the left to the class of cell Distance
43 in points in the top of the window to return to using the cell above the vertical distance
44 in points in the window on the left to return to the use of a cell to the right of the horizontal distance
45 points unit returns to the top of the window used in the cell below the vertical distance
46 are inserted remarks if the cell returns True
47 if the cell has to insert a sound hint return True
48 are inserted into the formula if cell Returns True
49 if the cell is an array formula returns the range of returns True
50 cell vertical combination, the figures show
51 perpendicular to the direction of the cell returned, figures show
52 pass back to the cell prefix character
53 text display shows the contents of the cell returned
54 returns the name of the PivotTable cell
55 returns the cell in the PivotTable twirl position
56
57 If always or a cell first character is superscript text return True
58 shows return of all or a cell font style
59 characters return the cell base line style, the figures show
60 If all or a cell for the first character subscript returns True
61 pivot
62 shows the workbook and worksheet name
63 returns the fill color of a cell mass
64 Back to the drawing foreground color
65 pivot
66 shows the workbook name