ラブグラフでCTOをしております横江( @yokoe24 )です！
スプレッドシートの 列名（A, B, C, AA, ...のようなやつ） を 列番号（1, 2, 3 列目を示す値） に相互変換する Ruby プログラムを書いていたら
いろいろハマりどころがありましたので共有します！
以降、パット見で区別がつきやすいよう「列名」は「列ラベル」と表記しています。
まずはクイズです！
まず、列ラベルを列番号に変換する上で、どのようになっているか理解をしていきましょう。
列ラベルと列番号は、以下の表で示す関係のようになっています。
| 列ラベル | 列番号 |
|---|---|
| A | 1 |
| B | 2 |
| ⋮ | ⋮ |
| Z | 26 |
| AA | 27 |
| AB | 28 |
| ⋮ | ⋮ |
| ZZ | ？ |
| AAA | ？ |
| ⋮ | ⋮ |
ここで問題です！
列ラベル「ZZ」の列番号はいくつでしょう？ 🤔
私はここを勘違いをしていたため、テストコードに誤った返り値を書くというミスをしました🥲
正解は、26 × 26 ＝ 676！
……ではなく、 26 + 26 × 26 ＝ 702 が正解です。
AA〜ZZ の個数が 26 × 26 で、 A〜Z の個数が 26 個だからですね。
この仕組みに注意しなければいけません。
26進数でも27進数でもない
話を急に変えて、2進数の話をします。
2進数を、上の方を0で埋めていくとしたとき、数字は以下のように増えていきます。
0000
0001
0010
0011
0100
0101
0111
「列ラベルは A〜Z の26文字で表現するのだから、26進数として考えれば列ラベルを列番号に直せそう！」
……最初はそう考えましたが、これは上手くいきませんでした。
最初の値が「A」、その後で「AA」に桁上がりをすることを考えると、
先ほどの2進数の数字の上がり方とは全然違う挙動をしていることがわかります。
仮に、「A」が 0 としましょう。
それを 0 埋めで考えた時、
AAAA = 0
AAAB = 1
AAAC = 2
⋮
AAAZ = 25
AAAA = 26
のように値が増えていくことになってしまいます。
「AAAA」が2回出てきてしまいました。これではおかしいですね。
じゃあ「A」を 1 として、実は空白文字が27文字目で、上位の桁は空白文字で埋められているのか？
と仮に考えたとしても、27進数の考え方としては
= 0
A = 1
B = 2
C = 3
⋮
Z = 26
A = 27
AA = 28
このように値が増えていくはずで、「A 」なんて列ラベルはないのでこれもおかしいです。
つまり、列ラベルと列番号の相互変換にあたって、
列ラベルの値の増え方は 26進数でも27進数でもない 、独特のものであるという理解が必要です！
どう実装するのか
ではどう実装すればいいのかを、実際に自分が実装したコードを見ながら説明します。
列ラベルを列番号に変換するスクリプト
このあとに出てくる「列番号 → 列ラベル」の変換スクリプトよりも、こっちのほうが単純です。
# スプレッドシートの列ラベルを列番号に変換
def column_number_by_label(label:)
a_to_z_size = ("A".."Z").to_a.length # A から Z までの文字数（定数で 26 と入れてしまってもよさそう）
label.chars.reverse.each_with_index.sum do |char, idx|
(char.ord - "A".ord + 1) * (a_to_z_size**idx)
end
end
基本的な考え方は、
「A」→「B」→「C」は 1 ずつ増えていき、
「AA」→「BA」→「CA」のように2桁目が変わるのは値が 26 増えたときで、
「AAA」→「BAA」→「CAA」のように3桁目が変わるのは値が 26 * 26 増えたとき……
というものです。
なので、「ABC」という列ラベルの列番号を求めるときの手順は、
一番右の1桁目が (C - A) + 1 = 3
2桁目が ((B - A) + 1) * 26 = 2 * 26 = 52
3桁目が ((A - A) + 1) * (26 ** 2) = 1 * (26 ** 2) = 676
これらの合計である 3 + 52 + 676 = 731 が「ABC」に対応する列番号となります。
この手順を再現するために、
label.chars.reverse で「ABC」というラベルの文字を逆順にして ["C", "B", "A"] とすることで、
1桁目から順々に処理できるようにしています。
やや難しい考え方ですが、伝わりましたでしょうか……？
列番号を列ラベルに変換するスクリプト
わかりづらいのでコメントを多めに書いていますが、それでも難しいと思います。
def column_label_by_number(number:)
a_to_z_size = ("A".."Z").to_a.length # A から Z までの文字数
label = ""
while number > 0
# 1〜26列目は A 〜 Z で、2文字にはならない。よって、26 で割る前に自身を 1 引く必要がある
add_num_for_a = (number - 1) % a_to_z_size # A からいくつアルファベットを移動させるか
label = ("A".ord + add_num_for_a).chr + label # 26で割った余りを使って、列名となる文字を右から順に埋めていくイメージ
number = (number - 1) / a_to_z_size # 26で割った値が1以上ならループは続く
end
label
end
基礎的な考え方としては、列ラベルと列番号の関係性として、
- A〜Z : 1 〜 26
- AA〜ZZ : (26 + 1) 〜 (26 + 26^2)
- AAA〜ZZZ : (26 + 26^2 + 1) 〜 (26 + 26^2 + 26^3)
- AAAA〜ZZZZ : (26 + 26^2 + 26^3 + 1) 〜 (26 + 26^2 + 26^3 + 26^4)
となっていることにあります。
数学に慣れている人であればなんとなく、関係性がつかめてくると思います。
しかしこのままでは、「A」や「AA」などの桁上がりをするタイミングの境界の数値が
中途半端な値でプログラミング的には扱いにくいので、 -1 をして考えます。
- A〜Z : 0 〜 25
- AA〜ZZ : 26 〜 (26 + 26^2 - 1)
- AAA〜ZZZ : (26 + 26^2) 〜 (26 + 26^2 + 26^3 - 1)
- AAAA〜ZZZZ : (26 + 26^2 + 26^3) 〜 (26 + 26^2 + 26^3 + 26^4 - 1)
こうすると何がうれしいかと言うと、
一番右の文字（ラベル「AB」の場合における「B」）が、「26で割った余り」に対応するということがわかってきます。
だから帰納法的に、列番号から -1 をおこなえば、ラベルの右の文字から順々にプログラムで文字を埋めていけるようになり、
列ラベルの文字列が完成していくわけです！
最初に示したコードで、26で割った余りを求めるときや、自身を26で割る前に -1 をしているのはそういうわけです。
……これは文章だけだと難しい話だと思うので、わからない方は実際にスクリプトを書いてみたり、
紙とペンで、列ラベルと列番号の関係性を整理してみることをオススメします。
おしまい！
以上、スプレッドシートやエクセルの列名（列ラベル）を列番号に相互変換する話でした！
一見単純な話に思えるのに、
競技プログラミングのようにアルゴリズムを整理して考えないと
プログラムを編み出せないことがおもしろかったです！
ここに行き着くまでに何度か間違った処理を書いていたので、
テストコードに大変救われました・・・☺️
Discussion