錦子老師

如下圖「日報表」工作表是公司在各個百貨公司清潔人員(總人數50人)起訖工作時間。

我想要在「統計表」工作表中統計每一個時段的人數及平均人數百分比。

▍統計各個時段工作人數

公式一：MATCH(DAY(B$1),日報表!$A$2:$Q$2,0) 首先我們要在「日報表」工作表A2:Q2範圍中找尋與統計表B1儲存格同一天的儲存格位於那一欄，由於公式會向右及向下複製故A2:Q2必須加上$號鎖定，而B1必須固定在第一列所以將1前面加$號鎖定。

公式二：OFFSET(日報表!$A$1,4,公式一-1,100,1) 然後將從A1儲存格移動4列N欄(公式一傳回結果減1)，再框選100列1欄儲存格範圍(每一位員工工作時間表)，由於會向右及向下複製公式，故將A1加上$號鎖定。

公式三：LEFT($A2,5) 傳回A2儲存格內容左邊5個字元，由於會向右複製公式，為將A欄固定，故在其前面加上$號鎖定。

公式四：VALUE(公式三) 將公式三傳回結果成數值。

公式五：OFFSET(日報表!$A$1,4,公式一,100,1) 然後將從A1儲存格移動4列N欄(公式一傳回結果)，再框選100列1欄儲存格範圍(每一位員工工作時間表)，由於會向右及向下複製公式，故將A1加上$號鎖定。

公式六：RIGHT($A2,5) 傳回A2儲存格內容右邊5個字元，由於會向右複製公式，為將A欄固定，故在其前面加上$號鎖定。

公式七：VALUE(公式六) 將公式六傳回結果成數值。

公式八：COUNTIFS(公式二,"<="&公式四,公式五,">="&公式七) 統計公式二傳回範圍中小於等於公式四傳回結果並且公式五傳回範圍中大於等於公式七傳回結果的儲存格筆數。

公式九：IFERROR(公式八,"")如果公式八傳回結果為錯誤訊息表示找不到符合資料，則不填入資料。

所以B2儲存格輸入公式：「=IFERROR(COUNTIFS(OFFSET(日報表!$A$1,4,MATCH(DAY(B$1),日報表!$A$2:$Q$2,0)-1,100,1),"<="&VALUE(LEFT($A2,5)),OFFSET(日報表!$A$1,4,MATCH(DAY(B$1),日報表!$A$2:$Q$2,0),100,1),">="&VALUE(RIGHT($A2,5))),"")」。

▍統計各時段各櫃平均人數

如果B2儲存格不是空格，則填入B2/25(員工總人數)的值，否則不填入資料。

所以B3儲存格輸入公式：「=IF(B2<>"",B2/25,"")」。

▍複製公式

將B2:B3儲存格框選起來，向右複製公式到AF2:AF3儲存格，再向下複製到AF32:AF33儲存格。