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

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

算術(shù)運算符

文本運算符

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

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

輸入“=”等號。③

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

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

輸入“=”等號。③

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

①

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

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

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

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

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

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

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

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

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

平時比例+期中成績

期中比例+期末成績

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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