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)))」，然後下拉複製。

最後用截止月份的「達成」除以「計劃」計算達成率即可，動態統計截止月份達成率的追蹤表便完成了。