matrixlt's blog
0%

Excel的列号转换

Posted on Edited on

在默认状态下，Excel中的列从第一列开始使用A，B，C，..，Z，AA，AB，..作为列号。如果我们已知需要读写第n列，那么我们需要找到第n列所对应的列号。

然而Excel实际上有另一种更加统一的表示单元格的方法，即使用RnCm代表第n行第m列的单元格，例如A2单元格可表示为R2C1。使用这种表示方式只需要沿着”文件”->”选项”->”公式”->勾选”R1C1引用样式”操作即可。这里需要注意，启用这种选项会导致默认的引用样式失效。换言之，引用样式是Excel表格属性的一部分，你只能同时使用一种引用样式。

上面的方法可以解决读写第n列的问题，大不了先换成R1C1样式，发给别人用之前再换回默认样式。不过，求第n列的默认列号仍然是一个有趣的问题。下面我们仔细考察这种转换。

第一个出现的想法应该是：这不就是26进制数吗，只是使用了A..Z表示1..26这26个数码而已。沿着这个想法，我们将n转换为26进制，再对每一位应用1..26 -> A..Z的映射似乎就大功告成了。

说起来容易做起来难。首先遇到的问题就是，化为26进制时是使用0..25这26个数码表示的，但是A..Z对应的是1..26。似乎我们应当这样考虑:
\[n \xrightarrow{f}
\overline{n_1n_2..n_k}_{26}(n_i \in {0,1,..,25})
\xrightarrow{g} \overline{n_1n_2..n_k}_{26}(n_i \in {1,2,..,26})
\xrightarrow{h} \overline{n_1n_2..n_k}_{26}(n_i \in {A,B,..,Z})\]
那么似乎很显然的$g: x \rightarrow{} x+1$,$f$与$h$如前所述。这样的想法当然是大错特错,以第2列为例：$2\rightarrow{} 2 \rightarrow{}3 \rightarrow{} C$实在荒谬。

想当然要不得。A..Z与1..26对应可是最确实的事实。那么似乎是
$g: x \rightarrow{} 26 \text{ if x == 0 else x}$。这当然也不对，以第26列为例:$26 \rightarrow \overline{10} \rightarrow \overline{1,26} \rightarrow \overline{AZ}$。这到底怎么回事，直觉上$f$与$h$是那么正确，但是怎么和$g$组合起来就不对呢？

我们先退一步，考虑反问题：已知列号，求其在第几列。按照A..Z对应1..26的原则，似乎有
$$\overline{n_1n_2..n_k}(n_i \in \{A,B,..,Z\}) \xrightarrow{h^{-1}} \overline{n_1n_2..n_k}_{26}(n_i \in \{1,2,..,26\})\xrightarrow{} \sum_{i=1}^kn_i26^{i-1}$$
上面的变换是完全正确的。如$B\rightarrow{}2\rightarrow{}2$,$Z\rightarrow{}26\rightarrow{}26\cdot26^0$,$YYZ\rightarrow{}25,25,26\rightarrow{} 25\cdot26^2 + 25\cdot26^1+26\cdot26^0=26^3$

这个逆变换给了我们极大启示：首先变换h没有问题，而变换g的作用应该是将$\sum_{i=1}^kn_i26^{i-1}(n_i \in {0,1,..,25}) \xrightarrow{g} \sum_{i=1}^kn_i26^{i-1}(n_i \in {1,2,..,26})$。这实现起来很简单，在进制转换时，原来遇到余数为0时其实就是余数为26。只不过原来不允许使用26这个数字，只好把这整个26扔到下一位去处理，现在可以用了，直接在本位上写上26就完成了。这一步完全与转换为26进制的步骤重复，故f与g可以合并。

代码如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
def number_to_char_26(n: int) -> str:
    if 1 <= n <= 26:
        return chr(ord("A") + n - 1)


def excel_col_number(col_number: int) -> str:
    # 求Excel第n列列号
    char_26 = []
    while True:
        remainder = col_number % 26
        if remainder == 0:
            remainder = 26
        char_26.append(remainder)
        col_number = (col_number - remainder) // 26
        if col_number == 0:
            break
    char_26.reverse()
    char_26 = "".join(map(number_to_char_26, char_26))
    return char_26

如果用一句话总结这个问题的关键是什么，那么可以引用我的精通计算机(包括C++)的好朋友yinf(他的博客地址为 www.linyinfeng.com )的看法作为答案:“不管前面的数如何最后一位都能靠除26直接确定”。这句话说明了我们只需要除一次26，根据余数就可以判断最后一位是1..26中的哪一个数码，之后将商作为新数字，不断递归下去就得到了问题的答案。他使用Haskell写成的代码如下，这里将0作为递归起点，对应空串，使得代码非常简洁优雅：

1
2
3
4
5
6
7
8
9
import           Data.Char

toExcel :: Int -> String
toExcel n = if n == 0 then "" else toExcel higher ++ [digitLetter]
 where
  remainder   = n `mod` 26
  digit       = if remainder == 0 then 26 else remainder
  digitLetter = chr (ord 'A' + digit - 1)
  higher      = (n - digit) `div` 26

至此，我们完整地解决了这个问题。这个问题看起来简单，关键代码只有将0换为26的一行，但是一次做对也不容易。在解决它的过程中，考虑其逆变换起到了很大的作用，这是一种具有一般性的思路。最后感谢CSDN用户taller_2000的博客《Excel列标与列号转换》，若不是看了那篇博客，我会花去更多的时间在这个问题上。CSDN上确实有原创的好内容，可惜不好的内容多得淹没了这些好内容。

不过也许你还有一些疑问，比如在k进制下，任取$a_i \in {n_1, n_2,..,n_k} \subset \mathbb{N},\sum_{i=1}^na_ik^{i-1}$可以表示哪些数？这些数有什么特征？表示是唯一的吗？这些问题应该不是很难，但探索它们也可满足我们的好奇心。