Word trick:
First, enter the three
Third, enter the three Enter = CHOOSE (WEEKDAY (I3, 2), show of the week
Excel common functions Daquan
1, ABS function
function name: ABS
main functions: find the corresponding figures the absolute value.
using the format: ABS (number)
Parameters: number of representatives shall be required to absolute values or cell references.
Example: If cell B2 enter the formula: = ABS (A2), in cell A2 enter either a positive number (eg 100) or negative (eg -100), B2 are shown in a positive (eg, 100).
Special note: If number is not numeric, but some characters (such as A, etc.), the B2 returns the error value
2, AND function
function name: AND
main function: Returns the logical value: if all parameters are 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.
Example: In cell C5 enter the formula: = AND (A5> = 60, B5> = 60), to confirm. If the C5 in the returns TRUE, the description of A5 and B5 in values greater than or equal to 60, if the returns FALSE, A5 and B5 in the description of at least one value is 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, ... ...: the average value shall be required or reference cell (area), not greater than 30.
Example: In cell B8 enter the formula: = AVERAGE (B7: D7, F7: H7, 7,8), confirmation, you can find the area B7 to D7, F7 to H7 region and the value 7,8 average.
Special note: If the reference area contains
4, COLUMN function
function name: COLUMN
main function: to display the column labels refer to a cell value.
using the format: COLUMN (reference)
Parameter Description: reference for the referenced cell.
Example: In cell C11 enter the formula: = COLUMN (B11), confirmation is displayed as 2 (ie, column B).
remind: If cell B11 enter the formula: = COLUMN (), also shows that 2; there is a corresponding value of the function to return row label - 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.
Example: 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 state the parameter with double quotes in English, 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 conditional expressions.
Example: 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 .
Example: In cell C20 enter the formula: = DATE (2003,13,35), confirmed, showing 2004-2-4.
Special note: Because of the above formula, January 13, 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, function name: DATEDIF
main functions: calculate the return difference between two date parameters.
using the format: = DATEDIF (date1, date2, a date, date2 on behalf of a later date; y (m, d) requires an annual return difference between two dates (month, day) number.
Example: 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 calculation of 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 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 quotes 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 containing numbers.
using the format: DCOUNT (database, field, criteria)
Parameter Description: Database range of cells that expressed the need for statistics; Field said that the data used in the function list (must be marked in the first line item); Criteria included conditions of the cells.
Application examples: Figure 1 shows, in cell F4 enter the formula: = DCOUNT (A1: D11, than or equal to 70, while values 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), you 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 expressed as the front separated by 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), the input finished, press key for confirmation, you can find the B2 to B31 area, according to area D2 to D36 values separated by the frequency of the segment number (equivalent to the number of statistical segments of each fraction).
remind: the input is an array formula, entered,
ralph lauren, need to press ({}), the braces can not be directly input.
12, IF function
function name: IF
main functions: the 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 logical TRUE
Example: In cell C29 enter the formula: = IF (C26> = 18, the C29 cell shows
Special note: this article, similar to the the appropriate cell, please refer to the attached specific instance document.
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 value to determine.
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 examples: Figure 3 shows, in cell F8 enter the formula: = INDEX (A1: D11, 4,3), confirmed the show 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 No..
14, INT function
function name: INT
main functions: the value rounded down to the nearest integer.
using the format: INT (number)
Parameters: number expressed the need for rounding numerical values or contains a reference cell.
Example: enter the formula: = INT (18.89), confirmed show 18.
remind: when in 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: a function type used to test whether the returned value is wrong. 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),
polo ralph lauren, recognized, if the B35 cell is empty or
Special note: This function is typically used with the IF function matching, if modified to the above formula: = 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)
Parameters: text representatives to cut the string of characters; num_chars interception on behalf of a given number.
Application examples: Suppose A38 cell preserve character.
remind: This function name in English means
17, LEN function
function name: LEN
main functions: the number of characters in a text string statistics.
using the format: LEN (text)
Parameters: text, said text string to statistics.
Application examples: Suppose A41 cell preserve
remind: LEN to statistics, whether in full-width characters, or the half-width characters, each character are counted as
18, MATCH function
function name: MATCH
main functions: to return in the specified mode with the specified number of elements in the array match the corresponding location.
using the format: MATCH (lookup_value, lookup_array, match_type)
Parameter Description: Lookup_value representatives need to find in the data table values;
Lookup_array that may contain the values you want to find a continuous range of cells;
Match_type that lookup values (-1, 0 or 1).
If match_type is -1, greater than or equal to lookup_value find the smallest 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,
louboutin, Lookup_array must be in ascending order ;
If match_type is 0, the first to find a value equal to lookup_value, Lookup_array can be 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 result is returned to find
remind: Lookup_array only for a column or row.
19,
louboutin pas cher, MAX function
function name: MAX
main functions: find a number of the maximum.
using the format: MAX (number1, number2 ... ...)
Parameter Description: number1, number2 ... ... representatives to request the maximum value or 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 of 7,8,9,10 .
Special note: If the parameters are text or logical values,
abercrombie, it is ignored.
20, MID function
function name: MID
main functions: a text string from the specified location, the interception of a specified number of characters.
using the format: MID (text, start_num, num_chars)
Parameters: text represents a text string; start_num that the specified starting position; num_chars that number to be intercepted.
Application examples: Suppose A47 cells stored in the
remind: the formula of the parameters, the use of the comma in English state,
21, MIN function
function name: MIN
main functions: find a number of the minimum.
using the format: MIN (number1, number2 ... ...)
Parameter Description: number1, number2 ... ... representatives to request the minimum value or reference 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 of 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 the remainder of dividing two numbers.
using the format: MOD (number, divisor)
Parameters: number on behalf of the dividend; divisor on behalf of the divisor.
Example: enter the formula: = MOD (13,4), confirmed the results show
remind: if divisor parameter is zero, then the error value .
23, MONTH function
function name: MONTH
main functions: find the specified date or the date cell reference 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 quotes 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,
doudoune moncler, you can let it change.
remind: the date and time display format, you can re-set the cell formatting.
25, OR function
function name: OR
main function: Returns the logical value, only if all parameter values are logical Otherwise, return a logical
using the format: OR (logical1, logical2, ...)
Parameters: Logical1, Logical2, Logical3 ... ...: that the conditions to be tested value or expression, up to the 30.
Example: In cell C62 enter the formula: = OR (A62> = 60, B62> = 60), to confirm. If C62 is returned TRUE, shows the value in A62 and B62 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 representatives need to sort the values; ref value on behalf of which the range of cells sorted; order on behalf of sort parameter (if ignored, according to descending ranking, that is,
abercrombie france, the greater the value, the smaller the ranking number; if other than
application example: If in cell C2 enter the formula: = RANK (B2, $ B $ 2: $ B $ 31,0), confirmed the small one can be derived in the language performance of students in class performance ranking the results.
remind: In the above formula, we let the Number parameter to a relative reference form, and let the ref parameter taken the form of an absolute reference (the addition of a mouse over lower right corner of the cell, into thin cross-linear (usually called the complete ranking of other students of language performance 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)
Parameters: text representatives to cut the string of characters; num_chars interception on behalf of a given number.
Application examples: Suppose A65 cell preserve character.
remind: Num_chars parameter must be greater than or equal to 0, if omitted, the 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, ...)
Parameters: Function_num 1 to 11 (includes hidden values) or 101 to 111 (ignores hidden values) between the numbers used to specify what function to use in calculating subtotals within a list (Figure 6); ref1,
moncler pas cher, ref2, ... ... on behalf of the region you want to subtotal or references, no more than 29.
Application examples: as shown in Figure 7, in cell 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 is displayed as 62 (including hidden line), which is shown as 61, not including the hidden rows.
Special note: If you take automatic screening, regardless of what type function_num parameter selection, SUBTOTAL function ignores any screening results are not included in the line; SUBTOTAL function for the data columns or vertical area, does not apply to data line or level area.
29, the 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), the logical values.
Application examples: as shown in Figure 7,
louboutin pas cher, in cell D64 enter the formula: = SUM (D2: D63), after confirmation you can find the language score.
Special note: If the 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 formula: = SUM (LARGE (D2: D63, {1,2,3,4,5})), you can find the top five results and.
30, SUMIF function
function name: SUMIF
main functions: calculate the range of cells that meet specified criteria and values.
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 representatives need to calculate the value where the range of cells.
Application examples: as shown in Figure 7, in cell D64 enter the formula: = SUMIF (C2: C63, .
Special note: If you modify the above formula: = SUMIF (C2: C63, Because it is text-based, the state needs to be placed in English double quotes (
31, TEXT function
function name: TEXT
main functions: the value of the specified format will be converted to the corresponding number in 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 value 1280.45, 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 system to confirm the date and time. If the system date and time has changed, just click the F9 function key, you can let it change.
remind: display the date format, you can re-set cell formatting (see Annex).
33, VALUE function
function name: VALUE
main functions: a representative value of the text string into a numeric type.
using the format: VALUE (text)
Parameter Description: text-based representatives need to convert text string value.
Example: If the B74 cell function is intercepted by the LEFT and other text-based strings, we have cell C74 enter the formula: = VALUE (B74), to confirm, you can convert it to numeric.
Special note: If you type text without the conversion value in dealing with the function of these values, it is often an error is returned.
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, the return table_array values in column 2, 3, the return value of the first 3 ... ...); Range_lookup is a logical value, if it is TRUE or omitted, then the return an approximate match, that is, if no exact match, it returns the largest value less than lookup_value; if FALSE, the return value is an exact match, if not found, returns 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 D 48), D65 cells immediately show the student's language performance.
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 parameter is wrong.
35, WEEKDAY function
function name: WEEKDAY
main functions: corresponding to the specified date given the number of weeks.
using the format: WEEKDAY (serial_number, return_type)
Parameter Description: serial_number behalf of the specified date or the date of the cell containing the reference; return_type representation on behalf of week [when Sunday (Sunday) to 1, Saturday (Saturday ) is 7, this parameter is 1; when Monday (Monday) 1, Sunday (Sunday) is 7, this parameter is 2 (this is in line with the Chinese people's habits); When Monday (Monday) to 0 , Sunday (Sunday) to 6, this parameter is 3].
Example: enter the formula: = WEEKDAY (TODAY (), 2), verify the system date is given after the number of weeks.
Special note: If the specified date, on the English state in double quotes, such as = WEEKDAY (
one. Select the cell color effect form, format - conditional formatting, conditions, select the
1. 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 to set the format is applied once every 3 row shading, use the formula:
= MOD (ROW (), 3) = 1
4 If you want to set the parity columns of different shades, as long as the formula ROW () to COLUMN () can, such as:
= MOD (COLUMN (), 2)
5. If you want to set chess checkerboard shading (white custom colors):
= MOD (ROW () + COLUMN (), 2)
Description: This conditional formatting formula used to determine the line number and column number and The remainder is divided by 2 to 0. If is 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 (),
abercrombie and fitch, 2) 0
6. If you want to set up chess checkerboard shading (custom colors + Custom colors):
adding style 2:
= MOD (ROW () + COLUMN (), 2) = 0
two. Color filter
excel2007 can cell color, font color, or icon sort.
If the excel2003, need to add auxiliary columns, 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 the serial number of available font colors. B1 enter = a will 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.
| Back to logs list
Tag: IT Clerk Name regional format
reproduced from a friend network user at 16:53 on June 12, 2011 Read (loading. ..) Comments (2) Category: Diary outside the set
相关的主题文章:
Wen stresses removal of system obstacles to develo Two men fled after the murder, security coordinato WASHINGTON family opened a small shop , only 12 -year-old Yan Yan (a pseudonym ) to take guests from the freezer to the beer , only to hear This occurred in the accident on August 14 , the Town Danzhou sea port village girl Yanyan facial injuries . Danzhou business sector informed of the situation, that will be involved in the investigation.