1.3　Excel 2016函數公式

在統計學中，可以廣泛、大量運用Excel函數公式進行統計與分析。Excel函數是一種在需要時可以直接調用的表達式，是預先定義好的公式，是一種特殊的公式。每一個函數都有一個唯一的名稱，每一個函數都有其功能和用途。公式是由用戶自行設計用來對工作表進行計算和處理的計算式。可以說函數是公式，但不能說公式是函數。函數可以是公式的一部分，但公式不一定總是包含函數?？傮w上說，函數與公式之間的關系是包含和被包含的關系，如圖1-12所示。

很多時候，我們將函數和公式統稱為函數公式，這實際上肯定了函數在公式中的獨特價值和特殊地位。

圖1-12　函數與公式的關系

1.3.1　Excel函數結構

Excel函數是預先編寫的公式，可以對一個或多個值執行運算，并返回一個或多個值。函數可以簡化和縮短工作表中的公式，尤其在用很長的公式或執行復雜的計算時。

函數一般由函數名、一對半角括號、參數、半角逗號組成。函數名稱后面緊跟左括號，

接著是用逗號分隔的被稱為參數的內容，最后用右括號表示函數結束。絕大多數函數有參數，函數的結構形式為：

    函數名（參數1,參數2,參數3,…）

其中，函數名為需要執行某種運算的函數的名稱，參數可以是數字、文本、邏輯值、引用、數組、其他函數等。參數是函數中最復雜的組成部分，它規定了函數的運算對象、順序或結構等。

以IF函數為例。IF函數是Excel中最常用的函數之一，它可以對值和期待值進行邏輯比較。IF函數的語法為：

    IF(logical_test,value_if_true,value_if_false)

將IF函數的語法“翻譯”成中文，就是：

    如果（邏輯為 True，則執行某些操作，否則執行其他操作）

例如：=IF(A1>=60，"合格"，"不合格")

“Logical_test”為第1參數，為條件部分，表示計算結果為TRUE（真）或FALSE（假）的任意值或表達式。“A1>=60”就是一個邏輯表達式，如果單元格A1中的值大于或等于60，表達式即為TRUE，否則為FALSE。本參數可使用任何比較運算符。

“Value_if_truelogical_test”為第2參數，是條件為TRUE時返回的值。如果“A1>=60”參數值為TRUE，則IF函數將顯示文本“合格”。本參數可以是其他公式。

“Value_if_falselogical_test”為第3參數，是條件為FALSE時返回的值。如果“A1>=60”參數值為FALSE，則IF函數將顯示文本“不合格”。本參數也可以是其他公式。

Excel 2016允許嵌套最多64個不同的IF函數，以完成復雜的邏輯判斷。

1.3.2　函數參數的類型

參數可以是常量（數字和文本）、邏輯值（例如TRUE或FALSE）、數組、錯誤值（例如#N/A）或單元格引用（例如E1:H1），甚至可以是另一個或幾個函數等。參數的類型和位置必須滿足函數語法的要求，否則將返回錯誤信息。

1．常量

常量是直接輸入到單元格或公式中的數字或文本，或由名稱所代表的數字或文本值，例如數字“3000”、日期“2017-1-1”和文本“中華”都是常量。但是公式或由公式計算出的結果都不是常量，它會隨著原始數據和函數參數的變化而變化。

公式中，常常需要將文本型數字轉換為數值，可以使用VALUE函數轉換，也可讓文本型數字直接參與加、減、乘、除、乘冪等算術運算來轉換。下面8個公式均可以將A1單元格中的文本型數字轉換為數值：

其中，最后一個公式最為簡便和常用。

要注意區分空單元格、空文本和空格3個概念。

空單元格是指在一個單元格中未曾輸入過任何數據和公式，或者雖曾輸入過數據和公式，但已被清除。

空文本也是文本，也要按照Excel的要求用一對半角雙引號“""”包含，但半角雙引號“""”中間什么內容也沒有，其字符長度為0，但絕不可以將空文本視作0。當然，可以用N函數將空文本轉換為“0”，公式為“=N（空文本）”。在Excel公式中，經常使用空文本來代替用戶不想看見的FALSE、0等結果，公式為“=原公式&""”。

空格是指按下鍵盤上的空格鍵得到的文本，具有字符長度，按一下就是一個字符，可以用LEN函數判斷其字符長度，公式為“=LEN（""）”。

2．邏輯值

邏輯值是比較特殊的一類參數，它只有TRUE（真）或FALSE（假）兩種類型。例如在公式“=IF(A3=0，""，A2/A3)”中，“A3=0”就是一個可以返回TRUE或FALSE兩種結果的參數。當“A3=0”為TRUE時，在公式所在單元格中填入一個空文本，否則在單元格中填入“A2/A3”的計算結果。

很多時候，TURE可以被當作1來使用，FALSE可以被當作0來使用。如“VLOOKUP(A1,B1:C10,2,FALSE)”這個公式中的“FALSE”可以寫成“0”。

邏輯值之間可以直接運算，運算結果為數值1或0，如下面3個式子：

TRUE*TRUE=1　　TRUE*FALSE=0　　FALSE*FALSE=0

邏輯值還可以與1或0進行加、減、乘、除、開方、乘冪等算術運算，甚至與百分號進行運算，運算結果為數值1或0，如下面16個式子：

3．錯誤值

使用錯誤值作為參數的主要是信息函數，例如“ERROR.TYPE”函數就是以錯誤值作為參數。常使用IFERROR函數將錯誤值轉換為“0”，公式為“=IFERROR（錯誤值，）”。

4．表達式

當表達式作為參數時，Excel會先對其進行計算，然后將計算結果作為參數。例如“=SQRT(A1^2+B1^2)”。式中，SQRT函數是求平方根的函數，其參數是表達式“A1^2+B1^2”，先計算表達式，再計算表達式結果的平方根。

5．單元格引用

單元格引用是函數中最常見的參數，引用的目的在于標識工作表單元格或單元格區域，指明公式或函數所使用的數據的位置，便于它們使用工作表各處的數據。Excel默認的引用方式，稱為“A1引用樣式”。還有一種方式為“R1C1引用樣式”。

一個函數可以引用多個單元格的數據，或者在多個函數中引用同一個單元格的數據。可以引用本工作表的數據，也可以引用同一工作簿其他工作表的數據，還可以引用其他工作簿中的數據。

根據公式所在單元格的位置發生變化、單元格引用變或不變的情況，可以將引用分為相對引用、絕對引用和混合引用3種類型。對單元格A1而言，$A$1為絕對引用，A1為相對引用，A$1、$A1為混合引用。單元格引用中，列標或行號前有美元符號“$”的為單元格引用的絕對引用部分，否則為相對引用部分。在列標或行號之前加美元符號，是為了“鎖定”列或行，不至于因為填充或復制公式而改變單元格引用。在單元格公式中使用單元格引用時，如果單元格引用中的列標部分為相對引用，當向右填充公式時，則列標逐列自動加1；如果單元格引用中的行號部分為相對引用，當向下填充公式時，則行號逐行自動加1；如果單元格引用中的列標和行號部分都為相對引用，當向右和向下填充公式時，則列標和行號逐列和逐行都自動加1。對單元格A1而言，復制公式時，有以下4種情況。

$A$1樣式：絕對引用，向右向下時引用的范圍都不變。

A1樣式：相對引用，向右向下時引用的范圍都會變。

A$1樣式：混合引用，列相對行絕對引用，向右時列標變化，而向下時行標不會變。

$A1樣式：混合引用，列絕對行相對引用，向右時列標不會變，而向下時行標會變。

跨表引用時，對象的前面必須用“！”作為工作表分隔符；跨工作簿引用時，再在工作表名稱前面用中括號“[]”作為工作簿分隔符。

6．數組

數組用于可產生多個結果或可以對存放在行和列中的一組參數進行計算。Excel中有常量數組、區域數組和內存數組。常量數組放在“{}”內部，而且內部各列的數值要用半角逗號“，”隔開，各行的數值要用半角分號“;”隔開。假如要表示第1行中的30、40、50和第2行中的60、70、80，就應該建立一個2行3列的常量數組“{30,40,50;60,70,80}”。

區域數組是一個矩形的單元格區域，該區域中的單元格共用一個公式。例如公式“{=TREND(B1:B3,A1:A3)}”作為數組公式使用時，它所引用的矩形單元格區域“B1:B3”“A1:A3”都是區域數組。“{}”由按“Ctrl+Shift+Enter”組合鍵自動生成，不能手動加上。

內存數組是存在于計算機內存中的數組。

7．名稱

為了更加直觀地標識單元格或單元格區域，我們可以給它們賦予一個名稱，從而在公式或函數中直接引用。例如“B2:B46”區域存放著學生的物理成績，求解平均分的公式一般是“=AVERAGE(B2:B46)”。在給B2:B46區域命名為名稱“物理”以后，該公式就可以變為“=AVERAGE（物理）”，從而使公式變得更加直觀。名稱引用多半是一種絕對引用。名稱有工作表級名稱和工作簿級名稱之分，工作表級名稱只能在本工作表中使用，工作簿級名稱可以跨工作表使用。

8．嵌套函數

除了上面介紹的情況，函數也可以是嵌套的，即一個函數是另一個函數的參數，例如“=IF(OR(RIGHTB(E2,1)="1"，RIGHTB(E2,1)="3"，RIGHTB(E2,1)="5"，RIGHTB(E2,1)="7"，RIGHTB(E2,1)="9")，"男"，"女")”。其中公式中的IF函數使用了嵌套的RIGHTB函數，并將后者返回的結果作為IF函數的邏輯判斷依據。

1.3.3　統計學常用函數

在Excel 2016中，統計函數有110個，大致可以分為7個小類，包括用于描述統計的函數、用于概率分布的函數、用于參數估計的函數、用于假設檢驗的函數、用于方差分析的函數、用于相關和回歸分析的函數以及其他統計函數。此外，數據庫函數及一些數學和三角函數也常用于統計分析，參見表1-2～表1-4。

表1-2　Excel 2016統計函數

續表

續表

續表

續表

表1-3　Excel 2016數據庫函數

表1-4　Excel 2016常用統計的數學函數

1.3.4　獲取Excel函數的幫助

Excel內置400多個函數，絕大多數人不可能全部熟悉，如果要查找Excel函數的功能和語法，最快速和簡單的方法是在編輯函數公式時獲取Excel函數的幫助。

?　將光標放置于Excel編輯區函數名稱之后的參數位置，Excel會自動彈出函數的參數說明框。

?　將光標放置于該框中的函數名稱之上，光標會變成手形，表明設有超鏈接，單擊超鏈接，就會彈出關于該函數的網頁。

操作過程及結果如圖1-13所示。

圖1-13　獲取Excel函數的幫助

1.3.5　啟用“開發工具”選項卡

如果希望使用宏功能，可在“開發工具”選項卡下選擇相應命令。在默認情況下，Excel 2016中功能區中不顯示“開發工具”選項卡，需要用戶進行自行設置。啟用“開發工具”選項卡的操作過程為：

?　在Excel功能區右擊。

?　在彈出的快捷菜單中，選擇“自定義功能區”命令。

?　在打開的“Excel選項”對話框左側的列表中，選擇“自定義功能區”選項。

?　在右側的“自定義功能區”列表框中勾選“開發工具”復選框。

?　單擊“確定”按鈕，“開發工具”選項卡得以啟用。

操作過程及結果如圖1-14所示。

圖1-14　啟用“開發工具”選項卡

1.3.6　加載“分析工具庫”工具

在Excel 2016中，有一組可以進行統計分析的數據分析工具，稱為“分析工具庫”。利用“分析工具庫”，可以很方便地進行常用的統計分析。使用時，只需要為每一個分析工具提供必要的數據和參數，該工具就會使用適宜的統計或工程函數，在輸出表格中顯示相應的結果。其中，有些工具在生成輸出表格的同時，還能生成圖表。

使用“分析工具庫”工具，必須事先要加載。加載過程為：

?　在Excel功能區右擊。

?　在彈出的快捷菜單中選擇“自定義功能區”菜單。

?　在打開的“Excel選項”對話框左側的列表中選擇“加載項”選項。

?　在右側的“加載項”列表框中選擇“分析工具庫”選項。

?　單擊“轉到”按鈕。

?　在“加載項”對話框中勾選“分析工具庫”復選框。

?　單擊“確定”按鈕，完成設置。在功能區“數據”選項卡中，就會新增一個名為“分析”的組，組中的“數據分析”按鈕就是啟動“分析工具庫”的開關。

操作過程及結果如圖1-15所示。

圖1-15　加載“分析工具庫”