如何在Google Sheets或Excel中根据选秀编号分类整理选秀数据

阿华AIGC实验室

2026-4-30

我来帮你搞定这个选秀数据格式转换的问题，不管用Google Sheets还是Excel都能实现，分步骤来操作就很清晰：

第一步：先把原始文本拆成「选秀编号+食物」的结构化两列

假设你的原始数据是放在单个单元格里（比如A1）：Pick selection 1 salad 2 pizza 3 pizza 4 pizza 5 bread 6 carrots 7 pizza 8 apples 9 apples 10 pizza 11 salad

在Google Sheets里拆分：

先去掉开头的冗余文本：在B1单元格输入公式：
```
=SUBSTITUTE(A1, "Pick selection ", "")
```
得到纯数据串：1 salad 2 pizza 3 pizza ... 11 salad
提取选秀编号到C列（C1开始）：
```
=INDEX(SPLIT(B1, " "), SEQUENCE(ROUNDUP(COUNTA(SPLIT(B1, " "))/2), 1, 1, 2))
```
这个公式会按步长2提取拆分后的第1、3、5...个元素，也就是所有编号。
提取食物到D列（D1开始）：
```
=INDEX(SPLIT(B1, " "), SEQUENCE(ROUNDUP(COUNTA(SPLIT(B1, " "))/2), 1, 2, 2))
```
同理，提取第2、4、6...个元素，对应每个编号的食物。

提取编号到C列：

=INDEX(TEXTSPLIT(B1, " "), SEQUENCE(ROUNDUP(COUNTA(TEXTSPLIT(B1, " "))/2), 1, 1, 2))

提取食物到D列：

=INDEX(TEXTSPLIT(B1, " "), SEQUENCE(ROUNDUP(COUNTA(TEXTSPLIT(B1, " "))/2), 1, 2, 2))

如果是旧版Excel，直接用「数据」选项卡的「文本分列」功能，按空格拆分后，手动把编号和食物列整理到两列即可。

第二步：按食物分组，合并对应的选秀编号

现在我们已经有了结构化的两列数据，接下来按食物分类合并编号：

提取不重复的食物列表：在E1输入Food，E2单元格输入：
```
=UNIQUE(D:D)
```
下拉填充会自动列出所有不重复的食物。
合并对应编号：在F2输入：
```
=TEXTJOIN(" ", TRUE, FILTER(C:C, D:D=E2))
```
这个公式会筛选出当前食物对应的所有编号，并用空格连接起来，下拉填充所有食物行。
（可选）合并成你要的最终格式：在G2输入=E2&" "&F2，下拉后，再用=TEXTJOIN(" ", TRUE, G:G)就能得到完整的目标字符串：Food apples 8 9 carrots 6 bread 5 salad 1 11 pizza 2 3 4 7 10

提取不重复食物：E2输入=UNIQUE(D:D)，下拉填充。
合并编号：F2输入：
```
=TEXTJOIN(" ", TRUE, FILTER(C:C, D:D=E2))
```
下拉填充后，同样用=TEXTJOIN(" ", TRUE, G:G)（G列是=E2&" "&F2）得到最终结果。

选中结构化的两列数据，点击「数据」选项卡→「从表格/区域」（旧版选「自表格」），导入Power Query编辑器。
在编辑器里，选中「食物」列，点击「转换」选项卡→「分组依据」，设置：
- 分组依据：食物
- 新列名：选秀编号
- 操作：自定义，公式填写Text.Combine(Text.From([选秀编号]), " ")
关闭并上载数据，得到分组后的结果，再整理成你要的格式即可。