excel表格數(shù)據(jù)計算.ppt

1、項目九 表格數(shù)據(jù)計算,Excel 2007中的公式和函數(shù)是高效計算表格數(shù)據(jù)的有效工具，也是必須學(xué)習(xí)的重要內(nèi)容。本項目將詳細(xì)介紹Excel公式和函數(shù)的使用方法，希望能夠幫助用戶提高利用函數(shù)和公式的能力，順利解決實際工作中的難題。,知識解析,1.公式 公式是由常量、單元格引用、單元格名稱、函 數(shù)和運(yùn)算符組成的字符串，也是在工作表中對 數(shù)據(jù)進(jìn)行分析的算式。公式可以對工作表中的 數(shù)據(jù)進(jìn)行加、減、乘、除等運(yùn)算。在使用公式 運(yùn)算過程中，可以引用同一工作表中不同的單 元格、同一工作簿不同工作表中的單元格，也 可以引用其他工作簿中的單元格。,（1）運(yùn)算符,運(yùn)算符是連接數(shù)據(jù)組成公式的符號，公式中的數(shù)據(jù)根據(jù)運(yùn)算符

2、的性質(zhì)和級別進(jìn)行運(yùn)算。運(yùn)算符可以分為4種類型。,（2）公式的組成,Excel中所有的計算公式都是以“=”開始，除此之外，它與數(shù)學(xué)公式的構(gòu)成基本相同，也是有參與計算的參數(shù)和運(yùn)算符組成。參與計算的參數(shù)可以是常量、變量、單元格地址、單元格名稱和函數(shù)，但不允許出現(xiàn)空格。,（3）公式的顯示、鎖定和隱藏,在默認(rèn)情況下，Excel只在單元格中顯示公式的計算結(jié)果，而不是計算公式，為了在工作表中看到實際隱含的公式，可以單擊含有公式的單元格，在編輯欄中顯示公式，或者雙擊該單元格，公式會直接顯示在單元格中。,鎖定公式就是將公式保護(hù)起來，別人不能修改。若不希望別人看到所使用的公式，可以將公式隱藏。需要注意的是在鎖定、

3、隱藏公式后，必須執(zhí)行“保護(hù)工作表”的操作，這樣才能使鎖定、隱藏生效。,2.單元格引用,使用“引用”可以為計算帶來很多的方便，但同時也會出現(xiàn)一些問題，尤其是在用戶進(jìn)行公式復(fù)制的時候。當(dāng)把計算公式從一個單元格復(fù)制到另一個單元格后，公式會發(fā)生改變，改變的原因就是在創(chuàng)建公式時使用了引用。,(1)相對引用,相對引用是指單元格引用會隨公式所在單元格的位置變化而變化，公式中單元格的地址是指當(dāng)前單元格的相對位置。當(dāng)使用該公式的活動單元格地址發(fā)生改變時，公式中所引用的單元格地址也相應(yīng)發(fā)生變化。,(2)絕對引用,絕對引用是指引用特定位置的單元格，公式中引用的單元格地址不隨當(dāng)前單元格的位置改變而改變。在使用時，單元

4、格地址的列號和行號前增加一個字符“$”。,(3)混合引用,根據(jù)實際情況，在公式中同時使用相對引用和絕對引用稱為混合引用。例如：$A1，A$1都是混合引用，其中$A1表示列地址不變，行地址變化，而A$1表示行地址不變，列地址變化。,3.函數(shù),函數(shù)是一些已經(jīng)定義好的公式。大多數(shù)函數(shù)是經(jīng)常使用的公式的簡寫形式。函數(shù)由函數(shù)名和參數(shù)組成，函數(shù)的一般格式為： 函數(shù)名（參數(shù)） 輸入函數(shù)有兩種方法：一種是在單元格中直接輸入函數(shù)，這與在單元格中輸入公式的方法一樣，只需先輸入一個“=”， 然后輸入函數(shù)本身即可。另一種是通過命令的方式插入函數(shù)。,任務(wù)一 使用公式,表格計算是在日常辦公中經(jīng)常遇到的問題。通常，用戶面對

5、的表格計算不僅計算量大，計算對象也頻繁變化，若使用公式則可以方便快捷地完成計算任務(wù)。,操作一 制作書店銷售報表,【情景模擬】作為書店的會計，小王每天都要上報書店圖書的流量、銷售額、利潤等情況。手工計算非常煩瑣，小王將書店營銷的一些數(shù)據(jù)輸入Excel表格，然后利用公式計算功能統(tǒng)計銷售情況。小王制作的書店銷售報表如樣例所示。,【步驟解析】,（1）啟動Excel 2007，建立一個新的工作表。 （2）在工作表中輸入基本數(shù)據(jù)，并按樣例格式排版，效果如圖所示。,（3）單擊H3單元格，在單元格內(nèi)輸入公式 “=F3-G3”，按回車鍵，結(jié)果顯示在H3單元格中。 （4）拖動H3單元格的填充柄至H7單元格釋放，

6、其他書種的結(jié)余數(shù)量會顯示在相應(yīng)的單元格中。 （5）參照第（3）、（4）步的方法，分別在I3單 元格中輸入公式“= G3*E3”， 在J3單元格中輸入 公式“=I3*0.3”，在I8單元格中輸入公式 “=I3+I4+I5+I6+I7”，在J8單元格中輸入公式 “=J3+J4+J5+J6+J7”，運(yùn)算結(jié)果會顯示在相應(yīng)的單 元格中。,（6）選中J3:J7單元格區(qū)域，單擊鼠標(biāo)右鍵，打開快捷菜單，選擇“設(shè)置單元格格式”命令，在“設(shè)置單元格格式”對話框的“保護(hù)”選項卡中選中“鎖定”和“隱藏”復(fù)選框，單擊“確定”按鈕，如圖所示。,（7）單擊“審閱”選項卡“更改”任務(wù)組中的“保護(hù)工作表”命令，打開“保護(hù)工作表

7、”對話框，選中“保護(hù)工作表及鎖定的單元格內(nèi)容”復(fù)選框，在“取消工作表保護(hù)時使用的密碼”文本框中輸入密碼，單擊“確定”，如圖所示。,（8）打開“確認(rèn)密碼”對話框，如圖所示，重新輸入密碼，進(jìn)行密碼確認(rèn)。,（9）J3:J7單元格區(qū)域中的公式被隱藏和鎖定，如圖所示。,（10）單擊“審閱”選項卡 “更改”任務(wù)組中的“撤消保護(hù)工作表”命令，打開“撤消工作表保護(hù)”對話框，如圖所示，輸入密碼，單擊“確定”，可以撤消工作表的保護(hù)。,提示,要“鎖定”和“隱藏”公式，必須要“保護(hù)工作表”。“保護(hù)工作表”與“鎖定”和“隱藏”公式的操作順序不能顛倒，如果先“保護(hù)工作表”，就無法對公式進(jìn)行“鎖定”和“隱藏”。,操作二 制

8、作具有引用功能的 產(chǎn)品銷售報表,【情景模擬】作為銷售部門的統(tǒng)計員，小馬每個月都要統(tǒng)計出產(chǎn)品銷售的情況。小馬制作銷售報表時，需要計算銷售額和利潤。利用單元格的引用功能，小馬每次都能很快地制作出報表。小馬制作的報表如樣例 所示。,【步驟解析】,（1）啟動Excel 2007，建立一個新的工作表。 （2）在工作表中輸入基本數(shù)據(jù)，如圖所示。,提示,在“Excel選項”對話框中，單擊左側(cè)列表中的“高級”，在“此工作表的顯示選項”選項中，選中“在單元格中顯示公式而非其計算結(jié)果”復(fù)選框，可以使單元格顯示公式，而不是計算的結(jié)果。,（3）在D3單元格中輸入公式“=B3*C3”，拖動D3單元格的填充柄至D7單元格

9、，填充公式。公式中的單價單元格、銷售數(shù)量單元格的地址隨著銷售額單元格位置的改變而改變。 （4）在E3單元格中輸入公式“=D3*$B$1”，拖動E3單元格的填充柄至E7單元格，填充公式。公式中的銷售額單元格的地址隨著利潤額單元格的位置改變而改變，而利潤率單元格的地址不變。,任務(wù)二 使用函數(shù),函數(shù)是內(nèi)置公式，利用它可以大大減少使用公式的復(fù)雜程度，提高計算效率。,操作一 制作學(xué)生成績統(tǒng)計表,【情景模擬】老師經(jīng)常需要對學(xué)生的考試成績進(jìn)行統(tǒng)計，有時需要計算單科平均分，有時需要計算總分，名目繁多。如果依靠人工計算，不但工作量非常大，也可能出現(xiàn)錯誤。李老師就是充分利用Excel函數(shù)的計算功能輕松應(yīng)對成績統(tǒng)計

10、工作，學(xué)生成績統(tǒng)計表如樣例所示。,步驟解析,（1）啟動Excel 2007，建立一個新的工作表。 （2）在工作表中輸入基本數(shù)據(jù)，并按樣例格式排版，結(jié)果如圖所示。,（3）在E2單元格輸入求和函“=SUM（B2:D2）”，按回車鍵，謝紅同學(xué)的總成績顯示在E2單元格內(nèi)。 （4）選中B3:D3單元格區(qū)域，單擊“公式”選項卡“函數(shù)庫”任務(wù)組中的“自動求和”下拉按鈕，打開“自動求和”菜單，如圖所示。單擊“求和”命令，E3單元格內(nèi)顯示李強(qiáng)同學(xué)的總成績。,（5）選中E4單元格，單擊“公式”選項卡“函數(shù)庫”任務(wù)組中的“插入函數(shù)”按鈕，打開“插入函數(shù)”對話框，如圖所示。,（6）在“插入函數(shù)”對話框的“選擇函數(shù)”列

11、表中，選中“SUM”，單擊“確定”按鈕。打開“函數(shù)參數(shù)”對話框，如圖所示。,（7）在“SUM”選項的“Number1”文本框中輸入求和的單元格區(qū)域B4:D4，也可以單擊文本框后面的“折疊對話框”按鈕，用鼠標(biāo)拖動的方法選中單元格區(qū)域，最后單擊“確定”按鈕。 （8）選取以上任意一種方法，計算其他同學(xué)的總成績。,（9）選中F2單元格，單擊“公式”選項卡“函數(shù)庫”任務(wù)組中的“自動求和”下拉按鈕，打開“自動求和”菜單，選擇“平均值”命令，選中求平均值的B2:D2單元格區(qū)域，如圖所示，按回車鍵，平均值顯示在F2單元格中。,（10）拖動F2單元格的填充柄到F6單元格，求出其他同學(xué)的平均值。 （11）利用“自

12、動求和”菜單中的“最大值”和“最小值”命令，可以求出語文的最低分和最高分，然后用鼠標(biāo)拖動填充，求出其他學(xué)科及總分和平均分的最低分和最高分。,操作二 制作考核等級表,【情景模擬】每到年終，單位要對每個員工的工作進(jìn)行考核，評出各個員工的工作業(yè)績情況。小王把每位員工的工作情況信息輸入表中，根據(jù)單位制定的評定標(biāo)準(zhǔn)，對每個員工進(jìn)行測評，得到評價結(jié)果。如果采用傳統(tǒng)的手工計算方式，任務(wù)十分繁瑣，為了提高工作效率，小王利用Excel的IF函數(shù)匯總結(jié)果。小王制作的考核結(jié)果統(tǒng)計表如樣例所示。,步驟解析,（1）啟動Excel 2007，建立一個新的工作表。 （2）在工作表中輸入基本數(shù)據(jù)，如圖所示。,（3）選中C2單

13、元格，在單元格內(nèi)輸入公式“=IF(B2=2400,優(yōu),IF(B2=1600,稱職 ,IF(B21600,不稱職)”，按回車鍵確認(rèn)，在單元格顯示出評價結(jié)果“稱職”。 （4）選中C2單元格，拖動填充柄到C8單元格，所有人員的評價結(jié)果便在相應(yīng)的單元格內(nèi)顯示。,（5）在D2、D3、D4單元格中輸入文本“優(yōu)秀人數(shù)、稱職人數(shù)、不稱職人數(shù)”。 （6）選中E2單元格，在單元格內(nèi)輸入公式“=COUNTIF(C2:C8, 優(yōu)秀)”，在E3單元格中輸入公式“=COUNTIF(C2:C8,稱職)”，在E4單元格中輸入公式“=COUNTIF(C2:C8,不稱職)”，可以統(tǒng)計出各個層次人員的數(shù)量。,（9）選中F2單元格，

14、單擊“公式”選項卡“函數(shù)庫”任務(wù)組中的“自動求和”下拉按鈕，打開“自動求和”菜單，選擇“平均值”命令，選中求平均值的B2:D2單元格區(qū)域，按回車鍵，平均值顯示在F2單元格中。,項目實訓(xùn),實訓(xùn)目標(biāo)： （1）掌握公式、常用函數(shù)的使用方法。 （2）了解運(yùn)算符的優(yōu)先級并能正確輸入公式。 （3）學(xué)會顯示、鎖定和隱藏公式。 （4）理解3種單元格引用的概念及用法。,實訓(xùn)過程：,（1）練習(xí)公式的輸入。 （2）給出公式，判斷運(yùn)算符的優(yōu)先級并進(jìn) 行運(yùn)算。 （3）在單元格中輸入公式，然后分別進(jìn)行 鎖定和隱藏操作。 （4）在工作表計算中應(yīng)用單元格引用。 （5）進(jìn)行SUM、AVERAGE、MAX、MIN、IF 函數(shù)計算。,項目總結(jié),在Excel中，所有的計算公式都要以“=”開始，之后才 是運(yùn)算符連接的運(yùn)算對象。公式可以在單元格中輸入，也可 以在編輯欄中輸入，輸完后按回車