求二维转一维

浮生若梦~~~

=TEXT({1,-1,0},TOCOL(IF(B2:D25,""""&A2:A25&""""&";"&B1:D1&";"&B2:D25,x),2))

博爱风雨同舟

=LET(A,GROUPBY(A2:A25,B2:D25,IF({1,0},LAMBDA(X,@X),TOCOL(B1:D1)),,0),FILTER(A,TAKE(A,,-1)>0))

1786024734

向右、下复制

1. =MID(CONCAT(IF(二维!$B$2:$D$25,二维!$A$2:$A$25&二维!$B$1:$D$1&(二维!$B$2:$D$25+0.1)%%,"")),ROW(A1)*23-CHOOSE(COLUMN(A1),22,9,3),CHOOSE(COLUMN(A1),13,3,3))

复制代码

单公式动态溢出

1. =MID(CONCAT(IF(二维!B2:D25,二维!A2:A25&二维!B1:D1&(二维!B2:D25+0.1)%%,"")),ROW(1:99)*23-{22,9,3},{13,3,3})

复制代码

zhangcheng6688

学习一下数组，打扰了
Sub test()
arr = [a1].CurrentRegion
ReDim brr(1 To 10000, 1 To 3)
For i = 2 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(1, j)
         brr(m, 3) = arr(i, j)
      End If
   Next
Next
[f1].Resize(m, 3) = brr
End Sub

hugeinfo

风雨相随3 发表于 2024-8-26 19:58
如果结果要写入E，U，X列，公式能分开吗？

=LET(x,LAMBDA(n,TOCOL(IF(B2:D25<>"",n,a),3)),x(A2:A25))

=LET(x,LAMBDA(n,TOCOL(IF(B2:D25<>"",n,a),3)),x(B1:D1))
=LET(x,LAMBDA(n,TOCOL(IF(B2:D25<>"",n,a),3)),x(B2:D25))

沈默00

=LET(t,二维!B2:D25,VSTACK({"合骏","线号","数量"},HSTACK(TOCOL(IF(t,二维!A2:A25,默),3),TOCOL(IF(t,二维!B1:D1,默),3),TOCOL(IF(t,二维!B2:D25,默),3))))

沈默00

分开写就
E1=VSTACK("线号",TOCOL(IF(二维!B2:D25,二维!B1:D1,默),3))

U1=VSTACK("数量",TOCOL(二维!B2:D25,3))

X1=VSTACK("合骏",TOCOL(IF(二维!B2:D25,二维!A2:A25,默),3))

我要成为专家

hstack(ifna(hstack(a2:a25,Ifna(expand(b1:d1,25,3),b1:d1))),a2:a25),tocol(b2:d25))

逐渐陌生

hxj119

E2:

1. =TOCOL(IF(二维!B2:D25,二维!$B$1:$D$1,0/0),3)

复制代码

U2:

1. =TOCOL(二维!B2:D25,3)

复制代码

Y2:

1. =TOCOL(IF(二维!B2:D25,二维!A2:A25,0/0),3)

复制代码