Excel函數介紹
_
首頁 線上購物 接課行事曆 教學資料 美食消息 新知新聞 黑輪Q&A 黑輪BLOG 黑輪日記 童軍團友會 好貨推薦

My status
首頁
向上
數學函數
文字函數
邏輯函數
查閱以及參照函數
日期與時間函數
財務分析函數
統計分析

 

 

一般工作表函數

 

工作表函數是個可以快速及簡單地執行複雜運算的特別工具。它們就像是精密計算機上的特殊按鍵一樣,能夠計算平方根、對數以及統計估計。

 
分析工具箱

一般我們所使用到的函數,大部分都已經內建在Excel中,然而在 分析工具箱 中,還是有一些額外的函數,而這些函數主要是一組有關資料分析的增益集工具及函數。當這些增益集工具及函數包含一些以巨集為基礎的分析工具時,它同時也包含了一些由插入函數的對話方塊中所能夠獲得的工作表函數。

若要查看您是否已經安裝了 分析工具箱 ,請您檢查 工具 功能表。假如 資料分析 指令是存在於 工具 功能表中(您可能必須在功能表底部的雙箭頭處按一下,以便使得 工具 功能表能夠完全地展開),然後您就能夠進行下一步。假如您並沒有看到該指令時,您也許需要執行安裝程式以便安裝 分析工具箱 

Microsoft Excel 2000中已經內建了數以百計能夠執行廣大範圍計算的函數。某些函數,如SUMSIN以及FACT也許和您已經手動寫好的一些冗長數學公式是相同的。至於其他的函數,如IF以及VLOOKUP都是不能夠藉由公式來複製的。

Excel還提供了數個群組的函數,它們在本章中皆尚未討論到。

當這些內建函數都不能符合您的需求時,您可以自訂函數

 

取得更多有關工作表函數的說明

 

當我們在籌備這本書時,我們必須做些困難的決定。完全地描述數以百計的工作表函數將會填滿整本書。為了能夠提供最大的效益,我們必須去判斷出哪個函數需要仔細敘述以及哪個函數只需簡短描述。對於那些我們沒有仔細的敘述而您想了解更多有關該函數的資訊時,Excel線上輔助系統對於每個工作表函數都會有詳細的敘述。

您也可以藉由使用 插入函數 按鈕來快速地取得有關函數的相關資訊。在本章中有提及相關資訊。

 

函數的能力

 

讓我們用以下的實例來說明Microsoft Excel函數的能力,在圖12-1的工作表中顯示出以12個月為週期的月寵物銷售額。

12-1. B16儲存格中的SUM函數計算出以12個月為週期的yak銷售額。

若您想要找出今年度yak的總銷售額,您可以在B16儲存格中輸入以下的公式

=B4+B5+B6+B7+B8+B9+B10+B11+B12+B13+B14+B15

然而,這個公式是非常累贅麻煩的,您可以使用SUM函數來產生出

=SUM(B4:B15)

此公式會讓Excel將儲存在B4到B15之間的數字加總起來。這個公式的結果和上述提及較長版本的公式結果是相同的:$6,001。

在公式中能夠包含超過一個以上的函數,而且您也可以在公式中將函數以巢狀的方式來使用。舉例而言,此公式

=AVERAGE(SUM(B4:E4),SUM(B5:E5))

會傳回一月以及二月寵物銷售額的加總平均。

 

函數的語法

 

工作表函數有兩個部分:函數的名稱接著帶有一個或更多的引數。函數名稱─如SUM以及AVERAGE─是在描述函數執行時的運作。引數則是指定由該函數所會使用到的數值或儲存格。舉例而言,在此公式中

=SUM(C3:C5)

SUM是函數的名稱,而C3:C5是函數的單一引數。這個公式是將在C3、C4以及C5儲存格中的數字加總起來。

注意

在公式開頭的等於符號(=)指出輸入項是一個公式,而不是文字(例如一個註解或是表格標題)。假如您省去相等符號,那麼Excel會將該輸入項解譯為文字,而且將不會執行任何計算。

請您注意在函數引數前後的括弧。開端的括弧標示出引數的開頭而且其必須在函數的名稱後立刻出現。假如您在函數名稱以及開端的括弧之間輸入一個空格或者是其他字元,Excel會顯示以下的錯誤訊息:「Microsoft Excel在您所輸入公式中發現一個錯誤。您是否願意接受底下所建議的更正嗎?」假如您按下 確定 ,Excel將會自動修正您所輸入的公式。假如您按下 取消 ,錯誤值#NAME?將會出現在該儲存格中。

有少數的函數是沒有引數的,例如:PI以及TRUE。(正如您將會看到的,這些函數通常是以巢狀的方式在其他公式或函數中使用。)即使是它們沒有引數,它們在函數名稱後也必須接續著一組括弧,如:

=A1*PI()

 

使用引數

當您在一個函數中使用超過一個以上的引數時,您需要以逗號將引數隔開。例如:此公式

=PRODUCT(C1,C2,C5)

使得Excel將C1、C2以及C5三個儲存格中的數字相乘。

您在函數中至多可以使用30個引數,而公式的最長長度是不能夠超過1,024個字元。然而,一個單一的引數可以是一個參照,而其範圍可以是在您的工作表儲存格中的任何數字。舉例而言:此函數

=SUM(A1:A5,C2:C10,D3:D17)

有三個引數然而此三個引數總共包括29個儲存格中的數字。(第一個引數A1:A5其所參考的是從A1到A5這五個儲存格的範圍,其餘依此類推。)被參考到的儲存格能夠依次將參考到更多儲存格或範圍的公式包含進來。藉著使用引數,您可以容易地創造出複雜的公式鍊以便執行強而有力的工作表運算。

運算式當作是引數

您可以使用多個函數的結合來創造出一個運算式,此運算式Excel將其求解為一個單一數值並將其解譯為一個引數。舉例而言,此公式

=SUM(SIN(A1*PI()),2*COS(A2*PI()))

SIN(A1*PI())以及2*COS(A2*PI())這兩個運算式皆是被求解出一個單一值,並將此一單一值當作是SUM函數的引數來使用。

 

引數的型態

截至目前為止,在我們所展示出的實例中,所有的引數一直是儲存格或者是範圍的參照。在函數中,您當然也可以使用數字、文字、邏輯數值、範圍名稱、陣列以及錯誤數值當作是引數。某些函數會傳回在這些資料型態中的數值,然後您可以將這些數值當作是引數傳給其他函數。

數值

在一個函數中的引數可以是數值。舉例而言,在此公式中的SUM函數

=SUM(327,209,176)

會將327,209以及176這三個數字進行總計。然而,在工作表儲存格中輸入您所要使用的數字,通常您可以利用參照這些儲存格的方式將其當作引數傳給您所使用的函數。

文字值

您可以將文字當作是引數傳給函數。舉例而言,在此公式中

=TEXT(NOW(),"MMM D, YYYY")

在TEXT函數中的第二個引數─"MMM D, YYYY",是一個用來具體指定特定模型的文字引數,該模型是負責將NOW函數所傳回來的一連串日期數值轉換為為文字字串。文字引數可以是一個以雙引號包夾起來的文字字串或者是對包含著文字的儲存格的參照。

邏輯值

對於少數函數而言,引數只是具體指定出一個選項被設定或是沒被設定;您可以使用邏輯值TRUE去設定某個選項或者是用FALSE指定出該選項尚未被設定。一個邏輯運算式會將TRUE或FALSE值傳回工作表或者是傳回包含該運算式的公式中。舉例而言,在下列IF函數公式中的第一個引數=IF(A1=TRUE,"Future","Past")&"History"

就是一個使用A1儲存格的數值的邏輯運算式;假如在A1中的值是TRUE,那麼A1=TRUE的運算式將會求解出TRUE,IF函數會傳回Future,則整個公式會將文字Future History傳回工作表中。

命名的參照

您可以將一個範圍名稱當作是一個引數傳給某個函數。舉例而言,假如您由 插入 功能表中的 名稱 子功能表裡使用了 定義 指令將名稱QtrlyIncome指定給儲存格範圍C3:C6,您在以下的公式就可以這樣使用

=SUM(QtrlyIncome)

將在C3,C4,C5以及C6儲存格中的數字加總起來。

陣列

在函數中您可以將一個陣列當作是一個引數。某些函數,如TREND以及TRANSPOSE是需要以陣列作為引數;而其他函數並不需要陣列引數但是也可以接受陣列引數在函數中使用。陣列的組成份子可以是數字、文字或邏輯值。

錯誤值

對Excel中的少數函數而言,是可以讓錯誤值當作引數來使用。

混合引數型態

在一個函數中您可以混用不同的引數型態。舉例而言,此公式

=AVERAGE(Group1,A3,5*3)

使用到一個範圍名稱(Group1),一個儲存格參照(A3)以及一個數值運算式(5*3)達成一個單一值。此三種型態在函數中皆是可以被接受的。

 

在工作表中輸入函數

 

您可以從鍵盤鍵入函數或者是由 插入 功能表中的 函數 指令以便在工作表中輸入函數。假如您是以小寫字母鍵入函數而同時您所輸入的函數也是正確的,那麼當您完成函數的鍵入而且按下Enter鍵後或者是選定另一個儲存格後,Excel會將函數的名稱改變為大寫字母。假如您所輸入的字母沒有改變,那麼您很有可能輸入不正確的函數名稱。

 

使用插入函數指令

當您選定一個儲存格而且由 插入 功能表中選擇 函數 後,Excel會顯示出如圖12-2的第一個 插入函數 對話方塊。(請您按下位於 插入函數 對話方塊左下角的 說明 按鈕,以便使得 小幫手 能夠根據這樣的方式來提供建議。)您也可以在 標準 工具列上按下 插入函數 按鈕,以便顯示 插入函數 對話方塊。

為了能選定某個您想使用的函數,首先,您可以先由函數類別清單選定某一種類別(或者選擇 全部 ),然後藉由捲動以字母為順序的 函數名稱 清單來選定您所要的函數。二者擇一地,您也可以按下所要的函數名稱的第一個字母直到該函數名稱在 函數名稱 清單中被反白。按下確定鈕或按下Enter鍵以便輸入該函數。

12-2. 由第一個 插入函數 對話方塊中選擇出您想要使用的函數。

Excel輸入一個等於符號(假如您正在公式的開端插入函數)、函數名稱以及一組括弧。然後Excel會移動到如圖12-3的第二個 插入函數 對話方塊。

12-3. 第二個 插入函數 對話方塊會協助您輸入函數的引數。

在第二個 插入函數 對話方塊中包含一個您所選定的函數中每一個引數的編輯方塊。假如函數接收到一個變數的引數,則此對話方塊會隨著您所輸入的選項引數而遞增。一個引數的敘述的編輯方塊會經常包含出現在接近對話方塊底端的插入點。

在每個引數編輯方塊的右邊,會有一個展示的區域以顯示出該引數目前的值。當您使用參照或自己定義的名稱時,此一展示區域是非常便於使用的。函數目前的數值(公式結果)會出現對話方塊的底端。

當您按下 確定 按鈕或按下 Enter 鍵後,在資料編輯列中會出現已完成的函數。

某些函數,如INDEX皆有超過一個以上的格式。當您由 函數名稱 清單中選擇出一個以上格式的函數,Excel會展示出一個如圖12-14的額外 插入函數 對話方塊,在此對話方塊中您可以選擇出您想要使用的格式。

12-4. 假如一個函數帶有一個以上的格式,那麼在 選擇引數 的對話方塊中可以讓您選擇出您想要的格式。
以鍵盤輸入引數

假如您知道您想要使用的函數名稱而卻記不住該函數的所有引數,您可以使用鍵盤快速鍵在資料編輯列中貼上引數名稱。在資料編輯列中鍵入一個等於符號,在其後面輸入函數名稱,然後按下Ctrl+AExcel會直接跳到第二個 插入函數 對話方塊。當您在使用那些具有易記的名稱卻帶有長字串的引數的函數時,此一功能是特別的有用。

 

插入參照

正如其他公式一樣,您可以將儲存格參照以及自己定義的名稱插入到您的函數中。舉例而言,請您在C11儲存格中輸入一個可以算出儲存格範圍C2:C10的平均數的函數,首先,選定C11儲存格然後輸入=Average(。其次,選定儲存格範圍C2:C10。一個點線框將出現並環繞住您所選定的儲存格,然後一個對您所選定的範圍的參照將出現在資料編輯列中。當您按下Enter以在公式中鎖定時,點線框會消失,同時Excel會在函數後面加入一個結尾括弧。(當您鍵入函數名稱而不是使用 插入函數 ,此時只有在一個公式中以巢狀方式使用函數時,您必須加上結尾括弧。然而,Excel括弧有時候會引起一些無法預期的結果;請您記得再次確認。)

假如您在工作表中定義被命名的範圍、常數或是公式時,您就可以由 插入 功能表的 名稱 子功能表中選擇 貼上 指令,然後從 貼上名稱 對話方塊中的清單選擇名稱。當您按下 確定 按鈕後,此名稱將會出現在公式的插入點處。

 
 

EmailAllen@Chen.name