"Excel表格从第 2 列起，每 N 列为一组，以 N=2 为例： A B C D E F G 1 ID Type 1 Count 1 Type 2 Count 2 Type 3 Count .."

jinxing
乾学院 53 号会员
5 回帖 • 463 浏览 • 6 个月前

Excel 每 N 列内容填成一行

桌面处理

Excel表格从第 2 列起，每 N 列为一组，以 N=2 为例：

	A	B	C	D	E	F	G
1	ID	Type 1	Count 1	Type 2	Count 2	Type 3	Count 3
2	1	a	640	d	290	a
3	2	d	12000	a	1900	f	6000
4	3	f	48000	f	3600	e	1600
5	4	c	46000	e	3100	b	1200
6	5	e	47000	c	3400	d	1400
7	6	b	64000	b	3600	c	1200

现在要进列转行：每行的每 2 列内容填成一行，即扩展成 3 行；第 1 列 ID 保留；新增第 2 列 No 是扩展出来的行号：

	A	B	C	D
1	ID	No	Type	Count
2	1	1	a	640
3	1	2	d	290
4	1	3	a
5	2	1	d	12000
6	2	2	a	1900
7	2	3	f	6000
8	3	1	f	48000
9	3	2	f	3600
10	3	3	e	1600

使用 SPL XLL，输入公式：

=spl("=?.conj(ID=~(1),~.m(2:).group((#-1)\2).(ID|#|~))",A2:G7)

1111png

~.m(2:)从当前行的第2列开始取数据直到结尾，group函数将这些数据每2列分一组。

改成每 5 列一组，只要分组时把2改成 5。

https://stackoverflow.com/questions/78449082/using-power-query-to-unpivot-table

excel插件(54)

优质回帖

JohnBian • 6 个月前
这样的题在实务中很常见，在 ExcelHome 网站上隔三差五就能看到这样的需求。
不得不说，SPL 在处理这种需求时确实方便高效，相比较于 Power Query 而言，SPL 天生具有索引，这一点非常友好，提供了极大的便利。Power Query 不存在天然索引，需在用到时人为创建索引号，这是不方便的地方，但其笛卡尔积展开 List.TransformMany 效率也是可以的，不用像 stackoverflow 上回帖中写的那样繁琐。以下是 Power Query 解法：

用 SPL 解此类需求时，个人愚见，用 ntile() 分块也挺好的，当然，如果要达成帖子开头处所说的从某列开始 N 列为一组动态生成，还有很多细节要考虑。比如，如果其中的一组是空的，得过滤掉，对吧，或者，表头如何实现动态，而不是一个一个手动敲，等等…
```
=spl("=?.news(~.m(2:).group(ntile(#,2)).select(~.conj()!=[]);E@b([get(1)(1)|#|~])(1)).derive@x()",A2:G7)
```

5 回帖

请输入回帖内容 ...

JohnBian • 6 个月前
这样的题在实务中很常见，在 ExcelHome 网站上隔三差五就能看到这样的需求。
不得不说，SPL 在处理这种需求时确实方便高效，相比较于 Power Query 而言，SPL 天生具有索引，这一点非常友好，提供了极大的便利。Power Query 不存在天然索引，需在用到时人为创建索引号，这是不方便的地方，但其笛卡尔积展开 List.TransformMany 效率也是可以的，不用像 stackoverflow 上回帖中写的那样繁琐。以下是 Power Query 解法：

用 SPL 解此类需求时，个人愚见，用 ntile() 分块也挺好的，当然，如果要达成帖子开头处所说的从某列开始 N 列为一组动态生成，还有很多细节要考虑。比如，如果其中的一组是空的，得过滤掉，对吧，或者，表头如何实现动态，而不是一个一个手动敲，等等…
```
=spl("=?.news(~.m(2:).group(ntile(#,2)).select(~.conj()!=[]);E@b([get(1)(1)|#|~])(1)).derive@x()",A2:G7)
```
JohnBian • 6 个月前

这个好，简洁高效，大佬 666👍 👍 👍
如何在最左边增加一列针对整体的编号 1,2,3,4,5,…n 😄

1 回复
jinxing • 6 个月前

英文版
279400248 • 6 个月前

=….(#|~)

1 回复

JohnBian • 6 个月前

😄 谢谢老贼👍
我想多了…

=spl("= n=0,
        E@b( ?.conj( (r=~).m(2:)
                          .group((#-1)\2)
                          .select(~.conj()!=[])
                          .((n+=1)|#|r(1)|~)
             )
        )
",A2:G7)