二维变一维

欢乐马20221029

将二维的名单变成一维

无关岁月

无脑堆

1. =HSTACK(TOCOL(IF(B3:AX6<>"",A3:A6,x),2),TOCOL(B3:AX6,1),TOCOL(IF(B3:AX6<>"",B2:AX2,x),2),TOCOL(IF(B3:AX6<>"",B1:AX1,x),2))

复制代码

hxj119

1. =LET(z,B3:AZ6,a,TOCOL(IF(z<>"",A3:A6,0/0),3),b,TOCOL(IF(z<>"",B2:AZ2,0/0),3),c,TOCOL(IF(z<>"",B1:AZ1,0/0),3),HSTACK(a,b,TOCOL(z,3),c))

复制代码

ovxyz

=REDUCE(A10:D10,B3:AX6,LAMBDA(s,x,IF(x="",s,VSTACK(s,HSTACK(INDIRECT("A"&ROW(x)),x,OFFSET(x,2-ROW(x),),OFFSET(x,1-ROW(x),))))))

zhangcheng6688

学习一下数组
Sub test()
arr = [a1].CurrentRegion
ReDim brr(1 To 10000, 1 To 4)
For i = 3 To UBound(arr)
   For j = 2 To UBound(arr, 2)
      If arr(i, j) <> "" Then
         m = m + 1
         brr(m, 1) = arr(i, 1)
         brr(m, 2) = arr(i, j)
         brr(m, 3) = arr(2, j)
         brr(m, 4) = arr(1, j)
      End If
   Next
Next
[a10].CurrentRegion.Offset(1).ClearContents
[a11].Resize(m, 4) = brr
End Sub