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

Question

前提

卒業論文で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

該当のソースコード

from openpyxl import load_workbook
import docx
import requests
import pandas as pd, openpyxl as xl, re, itertools
from string import ascii_uppercase

excel_path="●●●●●\●●●●●●●●●●\●●●●●●\●●●●●●●●●●●●"
workbook = load_workbook(filename=excel_path, read_only=True)

load_sheet1 = input("調べたいシート :")
sheet1 = workbook[load_sheet1]
load_cell1 = input("調べたいセル :")

# 取得した値の表示
if load_cell1 == "" :
    print("null")
else :
    print(load_sheet1,load_cell1,sheet1[load_cell1].value)

dfs = []
sname = []

def iter_all_strings():
    for size in itertools.count(1):
        for s in itertools.product(ascii_uppercase, repeat=size):
            yield ''.join(s)

for ws in workbook.worksheets:
    df = pd.DataFrame(ws.values)
    clst = [] 
    for s in itertools.islice(iter_all_strings(), df.shape[1]):
        clst.append(s)
    df.columns = clst 
    dfs.append(df)
    sname.append(ws.title)

def link_search_01(ws, cell):
    cell_no = int(re.sub(r'\D', '', cell)) 
    cell_alpha = re.sub(r'(\W|\d)', '', cell)
    text = dfs[sname.index(ws)].loc[cell_no-1,cell_alpha]     #セルの計算式
    #------------------------------------------------------------------------------------
    # 以下の1行を付け加えた
    #------------------------------------------------------------------------------------
    new_text = re.sub("\$" , "", text)         

    results_01 = [] 
    regex1 = re.compile(r'[A-Z]+\d+') 
    #------------------------------------------------------------------------------------
    #results_01.append(regex1.findall(text))
    #------------------------------------------------------------------------------------
    results_01.append(regex1.findall(new_text))
    ret1 = list(itertools.chain.from_iterable(results_01)) 
    return(ret1)
    print(ret1)

link_search_01(load_sheet1,load_cell1)

new_ret1 = link_search_01(load_sheet1,load_cell1)

def get_duplicate_list_order(seq):
    seen = []
    return [x for x in seq if seq.count(x) > 1 and not seen.append(x) and seen.count(x) == 1]
print(get_duplicate_list_order(new_ret1))

def map1(link_search_01, new_ret1):
    if new_ret1 == []:
        return []
    else:
        return [link_search_01(load_sheet1,new_ret1[0])] + map1(link_search_01,new_ret1[1:])

def get_duplicate_list_order(seq):
    seen = []
    return [x for x in seq if seq.count(x) > 1 and not seen.append(x) and seen.count(x) == 1]

print(get_duplicate_list_order(map1(link_search_01, new_ret1)))

#今回のinputの内容
load_sheet1 = ●●●●●
load_cell1 = N43

#結果
 ['K43', 'I43', 'J43']
 [['AI43', 'AQ43'], ['N27', 'N27'], []]

試したこと

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

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

Comments

[can110]

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

[minato007]

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

[can110]

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

[minato007]

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

[can110]

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

[minato007]

Noneとなっているセルがあるため、修正ができないのだと思います。
そのうえで、どのようにすればよいのかがわかりません。

nanであれば、同じ欠損値でも扱いやすそうに感じていますが、調べた限りnanからNoneにする方法ばかりで、Noneからnanにする方法はわかりませんでした。

[can110]

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

Answer 1

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

from typing import Iterable
import re
from openpyxl import load_workbook
from openpyxl.formula import Tokenizer


# セル範囲を表現する文字列からセルを識別するキー
# [親ディレクトリ名, ブック名, シート名, セル名]を得る
def get_key(exp):
    # 'C:hoge\[test2.xlsx]Sheet1'!$A$1
    m = re.match(r"'(.+?)\[(.+?)\](.+?)'\!(.+)", exp)
    if m:
        return list(m.groups())
    # [test2.xlsx]Sheet1!$A$1
    m = re.match(r"\[(.+?)\](.+?)\!(.+)", exp)
    if m:
        return [''] + list(m.groups())
    # Sheet1!A1 Sheet1!#REF!
    m = re.match(r"(.+?)\!(.+)", exp)
    if m:
        return ['',''] + list(m.groups())
    # A1
    return ['','','',exp]


# 計算式に含まれる参照セル（を示すキー）リストを取得
def get_ref_cells(ws_name, formula):
    tok = Tokenizer(formula)
    ref_cells = []
    for t in tok.items:
        if t.type == 'OPERAND' and t.subtype == 'RANGE':

            key = get_key(t.value)

            # シート名がなければセット
            if not key[2]:
                key[2] = ws_name

             # 絶対参照の正規化
            key[3] = key[3].replace('$', '')

            ref_cells.append(tuple(key))

    return ref_cells


# セル（範囲）を一次元リストに展開
# 単一セル(A1)    A1
# 行列範囲(A1:B2) ((A1,B1),(A2,B2))
# 行範囲  (1:1)   (A1,A2,A3) 
def flatten(cells):
    if isinstance(cells,Iterable):    # セル範囲
        tmp = []
        for row in cells:
            if isinstance(row,Iterable):
                tmp += row
            else:
                tmp += [row]
        cells = tmp
    else:                           # 単一セル A1
        cells = [cells]

    return cells


# 指定された単一セルorセル範囲の計算式から参照セルを取得
def get_refs(wb, cells, results):

    cells = flatten(cells)
    for cell in cells:
        ws_name = cell.parent.title
        key = ('','',ws_name, cell.coordinate) # 各セルを識別するキー

        # 計算式で未取得なら式内の参照セルを取得
        if cell.data_type != 'f' or key in results:
            continue
        formula = cell.value
        ref_cells = get_ref_cells(ws_name, formula)

        results[key] = {'formula':formula, 'refs':ref_cells}

        # 再帰取得
        for ref_cell in ref_cells:
            # このブック内で参照エラーではないセル
            dir_name, wb_name, ws_name, coord = ref_cell
            if not dir_name and not wb_name and not coord.startswith('#'):
                try:
                    cur_refs = wb[ws_name][coord]
                    get_refs(wb, cur_refs, results)
                except AttributeError as e:
                    print(e)
                    pass



# 「A:A」のような列全体も取得するにはread_only=Falseである必要あり
# Iterate through columns in Read-only workbook in openpyxl
# https://stackoverflow.com/questions/47582274/iterate-through-columns-in-read-only-workbook-in-openpyxl
wb = load_workbook('test.xlsx', read_only=False)

cells = wb['Sheet1']['B1:B20'] # 取得範囲セル
results = dict()
get_refs(wb, cells, results)

for c in results.items():
    print(c)

.xlsx例

	A	B	C	D	E	F	G	H
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)

結果例

(('', '', 'Sheet1', 'B2'), {'formula': '=C2', 'refs': [('', '', 'Sheet1', 'C2')]})
(('', '', 'Sheet1', 'B3'), {'formula': '=Sheet2!A1', 'refs': [('', '', 'Sheet2', 'A1')]})
(('', '', 'Sheet1', 'B4'), {'formula': '=C4+D4+E4', 'refs': [('', '', 'Sheet1', 'C4'), ('', '', 'Sheet1', 'D4'), ('', '', 'Sheet1', 'E4')]})
(('', '', 'Sheet1', 'C4'), {'formula': '=D4+E4', 'refs': [('', '', 'Sheet1', 'D4'), ('', '', 'Sheet1', 'E4')]})
(('', '', 'Sheet1', 'D4'), {'formula': '=E4', 'refs': [('', '', 'Sheet1', 'E4')]})
(('', '', 'Sheet1', 'B5'), {'formula': '=SUM(Sheet2!A1:E1)', 'refs': [('', '', 'Sheet2', 'A1:E1')]})
(('', '', 'Sheet1', 'B6'), {'formula': '=SUM(Sheet2!A1:A5)', 'refs': [('', '', 'Sheet2', 'A1:A5')]})
(('', '', 'Sheet1', 'B7'), {'formula': '=SUM(Sheet2!1:1)', 'refs': [('', '', 'Sheet2', '1:1')]})
(('', '', 'Sheet1', 'B8'), {'formula': '=SUM(Sheet2!A:A)', 'refs': [('', '', 'Sheet2', 'A:A')]})
(('', '', 'Sheet1', 'B9'), {'formula': '=SUM(D9:E11)', 'refs': [('', '', 'Sheet1', 'D9:E11')]})
(('', '', 'Sheet1', 'D9'), {'formula': '=F9', 'refs': [('', '', 'Sheet1', 'F9')]})
(('', '', 'Sheet1', 'E9'), {'formula': '=G9', 'refs': [('', '', 'Sheet1', 'G9')]})
(('', '', 'Sheet1', 'D11'), {'formula': '=F11', 'refs': [('', '', 'Sheet1', 'F11')]})
(('', '', 'Sheet1', 'E11'), {'formula': '=G11', 'refs': [('', '', 'Sheet1', 'G11')]})
(('', '', 'Sheet1', 'B10'), {'formula': '=SUM(C10,E10)', 'refs': [('', '', 'Sheet1', 'C10'), ('', '', 'Sheet1', 'E10')]})
(('', '', 'Sheet1', 'B11'), {'formula': '=D11+E11', 'refs': [('', '', 'Sheet1', 'D11'), ('', '', 'Sheet1', 'E11')]})
(('', '', 'Sheet1', 'B12'), {'formula': '=D12', 'refs': [('', '', 'Sheet1', 'D12')]})
(('', '', 'Sheet1', 'B13'), {'formula': '=D13', 'refs': [('', '', 'Sheet1', 'D13')]})
(('', '', 'Sheet1', 'B14'), {'formula': '=#REF!', 'refs': []})
(('', '', 'Sheet1', 'B15'), {'formula': '=SUM(Sheet2!#REF!)', 'refs': [('', '', 'Sheet2', '#REF!')]})
(('', '', 'Sheet1', 'B16'), {'formula': '=[1]Sheet1!$A$1', 'refs': [('', '1', 'Sheet1', 'A1')]})
(('', '', 'Sheet1', 'B17'), {'formula': '=SUMM(A1)', 'refs': [('', '', 'Sheet1', 'A1')]})