Excel：从单一输入获取范围地址（寻找紧凑公式）

Question

我正在尝试通过单个输入获取绝对范围地址。类似这样。

Formula
=SOME.MAGIC(A1:B10)

Output (string)
"$A$1:$B$10"

尝试 1

我能够得到这个结果，但使用以下（数组）公式的相当复杂的方法。

{=ADDRESS(MIN(ROW(A1:B10)),MIN(COLUMN(A1:B10)),1,1)&":"&ADDRESS(MAX(ROW(A1:B10)),MAX(COLUMN(A1:B10)),1,1)}

其中（如您所见）字符串A1:B10使用了四次。

第二次尝试

我的另一个尝试是使用函数FORMULATEXT。 范围的链接仅存在一次，但使用一个单元格是一种令人讨厌的解决方法，并且仅适用于相对地址，它不适用于命名范围。

C1=$A$1:$B$10
C2=MID(FORMULATEXT(C1),2,LEN(FORMULATEXT(C1))-1) //Returns string "$A$1:$B$10" ✓

But
C1=A1:B10
C2=MID(FORMULATEXT(C1),2,LEN(FORMULATEXT(C1))-1) //Returns string "A1:B10" ✘

And
C1=Named_range
C2=MID(FORMULATEXT(C1),2,LEN(FORMULATEXT(C1))-1) //Returns string "Named_range" ✘

第三次尝试

下一步是尝试 1+2 的组合。它成功了，但我并不满意。

C1=A1:B10
C2=MID(FORMULATEXT(C1),2,LEN(FORMULATEXT(C1))-1)
C3{=ADDRESS(MIN(ROW(INDIRECT(C2))),MIN(COLUMN(INDIRECT(C2))),1,1)&":"&ADDRESS(MAX(ROW(INDIRECT(C2))),MAX(COLUMN(INDIRECT(C2))),1,1)}

有没有更简单的方法（不太复杂的公式）且输出相同的结果？

Answer 1

这可能是另一种方式：

=CELL("address",A1:B10)&":"&CELL("address",INDEX(A1:B10,ROWS(A1:B10),COLUMNS(A1:B10)))

如果你有 Office 365，请使用 LET 仅引用一次范围

=LET(ref, A1:B10, CELL("address",ref)&":"&CELL("address",INDEX(ref,ROWS(ref),COLUMNS(ref)))

Answer 2

考虑用户定义的函数：

Option Explicit

Public Function adres(rng As Range) As String
    adres = rng.Address(True, True)
End Function

用户定义函数 (UDF) 非常容易安装并使用：

1. ALT-F11 打开 VBE 窗口
2. ALT-I ALT-M 打开新模块
3. 粘贴内容并关闭 VBE 窗口

如果您保存工作簿，UDF 将随之保存。如果您使用的是 2003 之后的 Excel 版本，则必须将文件保存为 .xlsm 而不是 .xlsx

到消除UDF：

1. 调出如上所示的 VBE 窗口
2. 清除代码
3. 关闭 VBE 窗口

到使用Excel 单元格中的 UDF：

=adres(A1:B10)

要了解有关宏的更多信息，请参阅：

http://www.mvps.org/dmcritchie/excel/getstarted.htm

和

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

有关 UDF 的详细信息，请参阅：

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

必须启用宏才能使其工作！

Answer 3

实际上，您的尝试 #1 正是这样做的方法。如果您正在寻找一种方法使其看起来更像一个函数（引用的重复较少/“参数”地址的单一使用），最好的方法是在 Office 365 中使用 LAMBDA。使用 LAMDA，您可以为命名范围提供参数，将 MAGIC 定义为

=LAMBDA(ref, ADDRESS(MIN(ROW(ref)),MIN(COLUMN(ref)),1,1)&":"&ADDRESS(MAX(ROW(ref)),MAX(COLUMN(ref)),1,1)}

然后在工作表中使用 =MAGIC(A1:B10)。