【ﾜﾚｺVBA】列番号･列アルファベット文字列の相互変換-高速版

エクセルVBAでプログラミングをする場合に、列アルファベット文字列を列番号数字に変換したい場合がある。

例えば、

A列なら 1
B列なら 2
じゃあ、AAA列は数字で言うと何列目？

と聞かれても直ぐには分からない。

あるいはその逆に、列番号をアルファベット文字列に変換したい場合もある。

列番号1はA
列番号26はZ
じゃあ列番号1000は英字に直すと何？

と聞かれても分からない。

そんな質問に直ぐに答えられる人はフォンノイマンかラマヌジャンくらいかもしれない。

この記事では、EXCEL作業中にそう言う列番号と列アルファベットの相互変換が必要になった時に使えるお勧めの関数を紹介したい。

VBAの関数としても使えるし、セル関数としても使える。

ネットを検索すると、この手の変換には各種の手法があるが、ワテが使っている高速に変換できる関数を紹介したい。

サンプルVBAダウンロード

本ページで使用したEXCELファイルをダウンロードしたい人はここからどうぞ。

列番号⇔列アルファベットの相互変換実験Book1-ver2.xlsm

フォン・ノイマンの生涯 (朝日選書 610)

朝日新聞出版

ラマヌジャン探検――天才数学者の奇蹟をめぐる (岩波科学ライブラリー)

岩波書店

では、本題に入ろう。

EXCELのR1C1形式とA1形式
列番号を列アルファベットに変換する関数
列アルファベットを列番号に変換する関数
これらの関数で利用出来る最大の列数
一般的な方式の変換関数との速度比較
まとめ

EXCELのR1C1形式とA1形式

EXCELではセルやレンジの範囲を指定する場合に、行と列を数字で表現するR1C1形式で書くとプログラムは書き易い。

Cells(10, 5) = "単一セル"
Range(Cells(10, 20), Cells(20, 30)).Value = "長方形領域"

こんな風に数字で行番号と列番号を指定出来るので、VBAのFor Nextループを使う場合には、このR1C1形式がよく使われる。

一方、セルやレンジの範囲を文字列で与える事も可能だ。

Range("A1").Value = "単一セル"
Range("a1:f5").Value = "長方形領域"
Cells("a2").Value = "単一セル" 　' これは出来ない。

この方式は A1形式と呼ばれる。

VBAでプログラムを書いていると、これらのR1C1形式とA1形式との間で相互変換したくなる場合が良くある。

列番号を列アルファベットに変換する関数

さて、先ずは列番号からアルファベット文字列への変換関数だ。

例：　1　→　”A”

Option Explicit

Function ColNum2Let(ByVal colNum As Long, Optional colStr As String = "") As String
 If colNum = 0 Then
 ColNum2Let = colStr
 Else
 colStr = Chr(65 + (colNum - 1) Mod 26) & colStr
 colNum = (colNum - 1) \ 26
 ColNum2Let = ColNum2Let(colNum, colStr)
 End If
End Function

VBAのエディターを開いて新しいモジュールを一個追加して、この関数をその中に貼り付けておくと良い。

そうすると、Sheet1のセルに

=ColNum2Let(1000)

などと入力すると、列番号1000に対応するアルファベットは

ALL

と表示される。

関数名 ColNum2Let はColumn Number To Letterと言う意味で付けている。

なお、この関数は再帰的に実行している。

二番目の引数 Optional colStr As String = “” は、再帰呼び出しの時に利用されるので、自分で呼び出す場合には二番目の引数は不要で、上記の通り最初の引数（colNum As Long）に1000などの列数字を整数で与えるだけで良い。

次は、逆変換。

列アルファベットを列番号に変換する関数

こちらの関数は引数で列アルファベットを文字列で与える。

例：　”A”　→　1

Function ColLet2Num(ByVal colStr As String) As Long
 Dim colNum As Long
 Dim i As Long
 colNum = 0
 For i = 1 To Len(colStr)
 colNum = colNum * 26 + (Asc(UCase(Mid(colStr, i, 1))) - 64)
 Next
 ColLet2Num = colNum
End Function

関数名 ColLet2Num はColumn Letter To Numberと言う意味で付けている。

使い方としては、以下のように引数で列アルファベット文字列を与えれば良い。

 =ColLet2Num("AAA")

その実行結果は以下の通り。

703

となる。

これらの関数で利用出来る最大の列数

これらの二つの関数の動作を試してみたい人は、EXCELのセルを使って以下のように計算してみると良い。

	A	B	C
1	1000	= ColNum2Let(A1)	= ColLet2Num(B1)
2	1001	= ColNum2Let(A2)	= ColLet2Num(B2)
3	1002	= ColNum2Let(A3)	= ColLet2Num(B3)

その結果、以下のように

	A	B	C
1	1000	ALL	1000
2	1001	ALM	1001
3	1002	ALN	1002

Ｂ列にアルファベット文字列が表示されて、Ｃ列で元の数字に戻れば成功だ。

ワテが試した限りでは、

列番号　　　　　　2,147,483,647

列アルファベット　FXSHRXW

までは問題なく相互に変換出来る。

ちなみに、

EXCEL 2007以上では、扱える行列の範囲は以下の通り。

1,048,576 行 x 16,384 列

EXCEL 2003や、それ以前は

65,536 行 x 256 列

一方、ワテが使っている方法では 2,147,483,647列まで対応している。

21億列だ。

35億ブルゾンちえみには及ばないが、まあ21億列もあるEXCELが発売されたとしても、ワテ方式は利用出来る。

従って、この記事で紹介した二つの関数を使う限りは、現行のエクセルだけでなく、今後発売される全てのエクセルでも利用出来ると思って良いだろう。

完璧や！

エクセルワレコと呼んでくれ！　21億

【C&Y】ブルゾンちえみ風なりきりキャリアOL 豪華４点セットコスプレハロウィン衣装仮装コスチューム忘年会宴会余興女装かつらカツラパーティーウィッグストライプシャツミニスカ

C&Y COLLECT MARKET

一般的な方式の変換関数との速度比較

高速版と銘打っている以上、一般的な変換方式に比べてどれくらい速いのか比較してみた。

エクセルの列番号と列アルファベット文字との相互変換で、良く見かけるのは以下のような関数だ。

要するに .Address プロパティとか、 .Columnプロパティを使う方式。

以下の関数はワテがネットのサンプルを見て即席で作ったやつなので改良の余地はあるかも知れない。

列番号を列アルファベット文字に変換する一般的な手法

Function ColNum2Let_一般方式(lngColNum As Long) As String
 On Error GoTo Error:
 Dim strAddr As String
 strAddr = Cells(1, lngColNum).Address(False, False)
 ColNum2Let_一般方式 = Left(strAddr, Len(strAddr) - 1)
 Exit Function
Error:
 ColNum2Let_一般方式 = "計算出来ませんでした。"
End Function

列アルファベット文字を列番号に変換する一般的な手法

Function ColLet2Num_一般方式(colStr As String) As Long
 On Error GoTo Error:
 Dim colAddress As String
 colAddress = colStr + "1"
 ColLet2Num_一般方式 = Range(colAddress).Column
 Exit Function
Error:
 ColLet2Num_一般方式 = -999 ' "計算出来ませんでした。"
End Function

高速版と一般的な手法との計算時間比較結果

テスト環境

自作パソコン（Core i7-4770K, 32GBメモリ, SSD750GB, HDD3TB,5TB)
Windows 10 Pro(64)
Visual Studio 2017 Community, 全パッケージをインストール（約100GBのSSD使う）
OFFICE Professional 2013

以下のような計算時間比較の為の関数を作ってみた。

列数字 i　=>　列アルファベットへ変換　=>　列数字へ変換

を100万回計算している。

Sub 計算時間比較_1()
 Dim StartTime, StopTime As Variant
 StartTime = Time
 Dim colLet As String
 Dim colNum As Long
 Dim n As Long
 Dim i As Long
 For n = 1 To 100
  For i = 1 To 10000
   '以下のコメント部分を書き換えて高速版/一般方式を切り替える 
   '高速版
   'colLet = ColNum2Let(i)
   'colNum = ColLet2Num(colLet)
   '一般方式
   colLet = ColNum2Let_一般方式(i)
   colNum = ColLet2Num_一般方式(colLet)
  Next i
 Next n
 StopTime = Time
 StopTime = StopTime - StartTime
 MsgBox "計算時間：" & Minute(StopTime) & "分" & Second(StopTime) & "秒"
End Sub

高速版
1.7秒

一般方式
5.5秒