動機
VBAでコードを書くとき、常々不満に思っていたことがあります。
それは、「Excel上では列名がアルファベット表記なのに、VBA上では何番目の列なのか指定する必要がある。これをいちいち照合するのは面倒くさい」ということです。
アルファベット表記の列名が、アルファベット26文字を使った単純な26進法(や、0にあたる表記を含めた27進法)になっていないのも面倒くさく感じる原因です。
その対策として作ったのが、今回の変換ツールです。
VBAでの困りごとなのでVBAで解決するのが筋だとは思います。
にもかかわらずPythonを使ったのも、コードゴルフをしたのも、単なるその場のノリで、要は何となくです。
ちなみに後述しますが、コマンドライン引数では半角スペースを入力できない、という性質を利用したコードなので、今回のコードをそのままVBAにしても、半角スペース入力がエラーにならずに処理されるという不具合が発生します。
要件
- アルファベット(大文字・小文字問わず)で列名を入力すると、対応する列番号が出力される。(例：aA→27)
- 1番の逆向きの動作も実行できる。(例：27→AA)
- 入力エラーを検知し、エラーの種類が確認できる形で出力する。ただしエラーの種類は以下を想定。
- 未入力(複数入力時は最初の入力を採用し、エラーとしない)
- 不適切な文字列入力
- 不適切な数値入力
実際のコード
import sys
def E(n):print('E-'+str(n));exit()
try:s=sys.argv[1]
except:E(1)
a=' ABCDEFGHIJKLMNOPQRSTUVWXYZ';m=2**14;l=26;x=len(s);s=(3-x)*'0'+s.upper();n=0
try:n=int(s)-1
except:
for c in a:z=s[0]==c;o=s[1]==c;t=s[2]==c;x-=z+o+t;n+=a.index(c)*(l*(l*z+o)+t)
if x+(m<n):E(2)
print(n);exit()
if n*(m-n-1)<0:E(3)
k=n//l-1;n%=l;print((a[k//l]+a[k%l+1])*(k>=0)+a[n+1])
なお、記事タイトルの365バイトとは、改行文字を1バイトとしたときのファイルサイズです。念のため。
入出力の例
まず通常の入出力の例。アルファベット2文字の出力の場合、出力の頭に半角スペースが付いてしまいますが、許容範囲としました。
>python ExcelColNumAlph.py aA
27
>python ExcelColNumAlph.py 702
ZZ
>python ExcelColNumAlph.py 16384
XFD
次にエラーの例。Excelの最終列は2^14=16384列目なので、その範囲を超えるとエラーとなります。
>python ExcelColNumAlph.py 0
E-3
>python ExcelColNumAlph.py XFE
E-2
同内容で読みやすくしたコード
同等な処理を、なるべく読みやすく記述したコードです。
これを最初のコードと見比べて納得できる方は、解説を読まなくても大丈夫です。
# コマンドライン引数取得だけのために import
import sys
# エラー出力関数
def error(n):
print('E-' + str(n))
# エラーが出たら、どうせそれ以降の動作は実行できないので終了
exit()
# 最初のコマンドライン引数を取得、それがなければエラー
try:
str_input = sys.argv[1]
except:
error(1)
# 定数を定義
ARR_ALPHABET = ' ABCDEFGHIJKLMNOPQRSTUVWXYZ'
MAX_COLUMN = 2 ** 14
TOTAL_ALPHABET = 26
# 入力が整数でない場合に使うカウンタと数値出力を初期化
decr_counter = len(str_input)
num_output = 0
# 入力の整形(小文字は大文字に、2文字以下の入力は頭に0を付けて3文字に)
str_formatted = str_input.upper()
if (len(str_input) == 1):
str_formatted = '00' + str_formatted
if (len(str_input) == 2):
str_formatted = '0' + str_formatted
# 入力(を整形したもの)を整数として扱える場合は整数にする
is_num_input = False
try:
num_input = int(str_formatted)
is_num_input = True
except:
pass
# 入力が整数でない場合
if (not is_num_input):
# 全アルファベットに対する処理
for i, c in enumerate(ARR_ALPHABET):
# 3文字目までに一致するアルファベットがあればカウンタ減算、同時に出力に適切な値を加算
# str_formatted は最低3文字なので、入力が適切なら3文字目が必ず一番下の桁
if(str_formatted[0] == c):
decr_counter -= 1
num_output += i * (TOTAL_ALPHABET ** 2)
if(str_formatted[1] == c):
decr_counter -= 1
num_output += i * TOTAL_ALPHABET
if(str_formatted[2] == c):
decr_counter -= 1
num_output += i
# (半角英字でない文字があるか、入力が4文字以上) または (計算結果が最終列の列番号を超える) 場合エラー
if (decr_counter != 0) or (MAX_COLUMN < num_output):
error(2)
# 出力して終了
print(num_output)
# 入力が整数の場合
else:
# 1≦(入力値)≦m でない場合エラー
if (num_input < 1) or (MAX_COLUMN < num_input):
error(3)
# 1～3文字目に対応する ARR_ALPHABET 上のインデックスを導出
output_index = [0, 0, 0]
output_index[0] = (((num_input - 1) // TOTAL_ALPHABET) - 1) // TOTAL_ALPHABET
output_index[1] = (((num_input - 1) // TOTAL_ALPHABET) - 1) % TOTAL_ALPHABET + 1
output_index[2] = ((num_input - 1) % TOTAL_ALPHABET) + 1
# 上の式だと num_input <= 26 で破綻するので条件分岐
str_output = ''
if (((num_input - 1) // TOTAL_ALPHABET) < 1):
str_output = ARR_ALPHABET[output_index[2]]
else:
str_output = ARR_ALPHABET[output_index[0]] + ARR_ALPHABET[output_index[1]] + ARR_ALPHABET[output_index[2]]
# 出力して終了
print(str_output)
解説
- 1-4行目
import sys
def E(n):print('E-'+str(n));exit()
try:s=sys.argv[1]
except:E(1)
まずエラー関数を定義します。
エラーが出たら、どうせそれ以降の処理は実行できないので、exit()でプログラムを終了させるところまで盛り込みます。
コードゴルフは大抵の場合、エラーのことを考えないのですが、これは曲がりなりにも実用のために作っているコードなので、エラーのことも考えて実装しています。
次に、 sys で最初のコマンドライン引数を読み取ります。
コマンドライン引数がない場合は try に失敗したので 'E-1' エラーを出力しますが、コマンドライン引数が複数ある場合は2番目以降のコマンドライン引数を無視して実行を続けます。
- 5行目
a=' ABCDEFGHIJKLMNOPQRSTUVWXYZ';m=2**14;l=26;x=len(s);s=(3-x)*'0'+s.upper();n=0
これは、どうせなら行数も減らした方がコードゴルフっぽいと思って1行にした部分です。
しかし当然、改行文字を1バイトにする場合は、改行を含む次の表記(コメントを除く)とサイズは変わりません。
a=' ABCDEFGHIJKLMNOPQRSTUVWXYZ' # a[i]: i番目のアルファベット
m=2**14 # 最終列の列番号
l=26 # アルファベットの文字数(3回以上使われるので定数化した方が軽量)
x=len(s) # 入力の文字数 アルファベット入力時にカウンタとして使用
s=(3-x)*'0'+s.upper() # 入力の整形(小文字は大文字に、2文字以下の入力は頭に0を付けて3文字に)
n=0 # 数値の初期化
入力の整形の補足として、4桁以上の数値が入力されたときに(負の数)*(文字列)の処理が走ります。
調べた限り、この場合は空文字列を出力するらしく、実際に自分の環境ではそのように動いたので、この実装をしました。
おま環だったらすみません。
- 6-7行目
try:n=int(s)-1
except:
事実上のif-else文です。
入力(を整形したもの)を整数として扱うことができない場合、入力はアルファベットであると解釈し、 except 内を実行します。
入力(を整形したもの)を整数として扱うことができる場合、 except 内を無視します。
except の最後に exit() があるので、if側とelse側(except 内とその後の行)の両方が実行されることはありません。
- 8行目
for c in a:z=s[0]==c;o=s[1]==c;t=s[2]==c;x-=z+o+t;n+=a.index(c)*(l*(l*z+o)+t)
記事を書くにあたってタブ文字を入力できなかったので、半角スペース1個で代用しました。
これは5行目(定数 a, m, l の定義から始まる行)と違い、改行するとインデントが必要なのでファイルサイズが増えてしまう行です。
少し見やすくすると次の通り。
for c in a:
z = (s[0] == c)
x -= z
n += a.index(c) * (l * l * z)
o = (s[1] == c)
x -= o
n += a.index(c) * (l * o)
t = (s[2] == c)
x -= t
n += a.index(c) * t
例1: s が 'AAA' の場合、 c=='A' のときにのみ z,o,t がすべて True になります。このときカウンタ x は 0 になり、 n は3回加算されます。(元のコードでは「実質」3回加算と言うべきですが。)
例2: 入力が 'ab' の場合、まず x=len(s) で x が 2 になった後、整形で s が '0AB' となります。その後、この場所(8行目)で x は 0 になります。
一方このとき、 n は c=='A' のときに 1*26*True が、 c=='B' のときに 2*True が加算され、合計 28 になります。
ここで文字列 s に半角空白文字が入っていると、 a[0]=' ' である関係上、 x が半角空白文字の数だけデクリメントされてしまうので都合が悪いです。
しかし s は元々コマンドライン引数から取ってきたものであり、コマンドライン引数は半角空白区切りで分断されるため、文字列 s が半角空白文字を含んでしまうことはありません。
一方、VBAを使ったり、Pythonでも input 関数を使ったりすると、ここに半角空白文字関連のバグが残ってしまいます。
そのため、そういった場合は a[0] はキーボード入力や端末出力ができないような制御文字に置換するか、削除して a が関係する箇所を修正する必要があります。
- 9-10行目
if x+(m<n):E(2)
print(n);exit()
このif文によるエラーは x == 0 かつ m >= n のときのみ回避できます。
x == 0 とは、入力文字列が3文字以下かつすべて半角英字であることを意味します。
m >= n とは、直前のfor文による計算結果が最終列の列番号以下であることを意味します。
この場合のみ正常出力として計算結果を出力し、プログラムは終了します。
- 11行目
if n*(m-n-1)<0:E(3)
ここからは数値入力の場合です。
この行は2次関数を使ってエラーチェックをしています。
n*(m-n-1) は、 n について上に凸かつ零点を2個持つ2次関数なので、ある有限区間でのみ正の値を取ることがわかります。
この行をわかりやすく書くと、次の通り。
if (n < 0) or (m < n + 1): E(3)
6行目で n=int(s)-1 としたため、入力値と n には1だけズレがあるので、これで 1≦(入力値)≦m のチェックができます。
- 12行目
k=n//l-1;n%=l;print((a[k//l]+a[k%l+1])*(k>=0)+a[n+1])
5行目と同様に、改行してもファイルサイズは変わらないところを、コードゴルフっぽいという理由で1行にまとめたものです。
少し見やすくすると次の通り。
k = n // l - 1 # 1,2文字目の情報を取得。変数名kは余った文字から適当に決めた
n %= l # nを26で割った余りを計算し、nに上書き
output = a[k // l] # (上書き前の)nを26で2回割っているので1番上の桁
output = output + a[k % l + 1] # (上書き前の)nを26で割った後で剰余を取っているので上から2番目の桁
if (k < 0): output = '' # この初期化をしないと26以下の入力に対して'ZZA'などが出力される
output = output + a[n + 1] # 6行目(n=int(s)-1)で引いた1を戻して1番下の桁を算出
print(output)
さらに、各桁の出力を別個に計算する場合の、同じ処理をするコードに直してみます。
(12行目の代わりに下記を入れても同様に動く、という意味です。)
num_input = int(s)
output_index = [0, 0, 0]
output_index[0] = (((num_input - 1) // 26) - 1) // 26
output_index[1] = (((num_input - 1) // 26) - 1) % 26 + 1
output_index[2] = ((num_input - 1) % 26) + 1
if (((num_input - 1) // 26) < 1):
str_output = a[output_index[2]]
else:
str_output = a[output_index[0]] + a[output_index[1]] + a[output_index[2]]
print(str_output)
ここでのポイントは2つあります。
1つ目は、剰余や商の計算の前に1を引き、剰余の後で1を足していることです。
これは、26番目のアルファベット 'Z' を正しく取り扱うために必要な処理です。
一方、この処理のせいで 0 < num_input <= 26 に対して output_index[0], output_index[1] が26になってしまうので、その場合は output_index[2] だけを見る処理になっています。
2つ目のポイントは、 ((num_input - 1) // 26) - 1 (元のコードの k )が0以上26未満のとき、 output_index[0] = 0 すなわち a[output_index[0]] = ' ' になることです。
これによって、「半角空白文字を含む3文字なので実質2文字」という形式の出力が実現できます。
文字列 a が ' ' から始まっているのは、コマンドライン引数の入力から a[0] を取得しないようにするためと、許容できる形式で出力されるようにするためという、2つの理由があったのです。
おまけ：実際に使っているコード
正直なところ、実用を考えると、最初のコードでは出力が不親切な感じがあります。
なので、実務で活用しているのは最初のコードではなく、次のコードです。
import sys
def P(s):print('\n'+s)
def E(n):P('Error: '+str(n));exit()
try:s=sys.argv[1]
except:E(1)
a=' ABCDEFGHIJKLMNOPQRSTUVWXYZ';m=2**14;l=26;P('Input: '+s);x=len(s);s=(3-x)*'0'+s.upper();n=0
try:n=int(s)-1
except:
for c in a:z=s[0]==c;o=s[1]==c;t=s[2]==c;x-=z+o+t;n+=a.index(c)*(l*(l*z+o)+t)
if x+(m<n):E(2)
P('Col Num: '+str(n));exit()
if n*(m-n-1)<0:E(3)
k=n//l-1;n%=l;P('Col Name: '+(a[k//l]+a[k%l+1])*(k>=0)+a[n+1])
完全に自分用だとしても、このくらいは出力してほしいな、という内容になっています。
とはいえ、これは人間が読みやすい出力を考えたものなので、出力をさらに別のコードに投げる場合は明らかに最初のコードの方が適切です。
最初のコードとの違いは、ほぼ出力文字列だけです。
唯一 P(s) という関数が増えていますが、この場合はそうした方が軽量化できるというだけの理由で追加した関数です。