Excel 转置分组时如何保留没有对应数据的空组
例题描述
有商品日销售记录表sales.xlsx，有些日期没有销售记录，部分数据如下图所示：
1、需要以日期为左表头、产品为上表头做一个交叉统计表，且要同时列出没有销售数据的日期，如下图所示：
2、需要以周为左表头、产品为上表头做一个交叉统计表，且要同时列出没有销售数据的周，如下图所示：
实现步骤
1、 运行集算器
可去润乾官网下载职场版，同时下载一个免费授权就够了，首次运行时会提示加载。
2、 编写脚本：
（1）按日期统计
把代码列出来看得清楚点：
A |
|
1 |
>dir="E:/pivot/" |
2 |
=file(dir+"sales.xlsx").xlsimport@t() |
3 |
=A2.pivot(saledate;product,amount) |
4 |
=A3.min(saledate) |
5 |
=A3.max(saledate) |
6 |
=A4|(A5-A4).(A4+~) |
7 |
=A3.align(A6,saledate) |
8 |
=A7.new(A6(#):saledate,bread,cookie,milk) |
9 |
=file(dir+"sales_day.xlsx").xlsexport@t(A8) |
A1 定义保存文件的目录
A2 读入sales.xlsx文件数据，选项@t表示首行是列标题
A3 对A2的数据进行转置分组，按saledate分组为左表头，product列的值为上表头，amout为交叉统计值
A4 求A3中的最小日期
A5 求A3中的最大日期
A6 把从最小日期到最大日期的所有日期按顺序组成一个序列
A7 把A3转置分组的saledate按A6的顺序对齐
A8 用new函数重新定义A7的每一行，#表示A7的当前行号，取A6中的同行号的日期值为saledate，再取A7当前行的bread、cookie、milk值
A9 把A8中的结果保存到文件sales_day.xlsx中
（2）按周统计
编写代码如下：
A |
|
1 |
>dir="E:/pivot/" |
2 |
=file(dir+"sales.xlsx").xlsimport@t() |
3 |
=A2.pivot@s(pdate@w(saledate):Sunday;product,sum(amount)) |
4 |
=A3.min(Sunday) |
5 |
=A3.max(Sunday) |
6 |
=A4|((A5-A4)\7).(A4+~*7) |
7 |
=A3.align(A6,Sunday) |
8 |
=A7.new(string(A6(#),"MM/dd/yyyy")/" -"/string(A6(#)+6,"MM/dd/yyyy"):week,bread,cookie,milk) |
9 |
=file(dir+"sales_week.xlsx").xlsexport@t(A8) |
A1 定义保存文件的目录
A2 读入sales.xlsx文件数据，选项@t表示首行是列标题
A3 对A2的数据按saledate所在的周日进行转置分组并命名为Sunday，product列的值为上表头，本周各产品的amout之和为交叉统计值。pdate@w(saledate)表示求saledate所在周的周日。
A4 求A3中的最小周日
A5 求A3中的最大周日
A6 把从最小周日到最大周日的所有周日按顺序组成一个序列
A7 把A3转置分组的Sunday按A6的顺序对齐
A8 用new函数重新定义A7的每一行，#表示A7的当前行号，取A6中的同行号的周日值按格式转成串再拼上它加6天后的周六值按格式转成串，命名为week，再取A7当前行的bread、cookie、milk值
A9 把A8中的结果保存到文件sales_day.xlsx中
3、 按F9运行以上两段程序，运行结束后就可以在E:/pivot目录中看到生成的文件，其结果正如本文开头所示。
【附件】 pivot.zip
