如何用Excel公式判断跨表值存在性及xlwings实现无匹配行标红？

一、判断值是否存在的Excel公式 #

可以用COUNTIF公式直接验证，比如在评论工作表的空白单元格（如B2）中输入：

=COUNTIF(菜谱!A:A, A2)=0

这个公式会检查当前行（A2）的recipe_id在「菜谱」工作表的A列中是否存在，返回TRUE代表不存在，FALSE代表存在。

二、用xlwings将公式批量粘贴到单元格 #

如果需要通过xlwings自动写入并填充公式，可使用以下代码：

import xlwings as xw

# 替换为你的Excel文件路径
file_path = "你的文件.xlsx"

with xw.Book(file_path) as wb:
    ws_comment = wb.sheets["评论"]
    # 获取评论表最后一行数据的行号
    last_data_row = ws_comment.range("A1").end('down').row
    
    # 在B2单元格写入公式
    ws_comment.range("B2").formula = '=COUNTIF(菜谱!A:A, A2)=0'
    # 自动填充公式到所有数据行
    ws_comment.range("B2").autofill(ws_comment.range(f"B2:B{last_data_row}"))

执行后，评论表的B列会自动生成验证结果，后续可以基于B列的TRUE值设置条件格式把对应行标红。

三、用xlwings直接标记红色行（无需公式） #

不想在工作表中保留公式的话，也可以直接通过xlwings读取数据并设置单元格格式，代码如下：

import xlwings as xw

file_path = "你的文件.xlsx"

with xw.Book(file_path) as wb:
    ws_recipe = wb.sheets["菜谱"]
    ws_comment = wb.sheets["评论"]
    
    # 提取菜谱表的所有recipe_id并转为集合（加快查询速度）
    recipe_id_list = ws_recipe.range("A2:A" + str(ws_recipe.cells.last_cell.row)).value
    recipe_id_set = set(filter(None, recipe_id_list))  # 过滤空值
    
    # 遍历评论表的每一行数据
    last_row = ws_comment.cells.last_cell.row
    for row_num in range(2, last_row + 1):
        current_id = ws_comment.range(f"A{row_num}").value
        # 如果当前id不在菜谱集合中，设置整行背景为红色
        if current_id not in recipe_id_set:
            ws_comment.range(f"A{row_num}:Z{row_num}").color = (255, 0, 0)

这种方法直接操作单元格格式，不需要额外的公式列，处理完后工作表更整洁。

内容的提问来源于stack exchange，提问作者Ирина Мухомор