excel函數公式應用：如何全自動統計所有家庭的人口數

2020-10-20 部落窩教育BLW

編按：哈嘍，大家好！如何快速統計家庭人口數呢？網上給出的大多公式不能統計最後一戶的人數，最後一戶需要手動填寫。今天我們將提供兩種方法，全自動統計所有家庭的人口數。第一個公式是從上到下統計，第二個公式是從下往上統計。使用的函數包括IFERROR、IF、MATCH、COUNTA、SUM函數等，趕緊來看看吧！學習更多技巧，請收藏關注部落窩教育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會對區域中有內容的單元格進行計數。

以上就是統計家庭人口數的第一個公式套路，這個公式完全是自上而下計數的邏輯，相信經過講解大家應該是可以理解的，但是第二個公式套路就完全是逆向思維了，是自下而上的計數邏輯，公式看上去更加簡短了，但是理解難度卻增加了。學習更多技巧，請收藏關注部落窩教育excel圖文教程。

第二個公式是這樣的：=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中的範圍是公式所在單元格下方的範圍。

小結：對比今天這兩個公式的套路，公式1算是一個常規思路，理解了相關函數的基本用法就能掌握，公式2則是思路上的徹底轉變，不知道這兩個公式你更喜歡哪個呢，歡迎留言分享你的心得。學習更多技巧，請收藏關注部落窩教育excel圖文教程。

＊＊＊＊部落窩教育-excel函數統計公式技巧＊＊＊＊

原創：老菜鳥/部落窩教育（未經同意，請勿轉載）

更多教程：部落窩教育

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

相關推薦：

合併單元格求和《雙十一到底要花多少錢？一張Excel表格，讓你看得明明白白！》

求和函數大匯總《求和，我是認真的(Excel函數教程）》

MATCH函數解析《MATCH：函數哲學家，找巨人做伴。新出道必學！》

COUNTIFS函數解析《同樣是countifs函數，為什麼同事卻使得比你好？原因在這裡！》

相關焦點

  • excel函數應用技巧:按區間統計個數,就用Frequency
    編按：價格帶統計與按成績統計優良中差的人數是一樣的，都是按區間統計個數。最簡單、最快速的辦法是用高級函數Frequency。學習更多技巧，請收藏關注部落窩教育excel圖文教程。接下來先看看FREQUENCY是如何解決這個問題的，再看看孰優孰劣。學習更多技巧，請收藏關注部落窩教育excel圖文教程。針對案例中需要統計的五個價格區間的商品個數，只需要一個公式：=FREQUENCY($C$2:$C$51,{15,50,80,100})就可以搞定。
  • excel函數公式應用:時間日期提取公式匯總,你用過哪些?
    如何快速在一組時間數據中分別提取出年月日、時分秒數據？如何快速計算某日期是年內第幾周、星期幾，以及日期之間間隔的天數、月數、年數、小時數、分鐘數？如何快速補全指定月份日期，合併日期和時間？今天老菜鳥針對上述在日常工作中經常會遇到的問題，總結了20個常用的關於日期和時間的公式，趕緊來看看吧！學習更多技巧，請收藏關注部落窩教育excel圖文教程。
  • excel函數應用技巧：按區間統計個數，就用Frequency
    編按：價格帶統計與按成績統計優良中差的人數是一樣的，都是按區間統計個數。按照領導的要求，需要劃分5個價格區間，並統計出每個區間包含的品規數，然後做商品的結構調整。今天，我們拋開具體的業務分析不談，只說統計這五個區間的商品個數。
  • 《excel函數公式大全》精選
    《excel函數公式大全》精選一、數字處理1、取絕對值函數公式：=ABS(數字)2、取整函數
  • excel函數公式:常用高頻公式應用總結(上)
    最近後臺的留言實在是太多了，由於時間關係，沒辦法一一給小夥伴們解答，所以這裡我們總結了小夥伴們問的最多的問題，整理出了10個職場人士最常用的excel公式，希望能幫大家排憂解難，趕緊來看看吧!今天總結了十個使用頻率很高的公式分享給大家，相信學會這十個公式，你也可以在職場上縱橫一番了。公式1：條件計數條件計數在Excel的應用中十分常見，例如統計人員名單中的女性人數，就是條件計數的典型代表。
  • EXCEL函數公式大全之利用YEAR函數COUNTIF函數統計員工入職年份
    EXCEL函數公式大全之利用YEAR函數與COUNTIF函數的組合統計各個年份員工入職的人數。excel函數與公式在工作中使用非常的頻繁，會不會使用公式直接決定了我們的工作效率，今天我們來學習一下提高我們工作效率的函數YEAR函數與COUNTIF函數的組合。
  • Excel函數公式:統計函數Count、Counta、Countblank、Countifs應用技巧
    統計函數在我們的工作中引用的非常廣泛，如果能夠熟練的掌握和加以應用，則會起到事半功倍的效果。今天我們來介紹Count、Counta、Countblank、Countifs四個統計函數。解讀：1、由於工作表較小，我們可以看出只有7行數據，但是公式=COUNT(C3:C9)的計算結果為6，為什麼？2、函數COUNT的作用是統計數字的個數，我們仔細觀察容易發現銷量下的第一個單元格中的值類型並不是「數據」類型，而是文本類型（左上角的綠三角標誌），所以就得到樂6的結果。
  • 【Excel函數教程】SUMPRODUCT函數的應用
    第二，函數 SUMPRODUCT 將非數值型的數組元素作為 0 處理。　　第三，如果是一個數組，那麼就是對這個數組的求和。　　我們先通過一個簡單的工作表數據來認識SUMPRODUCT函數。　　此題有兩個條件：第一，大於15天，用E4:E33>15表示。第二，男性，用C4:C33="男"表示。　　套用SUMPRODUCT((條件1)*(條件2)*(條件3)*...)，得到公式：=SUMPRODUCT((E4:E33>15)*(C4:C33="男"))，結果為8人。
  • excel函數公式應用：時間日期提取公式匯總，你用過哪些？
    如何快速在一組時間數據中分別提取出年月日、時分秒數據？如何快速計算某日期是年內第幾周、星期幾，以及日期之間間隔的天數、月數、年數、小時數、分鐘數？如何快速補全指定月份日期，合併日期和時間？今天老菜鳥針對上述在日常工作中經常會遇到的問題，總結了20個常用的關於日期和時間的公式，趕緊來看看吧！學習更多技巧，請收藏關注。
  • 職場速遞-Excel函數會計應用3:隔列求和函數公式應用
    隔列求和函數公式應用隔列求和函數公式應用是會計甚至材料等部門經常用到的函數，今天給大家演示一下，希望對你有所幫助。隔列求和函數公式應用公式F4:=SUMIF($A$3:$E$3,F$3,A4:E4)如果第三行的標題，那麼我們只能用稍複雜的函數公式。
  • excel函數公式：常用高頻公式應用總結（上）
    最近後臺的留言實在是太多了，由於時間關係，沒辦法一一給小夥伴們解答，所以這裡我們總結了小夥伴們問的最多的問題，整理出了10個職場人士最常用的excel公式，希望能幫大家排憂解難，趕緊來看看吧! 今天總結了十個使用頻率很高的公式分享給大家，相信學會這十個公式，你也可以在職場上縱橫一番了。公式1：條件計數條件計數在Excel的應用中十分常見，例如統計人員名單中的女性人數，就是條件計數的典型代表。
  • EXCEL函數公式大全之利用SUM函數FREQUENCY函數統計不同區間數據
    EXCEL函數公式大全之利用FREQUENCY函數數組公式統計不同區間數據個數。EXCEL函數與公式在工作中使用非常的頻繁，會不會使用公式直接決定了我們的工作效率，今天我們來學習一下提高我們工作效率的函數FREQUENCY函數和數組公式。
  • excel函數應用：組合函數管理日常花銷
    現在需要針對不同消費筆數計算每次的平均消費額。主要難點在於如何去除指定個數的最高和最低消費。這個問題解決後我們就可以通過IF函數進行判斷返回關鍵數值X。下面我們將拆分所有判斷條件，依次跟大家分享一下解決過程。1.消費次數小於4的情況消費筆數小於4的情況下則計算這幾次消費額的平均金額，這個條件還是比較簡單的。
  • Excel|SUMIF函數應用大全
    清晨，與您相約SUMIF條件求和函數是excel最常用的函數之一。
  • 你早該這麼學Excel函數,Excel公式教程,第二課《公式與函數》
    excel公式與函數摘要：①簡單公式計算；②表達式與地址引用；③常用函數及其應用；④誤操作提示；⑤其它函數>教學重點：簡單公式計算、常用函數及其應用3) 頻數函數：FREQUENCY(數據區,分段點區)功能：分段統計指定範圍內數據出現的個數。
  • excel函數公式技巧:分級統計的七個公式,選擇哪個?
    在日常工作中，相信大家都遇到過這樣一種情況，要求按照等級統計得分。這個問題說難倒也不難，但如果要小夥伴列出3種以上的解決方法，估計不少人會蒙圈。思路越多，解決問題的方法就越多，對函數的掌控程度也會越好。今天作者E圖表述將為大家分享7種解決方法，趕緊來看看吧！學習更多技巧，請收藏部落窩教育excel圖文教程。
  • excel函數公式技巧：分級統計的七個公式，選擇哪個？
    在日常工作中，相信大家都遇到過這樣一種情況，要求按照等級統計得分。這個問題說難倒也不難，但如果要小夥伴列出3種以上的解決方法，估計不少人會蒙圈。思路越多，解決問題的方法就越多，對函數的掌控程度也會越好。今天作者E圖表述將為大家分享7種解決方法，趕緊來看看吧！學習更多技巧，請收藏部落窩教育excel圖文教程。
  • excel函數案例:如何用COUNTIFS實現區間統計
    excel區間統計在工作中經常遇到，比如：公司員工中，70後的多少人、80後的多少人、90後的多少人；員工業績裡，1萬~3萬的多少人、3萬~5萬的多少人、5萬~10的多少人現在，我們來講下用函數的話，應該如何實現。在圖中案例表，已知銷售員及其銷售金額，現在需要根據銷售金額的區間統計人數。
  • Excel函數公式中,如何計算「個數」?
    如圖所示，如何計算女生的人數？在單元格中輸入公式=COUNTIF(B2:B8,"女")COUNTIF函數說明：函數定義：統計滿足某個條件的單元格個數。語法格式：=COUNTIF（條件區域，條件）除了使用COUNTIF函數之外，還可以用SUMPRODUCT函數，在單元格中輸入公式=SUMPRODUCT((B2:B8="女")*1)SUMPRODUCT函數說明：SUMPRODUCT
  • Excel中統計個數的函數有哪些?如何使用
    考慮到內容比較完整系統，還是放到公眾號首發1、COUNT先來看一下英文單詞COUNT美[kaʊnt]英[kaʊnt]從單詞的含義知道，它是用於計數的，但在Excel中，此函數只能用於統計數字的個數。語法格式：=COUNT（參數1，參數2)擴展閱讀：如果要統計所有內容(數字、文本）的個數，要用到COUNTA函數（