0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

エクセルのA1形式の列を0ベースの10進数にする

Posted at

エクセルA1の列を0ベースの10進数にしたい

なぜ?

エクセルでデータ見ているのに、pandasのusecolsで絞りたいときは0ベースの10進数を使う。
いちいち「これって…10進数で言うと何?」ってなる時がある。

実装例

def xlcol_to_deci(string:str, zero_base:bool=True)->int:
    # アルファベット列挙する ['A', 'B', 'C', ...'Z']
    alphabet_order = [ chr(i) for i in range(65, 91) ]
    # 対応表を作る {'A':1, 'B':2, 'C':3, ...'Z':26}
    table = { chara:i for i,chara in enumerate(alphabet_order,1) }
    # 文字列を逆転させ、1桁目から変換を始める (enumerateがstepを書けず、iを活用できないため)
    # 対応数値 * 26 ** i 
       # iは文字列の長さ毎に増えていく。 最初は0なので26**0 = 1となり、対応数値 * 1
       # 3文字目はi=2で、対応数値 * 26**2
    sum_ = sum([ table[one_letter] * (26**i) for i, one_letter in enumerate(reversed(string)) ])
    return sum_ - int(zero_base)

参考: アルファベットを列挙する
https://qiita.com/ell/items/6eb48e934a147898d823

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?