【Excel 教學 — 函數】如何思考公式邏輯：以countif + offset 應用為例
剛好工作上遇到了一個資料加總的問題，可以用 countif 和 offset 兩個函數的組合來解。對 offset 還不太熟悉的朋友，可以先參考之前有寫過的 《進階應用：offset》；本篇會著重在組合函數的邏輯架構思考，不會在函數的使用上有太深的著墨。
Excel 函數官方說明：
OFFSET 傳回根據所指定列數及欄數之儲存格或儲存格範圍之範圍的參照。
COUNTIF 用來計算符合準則的儲存格數目。1確認問題情境》思考公式要怎麼寫之前，一定要先搞清楚問題是什麼，才能夠藥到病除。
這次問題是這樣，要加總每個新產品剛上架前 12 個月的營收。稍微整理一下重點：加總、每個產品、前 12 個月，寫公式的時候就要考慮到這幾個要素。
一般來說，加總用函數 sum 、sumif 或 sumifs 就可以處理。但這次要處理的資料期間長達五年，期間有近百種新產品上架，所以沒辦法以特定的欄位來加總，必須每個產品分別判定要加總哪 12 個數字。
題外話，很常會遇到完全不知道該用什麼函數的狀況。這時候可以試著用關鍵字搜尋，例如：Excel 加總、Excel 計算有數字…等，其實網路上的資源算是滿完整而且豐富的。或是從 Excel 內建的官方文件找適合的函數也是一個方法，這部分好像滿多人很陌生？這部分在我的另一篇文章「4個一定要知道的 Excel 學習工具」有稍微提過，也歡迎你留言告訴我其他你想知道的部分 :)
如果看到這裡，你已經對要選擇什麼函數、公式怎麼寫有點想法，可以直接跳到第三段看我怎麼解這題。
2 釐清邏輯》先不要急著開始寫公式，在這之前，我會先想想判斷的邏輯是什麼，這有點抽象，邏輯思考的過程會因人而異，以下會以我自己的情況做說明。
其實這題並不算太難，只需要加總前 12 個數字，這部分用函數 sum 就可以解決。但這次的狀況是，每個產品開始時間不一樣，這才是比較困難的部分，所以重點會在怎麼判斷加總的起始點。
- 思考點 1 ：有 N 個儲存格為數字，取前 12 個
用 count 可以計算有幾個儲存格有數字，但好像沒辦法解決從哪裡開始計算的關鍵問題；假設 count 算出來是 16，扣除掉最後 4 個數字就會是答案，但要怎麼寫公式扣最後 4 個? (卡關姑且先放棄這條路)
公式：count(B2:S2)- 思考點 2 ：那換個方向想，去判斷有幾個儲存格沒有數字，是否可行？
用 countif 可以設定條件，來計算幾個儲存格是空白，假設計算出來是 N，那 N+1 就是加總的起始點！(登登登! 腦中出現通關音效 XD)
公式：=COUNTIF(B2:S2,””)，""為判別儲存格為空白的條件
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
感謝您的閱讀，如果覺得這篇文章對你有幫助，請幫我拍手讓我知道!
點擊 Medium 的「拍手(Claps)」，最多可拍50下。
喜歡我的文章記得要按「追蹤(Follow)」，有任何的問題與想法也歡迎告訴我喔 😃► 關於我 ◀
我是 Misa C，你也可以叫我米莎，是熱衷於「投資人生」的財務分析師。分享學習、理財、個人成長、生活經營，
陪伴想要開始投資人生的你，一步一步描繪屬於自己的人生投資藍圖。Facebook粉絲團：人生投資分析師 - Misa C► 更多文章 ◀
點 文章列表 看其他分享
你可能也會有興趣…
