Excel的表格不规范,每三行对应一条记录,纵向每三列对应一格。
A | B | C | D | E | |
1 | Fruit | Breed | Shop | price | qty |
2 | Musk | ||||
3 | Lime | Lavonne | 1 | 4 | |
4 | Melon | ||||
5 | Apple | Kirin | Way Side | 2 | 5 |
6 | |||||
7 | |||||
8 | peach | Japan | |||
9 | Kubo | ||||
10 | Daimomo | Cshop | 3 | 4 |
请规范化表格,把每一列的每3行的格值拼进一个格子,用空格分隔。
G | H | I | J | K | |
1 | Fruit | Breed | Shop | price | qty |
2 | Lime | Musk Melon | Lavonne | 1 | 4 |
3 | Apple | Kirin | Way Side | 2 | 5 |
4 | peach | Japan Kubo Daimomo | Cshop | 3 | 4 |
使用 SPL XLL,输入公式:
=spl("=E@2p(E@2p(?).(~.group((#-1)\3).(~.select(~).concat("" ""))))",A2:E10)
函数 E@2p用于二维表转置,group 分组,# 是行号。