如何把一行数据，依据条件通过函数变成两行

hbzql · 发表于 2023-8-8 17:12

M函数：

let
源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
合并 = List.Accumulate({"主","零"},源,(x,y)=>Table.CombineColumns(x,{y&"-芯数",y&"-规格"},each _,y)),
逆透视 = Table.Unpivot(合并,{"主","零"},"相序","值"),
展开 = Table.SplitColumn(逆透视,"值",each _,{"芯数","规格2"}),
选择非空行 = Table.SelectRows(展开,each [芯数]<>null)
in
选择非空行

复制代码

夏国兴 · 发表于 2023-8-9 10:55

hbzql 发表于 2023-8-8 16:41
A7=LOOKUP(,0/FREQUENCY(ROW(A1),COUNTIF(OFFSET($G$2,,,ROW($1:$3),4),"")/2),OFFSET($A$2,,MATCH(A$6,$A$ ...

分段处理，思路新颖

cyh3230 · 发表于 2023-8-9 14:21

用这个弄了下, 应该也行

select 工作号,型号,电压等级,规格,单位,数量,
"主" as 相序,[主-芯数] as 芯数,[主-规格] as 规格,备注 from [Sheet1$A1:K4]
where [主-芯数] is not null
union all
select 工作号,型号,电压等级,规格,单位,数量,
"零" as 相序,[零-芯数] as 芯数,[零-规格] as 规格,备注 from [Sheet1$A1:K4]
where [零-芯数] is not null
order by 工作号,相序

jiangxiaoyun · 发表于 2023-8-9 21:36

=TOCOL(IF(H2:I4<>"",A2:A4,""))

=IFERROR(INDEX(B$2:B$4,MATCH($A7,$A$2:$A$4,0),),"")

可以多条函数搞掂，这样显得简单些，也常用。出了tocol都是以前的旧函数index，match，countif等

订单工序化.zip (11.91 KB, 下载次数: 3)

		自动登录	找回密码
密码			免费注册

[求助] 如何把一行数据，依据条件通过函数变成两行