You need to enable JavaScript to run this app. 如何基于两张Excel工作表的共享关联数据生成第三张结果工作表
优惠活动
大模型
产品
解决方案
定价
更多

如何基于两张Excel工作表的共享关联数据生成第三张结果工作表

跨表关联聚合结果表实现方案

需求拆解

  • 基础数据源:
    • SHEET1:存储AccessLevels与关联Zones的映射,单个权限等级可关联任意数量区域
    • SHEET2:存储Zones与关联Items的映射,单个区域可关联任意数量条目
  • 输出要求:
    • 完整保留SHEET1内所有AccessLevel条目
    • 为每个条目聚合其关联所有Zone在SHEET2中对应的全部Items,自动去重后按顺序排布
    • 适配基础VBA能力场景,无需复杂Power Query配置

参考数据结构

SHEET1
| AccessLevels | Z1    | Z2    | Z3    |
| ------------ | ----- | ----- | ----- |
| AccessLevel1 | ZoneA | ZoneB | ZoneC |
| AccessLevel2 | ZoneA | ZoneC |       |
| AccessLevel3 | ZoneB |       |       |
| AccessLevel4 | ZoneA | ZoneD |       |

SHEET2
| Zones | I1    | I2    | I3    |
| ----- | ----- | ----- | ----- |
| ZoneA | ItemA | ItemB |       |
| ZoneB | ItemB | ItemC |       |
| ZoneC | ItemA | ItemD | ItemF |
| ZoneD | ItemE |       |       |

RESULT_SHEET
| AccessLevels | I1    | I2    | I3    | I4    | I5    | I6    |
| ------------ | ----- | ----- | ----- | ----- | ----- | ----- |
| AccessLevel1 | ItemA | ItemB | ItemC | ItemD | ItemF |       |
| AccessLevel2 | ItemA | ItemB |       | ItemD | ItemF |       |
| AccessLevel3 |       | ItemB | ItemC |       |       |       |
| AccessLevel4 | ItemA | ItemB |       |       |       | ItemE |

可直接运行的VBA实现代码

代码内置自动去重、自适应表结构、结果表自动初始化逻辑,无需手动调整范围:

Sub GenerateAggregatedResult()
    Dim wsAccess As Worksheet, wsZone As Worksheet, wsResult As Worksheet
    Dim zoneItemDict As Object, tempItemDict As Object
    Dim lastRowAccess As Long, lastColAccess As Long
    Dim lastRowZone As Long, lastColZone As Long
    Dim rowIdx As Long, colIdx As Long, itemColIdx As Long
    Dim curZone As String, curItem As Variant
    
    ' 绑定数据源工作表
    Set wsAccess = ThisWorkbook.Worksheets("SHEET1")
    Set wsZone = ThisWorkbook.Worksheets("SHEET2")
    
    ' 初始化/清空结果表
    On Error Resume Next
    Set wsResult = ThisWorkbook.Worksheets("RESULT_SHEET")
    If wsResult Is Nothing Then
        Set wsResult = ThisWorkbook.Worksheets.Add(after:=wsZone)
        wsResult.Name = "RESULT_SHEET"
    Else
        wsResult.Cells.Clear
    End If
    On Error GoTo 0
    
    ' 建立Zone-Item全局映射字典
    Set zoneItemDict = CreateObject("Scripting.Dictionary")
    lastRowZone = wsZone.Cells(wsZone.Rows.Count, "A").End(xlUp).Row
    lastColZone = wsZone.Cells(1, wsZone.Columns.Count).End(xlToLeft).Column
    
    For rowIdx = 2 To lastRowZone
        curZone = Trim(wsZone.Cells(rowIdx, "A").Value)
        If curZone <> "" Then
            Set tempItemDict = CreateObject("Scripting.Dictionary")
            For colIdx = 2 To lastColZone
                curItem = Trim(wsZone.Cells(rowIdx, colIdx).Value)
                If curItem <> "" Then tempItemDict(curItem) = ""
            Next colIdx
            zoneItemDict(curZone) = tempItemDict
        End If
    Next rowIdx
    
    ' 写入结果表表头
    wsResult.Cells(1, "A").Value = "AccessLevels"
    
    ' 逐行聚合生成结果
    lastRowAccess = wsAccess.Cells(wsAccess.Rows.Count, "A").End(xlUp).Row
    lastColAccess = wsAccess.Cells(1, wsAccess.Columns.Count).End(xlToLeft).Column
    For rowIdx = 2 To lastRowAccess
        wsResult.Cells(rowIdx, "A").Value = Trim(wsAccess.Cells(rowIdx, "A").Value)
        Set tempItemDict = CreateObject("Scripting.Dictionary")
        
        ' 收集当前AccessLevel关联的所有Items
        For colIdx = 2 To lastColAccess
            curZone = Trim(wsAccess.Cells(rowIdx, colIdx).Value)
            If curZone <> "" And zoneItemDict.Exists(curZone) Then
                For Each curItem In zoneItemDict(curZone).Keys
                    tempItemDict(curItem) = ""
                Next curItem
            End If
        Next colIdx
        
        ' 将聚合后的Items写入结果行
        itemColIdx = 2
        For Each curItem In tempItemDict.Keys
            wsResult.Cells(rowIdx, itemColIdx).Value = curItem
            itemColIdx = itemColIdx + 1
        Next curItem
    Next rowIdx
    
    ' 自动调整列宽提升可读性
    wsResult.Columns.AutoFit
    MsgBox "关联结果表生成完成"
End Sub

内容的提问来源于stack exchange,提问作者Harley

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.13 18:45:06