![通过公式在 Excel 中创建对整列的引用](https://linux22.com/image/1424991/%E9%80%9A%E8%BF%87%E5%85%AC%E5%BC%8F%E5%9C%A8%20Excel%20%E4%B8%AD%E5%88%9B%E5%BB%BA%E5%AF%B9%E6%95%B4%E5%88%97%E7%9A%84%E5%BC%95%E7%94%A8.png)
我有一个电子表格,需要引用整列。问题是我事先不知道是哪一列,所以我使用MATCH
函数来找出它。找到正确的列后,我需要引用其中的单元格。
有两个问题阻止我使用诸如CHOOSE
函数之类的简单方法:
- 我有几百个可以引用的列
- 我需要简单地引用整个列,因为行数也在变化
最初我考虑使用MATCH
、ADDRESS
、INDIRECT
,但ADDRESS
需要我指定行。然后我想可以使用MATCH
和CODE
,但我的包含多个字母的列的公式有问题。
我该如何实现这个目标?(根据查找/匹配引用整个列。)
答案1
为了稍微澄清一下 Mureinik 的回答,因为这是一个很好的开始,对于 MyColumn 列,您可以使用:OFFSET($A$1,0,MyColumn-1,ROWS($A:$A),1)
从 $A$1 开始,然后偏移 0 行和(您的列号减 1),使用 ROWS($A:$A) 的高度和 1 的宽度。最新版本中的最大行数为 1,048,576。如果您知道最大高度可能是多少,则可以使用较小的高度。
因此对第 3 列求和:
=SUM(OFFSET($A$1,0,3-1,ROWS($A:$A),1))
sgp667 的答案还不错,但请注意,如果经常使用 SUBSTITUTE,它可能会有点混乱和缓慢。
要引用另一张表,只需将引用更改为 $A$1,例如:SUM(OFFSET(Sheet2!$A$1,0,3-1,ROWS($A:$A),1))
答案2
列中的单元格数是一个常数,自 Excel 2007 以来为 1048576,如果我没记错的话,在早期版本中为 65535。如果您打开xls
(旧格式)文件,它会恢复到旧的行数,因此您可能需要考虑到这一点。
1
因此,您可以引用以行开头并以行结尾104576
的范围A1:A1048576
,但使用您使用的任何公式。
但是,引用整个列可能会使电子表格非常慢。通常,计数行数(使用COUNTROWS
once 或其他任何方法)并准确引用范围是一个好主意。
答案3
抱歉,我提问的方式有些模糊。
我找到了答案,尽管这里有一个将数字转换为列引用的简单公式:substitute(address(1,YOURNUMBER),"$1","")
正如我之前所说,我正在使用match
来查找我的列号,因此YOURNUMBER
我使用比赛的结果。
答案4
您可以使用索引和匹配
=INDEX(Array,0,MATCH(Row,Cell,0))
您将匹配行内的单元格以找到列号,然后使用索引创建一个包含 0 行和匹配结果的列号的数组,最终得到所需的列名。