HR，你會用SUMPRODUCT自動計算補貼嗎？
昨天，小編的一個朋友請我幫他優化一個公式，他出現的問題，很多HR也經常會出現，所以總結一下，分享給大家。
一、原文截圖
表格意圖很明瞭，即根據"取證日期"自動填寫"年限"，再結合"職稱級別"自動計算"補貼金額"。小編覺得這公式過於複雜，於是試著幫助他優化一下。
二、優化
1、優化5（重點）
倒過來說，我們先說重點、核心公式
E9單元格公式=SUMPRODUCT(($A$4:$A$6=D9)*($B$3:$D$3=C9),$B$4:$D$6)
要訣：一個橫向條件、一個縱向條件，無論是查找、求和、還是計算個數，用SUMPRODUCT函數最為高效。
當然也是實現：N個橫向條件、N個縱向條件的查找、求和、計算個數。
小編稱為十字交叉查找，大量運用在小編的《員工花名冊》中（見小編相關文章）
含義：SUMPRODUCT（（條件1區域=條件1）*（條件2區域=條件3）*（條件N區域=條件B），目標所在區域）
2、優化4（次重點）
這個優化的目的，是實現數據輸入內容的自動化、標準化。即：用下拉菜單來連接數據的輸入（A4至A6單元格）、輸出（D9至D11），實現A4至A6單元格發生變化後，D9至D11能夠實現間接變化，且內容完全一致，避免D9至D11單元格輸入不規範導致的錯誤可能性（如前後多了空格，少輸入漢字等），進一步確保公式計算結果的準確性。
3、優化3（思維變通）
這樣的設計有兩個目標：
第一：引入輔助列。當公式過於繁雜時，必將導致後期修改、完善、糾錯的工作量也過於繁雜，甚至讓我們灰心喪氣。
"類別"所在列、職稱等級所在行都是引入了輔助列、輔助行的概念。它們本質上很類似數學上的中間步驟、語文上的銜接語句。
第二：數據自動傳遞。當我們將B3至D3單元格（輸入）內容更換為甲、乙、丙時，下面的C9至C11單元格（輸出）內容會全自動更新，無需人工干預。
4、優化2（視覺標準化）
小編一直強調、運用數據的標準，視覺標準化就是其中之一。所以日期型格式，要不用0000-00-00，要不就用0000/00/00，整齊劃一，即2012-3-3表現為2012-03-03格式，或2012/03/03格式。
數據，不但要給自己用，還要給別人看。儘量美觀一點點。
5、優化1（邏輯思維）
人腦的抽象思維遠遠高於電腦的邏輯思維，反之電腦的邏輯思維高於人腦，，所以，要清晰告訴電腦邏輯是什麼。
將3年以下，定義為A，就更利於電腦的邏輯思維發揮。
同時也為數據傳遞的邏輯思維奠定了堅實的基礎。很顯然，ABC的引入，是簡化公式的核心邏輯思維（HR各模塊數據和制度也應該基於邏輯思維）。
同理，SUMPRODUCT可以運用在夜班補貼、粉塵補貼等多方面。如有需要幫助解決，請關注微信公眾號【焦點說管理】，把文件發給我，我幫您修改完善！
總之：數據要儘量美觀一點、邏輯更清晰一點、輸入輸出更準確一點、計算更優化一點。
小編後期將逐步更新人力資源相關的Excel實際操作介紹，不定時發佈中間版本的彙總版。
關注+私信回覆：【2222】得本文Excel、Word下載方式。
閱讀更多 焦點說管理 的文章
