the Word
trick:
, enter the three , enter the three (WEEKDAY (I3, 2), 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday') Display day of the week
Excel common functions Daquan
, the ABS the function
function name: ABS
main function: find the corresponding figures for the absolute value.
format: ABS (number)
the parameters: number representative of demand for the value of the absolute value or cell reference.
application example: if cell B2 enter the formula: = ABS (A2), either enter a positive number (such as 100) or negative (-100) in cell A2, B2 are indicative of the positive (eg, 100).
Special note: If number is not numeric, but some characters (such as A, etc.), B2, returns an error value of the # VALUE!
2 AND function
function name: AND
main function: Returns the logical value: if all parameter values are logical true (TRUE) logical using the format
: AND (logical1, logical2, ...)
parameter description: Logical1, Logical2, Logical3 ......: said the condition value or expression to be tested up to 30.
Application examples: C5 cell enter the formula: = AND (A5> = 60, B5,> = 60) to confirm. C5 returns TRUE, the value of A5 and B5 are greater than or equal to 60, returns FALSE, the value in the A5 and B5 at least a less than 60.
Special note: if the specified logical condition parameters included in the non-logical values, the function returns the error value
3, the AVERAGE function
function name: the main function of the AVERAGE
: find the arithmetic mean of all parameters.
using the format: AVERAGE (number1, number2, ...)
parameters Description: number1, number2, ......: demand for the value of the average or reference cell (area), parameters are not over 30.
Application examples: B8 cell enter the formula: = AVERAGE (B7: D7, F7: H7, 7,8), after confirmation, you can find the B7 to D7 region, the values in the region of the F7 to H7 and 7-8 the average.
Special note: the reference area contains
4 COLUMN function
function name: the COLUMN
main function: to display the reference cell of the column label value.
format: COLUMN (reference)
parameters: the reference cell that is referenced.
Application examples: C11 cell enter the formula: = COLUMN (B11), and the confirmation 2 (column B).
remind: If you enter the formula in B11 cell: = COLUMN (), also shows a 2; corresponding to a function returns the value of the line label - ROW (reference).
5, CONCATENATE function
function name: CONCATENATE
main functions: the number of characters in the text or data in a cell are connected together to display in a cell.
using the format: CONCATENATE (Text1, Text ...)
parameters: Text1, Text2 ... need to connect the characters in the text or the referenced cell.
Application examples: in the C14 cell enter the formula: = CONCATENATE (A14,, '@', B14, 'com'), is confirmed, the characters in the A14 cell, _AT_ B14, cell character. com connection as a whole, in C14 cells.
Special note: If the parameter is not referenced cell, text format, to the parameter with double quotes in the English state, if the above formula to: = A14, & '@' & B14, & ' the dot-com ', will also achieve the same purpose. 6, the COUNTIF function
function name: the COUNTIF
main functions: statistical cell number of a range of cells that meet specified conditions.
using the format: COUNTIF (Range, Criteria)
parameters Description: the Range on behalf of the range of cells you want to Statistics; the Criteria specified in the conditional expression.
Application examples: in the C17 cell enter the formula: = COUNTIF (B1: B13, '> = 80'), confirmed to Statistics B1 to B13 range of cells, the value is greater than or equal to 80 cell number.
Special note: the blank cell in the allowed range of cells referenced.
7, the DATE the function
function name: the DATE
main function: to give the date of the specified value. parameters
using the format: DATE (year, month, day)
Description: The year for the specified year value (less than 9999); month for the specified month values (greater than 12); day for a specified number of days .
Application examples: in the C20 cell enter the formula: = DATE (2003,13,35), after confirmation, showing 2004-2-4.
Special note: the above formula, January 13, more than a month, postponed to January 2004; the number of days to 35 than the actual number of days in January 2004, the addition of four days, it is also postponed to 2004 February 4.
8, the function name: the DATEDIF
main functions: Calculate the return difference between the two date parameters.
using the format: = DATEDIF (date1, date2, 'y'), = DATEDIF (date1, date2, 'm') = DATEDIF (date1, date2, 'd')
parameter description: date1 on behalf of the front a date, date2 on behalf of the latter date; y (m, d) asked to return the difference of the two dates (month, day) number.
Application examples: C23 cell enter the formula: = DATEDIF (A23, TODAY (), 'y'), confirm return the current date TODAY ()) the difference with the date of the A23 cell , and returns the difference between the number of years.
Special note: This is a hidden function in Excel function wizard, can be entered directly used for the calculation of age, seniority and other very effective. 9
DAY function
function name: the DAY
main function: to find the specified date or reference number of days of the date in the cell.
using the format: DAY (serial_number)
parameters to: serial_number on behalf of the specified date, or reference cell.
Application examples: enter the formula: = DAY ('2003-12-18 '), after confirmation, the display 18.
Special note: If the given date, please include the quotation marks.
10, the DCOUNT functions
function name: the DCOUNT
function: return a database or list column in the meet specified criteria and the number of cells that contain numbers.
format: DCOUNT
parameters (database, field, criteria is) Description: Database expressed the need for a statistical range of cells; the Field function using the data columns (in the first row must have a logo items); the Criteria contain The condition of the range of cells.
application example: Figure 1, in the F4 cell enter the formula: = DCOUNT (A1: D11, 'language', F1: G2), after confirmation, you can find the equal to 70, while less than 80 the value of the number of cells (equivalent to the number of scores paragraph).
Special note: If the above formula is modified as follows: = DCOUNT (A1: D11, F1: G2), can also achieve the same purpose. 11
the FREQUENCY the function
function name: the FREQUENCY
main functions: to a vertical array of returned data in an area frequency distribution. using the format: - the FREQUENCY (data_array is bins_array)
parameters Description: Data_array said a set of data used to calculate the frequency or range of cells; Bins_array in front of the array are separated by a value.
Application examples: as shown in Figure 2, and B32 to B36 range of cells is selected, enter the formula: = the FREQUENCY (B2: B31, D2: D36 at), the input is complete, press CTRL + SHIFT + ENTER key to confirm, you can find the B2 to B31 region, the number of frequency for the values separated by D2 to D36 region (equivalent to the statistics of each fraction number).
Special note: the above input is an array formula input is completed, you need to confirm by pressing ({}), this braces can not be directly input. 12
, IF function
function name: the main function of the IF
: logic to judge the genuineness of the results based on specified criteria,
casque beats, returning the corresponding content.
using the format: = IF (the Logical Value_if_true, Value_if_false,)
parameter description: Logical representing a logical judgment expression; Value_if_true said when judging condition is a logic TRUE
Application examples: C29 cell enter the formula: = IF (C26> = 18 'compliant', 'not met'), convinced that, if the value is greater than or equal to 18 in the C26 cell the C29 cells meet the requirements of the words, on the contrary show
Special note: similar to the appropriate cell, please refer to the attached instance file.
13, the INDEX function
the name of the function: INDEX
main function: return to the list or array element values, this element by the row number and column ordinal index value to determine. use
format: INDEX (array, row_num, column_num)
parameters Description: Array on behalf of a range of cells or an array constant; Row_num specified line number (if omitted row_num, you must have column_num); Column_num said specified the column number (if omitted column_num, you must have row_num).
Application examples: as shown in Figure 3, F8 cell enter the formula: = INDEX (A1: D11, 4,3), after confirmation, showing the range of cells A1 to D11, line 4 and 3 at the intersection of the cell (ie, C4) the contents of the.
Special note: here the line number parameters (row_num) and the column number of parameters (column_num) relative to the reference range of cells in terms of an Excel worksheet rows or columns sequence number.
14, the INT the function
function name: The main function of the INT
: the value down rounded to the nearest integer.
using the format: INT (number)
parameters: the number expressed the need to take the whole of the value or contains the value of the reference cell.
Application examples: enter the formula: = INT (18.89), confirmed 18.
Special note: rounded, not rounded; If you enter the formula = INT (-18.89), it returns -19.
15, The ISERROR the function
function name: The ISERROR
main function of: used to test the functional value returned is wrong. If wrong, the function returns TRUE, otherwise returns FALSE.
format: ISERROR (value)
parameters: Value the need to test the value or expression.
application example: enter the formula: = ISERROR (A35/B35), confirm, if the B35 cell is empty or
remind: This function is usually associated with the IF function supporting the use of the above formula is modified to: = IF (ISERROR (A35/B35),'', A35/B35) If B35 is empty or the cell appears to be empty, contrary display A35/B35
results. In 16
the LEFT the function
function name: the LEFT
main functions: the first character of a text string beginning the interception of a specified number of characters.
format: LEFT (text, num_chars is)
parameters Description: The text represents the cross-sectional character string; num_chars is representative of a given interception number.
application example: assume that A38 cell to save the characters.
Special note: this function name in English means
17, LEN function
function name: the LEN
main functions: statistical text string in the number of characters.
using the format: LEN (text)
parameters: text, said text string to be statistical.
application example: assume that A41 cell saved
to remind: the LEN Statistics, regardless of the full-width characters or SBCS characters, each character is counted as
18, the MATCH function
function name: the MATCH
main function: Returns the array of elements that match the specified value in a specified manner the corresponding location. Use format: - the MATCH (--------------------------------------------------------------------- lookup_value The lookup_array, match_type)
, parameters: Lookup_value representatives need to find in the data table value; said
Lookup_array. may contain the values you want to find a contiguous range of cells;
Match_type way to find the value of (-1, 0 or 1).
If match_type is -1, to find greater than or equal to lookup_value minimum value, Lookup_array in descending order;
match_type is 1, find the less than or equal to lookup_value maximum value, Lookup_array must be sorted in ascending order ;
match_type is 0, find the equal to lookup_value first value Lookup_array can be arranged in any order; If you omit match_type, the default is 1.
Application examples: shown in Figure 4, in cell F2 enter the formula: = MATCH (E2, B1: B11, 0), the confirmation is returned to find results
remind: Lookup_array only one or a line.
19, the MAX function
function name: MAX
main function: calculate the maximum value in a number of groups.
format: the MAX (number1, number2 ...)
parameter description: number1, number2 ... representative to request the maximum value or the reference cell (area), not more than 30 parameters.
Application examples: enter the formula: = MAX (the E44: J44, 7,8,9,10), confirmed to display the maximum value in the E44 to J44 units and regional and values 7, 8, 9, 10 .
Special note: if the argument in the text or logical value is ignored.
20, the MID the function
function name: the MID
main functions: from a text string in the specified location, the interception of a specified number of characters.
using the format: MID (text, start_num equal to, num_chars is)
parameter description: the text on behalf of a text string; start_num equal to the specified starting position; num_chars is said to be the number of intercepted.
application example: assume that A47 cells saved the
Special note: the parameters in the formula to use English to state a comma
21, MIN function
function name: MIN
main function: calculate the minimum value in a number of groups.
format: MIN (number1, number2 ...)
parameter description: number1, number2 ... representative shall be required to the minimum value or the reference cell (area), not more than 30 parameters.
Application examples: enter the formula: = MIN (the E44: J44, 7,8,9,10), confirmed to show the minimum value of E44 to J44 units and regional and values 7, 8, 9, 10 .
Special note: if the argument in the text or logical value is ignored.
22, the MOD the function
function name: the main function of the MOD
: find the remainder of dividing two numbers.
using the format: MOD (number, divisor)
parameter description: number on behalf of the dividend; divisor on behalf of the divisor.
Application examples: enter the formula: = MOD (13,4), confirmed 1.
remind: If the divisor parameter is zero, it shows the error value # DIV / 0! .
23, MONTH, the function
function name: MONTH,
main function: find the specified date or reference date in the cell in January.
using the format: MONTH (serial_number)
, parameters: serial_number representative of the specified date, or reference cell.
Application examples: enter the formula: = MONTH ('2003-12-18 '), after confirmation, the display 11.
Special note: If the given date, contained in quotation marks; If the above formula is modified to: = YEAR ('2003-12-18 ') Returns the year corresponding to the value
24, the NOW the function
function name: the NOW
main functions: given the current system date and time. The parameters
using the format: NOW ()
: This function takes no arguments.
Application examples: enter the formula: = NOW (), immediately after confirmation of the current system date and time. If the system date and time changes, simply click on the F9 function key, you can let it change.
remind: to display the date and time format, you can re-set the cell formatting.
25, the OR function
function name: OR
main function: Returns the logical value only if all parameter values are logical otherwise returns logical true (TRUE) using the format
: OR (logical1, logical2, ...)
parameters: Logical1, Logical2 Logical3 ......: conditional value or expression to be tested up to 30.
Application examples: C62 cell enter the formula: = OR (the A62> = 60, B62> = 60), to confirm. If C62 returns TRUE, the value in A62 and B62 at least one greater than or equal to 60, returns FALSE, the value in A62 and B62 are less than 60.
Special note: if the specified logical condition parameters included in the non-logical values, the function returns the error value 26
RANK function
function name: of RANK
main functions: return a certain value, a value relative to other numerical ranking. use
format: RANK (Number, ref, order)
parameters Description: Number representatives need to sort values; ref on behalf of the sort the values in which the range of cells; order on behalf of sorting the way parameters (if ignored, in descending order according to rank, that is, the greater the value, the ranking results with smaller values; if a non-
Application examples: in cell C2 enter the formula: = RANK (B2, $ B $ 2: $ B $ 31,0), confirmation can be obtained after a small students' language scores rank in the class results in results.
Special note: in the above formula, we let the Number parameter to take the form of relative reference, and let the ref parameters take the form of an absolute reference (an increase of a mouse over the lower right corner of the cell, into thin cross-linear (often called the to complete the ranking statistics of the language scores of other students. 27
the RIGHT the function
function name: the RIGHT
main functions: starting from the last character of a text string, the interception of a specified number of characters. using the format: RIGHT (text, num_chars is)
parameters
Description: The text represents the cross-sectional character string; num_chars is representative of a given interception number.
application example: assume that A65 cell to save the characters.
remind: Num_chars parameter must be greater than or equal to 0, if omitted, the default for one; If the the num_chars parameter is greater than the length of the text, the function returns the entire text. In 28
SUBTOTAL the function
function names: the SUBTOTAL
main function: return to the list or database classification summary.
using the format: SUBTOTAL (function_num,, ref1 had, ref2, ...)
parameters Description: Function_num 1-11 (includes hidden values) or 101-111 (ignores hidden values) between the digital and used specify what functions in calculating subtotals within a list (Figure 6); ref1 had, ref2, ...... representatives to carry out the subtotals area or a reference to not more than 29.
Application Examples: As shown in Figure 7, B64 and C64 cells enter the formula: = SUBTOTAL (3, C2: of C63) and = SUBTOTAL103, C2: of C63), and hidden line 61, to confirm The former shows that 62 (including hidden line), which is displayed as 61, not including the hidden rows. particular, to remind
: to take the the AutoFilter whether function_num parameters to choose what type of the SUBTOTAL function ignores any not included in the filter results in line; SUBTOTAL function applies to the data columns or vertical areas, does not apply to data line or level area.
29, the function name: the SUM
main function: and calculating the values of all parameters. using the format
: SUM (the Number1 the Number2 ......)
parameters: Number1, Number2. representatives need to calculate the value can be specific values, the reference cell (area), the logic value.
Application examples: As shown in Figure 7, in the D64 cell enter the formula: = SUM (D2: D63), confirm the language total score can be calculated.
Special note: If the argument is an array or reference, only one of the figures will be calculated. Blank cell in the array or reference, logical values, text, or an error value will be ignored; If the above formula is modified to: = SUM (LARGE (D2: D63, {1, 2, 3, 4, 5})) can find the top 5 results. In 30
SUMIF function
function name: the main function of the SUMIF
: falls within the value within the range of cells of the specified conditions. using the format
: the SUMIF (Range, Criteria, and Sum_Range the)
parameters: the Range on behalf of the conditions to judge the range of cells; the expression of the Criteria for the specified conditions; Sum_Range on behalf of the need to calculate the numerical range of cells. the
Application Examples: As shown in Figure 7, in the D64 cell enter the formula: = the SUMIF (C2: C63, 'Male', D2: D63), confirm you can find the language scores of male students and .
Special note: If the above formula is modified to: = the SUMIF (C2: C63, 'female',
www.chaussurellouboutinpascher.com, D2: D63), you can find the Because it is text-based, needs to be placed in English under the double quotation marks ('male' and 'female'). function name of the TEXT function
31,: TEXT
main function: according to the specified numeric format corresponding figure is converted to text form.
using the format: TEXT (value, the format_text)
parameters Description: need to convert the value of the value on behalf of or reference to the cell; format_text specified text in the form of digital format.
application example: the value 1280.45 B68 cells saved, we enter in the C68 cell formula: = TEXT (B68, '$ 0.00'), confirmed
reminded: the the format_text parameters can be determined according to the type of the
32, TODAY function
function name: the TODAY
main function: given the system date. The parameters
format: TODAY ()
: This function takes no arguments.
application example: enter the formula: = TODAY (), immediately after confirmation showing the date and time. If the system date and time changes, simply click on the F9 function key, you can let it change.
Special note: the displayed date format, you can re-set (see annex) through the cell formatting.
33, the VALUE the function
function Name: the VALUE
main functions: a representative of the value of the text string is converted to numeric.
using the format: the VALUE (text)
parameters: the text on behalf of the need to convert the text string value.
Application examples: B74 cells through the LEFT function to intercept text string in the C74 cell enter the formula: = VALUE (B74), after confirmation can be converted to numeric.
Special note: If the text values without conversion function to deal with these values, often returns an error.
34, the VLOOKUP function
function name: the VLOOKUP
main function: to find the value specified in the first column of the data table, and return to the data table to the current line at the specified column value. using the format
: VLOOKUP (--------------------------------------------------------------------- lookup_value The, of table_array, a col_index_num, range_lookup It is)
parameter description: Lookup_value on behalf of the need to find the value; Table_array representatives need to find data in which a range of cells; Col_index_num for the the table_array region to be returned match the value of the column number (When Col_index_num for 2:00, return the value 2 table_array, returns three values of 3:00, ...); the Range_lookup is a logical value, TRUE or omitted, it returns an approximate match, that is, if you can not find the exact matching value, it returns the maximum value of less than the lookup_value; If it is FALSE, return an exact match. If not, and returns the error value # N / A.
Application examples: see Figure 7, in the D65 cell enter the formula: = VLOOKUP (B65, B2: D63, 3, FALSE). confirmed, enter the name of a student in the B65 cells (such as small 48),
supra shoes, the D65 cell instantly display the students' language scores.
remind: the Lookup_value see in the first column of the Table_array region; Range_lookup parameter is ignored, then the Table_array the first column must be sorted; in this function wizard, about Range_lookup parameter usage is wrong.
35, WEEKDAY function
function name: WEEKDAY
main function: given a specified date, the corresponding number of weeks.
using the format: WEEKDAY (serial_number, return_type)
parameters shows: serial_number representatives of the specified date, or reference the cell containing the date of; the representation the return_type on behalf week [Sunday (Sunday) to 1, Saturday (Saturday ) is 7:00,
burberry soldes, this parameter is 1; Monday (Monday), Sunday (Sunday) 7, the parameter (in this case the Chinese people's habits); Monday (Monday) to 0 Sunday (Sunday) 6:00,
supra, this parameter is 3].
application example: enter the formula: = WEEKDAY (TODAY (), 2), to confirm the number of weeks given after the system date.
Special note: If the specified date, please put the double quotes in the English state, such as = WEEKDAY ('2003-12-18 ', 2).
one. Select the color effect of the cell form format - conditional formatting, conditional selection formula, the formula is as follows, and then choose
interlaced color effect (odd row colors):
= MOD (ROW (), 2) = 1
2 interlaced color effect (even row colors):
= MOD (ROW (), 2) = 0
3. If you want the formatting to be applied once every three lines of shading, use the formula:
= MOD (ROW (), 3) = 1
4. If you want to set the parity columns of different shading, as long as the ROW () formula changed COLUMN () can, such as:
= MOD (COLUMN (), 2)
5. If you want to set chess checkerboard shading (white + custom color):
= MOD (ROW () + COLUMN (), 2)
Note: The conditional formatting formula is used to judge the line number and column number and the remainder divided by 2 to 0. If 0, the same as the parity of the number of rows and number of columns, then fill the cells with the specified color, or not filling. In conditional formatting, formula results returns a number, non-zero number is TRUE, 0 and the error is FALSE. Therefore, the above formula can be written as:
= MOD (ROW () + COLUMN (), 2) 0
6 If you want to set up the checkerboard shading of chess (custom color + custom color) the:
join style 2:
= MOD (ROW () + COLUMN (), 2) = 0
two.
Excel2007 sort by cell color, font color, or icon color filter.
If excel2003 need to add a secondary column, use the defined name corresponding to the color number, then the auxiliary column to sort:
color cell in column A, select B1, Insert -> Name - > Define, enter a below enter the formula = get.cell (24, $ a1),
This method is available the serial number of the font color. B1 enter = a formula is copied down to the appropriate line.
full table can sort by column B. If they wanted to get the serial number of the background color will be changed to 24 63 to.
== into the name to use
2.Get.Cell (Num, Cell) is the format of this function, Get.Cell can get a variety of information in a cell, such as border format the Num is specified to obtain , 24 this function to remove the color information of the unit, take out a 1-56, that a certain number of the color
3. If you enter in B1 column you define the name and filled, then figure is the color code in the A1 B1, B2 Digital will be A2, color code, and so on
4 Now you press the B column sort, in fact, sort by the color of the column A a.
attached Get.Cell of the meaning of each parameter:
reference cell absolute address
of 2 with reference to the cell's column number
3 reference cell column,
4 similar TYPE function
5 refer to the address Content
the 6 text reference to the address of the formula
reference to the format of the address text display
8 text with reference to the extra box the address the format
of 9 returned storage on the left style, digital storage
10 returns the right of the extra box style, the figures show that
11 pass back to store extra box on the side style, digital display
12 pass side of the back to store extra box style, digital
13 returns the internal drawings, figures show
14 if the cell is set, 15
, locked transfer back to True if the formula in cell width
hidden state returns True
16 returns. returned 17 points as the unit cell height
18 fonts
19 units of returns font
21 of the 20 cell all or the first character for the Bold returns True
If a cell all or the first character returns True
22 cell or the first character is the single bottom line returns True
23 if the cell or the first character fonts the middle of a horizontal line in italics returns True
24 returns the cell a character color digital, 1-56. If set to automatic, returns the 0
25 in MS Excel does not support the outline format
26 MS Excel does not support shadow format
27 figures show that manually insert page breaks set
28 outline column bar level
30 of the the level
29 outline a summary of the outline of the range of the column, compared with True
31 range for a summary of the outline of the bar compared to True
32-show live workbook and worksheet names
33 if the cell formatting, compared with True
34 return save extra box on the left color for multiple lines of text, figures show. If set to automatic, returns 0
35 returns save extra box to the right of color, figures show. If set to automatic, returns 0
36 returns the stored extra box above the color, figures show. If set to automatic, returns 0
37 returns the stored extra box below colors, figures show. If set to automatic, returns 0
38 returned cell prospects for the shadow color, figures show. If set to automatic, returns 0
39 returns the cell back the shadow color, figures show. If set to automatic, returns 0
40 text display cell styles
41 returns a reference to the address of the original formula
42 point to as the unit returned to use in the window on the left to the left of the cell level distance distance
43 point returns the top of the window to the cell above the vertical distance
44 points as the unit returned in the window on the left to the cell right of the level of 45 points for the unit returns to the top of the window to the cell below the vertical distance
46 cell insert annotations returned to True
47 cell insert the voice prompt returns True
48 cell insert the formula相关的主题文章:
but remain as living in a fog 雨越下越大 green leaves from the root out, but the roots, Hanlaishuwang, this seems a symbol of spring and autumn turnover, just this cycle, but also contains a number of conditions, how much love it?