多列数据转换为纵向的单列数据

lsgr

如何把横向的多列数据转换为纵向的单列数据，并且去除空白和0的数据。
原始数据：

想要的结果：

cnmlgb9998

PQ，逆透视！！！！！！！！！！！！！！！！！！

hxj119

参考公式：=LET(a,VSTACK(A2:E12,HSTACK(A2:D12,F2:F12),HSTACK(A2:D12,G2:G12),HSTACK(A2:D12,H2:H12)),FILTER(a,CHOOSECOLS(a,5)<>""))

另：考虑用PQ方法

逐渐陌生

良良

模拟结果有误。

A2=IFERROR(INDEX(原始数据!A:A,RIGHT(SMALL(IF(原始数据!$E$2:$H$12<>"",COLUMN($A:$D)/1%+ROW($2:$12)),ROW(A1)),2)),"")，三键，右拉至D2，再下拉。
E2=IFERROR(INDEX(原始数据!$1:$1,RIGHT(SMALL(IF(原始数据!E$2:H$12<>"",ROW($2:$12)/1%+COLUMN(E:H)*10001),ROW(A1)),2)),"")，三键，下拉。
F2=IFERROR(INDIRECT(TEXT(RIGHT(SMALL(IF(原始数据!E$2:H$12<>"",ROW($2:$12)/1%+COLUMN($E:$H)*10001),ROW(A1)),4),"原始数据!!R0C00"),),"")，三键，下拉。

boyuanoffice

1. =LET(dt,E2:H12,up,E1:H1,lft,A2:D12,VSTACK({"标题1","标题2","标题3","月份","科目","金额"},
   
2.   SORT(HSTACK(
   
3.     TEXTSPLIT(CONCAT(IF(dt,BYROW(lft,LAMBDA(r,TEXTJOIN(",",,r)&";")),"")),",",";",1),
   
4.     TOCOL(IF(dt,up,NA()),2),
   
5.     TOCOL(dt,1)
   
6. ),5)))

复制代码

lsgr

boyuanoffice 发表于 2024-3-20 13:05

实际应用中提示Concat文本过长

lsgr

逐渐陌生 发表于 2024-3-20 12:40

sum代表什么？

boyuanoffice

本来想取个巧, 奈何数据太大,那就用笨方法了:

1. =LET(dt,E2:H12,up,E1:H1,lft,A2:D12,VSTACK({"标题1","标题2","标题3","月份","科目","金额"},
   
2. HSTACK(
   
3.   TOCOL(IF(dt,CHOOSECOLS(lft,1),NA()),2),
   
4.   TOCOL(IF(dt,CHOOSECOLS(lft,2),NA()),2),
   
5.   TOCOL(IF(dt,CHOOSECOLS(lft,3),NA()),2),
   
6.   TOCOL(IF(dt,CHOOSECOLS(lft,4),NA()),2),
   
7.   TOCOL(IF(dt,up,NA()),2),
   
8.   TOCOL(dt,1)
   
9. )))

复制代码

咋觉着中间部分能用循环来做. 试了一下, 果然可以:

1. =LET(dt,E2:H12,up,E1:H1,lft,A2:D12,VSTACK({"标题1","标题2","标题3","月份","科目","金额"},
   
2. HSTACK(
   
3.   DROP(REDUCE("",SEQUENCE(4),LAMBDA(x,y,HSTACK(x,TOCOL(IF(dt,CHOOSECOLS(lft,y),NA()),2)))),,1),
   
4.   TOCOL(IF(dt,up,NA()),2),
   
5.   TOCOL(dt,1)
   
6. )))

复制代码

lsgr

boyuanoffice 发表于 2024-3-20 14:40
本来想取个巧, 奈何数据太大,那就用笨方法了:

咋觉着中间部分能用循环来做. 试了一下, 果然可以:

如果数据为0会出现错误