Excel教程:這2個Excel公式,搞定同事半天的工作,太好用了!

Excel學得好，下班回家早！趕緊跟蜜蜜來get超好用的2個Excel公式吧~

對於戶籍統計工作者來說，在一份人口清單中統計每戶的人口數是家常便飯了。

近日就有蜜粉提出了這方面的問題，詢問有無公式能夠得到家庭人口數，實在不想一個一個手動填寫了，模擬數據源如圖所示：

 

人員姓名系模擬數據，如有雷同純屬巧合。要求是在每家戶主所在行填寫對應的家庭人口數，每個小區都有幾百戶需要統計，純靠手工填寫想想都嚇人。

今天就分享兩個可以統計家庭人口數的公式套路，想一起學的趕緊準備開始吧。

公式1：=IFERROR(IF(C2="戶主",MATCH(C2,C3:C9,),""),COUNTA(C3:C9)+1)

這個公式看起來有點長，其實核心只是MATCH(C2,C3:C9,)這部分，因此先從這個地方開始解釋。

 MATCH函數的基本功能是得到一個數據在一組數據中出現的位置，例如C2中的內容（「戶主」）在C3:C9這個區域中出現的位置是3。

 

 

注意這裡區域的選擇，是從戶主的下一行開始的，得到的實際上是第二個戶主出現的位置，但是這個數字正好就是所統計的這一戶的家庭人口數，想明白這一點對理解後面的原理很重要。

 

C3:C9包含了7個單元格，如果存在超過7口人的家庭，這個範圍就要擴大，否則會出現錯誤，至於具體用什麼區域統計，明白這一點就可以自己調整了，或者直接用C3:C99也行。

 

明白了MATCH這部分之後，增加了IF的IF(C2="戶主",MATCH(C2,C3:C9,),"")也就不難理解了。

 

 

僅當C列為戶主的時候，才顯示MATCH的結果，其他都顯示為空白。

 

此時的公式看似已經實現了需要的結果，但是當我們把表格拉到最下面的時候，就發現有問題。

 

 

這是因為在最後一個戶主之後，MATCH無法繼續找到戶主就得到了錯誤值，解決方法有兩個。

第一個方法是在最下面寫一個戶主進去，這樣不用改變公式也能得到正確結果。

 

 

第二個方法就是修改公式，利用IFERROR函數單獨計算最後一戶的人口數，公式為：=IFERROR(IF(C31="戶主",MATCH(C31,C32:C38,),""),COUNTA(C32:C38)+1)

 

 

最後一戶的人口數就是單元格區域中數據的個數加1，COUNTA會對區域中有內容的單元格進行計數。

 

以上就是統計家庭人口數的第一個公式套路，這個公式完全是自上而下計數的邏輯，相信經過講解大家應該是可以理解的，但是第二個公式套路就完全是逆向思維了，是自下而上的計數邏輯，公式看上去更加簡短了，但是理解難度卻增加了。

 

第二個公式是這樣的：=IF(C2="戶主",COUNTA(C2:C35)-SUM(D3:D36),"")

 

這個公式的特殊之處在於D2單元格的公式用到了同一列後面的單元格數據。

 

 

而且用之前分析公式的方法似乎都有點難以解釋，比如單獨看COUNTA(C2:C35)，結果就是統計表中人數的遞減，一共34人，每往下一行人數減少1。

 

 

再看=COUNTA(C2:C35)-SUM(D3:D36)這部分的結果，又全都變成了1。

 

 

但是再看加了IF的效果，公式=IF(C2="戶主",COUNTA(C2:C35)-SUM(D3:D36),0)的結果又完全變了。

 

 

到底為什麼會這樣，為了便於大家理解，我們只用三戶人家來做說明。

 

第三戶人數統計結果為7，其實就是這樣得到的，COUNTA函數統計了後面的所有人數，由於後面沒有戶主了，所以IF得到的都是0，這一點從最後一行來往上看，因此這個公式的思路是自下而上的。SUM得到的結果也就是0，進而COUNTA-SUM就變成了這一戶的總人數。

 

 

再看統計第二戶人數的時候，隨著公式下拉，公式中的區域發生變化，COUNTA統計的是除第一戶以外的總人數，應該是11人。

由於非戶主所對應的都是0，所以SUM得到的是第三戶對應的人數7，這樣第二戶的人數就是11-7，結果是4人。第一戶的3人也是這樣倒推出來的。

 

這個公式難於理解的正是這種倒推計算的思路，如果一時間還無法明白的話，只要了解這個公式套路中的要點也可以隨時套用，COUNTA中的範圍是實際數據範圍，而SUM中的範圍是公式所在單元格下方的範圍。

來源：Excel教程