今日目標：
理解表格設計的重要性
上週我們搞了一個《3天Excel集訓體驗營》的活動，3節Excel專題直播。
異常的火爆，總共有600多人參加活動。
- 專門針對職場新人挑選的，3個Excel實用專題。
- 重點是價格，只要1元，不為賺錢，就是交個朋友。
不是廣告，別划走！
3天下來，我最大的感受有兩點：
1- 這樣的活動，要多搞一些。
2- 職場新人，真的真的要學習一下如何設計表格。
真的，大部分的Excel問題，都是因為表格設計的不好，自己給自己找麻煩。
比如下面這位同學的問題。
她想要根據「宿舍房間」號碼的合併單元格，統計每個房間的人數，然後計算每人分攤金額。
本來一個挺簡單的計數問題，結果因為一個合併單元格，
變的
特別的複雜。
我從簡單的開始講，幫大家捋一下這個設計過程。
1- 簡單的計數
最簡單的計數，就是沒有任何的條件，簡單統計人員數量。
用一個簡單的COUNTA函式就可以了，公式如下：
=COUNTA（$B$2：$B$5）
COUNTA函式的作用，是統計非空單元格的數量。
2- 分組的計數
問題複雜一點的話，可以新增一個分組統計的條件。
即，統計每個房間的人數。
問題也不是特別的複雜，按照指定的條件統計個數，使用COUNTIF函式就可以實現。
=COUNTIF（$A$2：$A$24，A2）
COUNTIF函式有兩個引數：
- 引數1，要計數的資料區域，也就是這裡的$A$2：$A$24
- 引數2，計數的條件，公式裡的A2，意思是說，引數1中只有等於房間號101的才統計數量，後面以此類推。
公式中唯一的難點，就是新增行列鎖定，避免填充公式的時候，引數1的區域發生偏移。
3- 合併單元格的計數
新手最喜歡的就是合併單元格，把相同的內容合併在一起，分組可以
變的
更清晰。
但是，函式公式最討厭的就是合併單元格。因為合併單元格只會保留一個數值，其他的都刪除掉，給公式製造了很多麻煩。
完全相同的需求，因為合併單元格，公式的難度上了4個等級。
完整公式如下：
=COUNTA（B2：$B$24）-SUM（D3：$D$24）
難度1
公式中使用了「拉燈模式」的行列鎖定，只鎖定了結束位置，沒有鎖定開始位置。
B2：$B$24
難度2
計數計算中使用了求和公式SUM，搞不懂。
SUM（D3：$D$24）
作用是對下方計數結果進行求和。
難度3
條件計數用的是COUNTA函式，根本沒用條件判斷，很費解。
COUNTA（B2：$B$24）
作用是計算當前單元格下方的「入職人員」數量。
難度4
前面這幾步為什麼要這麼做，完全看不懂。
=COUNTA（B2：$B$24）-SUM（D3：$D$24）
用當前的數量，減去下面計數結果的總和，就是當前房間的人數。
難度5
因為有合併單元格的存在，導致分攤的除法計算錯誤。
4- 合併單元格的計數
為了避免合併單元格導致的「分攤」計算錯誤。
這位同學把「人數」列取消了合併，這樣問題難度又變大了。
- 因為要判斷每個單元格每行對應的房間號是多少。
- 如果房號相同才做計數，但是合併單元格只有第1個單元格有數值，其他都是空白，所以難度又增加了。
為此，我添加了兩個輔助列，「起始的行號」和「結束行號」，然後兩個相減。
「起始行號」對應的公式。
=IF（A2<>“”，ROW（），F1）
「結束行號」對應的公式。
=INDEX（$F$2：$F$25，MATCH（F2，$F$2：$F$25，1）+1）
具體的過程非常的複雜，我不打算講解了，會把一大波讀者趕跑的。
我想說的是，稍微設計一下表格的結構，問題解決起來會非常簡單。
5- 表格設計後
修改後的表格大概是下面的樣子。
1- 一個房間一行記錄
一個房間是一行記錄，房間的費用，人數、分攤，這些資訊都在一行中。
這樣公式值對當前這一行的資料計算就可以了。
2- 人員按行排列
「入職人員」名單橫向排列，和「房間」在同一行記錄中，
這樣可以很好的避免了合併單元格。
同時，人員的數量，直接使用簡單的COUNTA函式計算得出。
在右側更新姓名時，所有的公式自動計算，「人數」「分攤」也自動更新。
6- 總結
這只是為了滿足「人數」這個單一的需求，對錶格做了一點結構的修改。
考慮到後續可能有下面的需求，表格的設計還需要不斷的最佳化：
1- 按照人員查詢，如果有其他的費用（如獎金、專案獎、個稅等），方便以人員為路徑查詢和計算。
2- 按照日期查詢，表格中如果增加了入住日期維度，如何按照日期快速統計月費用總和？
需求永無止境，解決問題只靠越來越長的函式公式遠遠不夠，好的表格，一定是設計出來的。
上面是上一期《3天Excel集訓體驗營》的課程大綱，沒趕上的同學，如果我們再搞一期，1元限購，你願意交這個朋友嗎？
下一期《3天Excel集訓體驗營》你願意參加嗎？
- 願意，交你這個朋友
- 不願意，我Excel挺不錯的
7- 案例檔案
本課案例檔案，已經上傳知識星球，檔案ID：
《SK264-HR，分組統計人數，一個合併單元格毀了一整天》
點選「
閱讀原文
」檢視。
我是拉小登，一個會設計表格的Excel老師