基于单元格地址查找公式实现测验错题自动展示的需求

核心逻辑 #

将答案字母（A-D）映射为数值（1-4），结合INDEX/MATCH关联Test表的题目数据，自动对比手写答案与正确答案，实现错题自动展示。全程无需手动链接单元格，仅需维护Test表的题目和答案即可。

各列公式设置（以第3行为例，可下拉填充） #

假设工作表名为Results，Test表结构为：

• Test!B列：题目编号（1-100）
• Test!C列：题目内容
• Test!D列：正确答案（A-D）

A列：自动填充题目编号 #

=ROW()-2

（从第3行开始时，ROW()-2自动生成1、2、3...的连续编号）

B列：自动提取正确答案（锁定/隐藏） #

直接从Test表拉取答案，无需手动录入：

=INDEX(Test!$D$3:$D$490,MATCH(Results!$A3,Test!$B$3:$B$490,0))

设置单元格为锁定并隐藏，防止误修改。

C列：自动判断对错 #

将答案字母转成数值后对比，返回“正确/错误”：

=IF(
    SWITCH(Results!$B3,"A",1,"B",2,"C",3,"D",4) = SWITCH(Results!$L3,"A",1,"B",2,"C",3,"D",4),
    "正确",
    "错误"
)

也可拆分到辅助列简化公式：

• 辅助列F（答案键数值）：=SWITCH(B3,"A",1,"B",2,"C",3,"D",4)
• 辅助列G（手写答案数值）：=SWITCH(L3,"A",1,"B",2,"C",3,"D",4)
• C列简化为：=IF(F3=G3,"正确","错误")

E列：自动显示题目内容 #

=INDEX(Test!$C$3:$C$490,MATCH(Results!$A3,Test!$B$3:$B$490,0))

D列：自动展示错题及正确答案 #

答题错误时显示题目+正确答案，否则为空：

=IF(Results!$C3="错误",Results!$E3&" 正确答案："&Results!$B3,"")

手写答案（L列）关联方案 #

若L列按题目编号顺序输入（L3对应题目1，L4对应题目2...），直接通过行号对应即可。若L列非顺序排列，可通过题目编号匹配关联：

=INDEX(Results!$L$3:$L$102,MATCH(Results!$A3,Results!$A$3:$A$102,0))

（假设题目到第102行，确保L列与A列题目编号一一对应）

优化建议 #

1. 给L列设置数据验证：仅允许输入A-D，避免无效值
2. 用条件格式标记错误行：给C列显示“错误”的整行设置填充色，快速定位错题
3. 隐藏辅助列（F、G），简化界面

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