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目录中看到生成的文件，其结果正如本文开头所示。