如何检查Excel公式用到的单元格？及批量判断单元格是否被引用

首先说明你用COUNTIF失败的原因：COUNTIF是统计单元格值的匹配情况，但B1显示的是平均值计算结果（1.5），并非公式里的引用范围，自然无法匹配A列的数值，所以失效。

下面提供两种可行方案，适配不同Excel版本：

方案1：适用于Excel 365/2021（支持新函数） #

在C1单元格输入以下公式，下拉填充至C3：

=IF(ISNUMBER(MATCH(CELL("address",A1), TEXTSPLIT(TEXTBEFORE(TEXTAFTER(FORMULATEXT(B1),"("),")"),":"),0)),A1,"")

公式逻辑：

• FORMULATEXT(B1)：提取B1的完整公式文本，得到=AVERAGE(A1:A2)
• 通过TEXTAFTER+TEXTBEFORE截取公式中括号内的引用区域，得到A1:A2
• TEXTSPLIT用冒号拆分区域，生成包含单个单元格地址的数组{"A1","A2"}
• 匹配当前A列单元格的地址，存在则显示对应值，否则显示空白

方案2：兼容所有Excel版本 #

如果你的Excel不支持TEXTSPLIT等新函数，用这个公式（C1输入后下拉）：

=IF(ISNUMBER(MATCH(CELL("address",A1), ADDRESS(ROW(INDIRECT(MID(FORMULATEXT(B1),FIND("(",FORMULATEXT(B1))+1,FIND(")",FORMULATEXT(B1))-FIND("(",FORMULATEXT(B1))-1))),COLUMN(INDIRECT(MID(FORMULATEXT(B1),FIND("(",FORMULATEXT(B1))+1,FIND(")",FORMULATEXT(B1))-FIND("(",FORMULATEXT(B1))-1))),4),0)),A1,"")

公式逻辑：

• 先提取B1公式里的引用区域文本，通过INDIRECT转为实际单元格引用
• 生成该区域内所有单元格的地址，匹配当前A列单元格地址，匹配成功则显示对应值，否则空白（此方案不受A列重复值影响）

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