Clerk Finance can be used. - Qzone log
| Back to logs list
Reprinted from 819898 at 5:10 p.m. on August 10, 2010 Read (loading. ..) Comments (0) Category: Personal Diary
Word trick:
one, enter the three \Third, enter the three \CHOOSE (WEEKDAY (I3, 2), \; show of the week
Excel common 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 both cell A2 enter a positive number (eg 100) or negative (eg -100), B2 in both show positive (eg 100).
to remind: If the number parameter is not numeric, but 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 expression, 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 specify a logical condition parameter contains non-logical values, then 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 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,
red wing store, 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 label value and a return line of the function - ROW (reference).
5, CONCATENATE function
function name: CONCATENATE
main functions: the number of characters in text or data in a cell together, displayed in a cell.
using the format: CONCATENATE (Text1, Text ... ...)
Parameter Description: Text1, Text2 ... ... the need to connect the characters of text or reference cell.
Application examples: In the cell C14 enter the formula: = CONCATENATE (A14 ,\characters and. com connection 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,
red wing sale, 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 blank cells appear.
7, DATE function
function name: DATE
main functions: the values given in the specified date.
using the format: DATE (year, 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, postponed to January 2004; 35 days, compared with January 2004 the actual number of days the addition of 4 days, it is also extended to 2004 on February 4.
8, the function name: DATEDIF
main functions: calculate the difference between returns two 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 annual difference between the two dates (month,
red wing shoes outlet, day) number.
Application examples: In the cell C23 enter the formula: = DATEDIF (A23, TODAY (), \, and returns the number of years difference.
reminded: This is a hidden function in Excel, is found in the Function Wizard, you can use direct input for the calculation of age, length of service and so very effective.
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 list of columns to meet 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 marks on the first line item); Criteria include Conditions range of cells.
Application examples: Figure 1 shows,
red wing work boots, 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 section).
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 array of 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 that separated the front of an array of values.
Application examples: as shown in Figure 2, while selected range of cells B32 to B36, enter the formula: = FREQUENCY (B2: B31, D2: D36), enter the complete press \key to confirm, you can find the B2 to the B31 area, according to D2 to D36 region separated values of the frequency of the number of segments (equivalent to the number of statistical segments of the score).
reminded: The input is an array formula, you're finished, you need to by pressing \({}), the braces 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 false 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,
red wing mens boots, similar to the \the corresponding 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 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 intersection of three cells (ie, C4) in the content.
remind: line number argument here (row_num) and column number parameter (column_num) is relative to the terms of reference of the range of cells, not Excel worksheet rows or columns in the sequence 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 empty or \
reminded: This function is usually associated 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 cut; 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 means \
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 manner that matches the specified value of the corresponding elements 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 continuous range of cells;
Match_type find the way 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, less than or equal to lookup_value find the maximum value, Lookup_array must be sorted in ascending 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), 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 dividend; divisor on behalf of the divisor.
Example: enter the formula: = MOD (13,4), confirmed 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 (\
to remind: If the given date, include double 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 (), immediately after confirmation showing the current system date and time. If the system date and time changed, just click 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 above formula, we let the Number parameter to a relative reference to the form, and let the ref parameter to the absolute reference in the form (adding a \mouse over the lower right corner of the cell, into thin cross-linear (usually 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: assume that the cell holds the A65 \characters.
reminded: Num_chars parameter must be greater than or equal to 0, if omitted, the default it is 1; num_chars parameter is greater than if 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 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 regional 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 hide the line 61, after confirmation The former appear as 62 (including the hidden rows),
red wing shoes online, 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 any screening results are not included in the line; SUBTOTAL function is applied to the data columns or vertical area, does not apply to row 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), 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, only one 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 top 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 preservation values 1280.45, we enter the formula in cells C68: = TEXT (B68, \
reminded: 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 takes no 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 displayed, you can re-set the cell format (see 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 deal with these values, often returns 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 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 approximate match, that is, if can not find an exact match, it returns the maximum value of less than lookup_value; If FALSE, it returns an exact match, if not, 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, as long as 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 wrong.
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 representation 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. Select a color effect to the cell form, format - conditional formatting, conditions, select the \
1. interlacing color effect (odd row colors):
= MOD (ROW (), 2) = 1
2. interlaced 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, 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 not filled. 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 can
excel2007 cell color, font color or icon to be sorted.
If excel2003, need to add auxiliary column, the name of the method defined by the color of the corresponding number, then the secondary sort column:
color cell in column A, select B1, Insert -> Name - > definition, input a, input the following formula = get.cell (24, $ a1),
this method get 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 want to get the background color of the serial number will be changed to 63 to 24.
== \into the name to use
2.Get.Cell (Num, Cell) is the function of the form, Get.Cell can get a cell in a variety of information, such as frame format, etc., Num is specified to obtain the kind of information, 24 out this function is to let the color information unit, taken out after a 1-56 number to represent color number
3. B1 column if you enter your name and fill out the definition, 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 similar
4 TYPE function
5 with reference to the text contents of the address
6 shows the light formula
7 with reference to the address format of the address, text display text display
8 format
9 with reference to the address to return extra storage box left style, figures show
10 extra storage box on the right to return the style, the figures show
11 store extra return on the style side box, digital display
12 frame side return the extra storage under the style, number of Show
13 returns the internal drawings, figures show
14 locked if the cell is set to return True
15 is hidden if the formula returns True
16
returns the width of the cell 17 points as the unit returned to the cell height
18 point font name
19 to return the font size for the unit cell all
20 or if a character is bold 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 single bottom line returns True
23 if the cell or the first characters of all the middle of a horizontal line font Returns True
24 returns the first character color cell 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 support the shadow figures
27 insert page breaks manually set the outline 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 summary 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 save extra box below the color, figures show. If set to automatic, returns 0
38 cells future returns shadow colors, figures show. If set to automatic, returns 0
39 returns the cell back shadow color, figures show. If set to automatic, returns 0
40 text display cell style
41 returns the address of the original equation reference
42 points as the unit returned to the use of the left window on the left to the level 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 comments if the cell returns True
47 if the cell has to insert a sound prompt 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 alignment, 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 pivot position
56
57 If all 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 bottom 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
Linear Mode
