|
全套Excel視頻教程，掃碼觀看 哈嘍，大家好！如何快速統(tǒng)計家庭人口數(shù)呢？網(wǎng)上給出的大多公式不能統(tǒng)計最后一戶的人數(shù)，最后一戶需要手動填寫。今天我們將提供兩種方法，全自動統(tǒng)計所有家庭的人口數(shù)。第一個公式是從上到下統(tǒng)計，第二個公式是從下往上統(tǒng)計。使用的函數(shù)包括IFERROR、IF、MATCH、COUNTA、SUM函數(shù)等，趕緊來看看吧！對于戶籍統(tǒng)計工作者來說，在一份人口清單中統(tǒng)計每戶的人口數(shù)是家常便飯了，近日就有群友提出了這方面的一個問題，詢問有無公式能夠得到家庭人口數(shù)，實在不想一個一個手動填寫了，模擬數(shù)據(jù)源如圖所示： 人員姓名系模擬數(shù)據(jù)，如有雷同純屬巧合。要求是在每家戶主所在行填寫對應的家庭人口數(shù)，每個小區(qū)都有幾百戶需要統(tǒng)計，純靠手工填寫想想都嚇人，今天就分享兩個可以統(tǒng)計家庭人口數(shù)的公式套路，想一起學的趕緊下載課件準備開始吧。公式1：=IFERROR(IF(C2="戶主",MATCH(C2,C3:C9,),""),COUNTA(C3:C9)+1)這個公式看起來有點長，其實核心只是MATCH(C2,C3:C9,)這部分，因此先從這個地方開始解釋。 MATCH函數(shù)的基本功能是得到一個數(shù)據(jù)在一組數(shù)據(jù)中出現(xiàn)的位置，例如C2中的內(nèi)容（“戶主”）在C3:C9這個區(qū)域中出現(xiàn)的位置是3。注意這里區(qū)域的選擇，是從戶主的下一行開始的，得到的實際上是第二個戶主出現(xiàn)的位置，但是這個數(shù)字正好就是所統(tǒng)計的這一戶的家庭人口數(shù)，想明白這一點對理解后面的原理很重要。C3:C9包含了7個單元格，如果存在超過7口人的家庭，這個范圍就要擴大，否則會出現(xiàn)錯誤，至于具體用什么區(qū)域統(tǒng)計，明白這一點就可以自己調(diào)整了，或者直接用C3:C99也行。明白了MATCH這部分之后，增加了IF的IF(C2="戶主",MATCH(C2,C3:C9,),"")也就不難理解了。僅當C列為戶主的時候，才顯示MATCH的結(jié)果，其他都顯示為空白。此時的公式看似已經(jīng)實現(xiàn)了需要的結(jié)果，但是當我們把表格拉到最下面的時候，就發(fā)現(xiàn)有問題。這是因為在最后一個戶主之后，MATCH無法繼續(xù)找到戶主就得到了錯誤值，解決方法有兩個。第一個方法是在最下面寫一個戶主進去，這樣不用改變公式也能得到正確結(jié)果。第二個方法就是修改公式，利用IFERROR函數(shù)單獨計算最后一戶的人口數(shù)，公式為：=IFERROR(IF(C31="戶主",MATCH(C31,C32:C38,),""),COUNTA(C32:C38)+1)最后一戶的人口數(shù)就是單元格區(qū)域中數(shù)據(jù)的個數(shù)加1，COUNTA會對區(qū)域中有內(nèi)容的單元格進行計數(shù)。以上就是統(tǒng)計家庭人口數(shù)的第一個公式套路，這個公式完全是自上而下計數(shù)的邏輯，相信經(jīng)過講解大家應該是可以理解的，但是第二個公式套路就完全是逆向思維了，是自下而上的計數(shù)邏輯，公式看上去更加簡短了，但是理解難度卻增加了。第二個公式是這樣的：=IF(C2="戶主",COUNTA(C2:C35)-SUM(D3:D36),"")這個公式的特殊之處在于D2單元格的公式用到了同一列后面的單元格數(shù)據(jù)。而且用之前分析公式的方法似乎都有點難以解釋，比如單獨看COUNTA(C2:C35)，結(jié)果就是統(tǒng)計表中人數(shù)的遞減，一共34人，每往下一行人數(shù)減少1。再看=COUNTA(C2:C35)-SUM(D3:D36)這部分的結(jié)果，又全都變成了1。但是再看加了IF的效果，公式=IF(C2="戶主",COUNTA(C2:C35)-SUM(D3:D36),0)的結(jié)果又完全變了。到底為什么會這樣，為了便于大家理解，我們只用三戶人家來做說明。第三戶人數(shù)統(tǒng)計結(jié)果為7，其實就是這樣得到的，COUNTA函數(shù)統(tǒng)計了后面的所有人數(shù)，由于后面沒有戶主了，所以IF得到的都是0，這一點從最后一行來往上看，因此這個公式的思路是自下而上的。SUM得到的結(jié)果也就是0，進而COUNTA-SUM就變成了這一戶的總?cè)藬?shù)。再看統(tǒng)計第二戶人數(shù)的時候，隨著公式下拉，公式中的區(qū)域發(fā)生變化，COUNTA統(tǒng)計的是除第一戶以外的總?cè)藬?shù)，應該是11人，由于非戶主所對應的都是0，所以SUM得到的是第三戶對應的人數(shù)7，這樣第二戶的人數(shù)就是11-7，結(jié)果是4人。第一戶的3人也是這樣倒推出來的。這個公式難于理解的正是這種倒推計算的思路，如果一時間還無法明白的話，只要了解這個公式套路中的要點也可以隨時套用，COUNTA中的范圍是實際數(shù)據(jù)范圍，而SUM中的范圍是公式所在單元格下方的范圍。對比今天這兩個公式的套路，公式1算是一個常規(guī)思路，理解了相關函數(shù)的基本用法就能掌握，公式2則是思路上的徹底轉(zhuǎn)變，不知道這兩個公式你更喜歡哪個呢，歡迎留言分享你的心得。 讓工作提速百倍的「Excel極速貫通班」
|
