如何用Excel或VBA将数据替换为对应ID的最高频率分配值？

一、Excel公式法（无需编程） #

假设数据表结构：

• A列：ID（如ID1、ID2）
• B列：分配值（如10%、35%）
• D列：目标ID列表（如D2=ID1，D3=ID2）

步骤： #

1. 添加辅助列计算众数：在C2单元格输入以下公式，按回车后下拉填充至数据末尾：

   =MODE.SNGL(IF($A$2:$A$100=A2,$B$2:$B$100,""))
   

   • 注：旧版Excel需按Ctrl+Shift+Enter作为数组公式执行；若存在多个众数，MODE.SNGL会返回第一个出现的众数。
2. 批量替换原分配值：选中C列的众数结果，右键复制→选中B列原分配值区域→右键选择“粘贴值”，完成替换。

二、VBA宏方法（批量自动化） #

适合数据量较大、需要重复执行的场景，以下是可直接使用的宏代码：

Sub ReplaceIDValuesWithMode()
    Dim dataSheet As Worksheet
    Dim targetIDs As Variant
    Dim lastDataRow As Long, i As Long
    Dim currentID As String
    Dim countDict As Object
    Dim maxOccur As Long, modeVal As String
    
    ' 配置参数：修改工作表名称和ID列表来源
    Set dataSheet = ThisWorkbook.Worksheets("数据表")
    ' 方式1：直接指定ID列表
    targetIDs = Array("ID1", "ID2")
    ' 方式2：从单元格读取ID列表（示例：D2到D列最后一行）
    ' targetIDs = dataSheet.Range("D2:D" & dataSheet.Cells(dataSheet.Rows.Count, "D").End(xlUp).Row).Value
    
    Set countDict = CreateObject("Scripting.Dictionary")
    lastDataRow = dataSheet.Cells(dataSheet.Rows.Count, "A").End(xlUp).Row
    
    ' 遍历每个目标ID
    For Each currentID In targetIDs
        countDict.RemoveAll
        maxOccur = 0
        modeVal = ""
        
        ' 第一步：统计当前ID下各分配值的出现次数
        For i = 2 To lastDataRow
            If dataSheet.Cells(i, "A").Value = currentID Then
                Dim valKey As String
                valKey = dataSheet.Cells(i, "B").Value
                ' 更新字典计数
                If countDict.Exists(valKey) Then
                    countDict(valKey) = countDict(valKey) + 1
                Else
                    countDict(valKey) = 1
                End If
                ' 记录出现次数最多的值
                If countDict(valKey) > maxOccur Then
                    maxOccur = countDict(valKey)
                    modeVal = valKey
                End If
            End If
        Next i
        
        ' 第二步：替换当前ID下所有分配值为众数
        If modeVal <> "" Then
            For i = 2 To lastDataRow
                If dataSheet.Cells(i, "A").Value = currentID Then
                    dataSheet.Cells(i, "B").Value = modeVal
                End If
            Next i
        End If
    Next currentID
    
    MsgBox "替换完成！"
End Sub

使用说明： #

1. 打开Excel文件，按Alt+F11打开VBA编辑器；
2. 右键左侧工程窗口→插入→模块，将上述代码粘贴进去；
3. 修改代码中的dataSheet名称（改为你的数据表工作表名），选择ID列表的获取方式；
4. 按F5运行宏，或添加按钮绑定该宏一键执行。

注意事项 #

• 确保ID列和分配值列的数据格式统一（避免同ID出现文本/数值混合的情况）；
• 若分配值是百分比格式，VBA会自动识别为数值，替换后保持原格式；
• 若某ID下所有分配值唯一，MODE.SNGL会返回第一个出现的值，VBA同理。

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