動態匯總每個區域每個月的收入【Excel表格模板設置】

盧子早期基本上都是在各大Excel論壇、Excel交流群免費幫人解答疑難。後來聽從一個朋友的建議，在淘寶上開了一家幫人解答疑難的店鋪。在這期間，我發現一個現象，付費的人普遍比以前那些人更有禮貌。就因為這個原因，我慢慢喜歡上在淘寶上幫人解答問題，做自己喜歡的事，還能掙點零花錢，何樂而不為。這裡挑選一些我在淘寶幫人解答的問題，進行講解。

現在有4個明細表，表格的格式完全一樣，就只是行數不一樣而已，分別是成都、深圳、海口、武漢，在最後的匯總表對這4個表進行統計。原先是先用公式在明細表統計好，再引用過去。

統計C列的性別，男女人數分別多少

=COUNTIF(C3:C40,"男")

=COUNTIF(C3:C40,"女")

統計F列客戶狀態，新客戶跟舊客戶人數分別為多少

=COUNTIF(F3:F40,"新客戶")

=COUNTIF(F3:F40,"舊客戶")

統計各種金額：消費金額、應收、扣款項目、實收

=SUM(G3:G40)

=SUM(J3:J40)

=SUM(K3:K40)

=SUM(L3:L40)

4個明細表設置的公式都一樣，只是區域不一樣而已。

分別統計好後，就在匯總表依次引用，如C3的公式就是：

=成都!B41

這樣做會出現2個問題：

明細表的區域限制死了，如果後續有數據添加，不能自動統計；

這種統計只是針對1個月份，如果有多個月份也不能統計。

看到這裡，盧子大概就有了一個思路，就問lee：你每個明細表下面的統計能否刪除掉？

lee：這個是為了方便統計用的，如果有更好的方法統計可以刪除。

得到了一個肯定的說法後，盧子就對這個表格進行了小小的變動。

將明細表的各種統計刪除。

在日期後面插入一列，獲取月份。

=MONTH(A3)&"月"

用同樣的方法，將其他明細表也這樣操作。

為了方便測試，盧子在成都這個表添加了幾行2月份的數據。

同時也在匯總表添加2月份的區域，並將原來的公式刪除。

男性、女性、新客戶、舊客戶人數所使用的公式一樣，只是區域跟條件略做更改而已。

=COUNTIFS(INDIRECT(B3&"!d:d"),"男",INDIRECT(B3&"!b:b"),A3)

=COUNTIFS(INDIRECT(B3&"!d:d"),"女",INDIRECT(B3&"!b:b"),A3)

=COUNTIFS(INDIRECT(B3&"!g:g"),"新客戶",INDIRECT(B3&"!b:b"),A3)

=COUNTIFS(INDIRECT(B3&"!g:g"),"舊客戶",INDIRECT(B3&"!b:b"),A3)

消費金額、應收、扣款項目所使用的公式一樣，只是區域跟條件略做更改而已。

=SUMIF(INDIRECT(B3&"!b:b"),A3,INDIRECT(B3&"!h:h"))

=SUMIF(INDIRECT(B3&"!b:b"),A3,INDIRECT(B3&"!k:k"))

=SUMIF(INDIRECT(B3&"!b:b"),A3,INDIRECT(B3&"!l:l"))

實收：

=H3-K3

尾款：

=H3-I3-K3

本來做完後盧子就只是把最終的附件發給lee，但lee實在水平有限，典型的菜鳥。看了以後一頭霧水，啥都不懂，一定要盧子給她解釋。為了解釋這2條公式，盧子足足花了一個小時。

lee：我的本和筆都準備好了。

盧子：

=COUNTIF(C3:C40,"男")

=COUNTIF(區域,條件)

這個語法能否懂嗎？

因為你原來區域是固定的C3:C40，現在你區域不固定，也就是區域要大一點，那你乾脆就引用整列，寫C:C。

就是統計C列性別為男的次數，像其他的統計新舊客戶人數的也一樣的道理。

現在進入核心部分，因為有很多表，不可能每個表單獨設置公式。

lee：嗯。

盧子：你當然希望同一列下拉就全部表格匯總出來。

lee：對的。

盧子：

正常的話，成都就用=COUNTIF(成都!C:C,"男")

下面的深圳就改成=COUNTIF(深圳!C:C,"男")

這樣好麻煩。

lee：是，要是有100張那要改100次。

盧子：

你看，這樣下拉就全部出來了

=B3&"!c:c"

所有表格的區域都出來，對吧

lee：慢點，出來區域和後面匯總分表數據啥關係？

盧子：要製作區域出來，這個能理解吧。如統計成都就用=COUNTIF(成都!C:C,"男")。

lee：你的意思是不是，匯總表區域下面城市就用，=B3&"!c:c"這個公式來設置。

lee：這個公式翻譯成漢語是啥意義

盧子：剛剛那個b3&"!c:c"就是把內容合併起來而已，就是在區域後面增加!c:c，成都!c:c這樣。

lee：喔，懂了。

盧子：因為我們是通過&連接起來的，不是直接用區域生成的，所以需要增加一個函數INDIRECT，才能進行計算

=COUNTIF(INDIRECT(B3&"!c:c"),"男")

也就是這條公式的由來。

lee：INDIRECT這個函數是啥意思？

盧子：因為你不是直接引用，是用公式生成，所以在那裡增加這個函數。

lee：直接引用和公式生成哪個方便點啊？

盧子：剛剛不是解釋了嗎，如果有100個表，你就得直接引用100次，你不搞死人。

lee：喔喔喔喔，明白了。

大神，前面分表是一個月的，總表匯總一個月，那如果有多個月需要怎麼做？

盧子：那就用COUNTIFS函數，COUNTIF函數是單條件計數，COUNTIFS函數是多條件計數。

COUNTIFS函數語法：

COUNTIFS(條件區域1,條件1, 條件區域2,條件2……)

剛剛我在你的表格增加了一列計算月份，這樣有了前面COUNTIF函數的基礎，設置公式就變得很簡單。兩個函數的語法基本一樣，只是COUNTIFS可以多條件計數而已。

=COUNTIFS(INDIRECT(B3&"!d:d"),"男",INDIRECT(B3&"!b:b"),A3)

男女人數跟新老客戶用的公式都一樣，其他只要更改區域就可以。

前面都是計數，後面的是求和。單條件求和用SUMIF函數，多條件用SUMIFS函數。現在都是單條件求和，那就用SUMIF函數，語法如下：

SUMIF(條件區域,條件,求和區域)

消費金額的公式為：

=SUMIF(INDIRECT(B3&"!b:b"),A3,INDIRECT(B3&"!h:h"))

其他的也相應改變區域跟條件即可。大概就這樣，你自己好好理解下。

lee：不打擾你了，我自己好好琢磨下。