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

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

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

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

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

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

6、其他書(shū)種的結(jié)余數(shù)量會(huì)顯示在相應(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é)果會(huì)顯示在相應(yīng)的單 元格中。,（6）選中J3:J7單元格區(qū)域，單擊鼠標(biāo)右鍵，打開(kāi)快捷菜單，選擇“設(shè)置單元格格式”命令，在“設(shè)置單元格格式”對(duì)話(huà)框的“保護(hù)”選項(xiàng)卡中選中“鎖定”和“隱藏”復(fù)選框，單擊“確定”按鈕，如圖所示。,（7）單擊“審閱”選項(xiàng)卡“更改”任務(wù)組中的“保護(hù)工作表”命令，打開(kāi)“保護(hù)工作表

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

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

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

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

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

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

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

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