pythonの正規表現で「excelのセルの参照元」を調べたい【プログラミング初心者です】

前提

卒業論文でexcelのセルの参照元を調べる必要が出てきました。
しかし、あまりにも参照元が多く、地道に調べるのは大変なため、少しかじったことのあるpythonで調べたいと思いました。
初歩的な質問かもしれませんが、よろしくお願いします。

実現したいこと

pythonでexcelのセルの参照元を調べたいです。
具体的に言うと、N43のセルが

N43
=IF(K43="-","-",IF(NOT(ISNUMBER(I43)),"",IF(OR(AND(J43="≤",ROUND(I43,1)<=K43),AND(J43="≥",ROUND(I43,1)>=K43)),Data!$C$273,Data!$C$272)))

となっている場合、

["K43", "I43", "J43", "C273", "C272"]

と表示できるようにしたいです。

このなかで、"$"のついていない"K43", "I43", "J43"については正規表現で表示できましたが、"$"のついている"$C$273", "$C$272"について表示できなくて困っています。

最終的には、求めることができたセルに対しても同様にさらにその参照元を調べるために、再帰させようと思っています。以下のコードの後半はそのための準備になっています。そこに関してもアドバイスがあればいただきたいです。

発生している問題・エラーメッセージ

【ご報告】ご指摘をいただき、誤字の修正をした結果、下記エラーが出るようになりました。
検証をこれから行いますが、ご助力いただければ幸いです。

ValueError: 272 is not in range

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "“●●●●●\●●●●●●●●●●\●●●●●●\●●●●●●●●●●●●”", line 70, in <module>
    print(get_duplicate_list_order(map1(link_search_01, new_ret1)))
  File ““●●●●●\●●●●●●●●●●\●●●●●●\●●●●●●●●●●●●””, line 64, in map1
    return [link_search_01(load_sheet1,new_ret1[0])] + map1(link_search_01,new_ret1[1:])
  File ““●●●●●\●●●●●●●●●●\●●●●●●\●●●●●●●●●●●●””, line 64, in map1
    return [link_search_01(load_sheet1,new_ret1[0])] + map1(link_search_01,new_ret1[1:])
  File ““●●●●●\●●●●●●●●●●\●●●●●●\●●●●●●●●●●●●””, line 64, in map1
    return [link_search_01(load_sheet1,new_ret1[0])] + map1(link_search_01,new_ret1[1:])
  [Previous line repeated 6 more times]
  File ““●●●●●\●●●●●●●●●●\●●●●●●\●●●●●●●●●●●●\●●●●●●●●●””, line 40, in link_search_01
    text = dfs[sname.index(ws)].loc[cell_no-1,cell_alpha]
  File ““●●●●●\●●●●●●●●●●\●●●●●●\●●●●●●●●●●●●\●●●●●●●●●””, line 925, in __getitem__
    return self._getitem_tuple(key)
  File ““●●●●●\●●●●●●●●●●\●●●●●●\●●●●●●●●●●●●\●●●●●●●●●””, line 1100, in _getitem_tuple
    return self._getitem_lowerdim(tup)
  File ““●●●●●\●●●●●●●●●●\●●●●●●\●●●●●●●●●●●●\●●●●●●●●●””, line 838, in _getitem_lowerdim
    section = self._getitem_axis(key, axis=i)
  File ““●●●●●\●●●●●●●●●●\●●●●●●\●●●●●●●●●●●●\●●●●●●●●●””, line 1164, in _getitem_axis
    return self._get_label(key, axis=axis)
  File ““●●●●●\●●●●●●●●●●\●●●●●●\●●●●●●●●●●●●\●●●●●●●●●””, line 1113, in _get_label
    return self.obj.xs(label, axis=axis)
  File "●●●●●\●●●●●●●●●●\●●●●●●\●●●●●●●●●●●●\●●●●●●●●●\●●●●●●", line 3776, in xs
    loc = index.get_loc(key)
  File " ●●●●●\●●●●●●●●●●\●●●●●●\●●●●●●●●●●●●\●●●●●●●●●\●●●●●●\●●●●●●", line 387, in get_loc
    raise KeyError(key) from err
KeyError: 272

該当のソースコード

python
1from openpyxl import load_workbook
2import docx
3import requests
4import pandas as pd, openpyxl as xl, re, itertools
5from string import ascii_uppercase
6
7excel_path="●●●●●\●●●●●●●●●●\●●●●●●\●●●●●●●●●●●●"
8workbook = load_workbook(filename=excel_path, read_only=True)
9
10load_sheet1 = input("調べたいシート :")
11sheet1 = workbook[load_sheet1]
12load_cell1 = input("調べたいセル :")
13
14# 取得した値の表示
15if load_cell1 == "" :
16    print("null")
17else :
18    print(load_sheet1,load_cell1,sheet1[load_cell1].value)
19
20dfs = []
21sname = []
22
23def iter_all_strings():
24    for size in itertools.count(1):
25        for s in itertools.product(ascii_uppercase, repeat=size):
26            yield ''.join(s)
27
28for ws in workbook.worksheets:
29    df = pd.DataFrame(ws.values)
30    clst = [] 
31    for s in itertools.islice(iter_all_strings(), df.shape[1]):
32        clst.append(s)
33    df.columns = clst 
34    dfs.append(df)
35    sname.append(ws.title)
36
37def link_search_01(ws, cell):
38    cell_no = int(re.sub(r'\D', '', cell)) 
39    cell_alpha = re.sub(r'(\W|\d)', '', cell)
40    text = dfs[sname.index(ws)].loc[cell_no-1,cell_alpha]     #セルの計算式
41    #------------------------------------------------------------------------------------
42    # 以下の1行を付け加えた
43    #------------------------------------------------------------------------------------
44    new_text = re.sub("\$" , "", text)         
45
46    results_01 = [] 
47    regex1 = re.compile(r'[A-Z]+\d+') 
48    #------------------------------------------------------------------------------------
49    #results_01.append(regex1.findall(text))
50    #------------------------------------------------------------------------------------
51    results_01.append(regex1.findall(new_text))
52    ret1 = list(itertools.chain.from_iterable(results_01)) 
53    return(ret1)
54    print(ret1)
55
56link_search_01(load_sheet1,load_cell1)
57
58new_ret1 = link_search_01(load_sheet1,load_cell1)
59
60def get_duplicate_list_order(seq):
61    seen = []
62    return [x for x in seq if seq.count(x) > 1 and not seen.append(x) and seen.count(x) == 1]
63print(get_duplicate_list_order(new_ret1))
64
65def map1(link_search_01, new_ret1):
66    if new_ret1 == []:
67        return []
68    else:
69        return [link_search_01(load_sheet1,new_ret1[0])] + map1(link_search_01,new_ret1[1:])
70
71def get_duplicate_list_order(seq):
72    seen = []
73    return [x for x in seq if seq.count(x) > 1 and not seen.append(x) and seen.count(x) == 1]
74
75print(get_duplicate_list_order(map1(link_search_01, new_ret1)))

python
1#今回のinputの内容
2load_sheet1 = ●●●●●
3load_cell1 = N43

python
1#結果
2 ['K43', 'I43', 'J43']
3 [['AI43', 'AQ43'], ['N27', 'N27'], []]

試したこと

上記コードにコメントアウトしたように、元のセルの計算式から"$"を省き、それをもとに正規表現でセルを調べようとしたのですが、コメントアウト部が思ったように稼働しませんでした。
コメントアウト部をなくすと、"$"なしのセルは正しく表示されます。

※誤字を修正した結果、エラーがでるようになりました。
検証はこれから行いますが、ご助力いただければ幸いです。

can110

2022/09/29 06:13

正規表現"\$ "の最後に半角空白が含まれていますが、意図通りでしょうか（誤字？）

minato007

2022/09/29 06:23

失礼しました。誤字です。修正しました。

can110

2022/09/29 06:34

修正したら、ちゃんと'C273', 'C272`も抽出できていないでしょうか？

minato007

2022/09/29 06:39

エラーが出るようになってしまいました。

can110

2022/09/29 07:04

検証結果が出ましたら追記ください。 pd.DataFrame(ws.values)だとセル値のない部分は読み込まれないのでエラーのとおり範囲外アクセスしそうですが。

minato007

2022/09/29 16:30

Noneとなっているセルがあるため、修正ができないのだと思います。そのうえで、どのようにすればよいのかがわかりません。 nanであれば、同じ欠損値でも扱いやすそうに感じていますが、調べた限りnanからNoneにする方法ばかりで、Noneからnanにする方法はわかりませんでした。

can110

2022/09/30 04:14

「ValueError: 272 is not in range」なのでそのセルにはアクセスできません。範囲内か？セル値はあるか？計算式か？をチェックして、そうでなければ処理を終了するようにすべきかと思います。

回答1件

ベストアンサー

コードにしてみました。
同一ブック内の別シート参照まで再帰的に参照セルを探します。
別ブックへの参照はそれ以上検索しません。（openpyxlでは正しくブックパスが取得できない模様）
なお、計算式の解析はParsing Formulasを参考にしました。

Python
1from typing import Iterable
2import re
3from openpyxl import load_workbook
4from openpyxl.formula import Tokenizer
5
6
7# セル範囲を表現する文字列からセルを識別するキー
8# [親ディレクトリ名, ブック名, シート名, セル名]を得る
9def get_key(exp):
10    # 'C:hoge\[test2.xlsx]Sheet1'!$A$1
11    m = re.match(r"'(.+?)\[(.+?)\](.+?)'\!(.+)", exp)
12    if m:
13        return list(m.groups())
14    # [test2.xlsx]Sheet1!$A$1
15    m = re.match(r"\[(.+?)\](.+?)\!(.+)", exp)
16    if m:
17        return [''] + list(m.groups())
18    # Sheet1!A1 Sheet1!#REF!
19    m = re.match(r"(.+?)\!(.+)", exp)
20    if m:
21        return ['',''] + list(m.groups())
22    # A1
23    return ['','','',exp]
24
25
26# 計算式に含まれる参照セル（を示すキー）リストを取得
27def get_ref_cells(ws_name, formula):
28    tok = Tokenizer(formula)
29    ref_cells = []
30    for t in tok.items:
31        if t.type == 'OPERAND' and t.subtype == 'RANGE':
32
33            key = get_key(t.value)
34
35            # シート名がなければセット
36            if not key[2]:
37                key[2] = ws_name
38
39             # 絶対参照の正規化
40            key[3] = key[3].replace('$', '')
41
42            ref_cells.append(tuple(key))
43
44    return ref_cells
45
46
47# セル（範囲）を一次元リストに展開
48# 単一セル(A1)    A1
49# 行列範囲(A1:B2) ((A1,B1),(A2,B2))
50# 行範囲  (1:1)   (A1,A2,A3) 
51def flatten(cells):
52    if isinstance(cells,Iterable):    # セル範囲
53        tmp = []
54        for row in cells:
55            if isinstance(row,Iterable):
56                tmp += row
57            else:
58                tmp += [row]
59        cells = tmp
60    else:                           # 単一セル A1
61        cells = [cells]
62
63    return cells
64
65
66# 指定された単一セルorセル範囲の計算式から参照セルを取得
67def get_refs(wb, cells, results):
68
69    cells = flatten(cells)
70    for cell in cells:
71        ws_name = cell.parent.title
72        key = ('','',ws_name, cell.coordinate) # 各セルを識別するキー
73
74        # 計算式で未取得なら式内の参照セルを取得
75        if cell.data_type != 'f' or key in results:
76            continue
77        formula = cell.value
78        ref_cells = get_ref_cells(ws_name, formula)
79
80        results[key] = {'formula':formula, 'refs':ref_cells}
81
82        # 再帰取得
83        for ref_cell in ref_cells:
84            # このブック内で参照エラーではないセル
85            dir_name, wb_name, ws_name, coord = ref_cell
86            if not dir_name and not wb_name and not coord.startswith('#'):
87                try:
88                    cur_refs = wb[ws_name][coord]
89                    get_refs(wb, cur_refs, results)
90                except AttributeError as e:
91                    print(e)
92                    pass
93
94
95
96# 「A:A」のような列全体も取得するにはread_only=Falseである必要あり
97# Iterate through columns in Read-only workbook in openpyxl
98# https://stackoverflow.com/questions/47582274/iterate-through-columns-in-read-only-workbook-in-openpyxl
99wb = load_workbook('test.xlsx', read_only=False)
100
101cells = wb['Sheet1']['B1:B20'] # 取得範囲セル
102results = dict()
103get_refs(wb, cells, results)
104
105for c in results.items():
106    print(c)

.xlsx例

	A	B	C	D	E	F	G
0	実値	123
1	単純な計算式	=C2	123
2	別シートを参照	=Sheet2!A1
3	多段で参照	=C4+D4+E4	=D4+E4	=E4	1
4	参照先が行範囲	=SUM(Sheet2!A1:E1)
5	参照先が列範囲	=SUM(Sheet2!A1:A5)
6	参照先が行全体	=SUM(Sheet2!1:1)
7	参照先が列全体	=SUM(Sheet2!A:A)
8	参照先が行×列範囲	=SUM(D9:E11)		=F9	=G9	12.0	34.0
9	参照先が飛びセル	=SUM(C10,E10)	12		34
10	参照元が結合セル	=D11+E11		=F11	=G11	56.0	78.0
11	参照先が結合セル	=D12		123
12	両方が結合セル	=D13		456
13	削除された別シート	=#REF!
14	参照先が範囲外	=SUM(Sheet2!#REF!)
15	参照先が別ブック	='C:hoge[test2.xlsx]Sheet1'!$A$1
16	NAME?	=SUMM(A1)

結果例

PlainText
1(('', '', 'Sheet1', 'B2'), {'formula': '=C2', 'refs': [('', '', 'Sheet1', 'C2')]})
2(('', '', 'Sheet1', 'B3'), {'formula': '=Sheet2!A1', 'refs': [('', '', 'Sheet2', 'A1')]})
3(('', '', 'Sheet1', 'B4'), {'formula': '=C4+D4+E4', 'refs': [('', '', 'Sheet1', 'C4'), ('', '', 'Sheet1', 'D4'), ('', '', 'Sheet1', 'E4')]})
4(('', '', 'Sheet1', 'C4'), {'formula': '=D4+E4', 'refs': [('', '', 'Sheet1', 'D4'), ('', '', 'Sheet1', 'E4')]})
5(('', '', 'Sheet1', 'D4'), {'formula': '=E4', 'refs': [('', '', 'Sheet1', 'E4')]})
6(('', '', 'Sheet1', 'B5'), {'formula': '=SUM(Sheet2!A1:E1)', 'refs': [('', '', 'Sheet2', 'A1:E1')]})
7(('', '', 'Sheet1', 'B6'), {'formula': '=SUM(Sheet2!A1:A5)', 'refs': [('', '', 'Sheet2', 'A1:A5')]})
8(('', '', 'Sheet1', 'B7'), {'formula': '=SUM(Sheet2!1:1)', 'refs': [('', '', 'Sheet2', '1:1')]})
9(('', '', 'Sheet1', 'B8'), {'formula': '=SUM(Sheet2!A:A)', 'refs': [('', '', 'Sheet2', 'A:A')]})
10(('', '', 'Sheet1', 'B9'), {'formula': '=SUM(D9:E11)', 'refs': [('', '', 'Sheet1', 'D9:E11')]})
11(('', '', 'Sheet1', 'D9'), {'formula': '=F9', 'refs': [('', '', 'Sheet1', 'F9')]})
12(('', '', 'Sheet1', 'E9'), {'formula': '=G9', 'refs': [('', '', 'Sheet1', 'G9')]})
13(('', '', 'Sheet1', 'D11'), {'formula': '=F11', 'refs': [('', '', 'Sheet1', 'F11')]})
14(('', '', 'Sheet1', 'E11'), {'formula': '=G11', 'refs': [('', '', 'Sheet1', 'G11')]})
15(('', '', 'Sheet1', 'B10'), {'formula': '=SUM(C10,E10)', 'refs': [('', '', 'Sheet1', 'C10'), ('', '', 'Sheet1', 'E10')]})
16(('', '', 'Sheet1', 'B11'), {'formula': '=D11+E11', 'refs': [('', '', 'Sheet1', 'D11'), ('', '', 'Sheet1', 'E11')]})
17(('', '', 'Sheet1', 'B12'), {'formula': '=D12', 'refs': [('', '', 'Sheet1', 'D12')]})
18(('', '', 'Sheet1', 'B13'), {'formula': '=D13', 'refs': [('', '', 'Sheet1', 'D13')]})
19(('', '', 'Sheet1', 'B14'), {'formula': '=#REF!', 'refs': []})
20(('', '', 'Sheet1', 'B15'), {'formula': '=SUM(Sheet2!#REF!)', 'refs': [('', '', 'Sheet2', '#REF!')]})
21(('', '', 'Sheet1', 'B16'), {'formula': '=[1]Sheet1!$A$1', 'refs': [('', '1', 'Sheet1', 'A1')]})
22(('', '', 'Sheet1', 'B17'), {'formula': '=SUMM(A1)', 'refs': [('', '', 'Sheet1', 'A1')]})