Excelの数式が複雑すぎてインデントしたいなと思ったときにやったこと

13306 ワード
Python Excel Openpyxl Python テキストリンク

概要

Excelの数式欄にかなりの長さの数式が埋まっており、読み解くにも修正するにもかなりの苦労が必要になってしまったので、ひとまず抜き出して解析を試みた話。

データの抜き出しには Openpyxl を利用しました。

こんな状況でした

Excelの数式に入れていい文字列は75文字までとか誰か決めてくれ・・・ｗ
（計算結果の途中をどっかのセルに吐き出すとか・・・ね。）

特に Excel の IF は読みにくい

=IF(A1="これこれ", "xxxx", IF(B1="あれこれ", "yyyy", "zzzz"))

こんな書き方、良くしますよね・・・。

if (A1 === "これこれ") {
   cell = "xxxx";
} elseif (B1 === "あれこれ") {
   cell = "yyyy";
} else {
   cell = "zzzz";
}

という感じなんですけど、独自関数定義するとマクロが・・・って話になりますし、リファクタリングも難しいです。なので、インデントしたりしてどうにか可読性をあげるしかないですね。

ちなみに Excel数式におけるインデントに、N（）関数を加えてコメントっぽく書くことができたり、配列数式を使ってコメントっぽく書く方法もあるようです・・・

→ 数式作成に便利なテクニック

今回は値の取り出し〜インデントを加えるところをやってみます

もちろんマクロを使って解決する方法もありますね。
→ ExcelでネストしたIf関数をVBAでインデントして分析しやすくする

Python でやってみる

今回、Openpyxlを使ってやってみます。

ずばり、数式の解析は Parsing Formula を使ってできるようです。

とりあえず書いてあるのを試してみましょう。

>>> from openpyxl.formula import Tokenizer
>>> tok = Tokenizer("""=IF(A1="これこれ", "xxxx", IF(B1="あれこれ", "yyyy", "zzzz"))""")
>>> print("\n".join("%12s%11s%9s" % (t.value, t.type, t.subtype) for t in tok.items))
         IF(       FUNC     OPEN
          A1    OPERAND    RANGE
           =OPERATOR-INFIX
      "これこれ"    OPERAND     TEXT
           ,        SEP      ARG
            WHITE-SPACE
      "xxxx"    OPERAND     TEXT
           ,        SEP      ARG
            WHITE-SPACE
         IF(       FUNC     OPEN
          B1    OPERAND    RANGE
           =OPERATOR-INFIX
      "あれこれ"    OPERAND     TEXT
           ,        SEP      ARG
            WHITE-SPACE
      "yyyy"    OPERAND     TEXT
           ,        SEP      ARG
            WHITE-SPACE
      "zzzz"    OPERAND     TEXT
           )       FUNC    CLOSE
           )       FUNC    CLOSE

なるほど。先頭からそれぞれのパーツが関数（開始・終了）なのか、変数なのか等々に分解してくれるようだ。

ということは関数が開始したら改行して、次の行にはインデントをつけ、関数が終了するときにインデントを減らしてから改行していけばよいということですね。

わかりやすさのためにインデントを可視化して書いてみる

from openpyxl.formula import Tokenizer
# あとでindent_charはもちろんスペースに直すよ
def parse(formula, indent_char = "_"):
    # 最終的に文字列として結合して返す
    result = ""
    tok = Tokenizer(formula)
    # インデント数
    n = 0
    # 直前で処理したトークン
    last = None

    for t in tok.items:
        if t.type == "FUNC":
            if t.subtype == "OPEN":
                if last is not None and (last.type == "FUNC" and last.subtype == "OPEN"):
                    n += 2
                    result += indent_char * n + t.value
                else:
                    result += t.value

                result += "\n"
            else:
                # function close。インデント数を戻す
                n -= 2
                result += "\n" + indent_char * n + t.value

        else:
            # それ以外は単純結合。直前が関数の場合はインデント
            if last is not None and (last.type == "FUNC" and last.subtype == "OPEN"):
                n += 2
                result += indent_char * n + t.value
            else:
                result += t.value

        last = t

    return result

s = """=IF(A1="これこれ", "xxxx", IF(B1="あれこれ", "yyyy", "zzzz"))"""

print(parse(s))

IF(
__A1="これこれ", "xxxx", IF(
____B1="あれこれ", "yyyy", "zzzz"
__)
)

うん。これで良さそう。

あとはExcelの指定したセル範囲とかを渡して

wb = load_workbook(filename, data_only = False)
sheet_name = "Sheet1"
cell_range = "A2:C2"
for line in wb[sheet_name][cell_range]:
    for cell in line:
        # formula のみを抽出
        if cell.data_type == "f":
            f = parse(cell.value)
            # cell.coordinateには「A1」などの表現が入る
            with open(cell.coordinate + ".txt", mode='w') as fl:
                fl.write(f)

こんな感じにすれば、数式が埋まっているセルだけ、A2.txt, C2.txtのようにインデント済のテキストとして吐き出せます。