PythonでExcelスプレッドシートの数式を計算するプログラムの作成方法

2次元の行列がExcelスプレッドシートを表していると仮定します。このとき、すべてのセルと数式を計算し終えた状態の同じ行列を求める必要があります。Excelスプレッドシートは一般的に以下のような構造を持っています。

列には「A、B、C…」、行には「1、2、3…」という名前が付けられています。各セルには、値そのもの、別のセルへの参照、あるいは数値やセル参照を使った演算を表すExcel数式のいずれかが格納されます(例:「=A1+5」「=A2+B2」「=2+5」など)。

入力と出力の例

たとえば、入力が以下のような場合を考えてみましょう。

このとき、期待される出力は次の通りです。

これは、B1 = 7(1行目・2列目のセル)であり、「=A1+A2」は A1 + A2 = 7 + 3 = 10 と計算されるためです。

解決のためのアプローチ

この問題を解くためには、以下の手順に従います。

• resolve()関数を定義します。引数として文字列 s を受け取ります。
  • s が数値に変換できる場合は、整数として返します。
  • 変換できない場合は、solve(getIdx(s)) の結果を返します。
• getIdx()関数を定義します。引数として文字列 s を受け取ります。
  • 1つ目の要素が「s の1文字目以降を整数化した値(行番号)」、2つ目の要素が「s[0] のASCIIコードから 'A' のASCIIコードを引いた値(列番号)」であるリストを返します。
• do()関数を定義します。引数として a、b、op を受け取ります。
  • op が「+」なら a + b を返します。
  • op が「-」なら a - b を返します。
  • op が「*」なら a * b を返します。
  • op が「/」なら a / b を返します。
• solve()関数を定義します。引数として行番号 i、列番号 j を受け取ります。
  • matrix[i][j] が数値であれば、その値をそのまま返します。
  • 数値でない場合は以下のように処理します。
    • s := matrix[i][j] とします。
    • s[0] が「=」である場合(数式セルの場合):
      • s[2:] の各文字 c について調べます。
      • c が演算子(+、-、/、*)のいずれかであれば、op := c としてループを抜けます。
      • [a, b] := s[1:] を op で分割した結果とします。
      • [aRes, bRes] := [resolve(a), resolve(b)] として両辺を解決します。
      • do(aRes, bRes, op) の結果を返します。
    • それ以外の場合(セル参照の場合):
      • solve(getIdx(s)) の結果を返します(参照先のセルを再帰的に解決)。
• i を 0 から matrix の行数まで、j を 0 から列数まで繰り返しながら、matrix[i][j] := str(solve(i, j)) で更新します。
• 最後に matrix を返します。

Pythonでの実装例

理解を深めるために、以下の実装例を見てみましょう。

class Solution:
   def solve(self, matrix):
      def resolve(s):
         try:
            return int(s)
         except:
            return solve(*getIdx(s))

      def getIdx(s):
         return [int(s[1:]) - 1, ord(s[0]) - ord("A")]

      def do(a, b, op):
         if op == "+":
            return a + b
         if op == "-":
            return a - b
         if op == "*":
            return a * b
         if op == "/":
            return a / b

      def solve(i, j):
         try:
            return int(matrix[i][j])
         except:
            s = matrix[i][j]
            if s[0] == "=":
               for c in s[2:]:
                  if c in "+-/*":
                     op = c
                     break
               a, b = s[1:].split(op)
               aRes, bRes = resolve(a), resolve(b)
               return do(aRes, bRes, op)
            else:
               return solve(*getIdx(s))

      for i in range(len(matrix)):
         for j in range(len(matrix[0])):
            matrix[i][j] = str(solve(i, j))

      return matrix

ob = Solution()
matrix = [
   ["B1", "7", "0"],
   ["3", "5", "=A1+A2"]
]
print(ob.solve(matrix))

入力

[["B1", "7", "0"],
["3", "5", "=A1+A2"]]

出力

[['7', '7', '0'],
['3', '5', '10']]

処理のポイント

この実装の重要なポイントは、再帰的なセル参照の解決です。resolve() 関数と solve() 関数が互いに呼び出し合うことで、「=A1+A2」のような数式中のセル参照(A1 や A2)が実際の値に置き換えられるまで再帰的に辿っていきます。これにより、セルが別のセルを参照し、さらにそのセルが別の数式を含んでいるような多段階の依存関係でも正しく計算できます。

また、getIdx() 関数では Excel 特有のセルアドレス形式(列名は英字、行番号は数字)を、プログラムで扱いやすい 0 始まりのインデックス(行番号、列番号)へと変換しています。ord(s[0]) - ord("A") によって、たとえば「B」は列インデックス 1 に対応付けられます。