总表转换成分表的四个Excel公式

先说要求，单元格A1:D5为源数据，是总表，现在要转换成分表，如单元格F1:G14的格式，可以看到几个规律：

1.分表中的每个记录都包含标题。

2.总表转换成分表，数据要从横向变成纵向的。

3. 分表中各个记录之间是有一个空行的的。

根据上述规律，可以提炼出一些思路，以及与此相关所需要用到的函数，比如规律1，总表中的标题要重复使用，等于是要将单元格区域A1:D1进行多次重复，我以前的文章就提到过，这里就有两个现成的思路，一个是IF+Sequence函数组合，另外一个是T+Sequence函数组合，当然还可以使用Chooserows()函数，只不过引用的行里面永远包括第1行，后面的行数则是动态的，而且是有规律的是，是{2，3，4，5}。

规律2，数据要从横向变成纵向，很明显可以使用transpose()函数，当然也可以使用Tocol()函数，因为从行变成一列，本身就是从横向变成纵向。

规律3，分表中的各记录之间有一个空行，这个好解决，我们在选取源数据的时候可以多选一列，比如表头是单元格A1:D1，选择数据的时候我们可以选取单元格A1:E1,这样从行变成列的时候，自然就多了一行。但是，这里又产生一个问题，我们要的是空行，意思是不要有任何的字符，但这样直接转换可能出现的是“0”而不是空字符,所以要嵌套一个if()函数进行判断。

=LET(a,A1:E1,b,A2:E5,

     c,TOCOL(IF(b="","",b)),

     d,TOCOL(IF(b<>"",a,"")),

     HSTACK(d,c))

表头，数据分别使用Tocol()函数，将其分别变成一列再用Hstack()函数横向连接，这里面嵌套了if()函数，如果不使用的话会是这个效果：

方法2，与方法1类似，使用了If+Sequence组合，只有标红色的部分是不同的

=LET(a,A1:E1,b,A2:E5,

c,TOCOL(IF(b="","",b)),

d,TOCOL(IF(SEQUENCE(ROWS(b),COLUMNS(b)),IF(a="","",a))),

HSTACK(d,c))

方法3，总体思路一致，不过这里使用了T+Sequence组合

=LET(a,A1:E1,b,A2:E5,

c,TOCOL(IF(b="","",b)),

d,TOCOL(a&T(SEQUENCE(ROWS(b),COLUMNS(b)))),

e,HSTACK(d,c),e)

T+Sequence组合的详细解释请参看前一篇文章：

T函数+Sequence函数:将整行数据按指定次数重复

方法4，这里就使用了Chooserows()函数

=IFERROR(DROP(

 REDUCE("",ROW(2:5),                  LAMBDA(x,y,VSTACK(x,TRANSPOSE(CHOOSEROWS(A1:D5,1,y)),"")))

    ,1),"")

这里的核心就是：CHOOSEROWS(A1:D5,1,y)，意思是每次遍历都提取第1行以及第y行，y=row(2:5)={2,3,4,5}, 比如遍历第一次的时候就提取第1，2行，第二次遍历的时候提取第1，3行,以此类推，嵌套transpose()是因为从横向变成纵向，每次遍历的结果纵向连接，drop()函数是用于删除第一行（为空行，累加器所致）， 加Iferror()是清除里面的错误值。