pandas ExcelWriter实现DataFrame相邻列映射值条件格式设置

数据老陈

2026-8-30

问题根因

你之前的方案未生效核心是3个错误：

条件格式应用范围与公式引用不匹配：规则绑定在C列，但公式比对的是A、B列单元格，完全没有关联需要着色的当前单元格与相邻对比列
单元格引用方式错误：公式锁死了第一行单元格，且没有和规则应用范围的起始单元格对齐，导致整列都复用第一行的判断逻辑
查找范围用整列引用+无异常处理，遇到空值、未收录评级时会返回#N/A错误，直接导致条件格式规则失效。

可直接运行的实现方案

import pandas as pd

# 评级映射字典
dict1 = {'Aaa': 1, 'Aa1': 2, 'Aa2': 3, 'Aa3': 4, 'A1': 5, 'A2': 6, 'NR': 7, 'WR': 8, 'Baa2': 9,
                 'Baa3': 10, 'Ba1': 11, 'Ba2': 12, 'Ba3': 13, 'B1': 14, 'B2': 15, 'B3': 16, 'Caa1': 17,
                 'Caa2': 18, 'Caa3': 19, 'Ca': 20}

file_name = "评级导出结果.xlsx"
# 替换为你的实际DataFrame
# df = your_dataframe

with pd.ExcelWriter(file_name, engine='xlsxwriter') as writer:
    workbook = writer.book
    # 创建隐藏辅助表存储评级映射
    sheet_aux = workbook.add_worksheet("rating_map")
    for row_idx, (rate_key, rate_val) in enumerate(dict1.items()):
        sheet_aux.write(row_idx, 0, rate_key)
        sheet_aux.write(row_idx, 1, rate_val)
    # 定义名称限定查找范围，替代整列引用提升兼容性
    workbook.define_name("rate_keys", "='rating_map'!$A$1:$A$20")
    workbook.define_name("rate_values", "='rating_map'!$B$1:$B$20")

    # 写入主数据
    df.to_excel(writer, sheet_name="Sheet1", index=False)
    sheet_main = writer.sheets["Sheet1"]

    # 定义填充格式
    fmt_red = workbook.add_format({
        'bg_color': '#FF0000',
        'font_color': '#000000'
    })
    fmt_green = workbook.add_format({
        'bg_color': '#92D050',
        'font_color': '#000000'
    })

    # 配置条件格式：示例为C列（MD Asset）对比左侧B列（MD Asset-7）
    # 可根据实际列位置循环配置多组相邻列规则
    target_col_letter = "C"  # 需要着色的当前列
    compare_col_letter = "B" # 用来比对的左侧相邻列
    target_col_idx = 2       # 当前列的0基索引（C列对应索引2）
    start_row = 1            # 数据起始行0基索引（第1行是表头，数据从第2行开始对应索引1）
    end_row = 999            # 数据结束行0基索引（对应Excel第1000行）

    # 规则1：当前单元格映射值 > 对比列值，填充红色
    sheet_main.conditional_format(
        start_row, target_col_idx, end_row, target_col_idx,
        {
            "type": "formula",
            "criteria": f'=IFERROR(INDEX(rate_values,MATCH({target_col_letter}2,rate_keys,0)),999) > IFERROR(INDEX(rate_values,MATCH({compare_col_letter}2,rate_keys,0)),999)',
            "format": fmt_red
        }
    )

    # 规则2：当前单元格映射值 < 对比列值，填充绿色
    sheet_main.conditional_format(
        start_row, target_col_idx, end_row, target_col_idx,
        {
            "type": "formula",
            "criteria": f'=IFERROR(INDEX(rate_values,MATCH({target_col_letter}2,rate_keys,0)),999) < IFERROR(INDEX(rate_values,MATCH({compare_col_letter}2,rate_keys,0)),999)',
            "format": fmt_green
        }
    )

    # 隐藏辅助表，激活主表
    sheet_aux.hide()
    sheet_main.activate()

关键配置说明

条件格式公式里引用的单元格，必须和规则应用范围的左上角第一个单元格完全对齐：比如规则应用在C2:C1000，公式里就写C2、B2，不要加$锁定行号，这样规则应用到整列时会自动逐行偏移匹配
用IFERROR包裹匹配逻辑，遇到空值、字典不存在的评级时不会抛出#N/A错误中断规则计算，示例中异常值默认返回999，可根据业务需求调整
用定义名称的方式限定映射表的查找范围，不要直接引用整列，避免不同Excel版本的兼容性问题，同时提升计算速度
存在多组相邻比对列时，把列字母、列索引的配置放到循环中批量生成规则即可，无需重复编写代码。

内容的提问来源于stack exchange，提问作者Moinak