第10章 完成復雜計算

下頁10.1 創(chuàng)建與編輯公式10.2 單元格的引用10.4 使用數(shù)組10.3 函數(shù)10.5 審核公式一、創(chuàng)建公式

10.1創(chuàng)建與編輯公式

二、公式的輸入

三、公式的編輯

返回四、使用復雜公式

五、公式返回的錯誤值和產(chǎn)生原因1.公式中的運算符

Excel的運算符有以下4類：算術(shù)運算符

比較運算符

文本運算符

引用運算符

下頁下頁算術(shù)運算符含義示例+（加號）加1+2-（減號）減2-1-（負號）負數(shù)-1*（星號）乘2*2/（斜杠）除4/2%（百分比）百分比12%＾（脫字符）乘冪3^2比較運算符含義示例=（等號）等于A1=A2>（大于號）大于A1>A2<（小于號）小于A1<A2>=（大于等于號）大于等于A1>=A2<=（小于等于號）小于等于A1<=A2Excel公式中的運算符下頁<>（不等號）不等于A1<>A2文本運算符含義示例&（連字符）將兩個文本連接起來產(chǎn)生連續(xù)的文本“學會”&“求知”產(chǎn)生“學會求知”引用運算符含義示例：（冒號）區(qū)域運算符，對兩個引用之間包括這兩個引用在內(nèi)的所有單元格進行引用A1:D1（引用A1到D1范圍內(nèi)的所有單元格），（逗號）聯(lián)合運算符，將多個引用合并為一個引用SUM(A1:D1,A2:C2)將A1:D2和A2:C2兩個區(qū)域合并為一個。（空格）交叉運算符，產(chǎn)生同時屬于兩個引用的單元格區(qū)域的引用SUM(A1:F1B1:B4)(B1同時屬于兩個引用A1:F1,B1:B4)返回２．公式的運算順序

運算符（優(yōu)先級從高到低）說明：（冒號）區(qū)域運算符，（逗號）聯(lián)合運算符（空格）交叉運算符-（負號）-５%（百分號）百分比＾（脫字符）乘冪*和／乘和除+和-加和減&文本運算符=、>、<、>=、<=、<>比較運算符下頁二、公式的輸入

１．在編輯欄中輸入公式2.在單元格里直接輸入公式

雙擊要輸入公式的單元格，或者先選中單元格再按F2鍵后，在單元格中輸入公式，最后按Enter鍵。

像輸入數(shù)字或文本一樣鍵入公式后，再按Enter鍵或單擊“輸入”按鈕。

３．舉例說明輸入公式的具體步驟下頁(1)建立一個如圖10.1所示的工作表。

(2)

選定單元格D2。(3)

在編輯欄中輸入“=B2+C2”。(4)

按Enter鍵，D2中將顯示公式的計算結(jié)果158。(1)

選定單元格E4。(2)

在編輯欄中輸入“=”，然后用鼠標單擊B4單元格，發(fā)現(xiàn)編輯欄中顯示“=B2”，在編輯欄中輸入“&”，再用鼠標單擊C4單元格，編輯欄中顯示“=B4&C4”。(3)

按Enter鍵，E4單元格中顯示“學會求知”，如圖10.2所示，這是公式“=B4&C4”的計算結(jié)果。返回例10.1：創(chuàng)建并輸入公式，將圖10.1工作表中B4和C4

單元格中的兩個文本連接為一個文本。圖10.2返回實用技巧：當要在一個單元格區(qū)域中輸入同一個公式時，先選定該區(qū)域，如圖10.3所示，輸入公式（“B6+C6”或“B6:B8+C6:C8”）后，按“Ctrl+Enter”鍵，執(zhí)行以上操作就不必在每個單元格中逐一輸入公式。在按Enter鍵確認輸入的公式之前，公式實際上并沒有被存儲在單元格中，可以單擊編輯欄左邊的“取消”按鈕或按Esc鍵來取消輸入的公式。圖10.3下頁三、公式的編輯１．修改公式修改公式同修改單元格中數(shù)據(jù)的方法一樣。先單擊包含要修改公式的單元格，如果要刪除公式中的某些項，在編輯欄中用鼠標選中要刪除的部分后，再按Backspace或者Delete鍵。如要替換公式中的某些部分，須先選中被替換的部分，然后再進行修改。在未確認之前單擊“取消”按鈕或按Esc鍵放棄本次修改。如果已確認修改但還未進行其他命令，單擊“編輯”菜單中的“撤消”命令或按“Ctrl+Z”鍵仍可放棄本次修改。下頁２．復制公式以將圖10.3單元格D2中的公式復制到單元格D6中為例，操作步驟如下：(1)

選定單元格D2。(2)

單擊“編輯”菜單中的“復制”命令，或按“Ctrl+C”快捷鍵。(3)

單擊D6單元格。(4)

單擊“編輯”菜單中的“選擇性粘貼”選項，彈出如圖10.4所示的“選擇性粘貼”對話框。(5)

在“選擇性粘貼”對話框中選擇“公式”單選按鈕。(6)單擊“確定”按鈕，D6中顯示26，即已將D2中的公式復制過來。返回返回３．移動公式以將圖10.3單元格D2中的公式復制到單元格G2中為例，操作步驟如下：(1)

選定D2單元格。(2)

將鼠標移到D2單元格的邊框上，當鼠標變?yōu)榘咨^時按下左鍵。(3)

拖動鼠標到G2單元格。(4)

釋放左鍵。也可以用菜單命令或“常用”工具欄上的工具按鈕像移動單元格一樣來移動公式。四、使用復雜公式１．公式中的數(shù)值轉(zhuǎn)換公式運算結(jié)果說明=“4”*“7”28當使用+、-、*、/等運算符時Excel認為運算數(shù)是數(shù)字。Excel自動將字符型數(shù)據(jù)“4”和“7”轉(zhuǎn)換為數(shù)字。=“99/7/20”-“98/7/20”365Excel將具有yy/mm/dd格式的文本當作日期，將日期轉(zhuǎn)換成序列數(shù)之后，再進行計算。SUM(“3+2”,5)#VALUE!返回出錯值，因為Excel不能將文本“3+2”轉(zhuǎn)換成數(shù)字，而SUM(“5”，5)可以返回103&“Word”3Word當公式中需要文本型數(shù)值時，Excel自動將數(shù)字轉(zhuǎn)換成文本下頁２．日期和時間的使用在Excel2000中不僅可以對數(shù)字和字符進行計算，也可以對日期和時間進行計算。Excel中顯示的時間和日期數(shù)字（例如34412.25），是以1900年1月1日星期日為日期起點，數(shù)值設定為1；以午夜零時（0:00:00）為時間起點，數(shù)值設定為0.0；其范圍是24小時。日期的計算中經(jīng)常用到兩個日期之差，例如公式=“98/10/20”-“98/10/5”，計算結(jié)果為15。也可以進行其他計算，例如公式=“99/7/20”+“99/7/5”，計算結(jié)果為72707。下頁返回在Excel2000中輸入日期時如果以短格式輸入年份（年份輸入兩位數(shù)），Excel將做如下處理：(1)

如果年份在00至29之間，Excel將作為2000至2029年處理，例如輸入10/10/20，Excel認為這個日期是2010年10月20日。(2)

如果年份在30至99之間，Excel將其作為1930至1999年處理，例如輸入73/3/23，Excel認為這個日期是1973年3月23日。小結(jié)五、公式返回的錯誤值和產(chǎn)生原因返回的錯誤值產(chǎn)生的原因#####!公式計算的結(jié)果太長，單元格容納不下，增加單元格的列寬可以解決這個問題#DIV/0除數(shù)為零#N/A公式中無可用的數(shù)值或缺少函數(shù)參數(shù)#NAME?使用了Excel不能識別的名稱#NULL!使用了不正確的區(qū)域運算或不正確的單元格引用#NUM!在需要數(shù)字參數(shù)的函數(shù)中使用了不能接受的參數(shù)，或者公式計算結(jié)果的數(shù)字太大或太小，Excel無法表示。#REF!公式中引用了無效單元格。#VALUE!需要數(shù)字或邏輯值時輸入了文本。第10章

第1節(jié)課授課內(nèi)容授課重點授課難點公式的創(chuàng)建、編輯公式的編輯使用復雜公式作業(yè)作業(yè)：上機實習第一題返回10.2 單元格的引用返回一、單元格引用及引用樣式二、輸入單元格引用三、引用其他工作表中的單元格四、引用其他工作簿中的單元格一、單元格引用及引用樣式

單元格的引用樣式R1C1樣式A1引用樣式相對引用

絕對引用混合引用下頁返回在Excel2000中，使用F4鍵可以快速改變單元格引用的類型。例如：

(1)

選擇單元格A1然后鍵入：“=B2”。(2)

按F4鍵將引用變?yōu)榻^對引用，該公式變?yōu)椋骸?$B$2”。(3)

再按F4鍵將引用變?yōu)榛旌弦茫ń^對行，相對列），公式變?yōu)椋骸?B$2”。(4)

再按F4鍵將引用變?yōu)榱硪环N混合形式（絕對列，相對行），公式變?yōu)椋骸?$B2”。(5)

再按F4鍵返回到原來的相對引用形式。二、輸入單元格引用在Excel中使用鼠標輸入單元格引用比用鍵盤節(jié)省時間而且準確率高。例10.2：用鼠標在單位格A3中輸入對A1和A2的引用。(1)

選擇A3，然后鍵入一個等號“＝”。(2)

單擊A1并鍵入一個加號“＋”。(3)

單擊A2后按Enter鍵。下頁返回實用技巧：若滾動工作表后活動單元格不再可見，按“Ctrl+Backspace”鍵可快速重新顯示活動單元格。在編輯公式時，被該公式所引用的所有單元格及單元格區(qū)域都將以彩色顯示在存放公式的單元格中，并在相應單元格及單元格區(qū)域的周圍顯示具有相同顏色的邊框。返回三、引用其他工作表中的單元格在Excel中，不僅可以引用當前工作表的單元格，還可以引用工作簿中其他工作表。其方法是：在公式中同時包括工作表引用和單元格引用。例如，要引用工作表Sheet9中的B2單元格，應在公式中輸入Sheet9!B2。感嘆號將工作表引用和單元格引用分開。如果工作表已命名，只需使用工作表名字再加上單元格引用。但是如果工作表名字中包含空格，必須用單引號括住工作表引用。使用鼠標也可以引用工作簿中另一張工作表的單元格或單元格范圍，其方法是：進入輸入公式的狀態(tài)，然后單擊需要引用的單元格所在的工作表標簽，選中需要引用的單元格，則該單元格引用會顯示在編輯欄中。如果工作表名字包括空格，Excel2000會自動用單引號括住工作表引用，最后按Enter鍵完成公式的輸入。返回四、引用其他工作簿中的單元格在Excel中，不但可以引用同一工作簿中不同工作表的單元格，還能引用不同工作簿中的單元格。其方法是：在公式中同時包括工作簿引用、工作表引用和單元格引用。例如：=[Book1]Sheet1!$A$1-[Book2]Sheet2!$B$1在上面的公式中，[Book1]和[Book2]是兩個不同工作簿的名稱，Sheet1和Sheet2是分別屬于兩個工作簿的工作表的名稱。$A$1和$B$1表示單元格的絕對引用。若引用的工作簿已關(guān)閉，那么在引用中將出現(xiàn)該工作簿存放位置的全部路徑，例如：=Sheet1!$A$1-‘C:\MYDOCUMENTS\[Book2.XLS]Sheet2’!$B$1返回第三節(jié) 函數(shù)一、Excel內(nèi)置函數(shù)二、常用函數(shù)三、在公式中使用函數(shù)四、編輯函數(shù)返回內(nèi)置函數(shù)分類分類功能簡介數(shù)據(jù)庫工作表函數(shù)分析數(shù)據(jù)清單中的數(shù)值是否符合特定條件日期與時間函數(shù)在公式中分析和處理日期值和時間值工程函數(shù)用于工作分析信息函數(shù)確定存儲在單元格中數(shù)據(jù)的類型財務函數(shù)進行一般的財務計算邏輯函數(shù)進行邏輯判斷或者進行復合檢驗統(tǒng)計函數(shù)對數(shù)據(jù)區(qū)域進行統(tǒng)計分析查找和引用函數(shù)在數(shù)據(jù)清單中查找特定數(shù)據(jù)或者查找一個單元格的引用文本函數(shù)在公式中處理字符串數(shù)學和三角函數(shù)進行數(shù)學計算下頁二、常用函數(shù)１．SUM函數(shù)

功能：SUM函數(shù)用于計算單個或多個參數(shù)之和。

語法：SUM（number1，number2，……）number1，number2，……為1到30個需要求和的參數(shù)

參數(shù)：邏輯值、數(shù)字、數(shù)字的文本形式、單元格的引用例10.3：SUM(10，20)等于30SUM(A1:E1)等于A1到E1共5個單元格中數(shù)值的和。下頁２．SUMIF函數(shù)功能：SUMIF函數(shù)對符合指定條件的單元格求和。語法：SUMIF（range，criteria，sum_range）range用于條件判斷的單元格區(qū)域。Criteria確定哪些單元格符合相加的條件。其形式可以是數(shù)字、表達式或文本。sum_range是需要求和的實際單元格區(qū)域，只有當range中的相應單元格滿足criteria中的條件時，才對sum_range中相應的單元格求和。若省略sum_range，則對range中滿足條件的單元格求和。例10.4：設A1:A4中的數(shù)據(jù)是10，20，30，40，而B1:B4中的數(shù)據(jù)是100，200，300，400，那么SUMIF(A1:A4，“>15”，B1:B4)

等于900，因為A2、A3、A4中的數(shù)據(jù)滿足條件，所以相應地對B2、B3、B4進行求和。３．AVERAGE函數(shù)功能：AVERAGE函數(shù)對所有參數(shù)計算算術(shù)平均值語法：AVERAGE(number1，number2，……)number1，number2，……為需要計算平均值的1到30個參數(shù)。參數(shù)應該是數(shù)字或包含數(shù)字的單元格引用、數(shù)組或名字。例10.5：AVERAGE(1,2,3,4,5)等于3。下頁下頁４．DAY函數(shù)功能:DAY函數(shù)將某一日期的表示方法從日期序列數(shù)形式轉(zhuǎn)換成它所在月份中的序數(shù)(即某月的第幾天)，用整數(shù)1到31表示。語法：DAY(serial_number)serial_number是用于日期和時間計算的日期時間代碼，可以是數(shù)字，或文本，如“98/10/20”。例10.6：DAY（“98/10/20”）等于20。DAY（“5-OCT”）等于5。下頁５．TODAY函數(shù)和NOW函數(shù)功能：TODAY函數(shù)返回計算機內(nèi)部時鐘的當前日期。NOW函數(shù)返回計算機內(nèi)部時鐘的當前日期和時間。語法：TODAY()NOW()這兩個函數(shù)都不需要輸入?yún)?shù)。下頁６．LEFT和RIGHT函數(shù)功能：LEFT函數(shù)返回字符串最左端的子字符串。RIGHT函數(shù)返回字符串最右端的子字符串。語法：LEFT(text，num_chars)text為包含要提取子字符串的字符串。num_chars為子字符串的長度。例10.7：LEFT(“MicrosoftExcel2000”,9)等于“Microsoft”。RIGHT(“Iamastudent”，7)等于“student”。下頁７．TRUNC函數(shù)功能：將數(shù)字截為整數(shù)或保留指定位數(shù)的小數(shù)。語法：TRUNC(number，num_digits)number為需要截尾取整的數(shù)字。num_digits為指定取整精度的數(shù)字（小數(shù)位數(shù)），默認值為0。例10.8：TRUNC(8.6)等于8TRUNC(-8.67，1)等于-8.6下頁功能：返回實數(shù)向下取整后的整數(shù)值。語法：INT(number)number是需要取整的實數(shù)。８．INT函數(shù)例10.9：INT(7.6)等于7INT(-7.6)等于-8下頁９．LOG和LOG10函數(shù)功能：LOG函數(shù)返回指定底數(shù)的對數(shù)，LOG10函數(shù)返回以10為底的常用對數(shù)。語法：LOG(number，base)LOG10(number)number是需要計算對數(shù)的正實數(shù)。base為對數(shù)的底數(shù)。LOG函數(shù)默認的底數(shù)為10。例10.10：LOG(9，2)等于3。LOG10(1000)等于3。返回１０．TYPE函數(shù)功能：返回數(shù)據(jù)的類型語法：TYPE(value)例10.11：TYPE(10)等于1如果A1單元格包含“EXCEL”，則TYPE(A1)等于2value為需要返回類型的數(shù)據(jù)TYPE函數(shù)的返回值參數(shù)value的類型TYPE函數(shù)的返回值數(shù)字１文本２邏輯值４公式８錯誤值１６數(shù)組６４TYPE函數(shù)的返回值返回三、在公式中使用函數(shù)1、直接鍵入函數(shù)2、使用函數(shù)選項板返回步驟：(3)

在“函數(shù)分類”列表中選擇函數(shù)類型，在“函數(shù)名”列表中選擇具體函數(shù)，例如“SUM函數(shù)”。(4)

單擊“確定”按鈕，在編輯欄下面彈出函數(shù)選項板，如圖10.6所示。(1)

選定要輸入公式的單元格，如果在編輯欄中輸入公式，將插入點移至要插入函數(shù)的位置。(2)單擊“插入”菜單中的“函數(shù)”命令，或單擊“常用”工具欄中的“粘貼函數(shù)”按鈕，打開“粘貼函數(shù)”對話框，如圖10.5所示。(5)

在參數(shù)框中輸入相應參數(shù)，按Tab鍵或“Shift+Tab”組合鍵在參數(shù)框間切換。(6)

按回車鍵或單擊“確定”按鈕，完成函數(shù)的輸入。返回圖10.5 “粘貼函數(shù)”對話框返回圖10.6 函數(shù)選項板返回小結(jié)四、編輯函數(shù)在編輯公式中所含函數(shù)時，一些比較小的修改，可以手工編輯，但是如果要對函數(shù)進行比較大的改動，還應該使用“函數(shù)選項板”。(1)

單擊要編輯函數(shù)的單元格。(2)

單擊“插入”菜單中的“函數(shù)”命令；或單擊“編輯欄”左側(cè)的“編輯公式”按鈕，以顯示“函數(shù)選項板”。(3)

“函數(shù)選項板”將顯示公式中的第一個函數(shù)和它的所有參數(shù)。(4)

在函數(shù)的編輯欄中單擊任意位置，可編輯第一個函數(shù)或同一公式中的其他函數(shù)。第十章

第二節(jié)課授課內(nèi)容單元格的引用和函數(shù)授課重點函數(shù)的使用授課難點常用函數(shù)作業(yè)作業(yè)：上機實習第三題返回第四節(jié) 使用數(shù)組一、數(shù)組公式的創(chuàng)建和輸入二、使用數(shù)組常量三、編輯數(shù)組公式或函數(shù)返回一、數(shù)組公式的創(chuàng)建和輸入(1)如果希望數(shù)組公式返回一個結(jié)果，單擊需要輸入數(shù)組公式的單元格，如果希望數(shù)組公式返回多個結(jié)果，選定需要輸入數(shù)組公式的單元格區(qū)域。(2)

鍵入公式的內(nèi)容。(3)

按“Ctrl+Shift+Enter”組合鍵，鎖定數(shù)組公式，Excel自動在編輯欄中公式的兩邊加上大括號，表明它是一個數(shù)組公式。注意：不要自己鍵入大括號，否則Excel會認為輸入的是一個正文標簽。下頁返回例10.12：計算期末考試個人成績總和(1)

選定區(qū)域H4到H11，如圖10.7所示。(2)

輸入公式“=B4:B11+C4:C11+D4:D11+E4:E11+F4:F11+G4:G11”，(3)

按“Ctrl+Shift+Enter”組合鍵結(jié)束輸入，計算結(jié)果如圖10.8所示。返回圖10.7 選定數(shù)組公式結(jié)果輸出區(qū)域返回圖10.8 數(shù)組公式的計算結(jié)果下頁二、使用數(shù)組常量在數(shù)組公式中，通常使用單元格區(qū)域引用，也可以直接鍵入數(shù)值數(shù)組，即數(shù)組常量。數(shù)組常量可由數(shù)、正文或邏輯值組成。使用數(shù)組常量時必須用大括號“｛｝”括起來，并且用逗號或分號分隔元素。逗號分隔不同列的值，分號分隔不同行的值。返回例10.13：計算1～9九個數(shù)值的平方根并顯示結(jié)果。(1)

選擇一塊3行3列的單元格區(qū)域。(2)

鍵入“=SQRT({1,2,3;4,5,6;7,8,9})”。注意：必須鍵入大括號，表明括起來的值組成一個數(shù)組常量。(3)

按“Ctrl+Shift+Enter”組合鍵。計算結(jié)果如圖10.9所示。圖10.9 數(shù)組常量計算結(jié)果下頁三、編輯數(shù)組公式或函數(shù)注意事項:·

(1)

在數(shù)組區(qū)域中不能編輯、清除和移動單個單元格，也不能插入或刪除單元格。必須將數(shù)組區(qū)域的單元格作為一個整體然后同時編輯它們。

(2)要移動數(shù)組區(qū)域的內(nèi)容，需選擇整個數(shù)組，并選擇“編輯”“剪切”命令，然后選擇新的位置并選擇“編輯”“粘貼”命令。還可以使用鼠標拖動選擇區(qū)域到新的位置。

(3)

不能剪切、清除或編輯數(shù)組的一部分，但可以為數(shù)組中單個單元格定義不同的格式。還可以從數(shù)組區(qū)域中復制單元格，然后在工作表的其他區(qū)域粘貼它們。返回編輯一個數(shù)組公式或函數(shù)的操作步驟如下：(1)

移動插入點至數(shù)組范圍中。(2)

單擊編輯欄，或按F2鍵，或者雙擊數(shù)組區(qū)域的第一個單元格，這時公式兩邊的括號將消失。(3)

編輯數(shù)組公式或函數(shù)。(4)

按Shift+Ctrl+Enter組合鍵，完成編輯修改。返回第五節(jié) 審核公式一、基本概念二、“審核”工具欄三、追蹤引用單元格四、追蹤從屬單元格返回

引用單元格：單元