如下内容包括：

1. 问题背景
2. 解决思路
3. 完整 Python 实现
4. HTML 可视化报告
5. 使用效果
6. 总结

同时对业务数据进行了脱敏：

• 原始地区名称 → 明细表01、明细表02
• 原始指标 → 统计指标
• 原始文件 → 汇总统计表.xlsx
• 示例数字仅用于说明

---

Python 实现 Excel 多表汇总数据来源追溯分析工具

1. 背景

在实际数据统计工作中，经常会遇到这样的 Excel 场景：

• 一个汇总表；
• 多个结构完全一致的明细表；
• 汇总表通过公式自动计算；
• 需要定期审核汇总结果。

例如：

汇总统计.xlsx

├── 汇总表
├── 明细表01
├── 明细表02
├── 明细表03
├── ...
└── 明细表N

汇总表中的公式：

=SUM(明细表01:明细表N!C12:C12)

表示：

汇总 C12 单元格的数据，来自明细表01到明细表N中的 C12。

公式本身没有问题，但是审核时会遇到一个实际问题：

汇总结果 43，到底来自哪些明细表？每个明细表贡献多少？

例如：

汇总表：

C12 = 43

希望得到：

来源表	贡献值
明细表01	30
明细表02	7
明细表03	2
明细表04	2
明细表05	1
明细表06	1

最终：

30+7+2+2+1+1 = 43

---

2. 传统人工方式存在的问题

通常人工处理：

1. 打开汇总表；
2. 查看公式；
3. 找到来源 Sheet；
4. 一个个打开分表；
5. 查看对应单元格。

当数据规模较小时可以接受。

但是如果：

• 50 个分表；
• 20 多列指标；
• 几百个统计单元格；

人工核查效率非常低。

同时容易出现：

• 漏看某个来源；
• 计算错误；
• 无法快速定位异常。

---

3. 解决方案

本文利用 Python 自动完成：

Excel汇总表

      ↓

解析SUM三维公式

      ↓

定位来源工作表范围

      ↓

读取来源单元格

      ↓

计算贡献值

      ↓

生成追溯报告

最终生成：

1）Excel明细报告

用于数据保存。

2）HTML交互报告

用于人工查看。

效果：

汇总 C12

结果：43


来源：

明细表01
30 ███████████


明细表02
7 ███


明细表03
2 █

状态：

√ 校验一致

---

4. 环境准备

Python版本：

Python 3.x

安装依赖：

pip install openpyxl

---

5. Python完整实现

5.1 功能说明

代码实现：

• 自动读取 Excel；
• 支持 C-Z 列扫描；
• 自动解析 SUM公式；
• 自动定位来源Sheet；
• 自动计算贡献值；
• 自动生成HTML报告。

---

5.2 完整代码

import openpyxl
import re
import json
from openpyxl.utils import get_column_letter


# ============================
# 文件配置
# ============================

input_file = "汇总统计表.xlsx"

output_html = "数据来源追溯分析.html"

summary_sheet = "汇总表"



# ============================
# 读取Excel
# ============================

# 读取公式
wb_formula = openpyxl.load_workbook(
    input_file,
    data_only=False
)


# 读取结果
wb_value = openpyxl.load_workbook(
    input_file,
    data_only=True
)


ws_formula = wb_formula[summary_sheet]

ws_value = wb_value[summary_sheet]



# ============================
# 数值处理
# ============================

def to_number(value):

    if value is None:
        return 0


    if isinstance(value,(int,float)):
        return value


    value=str(value).strip()


    if value in [
        "",
        "-",
        "—",
        "无"
    ]:
        return 0


    try:
        return float(value)

    except:
        return 0




# ============================
# 获取Sheet范围
# ============================

def get_sheet_range(start,end):

    sheets = wb_formula.sheetnames


    start_index = sheets.index(start)

    end_index = sheets.index(end)


    return sheets[
        start_index:
        end_index+1
    ]




# ============================
# 解析SUM公式
# ============================

def parse_formula(formula):


    pattern = (
        r"SUM\((.+):(.+)!"
        r"(\$?[A-Z]+\$?\d+)"
    )


    result = re.search(
        pattern,
        formula
    )


    if result:


        return {

            "start":
            result.group(1),

            "end":
            result.group(2),

            "cell":
            result.group(3)
            .replace("$","")

        }


    return None




# ============================
# 分析数据
# ============================

trace_data={}



# C-Z列

for col in range(3,27):


    col_name=get_column_letter(col)


    trace_data[col_name]=[]



    for row in range(
        1,
        ws_formula.max_row+1
    ):


        address=f"{col_name}{row}"


        formula=ws_formula[address].value



        if not(
            isinstance(formula,str)
            and "SUM(" in formula
        ):
            continue



        info=parse_formula(
            formula
        )


        if not info:
            continue



        sheets=get_sheet_range(
            info["start"],
            info["end"]
        )


        total=to_number(
            ws_value[address].value
        )


        sources=[]

        source_total=0



        for sheet in sheets:


            value=to_number(
                wb_value[sheet]
                [info["cell"]]
                .value
            )


            if value!=0:


                source_total+=value


                sources.append({

                    "name":
                    sheet,

                    "value":
                    value

                })



        # 按贡献排序

        sources.sort(
            key=lambda x:x["value"],
            reverse=True
        )


        trace_data[col_name].append({

            "cell":
            address,

            "total":
            total,

            "status":
            "一致"
            if total==source_total
            else "异常",

            "sources":
            sources

        })



# ============================
# 生成HTML
# ============================


json_data=json.dumps(
    trace_data,
    ensure_ascii=False
)



html="""

<html>

<head>

<meta charset='utf-8'>


<title>
数据来源追溯分析
</title>


<style>


body{

font-family:
Microsoft YaHei;

background:#f5f5f5;

padding:20px;

}


.card{

background:white;

padding:20px;

margin:15px;

border-radius:10px;

}



button{

padding:8px;

margin:5px;

}



.bar{

height:15px;

background:#4caf50;

}


.ok{

color:green;

}


</style>


</head>


<body>


<h2>
Excel数据来源追溯分析
</h2>


<div id='buttons'></div>


<div id='content'></div>



<script>


let DATA=
DATA_PLACEHOLDER;



let btn="";


Object.keys(DATA)
.forEach(function(col){


btn+=

"<button onclick=\"show('"
+col+
"')\">"

+col+

"列</button>";



});


document
.getElementById("buttons")
.innerHTML=btn;



function show(col){


let html="";


DATA[col]
.forEach(function(item){


html+=`

<div class='card'>


<h3>

${item.cell}

汇总：

${item.total}

</h3>


状态：

<span class='ok'>

${item.status}

</span>


<hr>


`;



item.sources.forEach(function(s){


let width=

s.value/item.total*300;



html+=`

<div>

${s.name}

:

${s.value}


<div class='bar'
style='width:${width}px'>

</div>


</div>

<br>


`;


});



html+="</div>";



});


document
.getElementById("content")
.innerHTML=html;


}



show("C");


</script>


</body>

</html>


"""



html=html.replace(
"DATA_PLACEHOLDER",
json_data
)



with open(
output_html,
"w",
encoding="utf-8"
) as f:

    f.write(html)



print(
"生成完成:",
output_html
)

---

6. 运行效果

运行：

python trace_excel.py

生成：

数据来源追溯分析.html

打开后：

顶部：

C列  D列  E列 …… Z列

选择 C列：

显示：

C12

汇总值：43


来源：

明细表01
30


明细表02
7


明细表03
2


状态：

一致

---

7. 工具优势

7.1 从黑盒统计变透明追溯

以前：

汇总值=43

不知道来源。

现在：

43

↓


明细表01 30

明细表02 7

明细表03 2

...

---

7.2 自动发现异常

例如：

汇总值：

50


来源合计：

48

自动：

状态：异常

---

7.3 支持大规模数据审核

适用于：

• 多部门报表；
• 多地区统计；
• 财务汇总；
• 项目数据汇总；
• 年度统计审核。

---

8. 注意事项（数据脱敏）

本文：

• 工作表名称；
• 指标名称；
• 数值示例；

均为虚构数据。

实际使用时：

只需要替换：

input_file
summary_sheet

即可应用于自己的 Excel 文件。

---

9. 总结

通过 Python + openpyxl，对 Excel 三维 SUM 引用进行解析，可以建立完整的数据来源链：

汇总数据

↓

来源工作表

↓

来源单元格

↓

贡献值

↓

一致性校验

↓

可视化展示

将传统 Excel 中难以追踪的数据关系，转换为清晰的数据审计视图。

对于任何：

多个结构一致 Excel → 一个汇总 Excel

的业务场景，都可以快速实现自动化核查。