如何製作可以自動匯總不同部門人數的EXCEL公式?

「小琪，我已經教給你INDIRECT函數如何使用了，接下來，我們就可以製作公式啦！」顧城說道。

「顧城哥，我們接下來怎麼做呢？」

「看我的！我們接著前面的步驟來。」

Step5：在Step3中C5單元格的COUNTIF函數中，需要構建的指針式字符串為：'1月'!C:C，因此可以在參數一Range中插入INDIRECT函數，如圖 5163所示。

圖5-163

在彈出的INDIRECT函數參數對話框中的第一個參數Ref_text中，構建字符串，C$4&"!C:C"（如圖 5164所示）。注意由於公式需要橫向拖動，所以對C4單元格進行混合引用設置。第二個參數A1，由於表格形式為A1形式，所以無需填寫。最後點擊「確定」按鈕。（如圖 5164所示）

圖5-164

最終公式為：=COUNTIF(INDIRECT(C$4&"!C:C"),$C$2)

隨後，將公式橫向拖動，最終效果如圖 5165所示：

圖5-165

當「部門」處選擇「全部部門」時：

當「部門」處選擇「全部部門」時，則需要統計全公司的人數。此時可用使用COUNTA函數。

COUNTA函數詳解（如圖 5166所示）：

一、作用：COUNTA為統計函數，其主要用於統計區域中非空單元格的個數。

二、參數：本函數參數為Value，最多可以有255個參數，代表要進行計數的值或單元格。

圖5-166

在本例中，只需在第一個參數中選擇「1月」工作表中的C列即可（如圖 5167所示），此時參數一中的字符串為「'1月'!C:C」。

圖5-167

為了實現根據月份標籤的變化，選擇對應工作表中的C列數據，可以在COUNTA函數中，插入INDIRECT函數，使其根據月份標籤的變化，自動構建字符串，從而實現製作一個通用公式的目的，具體方法如下：

在COUNTA函數中插入INDIRECT函數（如圖 5168所示）。

圖5-168

在INDIRECT函數對話框中，利用「月份」標籤構建字符串：C$4&"!C:C"

圖5-169

最後，點擊「確定」按鈕，完成公式錄入（如圖 5169所示）。完整公式為：=COUNTA(INDIRECT(C$4&"!C:C"))。

特別說明：此公式統計的為C列所有的非空單元格個數，包括欄位名所在的第一行，所以如果需要統計人數，需在此公式的基礎上減去1，即：=COUNTA(INDIRECT(C$4&"!C:C"))-1，最後將公式橫向拖動，實現圖 5170的效果。

圖5-170

此時，我們已經根據部門選擇的結果製作了兩個不同的統計人數的公式，分別為：當選擇「全部部門」時，公式為=COUNTA(INDIRECT(C$4&"!C:C"))-1

當選擇某一個具體部門時，公式為=COUNTIF(INDIRECT(C$4&"!C:C"),$C$2)

接下來就可以利用IF函數通過對部門選擇的結果進行判斷，然後選擇合適的統計人數的公式就可以了。具體方法如圖 5171所示：

圖5-171

在IF函數對話框中，錄入判斷條件，當C2單元格內容為「全部部門」時，則採用公式COUNTA(INDIRECT(C$4&"!C:C"))-1，否則就採用公式COUNTIF(INDIRECT(C$4&"!C:C"),$C$2)

最終完整公式為：=IF($C$2="全部部門",COUNTA(INDIRECT(C$4&"!C:C"))-1,COUNTIF(INDIRECT(C$4&"!C:C"),$C$2))

由於工資表是隨月份，逐步添加的，如圖 5172所示，工資表只到5月份，因此6月份之後的匯總數據會出現錯誤值的情況，為避免出現這種情況可以再加入一個IFERROR函數進行處理。

圖5-172

具體方法如圖 5173所示，在C5單元格處插入IFERROR函數，在函數參數對話框中，首先將之前製作好的IF函數公式：IF($C$2="全部部門",COUNTA(INDIRECT(C$4&"!C:C"))-1,COUNTIF(INDIRECT(C$4&"!C:C"),$C$2))複製到Value參數中，然後在Value_if_error參數中錄入半角雙引號，表示當參數一中的公式結果為錯誤值時，返回空白單元格。

圖5-173

最後，點擊「確定」按鈕完成公式錄入。

完整公式為：=IFERROR(IF($C$2="全部部門",COUNTA(INDIRECT(C$4&"!C:C"))-1,COUNTIF(INDIRECT(C$4&"!C:C"),$C$2)),"")

隨後將公式橫向拖動，即可完成全年人數的統計工作。（如圖 5174所示）

圖5-174

特別說明：由於在此案例中，只有5個月的工資數據，因此人數匯總數據便截止到5月，當6月份工資表數據生成後，只需將6月份的工資數據複製到此工資薄中，並命名為「6月」，此匯總表便會自動生成6月份人數的相關匯總數據。

「小琪，到目前為止，一個完整的關於『人數』的自動匯總公式才算是製作完成！」

「顧總，這個公式實在是太複雜了，還好你是用分步組合法講的，不然我可要暈了！」

「小琪，對於新手來說，可以先通過分步的方法，把公式逐一製作出來，然後再將它們進行組合，有助於理清其中的邏輯關係，可以把複雜的公式變得簡單而且易於理解，就像學舞蹈一樣，老師都是從分解動作教起的。」

「顧總，沒想到你對舞蹈還有研究呢？」

「你忘了，你學跳舞的時候，成天在我面前跳，都是分解動作。」

「哼，還說呢，等我會跳一支完整的舞蹈的時候，你已經出國了！」

「我現在回來了，你再跳一個我看看！」

「好啊，那你先把這個工資匯總表給我講完，我就跳！」

「沒問題！」

小夥伴們，歡迎留言跟小編討論互動喲！

如果覺得不過癮，告訴大家一個好消息：顧城與小琪的故事即將在中國鐵道出版社出版，書名為《HR精英都是Excel控：人力資源量化管理和數據分析（職場進階版）》，很快大家就可以在書店裡看到顧城與小琪啦！大家還可以在網易雲課堂找到孫晨老師的視頻課程喲