Excel函数应用篇：动态统计截止月份的达成率

实际工作中，经常需要制作预算与实际使用、指标与达成等计算达成率的追踪报表，一般都会将年度计划拆分到12个月，查看月度达成率的同时，查看截止到当月的累计达成率也同样重要，因为该数据更能反映年度计划的完成状况。

当然，可以把表格设计成这样，每个月份“达成率”之后增加一列“累计达成率”，即从1月截止到当月的实际完成与计划的比值。

实际上，我们还可以动态统计截止月份的达成率，将表格设计得更加灵活和简洁一些。

首先是在表格上方的单元格B3利用数据有效性创建一个数字1-12的下拉框（关于数据有效性，请参阅Excel的数据有效性工具），供选择截止月份。

然后统计截止月份的“计划”数，在单元格B6键入公式“=SUMPRODUCT($E6:$AN6*($E$5:$AN$5=$B$5)*(((COLUMN(E:AN)-5)/3)<($B$3)))”，并下拉复制。

SUMPRODUCT函数的主要功能是返回数组乘积的和，结合逻辑运算返回的逻辑值数组可以解决多条件求和的问题（关于SUMPRODUCT函数，请参阅Excel数组函数Sumproduct()的用法和用途）。

公式中包括了三个数组：

数组1$E6:$AN6为东区1-12月份各项数据“{7085,7014,0.989978828510939,7913,8625,1.08997851636547,7650,7726,1.00993464052288,5785,5785,1,10866,12061,1.10997607215167,5785,0,0,7682,0,0,4691,0,0,7786,0,0,7704,0,0,7066,0,0,5819,0,0}”，包括“计划”、“达成”和小数形式的“达成率”。

数组2($E$5:$AN$5=$B$5)判断对应列的数据是否为“计划”数。这是一个逻辑运算，返回一个逻辑值的数组“{TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE}”

数组3(((COLUMN(E:AN)-5)/3)<($B$3)))判断对应列是否在需要统计月份内，也是一个逻辑运算，生成的逻辑值数组为“{TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE}”。

其中COLUMN(E:AN)返回对应列的列值“{5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40}”，通过算法(COLUMN(E:AN)-5)/3)将相应月份的返回值控制在月份以内，所以返回值<($B$3)即小于选择的截止月份就是满足条件的值。

逻辑值参加运算时，TRUE等于1，FALSE等于0，三个数组相乘然后相加，其实就是东区数据中同时满足“计划”列和统计月份的数据相加（数组运算，请参阅知道这些Excel数组概念和运算规则，数组公式就豁然开朗了）。

统计“达成”的思路也是一样的，在单元格C6键入公式“=SUMPRODUCT($E6:$AN6*($E$5:$AN$5=$C$5)*(((COLUMN(E:AN)-5)/3)<($B$3)))”，然后下拉复制。

最后用截止月份的“达成”除以“计划”计算达成率即可，动态统计截止月份达成率的追踪表便完成了。