How to use the regiona parameter in Excel

The rank function is a rank function. The rank function is most commonly used to find the rank of a certain value in a certain area. Here's an example of how to use the excel rank function.

Step Method.

The syntax of the rank function is as follows: rank(number,ref,[order])

In the parameter after the function name, number is the number or cell name that needs to be ranked, ref is the reference value area of the ranking, and the order is 0 and 1, and the default does not need to be entered, what you get is the ranking from large to small, if you want to find the penultimate number, please use 1 for the value of order.

Here are a few examples of the rank function:

Example 1: Positive ranking.

In this example, we find the ranking of the value 20 in the a1:a5 region in cell b2, we do not enter the order parameter, and the default order value is 0, that is, sort from high to low. In this example, 20 is in a1:

The positive sort in the a5 area is 1, so the result displayed is 1.

Example 2: Backward ranking.

In this example, in the example above, we enter the order value as 1, and we find that the result is very different, because the order value is 1, which means that the reciprocal rank is calculated, and the reciprocal rank of 20 in the a1:a5 region is 4.

Example 3: Find the ranking of a column of numbers.

In practical applications, we often need to find the ranking of the values in a certain column, for example, we find the respective ranking of the data in cells A1 to A5. We can use the method of cell reference to rank: =rank(a1,a1:

A5), this formula is to find the ranking of A1 cells in A1:A5 cells, when we use the autofill tool to drag the data, we found that the result is not right, and after careful study, we found that the formula of A2 cells actually became =rank(A2,A2:A6) This exceeded our expectations, and the area of the data we compared was A1:

a5, it can't be changed, so we need to lock the formula a1:a2 in the formula with the $ sign, so the formula for cell a1 becomes =rank(a1,a$1:a$5).

Tools Materials: Computer, UG software.

There are many ways to do this, and here's how:

The a1 style means to return the value of a15.

The r1c1 style means to return the value of a15.

function, =index(a:a,c1)=index(a:a,15), which means that the value of line A15 is returned.

function, =offset(a1,c1-1,0)=offset(a1,14,0), which means that the value of a15 is returned.

1. First of all, let's open a ** to calculate the salary of the A parts part that each person does.

2. At this time, we enter "=$b$2*b4" in cell C4. The $ symbol inserted in b2 means an absolute reference, which means that no matter how the cell position changes later, b2 will be our fixed reference.

3. Drag the cross to C8 in the lower right corner of cell C4, so that the following B C also comes out.

4. At this time, we select the C7 cell, and we can see that its formula is "=$b$2*B7", only B7 has changed and the unit price B2 has not changed. That's the right absolute quote.

There are many ways to do this.

1. Use the A1 style of indirect, as shown in the following figure =indirect("a"&c1)

2. Use the r1c1 style of indirect, as shown in the following figure =indirect("r"&c1&"c1"3. Use the index function, as shown in the following figure.

index(a:a,c1)

4. Use the offset function, as shown in the following figure.

offset(a1,c1-1,0)

Formula analysis: Suppose c1=15

One represents the value of cell A15 returned.

2 indicates the value of the first column of row 15 (i.e., a15), and when the indirect function uses the r1c1 style, the second parameter is 0 and cannot be omitted.

3. =index(a:a,c1)=index(a:a,15), which indicates the value of row 15 of column a.

4. =offset(a1,c1-1,0)=offset(a1,14,0), which means the value of the cell (i.e., a15) that returns the a1 cell that is offset by 14 rows downward and column 0 to the right.

It depends on your specific needs! For example, if a number in column A is equal to C1, then the number in column A will be displayed in column B.

Then enter b1: =if(a1=$c$1,a1,"")

Then copy the cell down!

Upstairs is right, but it doesn't seem to be written correctly, if you use a1 instead of 4, reference:

Are you referring to d4:d18 4 with a value inside a cell?

You can change it to indirect("d"&a1):d18

If cell A1 is 5, the result is D5:D18

a2:a(2+n) is not right to express it like this, it expresses the text in this way, not a cell reference, and needs to be converted with indirect. Again, what's your n? How does it change?

Look at the above first**.

You can fill in any region in column H, as long as there is such an area in column A, and it is not necessary to fill in the order of column A.

J2] The formula for the cell is as follows:

if(or(h2="",i2=""),"",if(i2<=1,vlookup(h2,a:f,2,0),if(i2<=3,vlookup(h2,a:f,3,0),if(i2<=5,vlookup(h2,a:

f,4,0),if(i2<=8,vlookup(h2,a:f,5,0),if(i2<=30,vlookup(h2,a:f,6,0),"")))

The above formula means:

If any cell of H2 or I2 is empty, the returned result is empty.

If i2<=1, then find the position of H1 in column A, and return the rightward count of the data in cell 2 (i.e. column B).

If i2<=3, then find the position of H1 in column A, and return the rightward count of the data in the 3rd cell (i.e. column C).

If i2<=5, then find the position of H1 in column A, and return the rightward count of the data in cell 4 (i.e., column D).

If I2<=8, then find the position of H1 in column A, and return the rightward count of the data in cell 5 (i.e., column E).

If i2<=30, then find the position of H1 in column A, and return the rightward count of the data in cell 6 (i.e., column F).

Otherwise, a null value is returned, for example, when i2 is greater than 30.

Let's assume that the name of the first chart is sheet1

j2=vlookup(h2,sheet1!$a:$f,if(and(i2>0,i2<=1),2,if(and(i2>1,i2<=3),3,if(and(i2>3,i2<=5),4,if(and(i2>5,i2<=8),5,if(and(i2>8,i2<=30),6)))0)

Formula drop-down filling.

You have an intersection in the range, where is the intersection counted, take the problem as the content (the subject of the email must contain "excel", I use this as a basis to identify non-spam, so as not to delete it by mistake), and the excel sample file (sample data, non-actual data, showing the current situation and target effect) as an attachment to see it.

There is a problem with your data, explain first, 5 is in column d and e, and it cannot be matched.

Absolutely, for example:

i=10range("a" & i)=123

Another example: x ="a10"

msgbox range(x)

Yes, for example, the argument n can be written as range("a"&i),"a"Column A is denoted and the n parameter is denoted by the number of rows.

The main function of the address function is to be able to return the address of the cell, the syntax structure of the address function is shown in the following figure, this function has 5 parameters, the first parameter row num: line number.

The second parameter column num: the column number.

The third parameter, abs num: the reference type.

There are 4 types of references: 1: absolute reference, if the third parameter is omitted, the default is 12: absolute row number, relative column number.

3: Relative row number, absolute column number.

4: Relative references.

The fourth parameter a1: Citation method.

There are two ways to cite it:

true: returns the A1 style, if the fourth parameter is omitted, the default value is the A1 style (a is a column, 1 is a row).

false returns: r1c1 style (r1 is row, c1 is column) The fifth parameter sheet text: indicates which worksheet in the workbook, that is, the worksheets in different workbooks.

address(row_num,column_num,[abs_num],[a1],[sheet_text])

The address function syntax has the following parameters:

row num required. A numeric value that specifies the line number to be used in the cell reference.

column num required. A numeric value that specifies the column number to be used in the cell reference.

abs num optional. A numeric value that specifies the type of reference to be returned.

A1 Optional. A logical value that specifies the a1 or r1c1 reference style. In the A1 style, columns and rows will be labeled alphabetically and numerically, respectively.

In the R1C1 reference style, both columns and rows are labeled in numerical order. If the parameter a1 is true or omitted, the address function returns an a1 style reference; If false, the address function returns an r1c1 style reference.

sheet text is optional. A text value that specifies the name of the worksheet to be used as an external reference. For example, the formula =address(1,1,,,"sheet2") returns sheet2!

a$1。If you ignore the parameter sheet text, no sheet name is used, and the address returned by the function refers to the cell on the current sheet.

For example: =address(2,3,1,false,"[book1]sheet1")

Absolute cell reference to another workbook and worksheet, the result is as follows:

book1]sheet1!r2c3

address (row number, column number, reference type).

Reference type parameter:

1 or omitted.

Absolutely quoted. 2 Absolute line numbers, relative to column labels.

3 Relative line number, absolute column marking.

4: Relative references.