【ワレコのVBA】列番号と列アルファベット文字列の相互変換-高速版

この記事は約10分で読めます。
スポンサーリンク

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

例えば、

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

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

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

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

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

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

 

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

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

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

サンプルVBAダウンロード

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

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

 

では、本題に入ろう。

スポンサーリンク
ワテ推薦のプログラミングスクールで学ぶ
スポンサーリンク
スポンサーリンク

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

と表示される。

 

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

二番目の引数 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("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

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

ワテが試した限りでは、

列番号      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億

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

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

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

要するに .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秒

まとめ

EXCELに於いて、列番号の数字をアルファベット文字列に変換する方法とその逆変換の方法を紹介した。

EXCELのセル関数やVBA関数として利用出来る。

この記事で紹介した方法では、純粋に計算式で求める手法なので、高速に計算できる。

ワテの場合、これらの変換関数に関してはかなり長期間ネット検索して、各種のサンプルプログラムを実際に試してみて、そして、最終的に採用したのが当記事で紹介した関数だ。

なので、列番号数字と列アルファベットの変換・逆変換に関しては当記事で紹介した関数は、世の中にある各種の方式と比べても最速レベルだと思っている。

質問募集

EXCEL VBAあるいはプログラミング一般に関して何か質問とか相談などありましたら、お気軽にお問い合わせください。

下のほうにあるコメント送信欄などをご利用下さい。

「こんな処理をVBAでやりたいんだが、どうやったら良いか分からない。」

など。

ワテの時間が有る限り、無料でお答えします。無料サンプルコードもお作りしますよ!

 

おかきを食う

全然話が変わるが、ワテはおかきが大好き。

そんなワテが各社のおかきを食べ比べて皆さんにお勧めするのがこの素焼茶屋だ。

これが物凄く美味い。

素焼きおかきは薄い塩味の素朴な味だ。

お米そのものの味をじっくりと味わえる。

この素焼茶屋はおかきの最高傑作だと思う。

熱いお茶を煎れて、おかきをバリバリ。

ああ、落ち着く。

スポンサーリンク
ワテ推薦のプログラミングスクールで学ぶ
コメント募集

この記事に関して何か質問とか補足など有りましたら、このページ下部にあるコメント欄からお知らせ下さい。

ExcelVBA
スポンサーリンク
warekoをフォローする
スポンサーリンク
われこ われこ

コメント