在excel中通过函数如何实现“一对多”的数据展开

网友问题：如何实现将“表一”的数据转换成“表二”的格式（如下图），且需要一次性完成。

实务工作中的数据量非常大，每次成百上千条数据，其中每个业务单据号对应的型号规格，是由逗号, 分隔的多个值组成的字符串，而且每个业务单据号的对应的型号规格的数量不一样。本文共总结了四种方案，建议使用第三或第四种方案，对应函数不是很熟悉的，建议直接用第四种Power Query的方式达到想要的目的，也希望有更简单的函数实现的朋友交流。

方案一：REDUCE+ VSTACK+ TOCOL+ TEXTSPLIT等函数组合

在E2录入“=DROP(REDUCE("", SEQUENCE(ROWS(A2:A4)), LAMBDA(x,y, VSTACK(x, LET(单据号, INDEX(A2:A4, y), 规格, TOCOL(TEXTSPLIT(INDEX(B2:B4, y), ",")), 行数, ROWS(规格), HSTACK(IF(SEQUENCE(行数), 单据号), 规格))))), 1)”

方案二：REDUCE+ VSTACK+ TRANSPOSE+ TEXTSPLIT等函数组合

在G2录入“=DROP(REDUCE("", SEQUENCE(ROWS(A2:A4)), LAMBDA(x,y, VSTACK(x, LET(单据号, INDEX(A2:A4, y), 规格, TRANSPOSE(TEXTSPLIT(INDEX(B2:B4, y), ",")), 行数, ROWS(规格), HSTACK(IF(SEQUENCE(行数), 单据号), 规格))))), 1)”

同方案一相比，TOCOL函数变成了TRANSPOSE函数，其他基本一致。

方案一和方案二公式中主要函数的用法解释：

ØTEXTSPLIT函数：将型号规格列中包含多个规格的文本，按逗号拆分开。

ØTOCOL函数或TRANSPOSE函数：把拆分出来的横向数据转成纵向的一列。

ØHSTACK+INDEX函数：把 N 列的单号与拆分出的那一列规格，左右拼接在一起。

ØREDUCE + VSTACK函数：让公式按行号自动循环遍历每一行数据，把每一行生成的结果上下堆叠起来。

ØDROP函数：去掉REDUCE 初始生成的那个空行。

方案三：TEXTSPLIT+ TEXTJOIN+ SUBSTITUTE组合

在I2录入=TEXTSPLIT(TEXTJOIN(",", TRUE,A2:A4 &”|”& SUBSTITUTE(B2:B4,”,", ",”&A2:A4 &”|”）),”|”，”,")

公式中主要函数的用法解释：

ØSUBSTITUTE函数：把型号规格列中的规格规格里的逗号“，”替换成逗号“，”+A列对应业务单据号+竖线“|”，这样就将B3格就变成了1.20*199.4*C,26XHXS001-0012346|1.20*199.4*C,26XHXS001-0012346|1.20*199.4*C。

ØTEXTJOIN函数：把这两行连成一长串

ØTEXTSPLIT函数：再按竖线“|”拆分成多列，按逗号“,”拆分成多行。

核心思考逻辑：设计两个不同的分隔符，可以分别进行列分隔和行分隔。这里本身型号规格中有逗号“,”，并要求进行行分别现实，所以就直接将“，”作为行分隔符，另外设计增加竖线"|" 作为列分隔符。相对和前两个方案来说，变得简单很多。

方案四：Power Query

说实话，我也被复杂的嵌套函数弄烦了，搞了很久，特别是对应FLATTEN函数，花了很多精力，也没搞出来，还好研究了方案三相对简单的方法。对函数不是很熟悉的朋友们，建议直接用Power Query，具体操作步骤如下：

Step1：点击【数据】，点击【来自表格/区域】

Step2：选中“型号规格”列，点击【拆分列】

Step3：选择【按分隔符】。

Step4：选择【逗号】，高级选项里选择【拆分为行】，点击【确定】

Step5：点击【关闭并上载】

很快就出现完美的结果，直接用Power Query，比函数更为简单，而且不会报错，数据运行很快，大量的数据也不会卡，在目前的这个案例来说，比函数更有优势！