如何在Excel中找出与参考服务器文件大小不一致的行？

现有一份记录各服务器文件信息的Excel表格，需以**Server1（黄金副本）**为参考服务器，找出其他服务器上同文件名的文件大小与参考不一致的行，通过Is Diff列标记Y/N。怎么用公式或其他方法处理大数据量的这类需求？

示例数据：

一、公式法（中小数据量适用） #

假设数据从第2行开始，A列存Server、B列存文件名、C列存文件大小、D列是要填的Is Diff。在D2单元格输入以下公式，下拉填充即可：

用VLOOKUP（兼容所有Excel版本） #

=IF(A2="Server1","N",IF(C2=VLOOKUP(B2,$A$2:$C$3,3,FALSE),"N","Y"))

注：把公式里的$C$3换成Server1数据的最后一行行号（比如示例里Server1到第3行，所以写3）。如果Server1的记录分散，最好先把它单独提取成一个干净的参考区域，避免公式出错。

用XLOOKUP（Excel 365/2021及以上，速度更快） #

=IF(A2="Server1","N",IF(C2=XLOOKUP(B2,$B$2:$B$3,$C$2:$C$3),"N","Y"))

二、大数据量高效方法（几万行以上推荐） #

如果数据量很大，公式下拉会卡顿，试试下面两种方法：

1. Power Query 批量处理 #

• 选中数据区域，点「数据」选项卡→「从表格/区域」，进入Power Query编辑器。
• 筛选出Server等于Server1的记录，只保留File Name和File Size列，把File Size重命名为Ref Size，关闭并加载为新的查询（或仅创建连接）。
• 回到原数据的Power Query编辑器，点击「合并查询」，选择原表和刚做的参考表，匹配条件选File Name，合并类型选「左外部」。
• 展开合并后的列，只保留Ref Size列。
• 添加自定义列，输入公式：

  if [Server] = "Server1" then "N" else if [File Size] = [Ref Size] then "N" else "Y"
  

• 删除Ref Size列，关闭并加载回Excel，就能得到标记好的结果。

2. VBA 脚本快速处理 #

打开VBA编辑器（按Alt+F11），插入模块，粘贴以下代码，运行脚本即可完成标记，十万级数据也能快速搞定：

Sub MarkFileDiff()
    Dim ws As Worksheet
    Dim refDict As Object
    Dim lastRow As Long, i As Long
    Dim fileName As String, fileSize As Long, serverName As String
    
    Set ws = ActiveSheet
    Set refDict = CreateObject("Scripting.Dictionary")
    
    ' 读取Server1的文件大小到字典
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    For i = 2 To lastRow
        serverName = ws.Cells(i, "A").Value
        fileName = ws.Cells(i, "B").Value
        fileSize = ws.Cells(i, "C").Value
        If serverName = "Server1" Then
            If Not refDict.Exists(fileName) Then
                refDict.Add fileName, fileSize
            End If
        End If
    Next i
    
    ' 遍历标记差异
    For i = 2 To lastRow
        serverName = ws.Cells(i, "A").Value
        fileName = ws.Cells(i, "B").Value
        fileSize = ws.Cells(i, "C").Value
        
        If serverName = "Server1" Then
            ws.Cells(i, "D").Value = "N"
        Else
            If refDict.Exists(fileName) Then
                ws.Cells(i, "D").Value = IIf(fileSize = refDict(fileName), "N", "Y")
            Else
                ' 参考服务器没有该文件，标记为Y
                ws.Cells(i, "D").Value = "Y"
            End If
        End If
    Next i
    
    MsgBox "差异标记完成！"
End Sub

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