如何基于两张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
相关产品推荐
相关产品推荐

