Excel如何将多值数据拆分为单球员对应单个国家的独立行？

适用场景 #

原表格结构为：A列是国家名称，B列是用统一分隔符（如逗号）拼接的多名球员，需要将其拆分为每行1个国家对应1名球员的格式。

方案1：适用于Excel 365/2021（支持动态数组） #

假设原数据在A2:A10（国家）和B2:B10（拼接球员），在空白单元格（如D2）输入以下公式，公式会自动溢出所有结果：

=HSTACK(TOCOL(IF(TEXTSPLIT(B2:B10, ",")<>"", A2:A10, "")), TOCOL(TEXTSPLIT(B2:B10, ",")))

公式解释 #

1. TEXTSPLIT(B2:B10, ",")：将每个单元格的球员按逗号拆分成多列的二维数组
2. IF(TEXTSPLIT(B2:B10, ",")<>"", A2:A10, "")：为每个拆分后的球员匹配对应的国家，空值位置填充空白
3. TOCOL(...)：将二维的国家匹配结果转换为一维列
4. TOCOL(TEXTSPLIT(B2:B10, ","))：将拆分后的球员二维数组转换为一维列
5. HSTACK：将国家列与球员列横向合并，得到最终的单行对应结果

方案2：适用于旧版Excel（不支持动态数组） #

步骤1：添加辅助列计算球员数量 #

在C2单元格输入公式，下拉填充到数据末尾：

=LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1

该公式用于计算每个国家对应的球员总数。

步骤2：提取国家列 #

在D2单元格输入数组公式（输入后按Ctrl+Shift+Enter确认），下拉填充直到出现错误值：

=INDEX(A:A, SMALL(IF(ROW($A$2:$A$10)-ROW($A$2)+1<=SUM($C$2:$C$10), ROW($A$2:$A$10)), ROW(A1)))

步骤3：提取球员列 #

在E2单元格输入数组公式（同样按Ctrl+Shift+Enter确认），下拉填充：

=INDEX(TEXTSPLIT(INDEX(B:B, SMALL(IF(ROW($A$2:$A$10)-ROW($A$2)+1<=SUM($C$2:$C$10), ROW($A$2:$A$10)), ROW(A1))), ","), MOD(ROW(A1)-1, INDEX($C:$C, SMALL(IF(ROW($A$2:$A$10)-ROW($A$2)+1<=SUM($C$2:$C$10), ROW($A$2:$A$10)), ROW(A1)))-ROW($C$2)+2)+1)

注意事项 #

• 确保球员之间的分隔符统一（如果有空格，可在公式中加入SUBSTITUTE(B2, " ", "")清除空格，比如TEXTSPLIT(SUBSTITUTE(B2:B10, " ", ""), ",")）
• 旧版Excel公式下拉时，出现#NUM!或#VALUE!即表示已提取完所有数据

内容的提问来源于stack exchange，提问作者janani