Excel公式错误检测与修复建议工具：加载表格文件、自动扫描所有公式语法错误、颜色编码标记问题单元格、一键导出修复建议报告

🚀 实例简介

一款面向财务、数据分析人员的Excel公式错误检测桌面工具。加载Excel文件后，自动扫描所有工作表中的公式，检测括号不匹配、函数拼写错误、引号未闭合、除零风险、空参数、非法引用等6类常见错误，以颜色编码（红色=错误，黄色=警告）标记问题单元格，并提供一键修复建议。支持导出CSV修复报告。

💻 核心代码

#!/usr/bin/env python3

# -*- coding: utf-8 -*-

"""

Excel公式错误检测与修复建议工具

================================

功能：加载Excel文件，自动扫描所有公式语法错误，颜色编码标记问题单元格，

      一键导出修复建议报告CSV。



依赖：pip install openpyxl

"""



import tkinter as tk

from tkinter import ttk, filedialog, messagebox, scrolledtext

import openpyxl

import csv

import os

import re

import platform

import threading



# 中文字体配置（禁止使用 option_add，必须每个组件单独指定）

CHINESE_FONT = "WenQuanYi Zen Hei" if platform.system() == "Linux" else "Microsoft YaHei"



# ========== 公式错误检测引擎 ==========



# 常见拼写错误映射

COMMON_MISSPELLINGS = {

    "SUMM": "SUM", "SUMM(": "SUM(",

    "AVGEAGE": "AVERAGE", "AVGEAGE(": "AVERAGE(",

    "AVERGE": "AVERAGE", "AVERGE(": "AVERAGE(",

    "CONUT": "COUNT", "COUTN": "COUNT",

    "MAXX": "MAX", "MINN": "MIN",

    "VLOOOKUP": "VLOOKUP", "VLOKUP": "VLOOKUP",

    "HLOOOKUP": "HLOOKUP",

    "LENTH": "LEN", "LENGH": "LEN",

    "CONCATENAT": "CONCATENATE",

    "IFERROR": "IFERROR",  # correct

    "INDIRECTT": "INDIRECT",

    "MATCHH": "MATCH",

}



# Excel合法函数名（常用子集）

VALID_FUNCTIONS = {

    "SUM", "AVERAGE", "COUNT", "MAX", "MIN", "IF", "AND", "OR",

    "VLOOKUP", "HLOOKUP", "INDEX", "MATCH", "CONCATENATE", "LEN",

    "LEFT", "RIGHT", "MID", "TRIM", "UPPER", "LOWER", "PROPER",

    "ROUND", "ROUNDUP", "ROUNDDOWN", "INT", "ABS", "MOD", "POWER",

    "DATE", "YEAR", "MONTH", "DAY", "HOUR", "MINUTE", "SECOND",

    "NOW", "TODAY", "IFERROR", "IFNA", "ISBLANK", "ISERROR",

    "INDIRECT", "OFFSET", "ROW", "COLUMN", "ROWS", "COLUMNS",

    "SUMIF", "SUMIFS", "COUNTIF", "COUNTIFS", "AVERAGEIF", "AVERAGEIFS",

    "TEXT", "VALUE", "NUMBERVALUE", "SUBSTITUTE", "REPLACE",

    "CONCAT", "TEXTJOIN", "UNICHAR", "UNICODE",

    "TRUE", "FALSE", "NOT", "XOR", "SWITCH",

    "LARGE", "SMALL", "RANK", "PERCENTILE", "QUARTILE",

    "PRODUCT", "QUOTIENT", "SIGN", "SQRT", "PI", "RAND", "RANDBETWEEN",

    "CEILING", "FLOOR", "EVEN", "ODD",

    "NETWORKDAYS", "WORKDAY", "EDATE", "EOMONTH", "DATEDIF",

    "SUMPRODUCT", "TRANSPOSE", "SORT", "FILTER", "UNIQUE",

    "XLOOKUP", "XMATCH",

}





def detect_formula_errors(formula: str) -> list:

    """

    检测单个公式的错误，返回 [(错误类型, 描述, 修复建议)] 列表

    """

    errors = []

    f = formula.strip()

    if not f.startswith("="):

        return errors



    expr = f[1:]  # 去掉等号



    # 1. 括号匹配检测

    depth = 0

    for i, ch in enumerate(expr):

        if ch == '(':

            depth += 1

        elif ch == ')':

💡 适用场景

• 财务人员检查Excel报表中的公式错误
• 数据分析师审核数据源文件的质量
• 教师批改学生的Excel作业
• 任何需要批量检查Excel公式正确性的场景

📦 使用方法

1. 安装依赖：pip install -r requirements.txt
2. 运行程序：python main.py
3. 点击"浏览"选择Excel文件
4. 点击"开始扫描"自动检测公式错误
5. 查看结果并导出CSV修复报告