Excel 将每组的前 3 名拼成串
分组明细表如下
| A | B | C | |
| 1 | Client | Location | Sales |
| 2 | ABC | New York | 5,000 |
| 3 | Florida | 4,000 | |
| 4 | Texas | 3,000 | |
| 5 | California | 2,000 | |
| 6 | Georgia | 1,000 | |
| 7 | XYZ | Tennessee | 10,000 |
| 8 | New Jersey | 8,000 | |
| 9 | Washington | 6,000 | |
| 10 | New York | 4,000 | |
| 11 | DEF | Ohio | 7,500 |
| 12 | Colorado | 5,000 | |
| 13 | HIJ | Virginia | 8,000 |
要求将每组的前 3 名明细用逗号拼成串，与分组一同展示
| E | F | |
| 1 | Client | Top 3 Locaction |
| 2 | ABC | New York,Florida,Texas |
| 3 | XYZ | Tennessee,New Jersey,Washington |
| 4 | DEF | Ohio,Colorado |
| 5 | HIJ | Virginia |
使用 SPL XLL，输入公式：
=spl("=?.group@i(~(1)).([~(1)(1),~.top(-3;~(3)).(~(2)).concat@c()])",A2:C13)
group@i 可按条件分组，~(1) 表示当前行的第 1 个成员，top 函数求前 N 个成员，concat@c 用逗号合并成员。
https://www.reddit.com/r/excel/comments/1d82y1w/is_it_possible_to_return_the_top_n_of_a_row_field/
英文版