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

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

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

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

示例7.6　按旬分段统计每月出租单位人员的租住情况

图7-17所示展示了某街道办2011年辖区内出租人口登记数据列表，如果希望按上中下旬分段统计每月出租单位人员的租住情况，请参照以下步骤。

图7-17　出租人口登记数据列表

步　骤1

在“出租人口登记表”工作表中的H1单元格输入“旬”，在H2单元格中输入公式，并将公式复制到D190单元格，如图7-18所示。

H2=IF(DAY(A2)<=10,“上旬”，IF(DAY(A2)<=20,“中旬”，“下旬”））

图7-18　添加辅助公式

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

步　骤2

以添加“旬”辅助列的“出入人口登记表”工作表A1:H190单元格区域为数据源，创建如图7-19所示的数据透视表。

步　骤3

对数据透视表的“日期”字段以步长为“月”进行自动组合，最终完成的数据透视表如图7-20所示。

图7-19　创建数据透视表

图7-20　最终完成的数据透视表

7.4.2　按条件组合日期型数据项

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

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

图7-21展示了某企业与物流公司的业务往来数据列表，该企业与物流公司的结算方式为月结，结算周期为每月26日至其下月的25日，结算日为每月的26日，如果希望根据此月结方式来统计每月月结报表，请参照以下步骤。

图7-21　货运记录数据列表

步　骤1

修改E1单元格内容为“年份”，在E2单元格输入以下公式，并复制到E1235单元格。

E2=IF(AND(MONTH(A2)=12,DAY(A2)>=26),YEAR(A2)+1,YEAR(A2))

修改F1单元格内容为“月份”，在F2单元格输入以下公式，并复制到F1235单元格，如图7-22所示。

F2=IF(AND(MONTH(A2)=12,DAY(A2)>=26),1,IF(DAY(A2)<=25,MONTH(A2),MONTH(A2)+1))

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