横转竖 转格式

飞天篮球猪

仅供参考...

zpy2

cli_one_dim~Sheet1~1;
select * from Sheet1union;

zhangcheng6688

学习一下数组
Sub test()
arr = [a1].CurrentRegion
ReDim brr(1 To 1000, 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
[a8].CurrentRegion.Offset(1).ClearContents
[a9].Resize(m, 3) = brr
End Sub

lanjian229

=GROUPBY(A3:A5,B3:M5,CHOOSE({1,2},SINGLE,TOCOL(B1:M1)),,0)

我要成为专家

一杯绿茶来了 发表于 2024-12-11 19:58
=TEXTSPLIT(TEXTJOIN(";",,IF(B2:M5>0,A2:A5&"/"&B1:M1&"/"&B2:M5,"")),"/",";",1)

目前最精炼的二维转一维表公式，但是有个瑕疵，因为数据区域使用了连接符，导致变成了文本，不便于后期数据统计

我要成为专家

1. =HSTACK(TEXTSPLIT(TEXTJOIN(",",,IF(B3:M5<>"",A3:A5&"-"&B1:M1,"")),"-",","),TOCOL(B3:M5,1))

复制代码

mykitami

=LET(a,TEXTSPLIT(TEXTJOIN(";",,A3:A5&","&B1:M1&","&B3:M5),",",";",1),b,FILTER(a,1-ISNA(TAKE(a,,-1))),IF(SEQUENCE(,3)=3,--b,b))

leepoison

=LET(a,B2:M5,b,a<>"",HSTACK(TOCOL(IF(b,A2:A5,1/0),3),TOCOL(IF(b,B1:M1,1/0),3),TOCOL(a,3)))

xtmyd5156

=LET(D,GROUPBY(A2:A5,B2:M5,CHOOSE({1,2},N,TOCOL(B1:M1)),1,0),VSTACK(A8:C8,FILTER(D,--TAKE(D,,-1)<>0)))

你是那块小饼干

1. =LET(T,TRANSPOSE(DROP(REDUCE("",A2:A5,LAMBDA(X,Y,HSTACK(X,IFNA(VSTACK(Y,VSTACK(B1:M1,OFFSET(Y,,1,,12))),Y)))),,1)),FILTER(T,CHOOSECOLS(T,3)<>0,""))

复制代码