[PowerShell] Excelの列名を列番号に変換する関数

Excelの列名は、通常「A」「AB」などアルファベット表示（A1形式）になっている。
Excelのオプション(※)を変更することによって「1」「28」など番号表示（R1C1形式）にもできる。

※ファイル＞オプション＞数式＞数式の処理＞R1C1参照形式を使用する

アルファベット表示（A1形式）

番号表示（R1C1形式）

普通にExcelを使っている分には、アルファベット表示の方が扱い易いけど、プログラムから列を指定する場合、数字の方が都合がいい。
例えば、A列からZZ列までループしたい場合など、for文で1から702(ZZ)まで回した方が楽。

ということで、列名をアルファベットから、番号に変換する関数をPowerShellで作ってみました。
ついでに、その逆の番号からアルファベットに変換する関数も。

サンプルソース。

sample.ps1

# Excelの列名を変換する関数
# アルファベット表示（AI形式）、数字表示（R1C1形式）の相互変換を行います。
# 使用例）
# ・英字から数字
#   $res = A1toR1C1("C")    ⇒ 3を返す
#   $res = A1toR1C1("AB")   ⇒28を返す
# ・数字から英字
#   $res = R1C1toA1(3)      ⇒ "C"を返す
#   $res = R1C1toA1(28)     ⇒"AB"を返す

Add-Type -AssemblyName Microsoft.VisualBasic

# 引数に指定した文字に対するASCIIコードを返す
# 例）"A"⇒65、"B"⇒66
function getAsciiCodeByChar($c){
    return [byte][char]$c
}

# 引数に指定したASCIIコードに対する文字を返す
# 例）65⇒"A"、66⇒"B"
function getCharByAsciiCode($a){
    return [System.Convert]::ToChar($a)
}

# 引数に指定した英文字を半角大文字に変換
function convertNarrowUpper($str){
    # 全角⇒半角
    $hc = [Microsoft.VisualBasic.Strings]::StrConv($str,[Microsoft.VisualBasic.VbStrConv]::Narrow)

    # 小文字⇒大文字
    $lc = $hc.ToUpper()

    return $lc
}

# 英字から数字に変換（A1⇒R1C1形式）
# 例）A⇒1、B⇒2、AA⇒27、AAA⇒703
function A1toR1C1($a1){
    # 小文字⇒大文字
    $A1 = convertNarrowUpper($a1)

    $val = 0
    for( $i=$A1.Length-1 ; $i -ge 0 ; $i--){
        $c = $A1.Substring($i, 1)
        $keta = $A1.Length-1-$i
        if($keta -eq 0){
            $val += (getAsciiCodeByChar($c))-64
        }else{
            $val += [Math]::Pow(26,$keta) * ((getAsciiCodeByChar($c))-64)
        }
    }
    return $val
}

# 数字から英字に変換（R1C1⇒A1形式）
# 例）1⇒A、2⇒B、27⇒AA、703⇒AAA、16384⇒XFD
function R1C1toA1($r1c1){
    $val = $r1c1 - 1
    $ret = $null
    if(($r1c1 -gt 0) -And ($r1c1 -le 26)){
        # A～Z
        $ret = getCharByAsciiCode($val+64+1)
    }elseif(($r1c1 -gt 26) -And ($r1c1 -le (26*26+26))){
        #AA～ZZ
        $c1 = [System.Convert]::ToInt32([Math]::Truncate($val / 26))
        $c2 = [System.Convert]::ToInt32([Math]::Truncate($val % 26)) + 1
        $ret  = getCharByAsciiCode($c1+64)
        $ret += getCharByAsciiCode($c2+64)
    }elseif(($r1c1 -gt (26*26+26)) -And ($r1c1 -le 18278)){
        # AAA～ZZZ
        $c1 = [System.Convert]::ToInt32([Math]::Truncate($val / (26 * 26)))
        $c2 = [System.Convert]::ToInt32([Math]::Truncate(($val - ($c1 * (26 * 26))) / 26))
        $c3 = [System.Convert]::ToInt32([Math]::Truncate($val % 26)) + 1
        $ret  = getCharByAsciiCode($c1 + 64)
        $ret += getCharByAsciiCode($c2 + 64)
        $ret += getCharByAsciiCode($c3 + 64)
    }
    return $ret
}

# テスト
$r1c1 = A1toR1C1("AB")  # 28
Write-Host $r1c1

$wk = R1C1toA1(731)     # ABC
Write-Host $wk

実行すると、以下の結果となる。
ちなみに実行は、IDEの▶ボタンで実行できる。

PS C:\> C:\sample.ps1
28
ABC

“AB”⇒28に、731⇒”ABC”に変換される。
これ、一応Excel上限の”XFD”列まで変換できます。
上限越えの”ZZZ”列まで変換可能。

おまけ

PowerShellを初めて実行すると、以下のようなメッセージが表示されます。

PS C:\> C:\sample.ps1
このシステムではスクリプトの実行が無効になっているため、ファイル C:\sample.ps1 を読み込むことができません。詳細については、「about_Execution_Policies」(https://go.microsoft.com/fwlink/?LinkID=135170) を参照してください。
    + CategoryInfo          : セキュリティ エラー: (: ) []、ParentContainsErrorRecordException
    + FullyQualifiedErrorId : UnauthorizedAccess

この場合、てっとり早く実行するには、「-ExecutionPolicy RemoteSigned」を付加すると実行できる。

powershell -ExecutionPolicy RemoteSigned .\sample.ps1

でも、毎回「-ExecutionPolicy RemoteSigned」を付加するのは面倒。
PowerShell ISEでデバッグしたり、頻繁に利用する場合は以下で設定変更しておくと便利。

管理者モードでWindows PowerShellを実行

以下のコマンドを実行

Set-ExecutionPolicy RemoteSigned

Windows PowerShell
Copyright (C) Microsoft Corporation. All rights reserved.

新しいクロスプラットフォームの PowerShell をお試しください https://aka.ms/pscore6

PS C:\WINDOWS\system32> Set-ExecutionPolicy RemoteSigned

実行ポリシーの変更
実行ポリシーは、信頼されていないスクリプトからの保護に役立ちます。実行ポリシーを変更すると、about_Execution_Policies
のヘルプ トピック (https://go.microsoft.com/fwlink/?LinkID=135170)
で説明されているセキュリティ上の危険にさらされる可能性があります。実行ポリシーを変更しますか?
[Y] はい(Y)  [A] すべて続行(A)  [N] いいえ(N)  [L] すべて無視(L)  [S] 中断(S)  [?] ヘルプ (既定値は "N"): Y