4.3 公式與函數(shù)電子課件

公式與函數(shù)主講人：****公式與函數(shù)2函數(shù)的應用1公式的應用3常用函數(shù)的應用公式的應用公式的應用了解公式公式是可以進行執(zhí)行計算、返回信息、操作其他單元格的內(nèi)容、測試條件等操作的方程式。公式始終以等號（=）開頭。公式可以包含函數(shù)、引用、運算符和常量的部分內(nèi)容或全部內(nèi)容，如圖所示。①函數(shù)：PI()函數(shù)的返回值為π。②引用：A2返回單元格A2中的值。③常量：直接輸入公式中的數(shù)字或文本值，如“2”。④運算符：^（脫字號）運算符表示乘方，而*（星號）運算符表示乘號。在公式中使用常量和運算符常量是一個不被計算的值，其值始終不變。例如，日期2023-9-10、數(shù)字350及文本“學生姓名”都是常量。運算符用于指定要對公式中的元素執(zhí)行的計算類型。計算時有一個默認的次序（遵循一般的數(shù)學規(guī)則），但可以使用括號更改計算次序。1．運算符類型運算符分為4種類型：算術(shù)、比較、文本連接和引用。（1）算術(shù)運算符。若要進行基本的數(shù)學運算（如加法、減法、乘法或除法）、合并數(shù)字及生成數(shù)值結(jié)果，可以使用表4-2所示的算術(shù)運算符。在公式中使用常量和運算符算術(shù)運算符含

義示

例+（加號）加法4+6–（減號）減法

負數(shù)7?1

?5*（星號）乘法3*8/（正斜杠）除法9/3%（百分號）百分比50%^（脫字號）乘方8^2算術(shù)運算符在公式中使用常量和運算符（2）比較運算符?？梢允褂孟卤硭镜倪\算符比較兩個值。當使用這些運算符比較兩個值時，結(jié)果為邏輯值TRUE或FALSE。比較運算符含

義示

例=（等號）等于A1=B1>（大于號）大于A1>B1<（小于號）小于A1<B1>=（大于等于號）大于或等于A1>=B1<=（小于等于號）小于或等于A1<=B1<>（不等號）不等于A1<>B1在公式中使用常量和運算符（3）文本連接運算符?？梢允褂门c號（&）連接一個或多個文本字符串，以生成一段文本，如下表示。文本運算符含

義示

例&（與號）將兩個值連接（或串聯(lián)）起來產(chǎn)生一個連續(xù)的文本值“Win”&“word”的結(jié)果為“Winword”在公式中使用常量和運算符（4）引用運算符?？梢允褂孟卤硭镜倪\算符對單元格區(qū)域進行合并計算。引用運算符含

義示

例冒號（:）區(qū)域運算符，生成一個對兩個引用之間所有單元格的引用（包括這兩個引用）A5:B10逗號（,）聯(lián)合運算符，將多個引用合并為一個引用SUM(A5:B10,D5:D15)空格交集運算符，生成一個對兩個引用中共有單元格的引用B7:D7C6:C8在公式中使用常量和運算符2．Excel執(zhí)行公式運算的次序在某些情況下，執(zhí)行計算的次序會影響公式的返回值，因此，了解如何確定計算次序及如何更改次序以獲得所需結(jié)果的技巧非常重要。（1）計算次序。公式按特定次序計算值。Excel中的公式始終以等號（=）開頭。Excel會將等號后面的字符解釋為公式。等號后面是要計算的元素（即操作數(shù)），如常量或單元格引用，它們由運算符分隔。Excel按照公式中每個運算符的特定次序從左到右依次計算。（2）運算符優(yōu)先級。如果一個公式中有若干個運算符，則Excel將按下表所示的次序進行計算。如果一個公式中的若干個運算符具有相同的優(yōu)先順序（例如既有乘號又有除號），則Excel將從左到右依次進行計算。在公式中使用常量和運算符運

算

符說

明冒號（:）；單個空格；逗號（,）引用運算符–負數(shù)（如–1）%百分比^乘方*和/乘和除+和–加和減&連接兩個文本字符串（串連）=

<>

<=

>=

<>比較運算運算符優(yōu)先級在公式中使用常量和運算符（3）使用括號。若要更改求值的次序，可以將公式中要先計算的部分用括號括起來。例如，4+5*8的結(jié)果是44，因為Excel先進行乘法運算，后進行加法運算，所以該公式先將5與8相乘，再將結(jié)果與4相加，如左圖所示。但是，如果用括號對該語法進行更改，如(4+5)*8，則Excel會先將4與5相加，再用結(jié)果乘以8得到72，如右圖所示。單元格的引用單元格的引用是指對工作表中的單元格或單元格區(qū)域進行引用，并指明公式中所使用的值或數(shù)據(jù)的位置。在Excel中可以引用同一張工作表中的單元格、其他工作表中的單元格及其他工作簿中的單元格，引用不同來源的單元格的格式不一樣，但其結(jié)構(gòu)類似。不同來源單元格的引用格式。（1）同一工作表中的單元格引用：如果B1單元格要引用A1單元格中的值，則在B1單元格中輸入“=A1”即可。（2）不同工作表中的單元格引用：如果B1單元格要引用Sheet2工作表A1單元格中的值，則在B1單元格中輸入“=Sheet2!A1”即可。單元格的引用（3）不同工作簿中的單元格引用：引用其他工作簿中的工作表的單元格數(shù)據(jù)的方法與引用其他工作表的單元格數(shù)據(jù)類似，一般格式為“=工作簿存儲地址[工作簿名稱]工作表名稱!單元格地址”。（4）引用單元格名稱：如果為引用的單元格定義了名稱，則可以直接引用定義的名稱，如“=總分-最高分-最低分”表示用名為“總分”的單元格的值減去名為“最高分”和“最低分”的單元格的值。（5）引用單元格區(qū)域名稱：如果為單元格區(qū)域定義了名稱，則可以引用對應名稱進行計算。單元格的引用由于單元格計算方式的不同，引用單元格可以分為相對引用、絕對引用和混合引用。（1）相對引用。公式中的相對單元格引用（如“=A1”）是基于包含公式和單元格引用的單元格的相對位置。如果公式所在單元格的位置改變，引用也隨之改變。如果進行多行或多列的復制或填充，引用會自動調(diào)整。默認情況下，新公式使用相對引用。例如，將單元格B1中的相對引用復制或填充到單元格B2中，則公式自動從“=A1”調(diào)整到“=A2”。（2）絕對引用。公式中的絕對單元格引用（如“$A$1”）總是在特定位置引用單元格。即使公式所在單元格的位置改變，絕對引用也保持不變。如果進行多行或多列的復制或填充，絕對引用將不做調(diào)整。默認情況下，新公式使用相對引用，因此，為滿足需求，用戶可能需要手動將它們轉(zhuǎn)換為絕對引用。例如，將單元格B1中的絕對引用復制或填充到單元格B2中，則絕對引用在兩個單元格中是一樣的，都是“=$A$1”。單元格的引用（3）混合引用?；旌弦冒^對列和相對行或絕對行和相對列。絕對引用列采用“$A1”“$B1”等形式。絕對引用行采用“A$1”“B$1”等形式。如果公式所在單元格的位置改變，則相對引用部分將改變，而絕對引用部分將不變。如果進行多行或多列的復制或填充，相對引用將自動進行調(diào)整，而絕對引用將不做調(diào)整。例如，將一個混合引用從A1單元格復制到B2單元格中，它將從“=A$1”調(diào)整到“=B$1”。公式的輸入輸入公式類似于輸入文本型數(shù)據(jù)，不同的是在輸入一個公式之前，要輸入一個等號“=”，然后輸入公式的表達式。在單元格中輸入公式的步驟如下。（1）單擊要輸入公式的單元格。（2）在單元格中輸入一個等號“=”。（3）輸入第一個數(shù)值、單元格引用或者函數(shù)等。（4）輸入一個運算符。（5）輸入下一個數(shù)值、單元格引用或者函數(shù)等。（6）輸入一個運算符。（7）根據(jù)公式表達式重復步驟（5）～（6），輸入完畢后按【Enter】鍵或單擊編輯欄中的“輸入”按鈕，即可在單元格中顯示出計算結(jié)果。拖曳填充柄可以復制引用公式。公式輸入錯誤信息在運用公式進行計算時，經(jīng)常會出現(xiàn)一些異常信息，通常以符號“#”開頭，以感嘆號或問號結(jié)尾。公式中的錯誤值及其對應的可能的出錯原因、解決方法如下表所示。錯誤值可能出錯的原因解決方法#####單元格中輸入的數(shù)值或公式太長，單元格顯示不下，這不代表公式有錯調(diào)整加寬列寬#DIV/0!做除法時，分母為0修改單元格引用或者用作除數(shù)的單元格中數(shù)值不能為0#N/A在公式使用查找功能的函數(shù)時，找不到匹配的值輸入數(shù)值或參數(shù)#NAME?在公式中刪除了公式中使用的名稱或者名稱無法識別把函數(shù)名稱拼寫正確即可#NUM!提供了無效的參數(shù)給工作表函數(shù)或公式的結(jié)果太大或太小而無法在工作表中表示確認函數(shù)中使用的參數(shù)正確或者修改公式#REF!公式中使用了無效的單元格引用修改正確的單元格引用#VALUE!在公式中輸入了錯誤的運算符，對文本進行了算術(shù)運算修改公式或者將文本型數(shù)據(jù)轉(zhuǎn)換成數(shù)值型數(shù)據(jù)并進行計算公式的應用打開“4.1課程成績表.xlsx”文檔，根據(jù)成績占比計算每位學生的課程總成績（1）計算第一位同學的課程總成績，將光標定位在M5單元格中。（2）在單元格中輸入一個等號“=”。（3）根據(jù)總成績構(gòu)成輸入公式表達式“D5*D4+E5*E4+F5*F4+G5*G4+L5*L4”。（4）單擊編輯欄中的“輸入”按鈕，確認公式的輸入。（5）利用填充柄對公式進行復制引用。在引用的過程中，發(fā)現(xiàn)成績占比單元格需要使用絕對引用，因此修改公式輸入表達式為“D5*$D$4+E5*$E$4+F5*$F$4+G5*$G$4+L5*$L$4”。（6）拖曳填充柄對公式進行復制引用。函數(shù)的應用函數(shù)的應用了解函數(shù)函數(shù)是預定義的公式，使用一些被稱為參數(shù)的特定數(shù)值來執(zhí)行計算。下面以ROUND()函數(shù)為例說明函數(shù)的語法。①結(jié)構(gòu)。函數(shù)的結(jié)構(gòu)以等號（=）開始，后面緊跟函數(shù)名稱和左括號，然后以逗號分隔函數(shù)參數(shù)，最后是右括號。②函數(shù)名稱。如果要查看可用函數(shù)的列表，可單擊一個單元格并按【Shift】+【F3】組合鍵。③參數(shù)。參數(shù)可以是數(shù)字、文本、TRUE或FALSE邏輯值、數(shù)組、#N/A等錯誤值或單元格引用。指定的參數(shù)都必須為有效參數(shù)值。參數(shù)也可以是常量、公式或其他函數(shù)。④參數(shù)工具提示。在輸入函數(shù)時，會出現(xiàn)一個帶有語法和參數(shù)的工具提示框。例如，輸入“=ROUND()”時，會出現(xiàn)工具提示框。僅在使用內(nèi)置函數(shù)時才出現(xiàn)工具提示框。輸入函數(shù)如果創(chuàng)建帶函數(shù)的公式，則需要單擊“公式”選項卡中的“插入函數(shù)”按鈕，彈出的對話框可以幫助用戶輸入工作表函數(shù)。在公式中輸入函數(shù)時，“插入函數(shù)”對話框?qū)@示函數(shù)的名稱、各個參數(shù)、函數(shù)及各個參數(shù)的說明、函數(shù)的當前結(jié)果，以及整個公式的當前結(jié)果。若要更輕松地創(chuàng)建和編輯公式并將輸入錯誤和語法錯誤減到最少，可使用“公式記憶式輸入”。當輸入等號（=）和開頭的幾個字母或顯示觸發(fā)字符之后，Excel會在單元格的下方顯示一個動態(tài)下拉列表。該下拉列表中包含與這幾個字母或?qū)|發(fā)字符相匹配的有效函數(shù)、參數(shù)和名稱，可以選擇將該下拉列表中的一項插入公式中。在公式和函數(shù)中使用名稱可以創(chuàng)建已定義名稱來代表單元格、單元格區(qū)域、公式、常量或Excel表格。名稱是一種有意義的簡寫形式，有利于用戶了解單元格引用、常量、公式或表格的用途。1．名稱的類型可以創(chuàng)建和使用的名稱類型有以下幾種。（1）已定義名稱：單元格、單元格區(qū)域、公式或常量值的名稱。用戶也可以創(chuàng)建自己的已定義名稱。此外，Excel有時也會為用戶創(chuàng)建已定義名稱，如在用戶設(shè)置打印區(qū)域時。（2）表名稱：Excel表的名稱。Excel表是存儲在記錄（行）和字段（列）中的特定對象的數(shù)據(jù)集。Excel會在用戶每次插入Excel表時創(chuàng)建一個默認的Excel表名稱，用戶可以更改這些名稱，使它們更有意義。在公式和函數(shù)中使用名稱2．創(chuàng)建和輸入名稱執(zhí)行下列操作可以創(chuàng)建名稱。（1）單擊“公式”選項卡“定義的名稱”組中的“定義名稱”下拉按鈕，在彈出的下拉菜單中選擇“定義名稱”命令，如左圖所示。（2）在彈出的“新建名稱”對話框中輸入要定義的名稱及引用的位置，單擊“確定”按鈕，如右圖所示。（3）在公式或函數(shù)中直接引用創(chuàng)建的名稱即可。常用函數(shù)的應用常用函數(shù)的應用求和函數(shù)SUM()功能：返回某一單元格區(qū)域中數(shù)字、邏輯值及數(shù)字的文本表達式之和。語法：SUM（number1，number2，…）。參數(shù)說明：至少需要包含一個參數(shù)number1。每個參數(shù)都可以是區(qū)域、單元格引用、數(shù)組、常量、公式或另一個函數(shù)的結(jié)果。例如，“=SUM（A1：A7）”是將單元格A1～A7中的所有數(shù)值相加；=SUM（A1，A3，A5）是將單元格A1、A3和A5中的數(shù)字相加。平均值函數(shù)AVERAGE功能：返回其參數(shù)的算術(shù)平均值。語法：AVERAGE（number1，number2，…）。參數(shù)說明：至少需要包含一個參數(shù)number1，最多可包含255個。參數(shù)可以是數(shù)值或包含數(shù)值的名稱、數(shù)組或引用。例如，“=AVERAGE（B1：B7）”是對單元格區(qū)域B1到B7中數(shù)值求平均值；“=AVERAGE（B1:B3，B5）”是對單元格區(qū)域B1到B3中的數(shù)值和B5中的數(shù)值求平均值。邏輯判斷函數(shù)IF功能：根據(jù)指定的條件來判斷其“真”（TRUE）、“假”（FALSE），根據(jù)邏輯計算的真假值，從而返回相應的內(nèi)容。語法：IF（logical_test，value_if_true，value_if_false）。參數(shù)說明如下。（1）Logical_test表示計算結(jié)果為TRUE或FALSE的任意值或表達式。（2）Value_if_true為TRUE時返回的值。（3）Value_if_false為FALSE時返回的值。例如，“=IF（A1<60，"不及格"，"及格"）”表示，如果單元格A1中的值小于60，則顯示“不及格”字樣，否則顯示“及格”字樣。最大值函數(shù)MAX功能：返回一組值或指定區(qū)域中的最大值。語法：MAX（number1，number2，…）。參數(shù)說明：參數(shù)至少有一個，且必須是數(shù)值，最多可以有255個。例如，“=MAX（A1：A5）”表示從單元格區(qū)域A1到A5中查找并返回最大數(shù)值。最小值函數(shù)MIN功能：返回一組值或指定區(qū)域中的最小值。語法：MIN（number1，number2，…）。參數(shù)說明：參數(shù)至少有一個，且必須是數(shù)值，最多可以有255個。例如，“=MIN（A1：A5）”表示從單元格區(qū)域A1到A5中查找并返回最小數(shù)值。排序函數(shù)RANK功能：求某一個數(shù)值在某一區(qū)域內(nèi)的排名。語法：RANK（number,ref,[order]）。參數(shù)說明如下。（1）number必須的參數(shù)。要確定其排位的數(shù)值。（2）Ref必須的參數(shù)。要查找的數(shù)值列表所在的位置。（3）Order可選的參數(shù)。指定數(shù)值列表的排序方式。例如，“=RANK（“80”，A1：A10，1）表示求取數(shù)值80在單元格區(qū)域A1到A10中的數(shù)值列表中的升序排位。計數(shù)函數(shù)COUNT功能：統(tǒng)計指定區(qū)域中包含數(shù)值的個數(shù)，只對包含數(shù)字的單元格進行計數(shù)。語法：COUNT（Value1,value2,