数据转置

齐而斋

wps如何使用函数将A表的数据转换成B表的内容来显示出来

edwin11891

1. =REDUCE(J2:M2,A3:A4,LAMBDA(x,y,VSTACK(x,IFNA(HSTACK(y,TAKE(WRAPROWS(B2:G2,2),,1),WRAPROWS(OFFSET(y,,1,,6),2)),y))))

复制代码

祝洪忠-

1. =HSTACK(TOCOL(IF((B2:G2<>"数量")*B3:G4,A3:A4,x),3),TOCOL(IF((B2:G2<>"数量")*B3:G4,B2:G2,x),3),WRAPROWS(TOCOL(B3:G4),2))

复制代码

无关岁月

1. =REDUCE(J2:M2,A3:A4,LAMBDA(_a,_b,VSTACK(_a,HSTACK(IF({1,0},_b,VSTACK(B2,D2,F2)),WRAPROWS(OFFSET(_b,,1,,6),2)))))

复制代码

leepoison

1. =REDUCE(J2:M2,A3:A4,LAMBDA(x,y,VSTACK(x,VSTACK(IFNA(HSTACK(y,TAKE(WRAPROWS($B$2:$G$2,2),,1),WRAPROWS(OFFSET(y,,1,,6),2)),y)))))

复制代码

mykitami

=LET(a,DROP(WRAPROWS(B2:G2,2),,-1),REDUCE({"订单号","类型","尺码","数量"},A3:A4,LAMBDA(x,y,VSTACK(x,IFNA(HSTACK(y,a,WRAPROWS(OFFSET(y,,1,,6),2)),y)))))

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) Step 2
      m = m + 1
      brr(m, 1) = arr(i, 1)
      brr(m, 2) = arr(2, j)
      brr(m, 3) = arr(i, j)
      brr(m, 4) = arr(i, j + 1)
   Next
Next
[j1].CurrentRegion.Offset(2).ClearContents
[j3].Resize(m, 4) = brr
End Sub

hugeinfo

=REDUCE(J2:M2,A3:A4,LAMBDA(x,y,VSTACK(x,IFNA(HSTACK(y,TAKE(WRAPROWS(TOCOL($B$2:$G$2),2),,1),WRAPROWS(TOCOL(OFFSET(y,,1,,6)),2)),y))))

Sabrinalijie

1. =REDUCE({"订单号","类型","尺码","数量"},A3:G4,LAMBDA(X,Y,IF(-1^COLUMN(Y)=1,VSTACK(X,HSTACK(OFFSET(Y,,1-COLUMN(Y)),OFFSET(Y,2-ROW(Y),),OFFSET(Y,,,,2))),X)))

复制代码

ovxyz

=LET(a,A3:A4,REDUCE(J2:M2,SEQUENCE(3,,,2),LAMBDA(s,x,VSTACK(s,HSTACK(a,EXPAND(OFFSET(A2,,x),ROWS(a),,OFFSET(A2,,1)),OFFSET(A3:A4,,x,,2))))))