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/
英文版