本篇會著重在組合函數的邏輯架構思考，不會在函數的使用上有太深的著墨。
這次，在工作上遇到了一個資料加總的問題，這篇會示範用 countif 和 offset 這兩個函數的組合來處理。對 offset 還不太熟悉的朋友，可以先參考之前有寫過的 《進階應用：offset》。
補充：Excel 函數官方說明
OFFSET 傳回根據所指定列數及欄數之儲存格或儲存格範圍之範圍的參照。
COUNTIF 用來計算符合準則的儲存格數目。
1. 確認問題情境
在思考公式要怎麼寫之前，第一步就是先弄清楚「問題是什麼」，寫出來的公式才能夠藥到病除。
這次要處理的資料如上表，首先說明一下這次要解決的問題。問題沒有很複雜，是要加總每個新產品剛上架前 12 個月的營收。
在開始思考函數和公式之前，我們先稍微整理一下問題的重點：
加總、每個產品、前 12 個月
所以在寫公式的時候，就要考慮到這幾個要素。
一般來說，加總可以用函數 sum 、sumif 或 sumifs 就處理。但這次要處理的資料期間長達五年，整個期間內有近百種新產品上市，每個產品的第一個月都不同，所以沒辦法以固定的欄位來加總，必須每個產品逐一判別要加總哪 12 個數字。
如果看到這裡，你已經對要選擇什麼函數、公式怎麼寫有點想法，可以直接跳到第 3 段看我怎麼解這題。
題外話
一開始在寫公式的時候，發生完全不知道該用什麼函數的狀況是很正常的。
這時候可以試著用關鍵字搜尋，例如：Excel 加總、Excel 計算有數字…等，其實網路上的資源算是滿完整而且豐富的。或者，也可以從 Excel 內建的官方文件找適合的函數，也是一個我很推薦也很常使用的方法。
如果你不熟悉官方說明文件，可以參考我的另一篇文章「4個一定要知道的 Excel 學習工具」。
2. 釐清邏輯
先不要急著開始寫公式，在這之前，我會先想想最重要的判斷邏輯是什麼。這有點抽象，邏輯思考的過程會因人而異，以下會以我自己的情況做說明。
其實這題並不算太難，只需要加總前 12 個數字，這部分用函數 sum 就可以解決。但有個變動因素會讓邏輯變得複雜，因為每個產品開始時間不一樣，這才是比較困難的部分，所以重點會在怎麼判斷加總的起始點。
- 思考點 1 ：有 N 個儲存格為數字，取前 12 個
函數 count 可以計算有幾個儲存格有數字，但好像沒辦法解決從哪裡開始計算的關鍵問題，還是只能計算全部期間，包含 B 欄到 S 欄；假設 count 算出來是 16，扣除掉最後 4 個數字就會是答案，但要怎麼寫公式扣最後 4 個? (卡關姑且先放棄這條路)
公式：count(B2:S2)
- 思考點 2 ：那換個方向想，去判斷有幾個儲存格沒有數字，是否可行？
改成用函數 countif 來計算，就可以設定條件，來計算幾個儲存格是空白，假設計算出來是 N，那 N+1 就是加總的起始點！(登登登! 腦中出現通關音效 XD)
公式：=COUNTIF(B2:S2,””)，””為判別儲存格為空白的條件
以思考 2 的邏輯來說，產品 B 前 2 個月為空白，所以是從第 3 個月開始加總。
3. 公式試算
開始進入最關鍵部分，確認好判斷邏輯之後，就是要實際寫公式，並驗證能不能得到正確的答案了。
如同前一段 思考點2 所說，我們先用 countif 來判斷有幾個儲存格為空白，結果顯示在 T 欄 (右邊紅底部分)，到這邊看起來都如預想的，那就著手繼續寫加總的公式。
儲存格T2 公式：=COUNTIF(B2:S2,””)
B2:S2判斷範圍要隨著列改變，””為判別儲存格為空白的條件
接著要搭配 sum 和 offset 來做運算，這部分用到 offset 比較複雜。如果不熟悉 offset 用法的話，請務必要看《進階應用：offset》這篇。
U 欄是計算前 12 個月的起始位置，V 欄則是結束的位置，公式就用 T欄+1和+12即可。W 欄公式則會用前面算出來的起始和結束位置來做運算。
儲存格 W2 公式：=SUM(OFFSET(A2,0,U2):OFFSET(A2,0,V2))
延伸閱讀推薦：訓練邏輯思維、快速解決問題的職人必備套組《解決問題的商業框架圖鑑》+《把問題化繁為簡的思考架構圖鑑》
4. 日後談
實際遇到這個問題的時候，受限於時間，只能以當下想到的方式來解。其實還有很多不同的思考方法，是我當初沒有想到的，在寫文章的時候突然又有了一些靈感。
實際在寫公式的時候，有一些例外和特殊情況其實都要考慮進去。
作為範例，我把數據簡化為產品都至少有 12 個月的數字；但如果產品只賣了 3 個月，那公式就要做調整，否則 W 欄的加總公式會超出資料範圍，反而會加總到後面公式運算的結果。
這個問題，我應該會在 V 欄結束位置的判斷中，多加一個 if 來排除結束位置大於 18 的情況。我相信這題也是有很多種解法，大家也可以試著想看看。
另外，就是一開始卡關的思考點 1，我在寫這篇文章的時候才想到，其實也是有換個角度的解法。
範例資料最多就是 18 個月，如果用 count 計算出有數字的儲存格是 6，那用 18 – 6 = 12，也可以算出是從第 12 格為起始點。實際在處理的時候，數據的期間太長(都是數據的錯啦!)，讓我完全忘記還可以用這個算法 XD
所以說，同樣的問題可以有很多種解法。
藉由這樣的思考過程來訓練寫公式的邏輯，是無法跳過的過程，雖然不同解法會有(電腦)計算效率的差別，但我們首要任務是解決問題，有多餘的時間再來思考可以怎麼做的更好。
如果需要 Excel 教學、Excel檔案優化、Excel 客製化諮詢等服務，歡迎直接聯絡我 misac@misachen.com
