求助·给数据编序号

看懂了又好像没看懂

'
Option Explicit
'
Sub abc()
　Dim a, i, j, m
　a = [a1].CurrentRegion.Resize(, 6)
　ReDim b(1 To UBound(a) * 4, 1 To 3), c(UBound(a, 2))
　For i = 2 To UBound(a)
　　For j = 3 To UBound(a, 2)
　　　If a(i, j) > 0 Then
　　　　c(j) = c(j) + 1
　　　　m = m + 1
　　　　b(m, 1) = m: b(m, 2) = a(i, 1): b(m, 3) = a(1, j) & c(j)
　　　End If
　　Next
　Next
　With [a1].Offset(, UBound(a, 2) + 1)
　　.Resize(, 3) = Split("序号,套餐,配置编号", ",")
　　.Offset(1).Resize(UBound(b), 3) = b
　End With
End Sub

参考公式：=LET(h,HSTACK(TOCOL(IF(C3:F6>0,A3:A6,1/0),3),TOCOL(IF(C3:F6>0,C2:F2,1/0),3)),s,SORTBY(h,MATCH(TAKE(h,,1),VSTACK("乙","丙","甲","丁"),)),d,SORT(DROP(REDUCE("",UNIQUE(TAKE(s,,1)),LAMBDA(x,y,VSTACK(x,FILTER(h,TAKE(h,,1)=y)))),1),2,1),m_1,DROP(REDUCE("",UNIQUE(DROP(d,,1)),LAMBDA(x_1,y_1,VSTACK(x_1,LET(n,FILTER(d,DROP(d,,1)=y_1),HSTACK(DROP(n,,-1),BYROW(HSTACK(DROP(n,,1),SEQUENCE(ROWS(n))),CONCAT)))))),1),VSTACK(HSTACK("序号","套餐","配置编号"),HSTACK(SEQUENCE(ROWS(m_1)),SORTBY(m_1,MATCH(TAKE(m_1,,1),VSTACK("乙","丙","甲","丁"),)))))