第3章公式與函數的基本應用Excel辦公應用高級教程目錄Contents3.1

初識公式3.2

理解Excel函數3.7

疑難解答3.3

審核和檢查公式3.4

常用數學函數3.6

實戰演練：制作銷售數據統計表3.5

常用統計函數3.1.1公式的組成Excel公式通常由“等號”、“運算符”、“單元格引用”、“函數”、“數據常量”等組成，下面列舉了一些常見的公式。3.1.2

公式的輸入通常情況下，當以“=”為開頭，在單元格內輸入時，單元格將自動變成公式輸入狀態。進入公式輸入模式后，鼠標選中其他單元格或區域時，該選中的單元格將會作為引用自動輸入到公式中。3.1.2

公式的輸入當公式輸入完成后，按【Enter】鍵，即可結束輸入狀態，計算出結果。如果需要修改公式，則可以通過以下幾種方法再次進入公式編輯狀態。1.雙擊公式所在單元格，即可進入編輯狀態。2.選中公式所在單元格，單擊上方的“編輯欄”，在編輯欄中進行修改。3.選中公式所在單元格，按【F2】鍵，即可進入編輯狀態。3.1.3

復制和填充公式當表格中多個單元格所需公式的計算規則相同時，可以使用復制和填充功能進行計算。1.復制公式選擇公式所在單元格，按【Ctrl+C】組合鍵進行復制。然后選擇目標單元格區域，按【Ctrl+V】組合鍵，粘貼公式。3.1.3

復制和填充公式2.填充公式方法一：選擇公式所在單元格，將鼠標光標移至該單元格右下角，向下拖動鼠標填充公式。方法二：選擇公式所在單元格，將鼠標光標移至該單元格右下角。雙擊鼠標，公式將向下填充到其他單元格中。3.1.4

公式中的常見錯誤使用公式進行計算時，可能會因為某種原因而無法得到或顯示正確結果，在單元格中返回錯誤值信息。3.1.5

單元格的3種引用在公式中的引用具有以下關系：如果單元格A1包含公式“=B1”。那么B1就是A1的引用單元格，A1就是B1的從屬單元格。從屬單元格與引用單元格之間的位置關系稱為單元格引用的相對性，可以分為3種不同的引用方式，即相對引用、絕對引用和混合引用。3.1.5

單元格的3種引用1.相對引用當復制公式到其他單元格時，Excel保持從屬單元格與引用單元格的相對位置不變，稱為相對引用。例如，在B1單元格輸入公式“=A1”。當向右復制公式時，將依次變為：=B1、=C1、=D1等。3.1.5

單元格的3種引用2.絕對引用當復制公式到其他單元格時，Excel保持公式所引用的單元格絕對位置不變，稱為絕對引用。例如，在B1單元格輸入公式“=$A$3”。則無論公式向右還是向下復制，都始終保持為“=$A$3”不變。3.1.5

單元格的3種引用3.混合引用當復制公式到其他單元格時，Excel僅保持所引用單元格的行或列方向之一的絕對位置不變，而另一方向位置發生變化，這種引用方式稱為混合引用，可分為行絕對列相對引用和行相對列絕對引用。例如，在B1單元格輸入公式“=$A2”。則公式向右復制時始終保持為“=$A2”。向下復制時行號將發生變化。即行相對列絕對引用。目錄Contents3.1

初識公式3.2

理解Excel函數3.7

疑難解答3.3

審核和檢查公式3.4

常用數學函數3.6

實戰演練：制作銷售數據統計表3.5

常用統計函數3.2.1

函數的結構在公式中使用函數時，通常由表示公式開始的“=”號、函數名稱、左括號、以半角逗號相間隔的參數和右括號構成。有的函數可以允許多個參數，有的函數沒有參數或可以不需要參數。函數的參數，可以由數值、日期和文本等元素組成，可以使用常量、數組、單元格引用或其他函數。當使用函數作為另一個函數的參數時，稱為函數的嵌套。3.2.2

函數的類型由于Excel的版本不同，所包含的函數類型也有所不同。在Excel2019版本中，函數的類型可分為：邏輯函數、文本函數、日期和時間函數、查找與引用函數、數學和三角函數、統計函數、信息函數、財務函數等。用戶可以在“公式”選項卡中的“函數庫”選項組中對函數的種類進行查看。3.2.3

函數的輸入方法用戶可以通過多種方法輸入函數，例如，使用函數庫輸入已知類別的函數、使用“插入函數”向導輸入函數。方法一：使用函數庫輸入已知類別的函數在“公式”選項卡中的“函數庫”選項組中單擊選擇需要的函數。打開“函數參數”對話框，從中設置各參數，單擊“確定”按鈕。即可在單元格中輸入函數公式。3.2.3

函數的輸入方法方法二：使用“插入函數”向導輸入函數如果用戶對函數所屬的類別不太熟悉，則可以使用“插入函數”向導選擇或搜索所需函數。在“公式”選項卡中單擊“插入函數”按鈕，或單擊“編輯欄”左側的“插入函數”按鈕。打開“插入函數”對話框，在“搜索函數”文本框中輸入關鍵詞，單擊“轉到”按鈕，即可顯示“推薦”的函數列表，在“選擇函數”列表框中選擇需要的函數，單擊“確定”按鈕。在打開的“函數參數”對話框中設置參數即可。3.2.3

函數的輸入方法自動計算出“總金額”數據用戶可以使用“自動求和”功能，快速對數據進行求和，下面將介紹具體的操作方法。（1）選擇D2:D6單元格區域，在“公式”選項卡中單擊“自動求和”下拉按鈕，從列表中選擇“求和”選項。（2）即可快速計算出“總金額”。目錄Contents3.1

初識公式3.2

理解Excel函數3.7

疑難解答3.3

審核和檢查公式3.4

常用數學函數3.6

實戰演練：制作銷售數據統計表3.5

常用統計函數3.3.1

追蹤單元格追蹤單元格作用是跟蹤選定單元格內公式的引用或從屬單元格。分為：追蹤引用單元格和追蹤從屬單元格。1.追蹤引用單元格追蹤引用單元格用于指示哪些單元格會影響當前所選單元格的值。選擇單元格，在“公式”選項卡中單擊“追蹤引用單元格”按鈕，即可出現藍色箭頭，指明當前所選單元格引用了哪些單元格。此外，選擇單元格，按【Ctrl+[】組合鍵，可以定位到所選單元格的引用單元格。3.3.1

追蹤單元格2.追蹤從屬單元格追蹤從屬單元格用于指示哪些單元格受當前所選單元格的值影響。選擇單元格，單擊“追蹤從屬單元格”按鈕，藍色箭頭指向受當前所選單元格影響的單元格。此外，選擇單元格，按【Ctrl+］】組合鍵，可以定位到所選單元格的從屬單元格。3.3.2

顯示公式本身當輸入完公式并結束編輯后，并未得到計算結果而是顯示公式本身。以下是兩種可能的原因和解決方法。1.檢查是否啟用了“顯示公式”模式如果用戶在“公式”選項卡中單擊“顯示公式”按鈕，則會將表格中的公式顯示出來。解決方法：在“公式”選項卡中再次單擊“顯示公式”按鈕，取消其選中狀態。3.3.2

顯示公式本身2.檢查是否單元格設置了“文本”格式如果未開啟“顯示公式”模式，單元格中仍然是顯示公式本身而不是計算結果，則可能是由于單元格設置了“文本”格式后再輸入公式。解決方法：選擇公式所在單元格，按【Ctrl+1】組合鍵，打開“設置單元格格式”對話框，在“數字”選項卡中選擇“常規”選項，單擊“確定”按鈕，重新激活單元格中的公式，并結束編輯。3.3.3

自動檢查錯誤公式當公式的結果返回錯誤值時，應該及時查找錯誤原因，并修改公式以解決問題。Excel提供了后臺檢查錯誤的功能，用戶只需要單擊“文件”按鈕，選擇“選項”選項，打開“Excel選項”對話框，選擇“公式”選項卡，在“錯誤檢查”區域勾選“允許后臺錯誤檢查”復選框，并在“錯誤檢查規則”區域勾選相應的規則選項即可。3.3.3

自動檢查錯誤公式當單元格中的公式或值出現與“錯誤檢查規則”選項中相符的情況時，單元格左上角會顯示綠色小三角。選擇該單元格，在其左側會出現感嘆號形狀的“錯誤指示器”。單擊“錯誤指示器”下拉按鈕，在列表中可以查看公式錯誤的原因，列表中第一個選項表示錯誤原因。3.3.3

自動檢查錯誤公式檢查并修改錯誤公式當表格中出現錯誤值時，用戶可以使用“錯誤檢查”功能，手動檢查錯誤公式，下面將介紹具體的操作方法。3.3.3

自動檢查錯誤公式（1）選擇C2:C6單元格區域。單擊區域左上角的“

”按鈕，從列表中選擇“轉換為數字”選項。（2）C7單元格中，即可顯示出正確的求和數量。（3）在“公式”選項卡中單擊“錯誤檢查”按鈕，打開“錯誤檢查”對話框，在該對話框中顯示出錯的單元格，以及出錯原因，用戶在對話框的右側可以進行“關于此錯誤的幫助”、“顯示計算步驟”、“忽略錯誤”、“在編輯欄中編輯”操作，這里單擊“在編輯欄中編輯”按鈕。（4）在“編輯欄”中修改公式后，單擊“繼續”按鈕，繼續檢查其他錯誤公式，檢查并修改完成后會彈出一個提示對話框，直接單擊“確定”按鈕即可。目錄Contents3.1

初識公式3.2

理解Excel函數3.7

疑難解答3.3

審核和檢查公式3.4

常用數學函數3.6

實戰演練：制作銷售數據統計表3.5

常用統計函數3.4.1

SUM函數SUM函數用于對單元格區域中所有數值求和。其語法格式為：參數說明：number1：必需參數。表示要求和的第1個數字，可以是直接輸入的數字、單元格引用或數組。number2：可選參數。表示要求和的第2-255個數字，可以是直接輸入的數字、單元格引用或數組。3.4.1

SUM函數計算“銷售總額”數據用戶可以使用SUM函數，計算“銷售總額”，下面將介紹具體的操作方法。（1）選擇G2單元格，輸入公式“=SUM(E2:E11)”。（2）按【Enter】鍵確認，即可計算出銷售總額。3.4.2

SUMIF函數SUMIF函數用于根據指定條件對若干單元格求和。其語法格式為：參數說明：Range：為條件區域，用于條件判斷的單元格區域。Criteria：是求和條件，由數字、邏輯表達式等組成的判定條件。Sum_range：為實際求和區域，需要求和的單元格、區域或引用。3.4.2

SUMIF函數計算出“筆記本”的銷售總額用戶可以使用SUMIF函數，計算“筆記本”的銷售總額，下面將介紹具體的操作方法。（1）選擇H2單元格，輸入公式“=SUMIF(B2:B11,G2,E2:E11)”。（2）按【Enter】鍵確認，即可計算出“筆記本”的銷售總額。3.4.3

SUMIFS函數SUMIFS函數用于解決多條件求和問題。其語法格式為：sum_range:是求和的實際單元格，即求和區域。criteria_range1:是要為特定條件計算的單元格區域，即條件1區域。criteria1:是數字、表達式或文本形式的條件，即條件1。它定義了單元格求和的范圍。3.4.3

SUMIFS函數計算2月掃描儀的入庫數量用戶可以使用SUMIFS函數，計算2月掃描儀的入庫數量，下面將介紹具體的操作方法。（1）選擇C13單元格，輸入公式“=SUMIFS(D2:D10,A2:A10,"2月",C2:C10,"掃描儀")”。（2）按【Enter】鍵確認，即可計算出2月掃描儀的入庫數量。3.4.4

RAND函數RAND函數用于返回大于等于0及小于1的均勻分布隨機數。其語法格式為：3.4.4

RAND函數隨機安排值班表排序“值班人員”要想隨機安排值班人員的排班順序，可以使用RAND函數生成隨機小數作為輔助列，然后進行排序。（1）選擇B2單元格，輸入公式“=RAND()”,按【Enter】鍵確認，計算出結果。（2）將公式向下填充，然后對“隨機數”進行“升序”排序。目錄Contents3.1

初識公式3.2

理解Excel函數3.7

疑難解答3.3

審核和檢查公式3.4

常用數學函數3.6

實戰演練：制作銷售數據統計表3.5

常用統計函數3.5.1

COUNTIF函數COUNTIF函數用于求滿足給定條件的數據個數。其語法格式為：Range為需要計算其中滿足條件的單元格數目的單元格區域。Criteria為確定哪些單元格將被計算在內的條件，其形式可以為數字、表達式或文本。3.5.1

COUNTIF函數統計銷售業績大于3萬的人數用戶可以使用COUNTIF函數，統計銷售業績大于3萬的人數，下面將介紹具體的操作方法。（1）選擇E2單元格，輸入公式“=COUNTIF(C2:C8,">30000")”。（2）按【Enter】鍵確認，即可統計出銷售業績大于3萬的人數。3.5.2

MAX函數MAX函數用于返回一組值中的最大值。其語法格式為：參數說明：number1,number2,...為指定需求最大值的數值或者數值所在的單元格。如果參數為錯誤值或不能轉換成數字的文本，將產生錯誤。如果參數為數組或引用，則只有數組或引用中的數字將被計算。數組或引用中的空白單元格、邏輯值或文本將被忽略。3.5.2

MAX函數統計最大銷售業績用戶可以使用MAX函數，統計最大銷售業績，下面將介紹具體的操作方法。（1）選擇E2單元格，輸入公式“=MAX(C2:C8)”。（2）按【Enter】鍵確認，即可統計出最大銷售業績。3.5.3

MIN函數MIN函數用于返回一組值中的最小值。其語法格式為：參數說明：number1,number2,...是要從中找出最小值的1到255個數字參數。參數可以是數字、空白單元格、邏輯值或表示數值的文字串。如果參數中有錯誤值或無法轉換成數值的文字時，將引起錯誤。如果參數是數組或引用，則函數MIN僅使用其中的數字、數組或引用中的空白單元格，邏輯值、文字或錯誤值將忽略。3.5.3

MIN函數統計最小銷售業績用戶可以使用MIN函數，統計最小銷售業績，下面將介紹具體的操作方法。（1）選擇E2單元格，輸入公式“=MIN(C2:C8)”。（2）按【Enter】鍵確認，即可統計出最小銷售業績。3.5.4

RANK函數RANK函數用于返回一個數值在一組數值中的排位。其語法格式為：參數說明：number：為指定需找到排位的數值，或數值所在的單元格。ref：為指定包含數值的單元格區域或區域名稱。ref區域內的空白單元格或文本、邏輯值將被忽略。order：指明排位方式。升序時指定為1，降序時指定為0。如果省略order，則用降序排位。3.5.4

RANK函數對員工銷售業績進行排名用戶可使用RANK函數，對員工銷售業績進行排名，下面將介紹具體的操作方法。（1）選擇D2單元格，輸入公式“=RANK(C2,$C$2:$C$8,0)”。（2）按【Enter】鍵確認，即可計算出結果，并將公式向下填充，統計出銷售業績排名。目錄Contents3.1

初識公式3.2

理解Excel函數3.7

疑難解答3.3

審核和檢查公式3.4

常用數學函數3.6

實戰演練：制作銷售數據統計表3.5

常用統計函數實戰演練：制作銷售數據統計表本章實戰演練將制作銷售數據統計表，運用前面所學知識，以熟練掌握和鞏固公式與函數的應用。1.案例效果本章實戰演練為制作銷售數據統計表。實戰演練：制作銷售數據統計表2.操作思路掌握SUM函數、COUNT函數、FREQUENCY函數的使用方法，下面將進行簡單介紹。（1）計算“銷售額”。在A2單元格中輸入公式“=SUM(J5:J17)”。（2）計算“訂單量”。在D2單元格中輸入公式“=COUNT(0/FREQUENCY(A5:A17,A5:A17))”。（3）計算“下單人數”。在G2單元格中輸入公式“=COUNT(0/FREQUENCY(B