第 3 章 公式與函數.

Presentation on theme: "第 3 章 公式與函數."— Presentation transcript:

1 第 3 章 公式與函數

2 本章重點 3-1 建立公式 3-2 相對參照位址與絕對參照位址 3-3 函數的使用 3-4 插入函數的綜合練習
3-5 不需輸入公式也能快速自動計算結果

3 3-1 建立公式 當我們需要將工作表中的數字資料做加、減、乘、除…等運算時, 就可以把計算的動作交給 Excel 的公式去做, 省去自行運算的工夫。而且當資料有所變動時, 公式計算的結果還會立即更新。 公式的表示法 運算子 輸入公式 更新公式計算的結果

4 公式的表示法 Excel 的公式和一般數學公式差不多, 通常數學公式的表示法為：
若將這個公式改用 Excel 表示, 則變成要在 A3 儲存格中輸入： 意思是, Excel 會將 A1 儲存格的值 ＋ A2 儲存格的值, 然後把結果顯示在 A3 儲存格中。

5 運算子 Excel 的公式運算共分為參照、算術、文字、與比較四大類, 下表按運算的優先順序列出所有的運算子：

6 輸入公式 輸入公式必須以等號 "=" 起首, 譬如 = A1 + A2, 這樣 Excel 才知道我們輸入的是公式, 而不是一般的文字資料。現在我們就來練習建立公式, 請開啟範例檔案 E03-01：

7 輸入公式 我們打算在 E2 儲存格存放 "王書桓的各科總分" , 也就是要將 "王書桓" 的英文、生物、理化分數加總起來, 放到 E2 儲存格中, 因此將 E2 儲存格的公式設計為 "= B2 + C2+ D2"： 請選定要輸入公式的 E2 儲存格, 並將指標移到資料編輯列中輸入等號 "="：

8 輸入公式 接著輸入 "=" 之後的公式, 即 "B2 + C2 + D2"。我們先輸入 "B2", 請在儲存格 B2 上按一下, Excel 便會將 B2 輸入到資料編輯列中：

9 輸入公式 再來請輸入 "+" , 然後選取 C2 儲存格, 再輸入 "+" , 選取 D2 儲存格, 如此公式的內容便輸入完成了：

10 輸入公式 最後按下資料編輯列上的輸入鈕 或按下 [Enter] 鍵, 公式計算的結果馬上顯示在 E2 儲存格中：

11 更新公式計算的結果 公式的計算結果會隨著儲存格的變動而自動更新。以上例來說, 假設當公式建好以後, 才發現 "王書桓" 的英文成績打錯了, 應該是 "90" 分才對, 當我們將儲存格 B2 的值改成 "90", 您將發現在 E2 儲存格中的計算結果立即從 220 更新為 225：

12 3-2 相對參照位址與絕對參照位址 公式中會運用到的位址有兩種類型：相對參照位址與絕對參照位址。相對參照位址的表示法如：B1、C4；而絕對參照位址的表示法則須在儲存格位址前面加上 "$" 符號,如：$B$1、$C$4。 相對與絕對參照的差異 實例說明 切換相對參照與絕對參照位址： F4 鍵 混合參照

13 相對與絕對參照的差異 假設你要前往某地, 但不知道該怎麼走, 於是就向路人打聽。結果得知你現在的位置往前走, 碰到第一個紅綠燈後右轉, 再直走約 100 公尺就到了, 這就是相對參照位址的概念。 另外有人乾脆將實際地址告訴你, 假設為 “中正路二段 60 號”, 這就是絕對參照位址的概念, 由於地址具有唯一性, 所以不論你在什麼地方, 根據這個絕對參照位址, 所找到的永遠是同一個地點。

14 相對與絕對參照的差異 將這兩者的特性套用在公式上, 代表相對參照位址會隨著公式的位置而改變, 而絕對參照位址則不管公式在什麼地方, 它永遠指向同一個儲存格。

15 實例說明 底下我們以實例為你說明相對參照位址與絕對參照位址的使用方式。請開啟範例檔案 E03-02：
開啟後請選取 D2 儲存格, 輸入公式 = B2 + C2 並計算出結果。根據前面的說明, 這是相對參照位址。接著我們要在 D3 儲存格輸入絕對參照位址的公式 = $B$3 + $C$3：

16 實例說明 請選取 D3 儲存格, 然後在資料編輯列中輸入 "=B3"。
按下 [F4] 鍵, 則 B3 便切換成$B$3, 成為絕對參照位址了：

17 實例說明 接著輸入 "+C3", 再按 [F4] 鍵將 C3 變成 $C$3, 最後按下 [Enter] 鍵, 公式就建立完成了：

18 實例說明 D2 及 D3 的公式分別是由相對位址與絕對位址組成, 但兩者的計算結果卻一樣。到底它們差別在哪裡呢？請選定 D2：D3 儲存格, 拉曳填滿控點到下一欄, 將公式複製到其他的儲存格：

19 切換相對參照與絕對參照位址： F4 鍵 [F4] 鍵可循序切換儲存格位址的參照類型, 每按一次 [F4] 鍵, 參照位址的類型就會改變, 其切換結果如下：

20 實例說明 相對位址公式 D2 的公式 = B2 + C2, 使用了相對位址, 表示要計算 D2 往左找兩個儲存格 (B2、C2)的總和, 因此當公式複製到 E2 儲存格後, 便改成從 E2 往左找兩個儲存格相加, 結果就變成 C2 和 D3 相加的結果：

21 實例說明 絕對位址公式 D3 的公式 = $B$3 ＋$C$3, 使用了絕對位址, 因此不管公式複製到哪裡, Excel 都是找出 B3 和 C3 的值來相加, 所以 D3 和 E3 的結果都是一樣的：

22 混合參照 我們可以在公式中同時使用相對參照與絕對參照, 這種情形稱為混合參照。例如：
這種公式在複製後, 絕對參照的部份 (如 $B1 的 $B) 不會變動, 而相對參照的部份則會隨情況做調整。

23 混合參照 我們繼續沿用範例檔案 E03-02 做示範, 請依照下列步驟將 E3 儲存格中的公式改成混合參照公式 = $B3 + C3：
請雙按 E3 儲存格, 進入編輯模式, 將插入點移至 "=" 之後, 接著按兩次 [F4] 鍵, 讓$B$3變成 $B3。

24 混合參照 將插入點移至 "+" 之後, 按 3 次 [F4] 鍵將 $C$3 變成 C3, 最後按下 [Enter] 鍵, 公式便輸入完成。
接著選定 E3, 分別拉曳填滿控點至 F3 及E4：

25 混合參照

26 混合參照

27 3-3 函數的使用 函數是 Excel 根據各種需要, 預先設計好的運算公式, 可讓你節省自行設計公式的時間, 底下我們就來看看如何運用 Excel 的函數。 函數的格式 引數的資料類型 輸入函數 － 使用函數方塊 自動加總鈕 開啟「插入函數」交談窗輸入其它函數 變更引數設定

28 函數的格式 每個函數都包含三個部份：函數名稱、引數和小括號。我們以加總函數 SUM 來說明：
小括號用來括住引數, 有些函數雖沒有引數, 但小括號還是不可以省略。 引數是函數計算時所必須使用的資料, 例如 SUM (1, 3, 5) 即表示要計算 1、3、5 三個數字的總和, 其中的 1, 3, 5 就是引數。

29 引數的資料類型 函數的引數不僅只有數字類型而已, 也可以是文字, 或是以下幾項：
位址：如 SUM (B1, C3) 即是要計算 B1 儲存格的值 + C3 儲存格的值。 範圍：如 SUM (A1：A4) 即是要加總 A1：A4 範圍的值。 函數：如 SQRT (SUM(B1：B4)) 即是先求出 B1：B4 的總和後, 再開平方根的結果。

30 輸入函數 － 使用函數方塊 函數也是公式的一種, 所以輸入函數時, 也必須以等號 "=" 起首。請開啟範例檔案 E03-03, 假設我們要在 B8 儲存格運用 SUM 函數來計算班費的總支出：

31 輸入函數 － 使用函數方塊 首先選取存放計算結果的 B8 儲存格, 並在資料編輯列中數入等號 "="。
接著請按下函數方塊右側的下拉鈕, 在列示窗中選取 SUM, 此時會開啟函數引數交談窗來協助函數的輸入：

32 輸入函數 － 使用函數方塊

33 輸入函數 － 使用函數方塊

34 輸入函數 － 使用函數方塊 再來就是要設定函數的引數。請先按下第一個引數欄 Number 1 右側的摺疊鈕, 將函數引數交談窗收起來, 再從工作表中選取 B4：B6 當作引數：

35 輸入函數 － 使用函數方塊 請按一下引數欄右側的展開鈕, 再度將函數引數交談窗展開：

36 輸入函數 － 使用函數方塊 按下確定鈕, 函數的計算結果就顯示在 B8 儲存格內：

37 自動加總鈕 在常用頁次下的編輯區有一個加總鈕, 可讓我們快速輸入函數。例如當我們選取 B8 儲存格, 並按下 鈕時, 便會自動插入 SUM 函數, 且連引數都自動幫我們設定好了：

38 自動加總鈕 事實上, 除了加總功能之外, 加總鈕還提供數種常用的函數供我們選擇使用, 只要按下 鈕旁邊的下拉鈕, 即可選擇要進行的計算：

39 開啟「插入函數」交談窗輸入其它函數 插入函數交談窗是 Excel 函數的大本營, 當你在函數方塊列示窗中找不到需要的函數時, 就可從這裡來輸入函數。請開啟範例檔案 E03-04,現在我們要練習透過插入函數交談窗來輸入函數, 列出麵包店各家門市的營業額排名：

40 開啟「插入函數」交談窗輸入其它函數 請選取 C4 儲存格, 然後按下資料編輯列上的插入函數鈕 (或是切換到公式頁次,在函數程式庫區中按下插入函數鈕), 你會發現資料編輯列自動輸入等號 "=", 並且開啟插入函數交談窗：

41 開啟「插入函數」交談窗輸入其它函數

42 開啟「插入函數」交談窗輸入其它函數 接著我們要從插入函數交談窗中選取 RANK 函數, 進行門市營業額的排名：

43 開啟「插入函數」交談窗輸入其它函數 開啟函數引數交談窗後, 請在 Number 引數欄中輸入 "B4", 然後在 Ref 欄輸入"$B$4:$B$13"：

44 開啟「插入函數」交談窗輸入其它函數 按下確定鈕即可得到計算結果。

45 開啟「插入函數」交談窗輸入其它函數 請選取 C4 儲存格, 並拉曳其填滿控點到 C13 儲存格。

46 變更引數設定 當你將函數存入儲存格以後, 若想變更引數設定, 請選取函數所在的儲存格, 然後按下插入函數鈕, 即可展開函數引數交談窗來重新設定引數。

47 3-4 插入函數的綜合練習 學會插入函數之後, 我們來做個綜合練習, 讓你對函數有更進一步的了解。請開啟範例檔案 E03-05, 在這個範例裡, 我們要依年資來計算中秋節發放的獎金, 年資未滿 1 年的員工沒有獎金, 滿 1 年以上未滿 3 年則發放5, 000 元獎金, 滿 3 年以上則發給8, 000元獎金。

48 插入函數的綜合練習

49 插入函數的綜合練習 首先要進行年資的計算, 請先選取 D2 儲存格, 在此要使用 DATEDIF 這個函數來計算兩個日期之間的年數、月數或天數, 其格式如下：

50 插入函數的綜合練習 計算出 "蔣文文" 的年資後, 請選取 D2 儲存格, 然後拉曳填滿控點至 D11 儲存格, 即可算出所有員工的年資。

51 插入函數的綜合練習 計算出年資後, 就可以依年資來計算獎金, 獎金的發放標準如右：

52 插入函數的綜合練習 請選取 E2 儲存格, 我們要使用 IF 函數來計算獎金。IF 函數是用來判斷測試條件是否成立, 如果所傳回的值為TRUE 時, 就執行條件成立時的作業, 反之則執行條件不成立時的作業。IF 函數的格式為：

53 插入函數的綜合練習

54 插入函數的綜合練習 計算好 "蔣文文" 的獎金後, 請將 E2 儲存格的填滿控點拉曳到 E11 儲存格, 即可算出所有人的獎金。

55 DATEDIF 的差距單位參數 在 DATEDIF 函數中, 可依據想要求算的結果, 搭配使用各種差距單位參數, 你可以參考以下的列表：

56 3-5 不需輸入公式也能 快速自動計算結果 本章最後我們要告訴你一個超實用的自動計算功能, 讓你在不需輸入任何公式或函數的情況下, 也能快速得到運算結果。請開啟範例檔案 E03-07, 並選取 B4：B13 儲存格, 即可馬上在狀態列中看到計算結果：

57 不需輸入公式也能快速自動計算結果

58 不需輸入公式也能快速自動計算結果 自動計算功能不僅會計算總和、平均值、項目個數, 還可以計算最大值、最小值、數字計數等。

59 不需輸入公式也能快速自動計算結果 以下是各項自動計算的功能說明： 平均值：計算選取範圍的平均值。
項目個數：計算選取範圍有幾個非空白的儲存格。 數字計數：計算選取範圍內資料為數值的儲存格個數。 最小值：找出選取範圍中最小的數字資料。 最大值：找出選取範圍中最大的數字資料。 加總：計算選取範圍內所有數值的總和。