今天在學(xué)員群給大家分享了一個實際工作中經(jīng)常遇到的難題，就是數(shù)據(jù)表中有錯誤值，這種情況下的快速的計算求和。
雖然題目中的數(shù)據(jù)條目設(shè)計的很少，但是我們實際工作中確實經(jīng)常成千上萬條數(shù)據(jù)的，我們?nèi)绾稳ソ鉀Q這樣的問題呢？？有的同學(xué)說：我們可以直接用定位非法值，通過批量填充將非法值替換為0再求和。這不失為一種好的辦法，但是有時候錯誤值在表格中也是有意義的，我們不希望將其替換為0。今天我來介紹7個解決方案，教你在遍布錯誤值的表格中也能輕松求和。很多人看到這樣的是函數(shù)可能有點兒懵，‘9e307’是個什么鬼？敲黑板了，知識點：9e307是一個極大值，可以理解為一個大的沒邊兒的數(shù)據(jù)，‘<9e307’就相當(dāng)于將所有數(shù)值都滿足條件并納入求和。這個公式基本上小伙伴們真的很少見到過，用過的應(yīng)該就更少了，今天就在這里簡單為大家介紹一下：這函數(shù)是一個大神級函數(shù)，多參數(shù)組合的函數(shù)，有一夫當(dāng)關(guān)萬夫莫開之勇。返回列表或數(shù)據(jù)庫中的合計。AGGREGATE 函數(shù)可將不同的聚合函數(shù)應(yīng)用于列表或數(shù)據(jù)庫，并提供忽略隱藏行和錯誤值的選項。具體公式：AGGREGATE(function_num, options, ref1, [ref2], …)A、第一個參數(shù)function_num是選擇什么計算函數(shù)類型，可選項有19個大類分別用B、第二個參數(shù)option，必須是一個數(shù)值，要明確剔除什么樣的數(shù)值，對應(yīng)關(guān)系如下：0 或省略忽略嵌套 SUBTOTAL 和 AGGREGATE 函數(shù)1忽略隱藏行、嵌套 SUBTOTAL 和 AGGREGATE 函數(shù)2忽略錯誤值、嵌套 SUBTOTAL 和 AGGREGATE 函數(shù)3忽略隱藏行、錯誤值、嵌套 SUBTOTAL 和 AGGREGATE 函數(shù)本題目種用到的參數(shù)是9和6，9為求和，6為忽略錯誤值。上面所介紹的兩種方法是用基本函數(shù)就可以完成的方法，下面幾種方法我們引入數(shù)組函數(shù)的概念來解決了；這個函數(shù)比較容易理解，是運用了iferror函數(shù)，對單元格中的數(shù)據(jù)進行判斷，如果是錯誤值則返回0，然后再求和，但是這個函數(shù)如果單獨回車的話無法得出正確值，必須在輸入公式之后，用CTRL+shift+enter，才能有效果，公式顯示為：{=SUM(IFERROR(J3:J14,0))}這個函數(shù)用的也很巧妙的，使用的是判斷大于0，但該函數(shù)有寫局限性，當(dāng)表格中的數(shù)據(jù)小于0的時候可能會有問題，不過已經(jīng)算很好的答案了，因為大不了再寫一個小于0即可哈^-^=SUM(IF(ISNA(C4:C9),0,C4:C9))
此方法只適用于非法值為N/A的類型，同樣使用數(shù)組進行判斷。方法3中的IFERROR可以包含所有類型的非法值。6、大招6：來自@?? ?? 麗麗???? ??：=SUM(IF(ISERROR(J4:J13),0,J4:J13))同樣是數(shù)組公式，用到了if函數(shù)和iserror函數(shù)，這樣也起到了一個完美的判斷效果；7、大招7：來自@?? ?? 麗麗???? ??：=SUM(IF(ISNUMBER(J4:J13),J4:J13,0))這個組合函數(shù)的技巧很棒呢，直接判斷是否是數(shù)值，如果是就返回數(shù)值本身，否則返回0，再進行求和。這個問題本身就是開放性的問題，在列一一列舉主要是幫助大家更好地掌握函數(shù)應(yīng)用，能夠靈活使用。2、sumif，條件求和函數(shù)，外搭一個sumifs函數(shù)也是ok的喲4、isnumber，判斷是否是數(shù)值函數(shù)最后，做個互動——以上7種方法，你最喜歡哪一種呢？歡迎留言。 作者 趙建軍
Excel表格學(xué)院聯(lián)合創(chuàng)始人，團隊核心成員。
