Excel:如何在不使用 COLUMN() 和 INDIRECT() 的情况下将列字母转换为相应的列号?

Excel:如何在不使用 COLUMN() 和 INDIRECT() 的情况下将列字母转换为相应的列号?

标准解决方案似乎是,=COLUMN(INDIRECT(letter&"1"))但应该有一个更好的解决方案,可以避免易失性INDIRECT函数。

样本专栏 对应列号
A 1
C 3
二十六
AA 二十七
Z Z 702

有一个公式 将数字转换为列字母但反之则不然。

最好采用能够与不同的电子表格软件兼容的解决方案。


建议的解决方案比较(样本表):

答案1

无需零填充或 Excel 365 公式(如 SEQUENCE)的替代答案 - 适用于 Excel、Libre Calc 和 Google Sheets

=SUMPRODUCT((CODE(MID(A1,CHOOSE(LEN(A1),{1},{1,2},{1,2,3}),1))-CODE("A")+1),CHOOSE(LEN(A1),{1},{26,1},{676,26,1}))

注意:这是一个数组公式。在 Excel 365 之前的版本中,您可能必须使用 CTRL+SHIFT+ENTER (CSE) 确认公式(但可能不需要,因为 SUMPRODUCT 可以识别数组并生成标量值)。

答案2

单字母列的基本解决方案,例如 A 到 Z

=CODE(A1)-CODE("A")+1

其中 A1 包含一个大写字母,代表 A 和 Z 之间的列引用。

多字母列的基本解决方案,例如 A、AA 或 AAA

对于多字母列,我们需要更聪明一些。首先在 B1(辅助列)中,我们用“零”填充,以便处理一个、两个和三个字母的列:

=RIGHT(REPT(CHAR(CODE("A")-1),3)&A1, 3)

这将 A 和 AA 之类的引用扩展为三个字符引用 @@A 和 @A。“@”字符的字符代码为 64(“A”下一位),因此相当于列号为 0。

现在我们取三个字符引用中的每个字符,减去“A”的字母偏移量,然后分别乘以 26^2、26^1 和 26^0 以获得 C1 中的列号:

=(CODE(LEFT(B1,1))-CODE("A")+1)*26*26 + (CODE(MID(B1,2,1))-CODE("A")+1)*26 + (CODE(RIGHT(B1,1))-CODE("A")+1)

在 Excel 365 中,您可以使用 LET 取消辅助列 (B),或者使用下面更快的数组公式。

多字母引用的快速数组公式解决方案

数组版本将三个字符放入一个数组中,减去“A”字符代码,然后乘以列向量{26^2; 26^1; 26^0}

=MMULT((CODE(MID(B1,{1,2,3},1))-CODE("A")+1),{676;26;1})

结合零填充公式可得出

=MMULT((CODE(MID(RIGHT(REPT(CHAR(CODE("A")-1),3)&A1, 3),{1,2,3},1))-CODE("A")+1),{676;26;1})

答案3

如果您的列字母在 A1 中(仅字母,而不是行),并且您的 Office 365 具有该SEQUENCE功能,则可以使用:

=SUMPRODUCT(CODE(MID(UPPER(A1),SEQUENCE(LEN(A1)),1))-64,26^SEQUENCE(LEN(A1),,LEN(A1)-1,-1))

如果您愿意,可以添加代码,当计算结果的列数小于 1 或大于 16384(取决于您的 Excel 版本)时返回错误消息。

答案4

一种完全不同的方法:创建第 1 行中所有地址的列表,然后在该列表中找到我们想要的地址。

=MATCH(A1&"1",ADDRESS(1,COLUMN($1:$1),4),0)

这适用于 Excel、Google Sheets 和 LibreCalc。关于数组公式:

  • Excel:根据版本的不同,您可能需要使用CTRL+ SHIFT+将其作为数组公式输入ENTER
  • Google Sheets:您肯定需要将其作为数组公式输入。或者,您可以手动将公式包装在函数中,这与按++时ArrayFormula()Sheets 执行的操作相同。CTRLSHIFTENTER
  • LibreCalc:您不需要在 7.2.6 版本中将其作为数组公式输入,这是我测试的全部版本。

下面的屏幕截图按顺序显示了 Excel、Sheets 和 Calc。

Excel    工作表    计算

相关内容