今回はExcelの列番号を高速で相互変換する関数・VBAの研究成果を発表します。
元ネタ
元々は以前私がツイートしたものです。
2019/12/15 関数のみで変換したい。
そう言えば昨晩、Excelの列番号(整数)から列文字列(AとかAAとかXFDとか)に変換する式を書こうとして断念したので、今日こそは上げます。
— ことりちゅん@えくせるちゅんちゅん (@KotorinChunChun) 2018年12月15日
26進数やろ?余裕やん?って思ったあなた!ぜひ挑戦してください。そんな甘くないです。
2019/12/17 大量のセルにAddressを書き込む。
今朝の件。10番煎じか100番煎じか知らないが、ちょっと試したいことがあったので下準備だけ。
— ことりちゅん@えくせるちゅんちゅん (@KotorinChunChun) 2018年12月14日
Cells←Cells.Addressは77s、描画切っても62s、二次元配列から代入すれば22s、Addressプロパティを使わなければ10s、数値型なら4.3s。Rangeに拘ると絶対超えられない壁がある。 pic.twitter.com/o2yFo9PAWo
2019/12/17 VBAの関数を最適化したい。
【整数をエクセル列番号を示すアルファベットに変換する関数】の比較結果
— ことりちゅん@えくせるちゅんちゅん (@KotorinChunChun) 2018年12月16日
なんと昔作ったDo~Loopさせる方法を最適化したコードが最速という結果になりました。
また、XFDどころかLong型の最大値である2,147,483,647=FXSHRXWまで対応できるという高い汎用性も備えており・・・(実用性皆無)
以上。 pic.twitter.com/cBugtgfil6
ここから更に改良を重ねて進化を遂げた、VBA史上最速の関数を発表することにしました。
列番号変換のテストパターン
式の作成にあたっては境界値分析が重要となってくるので、テストパターンを列挙しておきます。
以前紹介した「Excel VBAの自作関数の効率的なテスト方法について」を使うと便利です。
整数(_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
検証用コード
これに加えて、先日のストップウォッチクラスを使用します。
'一括テストとレポート出力 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))
こんな感じで、シート5
~1
の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・)