Excel 2016數(shù)據(jù)處理與分析應(yīng)用教程 課件 第3章 公式

第3章公式3.1公式的概念公式是對(duì)工作表中的數(shù)據(jù)進(jìn)行計(jì)算的有效操作。公式以等號(hào)（=）開頭，后面跟一個(gè)表達(dá)式。例如，“=2*B2+5”3.1.1公式的組成公式中的表達(dá)式是由常量、單元格引用、函數(shù)、運(yùn)算符和括號(hào)構(gòu)成的。常量：直接輸入到公式中的數(shù)字或文本，例如，數(shù)值123或文本“計(jì)算機(jī)”。單元格引用：引用某一個(gè)單元格或單元格區(qū)域中的數(shù)據(jù)，例如，B2或A2:D5。函數(shù)：系統(tǒng)提供的函數(shù)，例如，求和函數(shù)SUM(C3:E3)。運(yùn)算符：連接公式中常量、單元格引用、函數(shù)的特定計(jì)算符號(hào)。括號(hào)：控制公式中的計(jì)算順序。類型運(yùn)算符功能應(yīng)用舉例引用運(yùn)算符區(qū)域運(yùn)算符（：）、聯(lián)合運(yùn)算符（，）用于對(duì)單元各區(qū)域進(jìn)行合并計(jì)算。將多個(gè)引用合并為一個(gè)引用=SUM(A1:A2,D1:D2)實(shí)現(xiàn)計(jì)算A1+A2+D1+D2算術(shù)運(yùn)算符+（加）、﹣（減）、*（乘）、/（除）、%（百分比）、^（乘方）完成數(shù)學(xué)運(yùn)算，運(yùn)算結(jié)果為數(shù)值=2^3+6/2結(jié)果是11文本運(yùn)算符&（文本連接）可以連接一個(gè)或多個(gè)文本字符串=“Hello”&“World”結(jié)果是“HelloWorld”比較運(yùn)算符=（等于）、>（大于）、<（小于）、>=（大于或等于）、<=（小于或等于）、<>（不等于）比較兩個(gè)值之間的大小關(guān)系，運(yùn)算結(jié)果為邏輯值TRUE或FALSE=5＞3結(jié)果是TRUE運(yùn)算符的優(yōu)先級(jí)為：引用運(yùn)算符

算術(shù)運(yùn)算符

文本運(yùn)算符

比較運(yùn)算符例如:公式1：=5>3+1按照運(yùn)算符的優(yōu)先級(jí)順序，先計(jì)算3+1，結(jié)果為4；然后再計(jì)算5>4，結(jié)果為TRUE。公式2：=(5>3)+1先計(jì)算括號(hào)內(nèi)的5>3，結(jié)果為TRUE（即1）;然后再計(jì)算1+1，結(jié)果為2。3.1.2單元格引用1.相對(duì)引用單元格相對(duì)引用的格式為：列號(hào)行號(hào)。例如，A1。如果公式所在單元格的位置改變，引用也隨之改變。如果多行或多列復(fù)制公式，引用會(huì)自動(dòng)調(diào)整。默認(rèn)情況下，公式中使用相對(duì)引用。2.絕對(duì)引用單元格絕對(duì)引用的格式為：$列號(hào)$行號(hào)。例如，$A$1。絕對(duì)引用總是引用指定位置的單元格。如果公式所在單元格的位置改變，絕對(duì)引用保持不變。如果多行或多列復(fù)制公式，絕對(duì)引用將不作調(diào)整。3.混合引用單元格混合引用的格式為：$列號(hào)行號(hào)（絕對(duì)列和相對(duì)行）或者是列號(hào)$行號(hào)（相對(duì)列和絕對(duì)行）。例如，$A1、A$1。如果公式所在單元格的位置改變，則相對(duì)引用部分改變，而絕對(duì)引用部分不變。如果多行或多列復(fù)制公式，相對(duì)引用自動(dòng)調(diào)整，而絕對(duì)引用不作調(diào)整。4.外部引用（1）引用不同工作表中的單元格如果需要引用同一工作簿中其他工作表上的單元格的數(shù)據(jù)，需要在單元格前加上工作表的名稱和感嘆號(hào)“！”。引用的格式為：=工作表名稱!單元格引用。例如，需要引用工作表名為“sheet1”中A2單元格，則輸入的公式為“=sheet1!$A$2”（2）引用不同工作簿中的單元格如果需要引用不同工作簿中某一工作表的單元格，需要包含工作簿的名稱。引用的格式為：=[工作簿名稱]工作表名稱!單元格引用。5.三維引用如果需要引用同一工作簿中多個(gè)工作表上的同一個(gè)單元格的數(shù)據(jù)，可以采用三維引用的方式。使用引用運(yùn)算符“：”指定工作表的范圍。三維引用的格式為：工作表名稱1:工作表名稱n!單元格引用。例如，Sheet1!:Sheet3!B2、Sheet2!:Sheet5!B2:G6。3.2公式編輯3.2.1手動(dòng)輸入公式手動(dòng)輸入是將公式中的全部?jī)?nèi)容通過(guò)鍵盤輸入的方法，手動(dòng)輸入公式的操作步驟如下：①選擇需要輸入公式的單元格。②輸入“=”等號(hào)。③輸入公式表達(dá)式。公式表達(dá)式通常由單元格引用、常數(shù)、函數(shù)、運(yùn)算符和括號(hào)組成。④公式輸入完畢后，按回車鍵完成輸入；或者單擊編輯欄上的輸入按鈕完成輸入。公式輸入結(jié)束后，包含公式的單元格內(nèi)會(huì)顯示公式的計(jì)算結(jié)果；而公式本身則顯示在編輯欄中。當(dāng)組成公式的單元格的內(nèi)容發(fā)生變化時(shí)，公式的計(jì)算結(jié)果也會(huì)隨之自動(dòng)變化。提示公式必須以“=”開始，否則公式將被識(shí)別為一個(gè)字符串。單元格中顯示公式計(jì)算結(jié)果，編輯欄中顯示公式表達(dá)式。3.2.2單擊單元格輸入用戶也可以通過(guò)單擊單元格來(lái)快速輸入公式，操作步驟如下：①

選擇需要輸入公式的單元格。②

輸入“=”等號(hào)。③

輸入公式表達(dá)式。當(dāng)需要輸入單元格引用時(shí)，鼠標(biāo)單擊公式中單元格地址對(duì)應(yīng)的單元格，則該單元格地址自動(dòng)寫入到公式表達(dá)式中。公式表達(dá)式中的常數(shù)和運(yùn)算符需要手動(dòng)輸入。④公式輸入完畢后，按回車鍵；或者單擊編輯欄上的輸入按鈕完成輸入?！纠?-6】計(jì)算圓面積和周長(zhǎng)，計(jì)算結(jié)果保留2位小數(shù)。根據(jù)B2單元格的圓半徑值，計(jì)算B3單元格的圓面積值和B4單元格的圓周長(zhǎng)值。①在B3單元格中輸入公式“=3.14*B2^2”，公式表示計(jì)算圓的面積，其中B2^2表示半徑的平方。②在B4單元格中輸入公式“=3.14*B2*2”，公式表示計(jì)算圓的周長(zhǎng)。③將B3和B4單元格格式的小數(shù)位數(shù)設(shè)置為“2”。④改變B2單元格的值，公式的計(jì)算結(jié)果自動(dòng)更新?！纠?-7】計(jì)算總成績(jī)①

單擊需要輸入公式的H2單元格。②

輸入“=”等號(hào)。③

輸入公式表達(dá)式。在H2單元格中直接輸入公式“=E2+F2+G2”后按回車鍵。H2單元格顯示公式的計(jì)算結(jié)果，即73+68+87=228如果組成公式的任意單元格中內(nèi)容發(fā)生變化例如，E2單元格的數(shù)值變成93，則H2單元格的總成績(jī)自動(dòng)隨之改變?yōu)?48，這就是公式計(jì)算的便捷之處。【例3-8】在學(xué)生工作表中，添加一列“單位”。該列的內(nèi)容由“學(xué)院”、“專業(yè)”和“班級(jí)”三列的內(nèi)容組成。

①

單擊需要輸入公式的G2單元格。②

輸入公式表達(dá)式。在G2單元格中輸入等號(hào)“=”；然后單擊A2單元格，該單元格自動(dòng)添加到公式中；輸入文本連接符“&”；然后單擊B2單元格，該單元格自動(dòng)添加到公式中；輸入文本連接符“&”；然后單擊C2單元格，該單元格自動(dòng)添加到公式，生成的公式為“=A2&B2&C2”，按回車鍵完成公式的輸入。③

在G3單元格中直接輸入公式“=A3&B3&C3”。3.3公式復(fù)制多數(shù)情況下某列或某行會(huì)采用相同的計(jì)算方法，不需要逐列或逐行輸入公式，可以通過(guò)復(fù)制公式的方法來(lái)快速完成公式的輸入。3.3.1復(fù)制公式可以使用自動(dòng)填充的方法復(fù)制公式，也可以使用“復(fù)制與粘貼”的方法復(fù)制公式。1.自動(dòng)填充的方法復(fù)制公式①

選擇需要復(fù)制公式的單元格。②

將鼠標(biāo)放置到選中單元格右下方填充柄處，當(dāng)鼠標(biāo)指針變成十字形狀時(shí)，按下鼠標(biāo)拖拽至所需要的單元格處釋放鼠標(biāo)。當(dāng)數(shù)據(jù)行較多時(shí)，可以雙擊填充柄將自動(dòng)復(fù)制公式至最后一行。2.“復(fù)制與粘貼”的方法復(fù)制公式通過(guò)“復(fù)制與粘貼”操作可以復(fù)制公式。在復(fù)制公式時(shí)，單元格引用會(huì)根據(jù)不同的引用類型而變化。①

選擇需要復(fù)制公式的單元格。②

在“開始”選項(xiàng)卡的“剪貼板”選項(xiàng)組中，單擊“復(fù)制”按鈕。③

選擇目標(biāo)單元格，單擊“粘貼”按鈕即可復(fù)制公式及格式.3.3.2顯示公式默認(rèn)情況下，含有公式的單元格中顯示的數(shù)據(jù)是公式的計(jì)算結(jié)果。如果需要顯示公式可以按照如下步驟進(jìn)行操作：①

在“公式”選項(xiàng)卡的“公式審核”選項(xiàng)組中，單擊“顯示公式”按鈕，則顯示出公式組成?！纠?-9】計(jì)算學(xué)生高等數(shù)學(xué)成績(jī)。分析：高等數(shù)學(xué)課程的成績(jī)是由平時(shí)成績(jī)、期中成績(jī)和期末成績(jī)?nèi)糠謽?gòu)成的，每部分所占比例不同，成績(jī)=平時(shí)成績(jī)

平時(shí)比例+期中成績(jī)

期中比例+期末成績(jī)

期末比例。需要使用單元格的絕對(duì)引用單元格F4的計(jì)算成績(jī)公式為“=D4*$B$2+E4*$D$2+F4*$F$2”對(duì)單元格F5~F9進(jìn)行公式復(fù)制后，絕對(duì)引用的單元格$B$2（20%）、$D$2（30%）、$F$2（50%）不會(huì)改變3.4名稱和數(shù)組單元格默認(rèn)情況下是用列號(hào)和行號(hào)來(lái)命名的，如B5單元格。用戶還可以對(duì)單元格或單元格區(qū)域重新命名，并在其后的公式中使用名稱進(jìn)行計(jì)算，使得計(jì)算公式更加易于理解。名稱可以是字母、漢字、數(shù)字和特殊字符（下劃線、圓點(diǎn)、反斜線、問(wèn)號(hào)）組成，但是不能以數(shù)字開頭，也不能與單元格地址相同（如B3）。3.4.1名稱定義1.使用名稱框定義名稱①

選中單元格或單元格區(qū)域，例如，E2:E7。②

將鼠標(biāo)定位到“名稱框”中，輸入自定義的名稱“高等數(shù)學(xué)”，然后按回車鍵完成名稱的定義。2.使用“定義名稱”命令創(chuàng)建名稱①

單擊“公式”選項(xiàng)卡中“定義的名稱”選項(xiàng)組中的“定義名稱”按鈕。②

在打開的“新建名稱”對(duì)話框中，在“引用位置”框中設(shè)置單元格區(qū)域“=Sheet1!$F$2:$F$7”；在“名稱”框中輸入名稱“英語(yǔ)”3.使用名稱管理器新建名稱①

單擊“公式”選項(xiàng)卡中“定義的名稱”選項(xiàng)組中的“名稱管理器”按鈕。②

在打開的“名稱管理器”對(duì)話框中，單擊“新建”按鈕。③

在打開的“新建名稱”對(duì)話框中，設(shè)置單元格區(qū)域的名稱。3.4.2在公式中使用名稱定義名稱后就可以直接在公式中使用名稱了，操作步驟如下：①

選中輸入公式的單元格。②

單擊“公式”選項(xiàng)卡中“定義的名稱”選項(xiàng)組中的“用于公式”按鈕，將顯示出已經(jīng)定義的名稱。③

逐個(gè)選擇需要的名稱，手動(dòng)輸入運(yùn)算符“+”，按回車鍵完成公式的輸入。也可以直接在公式中輸入名稱和運(yùn)算符。其中G2單元格中輸入公式為“=高等數(shù)學(xué)+物理+英語(yǔ)”，比較直觀反應(yīng)了總成績(jī)的組成?！纠?-10】使用名稱計(jì)算“總成績(jī)”。①選中E2:E7。單擊鼠標(biāo)右鍵，在快捷菜單中選擇“定義名稱”。②在打開的“新建名稱”對(duì)話框中，設(shè)置單元格區(qū)域的名稱為“高等數(shù)學(xué)”。③選中F2:F7重復(fù)①~②，名稱為“英語(yǔ)”。④選中G2:G7重復(fù)①~②，名稱為“物理”。⑤在H2單元格中輸入公式“=高等數(shù)學(xué)+物理+英語(yǔ)”，然后單擊編輯欄上的輸入按鈕完成輸入。⑥雙擊H2單元格的十字填充柄進(jìn)行公式的復(fù)制。3.4.3名稱編輯和刪除用戶可以對(duì)已經(jīng)定義名稱的引用范圍進(jìn)行修改，也可以刪除不需要的名稱。編輯名稱①

單擊“公式”選項(xiàng)卡中“定義的名稱”選項(xiàng)組中的“名稱管理器”按鈕。②

在打開的“名稱管理器”對(duì)話框中，選中需要編輯的名稱，單擊“編輯”按鈕，重新設(shè)置單元格區(qū)域的名稱。刪除名稱①

單擊“公式”選項(xiàng)卡中“定義的名稱”選項(xiàng)組中的“名稱管理器”按鈕。②

在打開的“名稱管理器”對(duì)話框中，選中需要?jiǎng)h除的名稱，單擊“刪除”按鈕，在彈出的確認(rèn)刪除對(duì)話框中單擊“確定”按鈕即可完成名稱的刪除。3.4.4數(shù)組公式在Excel函數(shù)和公式中，數(shù)組是指一行、一列或多行多列的一組數(shù)據(jù)元素的集合。1.數(shù)組的類型常見的數(shù)組可以分為常數(shù)數(shù)組、區(qū)域數(shù)組等。(1)常數(shù)數(shù)組常數(shù)數(shù)組是指包含在大括號(hào)“{}”內(nèi)的常量數(shù)值。如果是文本類常量必須用英文雙引號(hào)括起來(lái)例如，{0,60,70,80,90}、{"不及格","及格","中等","良好","優(yōu)秀"}。(2)區(qū)域數(shù)組區(qū)域數(shù)組就是單元格區(qū)域引用形式。例如，函數(shù)AVERAGE(F2:F5)中的F2:F5是區(qū)域數(shù)組2.數(shù)組公式數(shù)組公式需要按下【Ctrl+Shift+Enter】組合鍵來(lái)完成編輯，系統(tǒng)會(huì)自動(dòng)在數(shù)組公式的首尾添加大括號(hào)“{}”?？梢允褂脭?shù)組公式返回一組運(yùn)算結(jié)果。【例3-11】利用數(shù)組計(jì)算“總成績(jī)”。選中H2:H7單元格區(qū)域，在編輯欄中輸入公式“=E2:E7+F2:F7+G2:G7”，然后按下【Ctrl+Shift+Enter】組合鍵，形成數(shù)組公式為：“{=E2:E7+F2:F7+G2:G7}”，此公式對(duì)每位學(xué)生的三門課程成績(jī)求和，同時(shí)計(jì)算出6位學(xué)生的總成績(jī)。提示數(shù)組中的大括號(hào)“{}”應(yīng)在編輯完成后按【Ctrl+Shift+Enter】組合鍵自動(dòng)生成如果手工輸入，系統(tǒng)將識(shí)別為文本字符，無(wú)法正確計(jì)算。3.5公式審核公式審核就是檢查公式與單元格之間的關(guān)系。當(dāng)數(shù)據(jù)關(guān)系復(fù)雜時(shí)，某個(gè)單元格中公式的計(jì)算既可能受到其他單元格數(shù)值的影響，又可能會(huì)影響到其他單元格的數(shù)值。利用公式審核功能，可以非常方便地檢查公式的組成，分析影響公式計(jì)算結(jié)果的因素，以及公式計(jì)算結(jié)果是否會(huì)影響其他單元格。3.5.1更正公式如果創(chuàng)建公式時(shí)輸入錯(cuò)誤，會(huì)在單元格內(nèi)顯示相應(yīng)的錯(cuò)誤信息，此時(shí)需要了解產(chǎn)生錯(cuò)誤的原因并加以更正。類型含義更正方法#####列寬不夠增加列寬或縮小字號(hào)。#DIV/0!除數(shù)為0檢查輸入公式中除數(shù)是否為0或引用了空白單元格或值為0的單元格作為除數(shù)。#N/A數(shù)值對(duì)函數(shù)或公式不可用檢查公式中引用的單元格的數(shù)據(jù)，并輸入正確的內(nèi)容。#NAME?無(wú)法識(shí)別公式中的文本檢查輸入文本時(shí)是否使用雙引號(hào)；區(qū)域引用中是否缺少了冒號(hào)：。#NULL!區(qū)域運(yùn)算符不正確使用區(qū)域運(yùn)算符（：）引用連續(xù)的單元格區(qū)域；使用聯(lián)合運(yùn)算符（,）引用不相交的兩個(gè)區(qū)域。#NUM!無(wú)效數(shù)據(jù)檢查數(shù)字是否超出限定范圍，或函數(shù)中的參數(shù)是否正確。#REF!單元格引用無(wú)效檢查引用的單元格是否已被刪除。#