工作表函数中的单元格、列和行的自引用

In a worksheet function in Excel, how do you self-reference the cell, column or row you're in?

在Excel中的工作表函数中，如何自引用所在的单元格、列或行?

Note that this is extremely useful for conditional formatting.

注意，这对于条件格式非常有用。

9 个解决方案

#1

where F13 is the cell you need to reference:

F13是需要参考的单元格:

=CELL("Row",F13)  yields 13; its row number

=CELL("Col",F13)  yields 6; its column number;  

=SUBSTITUTE(ADDRESS(1,COLUMN(F13)*1,4),"1","") yields F; its column letter

#2

For a cell to self-reference itself:

细胞自我引用:

INDIRECT(ADDRESS(ROW(), COLUMN()))

For a cell to self-reference its column:

一个单元格自我引用它的列:

INDIRECT(ADDRESS(1,COLUMN()) & ":" & ADDRESS(65536, COLUMN()))

For a cell to self-reference its row:

对于一个单元格自引用它的行:

INDIRECT(ADDRESS(ROW(),1) & ":" & ADDRESS(ROW(),256))
or
INDIRECT("A" & ROW() & ":IV" & ROW())

The numbers are for 2003 and earlier, use column:XFD and row:1048576 for 2007+.

这些数字是2003年和更早的，使用列:XFD和行:1048576表示2007年+。

Note: The INDIRECT function is volatile and should only be used when needed.

注意:间接功能是不稳定的，只有在需要的时候才使用。

#3

I don't see the need for Indirect, especially for conditional formatting.

我不认为需要使用间接格式，尤其是条件格式。

The simplest way to self-reference a cell, row or column is to refer to it normally, e.g., "=A1" in cell A1, and make the reference partly or completely relative. For example, in a conditional formatting formula for checking whether there's a value in the first column of various cells' rows, enter the following with A1 highlighted and copy as necessary. The conditional formatting will always refer to column A for the row of each cell:

自引用单元格、行或列的最简单方法是通常引用它，例如，单元格A1中的“=A1”，并使引用部分或完全相对。例如，在一个条件格式的公式中，检查各种单元格的第一列中是否有一个值，在需要的时候，输入下面的A1高亮显示和复制。条件格式将始终指向每行单元格的A列:

= $A1 <> ""

#4

For a non-volatile solution, how about for 2007+:

对于非易失性的解决方案，2007年+:

for cell    =INDEX($A$1:$XFC$1048576,ROW(),COLUMN())
for column  =INDEX($A$1:$XFC$1048576,0,COLUMN())
for row     =INDEX($A$1:$XFC$1048576,ROW(),0)

I have weird bug on Excel 2010 where it won't accept the very last row or column for these formula (row 1048576 & column XFD), so you may need to reference these one short. Not sure if that's the same for any other versions so appreciate feedback and edit.

我在excel2010上有一个奇怪的错误，它不接受这些公式的最后一行或列(第1048576行和第XFD列)，因此您可能需要简短地引用这一行。不知道其他版本是否相同，所以请欣赏反馈和编辑。

and for 2003 (INDEX became non-volatile in '97):

2003年(97年指数变得不波动):

for cell    =INDEX($A$1:$IV$65536,ROW(),COLUMN())
for column  =INDEX($A$1:$IV$65536,0,COLUMN())
for row     =INDEX($A$1:$IV$65536,ROW(),0)

#5

There is a better way that is safer and will not slow down your application. How Excel is set up, a cell can have either a value or a formula; the formula can not refer to its own cell. Otherwise, You end up with an infinite loop, since the new value would cause another calculation... .

有一种更好的方法更安全，不会减慢应用程序的速度。如何设置Excel，单元格可以有值也可以有公式;公式不能引用它自己的单元格。否则，您将得到一个无限循环，因为新的值将导致另一个计算…。

Use a helper column to calculate the value based on what you put in the other cell.

使用helper列根据您在其他单元格中输入的内容计算值。

For Example:

例如:

Column A is a True or False, Column B contains a monetary value, Column C contains the following formula:

A列为真或假，B列为货币值，C列为:

=B1

Now, to calculate that column B will be highlighted yellow in a conditional format only if Column A is True and Column B is greater than Zero...

现在，要计算B列，只有当a列为真，B列大于0时，才会以条件格式突出显示为黄色……

=AND(A1=True,C1>0)

You can then choose to hide column C

然后可以选择隐藏列C

#6

In a VBA worksheet function UDF you use Application.Caller to get the range of cell(s) that contain the formula that called the UDF.

在VBA工作表函数UDF中，您使用应用程序。调用者获取包含称为UDF的公式的单元格范围。

#7

My current Column is calculated by:

我的当前列的计算方法为:

Method 1:

方法1:

=LEFT(ADDRESS(ROW(),COLUMN(),4,1),LEN(ADDRESS(ROW(),COLUMN(),4,1))-LEN(ROW()))

=左(地址(行(),列()4 1),LEN(地址(行(),列()4 1))LEN(行()))

Method 2:

方法2:

=LEFT(ADDRESS(ROW(),COLUMN(),4,1),INT((COLUMN()-1)/26)+1)

=左(地址(行(),列()4 1),INT((第1列())/ 26)+ 1)

My current Row is calculated by:

我当前的行由:

=RIGHT(ADDRESS(ROW(),COLUMN(),4,1),LEN(ROW()))

=正确(地址(行(),列()4 1),LEN(行()))

so an indirect link to Sheet2!My Column but a different row, specified in Column A on my row is:

间接链接到Sheet2!我的列但是另一行，在我的行上的a列中指定为:

Method 1:

方法1:

=INDIRECT("Sheet2!"&LEFT(ADDRESS(ROW(),COLUMN(),4,1),LEN(ADDRESS(ROW(),COLUMN(),4,1))-LEN(ROW()))&INDIRECT(ADDRESS(ROW(),1,4,1)))

=间接(“Sheet2 !”左(地址(行(),列()4 1),LEN(地址(行(),列()4 1))LEN(行()))间接(地址(行(),1 4 1)))

Method 2:

方法2:

=INDIRECT("Sheet2!"&LEFT(ADDRESS(ROW(),COLUMN(),4,1),INT((COLUMN()-1)/26)+1)&INDIRECT(ADDRESS(ROW(),1,4,1)))

=间接(“Sheet2 !”左(地址(行(),列()4 1),INT((第1列())/ 26)+ 1)间接(地址(行(),1 4 1)))

So if A6=3 and my row is 6 and my Col is C returns contents of "Sheet2!C3"

如果A6=3，行为6,Col为C则返回"Sheet2!C3"

So if A7=1 and my row is 7 and my Col is D returns contents of "Sheet2!D1"

如果A7=1，我的行是7，而Col是D返回"Sheet2!D1"

#8

I was looking for a solution to this and used the indirect one found on this page initially, but I found it quite long and clunky for what I was trying to do. After a bit of research, I found a more elegant solution (to my problem) using R1C1 notation - I think you can't mix different notation styles without using VBA though.

我在寻找一种解决方法，并使用了这个页面上最初找到的间接方法，但我发现它对我正在尝试做的事情来说非常冗长和笨拙。经过一些研究，我发现了一个更优雅的解决方案(针对我的问题)使用R1C1表示法——我认为如果不使用VBA，就不能混合不同的表示法风格。

Depending on what you're trying to do with the self referenced cell, something like this example should get a cell to reference itself where the cell is F13:

根据你想对自我引用单元格做什么，类似这个例子应该让一个单元格在F13中引用自己:

Range("F13").FormulaR1C1 = "RC"

And you can then reference cells in relative positions to that cell such as - where your cell is F13 and you need to reference G12 from it.

然后你可以将相对位置的单元格引用到单元格中比如-你的单元格是F13你需要从它中引用G12。

Range("F13").FormulaR1C1 = "R[-1]C[1]"

You're essentially telling Excel to find F13 and then move down 1 row and up one column from that.

你实际上是在告诉Excel找到F13然后向下移动一行，向上移动一列。

How this fit into my project was to apply a vlookup across a range where the lookup value was relative to each cell in the range without having to specify each lookup cell separately:

我的项目就是将vlookup应用到一个范围内，在这个范围内查找值相对于该范围内的每个单元，而不必分别指定每个查找单元:

Sub Code()
    Dim Range1 As Range
    Set Range1 = Range("B18:B23")
        Range1.Locked = False
        Range1.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1],DATABYCODE,2,FALSE),"""")"
        Range1.Locked = True
End Sub

My lookup value is the cell to the left of each cell (column -1) in my DIM'd range and DATABYCODE is the named range I'm looking up against.

我的查找值是每个单元格左边的单元格(列-1)，并且DATABYCODE是我要查找的命名范围。

Hope that makes a little sense? Thought it was worth throwing into the mix as another way to approach the problem.

希望这有点道理?我认为把它作为解决这个问题的另一种方法是值得一试的。

#9

Just for row, but try referencing a cell just below the selected cell and subtracting one from row.

只用于行，但请尝试引用位于选定单元格下面的单元格，并从行中减去一个单元格。

=ROW(A2)-1

Yields the Row of cell A1 (This formula would go in cell A1.

生成单元格A1的行(这个公式在单元格A1中。

This avoids all the indirect() and index() use but still works.

这避免了所有间接()和索引()的使用，但仍然有效。