实际工作中，经常需要制作预算与实际使用、指标与达成等计算达成率的追踪报表，一般都会将年度计划拆分到12个月，查看月度达成率的同时，查看截止到当月的累计达成率也同样重要，因为该数据更能反映年度计划的完成状况。
当然，可以把表格设计成这样，每个月份“达成率”之后增加一列“累计达成率”，即从1月截止到当月的实际完成与计划的比值。
实际上，我们还可以动态统计截止月份的达成率，将表格设计得更加灵活和简洁一些。
首先是在表格上方的单元格B3利用数据有效性创建一个数字1-12的下拉框（关于数据有效性，请参阅Excel的数据有效性工具），供选择截止月份。
然后统计截止月份的“计划”数，在单元格B6键入公式“=SUMPRODUCT(E6:AN6*(E5:AN5=B5)*(((COLUMN(E:AN)-5)/3)(B3)))”，并下拉复制。
SUMPRODUCT函数的主要功能是返回数组乘积的和，结合逻辑运算返回的逻辑值数组可以解决多条件求和的问题（关于SUMPRODUCT函数，请参阅Excel数组函数Sumproduct()的用法和用途）。
公式中包括了三个数组：
数组1E6:AN6为东区1-12月份各项数据“{,,0.,,,1.47,,,1.88,,,1,,,1.67,,0,0,,0,0,,0,0,,0,0,,0,0,,0,0,,0,0}”，包括“计划”、“达成”和小数形式的“达成率”。
数组2(E5:AN5=B5)判断对应列的数据是否为“计划”数。这是一个逻辑运算，返回一个逻辑值的数组“{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)(B3)))判断对应列是否在需要统计月份内，也是一个逻辑运算，生成的逻辑值数组为“{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)将相应月份的返回值控制在月份以内，所以返回值(B3)即小于选择的截止月份就是满足条件的值。
逻辑值参加运算时，TRUE等于1，FALSE等于0，三个数组相乘然后相加，其实就是东区数据中同时满足“计划”列和统计月份的数据相加（数组运算，请参阅知道这些Excel数组概念和运算规则，数组公式就豁然开朗了）。
统计“达成”的思路也是一样的，在单元格C6键入公式“=SUMPRODUCT(E6:AN6*(E5:AN5=C5)*(((COLUMN(E:AN)-5)/3)(B3)))”，然后下拉复制。
最后用截止月份的“达成”除以“计划”计算达成率即可，动态统计截止月份达成率的追踪表便完成了。
异乡闲人喜欢作者