えくせるちゅんちゅん

ことりがエクセルをちゅんちゅんするブログ

MENU

ExcelとVBAで列番号を最速で相互変換する関数を求めて徹底調査してみた

今回はExcelの列番号を高速で相互変換する関数・VBAの研究成果を発表します。


元ネタ

元々は以前私がツイートしたものです。

2019/12/15 関数のみで変換したい。

2019/12/17 大量のセルにAddressを書き込む。

2019/12/17 VBAの関数を最適化したい。

ここから更に改良を重ねて進化を遂げた、VBA史上最速の関数を発表することにしました。


列番号変換のテストパターン

式の作成にあたっては境界値分析が重要となってくるので、テストパターンを列挙しておきます。

以前紹介した「Excel VBAの自作関数の効率的なテスト方法について」を使うと便利です。

www.excel-chunchun.com

整数(_n_ 文字列(_s_ 文字列規定外(_s_ 備考
0 エラー
1 A A 最小値
26 Z Z 1桁最大値
27 AA AA 2桁最小値
52 AZ AZ 1桁目繰り上がり前
53 BA BA 1桁目繰り上がり後
256 IV IV xls形式最大値
257 IW IW xls形式エラー値
702 ZZ ZZ 2桁最大値
703 AAA AAA 3桁最小値
728 AAZ AAZ 1桁目繰り上がり前
729 ABA ABA 1桁目繰り上がり後
1,378 AZZ AZZ 2桁目繰り上がり前
1,379 BAA BAA 2桁目繰り上がり後
16,384 XFD XFD xlsx形式最大値
16,385 XFE xlsx形式エラー値
2,147,483,647 FXSHRXW Long型変数限界値
2,147,483,648 #NUM! オーバーフロー


エクセル関数による変換式

列番号の整数を文字列に変換する関数式(1→A)

(1) 数値を文字列に変換する一般的な方法

現在はこれが主流でしょう。IFERRORがあるため、使えるのはExcel2007以降となります。

A1、B1、C1を生成して、1を置換で抹消する考え方ですね!

=IFERROR(SUBSTITUTE(ADDRESS(1,_n_,4),"1",""),"")

(2) 昔からあるもう一つの方法

同じ変数を2回使うのでイマイチですが、こんな方法もあります。

A$1、B$1、C$1を生成して$の手前までを抽出する考え方ですね!

=LEFT(ADDRESS(1,_n_,2),FIND("$",ADDRESS(1,_n_,2))-1)

(3) 論理的に計算で導き出す方法

ADDRESSを使わず文字コードから計算のみで求める、という縛りプレイから編み出した式がこれです。

=IF(_n_-702>0,CHAR(65+INT((_n_-26*27-1)/26/26)),"")&
IF(_n_-26>0,CHAR(65+MOD(INT((_n_-26-1)/26),26)),"")&
CHAR(65+MOD(_n_-1,26))

真っ先にエクセルの列番号はA~Zの26進数かと考えますが、実は最上位の桁には空白があるため27通りあります。では27進数かと思いきや下位の桁は十進数の0に相当する空白が無いんですよね。

従って26進数を使った方法でも、27進数の方法でも算出できません。(たぶん)

VBAのようにループが出来れば良いのですが、Excel関数は1ステップで計算しなければなりません。

これらに対応するために上記のような超難解の数式が必要となります。

良い頭の体操になるので、好きな人は見ないで考えてみてくださいね!(見てもわからんって?それもそうか・・)

ちなみに私はこの式を組み上げるのに3時間ほど掛かりました。


一応、この3の式の特徴はこんな感じです。

式\値 -1 0 1 16384 16385
1 (置換) A XFD
2 (LEFT) #VALUE! #VALUE! A XFD #VALUE!
3 (計算) Y Z A XFD XFE

現在(~Excel2019)の最大列数であるXFD=16384以下ならば、(1)の式で対応できますが、それ以上の列番号を求めたいという超イレギュラーな場面では、(3)を知っていると良いかも知れません。ヾノ ゚ω゚ )ナイナイ

列番号の文字列を整数に変換する関数式(A→1)

(1) 一般的な方法1

参照に変換して列番号を求めるだけの単純な式ですね!

=COLUMN(INDIRECT("A1"))

(2) 一般的な方法2

同じく参照に変換していますが、列番号を求めるのにはCELL関数を使っています。

ただしCELLはアクティブシートによって挙動が変わる場面があるため、基本的には(1)を使ったほうが良いと思います。

=CELL("col",INDIRECT("A1"))

(3) 論理的に計算して出す方法

文字列を数値に戻すのは簡単です。なんパターンか考えてみました。

同じロジックを二度使わない。

=IF(LEN(A1)>=1,(CODE(RIGHT(A1,1))-64),0)
 +IF(LEN(A1)>=2,(CODE(MID(A1,LEN(A1)-1,1))-64)*26,0)
 +IF(LEN(A1)>=3,(CODE(MID(A1,LEN(A1)-2,1))-64)*26^2,0)

右端1桁目のみ効率化

=(CODE(RIGHT(A1,1))-64)
 +IF(LEN(A1)=2,(CODE(MID(A1,1,1))-64)*26,0)
 +IF(LEN(A1)=3,(CODE(MID(A1,1,1))-64)*26^2
   +(CODE(MID(A1,2,1))-64)*26,0)

CHOOSEを使用

=(CODE(RIGHT(A1,1))-64)
 +CHOOSE(LEN(A1),
  0,
  (CODE(MID(A1,1,1))-64)*26,
  (CODE(MID(A1,1,1))-64)*26^2+(CODE(MID(A1,2,1))-64)*26
 )

脳死

=IF(LEN(A1)=1,
  CODE(A1)-64,
  IF(LEN(A1)=2,
    (CODE(MID(A1,1,1))-64)*26+CODE(MID(A1,2,1))-64,
     (CODE(MID(A1,1,1))-64)*26^2+(CODE(MID(A1,2,1))-64)*26+CODE(MID(A1,3,1))-64
  )
)

わえなび様から配列を使った式をご提供いただきました。

https://twitter.com/waenavi_jp/status/1165981036276117504?s=20

=SUMPRODUCT((CODE(RIGHT(A1,{1,2,3}))-64)*26^{0,1,2}*(LEN(A1)>{0,1,2}))


VBAによる変換関数

というわけで、余興はここまで!

ここからはVBAを使った変換関数のガチバトルが始まりまります。

列番号の整数を文字列に変換するVBA関数(1→A)

私の知る限りの方法で、なんと6種類もの関数をご用意しました。

できるだけ高速化したつもりですが、プロな方からは叱られるかも・・?というか叱って下さい。よろしくおねがいします。(でもMじゃないですから!)

'Addressプロパティから求める方法
Function GetColumn_ToStr_AddressLeft(col As Long) As String
    On Error GoTo ErrorSkip:
    Dim adr As String
    adr = Cells(1, col).Address(False, False)
    GetColumn_ToStr_AddressLeft = Left(adr, Len(adr) - 1)
    Exit Function
ErrorSkip:
    GetColumn_ToStr_AddressLeft = ""
End Function

'AddressプロパティをSplitで分解してワンステップで求める方法
Function GetColumn_ToStr_AddressSplit(col As Long) As String
    GetColumn_ToStr_AddressSplit = Split(Cells(1, col).Address, "$")(1)
End Function

'1行の関数で求める方法
    '=IF(D2-702>0,CHAR(65+INT((D2-702-1)/26/26)),"")&
    ' IF(D2-26>0,CHAR(65+MOD(INT((D2-26-1)/26),26)),"")&
    'CHAR(65+MOD(D2-1,26))
Function GetColumn_ToStrIIF(col As Long) As String
    GetColumn_ToStrIIF = IIf(col <= 26 * 27, "", Chr(65 + Int((col - 1 - 26 * 27) / 26 / 26))) & _
                              IIf(col <= 26, "", Chr(65 + Int((col - 1 - 26) / 26) Mod 26)) & _
                               IIf(col <= 0, "", Chr(65 + Int((col - 1) Mod 26)))
End Function

'Excel関数式のIIFを展開して高速化したもの
Function GetColumn_ToStr_Function(col As Long) As String
    If col <= 0 Then
        GetColumn_ToStr_Function = ""
    ElseIf col <= 26 Then
        GetColumn_ToStr_Function = Chr(65 + ((col - 1) Mod 26))
    ElseIf col <= 26 * 27 Then
        GetColumn_ToStr_Function = Chr(65 + ((col - 27) \ 26) Mod 26) & _
                                   Chr(65 + ((col - 1) Mod 26))
    ElseIf col <= 26 * 27 * 27 Then
        GetColumn_ToStr_Function = Chr(65 + ((col - 27 * 27) / 26 \ 26)) & _
                                   Chr(65 + ((col - 27) \ 26) Mod 26) & _
                                   Chr(65 + ((col - 1) Mod 26))
    Else
        GetColumn_ToStr_Function = ""
    End If
End Function

'再帰によって求める方法
Function GetColumn_ToStr_Recursion(ByVal col As Long, Optional ByRef cs As String = "") As String
    If col = 0 Then
        GetColumn_ToStr_Recursion = cs
    Else
        cs = Chr(65 + (col - 1) Mod 26) & cs
        col = (col - 1) \ 26
        GetColumn_ToStr_Recursion = GetColumn_ToStr_Recursion(col, cs)
    End If
End Function

'Do~Loopで回して求める方法
Function GetColumn_ToStr_DoLoop(ByVal col As Long) As String
    Dim az As Long
    Do While col <> 0               'colの残りが0になったら終了
        az = (col - 1) Mod 26 + 1   'colから下一桁の1~26(A~Z)を取り出してazへ
        col = Int((col - az) / 26)  'colから下一桁分を取り除く
        GetColumn_ToStr_DoLoop = Chr(65 + az - 1) & GetColumn_ToStr_DoLoop  '1~26を文字A~Zへ変換して戻り値に結合
    Loop
End Function

検証用コード

これに加えて、先日のストップウォッチクラスを使用します。

www.excel-chunchun.com

'一括テストとレポート出力
Sub Test_GetColumn_ToStr_Report()

    Dim Data As Variant
    ReDim Data(1 To 7, 1 To 5)
    Dim List As Collection
    Dim i As Long, j As Long, k As Long
    
    Data(1, 1) = "-"
    Data(2, 1) = "AddressLeft"
    Data(3, 1) = "AddressSplit"
    Data(4, 1) = "FunctionIIF"
    Data(5, 1) = "Function"
    Data(6, 1) = "DoLoop"
    Data(7, 1) = "Recursion"
    
    Data(1, 2) = "A~Zx10000"
    Set List = Test_GetColumn_ToStr_Func(10000, 1, 26)
    For i = 2 To UBound(Data, 1)
        Data(i, 2) = List(Data(i, 1))
    Next
    
    Data(1, 3) = "AA~ZZx1000"
    Set List = Test_GetColumn_ToStr_Func(1000, 27, 702)
    For i = 2 To UBound(Data, 1)
        Data(i, 3) = List(Data(i, 1))
    Next
    
    Data(1, 4) = "AAA~XFDx100"
    Set List = Test_GetColumn_ToStr_Func(100, 703, 16384)
    For i = 2 To UBound(Data, 1)
        Data(i, 4) = List(Data(i, 1))
    Next
    
    Data(1, 5) = "A~XFDx100"
    Set List = Test_GetColumn_ToStr_Func(100, 1, 16384)
    For i = 2 To UBound(Data, 1)
        Data(i, 5) = List(Data(i, 1))
    Next
    
    Dim Ws As Worksheet
    Worksheets.Add
    Set Ws = ActiveSheet
    Ws.Cells(1, 1).Resize(UBound(Data, 1), UBound(Data, 2)).Value = Data
    
End Sub

Function Test_GetColumn_ToStr_Func(LoopCount As Long, _
        iStart As Long, iEnd As Long) As Collection
        
    Dim rv As Collection
    Set rv = New Collection
    
    Dim i As Long, j As Long
    Dim s As String
    
    Dim cSW As clsStopWatch
    Set cSW = New clsStopWatch
        
    Call cSW.Start("0")

    'AddressをLeftで求める方法
    For j = 1 To LoopCount
        For i = iStart To iEnd
            s = GetColumn_ToStr_AddressLeft(i)
        Next
    Next
    rv.Add cSW.LapTime(), "AddressLeft"
    Debug.Print "AddressLeft", rv("AddressLeft")

    'Addressプロパティを使用する方法
    For j = 1 To LoopCount
        For i = iStart To iEnd
            s = GetColumn_ToStr_AddressSplit(i)
        Next
    Next
    rv.Add cSW.LapTime(), "AddressSplit"
    Debug.Print "AddressSplit", rv("AddressSplit")

    'Excel関数式のままで求める方法
    For j = 1 To LoopCount
        For i = iStart To iEnd
            s = GetColumn_ToStr_FunctionIIF(i)
        Next
    Next
    rv.Add cSW.LapTime(), "FunctionIIF"
    Debug.Print "FunctionIIF", rv("FunctionIIF")

    'IIFを展開した関数
    For j = 1 To LoopCount
        For i = iStart To iEnd
            s = GetColumn_ToStr_Function(i)
        Next
    Next
    rv.Add cSW.LapTime(), "Function"
    Debug.Print "Function", rv("Function")

    'Do~Loopで回して求める方法
    For j = 1 To LoopCount
        For i = iStart To iEnd
            s = GetColumn_ToStr_DoLoop(i)
        Next
    Next
    rv.Add cSW.LapTime(), "DoLoop"
    Debug.Print "DoLoop", rv("DoLoop")
    
    '再帰によって求める方法
    For j = 1 To LoopCount
        For i = iStart To iEnd
            s = GetColumn_ToStr_Recursion(i)
        Next
    Next
    rv.Add cSW.LapTime(), "Recursion"
    Debug.Print "Recursion", rv("Recursion")
    
    Set Test_GetColumn_ToStr_Func = rv
End Function

計測結果

というわけで、5回の平均はこのような結果になりました。

検証コードは実行するたびに新しいシートを生成するので、串刺し集計で平均を出しましょう

たとえば、=INT(AVERAGE('5:1'!B5))こんな感じで、シート51のB5の平均が出せます。

- A~Zx10000 AA~ZZx1000 AAA~XFDx100 A~XFDx100
Function 37 222 762 778
DoLoop 56 268 906 912
Recursion 97 397 1265 1334
FunctionIIF 309 799 1847 1993
AddressLeft 672 1766 4172 4360
AddressSplit 859 2219 5125 5375
  • Windows10 Pro 1709 64bit
  • Office365 32bit 16.0.11231.20122
  • Core i7 4790K 4.0HHz 16GB

分かりやすいように、最速であるFunctionを1としたときの割合で表現してみます。

- 説明 特徴 係数
Function 関数式のVBA最適化版 1.00
DoLoop Do~Loop減算 Longの限界まで対応可 1.18
Recursion 再帰減算 Longの限界まで対応可 1.73
FunctionIIF Excel関数式転用 エラー時誤字 2.49
AddressLeft AddressをLeftで抽出 5.40
AddressSplit AddressをSplit($)で分割 エラー時#VALUE! 6.93

というわけで、最速のコードはExcelの関数式をVBA向けに最適化したものという答えになりました。


列番号の文字列を整数に変換するVBA関数(A→1)

文字列から整数への変換は比較的簡単です。(後日高速化処理を入れていたら変に複雑になってしまいました。)

こちらもなんと7種類の関数をご用意しました。

'Cellsで簡単に列番号を求める方法
Function GetColumn_ToNum_Cells(Alpha As String) As Long
    On Error Resume Next
    GetColumn_ToNum_Cells = 0
    GetColumn_ToNum_Cells = Cells(1, Alpha).Column
End Function

'Columnsで簡単に列番号を求める方法
Function GetColumn_ToNum_Columns(Alpha As String) As Long
    On Error Resume Next
    GetColumn_ToNum_Columns = 0
    GetColumn_ToNum_Columns = Columns(Alpha).Column
End Function

'Rangeで簡単に列番号を求める方法
Function GetColumn_ToNum_Range(Alpha As String) As Long
    On Error Resume Next
    GetColumn_ToNum_Range = 0
    GetColumn_ToNum_Range = Range(Alpha & "1").Column
End Function

'エクセル列番号を計算してアルファベットを整数に変換(毎ループで値を確定させる方式)
'=n[桁]*(26^(桁-1))を合計する
'For i = Len(Alpha) To 1 Step -1    'ループ順を入れ替えても動く
Function GetColumn_ToNum_Sum(ByVal Alpha As Variant) As Long
    GetColumn_ToNum_Sum = 0
    Alpha = UCase(Alpha)
    Dim i As Long
    For i = 1 To Len(Alpha)
        GetColumn_ToNum_Sum = GetColumn_ToNum_Sum + (Asc(Mid(Alpha, Len(Alpha) - i + 1, 1)) - 64) * (26 ^ (i - 1))
    Next
End Function

'エクセル列番号を計算してアルファベットを整数に変換(上から下へ。直前の値を繰り上げる方式)
'上位の位から取り出して1~26の整数に変換
'下位の位の計算するときは、上位の位を繰り上げる(*26)
Function GetColumn_ToNum_Product(ByVal Alpha As String) As Long
    GetColumn_ToNum_Product = 0
    
    If Len(Alpha) = 1 Then
        GetColumn_ToNum_Product = Asc(Alpha) Mod 32
        Exit Function
    End If
    
    Dim i As Long
    For i = 1 To Len(Alpha)
        GetColumn_ToNum_Product = GetColumn_ToNum_Product * 26 + (Asc(Mid(Alpha, i, 1)) Mod 32)
    Next
End Function

'バイト配列に変換して取り出すProduct改良版 By Furyutei
Function GetColumn_ToNum_UnicodeArray(ByVal Alpha As String) As Long
    GetColumn_ToNum_UnicodeArray = 0
    
    If Len(Alpha) = 1 Then
        GetColumn_ToNum_UnicodeArray = Asc(Alpha) Mod 32
        Exit Function
    End If
    
    Dim UnicodeArray() As Byte: UnicodeArray = Alpha
    Dim i As Long
    For i = LBound(UnicodeArray) To UBound(UnicodeArray) Step 2
        GetColumn_ToNum_UnicodeArray = GetColumn_ToNum_UnicodeArray * 26 + (UnicodeArray(i) Mod 32)
    Next
End Function
'For i = 0 To 5 '最大桁数周回またはInt(WorksheetFunction.Log(col, 26))でも良いがZでは1週分無駄に回ろうとする。

'エクセル列番号を計算してアルファベットを整数に変換(直前の値を繰り上げる方式を再帰処理)
Function GetColumn_ToNum_ProductRec(ByVal Alpha As String, Optional ByVal n As Long = 0) As Long
    GetColumn_ToNum_ProductRec = 0
    Alpha = UCase(Alpha)
    Dim i As Long
    If i = 0 Then i = Len(Alpha)
    If i = 0 Then
        GetColumn_ToNum_ProductRec = 0
    Else
        GetColumn_ToNum_ProductRec = _
            GetColumn_ToNum_ProductRec(Left(Alpha, i - 1), i - 1) * 26 + (Asc(Right(Alpha, 1)) Mod 32)
    End If
End Function

検証用コード

Option Explicit
'一括テストとレポート出力
Sub Test_GetColumn_ToNum_Report()

    Dim Data As Variant
    ReDim Data(1 To 8, 1 To 5)
    Dim List As Collection
    Dim i As Long, j As Long, k As Long
    
    Data(1, 1) = "-"
    Data(2, 1) = "Cells"
    Data(3, 1) = "Columns"
    Data(4, 1) = "Range"
    Data(5, 1) = "Sum"
    Data(6, 1) = "Product"
    Data(7, 1) = "UnicodeArray"
    Data(8, 1) = "ProductRec"
    
    Data(1, 2) = "A~Zx10000"
    Set List = Test_GetColumn_ToNum_Func(10000, 1, 26)
    For i = 2 To UBound(Data, 1)
        Data(i, 2) = List(Data(i, 1))
    Next
    
    Data(1, 3) = "AA~ZZx1000"
    Set List = Test_GetColumn_ToNum_Func(1000, 27, 702)
    For i = 2 To UBound(Data, 1)
        Data(i, 3) = List(Data(i, 1))
    Next
    
    Data(1, 4) = "AAA~XFDx100"
    Set List = Test_GetColumn_ToNum_Func(100, 703, 16384)
    For i = 2 To UBound(Data, 1)
        Data(i, 4) = List(Data(i, 1))
    Next
    
    Data(1, 5) = "A~XFDx100"
    Set List = Test_GetColumn_ToNum_Func(100, 1, 16384)
    For i = 2 To UBound(Data, 1)
        Data(i, 5) = List(Data(i, 1))
    Next
    
    Dim Ws As Worksheet
    Worksheets.Add
    Set Ws = ActiveSheet
    Ws.Cells(1, 1).Resize(UBound(Data, 1), UBound(Data, 2)).Value = Data
    
End Sub

Function Test_GetColumn_ToNum_Func(LoopCount As Long, _
        iStart As Long, iEnd As Long) As Collection
        
    Dim rv As Collection
    Set rv = New Collection
    
    Dim i As Long, j As Long
    Dim n As Long
    
    Dim cSW As clsStopWatch
    Set cSW = New clsStopWatch
    
    '配列を準備
    Dim Arr() As String
    ReDim Arr(iStart To iEnd)
    For i = iStart To iEnd
        Arr(i) = GetColumn_ToStr_Function(i)
    Next
    
    Call cSW.Start("0")
    Debug.Print "-----Test_GetColumn_ToNum_Func-----"

    'Cellsで求める方法
    For j = 1 To LoopCount
        For i = iStart To iEnd
            n = GetColumn_ToNum_Cells(Arr(i))
        Next
    Next
    rv.Add cSW.LapTime(), "Cells"
    Debug.Print "Cells", rv("Cells")

    'Columnsで求める方法
    For j = 1 To LoopCount
        For i = iStart To iEnd
            n = GetColumn_ToNum_Columns(Arr(i))
        Next
    Next
    rv.Add cSW.LapTime(), "Columns"
    Debug.Print "Columns", rv("Columns")

    'Rangeで求める方法
    For j = 1 To LoopCount
        For i = iStart To iEnd
            n = GetColumn_ToNum_Range(Arr(i))
        Next
    Next
    rv.Add cSW.LapTime(), "Range"
    Debug.Print "Range", rv("Range")

    'Sumで求める方法
    For j = 1 To LoopCount
        For i = iStart To iEnd
            n = GetColumn_ToNum_Sum(Arr(i))
        Next
    Next
    rv.Add cSW.LapTime(), "Sum"
    Debug.Print "Sum", rv("Sum")

    'Productで求める方法
    For j = 1 To LoopCount
        For i = iStart To iEnd
            n = GetColumn_ToNum_Product(Arr(i))
        Next
    Next
    rv.Add cSW.LapTime(), "Product"
    Debug.Print "Product", rv("Product")

    'UnicodeArrayで求める方法
    For j = 1 To LoopCount
        For i = iStart To iEnd
            n = GetColumn_ToNum_UnicodeArray(Arr(i))
        Next
    Next
    rv.Add cSW.LapTime(), "UnicodeArray"
    Debug.Print "UnicodeArray", rv("UnicodeArray")

    'ProductRecで求める方法
    For j = 1 To LoopCount
        For i = iStart To iEnd
            n = GetColumn_ToNum_ProductRec(Arr(i))
        Next
    Next
    rv.Add cSW.LapTime(), "ProductRec"
    Debug.Print "ProductRec", rv("ProductRec")
    
    Set Test_GetColumn_ToNum_Func = rv
End Function

検証結果

このようになりました。

- A~Zx10000 AA~ZZx1000 AAA~XFDx100 A~XFDx100
UnicodeArray 44 291 681 716
Product 42 259 759 773
Sum 140 512 1534 1553
ProductRec 163 686 2361 2443
Columns 605 1481 3433 3430
Cells 575 1483 3459 3516
Range 988 2474 5650 5859

同様に最速のUnicodeArrayを1とした時の比較はこのようになります。

説明 特徴 係数
UnicodeArray Byte配列から計算 Longの限界まで対応可 1.00
Product 桁毎の積と計算結果 Longの限界まで対応可 1.07
Sum 桁毎の計算結果の合計 Longの限界まで対応可 2.16
ProductRec Productの再帰処理化 Longの限界まで対応可 3.41
Columns Columnsから取得 4.79
Cells Cellsから取得 4.91
Range Rangeから取得 8.18

というわけで、素直に計算量の一番少なそうなFor~Nextの関数が最速になりました。

どうもネットで見てるとRangeを使って求めている事例が多いようですが、Rangeは列と行を結合してから求めるためトコトン遅いです。

列番号の計算時間が気になるほどシビアな環境はあまりないと思いますけどね・・・?

で、VBAで最速の関数は?

上から探すの大変だと思うので、もう一度貼っておきますね。

整数→文字列

'列番号の整数を文字列に変換する関数(1→A)
'Excel関数式のIIFを展開して高速化したもの
    '=IF(D2-702>0,CHAR(65+INT((D2-702-1)/26/26)),"")&
    ' IF(D2-26>0,CHAR(65+MOD(INT((D2-26-1)/26),26)),"")&
    'CHAR(65+MOD(D2-1,26))
Function GetColumn_ToStr(col As Long) As String
    If col <= 0 Then
        GetColumn_ToStr = ""
    ElseIf col <= 26 Then
        GetColumn_ToStr = Chr(65 + ((col - 1) Mod 26))
    ElseIf col <= 26 * 27 Then
        GetColumn_ToStr = Chr(65 + ((col - 27) \ 26) Mod 26) & _
                                   Chr(65 + ((col - 1) Mod 26))
    ElseIf col <= 26 * 27 * 27 Then
        GetColumn_ToStr = Chr(65 + ((col - 27 * 27) / 26 \ 26)) & _
                                   Chr(65 + ((col - 27) \ 26) Mod 26) & _
                                   Chr(65 + ((col - 1) Mod 26))
    Else
        GetColumn_ToStr = ""
    End If
End Function

文字列→整数

'列番号の文字列を整数に変換する関数(A→1)
'エクセル列番号を計算してアルファベットを整数に変換(上から下へ。直前の値を繰り上げる方式)
'上位の位から取り出して1~26の整数に変換
'下位の位の計算するときは、上位の位を繰り上げる(*26)
Function GetColumn_ToNum(ByVal Alpha As String) As Long
    GetColumn_ToNum = 0
    
    If Len(Alpha) = 1 Then
        GetColumn_ToNum = Asc(Alpha) Mod 32
        Exit Function
    End If
    
    Dim i As Long
    For i = 1 To Len(Alpha)
        GetColumn_ToNum = GetColumn_ToNum * 26 + (Asc(Mid(Alpha, i, 1)) Mod 32)
    Next
End Function


まとめ

今回も思った以上に大作になってしまいましたが、これで心置きなく列番号の変換ができるようになりました。

も~~~っと高速で出来る方法をご存知の方は、是非教えてくださいね!


さて、ここまで書いておいて、こういう式を探しに来た人のために一つだけ注意を。

大抵の場合、列番号なんて変換しなくてもそのまま動かす方法があります。

Range("A1")だったら、Cells(1,1)と書けば済む話です。

それにCells(1,"A")と書くこともできます。

ワークシート関数ならR1C1書式を使えば整数のまま処理できます。

=INDIRECT("R1C"&1, FALSE)

フォーミュラに書き出す場合もR1C1形式を使えば問題ありません。

Cells(1,1).FormulaR1C1 = "=R1C" & "1"

それを踏まえた上で、必要な時はここで紹介したような式を使っていただければと思います。

以上


何か御座いましたらコメント欄、またはTwitterからどうぞ♪

それではまた来週♪ ちゅんちゅん(・8・)

プライバシーポリシー