【 Weak current College 】 EXCEL tips, life is enough for you to enjoy (3)

VIII) lookup and reference functions

1.ADDRESS
Purpose: to return the text in a workbook with a cell reference.
Syntax: ADDRESS (row_num, column_num, abs_num, a1, sheet_text)
Parameters: Row_num is the cell references in the use of line number; Column_num is a cell reference is used in the specified column; Abs_num returns a reference type (1 or omitted as absolute references absolute line number 2, relative column, 3 relative line number, absolute column, 4 is a relative reference); A1 is a logical value that is used to indicate to A1 or R1C1 reference style is returned. If A1 is true or omitted, the function returns the ADDRESS A1-style reference; if false, the function A1 ADDRESS returns R1C1-style references. Sheet_text is a text, specified as an outer reference in the name of the worksheet, if you omit the sheet_text, you do not use any of the name of the worksheet.
Example: the formula "= ADDRESS (1, 4, 4, 1)" return D1.
2.AREAS
Purpose: Returns a reference that contains the number of areas.
Syntax: in AREAS (reference).
Parameters: Reference to a cell or cell range reference, or you can refer to multiple areas.
Note: If you need to combine several reference is specified as a parameter, you must use parentheses to prevent Excel comma as the delimiter between parameters.
Example: the formula "= AREAS (a2: b4)" returns 1, = AREAS ((A1: A3, A4: A6, B4: B7, A16: A18)) returns 4.
3.CHOOSE
Use: according to the given index value, from as many as 29 to select parameters in the appropriate value or operation.
Syntax: CHOOSE (value1, value2 index_num,, ...).
Parameters: Index_num is used to specify the ordinal value of the parameter to be chosen, it must be 1 to 29 digits, or between is a number from 1 to 29 of the formula or cell reference; Value1, value2, ... From 1 to 29 values parameter can be a number, cell, defined names, formulas, functions, or text.
Example: the formula "= CHOOSE (2," computer "," Lovers ") returns" enthusiasts ". The formula "= SUM (A1: CHOOSE (3, A10, A20, A30))" and the formula "= SUM (A1: A30)" equivalent (because CHOOSE (3, A10, A20, A30) returns the A30).
4.COLUMN
Purpose: Returns the column number of the given reference.
Syntax: COLUMN (reference).
Parameters: the Reference for which you want to know the column of the cell or range of cells. If you omit the reference, it is assumed that the function COLUMN is on the cell references. If the reference to a cell range, and the function COLUMN as an array of input level, the COLUMN function reference in the column to return the horizontal array.
Example: the formula "= COLUMN (A3)" returns 1, = COLUMN (B3: C5) returns 2.
5.COLUMNS
Purpose: Returns an array or reference number of the column.
Syntax: COLUMNS (array).
Parameters: Array for which you want to know the number of columns in the array, the array formula or reference to a range of cells.
Example: the formula "= COLUMNS (B1: C4)" returns 2 = COLUMNS ({5, 4, 4, 5}) returns 2.
6.HLOOKUP
Use: table or an array of first-line finds the specified value, and then returns a table or an array of the current column values at the specified row.
Syntax: HLOOKUP (lookup_value, table_array, row_index_num, range_lookup)
Lookup_value is the required parameter: the data table to find the first row of the values, which can be a numeric value, a reference or text string; Table_array is you need to find data in a datasheet, you can use on the region or area name reference to the first row of Table_array value can be text, numbers, or logical values. Row_index_num is to be returned in table_array match value of the row number. Range_lookup is a logical value that indicates the HLOOKUP function is an exact match lookup, or an approximate match.
Example: If A1: B3 regions hold 34, 23, 68, 69, 92, 36, the formula "= HLOOKUP (34, A1: B3, 1, FALSE) returns 34; = HLOOKUP (3, {1, 2, 3;" a"，"b"，"c";" D "," e "," f "}, 2,TRUE) returns" c ".
7.HYPERLINK
Purpose: create a shortcut to open the storage on a network server, Intranet (Internet) or other file on the local hard disk.
Syntax:HYPERLINK(link_location，friendly_name)
Parameters: Link_location is the path and file name of the file, it can also point to the document of a more specific location, such as Execl sheet or workbook to a specific cell or named range, or point to a bookmark in the Word document. Path can be stored in a file on your hard drive, or on the Internet or an intranet URL path; Friendly_name is cell displayed link text or numbers that appear in blue and underlined. If you omit the Friendly_name, the cell will display as a link. link_location
Example: HYPERLINK ("http://www.mydrivers.com/", "drive home") will display the text in the worksheet "drive home", click it to connect to the "http://www.mydrivers.com/". The formula "= HYPERLINK (" D:\README.TXT "," documentation ")," on the worksheet, the establishment of a blue "documentation" link, click it to open D Readme.txt file on the disc.
8.INDEX
Purpose: return a table or range of values or reference that. Function INDEX () come in two forms: array and reference. The array form always returns a value or an array of values; the reference form always returns a reference.
Syntax: INDEX (array, row_num, column_num) returns an array of the specified cell or range of values of the array. INDEX (row_num, column_num, reference, and area_num) returns the reference in the specified cell or cell range reference.
Parameters: Array as a cell range or an array constant; one for the array Row_num row number, the function returns the value from the row. If you omit the row_num, column_num must have; Column_num is an array of a column in the column ordinal, function returns a numeric value from a column. If omitted, column_num, you must have the row_num. Reference to one or more reference to a range of cells, if a reference type a discontinuous selection, must be enclosed in parentheses. Area_num is to select an area in the reference, and returns the row_num and column_num in the area of cross-regional. Select or enter the serial number of the first area is 1, the second is 2, and so on. If you omit area_num, INDEX function uses the region 1.
Example: If A1 = A2 = 68, 96, A3 = 90, then the formula "= INDEX (A1: A3, 1, 1)" returns 68 = INDEX (A1: A3, 1, 1, 1) returns 68.
9.INDIRECT
Purpose: return a text string of the specified reference. This function is evaluated immediately to reference and display its contents. When you need to change the formula in the cell reference, without changing the formula itself, you can use the INDIRECT function.
Syntax: INDIRECT (ref_text, a1).
Parameters: Ref_text is a reference to the cell, the cell can contain an a1-style references, references to R1C1 style, defined as the name of the reference or text string cell A1 is a logical; value specified is contained in cell ref_text reference type in. If a1 is true or omitted, ref_text is interpreted as A1-style references. If a1 is false, ref_text is interpreted as R1C1-style references.
Example: If there is a deposited cell a1, and B1 B1 text cells store the numeric 68.75, the formula "= INDIRECT ($ A $ 1)" returns 68.75.
10.LOOKUP
Purpose: return a vector (-row or one-column range) or values in the array. This function has two syntax forms: vector and array, its vector form is in-row or one-column range (vector) to find the value, and then returns the second-row or one-column range in the same location; its array in an array of the first row or column to find the specified value and returns an array of the last row or column in the same location.
Syntax 1 (vector format): LOOKUP (lookup_value, the lookup_vector and result_vector)
Syntax 2 (array): LOOKUP (lookup_value, array).
Parameter 1 (vector format): a function LOOKUP Lookup_value in the first vector to find the number. Lookup_value can be numbers, text, logical values, or values of the name or reference. Lookup_vector to only contain one row or column area. The numeric value for the Lookup_vector can be text, numbers, or logical values.
Parameter 2 (array): function LOOKUP Lookup_value is in an array of values that you want to find. Lookup_value can be numbers, text, logical values, or values of the name or reference. If LOOKUP can't find the lookup_value, it uses the array is less than or equal to the maximum value of lookup_value. Array to contain text, numbers, or logical valuesThe range of cells, its value is used to compare with lookup_value.
Note: the numerical Lookup_vector must be in ascending order, otherwise, the LOOKUP function does not return the correct results, the text in a parameter are not case sensitive.
Example: If A1 = A2 = 68, 76, 85, A3 = A4 = 90, then the formula "= LOOKUP (76, A1: A4)" returns 2 = LOOKUP ("bump", {"a", "1; b"，2;" C ", 3}) returns 2.
Lookup and reference functions (2)

11.MATCH
Purpose: Returns the specified mode and specify the values match the corresponding elements in the array. If you need to find out the location of the matched element rather than matching the element itself, you should use the MATCH function.
Syntax: MATCH (lookup_value, lookup_array, match_type).
Parameters: Lookup_value is needed in the data table to find the value, it can be a number (or numbers, text, or logical value), on numbers, text, or logical values, cell references. Lookup_array is likely to contain the values you want to find contiguous range of cells, Lookup_array can be an array or an array reference; Match_type is a digital-to-1, 0 or 1, it shows how Excel find lookup_value in lookup_array. If match_type is 1, function MATCH find lookup_value is smaller than or equal to the maximum value. If match_type is 0, the function MATCH find equal to lookup_value in the first numeric value. If match_type is-1, function MATCH lookup is greater than or equal to the minimum value of lookup_value.
Note: the MATCH function to return the target values in the lookup_array, not the value itself. If match_type is 0 and lookup_value is text, the lookup_value can contain wildcards ("*" and "?"). The asterisk matches any sequence of characters, question mark can match a single character.
Example: If A1 = A2 = 68, 76, 85, A3 = A4 = 90, then the formula "= MATCH (90, A1: A5, 0)" returns 3.
12.OFFSET
Purpose: to specify the reference frames of reference is through a given offset is the new reference. Returns a reference to a cell or range of cells, and you can specify a return number of rows or columns.
Syntax: OFFSET (reference, rows, cols, height, width).
Parameters: the Reference is used as a reference offset frame of reference, it must be a cell or contiguous range of cells, Rows of references is relative to the offset of the reference frame of the upper-left cell, (ii) offset by the number of rows. If you use the 5 Rows as a parameter, then the target reference area of the upper-left cell of the row than low 5 reference. Number of rows that can be positive (represents just below the starting reference) or negative (on behalf of the above the starting reference); Cols offset is relative to the reference frame of the upper-left cell, and left (right) offset by the number of columns. If you use 5 Cols as a parameter, then the target reference area of the upper-left cell on the right than the 5 column reference. The number of columns can be positive (to the right of the starting reference) or negative (to the left of the starting reference); Height is a reference to the region you want to return the number of rows in Height must be positive; the Width is to return the number of columns in the reference zone, Width must be positive.
Example: If A1 = A2 = 68, 76, 85, A3 = A4 = 90, then the formula "= SUM (OFFSET (A1: A2, 2, 0, 2, 1)) returns" 177.
13.ROW
Purpose: Returns the line number of the given reference.r>Syntax: ROW (reference).
Reference to which you want to know the line number of the cell or range of cells.
Example: the formula "= ROW (A6)" returns 6, if you enter the formula in cell C5 "= ROW ()," the result is 5.
14.ROWS
Purpose: Returns a reference to the number of rows or arrays.
Syntax: ROWS (array).
Parameters: Array is the number for which you want to know the row of the array, the array formula or reference to a range of cells.
Example: the formula "= ROWS (A1: A9)" returns 9, = ROWS ({1, 2, 3, 4, 5, 6; 1, 2, 3}) returns 3.
15.RTD
Application: from supporting COM automation program to return to the live data.
Syntax: RTD (ProgID, server, topic1, [topic2], ...)
Parameters: ProgID is installed on the local computer, after registration of COM automation add-ins for the ProgID name, the name in quotation marks. Server is to run the add-in for the name of the server. If you do not have a server that is running on the local computer, then this parameter is blank. topic1，topic2，... 1 to 28 parameters that together represent a unique real-time data.
16.TRANSPOSEBR > purpose: Returns the transpose of the region (the so-called transpose is the first row of the array as a new array of the first column, second row of the array as a new array of the second column, and so on).
Syntax: TRANSPOSE (array).
Parameters: Array is the need to transpose of an array or a worksheet cell.
Example: If A1 = A2 = 68, 76, 85, B1 = B2 = 90, then the formula "{= TRANSPOSE (A1: B1)}" returned C1 = D1 = 56, 98, C2 = 90, D2 = 87.
17.VLOOKUP
Use: table or an array of the first column to find the specified value, and then returns a table or an array of the current row in the column of values. When comparing values in the first column in the datasheet, you can use the VLOOKUP function instead of the HLOOKUP function.
Syntax: VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)
Parameters: Lookup_value is needed in the data table to find the first column value, which can be a numeric value, a reference or text string. Table_array is you need to find data in a datasheet, you can use on the region or area name references. Col_index_num is pending in table_array returns the matching value in the column ordinal. Col_index_num value of 1 returns the first column in table_array; a col_index_num of 2 returns the second column in table_array, and so on. Range_lookup is a logical value that indicates when the VLOOKUP function returns is an exact match or an approximate match. If true or omitted, it returns the approximate matching values, that is, if an exact match is not found, the return value is less than lookup_value maximum value; if false, the function range_value VLOOKUP will return exact matches. If not found, returns the # n/a error value.
Example: If A1 = A2 = 23, 45, 50, A3 = A4 = 65, the formula "= VLOOKUP (50, A1: A4, 1, TRUE) returns 50"