Excel小結——函數部分（一）

最近學習《別怕，Excel函數其實很簡單》（人民郵電出版社）。主要介紹了Excel最常用的20個函數，主要以win7和Excel2010為寫作環境。當然，針對03，07或13，16版，其使用大體是相同的。高版本自不用說，肯定兼容，低版本的話，有些函數可能沒有，要實現功能需要細微的“曲線救國”，不過也並無大礙。作者是Excel home團隊，這可以說是圖書質量的保證吧。本文不做詳細的基礎功能展開，主要講解我的學習體會和使用時需要注意避開的坑。

Excel功能比我們想象中強大，我們的技能比自己以為的渺小。我們只用真正掌握最常用的20個函數，就足夠高效應付大多數工作了；如果深入掌握50個函數，就可以說相當厲害了，儘管這隻佔Excel函數總量的1/8左右。很多高手戲說，自己使用Excel多年，卻沒用上他全部功能的10%，可能吧。我也相信，如果掌握Excel全部功能的10%，一定可以馳騁職場。

先強調幾個觀點。1、Excel表格可分為數據表和報表。數據表類似於數據倉庫，裡面的數據是給自己看的，最好不要有任何的統計和計算、分類和彙總、格式設置和更改對齊方式等等（例如，文本格式默認是左對齊，數值默認右對齊，如果人為更改，容易掩飾數據格式錯誤），不要有任何的修飾（例如合併單元格）；每類數據類型佔一列，不同列的數據類型不同。另一類是數據報表。報表是呈現的結果，給別人看的，通過公式與數據表關聯，能通過數據表的更改自動更新。2、Excel就像一個設計好的麵包機，我們只需要按規定輸入原料，操作機器即可，並不需要了解其運作原理和過程。學Excel的函數，相當於只學習機器的使用說明書。機器都設計好了，只要我們學個用法，說明書還不願意看嗎？3、使用函數公式時，最好多用單元格引用，而不是直接輸入數據。因為引用單元格的話，方便後期的修改，而不用改動公式本身，更靈活。

還在為低效而沮喪？快來跟我學吧，就現在！

再開始總結函數。Excel函數大體分為：邏輯函數、數學運算和統計函數，文本處理函數，查找類函數，日期與時間函數。

（1）邏輯類函數，用於信息判斷，返回true或false。最常用的是IF函數了。一個if函數就是一次選擇，只能二選一，其輸入格式也較容易理解：=IF(條件判斷, 結果為真返回值, 結果為假返回值)。原理很簡單，但是如果能多想到、多去用，功能還是很豐富的！有兩點值得一提：① 用iserror函數或IFERROR(表達式，出錯時的返回值)能屏蔽錯誤結果。例如，if(iserror(C1/B1)，0，C1/B1)，正常的話顯示C1/B1，若有錯誤，則相似0。②如果要用到if函數的嵌套，為了方便閱讀和理清思路，“枝丫”最好往一個方向“生長”。例如：判斷學生成績，IF(B2>89, "A", IF(B2>79,"B", IF(B2>69,"C", IF(B2>59,"D","E"))))，這樣，閱讀者思考時不會分散注意力。③and、or函數，分別求”且”和”或”，與if配套非常好用，能減少if嵌套的個數。

（2）數學運算和統計函數，功能比較強大了。最常用的統計函數包括：Sum函數、Sumifs函數，Countifs函數，Average函數、averageifs函數。這三者其實用法都非常相似。這裡只以sum函數系列為例：SUMIF(判斷區域，條件，求和區域)，注意，第1和第3個參數的相對位置關係一一對應；sumifs(實際求和區域，第一個條件區域，第一個對應的求和條件，第二個條件區域，第二個對應的求和條件，第N個條件區域，第N個對應的求和條件)，它是2007版及之後對sumif函數的升級版，真的是一個很棒的函數，可以設置127個求和條件，有了它，甚至可以放棄sumif啦。Count系列和average系列非常類似，具體可百度。

有幾點要注意：①這三類函數，若參數為單元格引用，如=sum(A2：A10)，Excel會忽略文本、邏輯值和空單元格，但不可以有錯誤值；當然，若將文本和邏輯值直接設為函數的參數，如=sum(1,2,”abc”,true)，函數不會忽略。②使用sumif函數，可以處理包含錯誤值的區域，例如，輸入=SUMIF(G1:G17,"<=9E+307")，這裡，用9E+307（9×10的307次方）這一接近excel處理上限的數值，來避開邏輯值。因為邏輯值比所有數值都大。③有時，輸入=SUM(G1:G17,">=I2")這種形式會報0，但是數據區域看上去是正常的。原因可能是，區域內數據不是數值類型，其中有一些看不到的字符，如空格等，所以就無法求和，顯示結果為0。④sumif函數為例，第三個參數和第一個參數是一一對應關係，這裡的對應，是指相對位置的對應，如影子一般，跟隨著平移。Sumif函數允許第三個參數與第一個參數區域尺寸不匹配，會自動以所輸入的求和區域最左上角的單元格為起點，擴展至能與第一個參數區域現狀匹配，但是小心sumifs函數不能自動智能擴展區域。⑤注意區分count函數：用於計算區域中，包含數字的單元格的個數。使用格式：count(數組、單元格引用或單元格區域)，不要和條件計數countifs函數弄混淆了！另外，注意：不自己寫，永遠不知道雷區這麼多。比如，要求非空單元格的個數，正確：=COUNTIF(B30:B36,"<>""")或正確：=COUNTIF(B30:B36,"=6")，錯誤：=COUNTIF(B30:B36,<>"")。

這裡，值得一提的是對空單元格的理解。真空單元格：是指該單元格沒有任何內容，沒有公式、沒有透明的字符、沒有不可見的符號。 假空單元格：是專指用公式得出的空單元格，是指用公式設定的空值，即單元格不顯示、有公式無內容。真空與假空的共性：都可以用""來表示。注意：="" 是假空，=" " 不是假空，雖然同樣看不到內容，但是這是得出一個空格字符，用LEN(A1)統計則得值為1。非真空：就是指不是真正的空單元格，其包含了假空和有內容的單元格，這兩種都不是真空單元格，說白了就是單元格寫入了內容，不管是公式還是其他內容。

Excel中的取捨函數。數值取捨分為3種：一是通過設置單元格格式，改變了數據的顯示方式，但是並沒有改變數據本身；二是四捨五入取捨，改變了數據，包括：ROUND函數；三是非四捨五入取捨，包括：INT，TRUNC函數。ROUND函數還包括Roundup函數：遠離零值，絕對值增大舍入，和rounddown函數：指靠近零值，絕對值減小的方向，向下舍入。TRUNC函數直接按位數截取，INT函數對數值進行向下舍、取到最接近的整數。