求Google Sheets公式：从逗号分隔单列数据生成唯一值列
2026-7-21
Google Sheets 提取逗号分隔值的唯一列表公式
需求
单列数据中每行包含多个逗号分隔的值，需生成所有值的唯一列表并显示在一列。
公式（假设原始数据在A列）
=UNIQUE(FLATTEN(SPLIT(TEXTJOIN(", ", TRUE, A:A), ", ")))
公式说明
TEXTJOIN(", ", TRUE, A:A)：将A列所有非空单元格的内容用", "拼接成单个字符串SPLIT(..., ", ")：把拼接后的字符串按", "分割为多维数组FLATTEN(...)：将多维数组转换为一维数组UNIQUE(...)：提取一维数组中的唯一值，自动去重并按出现顺序排列
原始数据
| 原始数据 |
|---|
| Catch-Up Q3, GROWTH, H1 2023 [Business need - Regular], H1 2023 [ALL] |
| Catch-Up Q3, H1 2023 [Business need - Regular] |
| CORPO, H1 2023 [ALL], H1 2023 [Business need - Intensive] |
| CORPO, H1 2023 [Business need - Intensive] |
| CORPO, H1 2023 [Business need - Regular], H1 2023 [ALL] |
| GROWTH, Catch-Up Q3, H1 2023 [ALL], H1 2023 [Business need - Regular] |
| GROWTH, CORPO, H1 2023 [ALL], H1 2023 [Integration need] |
| GROWTH, H1 2023 [ALL], H1 2023 [Business need - Regular] |
| GROWTH, H1 2023 [Business need - Regular], H1 2023 [ALL] |
| GROWTH, H1 2023 [Integration need] |
| H1 2023 [ALL], CORPO, H1 2023 [Business need - Intensive] |
| H1 2023 [ALL], CORPO, H1 2023 [Integration need] |
| H1 2023 [ALL], GROWTH, H1 2023 [Business need - Regular] |
| H1 2023 [ALL], H1 2023 [Business need - Intensive], CORPO |
| H1 2023 [ALL], H1 2023 [Business need - Intensive], GROWTH |
| H1 2023 [ALL], H1 2023 [Business need - Regular], CORPO |
| H1 2023 [ALL], H1 2023 [Business need - Regular], GROWTH |
| H1 2023 [ALL], H1 2023 [Business need - Regular], OPS |
| H1 2023 [ALL], H1 2023 [Business need - Regular], TECH |
| H1 2023 [ALL], H1 2023 [Integration need], Catch-Up Q3, GROWTH |
| H1 2023 [ALL], H1 2023 [Integration need], CORPO |
| H1 2023 [ALL], H1 2023 [Integration need], OPS |
| H1 2023 [ALL], H1 2023 [Integration need], PRODUCT |
| H1 2023 [ALL], H1 2023 [Integration need], TECH |
| H1 2023 [ALL], OPS, H1 2023 [Business need - Regular] |
| H1 2023 [ALL], OPS, H1 2023 [Integration need] |
| H1 2023 [ALL], PRODUCT |
| H1 2023 [ALL], TECH, H1 2023 [Business need - Intensive] |
| H1 2023 [Business need - Intensive] |
| H1 2023 [Business need - Intensive], CORPO |
| H1 2023 [Business need - Intensive], CORPO, H1 2023 [ALL], OPS |
| H1 2023 [Business need - Intensive], CORPO, OPS, H1 2023 [ALL] |
| H1 2023 [Business need - Intensive], TECH, H1 2023 [ALL] |
| H1 2023 [Business need - Regular] |
| H1 2023 [Business need - Regular], Catch-Up Q3 |
| H1 2023 [Business need - Regular], GROWTH, H1 2023 [ALL] |
| H1 2023 [Business need - Regular], H1 2023 [ALL], CORPO, H1 2023 [Business need - Intensive] |
| H1 2023 [Business need - Regular], H1 2023 [ALL], GROWTH |
| H1 2023 [Business need - Regular], H1 2023 [ALL], OPS |
| H1 2023 [Business need - Regular], H1 2023 [ALL], PRODUCT |
| H1 2023 [Business need - Regular], H1 2023 [ALL], TECH |
| H1 2023 [Business need - Regular], OPS, Catch-Up Q3 |
| H1 2023 [Business need - Regular], OPS, H1 2023 [ALL] |
| H1 2023 [Business need - Regular], TECH, H1 2023 [ALL] |
| H1 2023 [Integration need] |
| H1 2023 [Integration need], H1 2023 [ALL], OPS |
| H1 2023 [Integration need], TECH |
| H1 2023 [Integration need], TECH, H1 2023 [ALL] |
| OPS, H1 2023 [ALL], H1 2023 [Business need - Intensive] |
| OPS, H1 2023 [ALL], H1 2023 [Integration need] |
| OPS, H1 2023 [Business need - Intensive] |
| OPS, H1 2023 [Business need - Intensive], H1 2023 [ALL] |
| OPS, H1 2023 [Business need - Regular], H1 2023 [ALL] |
| OPS, H1 2023 [Integration need], H1 2023 [ALL] |
| PRODUCT, H1 2023 [ALL], H1 2023 [Business need - Regular] |
| PRODUCT, H1 2023 [ALL], H1 2023 [Integration need] |
| PRODUCT, H1 2023 [Business need - Intensive] |
| PRODUCT, H1 2023 [Business need - Regular] |
| PRODUCT, H1 2023 [Integration need], H1 2023 [ALL] |
| TECH, H1 2023 [ALL], Catch-Up Q3, H1 2023 [Business need - Intensive] |
| TECH, H1 2023 [ALL], H1 2023 [Business need - Regular] |
| TECH, H1 2023 [ALL], H1 2023 [Integration need] |
| TECH, H1 2023 [Business need - Intensive], H1 2023 [ALL] |
| TECH, H1 2023 [Business need - Regular], H1 2023 [ALL] |
| TECH, H1 2023 [Integration need] |
预期结果
| 结果 |
|---|
| Catch-Up Q3 |
| CORPO |
| GROWTH |
| H1 2023 [ALL] |
| H1 2023 [Business need - Intensive] |
| H1 2023 [Business need - Regular] |
| H1 2023 [Integration need] |
| OPS |
| PRODUCT |
| TECH |
内容的提问来源于stack exchange，提问作者fini-stair
相关产品推荐
相关产品推荐
