在项目组合中运用函数辅助处理

使用分组功能对数据透视表中的数据项进行自动组合，存在诸多限制，有时不能按照用户的意愿进行组合，不利于对数据进行分析整理。如果结合函数对数据源进行辅助处理，则可以大大增强数据透视表组合的适用性，以满足用户的分析需求。

按不等距长自动组合数值型数据项

在数据透视表中对于不等距长的数值型数据项的组合，往往需要用手动组合的方式来完成，如果数据透视表中需要手动组合的数据量很多，则操作起来将会很烦琐。通过在数据源中添加函数辅助列的方式可以轻松解决这类问题。

示例：按旬分段统计客户投资金额

图9-19所示为某公司客户投资情况统计表，若要按上、中、下旬分段统计每月客户投资情况，按旬进行分组，并不是一个等距的步长，如果利用手动组合完成则费时费力，此时需要在数据源中添加一个辅助列，辅助完成这样的分组，具体操作步骤如下。

图9-19客户投资情况统计表

步骤1在数据源中的I1单元格内输入“旬”，在I2单元格中输入以下公式，并将公式快速填充到I256单元格，如图9-20所示。

图9-20在数据源中添加辅助列公式

=IF(DAY(H2)<=10,'上旬',IF(DAY(H2)<=20,'中旬','下旬'))

公式解析：该公式利用DAY函数返回日期的天数，再用IF函数将天数与旬的段值比较，从而获得该日期所属的旬段。

步骤2以添加“旬”辅助列后的工作表为数据源创建数据透视表，如图9-21所示。

图9-21创建数据透视表

步骤3对数据透视表的“出借日”字段以步长为“月”和“年”进行自动组合，最终完成按旬分段统计客户投资金额，如图9-22所示。

图9-22最终完成的数据透视表

按条件组合日期型数据项

在数据透视表中，日期型字段可以按年、月、日等多个日期单位进行自动组合，但对于需要跨月交叉进行组合日期时，难以使用自动组合的方式来实现，如何解决这种情况，下面介绍一种快速实现的方法。

示例：制作跨月月结汇总报表

图9-23所示为某金融公司客户投资情况统计表，该公司业绩结算方式为月结算，结算周期为每月25日至次月25日，结算日为每月26日，如果希望根据此月结方式来统计每月业绩报表，具体操作步骤如下。

图9-23客户投资情况统计表

步骤1在数据源中的I1单元格中输入“年份”，在I2单元格中输入以下公式，并填充至I256单元格。I2=IF(AND(MONTH(H2)=12,DAY(H2)>=26),YEAR(H2)+1,YEAR(H2))

在数据源中的J1单元格中输入“月份”，在J2单元格中输入以下公式，并填充至J256单元格，如图9-24所示。

图9-24添加辅助列

J2=IF(AND(MONTH(H2)=12,DAY(H2)>=26),1,IF(DAY(H2)<=25,MONTH(H2),MONTH(H2)+1))

公式解析：第一个公式通过YEAR函数返回出借日中的年份值，MONTH函数返回出借日中的月份值，再通过IF函数来判断，对于月份值等于12，且出借日的天数值大于等于26的出借日记录归纳到下一年，否则归纳到本年。同理，使用第二个公式将月份值等于12，且出借日的天数值大于等于26的日期记录归纳到下一月，否则归纳到本月中。

步骤2以添加了“年份”和“月份”辅助列的数据源工作表的A1:J256单元格区域创建数据透视表，如图9-25所示。

图9-25创建数据透视表

END