盧子早期基本上都是在各大Excel論壇、Excel交流群免費(fèi)幫人解答疑難。后來(lái)聽(tīng)從一個(gè)朋友的建議，在淘寶上開(kāi)了一家?guī)腿私獯鹨呻y的店鋪。在這期間，我發(fā)現(xiàn)一個(gè)現(xiàn)象，付費(fèi)的人普遍比以前那些人更有禮貌。就因?yàn)檫@個(gè)原因，我慢慢喜歡上在淘寶上幫人解答問(wèn)題，做自己喜歡的事，還能掙點(diǎn)零花錢，何樂(lè)而不為。這里挑選一些我在淘寶幫人解答的問(wèn)題，進(jìn)行講解。
現(xiàn)在有4個(gè)明細(xì)表，表格的格式完全一樣，就只是行數(shù)不一樣而已，分別是成都、深圳、?？?、武漢，在最后的匯總表對(duì)這4個(gè)表進(jìn)行統(tǒng)計(jì)。原先是先用公式在明細(xì)表統(tǒng)計(jì)好，再引用過(guò)去。
統(tǒng)計(jì)C列的性別，男女人數(shù)分別多少
=COUNTIF(C3:C40,"男")
=COUNTIF(C3:C40,"女")
統(tǒng)計(jì)F列客戶狀態(tài)，新客戶跟舊客戶人數(shù)分別為多少
=COUNTIF(F3:F40,"新客戶")
=COUNTIF(F3:F40,"舊客戶")
統(tǒng)計(jì)各種金額：消費(fèi)金額、應(yīng)收、扣款項(xiàng)目、實(shí)收
=SUM(G3:G40)
=SUM(J3:J40)
=SUM(K3:K40)
=SUM(L3:L40)
4個(gè)明細(xì)表設(shè)置的公式都一樣，只是區(qū)域不一樣而已。
分別統(tǒng)計(jì)好后，就在匯總表依次引用，如C3的公式就是：
=成都!B41
這樣做會(huì)出現(xiàn)2個(gè)問(wèn)題：
明細(xì)表的區(qū)域限制死了，如果后續(xù)有數(shù)據(jù)添加，不能自動(dòng)統(tǒng)計(jì)；
這種統(tǒng)計(jì)只是針對(duì)1個(gè)月份，如果有多個(gè)月份也不能統(tǒng)計(jì)。
看到這里，盧子大概就有了一個(gè)思路，就問(wèn)lee：你每個(gè)明細(xì)表下面的統(tǒng)計(jì)能否刪除掉？
lee：這個(gè)是為了方便統(tǒng)計(jì)用的，如果有更好的方法統(tǒng)計(jì)可以刪除。
得到了一個(gè)肯定的說(shuō)法后，盧子就對(duì)這個(gè)表格進(jìn)行了小小的變動(dòng)。
將明細(xì)表的各種統(tǒng)計(jì)刪除。
在日期后面插入一列，獲取月份。
=MONTH(A3)&"月"
用同樣的方法，將其他明細(xì)表也這樣操作。
為了方便測(cè)試，盧子在成都這個(gè)表添加了幾行2月份的數(shù)據(jù)。
同時(shí)也在匯總表添加2月份的區(qū)域，并將原來(lái)的公式刪除。
男性、女性、新客戶、舊客戶人數(shù)所使用的公式一樣，只是區(qū)域跟條件略做更改而已。
=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)
消費(fèi)金額、應(yīng)收、扣款項(xiàng)目所使用的公式一樣，只是區(qū)域跟條件略做更改而已。
=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"))
實(shí)收：
=H3-K3
尾款：
=H3-I3-K3
本來(lái)做完后盧子就只是把最終的附件發(fā)給lee，但lee實(shí)在水平有限，典型的菜鳥(niǎo)?？戳艘院笠活^霧水，啥都不懂，一定要盧子給她解釋。為了解釋這2條公式，盧子足足花了一個(gè)小時(shí)。
lee：我的本和筆都準(zhǔn)備好了。
盧子：
=COUNTIF(C3:C40,"男")
=COUNTIF(區(qū)域,條件)
這個(gè)語(yǔ)法能否懂嗎？
因?yàn)槟阍瓉?lái)區(qū)域是固定的C3:C40，現(xiàn)在你區(qū)域不固定，也就是區(qū)域要大一點(diǎn)，那你干脆就引用整列，寫(xiě)C:C。
就是統(tǒng)計(jì)C列性別為男的次數(shù)，像其他的統(tǒng)計(jì)新舊客戶人數(shù)的也一樣的道理。
現(xiàn)在進(jìn)入核心部分，因?yàn)橛泻芏啾?，不可能每個(gè)表單獨(dú)設(shè)置公式。
lee：嗯。
盧子：你當(dāng)然希望同一列下拉就全部表格匯總出來(lái)。
lee：對(duì)的。
盧子：
正常的話，成都就用=COUNTIF(成都!C:C,"男")
下面的深圳就改成=COUNTIF(深圳!C:C,"男")
這樣好麻煩。
lee：是，要是有100張那要改100次。
盧子：
你看，這樣下拉就全部出來(lái)了
=B3&"!c:c"
所有表格的區(qū)域都出來(lái)，對(duì)吧
lee：慢點(diǎn)，出來(lái)區(qū)域和后面匯總分表數(shù)據(jù)啥關(guān)系？
盧子：要制作區(qū)域出來(lái)，這個(gè)能理解吧。如統(tǒng)計(jì)成都就用=COUNTIF(成都!C:C,"男")。
lee：你的意思是不是，匯總表區(qū)域下面城市就用，=B3&"!c:c"這個(gè)公式來(lái)設(shè)置。
lee：這個(gè)公式翻譯成漢語(yǔ)是啥意義
盧子：剛剛那個(gè)b3&"!c:c"就是把內(nèi)容合并起來(lái)而已，就是在區(qū)域后面增加!c:c，成都!c:c這樣。
lee：喔，懂了。
盧子：因?yàn)槲覀兪峭ㄟ^(guò)&連接起來(lái)的，不是直接用區(qū)域生成的，所以需要增加一個(gè)函數(shù)INDIRECT，才能進(jìn)行計(jì)算
=COUNTIF(INDIRECT(B3&"!c:c"),"男")
也就是這條公式的由來(lái)。
lee：INDIRECT這個(gè)函數(shù)是啥意思？
盧子：因?yàn)槟悴皇侵苯右茫怯霉缴?，所以在那里增加這個(gè)函數(shù)。
lee：直接引用和公式生成哪個(gè)方便點(diǎn)??？
盧子：剛剛不是解釋了嗎，如果有100個(gè)表，你就得直接引用100次，你不搞死人。
lee：喔喔喔喔，明白了。
大神，前面分表是一個(gè)月的，總表匯總一個(gè)月，那如果有多個(gè)月需要怎么做？
盧子：那就用COUNTIFS函數(shù)，COUNTIF函數(shù)是單條件計(jì)數(shù)，COUNTIFS函數(shù)是多條件計(jì)數(shù)。
COUNTIFS函數(shù)語(yǔ)法：
COUNTIFS(條件區(qū)域1,條件1, 條件區(qū)域2,條件2……)
剛剛我在你的表格增加了一列計(jì)算月份，這樣有了前面COUNTIF函數(shù)的基礎(chǔ)，設(shè)置公式就變得很簡(jiǎn)單。兩個(gè)函數(shù)的語(yǔ)法基本一樣，只是COUNTIFS可以多條件計(jì)數(shù)而已。
=COUNTIFS(INDIRECT(B3&"!d:d"),"男",INDIRECT(B3&"!b:b"),A3)
男女人數(shù)跟新老客戶用的公式都一樣，其他只要更改區(qū)域就可以。
前面都是計(jì)數(shù)，后面的是求和。單條件求和用SUMIF函數(shù)，多條件用SUMIFS函數(shù)?，F(xiàn)在都是單條件求和，那就用SUMIF函數(shù)，語(yǔ)法如下：
SUMIF(條件區(qū)域,條件,求和區(qū)域)
消費(fèi)金額的公式為：
=SUMIF(INDIRECT(B3&"!b:b"),A3,INDIRECT(B3&"!h:h"))
其他的也相應(yīng)改變區(qū)域跟條件即可。大概就這樣，你自己好好理解下。
lee：不打擾你了，我自己好好琢磨下。
聯(lián)系客服
