.2.1公式的基本操作EXCEL公式可以對工作表中的值進行計算的等式，舉例如下。請打開“素材：學生身體素質(zhì)情況統(tǒng)計表”工作簿，選擇“公式入門”工作表,如圖1所示，根據(jù)要求計算體重指數(shù)和標準體重參考值。計算公式如下：標準體重（公斤）=[身高(厘米)－100]×0.9。體重指數(shù)=體重（公斤）÷[身高（米）的平方]。圖1計算體重指數(shù)和標準體重第1步：先計算220101號同學標準體重，再計算其余同學的標準體重。公式的形式：“＝表達式”。表達式由數(shù)據(jù)和運算符構(gòu)成，數(shù)據(jù)可以是具體數(shù)值或文本，也可以是單元格引用或函數(shù)。公式中一律使用小括號。表1Excel算術運算符運算符含義示例+加50+50-減50-50*乘50*50/除50/50^乘方50^2比較操作：方法1：在E4單元格輸入：＝(B4*100-100)*0.9方法2：在F4單元格輸入：＝(1.70*100-100)*0.9比較兩種計算的結(jié)果，然后運用智能填充功能分別對E5:E11,F5:F11單元格的數(shù)據(jù)進行填充。第2步：先計算220101號同學體重指數(shù)，再計算其余同學的體重指數(shù)。在D4單元格輸入公式：＝C4/(B4*B4)。然后對其余單元格進行數(shù)據(jù)填充。選擇“公式進階”工作表，如圖2所示，根據(jù)要求完成年級學生身體素質(zhì)情況匯總。圖2計算體重指數(shù)和標準體重第1步：計算C13單元格的數(shù)據(jù)，輸入公式＝C9/B9。第2步：對D13:F13數(shù)據(jù)區(qū)域進行數(shù)據(jù)填充。公式實質(zhì)表示的是單元格之間的關系，復制公式只是復制關系。Excel將根據(jù)公式復制的目標位置和源公式的位置關系進行推算，由源公式自動推導其他單元格的公式。Excel中地址的引用類型如表2所示，有三種形式，如果在復制公式時，公式中引用的單元格地址固定不變時，可以給地址加鎖：在地址前輸入$符號，或者輸入地址后選中地址再按F4鍵，成為絕對地址。C13單元格的正確公式＝C9/$B$9。表2Excel單元格引用的地址分類地址類型舉例特點相對地址D3在公式復制時，公式中引用的單元格地址將隨公式復制的單元格位置變化而發(fā)生相對變化。絕對地址$D$3在復制公式時，公式中引用的單元格地址固定不變。混合地址D$3行固定，列為相對地址。$D3列固定，行為相對地址。4.2.1公式的基本操作EXCEL公式可以對工作表中的值進行計算的等式，舉例如下。請打開“素材：學生身體素質(zhì)情況統(tǒng)計表”工作簿，選擇“公式入門”工作表,如圖1所示，根據(jù)要求計算體重指數(shù)和標準體重參考值。計算公式如下：標準體重（公斤）=[身高(厘米－100]×0.9。體重指數(shù)=體重（公斤）÷[身高（米）的平方]。圖1計算體重指數(shù)和標準體重第1步：先計算220101號同學標準體重，再計算其余同學的標準體重。公式的形式：“＝表達式”。表達式由數(shù)據(jù)和運算符構(gòu)成，數(shù)據(jù)可以是具體數(shù)值或文本，也可以是單元格引用或函數(shù)。公式中一律使用小括號。表1Excel算術運算符運算符含義示例+加50+50-減50-50*乘50*50/除50/50^乘方50^2比較操作：方法1：在E4單元格輸入：＝(B4*100-100)*0.9方法2：在F4單元格輸入：＝(1.70*100-100)*0.9比較兩種計算的結(jié)果，然后運用智能填充功能分別對E5:E11,F5:F11單元格的數(shù)據(jù)進行填充。第2步：先計算220101號同學體重指數(shù)，再計算其余同學的體重指數(shù)。在D4單元格輸入公式：＝C4/(B4*B4)。然后對其余單元格進行數(shù)據(jù)填充。選擇“公式進階”工作表,如圖2所示，根據(jù)要求完成年級學生身體素質(zhì)情況匯總。圖2計算體重指數(shù)和標準體重第1步：計算C13單元格的數(shù)據(jù)，輸入公式＝C9/B9。第2步：對D13:F13數(shù)據(jù)區(qū)域進行數(shù)據(jù)填充。公式實質(zhì)表示的是單元格之間的關系，復制公式只是復制關系。Excel將根據(jù)公式復制的目標位置和源公式的位置關系進行推算，由源公式自動推導其他單元格的公式。Excel中地址的引用類型如表2所示，有三種形式，如果在復制公式時，公式中引用的單元格地址固定不變時，可以給地址加鎖：在地址前輸入$符號，或者輸入地址后選中地址再按F4鍵，成為絕對地址。C13單元格的正確公式＝C9/$B$9。表2Excel單元格引用的地址分類地址類型舉例特點相對地址D3在公式復制時，公式中引用的單元格地址將隨公式復制的單元格位置變化而發(fā)生相對變化。絕對地址$D$3在復制公式時，公式中引用的單元格地址固定不變。混合地址D$3行固定，列為相對地址$D3列固定，行為相對地址4.2.2常用函數(shù)的使用函數(shù)是按照特定語法進行計算的一種表達式。電子表格提供了數(shù)學、財務、統(tǒng)計等豐富的函數(shù)，用于完成復雜、繁瑣的計算或者處理工作。AVERAGE函數(shù)的使用。選擇“素材：體能素質(zhì)情況統(tǒng)計表”“工作簿的“函數(shù)入門”工作表，計算各項體能素質(zhì)活動的平均值。利用平均值函數(shù)（AVERAGE函數(shù)）計算各項體能素質(zhì)活動的平均值。方法1：單擊選中D130單元格，選擇“公式”選項卡中的“fx插入函數(shù)”命令，在如圖1“插入函數(shù)”對話框中選擇AVERAGE函數(shù)，根據(jù)提示選擇D2:D129為計算數(shù)據(jù)區(qū)域。編輯欄中出現(xiàn)公式=AVERAGE(D2:D129)。函數(shù)的一般形式為“函數(shù)名（[參數(shù)1],[參數(shù)2],...）”。圖1“插入函數(shù)”對話框方法2：選擇D130單元格，選擇“開始”選項卡，在“函數(shù)庫組”中單擊“求和”按鈕的下拉箭頭（或“公式”選項卡的“函數(shù)庫”組中的“自動求和”按鈕的下拉箭頭），在如圖2所示的下拉菜單中選擇“平均值”命令，根據(jù)提示選擇D2:D129為計算數(shù)據(jù)區(qū)域。方法3：選定D130單元格，輸入等號“=”，然后輸入函數(shù)名的第1個字母，電子表格會自動列出以該字母開頭的函數(shù)名，如圖3所示。鼠標滾動找到需要的“AVERAGE”函數(shù)，點擊該函數(shù)名，在D130單元格內(nèi)會自動顯示一個帶有語法和參數(shù)的工具提示即“=AVERAGE(”。選定要引用的單元格區(qū)域，輸入右括號，然后按Enter鍵。圖2“自動求和”按鈕下拉菜單圖3函數(shù)自動匹配功能SUM函數(shù)的使用選擇“函數(shù)入門”工作表,計算各項體能素質(zhì)活動數(shù)據(jù)的總和。選擇求和函數(shù)（SUM函數(shù)）實現(xiàn)數(shù)據(jù)求和。自主探索MAX函數(shù)和MIN函數(shù)的使用利用最大值函數(shù)（MAX函數(shù)）和最小值函數(shù)（MIN函數(shù)）分別求出最大值和最小值。在使用函數(shù)時常常需要選擇數(shù)據(jù)區(qū)域，如果需要選擇不連續(xù)的數(shù)據(jù)區(qū)域時，可以按住Ctrl鍵選擇相應的不連續(xù)區(qū)域。例如，MAX(C4:C11)和MAX(C4,C11)這兩個函數(shù)式是不同的。復制函數(shù)拖動智能填充柄填充其他單元格區(qū)域，可實現(xiàn)復制函數(shù)。RANK函數(shù)的使用RANK函數(shù)功能是返回某一個數(shù)值在某一區(qū)域內(nèi)一組數(shù)值中的排名。將“函數(shù)入門”工作表按總計數(shù)值從高到低排序。方法1：單擊M2單元格，使用RANK函數(shù)，如圖4所示，根據(jù)L2單元格數(shù)值計算在L2：L129數(shù)據(jù)區(qū)域中的排名。運用數(shù)據(jù)填充功能填充其他區(qū)域數(shù)據(jù)。RANK函數(shù)格式為“rank(number,ref,[order])”。其中參數(shù)，number指參與排名的數(shù)值；ref是排名的數(shù)值區(qū)域；order有1和0兩種。0表示從大到小排名（降序），1表示從小到大排名（升序）。0默認不用輸入，得到的就是從大到小的排名。圖4RANK函數(shù)的使用思考：為什么這里的數(shù)據(jù)區(qū)域L2:L129需要設置為絕對地址引用？如果在復制公式時，公式中引用的單元格地址固定不變時，可以給地址加鎖：在地址前輸入$符號，或者輸入地址后選中地址再按F4鍵。本例RANK函數(shù)中數(shù)值區(qū)域ref參數(shù)值設置為$L$2:$L$129，將區(qū)域鎖定，便于復制函數(shù)。方法2：在M3單元格輸入以下公式：=RANK(L2,$L$2:$L$129)IF函數(shù)的使用選擇“函數(shù)入門”工作表，利用嵌套IF函數(shù)，根據(jù)總計數(shù)據(jù)學生體能素質(zhì)給出相應等級。對應關系為體能素質(zhì)總分在90分及以上為優(yōu)秀，60分以上為合格，60分以下為不合格。請根據(jù)題意，填寫完整下表。表1根據(jù)體能素質(zhì)判斷等級體能素質(zhì)總分等級L2>=90請根據(jù)上表分析，完成流程圖。LL2>=90是否合格是否是IF函數(shù)格式是“IF(logical_test,value_if_true,value_if_false)”，其中“l(fā)ogical_test”表示設定的條件，“value_if_true”表示當目標單元格與設定條件相符時返回的函數(shù)值，“value_if_false”表示當目標單元格與設定條件不符時返回的函數(shù)值。IF函數(shù)功能是根據(jù)設定條件來判斷其“真”(TRUE)、“假”(FALSE),從而返回相應的內(nèi)容。在進行條件判斷時，常常需要用到比較運算符，Excel中的比較運算符見表2。表2Excel比較運算符運算符含義=等于>大于<小于>=小于等于<=大于等于<>不等于嵌套IF函數(shù)的使用在N2單元格輸入以下公式：=IF(L2>=90,"優(yōu)秀",IF(L2>=60,"合格","不合格"))思考：還有其他判斷方法來實現(xiàn)輸出相應等級嗎？7.調(diào)用COUNTIF函數(shù)COUNTIF函數(shù)功能：對指定區(qū)域中符合指定條件的單元格計數(shù)。使用COUNTIF函數(shù)，可以實現(xiàn)按性別統(tǒng)計學生人數(shù)。在“函數(shù)進階”工作表B3單元格中調(diào)用該函數(shù)，輸入如圖5所示參數(shù)。圖5COUNTIF函數(shù)的使用調(diào)用AVERAGEIF函數(shù)AVERAGEIF函數(shù)功能是返回某個區(qū)域內(nèi)滿足給定條件的所有單元格的算術平均值。AVERAGEIF函數(shù)可以實現(xiàn)按性別統(tǒng)計項目得分。在“函數(shù)進階”工作表B10單元格中調(diào)用該函數(shù)，輸入如圖6所示參數(shù)。圖6AVERAGEIF函數(shù)的使用調(diào)用COUNTIFS函數(shù)按性別統(tǒng)計“優(yōu)秀”“合格”和“不合格”人數(shù)，這時需要請出COUNTIFS函數(shù)。COUNTIFS函數(shù)功能：用來計算多個區(qū)域中滿足給定條件的單元格的個數(shù)，可以同時設定多個條件，如圖7所示。該函數(shù)為COUNTIF函數(shù)的擴展。圖7COUNTIFS函數(shù)的使用函數(shù)的使用函數(shù)是按照特定語法進行計算的一種表達式。電子表格提供了數(shù)學、財務、統(tǒng)計等豐富的函數(shù)，用于完成復雜、繁瑣的計算或者處理工作。AVERAGE函數(shù)的使用。選擇“函數(shù)入門”工作表,計算各項體能素質(zhì)活動的平均值。利用平均值函數(shù)（AVERAGE函數(shù)）計算各項體能素質(zhì)活動的平均值。單擊選中D130單元格，選擇“公式”選項卡中的“fx插入函數(shù)”命令，在如圖1的“插入函數(shù)”對話框中選擇AVERAGE函數(shù)，根據(jù)提示選擇D2:D129為計算數(shù)據(jù)區(qū)域。編輯欄中出現(xiàn)公式=AVERAGE(D2:D129)。函數(shù)的一般形式為“函數(shù)名（[參數(shù)1],[參數(shù)2],...）”。圖1“插入函數(shù)”對話框你還可以使用以下方法來計算平均值。選擇D130單元格，選擇“開始”選項卡，在“函數(shù)庫組”中單擊“求和”按鈕的下拉箭頭（或“公式”選項卡的“函數(shù)庫”組中的“自動求和”按鈕的下拉箭頭），在如圖2所示的下拉菜單中選擇“平均值”命令，根據(jù)提示選擇D2:D129為計算數(shù)據(jù)區(qū)域。選定D130單元格，輸入等號“=”，然后輸入函數(shù)名的第1個字母，電子表格會自動列出以該字母開頭的函數(shù)名，如圖3所示。鼠標滾動找到我們需要的“AVERAGE”函數(shù)，點擊該函數(shù)名，在D130單元格內(nèi)會自動顯示一個帶有語法和參數(shù)的工具提示即“=AVERAGE(”。選定要引用的單元格區(qū)域，輸入右括號，然后按<Enter>鍵。圖2“自動求和”按鈕下拉菜單圖3函數(shù)自動匹配功能SUM函數(shù)的使用。選擇“函數(shù)入門”工作表,計算各項體能素質(zhì)活動數(shù)據(jù)的總和。選擇求和函數(shù)（SUM函數(shù)）實現(xiàn)數(shù)據(jù)求和。自主探索MAX函數(shù)和MIN函數(shù)的使用。利用最大值函數(shù)（MAX函數(shù)）和最小值函數(shù)（MIN函數(shù)）分別求出最大值和最小值。在使用函數(shù)時常常需要選擇數(shù)據(jù)區(qū)域，如果需要選擇不連續(xù)的數(shù)據(jù)區(qū)域時，可以按住Ctrl鍵選擇相應的不連續(xù)區(qū)域。例如，MAX(C4:C11)和MAX(C4,C11)這兩個函數(shù)式是不同的。復制函數(shù)。拖動智能填充柄填充其它單元格區(qū)域，實現(xiàn)復制函數(shù)操作。RANK函數(shù)的使用。RANK函數(shù)功能是返回某一個數(shù)值在某一區(qū)域內(nèi)一組數(shù)值中的排名。將“函數(shù)入門”工作表按總計數(shù)值從高到低排序。方法一：單擊M2單元格，使用RANK函數(shù)，如圖4所示，根據(jù)L2單元格數(shù)值計算在L2：L129數(shù)據(jù)區(qū)域中的排名。運用數(shù)據(jù)填充功能填充其它區(qū)域數(shù)據(jù)。RANK函數(shù)格式為“rank(number,ref,[order])”。其中參數(shù)，number指參與排名的數(shù)值；ref是排名的數(shù)值區(qū)域；order有1和0兩種。0表示從大到小排名（降序），1表示從小到大排名（升序）。0默認不用輸入，得到的就是從大到小的排名。圖4RANK函數(shù)的使用思考：為什么這里的數(shù)據(jù)區(qū)域L2:L129需要設置為絕對地址引用？如果在復制公式時，公式中引用的單元格地址固定不變時，可以給地址加鎖：在地址前輸入$符號，或者輸入地址后選中地址再按F4鍵。本例RANK函數(shù)中數(shù)值區(qū)域ref參數(shù)值設置為$L$2:$L$129，將區(qū)域鎖定，便于復制函數(shù)。方法二：在M3單元格輸入以下公式：=RANK(L2,$L$2:$L$129)IF函數(shù)的使用。選擇“函數(shù)入門”工作表，利用嵌套IF函數(shù)，根據(jù)總計數(shù)據(jù)學生體能素質(zhì)給出相應等級。對應關系為體能素質(zhì)總分在90分及以上為優(yōu)秀，60分以上為合格，60分以下為不合格。請根據(jù)題意，填寫完整下表1。表1根據(jù)體能素質(zhì)判斷等級體能素質(zhì)總分等級L2>=902.請你根據(jù)上表分析，完成流程圖。LL2>=90是否合格是否是IF函數(shù)格式是“IF(logical_test,value_if_true,value_if_false)”，其中“l(fā)ogical_test”表示設定的條件，“value_if_true”表示當目標單元格與設定條件相符時返回的函數(shù)值，“value_if_false”表示當目標單元格與設定條件不符時返回的函數(shù)值。IF函數(shù)功能是根據(jù)設定條件來判斷其“真”(TRUE)、“假”(FALSE),從而返回相應的內(nèi)容。3.在進行條件判斷時，常常需要用到比較運算符，表格中的比較運算符見表2。表2比較運算符運算符含義=等于>大于<小于>=小于等于<=大于等于<>不等于4.嵌套IF函數(shù)的使用在N2單元格輸入以下公式：=IF(L2>=90,"優(yōu)秀",IF(L2>=60,"合格","不合格"))5.思考：還有其它判斷方法來實現(xiàn)輸出相應等級嗎？七、調(diào)用COUNTIF函數(shù)。COUNTIF函數(shù)的功能是可求出計算區(qū)域中符合給定條件的單元格個數(shù)。使用COUNTIF函數(shù)，可以實現(xiàn)按性別統(tǒng)計學生人數(shù)。在“函數(shù)進階”工作表B3單元格中調(diào)用該函數(shù)，輸入如圖5所示參數(shù)。圖5COUNTIF函數(shù)的使用八、調(diào)用AVERAGEIF函數(shù)。 AVERAGEIF函數(shù)功能是求出指定區(qū)域中滿足給定條件的所有單元格的算術平均值。調(diào)用AVERAGEIF函數(shù)，求出按性別統(tǒng)計項目平均得分。在“函數(shù)進階”工作表B10單元格中調(diào)用改函數(shù)，輸入如圖6所示參數(shù)。圖6AVERAGEIF函數(shù)的使用九、調(diào)用COUNTIFS函數(shù)。COUNTIFS函數(shù)功能：用來計算多個區(qū)域中滿足給定條件的單元格的個數(shù)，可以同時設定多個條件。該函數(shù)為COUNTIFS函數(shù)的擴展。請按性別統(tǒng)計“優(yōu)秀”“合格”和“不合格”人數(shù)，需要調(diào)用COUNTIFS函數(shù)。圖7COUNTIFS函數(shù)的使用統(tǒng)計學習成效數(shù)據(jù)查詢（MSOffice版）設置數(shù)據(jù)序列選擇“函數(shù)提高”工作表，單擊選中“學習內(nèi)容”下方單元格，單擊“數(shù)據(jù)”選項卡→“數(shù)據(jù)工具”功能區(qū)→“數(shù)據(jù)驗證”下拉框→“數(shù)據(jù)驗證”對話框，設置數(shù)據(jù)序列，數(shù)據(jù)來源選擇“公式函數(shù)入門”工作表A2:A15區(qū)域，如圖1所示。操作完成后，本學期所學課文篇目名稱會以下拉選項框的形式展現(xiàn)。圖1設置數(shù)據(jù)序列調(diào)用VLOOKUP函數(shù)VLOOKUP函數(shù)是Excel中的一個縱向查找函數(shù)，在數(shù)據(jù)處理中有著廣泛應用。例如可以用來核對數(shù)據(jù)，多個表格之間快速導入數(shù)據(jù)等函數(shù)功能。功能是按列查找，最終返回該列所需查詢序列所對應的值。該函數(shù)的語法規(guī)則如下：VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])如圖2所示，在“函數(shù)提高”工作表數(shù)據(jù)區(qū)域中選擇所學內(nèi)容名稱，顯示該課文小華的學習評價。手動查找在數(shù)據(jù)量大的時候十分繁瑣，使用VLOOKUP函數(shù)可以輕松實現(xiàn)查找。選定“函數(shù)提高”工作表E10單元格，插入VLOOKUP函數(shù)，此時Excel就會提示4個參數(shù)，如圖2所示。圖2VLOOKUP函數(shù)參數(shù)設置第1個參數(shù)是查找值，選擇D10單元格，表示課文名稱；第2個參數(shù)是數(shù)據(jù)表，這里輸入查找區(qū)域也就是從“公式函數(shù)入門”表返回數(shù)據(jù)的區(qū)域(絕對引用)，即“公式函數(shù)入門!$A$2:$L$15”；查找時會用“函數(shù)提高！E10”與“公式函數(shù)入門!$A”列的內(nèi)容匹配；第3個參數(shù)為序列數(shù)，由于“學習評價”是查找區(qū)域的第12列，所以這里輸入“12”，就會顯示相應的學習評價。這里的列數(shù)不是Exce默認的列數(shù)，而是查找范圍的第幾列；第4個參數(shù)是匹配條件，因為我們要精確查找課文名稱，所以輸入“FALSE"或者“0”。如果只需模糊查找可以輸入“TURE”或“1”。試一試：使用VLOOKUP函數(shù)查找資源學習進度、活動參與率等信息。3.調(diào)用MID函數(shù)、REPLACE函數(shù)（1）MID函數(shù)MID函數(shù)可以從一個字符串中截取出指定數(shù)量的字符。請使用MID函數(shù)提取學生姓名中姓，如圖3所示。名字用“某某”進行替換，如圖4所示，實現(xiàn)保護個人信息隱私。圖3調(diào)用MID函數(shù)圖4替換姓名中名字（2）REPLACE函數(shù)REPLACE函數(shù)功能是將指定位置的特定數(shù)量的字符用新字符來代替，簡單地說就是進行字符的替換。將“個人信息”表中學生學號的后三位用“***”進行替換，在“函數(shù)提高”工作表E2單元格中顯示，設置如圖5所示。REPLACE函數(shù)總共有四個參數(shù)，第1參數(shù)是原始字符串，第2參數(shù)是要替換字符的位置，第3參數(shù)是要替換字符的字符數(shù)，第4參數(shù)是要替換的新字符串。圖5REPLACE函數(shù)在信息化時代，人人都應該增強信息安全意識。例如手機號碼、身份證號碼、家庭住址等個人信息，不能隨意公開。利用MID、REPLACE函數(shù)，隱去個人部分信息，達到增強信息安全的目的。請利用MID函數(shù)、REPLACE函數(shù)來試一試。統(tǒng)計學習成效數(shù)據(jù)查詢WPSOffice版設置數(shù)據(jù)序列選擇“函數(shù)提高”工作表，單擊選中“學習內(nèi)容”下方單元格，單擊“數(shù)據(jù)”選項卡→“下拉列表”按鈕→“插入下拉列表”對話框，設置數(shù)據(jù)序列，數(shù)據(jù)來源選擇“公式函數(shù)入門”工作表$A$2:$A$15區(qū)域，如圖1所示。操作完成后，本學期所學課文篇目名稱會以下拉選項框的形式展現(xiàn)。圖1設置數(shù)據(jù)序列調(diào)用VLOOKUP函數(shù)VLOOKUP函數(shù)是表格中的一個縱向查找函數(shù)，在數(shù)據(jù)處理中有著廣泛應用。例如可以用來核對數(shù)據(jù)，多個表格之間快速導入數(shù)據(jù)等函數(shù)功能。功能是按列查找，最終返回該列所需查詢序列所對應的值。該函數(shù)的語法規(guī)則如下：VLOOKUP(lookup_value,table_array,col_index_num,[rang