Excel 2016高級應用案例教程課件

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

初識公式3.2

理解Excel函數(shù)3.7

疑難解答3.3

審核和檢查公式3.4

常用數(shù)學函數(shù)3.6

實戰(zhàn)演練：制作銷售數(shù)據(jù)統(tǒng)計表3.5

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

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

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

復制和填充公式當表格中多個單元格所需公式的計算規(guī)則相同時，可以使用復制和填充功能進行計算。1.復制公式選擇公式所在單元格，按【Ctrl+C】組合鍵進行復制。然后選擇目標單元格區(qū)域，按【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”。當向右復制公式時，將依次變?yōu)椋?B1、=C1、=D1等。3.1.5

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

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

初識公式3.2

理解Excel函數(shù)3.7

疑難解答3.3

審核和檢查公式3.4

常用數(shù)學函數(shù)3.6

實戰(zhàn)演練：制作銷售數(shù)據(jù)統(tǒng)計表3.5

常用統(tǒng)計函數(shù)3.2.1

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

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

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

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

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

初識公式3.2

理解Excel函數(shù)3.7

疑難解答3.3

審核和檢查公式3.4

常用數(shù)學函數(shù)3.6

實戰(zhàn)演練：制作銷售數(shù)據(jù)統(tǒng)計表3.5

常用統(tǒng)計函數(shù)3.3.1

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

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

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

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

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

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

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

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

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

初識公式3.2

理解Excel函數(shù)3.7

疑難解答3.3

審核和檢查公式3.4

常用數(shù)學函數(shù)3.6

實戰(zhàn)演練：制作銷售數(shù)據(jù)統(tǒng)計表3.5

常用統(tǒng)計函數(shù)3.4.1

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

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

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

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

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

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

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

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

初識公式3.2

理解Excel函數(shù)3.7

疑難解答3.3

審核和檢查公式3.4

常用數(shù)學函數(shù)3.6

實戰(zhàn)演練：制作銷售數(shù)據(jù)統(tǒng)計表3.5

常用統(tǒng)計函數(shù)3.5.1

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

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

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

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

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

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

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

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

初識公式3.2

理解Excel函數(shù)3.7

疑難解答3.3

審核和檢查公式3.4

常用數(shù)學函數(shù)3.6

實戰(zhàn)演練：制作銷售數(shù)據(jù)統(tǒng)計表3.5

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