当前位置：首页 > news >正文

使用VBA嵌套字典快速统计生产流转信息

news 2025/8/31 5:10:17

实例需求：某公司各车间产品统计表如下图所示（A列至C列）。

在这里插入图片描述

现在需要完成产品流转信息统计，如上图右侧所示（E列至G列）。

车间列：排重车间
产品数量清单：
– 统计该车间每个产品的个数，3A代码该车间生产了3个批次产品A
产品流转信息：分为三个部分
– 第一部分：产品个数（不排重），相当于该车间的数据行数
– 第二部分：产品数量清单
– 第三部分：每个产品批次产品的流转天数（C列），剔除流转天数为1的记录(例如第10行)

示例代码如下

Sub Demo()Const COMMA = "，"Const SEMICOLON = "；"Dim i As Long, lIndex As Long, iTotal As LongDim sKDept As String, sKProduct, sKIdxDim sTxt As String, sDetail As StringDim objDic As Object: Set objDic = CreateObject("scripting.dictionary")Dim rngData As Range: Set rngData = Range("A3").CurrentRegionDim arrData: arrData = rngData.ValueFor i = LBound(arrData) + 1 To UBound(arrData)sKDept = arrData(i, 1)If Not objDic.exists(sKDept) ThenSet objDic(sKDept) = CreateObject("scripting.dictionary")End IfsKProduct = arrData(i, 2)If Not objDic(sKDept).exists(sKProduct) ThenSet objDic(sKDept)(sKProduct) = CreateObject("scripting.dictionary")End IflIndex = objDic(sKDept)(sKProduct).Count + 1objDic(sKDept)(sKProduct)(lIndex) = arrData(i, 3)Next iDim res(): ReDim res(1 To objDic.Count, 1 To 3)For i = 1 To objDic.CountsKDept = objDic.keys()(i - 1)res(i, 1) = sKDeptsTxt = "": iTotal = 0: sDetail = ""For Each sKProduct In objDic(sKDept)sTxt = sTxt & COMMA & objDic(sKDept)(sKProduct).Count & sKProductiTotal = iTotal + objDic(sKDept)(sKProduct).CountFor Each sKIdx In objDic(sKDept)(sKProduct)If objDic(sKDept)(sKProduct)(sKIdx) > 1 ThensDetail = sDetail & COMMA & "1" & sKProduct & objDic(sKDept)(sKProduct)(sKIdx) & "天"End IfNextNextres(i, 2) = Mid(sTxt, 2)If Len(sDetail) > 0 Then sDetail = Mid(sDetail, 2)sTxt = iTotal & SEMICOLON & res(i, 2) & SEMICOLON & sDetailres(i, 3) = sTxtNextWith Range("E3").Resize(1, 3).EntireColumn.Clear.Resize(1, 3).Value = Array("车间", "产品数量清单", "产品流转信息").Offset(1).Resize(objDic.Count, 3) = resWith .CurrentRegion.Borders.LineStyle = Excel.XlLineStyle.xlContinuous.EntireColumn.AutoFitEnd WithEnd WithSet objDic = Nothing
End Sub

【代码解析】
第2-3行代码定义分隔符常量。
第7行代码声明字典对象 objDic 并实例化为 Scripting.Dictionary。
第8行代码定义数据区域 rngData，取自单元格 A3 开始的连续区域。
第9行代码将数据区域内容加载到数组 arrData 中，便于后续快速处理。
第10行代码从数组中第2行开始循环（跳过标题行）到最后一行。
第11行代码将当前行第1列（即：车间名称）赋值给变量 sKDept。
第12至第14行代码如果字典 objDic 中不存在该车间，则为其创建一个新的嵌套字典。
第15行代码读取当前行第2列（即：产品名称）到变量 sKProduct。
第16至第18行代码如果车间下不存在该产品，则为该产品建立新的嵌套字典。
第19行代码获取该产品字典的现有元素数量，加1作为新的索引。
第20行代码将当前行第3列的数值存入该产品字典，以索引为键。
第21行代码结束外层循环，完成车间 → 产品 → 数据的三级嵌套字典构建。
第22行代码定义结果数组 res，大小为车间数量 × 3 列。
第23行代码遍历字典 objDic 中的每一个车间。
第24行代码取出当前车间名称并存入 sKDept。
第25行代码将车间名称写入结果数组 res(i, 1)。
第26行代码初始化拼接字符串 sTxt，产品总数 iTotal 和详细描述 sDetail。
第27行代码遍历当前车间中的每个产品。
第28行代码拼接当前产品的数量与名称，添加到 sTxt。
第29行代码累加该产品数量到总数 iTotal。
第30行代码遍历当前产品下的每个索引项（表示不同的记录）。
第31至第33行代码如果记录值大于1，则说明该行为有效的产品记录，拼接详细信息 sDetail（格式如“1产品X天”）。
第34行代码结束索引循环。
第35行代码结束产品循环。
第36行代码将拼接的产品清单（去掉第一个多余的逗号）写入结果数组第2列。
第37行代码如果存在详细信息，去掉开头多余的逗号。
第38行代码按“总数；产品清单；详细信息”的格式生成汇总字符串。
第39行代码将汇总字符串写入结果数组第3列。
第40行代码结束车间循环。
第41行代码以 E3 单元格为起点，清空右侧3列单元格。
第42行代码写入标题行。
第43行代码将结果数组写入标题行下方区域。
第44至第47行代码对结果区域设置边框和自动调整列宽。
第48行代码释放字典对象 objDic。