报表转成清单型的明细表(二维表转一维),我用三种方法搞定它

读者微信群读者朋友路索蓝胖子提问:如何将二维的报表转成清单型的报表

这种需求很常见,特写文章总结一下。常用的方法有九种……

哦,不,有三种,分别为透视表法、PQ法、函数法。

数据源如下:

方法1:透视表法

Step 1:按Alt+D+P,打开透视表向导,选择多重合并计算数据区域,点击下一步

Step 2:选择区域,点击添加,然后点击下一步,透视表显示的位置选择“新工作表”

Step 3:透视后的结果如下图,双击F15单元格。

Step 4:搞定,收工

方法2:Power Query法

Step 1:数据--来自表格

Step 2:选定A产品到D产品四列,点击转换选项卡下的“逆透视列”

Step 3:点击主页的关闭并上载

上载后效果如下图:

逆透视在《偷懒的技术2:财务Excel表格轻松做》图书和以前的文章也介绍过,详见:

练习题045及答案:将不规范的考勤数据整理成清单式表格

方法3:函数公式法

分析:
将A1:E11转换为G1:I41,我们将其分拆为二个需求
1、A列转为G列,实际上就是将A列每个客户重复四次,公式:
FLOOR(ROW(A4),4)/4
解释:
ROW(A4)计算A4单元格的行号,结果为4
FLOOR,向下舍入取最接近的倍数。
2、将B1:E1转为H列,实际上就是将他们循环重复,公式:
MOD(ROW(A4),4)+1
解释:
MOD取余数
然后再将上面的序列装入到INDEX函数中,组成完整的公式:
G2单元格公式:
=INDEX($A$2:$A$11,FLOOR(ROW(A4),4)/4)
H2单元格公式:
=INDEX($B$1:$E$1,MOD(ROW(A4),4)+1)
I2单元格公式:
=INDEX($B$2:$E$11,FLOOR(ROW(A4),4)/4,MOD(ROW(A4),4)+1)
关于INDEX函数的详细解释及应用案例参见:
  • 行列交叉查询公式汇总及解释

  • 行列交叉查询公式汇总及解释(二)

  • 【文末赠书】行列交叉查询公式汇总及解释(三)

上面二个需求,实际上都用到了序列,要生成指定的序列所用到的方法基本上差不多,在之前写过几篇用函数画画的文章中有中介绍:
  • 牛,用函数公式居然能画沙漏!来,一起动手画一个
  • 用公式画一套“偷懒”牌台灯和铅笔,送给挑灯奋战夜读书的你,祝逢考必过!
  • 用函数公式画一棵圣诞树,祝大家岁岁平安
上面的三篇文章,在工作中似乎用不上。实际上,其原理就是生成指定序列,在文本处理或数据整理时常用到,比如本文。建议大家有时间阅读一下,掌握其生成序列的方法和函数。
(0)

相关推荐