excel批量转置

实现目标：如图中所示，需要将B列中逗号分隔开的每一个内容和A列中的编号对应。

                                        

---

1.将B列内容分列

    1.1选中B列内容分列，并选中首行筛选，从G列开始之后的列已经没有数据

        

        在H列中填入公式=counta（E1:G1）,统计分列前B列每个单元格中包含的内容数。

        

2.插入与B列单元格中内容数量相同的空行

        自动填充相应数量的空行：

               I列第一个单元格中填入数字1，第二个单元格中填入公式=H1+I1，等于当前行在填充空行后所处的位置行数。在I列最后从1开始继续填充，共递增填充1070个单元格。

         

               选中I列，去除重复值，将会删除重复数据中的后一个单元格。将I列剪切至左右两侧列内容没有间断的位置。选中I列排序（扩展选定区域）。可自动填充空行，如图所示：

         

3.自动补全编号

        自动填充间断列编号：

               清除B列内容，第一个单元格复制左侧内容，下方单元格输入公式=if(isblank(A2),B1,A2),判断左侧单元格是否为空，为空等于上方单元格，不为空等于左侧单元格。

               

4.批量转置 

     确定offset公式偏移量其实单元格位置：

       A列前插入空列，第一个单元格中填入公式=if(isblank(b1),'',"$"&char(64+column()+3)&"$"&row()),计算出偏移量起始位置。

下方单元格输入公式=if(isblank(b1),A1,"$"&char(64+column()+3)&"$"&row())。若右侧为空等于上方单元格，否则计算起始位置。如下图所示：

         

        计算偏离量：

         A列前插入一空列，第一个单元格填入1，下方单元格填入公式=if(isblank(c2),A1+1,1),判断右侧c列单元格是否为空，如果为空则等与上方单元格加1，否侧等于1（从1开始递增）。如下图：

        

       使用offset公式批量转置，删除c列，插入空列到D列，填入公式=offset（indirect（B1),0,A1,1,1)，通过提前计算出来的起始位置和偏移量，完成批量转置。