191 index
Mnemonic: The "index" in English.
Categories: Find and Reference
The function has two different forms, array and reference, which can be considered to be two forms sharing a function name, and its parameters and usage are different, so it should be said separately.
Array form
grammar:
index(array,row_num,[column_num])
Parameters: 2 to 3 parameters
array required. A range of cells. Of course, if the content is relatively fixed and the rows and columns are relatively small, you can directly use the constant array, put in curly braces to distinguish the rows and columns with commas and semicolons, for example, {1,2; 3,4; 5,6} is an array of constants of three rows and two columns.
row_num Required. Specifies the number of rows in the array.
column_num Optional. Specifies the number of columns in the array.
usage:
Returns the array elements specified by the index value consisting of the number of rows and columns in the array. Excel help to write badly, there are several instructions in it, just look confused, the following through the example to explain one by one.
First of all, we can think of the array as a student square team arranged on the playground, and find out the students who need it through the command.
1) Look at the situation of the block team first. 4 classmates codenamed tiger, bug, stick, chicken (with a relationship between each other) stand in 2 rows and 2 columns of blocks, you can choose the A1:B2 area, enter "square" in the name box (it is more convenient after naming, directly use the reference to the cell range, but be rememberable). Then we enter "=index" in cell F6 to show the "chicken" at the intersection of row 2 and column 1. (Article 1: Use both row_num and column_num to return the value at the intersection.) ）
Block team
2) Change the first parameter to 0, and the carriage return shows an incorrect value. (Article 2 states that if the row_num or column_num is set to 0, the array values of the entire column or row are returned, respectively.) Enter the array formula to press Ctrl+Shift+Enter at the same time). We select the F6 cell and press F2 to enter the editing state, press the key combination to display "Bugs". There was only 1 person, and that was because we didn't leave enough room for the queue beforehand, like only one stool.
The parameter is 0 to use an array formula
3) Now select F6:F8 (prepare 3 stools), press F2, press the key combination, as shown in the 2nd column of the students are all seated, there is still 1 stool left. Change the 3rd parameter to 1 to see, the main thing is to select the F6:F8 area first, press F2, modify the parameters, press the key combination, this time the tiger and the stick in the 1st column are seated.
The second column is elected
4) Change the 3rd parameter to 0 to see, now first select the F6:H8 area, press F2, modify the parameters, press the key combination, and all sit.
All sit down
5) Let the starter shout 0 lines or 0 columns always feel strange, in reality we directly shout the first row, the second column, the whole. Take a closer look at the description of the argument array: If the array has multiple rows or columns, but only uses row_num or column_num, the function returns an array of entire rows or columns in the array. Let's choose a larger area, omit both parameters, see if it's all, don't have to write 0, so write 0 is not clear.
Select All (equivalent to a 2D plane)
6) Now let's go back and look at it, 2 parameters determine a cell, equivalent to a point; 1 parameter determines a one-dimensional array, equivalent to a line; no parameter determines the entire array, equivalent to a polygon. When we select a large area, we fill the full selection with all the above two parameters omitted. Fill with lines when there is only 1 parameter or omit 1 parameter, and points when there are 2 parameters. As shown in the following two figures
Single column (equivalent to a one-dimensional line)
Cell (equivalent to a dot)
7) row_num and column_num for one-dimensional arrays (only 1 row or 1 column), this parameter is automatically recognized as a meaningful number of rows or columns. For example, the formula for cell A6 in the image above is "=index(A1:A4,2)" and the formula for cell F1 is "=index(A1:D1,2)". For F1 cells the normal syntax is "=index(A1:D1,,2)", but because there is only one row, the argument is automatically recognized as a column number.
8) Article 3 states: row_num and column_num must point to a cell in the array; otherwise, the function index returns the error value #REF!.
(To be continued)