如何用公式或VBA对比Excel两工作表数据并在第三表列出缺失值？

方案一：动态数组公式法（适用于Excel 365/2021及以上版本） #

无需VBA，直接用公式自动提取缺失值：

• 在Sheet3的A1单元格输入以下公式，自动合并Sheet1中C-F列的所有非空值为单列：

  =TOCOL(Sheet1!C:F,1)
  

  （参数1用于忽略空单元格）
• 在Sheet3的B1单元格输入公式，筛选出Sheet2中未出现的记录：

  =FILTER(TOCOL(Sheet1!C:F,1),COUNTIF(Sheet2!C:C,TOCOL(Sheet1!C:F,1))=0,"无未归还项")
  

  公式会自动返回所有Sheet1存在但Sheet2未录入的条目，无缺失时显示“无未归还项”。

若你的Excel版本不支持动态数组，可采用传统数组公式分步操作：

1. 在Sheet3的A列，用INDEX/OFFSET或手动填充，将Sheet1的C-F列数据逐行转成单列（例：A1=Sheet1!C1，A2=Sheet1!D1，A3=Sheet1!E1，A4=Sheet1!F1，A5=Sheet1!C2，以此类推）
2. 在Sheet3的B1输入公式后按Ctrl+Shift+Enter（数组公式），下拉至出现空单元格：

   =IFERROR(INDEX($A:$A,SMALL(IF(COUNTIF(Sheet2!C:C,$A:$A)=0,ROW($A:$A)),ROW(A1))),"")
   

方案二：VBA代码法（全版本适用，支持一键更新） #

按以下步骤操作：

1. 打开Excel，按Alt+F11打开VBA编辑器
2. 右键点击左侧工程窗口中的当前工作簿，选择「插入」→「模块」
3. 粘贴以下代码：

   Sub GetUnreturnedItems()
       Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
       Dim rng1 As Range, cell As Range
       Dim unreturned As Collection
       Dim lastRow2 As Long, i As Long
       
       ' 绑定目标工作表
       Set ws1 = ThisWorkbook.Sheets("Sheet1")
       Set ws2 = ThisWorkbook.Sheets("Sheet2")
       Set ws3 = ThisWorkbook.Sheets("Sheet3")
       Set unreturned = New Collection
       
       ' 清空Sheet3旧数据并设置表头
       ws3.Cells.Clear
       ws3.Range("A1").Value = "未归还项"
       
       ' 遍历Sheet1的C-F列，收集非空且不重复的借出项
       On Error Resume Next ' 忽略重复项报错
       For Each rng1 In ws1.Range("C:F")
           If rng1.Value <> "" Then
               unreturned.Add rng1.Value, Key:=CStr(rng1.Value)
           End If
       Next rng1
       On Error GoTo 0
       
       ' 遍历Sheet2的C列，移除已归还的项
       lastRow2 = ws2.Cells(ws2.Rows.Count, "C").End(xlUp).Row
       For i = 1 To lastRow2
           If ws2.Cells(i, "C").Value <> "" Then
               On Error Resume Next
               unreturned.Remove CStr(ws2.Cells(i, "C").Value)
               On Error GoTo 0
           End If
       Next i
       
       ' 将未归还项写入Sheet3
       For i = 1 To unreturned.Count
           ws3.Cells(i + 1, "A").Value = unreturned(i)
       Next i
       
       MsgBox "已更新未归还项，共" & unreturned.Count & "条", vbInformation
   End Sub
   

4. 返回Excel界面，按Alt+F8选择GetUnreturnedItems执行，即可一键生成未归还列表。

代码说明 #

• 自动去重：Sheet1中重复的借出项仅保留一条
• 空值过滤：自动跳过所有空单元格，只处理有效数据
• 旧数据清空：每次执行会清除Sheet3之前的结果，避免重复

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