第 5 章 公式與函數.

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

1 第 5 章 公式與函數

2 本章提要 建立公式 相對參照位址與絕對參照位址 函數的使用 自動計算功能 在公式中使用名稱 工作表的稽核 公式值的驗證與錯誤檢查

3 建立公式 公式的表示法 運算子 輸入公式 更新公式計算的結果

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

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

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

7 輸入公式 請選定要輸入公式的儲存格, 即 E2, 接著將指標移到資料編輯列中輸入等號 "="：

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

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

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

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

12 相對參照位址與絕對參照位址 相對與絕對參照的差異 實例說明 混合參照 切換相對參照與絕對參照位址： 鍵 相對位址公式 絕對位址公式
切換相對參照與絕對參照位址： 鍵 相對位址公式 絕對位址公式 混合參照 為什麼公式要有這麼多種參照方式？

13 相對參照與絕對參照位址 公式中會運用到的位址有兩種類型：相對參照位址與絕對參照位址。
相對參照位址的表示法如：B1、C4；而絕對參照位址的表示法則須在儲存格位址前面加上 "$" 符號, 如：$B$1、$C$4。

14 相對與絕對參照的差異 假設您要前往某地, 但不知道該怎麼走, 於是就向路人打聽。結果得知您現在的位置往前走, 碰到第一個紅綠燈後右轉, 再直走約 100 公尺就到了, 這就是相對參照位址的概念。 另外有人乾脆將實際地址告訴你, 假設為 "中正路二段 60 號", 這就是絕對參照位址的概念。

15 實例說明 說明相對參照位址與絕對參照位址的使用方式。請開啟範例檔案 Ch05-02：

16 實例說明 開啟後請選取 A3, 然後輸入公式 "= A1 + A2" 並計算出結果。根據前面的說明, 這是相對參照位址。接著我們要在 B3 輸入絕對參照位址的公式 = $B$1 +$B$2, 請如下操作： 請選取 B3, 然後在資料編輯列中輸入 "=B1"。

17 實例說明 按下 鍵, 則 B1 便切換成 $B$1, 成為絕對參照位址了：

18 實例說明 接著輸入 "+B2", 再按 鍵將 B2 變成 B$2, 最後按下 鍵, 將公式建立完成：

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

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

21 相對位址公式 A3 的公式 = A1 + A2, 使用了相對位址, 表示要計算 A3 往上找兩個儲存格 (A1、A2) 的總和, 因此當公式複製到 A4 後, 便改成從 A4 往上找兩個儲存格相加, 結果就變成 A2 和 A3 相加的結果：

22 絕對位址公式 B3 的公式 = $B$1＋$B$2, 使用了絕對位址, 因此不管公式複製到哪裡, Excel 都是找出 B1 和 B2 的值來相加, 所以 B3 和 B4 的結果都是一樣的：

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

24 混合參照 請依照下列步驟將 B4 儲存格中的公式改成混合參照公式 = $B1 + B2：
接著選定 B4, 分別拉曳填滿控點至 C4 及B5：

25 混合參照

26 為什麼公式要有這麼多種參照方式？ 當我們在設計公式時, 並不是每一次都使用相對位址的參照方式就可以了。舉個例子來說, 我們要計算股利, 其公式為 "=分配盈餘 ＊((原有股數＋配股數) / 業績)", 若以相對參照位址設計公式, 再拉曳填滿控點來複製公式, 就會發現計算出來的結果有錯：

27 為什麼公式要有這麼多種參照方式？

28 為什麼公式要有這麼多種參照方式？ 在公式中, 分配盈餘應該固定指向 B1 儲存格, 不論公式複製到其他的儲存格也不會改變。所以, 我們應將 E4 的公式修正為 "=$B$1*((C4+D4)/B4), 亦即使用絕對位址的參照方式來指定 $B$1 這個儲存格, 然後再複製到 E5：E8。 依據不同的工作表設計, 公式也必須適當地使用不同的參照方式, 才能確保在複製公式到其他儲存格, 其結果會是正確的。

29 函數的使用 函數的格式 引數的資料類型 輸入函數 ─ 使用函數方塊 快速設定引數 自動加總鈕 輸入其它函數 – 插入函數交談窗 變更引數設定

30 函數的格式 每個函數都包含三個部份： 函數名稱。 引數。 和小括號。

31 加總函數 SUM SUM 即是函數名稱, 從函數名稱可大略得知函數的功能、用途。
小括號用來括住引數, 有些函數雖沒有引數, 但小括號還是不可以省略。 引數是函數計算時所必須使用的資料, 例如 SUM (1, 3, 5) 即表示要計算 1、3、5 三個數字的總和, 其中的 1, 3, 5 就是引數。

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

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

34 輸入函數 － 使用函數方塊 首先選取存放計算結果的儲存格 B8, 並在資料編輯列中數入等號 "="。

35 輸入函數 － 使用函數方塊 接著請您按下函數方塊右側的下拉鈕, 在函數列示窗中選取 SUM, 此時會開啟函數引數交談窗來協助函數的輸入。

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

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

38 快速設定引數 一個引數欄只用來設定一個儲存格位址, 譬如上面的例子本來應該要做如下的設定：
但為了節省時間, 我們通常會將整個引數範圍都設定在一個引數欄中。

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

40 自動加總鈕 一般工具列上的自動加總鈕 , 它可讓我們快速輸入函數：

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

42 輸入其它函數 – 插入函數 交談窗 插入函數交談窗是 Excel 函數的大本營, 當您在函數方塊列示窗中找不到需要的函數時, 就可從這裡來輸入函數。 請選取儲存格 B8, 然後按下資料編輯列上的插入函數鈕 (或執行『插入 / 函數』 命令), 您會發現資料編輯列自動輸入等號 "=", 並且開啟插入函數交談窗：

43 輸入其它函數 – 插入函數 交談窗

44 輸入其它函數 – 插入函數 交談窗 接著我們要從插入函數交談窗中選取 SUM 函數：

45 輸入其它函數 – 插入函數 交談窗

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

47 自動計算功能 使用自動計算 自動計算的功能項目

48 使用自動計算 請您開啟 Ch05-05 範例檔案, 接著選取 B2：D2 儲存格範圍, 則這三個儲存格的加總值馬上就會顯示在狀態列上：

49 自動計算的功能項目

50 自動計算的功能項目 無：取消自動計算的功能。 項目個數：計算選定範圍中, 有幾個非空白的儲存格。
數字項目個數：計算選定範圍中, 資料為數值的儲存格個數。 最大值：找出選定範圍中, 最大的數字資料。 最小值：找出選定範圍中, 最小的數字資料。

51 在公式中使用名稱 命名的原則 定義名稱 在公式中貼上名稱 公式錯了吧？ 刪除名稱 自然語言公式 出現 "#NAME?" 的錯誤訊息？

52 命名的原則 名稱的第一個字元必須是中文、英文、或底線 (_) 字元。其餘字元則可以是英 文、中文、數字、底線、句點 (.) 和問號 (?)。
名稱的第一個字元必須是中文、英文、或底線 (_) 字元。其餘字元則可以是英 文、中文、數字、底線、句點 (.) 和問號 (?)。 名稱最多可達 255 個字元。但別忘了一個中文字就佔兩個字元。 名稱不能類似儲存格的位址, 如 A3、$C$5。 名稱不區分大小寫字母, 所以 MONEY 和money 視為同一個名稱。

53 定義名稱 請您開啟 Ch05-06 範例檔案, 現在我們要將 B2：B3 儲存格範圍命名為 "歷史分數" ：

54 在公式中貼上名稱 請選取 E2, 接著執行 『插入 / 名稱 / 貼上』 命令。在貼上名稱交談窗中選取 "歷史分數" 項目, 然後按下確定鈕, 資料編輯列和儲存格中便會出現 "=歷史分數" ：

55 在公式中貼上名稱 接著鍵入 "+ C2 + D2", 並按下 鍵, 如此林米奇的總分便會顯示在 E2 儲存格 中：

56 公式錯了吧？ 公式的運算元應是單一值、單一儲存格、或參照單一儲存格的名稱。因此當我們指定一欄或一列的儲存格來參照時, Excel 便會主動從範圍中選擇一個儲存格來計算, 其原則如下： 若指定的範圍是一列, 則選擇與公式同欄的儲存格。 若指定的範圍是一欄, 則選擇與公式同列的儲存格。

57 公式錯了吧？ 所以 "歷史分數" 雖然有兩個儲存格, 但此處僅會選擇儲存格 B2 來計算：
如果 Excel 無法從範圍中選定一個儲存格來計算, 則會出現 "#VALUE!" 錯誤訊息, 這時就要修改公式了。

58 刪除名稱 假如定義的名稱用不到了, 想要將它刪除掉, 可執行『插入 / 名稱 / 定義』命令, 在定義名稱交談窗中選取欲刪除的名稱, 再按下刪除鈕即可。

59 自然語言公式 您也可以使用自然語言 — 利用欄標題與列標題的組合來建立公式！

60 自然語言公式 我們現在就利用「自然語言」建立 E3 的計算公式。首先選定儲存格 E3, 然後在資料編輯列中輸入公式 "何米尼歷史 + 何米尼地理+ 何米尼生物", 接著按下 鍵, 公式即建立完成：

61 出現 "#NAME?" 的錯誤訊息？ 若您的自然語言公式的計算結果竟是出現 "#NAME?" 錯誤訊息, 請執行『工具 / 選項』命令, 切換到計算頁次, 然後勾選活頁簿選項區中的公式中允許使用標籤選項, 最後按下確定鈕。 接著再度選取該儲存格, 並將插入點移到資料編輯列中按下 鍵, 即可出現正確的計算結果。

62 工作表的稽核 公式自動校正 範圍搜尋 利用拉曳控點改變公式的參照位址 公式稽核 前導參照 從屬參照 追蹤錯誤 稽核工具列

63 公式自動校正

64 公式自動校正功能

65 範圍搜尋 請您開啟範例檔案 Ch05-07, 假設我們在 E4 輸入公式 "= 歷史分數 + C4 + D4" , 結果卻出現 # VALUE!：

66 範圍搜尋 請雙按 E4 儲存格, 顯示 Excel 的範圍搜尋功能：

67 利用拉曳控點改變公式的 參照位址 利用範圍搜尋功能找出公式參照的儲存格或範圍之後, 可直接拉曳四個角落的填滿控點來改變公式參照的位址。例如下圖中 E2 的公式為 "=SUM (A2：C2)", 只要往左拉曳 C2 儲存格的填滿控點, 便可將公式改為 "=SUM (A2：B2)： 不過, 若公式中包含 「自然語言」或 「名稱」, 則用範圍搜尋功能就不會出現控點, 所以也就不能用拉曳的方式改變公式參照的儲存格了。

68 利用拉曳控點改變公式的 參照位址

69 公式稽核 前導參照：影響某儲存格中公式或函數的所有儲存格位址。 從屬參照：被某儲存格影響到的所有儲存格。
對 A3 來說, A1、A2 為其前導參照；C3 則為其從屬參照。

70 前導參照 請選取儲存格 B4, 然後執行『工具 / 公式稽核 / 追蹤前導參照』命令來查出 B4 的前導參照儲存格：

71 從屬參照 請選取儲存格 B4, 然後執行『工具 / 公式稽核 / 追蹤從屬參照』命令, 即可找出 B4 的從屬參照儲存格：

72 追蹤錯誤 現在請您切換到 Ch05-08 的 Sheet2 工作表, 然後選取儲存格 E4, 執行『工具 / 公式稽核 / 追蹤錯誤』命令來找出錯誤的來源：

73 稽核工具列 利用稽核工具列來進行工作表的稽核。請執行 『工具 / 公式稽核 / 顯示公式稽核工具列』命令, 將公式稽核工具列顯示出來：

74 稽核工具列

75 公式值的驗證與錯誤檢查 公式值的驗證 錯誤檢查 關閉錯誤檢查功能 從『公式稽核工具列』 執行錯誤檢查

76 公式值的驗證 請您開啟範例檔案 Ch05-09, 在此範例中C2 的值 = A2*B2, 而且 C2 儲存格所設定的驗證條件為小於 100 的整數：

77 公式值的驗證 假設更改 A2 值為 30, 便會造成 C2 無法符合驗證規則。為了避免事後修正又不符合原驗證規則, 那麼我們可以按下公式稽核工具列上的圈選錯誤資料鈕 來檢查：

78 錯誤檢查 當儲存格發生 "#VALUE!"、 "#NAME?" 這類錯誤時, 只要選取錯誤的儲存格, 便可在儲存格旁邊發現錯誤檢查選項鈕 的蹤影, 它提供了幾種不同的除錯方法, 我們一起來探討看看！請開啟範例檔案 Ch05-10：

79 錯誤檢查

80 錯誤檢查 接著, 請您選擇『顯示計算步驟』 , 開啟評估值公式交談窗, 看看能否找出 E3 發生錯誤的原因：

81 關閉錯誤檢查功能 現在請您改選擇 鈕下拉選單中的『錯誤檢查選項』命令, 開啟選項交談窗 (或執行『工具 / 選項』命令, 並切換到錯誤檢查頁次)：

82 關閉錯誤檢查功能

83 從『公式稽核工具列』 執行錯誤檢查 假如您將錯誤檢查功能關閉掉, 而不會出現錯誤檢查選項鈕時, 該怎麼進行除錯呢？很簡單, 您可以將公式稽核工具列顯示出來, 然後使用其中的錯誤檢查鈕 來為你指出錯誤的儲存格：

84 執行錯誤檢查 從『公式稽核工具列』