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

初識(shí)公式3.2

理解Excel函數(shù)3.7

疑難解答3.3

審核和檢查公式3.4

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

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

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

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

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

復(fù)制和填充公式當(dāng)表格中多個(gè)單元格所需公式的計(jì)算規(guī)則相同時(shí)，可以使用復(fù)制和填充功能進(jìn)行計(jì)算。1.復(fù)制公式選擇公式所在單元格，按【Ctrl+C】組合鍵進(jìn)行復(fù)制。然后選擇目標(biāo)單元格區(qū)域，按【Ctrl+V】組合鍵，粘貼公式。3.1.3

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

公式中的常見錯(cuò)誤使用公式進(jìn)行計(jì)算時(shí)，可能會(huì)因?yàn)槟撤N原因而無法得到或顯示正確結(jié)果，在單元格中返回錯(cuò)誤值信息。3.1.5

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

單元格的3種引用1.相對引用當(dāng)復(fù)制公式到其他單元格時(shí)，Excel保持從屬單元格與引用單元格的相對位置不變，稱為相對引用。例如，在B1單元格輸入公式“=A1”。當(dāng)向右復(fù)制公式時(shí)，將依次變?yōu)椋?B1、=C1、=D1等。3.1.5

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

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

初識(shí)公式3.2

理解Excel函數(shù)3.7

疑難解答3.3

審核和檢查公式3.4

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

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

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

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

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

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

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

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

初識(shí)公式3.2

理解Excel函數(shù)3.7

疑難解答3.3

審核和檢查公式3.4

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

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

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

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

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

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

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

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

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

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

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

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

初識(shí)公式3.2

理解Excel函數(shù)3.7

疑難解答3.3

審核和檢查公式3.4

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

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

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

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

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

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

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

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

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

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

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

初識(shí)公式3.2

理解Excel函數(shù)3.7

疑難解答3.3

審核和檢查公式3.4

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

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

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

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

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

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

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

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

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

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

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

初識(shí)公式3.2

理解Excel函數(shù)3.7

疑難解答3.3

審核和檢查公式3.4

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

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

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