第5章EXCE基本應(yīng)用課件

1、大學(xué)計(jì)算機(jī)基礎(chǔ)大學(xué)計(jì)算機(jī)基礎(chǔ)計(jì)算機(jī)基礎(chǔ)教學(xué)部第第5 5章章 EXCELEXCEL基本應(yīng)用基本應(yīng)用內(nèi)容提要項(xiàng)目分析項(xiàng)目分析12制作課堂考勤登記表制作課堂考勤登記表課堂考勤成績計(jì)算課堂考勤成績計(jì)算34課程成績計(jì)算課程成績計(jì)算課程成績統(tǒng)計(jì)課程成績統(tǒng)計(jì)56制作成績通知單制作成績通知單項(xiàng)目分析（1） 張老師需要記錄“大學(xué)計(jì)算機(jī)”課程的考勤情況及成績。項(xiàng)目分析（2）制作課堂考勤登記表知識(shí)點(diǎn)解析（1）1.EXCEL工作界面制作課堂考勤登記表知識(shí)點(diǎn)解析（2）2.單元格地址與單元格選擇（1） 每個(gè)單元格都用其所在的列標(biāo)題和行標(biāo)題來標(biāo)識(shí)，稱為單元格地址。如第3列第4行的單元格地址用C4表示。當(dāng)選擇了某個(gè)單元格時(shí)，

2、該單元格對(duì)應(yīng)的行標(biāo)題和列標(biāo)題會(huì)用突出顏色特別標(biāo)識(shí)出來，名稱框中顯示該選擇的單元格地址，編輯欄中顯示該選擇的單元格中的內(nèi)容。 要在單元格中輸入內(nèi)容，需要先選擇該單元格。默認(rèn)情況下，單元格中內(nèi)容顯示為1行。制作課堂考勤登記表知識(shí)點(diǎn)解析（3）2.單元格地址與單元格選擇（2） 如果要選中多個(gè)連續(xù)的單元格，可以先選中第1個(gè)單元格（如B2），按下鼠標(biāo)左鍵，拖動(dòng)鼠標(biāo)到最后1個(gè)需要被選擇的單元格（如D5），松開鼠標(biāo)?？梢钥吹?，由第1個(gè)和最后1個(gè)單元格的行、列標(biāo)題所構(gòu)成的矩形區(qū)域中的所有單元格（稱為單元格區(qū)域，標(biāo)記為B2:D5）都被選擇，在名稱框中顯示的是第1個(gè)被選擇的單元格的地址（B2）制作課堂考勤登記表知識(shí)

3、點(diǎn)解析（4）2.單元格地址與單元格選擇（3） 而要選擇非連續(xù)單元格區(qū)域（如B2，B4，C3），可以先選中第1個(gè)單元格B2，按下Ctrl鍵不松手，用鼠標(biāo)單擊B4和C3，松開Ctrl鍵，則B2、B4和C3這3個(gè)單元格都被選中了，在名稱框中顯示的是最第1個(gè)被選擇的單元格的地址（C3） 要選擇某一列或者某一行，將鼠標(biāo)單擊對(duì)應(yīng)的列標(biāo)題或行標(biāo)題即可 而要同時(shí)選擇多行或多列，其操作方式與連續(xù)單元格區(qū)域和非連續(xù)單元格區(qū)域的選擇方式類似制作課堂考勤登記表知識(shí)點(diǎn)解析（5）3.用填充柄快速填充數(shù)據(jù) 在第1個(gè)單元格（A1）輸入“星期一”后，選擇A1單元格，移動(dòng)鼠標(biāo)至單元格右下角，在鼠標(biāo)變成黑色填充柄 的情況下，按下鼠

4、標(biāo)左鍵，拖動(dòng)鼠標(biāo)到A7單元格，松開鼠標(biāo)，則在A2:A7單元格里自動(dòng)填充了“星期二”到“星期日”。 如果填充數(shù)據(jù)非連續(xù)但又規(guī)律，則可以在第1個(gè)單元格（B1）輸入“第1天”，在第2個(gè)單元格（B2）輸入“第3天”后，選擇B1:B2單元格，移動(dòng)鼠標(biāo)，在鼠標(biāo)變成黑色填充柄 的情況下，按下鼠標(biāo)左鍵，拖動(dòng)鼠標(biāo)到B7單元格，松開鼠標(biāo)，則在B2:B7單元格里自動(dòng)填充了“第5天”到“第13天”。制作課堂考勤登記表知識(shí)點(diǎn)解析（6）4.設(shè)置單元格格式 “開始”選項(xiàng)卡主要用于設(shè)置單元格格式。其中v “字體”組主要用于設(shè)置被選擇的單元格（單元格區(qū)域）的字體、字號(hào)、顏色、邊框等。 v “對(duì)齊方式”組主要用于設(shè)置被選擇的單元

5、格（或單元格區(qū)域）中的內(nèi)容相對(duì)于單元格的對(duì)齊方式、單元格內(nèi)容的多行顯示及合并居中（即將多個(gè)單元格合并成1個(gè)單元格）等。v “數(shù)字”組主要用于設(shè)置被選擇的單元格（或單元格區(qū)域）的數(shù)字顯示格式。 v “單元格”組主要用于設(shè)置被選擇的單元格（或單元格區(qū)域）的行高和列寬等。制作課堂考勤登記表任務(wù)實(shí)現(xiàn)（1）1.任務(wù)分析制作如圖5-10（見書本）所示的表格，要求：（1）標(biāo)題為“仿宋”，16號(hào)字，藍(lán)色，加粗，位于A列至O列的中間。（2）學(xué)期及課程信息為“宋體”，10號(hào)字，藍(lán)色，加粗。課程信息為文本右對(duì)齊。（3）表格區(qū)域（A3:O40）為“宋體”，10號(hào)字，自動(dòng)顏色，單元格文本水平居中和垂直居中對(duì)齊。（4）表

6、頭區(qū)域（A3:O4）加粗。（5）A3:A4、B3:B4以及C3:L3分別合并后居中，并設(shè)置為垂直居中。（6）M3:M4、N3:N4、O3:O4分別合并后居中，并設(shè)置為垂直居中及自動(dòng)換行。（7）表格外邊框?yàn)楹谏?xì)實(shí)線，內(nèi)邊框?yàn)楹谏摼€。（8）調(diào)整單元格為合適的行高和列寬。制作課堂考勤登記表任務(wù)實(shí)現(xiàn)（2）2.實(shí)現(xiàn)過程 請(qǐng)參照書本完成，有問題請(qǐng)隨時(shí)舉手示意老師。制作課堂考勤登記表總結(jié)與提高（1）1. 根據(jù)模板生成Excel文檔2. 單元格區(qū)域選擇v 對(duì)于連續(xù)單元格的選擇，可以在選擇起始單元格后，按住Shift鍵，選擇最后一個(gè)單元格，就將兩個(gè)單元格為對(duì)角線的單元格區(qū)域選中了。3. 設(shè)置單元格格式v 在

7、選擇單元格后，可以單擊鼠標(biāo)右鍵，在彈出的菜單中選擇“設(shè)置單元格格式”，在彈出的“設(shè)置單元格格式”對(duì)話框中，通過切換選項(xiàng)卡來進(jìn)行單元格格式設(shè)置。v 對(duì)于相同格式單元格，可以一起選擇后進(jìn)行設(shè)置，也可以在設(shè)置好第1個(gè)單元格后，利用格式刷進(jìn)行格式復(fù)制。制作課堂考勤登記表總結(jié)與提高（2）4. 對(duì)齊方式v 單元格中的內(nèi)容相對(duì)于單元格邊框左右的對(duì)齊方式稱為“水平對(duì)齊”，相對(duì)于單元格邊框上下的對(duì)齊方式稱為“垂直對(duì)齊”。v “自動(dòng)換行”按鈕 和“合并后居中”按鈕 屬于雙態(tài)按鈕，即第1次按 時(shí)實(shí)現(xiàn)單元格中的內(nèi)容根據(jù)單元格寬度自動(dòng)調(diào)整成多行顯示，第2次按 時(shí)實(shí)現(xiàn)單元格中的內(nèi)容用1行顯示。5. 自動(dòng)換行v 除了通過單

8、元格格式的 “自動(dòng)換行”來實(shí)現(xiàn)單元格內(nèi)容分行顯示外，還通過在單元格中需要換行顯示的內(nèi)容前同時(shí)按下Alt鍵和回車鍵來實(shí)現(xiàn)單元格內(nèi)容的分行顯示。v 第一種是“軟”換行，只有該單元格的列寬不夠顯示單元格中的內(nèi)容，才將內(nèi)容以兩行（甚至多行）來顯示。而第二種是“硬”換行，無論將列調(diào)整到多寬，該單元格總是將 “Alt”鍵和回車鍵處開始的內(nèi)容另起一行顯示。課堂考勤成績計(jì)算知識(shí)點(diǎn)解析(1)1.單元格內(nèi)容復(fù)制2.文本數(shù)據(jù)的輸入v 在數(shù)字型文本數(shù)據(jù)前添加英文單引號(hào)“”。v 將該單元格數(shù)字格式設(shè)置為“文本”。3.數(shù)據(jù)有效性設(shè)置在C1單元格中輸入身份證號(hào)碼時(shí)，必須輸入18位字符，可以將其有效性設(shè)置為“允許的文本長度為

9、18”。課堂考勤成績計(jì)算知識(shí)點(diǎn)解析(2)4.公式與相對(duì)引用v李春的總工資等于B2和C2單元格的和，D2中輸入 “=B2+C2”。v“=B2+C2”是Excel中的公式，表明D2單元格引用了B2單元格的數(shù)據(jù)和C2單元格的數(shù)據(jù)，將他們的和作為自己單元格的內(nèi)容。v要計(jì)算許偉嘉的總工資，可以將D2單元格的內(nèi)容復(fù)制到D3單元格?？梢钥吹?，D3單元格的公式為“=B3+C3”。v在進(jìn)行公式復(fù)制時(shí)，公式中引用的地址發(fā)生了變化，這是因?yàn)橐肂3和C3單元格時(shí)，使用的是“相對(duì)引用”，在此公式中，B3和C3稱之為相對(duì)地址。相對(duì)引用是指當(dāng)把公式復(fù)制到其它單元格時(shí)，公式中引用的單元格或單元格區(qū)域中代表行的數(shù)字和代表列的

10、字母會(huì)根據(jù)實(shí)際的偏移量相應(yīng)改變。D2中的公式“=B2+C2”表明“D2的值是等于當(dāng)前單元格的前2列單元格加上前1列列單元格的和”，因此，將D2單元格中的公式復(fù)制到D3后，系統(tǒng)認(rèn)為“D3的值也應(yīng)該等于當(dāng)前單元格的前2列單元格加上前1列單元格的值”，因此公式自動(dòng)變化為“=B3+C3”。課堂考勤成績計(jì)算知識(shí)點(diǎn)解析(3)5.函數(shù)與統(tǒng)計(jì)函數(shù)COUNTAv要統(tǒng)計(jì)A1:A4中非空單元格的個(gè)數(shù)，可以在B3單元格輸入函數(shù)“=COUNTA(A1:A4)”，則B3單元格中的內(nèi)容為3，即表示A1:A4中有3個(gè)非空的單元格。vCOUNTA函數(shù)返回參數(shù)列表中非空值的單元格個(gè)數(shù)。COUNTA函數(shù)的語法格式為：COUNTA(

11、value1,value2,.)Value1, value2, . 為所要計(jì)算的值，參數(shù)個(gè)數(shù)為 1 到 255個(gè)，代表要計(jì)數(shù)的值、單元格或單元格區(qū)域。vExcel函數(shù)是系統(tǒng)預(yù)先定義的，使用一些稱為參數(shù)的特定數(shù)值來執(zhí)行計(jì)算、分析等任務(wù)的特殊公式，并返回一個(gè)或多個(gè)值?？梢杂煤瘮?shù)進(jìn)行一系列運(yùn)算。課堂考勤成績計(jì)算知識(shí)點(diǎn)解析(4)6. IF函數(shù)v假設(shè)公司本月的績效工資與出勤有關(guān)：全勤員工的績效工資為2520元非全勤員工的績效工資為2000元。v為了計(jì)算“李春”的績效工資，需要根據(jù)其出勤情況（B2）來進(jìn)行判斷，如果B2中為“全勤”，其績效工資（C3）為2520，否則為2000。v可以使用IF函數(shù)來實(shí)現(xiàn)。I

12、F函數(shù)的語法格式為：IF(Logical_test, Value_if_true, Value_if_false)（1）Logical_test：邏輯表達(dá)式。（2）Value_if_true：如果邏輯表達(dá)式的值為真，則返回該值。（3）Value_if_false：如果邏輯表達(dá)式的值為假，則返回該值。v其執(zhí)行過程是：（1）如果Logical_test的結(jié)果為TRUE（真），就返回Value_if_true的值作為結(jié)果。（2）如果Logical_test的結(jié)果為FALSE（假），則返回Value_if_false的值作為結(jié)果。v Logical_test是計(jì)算結(jié)果可能為TRUE或FALSE的任意值

13、或表達(dá)式。 Logical_test可使用的比較運(yùn)算符有：=（等于）、（大于）、=（大于等于）、=（小于等于）以及（不等號(hào)）。 v“李春”的績效工資對(duì)應(yīng)的函數(shù)是“=IF(B2=全勤,2520,2000)”。v思考并完成“許偉嘉”的績效工資。課堂考勤成績計(jì)算知識(shí)點(diǎn)解析(5)7. 條件格式v將單元格區(qū)域A1:D1所有大于100的單元格用紅色字體標(biāo)注出來。v如果你是一名教師，在分析考卷時(shí)，經(jīng)常要進(jìn)行的工作有：排名前20%的學(xué)生是誰，排名后20%的學(xué)生是誰？ 低于平均值的學(xué)生是誰？如果你是一名銷售經(jīng)理，你關(guān)心的可能會(huì)是：在過去五年的利潤匯總中，有哪些異常情況？過去兩年的營銷調(diào)查反映出哪些傾向？這個(gè)月誰

14、的銷售額超過￥50,000？哪種產(chǎn)品賣得最好？v在某些時(shí)候，需要將一些特別的數(shù)據(jù)突出顯示出來。Excel的條件格式有助于快速完成上述要求，它可以預(yù)置一種單元格格式，并在指定的某種條件被滿足時(shí)自動(dòng)應(yīng)用于目標(biāo)單元格?？深A(yù)置的單元格包括：邊框、底紋、字體顏色等。課堂考勤成績計(jì)算任務(wù)實(shí)現(xiàn)（1）1. 任務(wù)分析完成課堂考勤成績計(jì)算，要求：（1）將所有學(xué)生名單復(fù)制到 “課堂考勤成績”工作表中，并不得改變工作表的格式。（2）編排學(xué)生學(xué)號(hào)，起始學(xué)生的學(xué)號(hào)為“01301001”，學(xué)號(hào)自動(dòng)加1遞增。（3）出勤區(qū)域的數(shù)據(jù)輸入只允許為遲到、請(qǐng)假或曠課，并將考勤數(shù)據(jù)復(fù)制到工作表中。（4）計(jì)算學(xué)生的缺勤次數(shù)。（5）計(jì)算學(xué)生

15、的出勤成績。計(jì)算出勤成績=100-缺勤次數(shù)10。（6）計(jì)算學(xué)生的實(shí)際出勤成績。如果學(xué)生缺勤達(dá)到5次以上（不含5次），實(shí)際出勤成績計(jì)為0分，否則實(shí)際出勤成績等于計(jì)算出勤成績。（7）將80分（不含80）以下的實(shí)際出勤成績用“淺紅填充色深紅色文本”特別標(biāo)注出來。（8）隱藏工作表的網(wǎng)格線，設(shè)置出勤表打印在一頁A4紙上，并水平和垂直居中。課堂考勤成績計(jì)算任務(wù)實(shí)現(xiàn)（2）2.實(shí)現(xiàn)過程 請(qǐng)參照書本完成，有問題請(qǐng)隨時(shí)舉手示意老師。課堂考勤成績計(jì)算總結(jié)與提高（1） 1.數(shù)據(jù)類型v 系統(tǒng)默認(rèn)的單元格數(shù)據(jù)類型為“常規(guī)”，在輸入內(nèi)容后，系統(tǒng)則會(huì)根據(jù)單元格中的內(nèi)容，自動(dòng)判斷數(shù)據(jù)類型。例如，如果A1的類型為“常規(guī)”，在其輸

16、入“43230219950110001”，系統(tǒng)會(huì)自動(dòng)判斷其為數(shù)字型，因此將其以“4.323E+16”的科學(xué)計(jì)數(shù)數(shù)據(jù)顯示。v 可以在輸入數(shù)據(jù)時(shí)通過前導(dǎo)符等形式，限制輸入的數(shù)據(jù)類型。數(shù)據(jù)類型數(shù)據(jù)類型錄入說明錄入說明示例示例文本文本（1）當(dāng)輸入的文本超出單元格的寬度時(shí)，需要調(diào)整單元格的寬度才能完整顯示。（2）輸入數(shù)字型文本時(shí)，可以在數(shù)字前輸入單引號(hào)“”。輸入身份證號(hào)碼：43230219950110001數(shù)值數(shù)值（1）輸入“3/4”時(shí)，系統(tǒng)自動(dòng)將其轉(zhuǎn)化為日期3月4日。（2）要輸入分?jǐn)?shù)，需要在其前面輸入0和空格。輸入分?jǐn)?shù)：0 3/4日期日期年月日之間用“/”或“-”號(hào)分隔。輸入日期：2014/3/21時(shí)

17、間時(shí)間時(shí)分秒間用“：”分隔。輸入時(shí)間：12：32：05課堂考勤成績計(jì)算總結(jié)與提高（2）2.選擇性粘貼v 在Excel2010中，復(fù)制內(nèi)容之后，在目標(biāo)單元格上單擊鼠標(biāo)右鍵，在選擇性粘貼右側(cè)有個(gè)箭頭，單擊箭頭會(huì)出現(xiàn)選擇性粘貼的所有粘貼方式，當(dāng)鼠標(biāo)停在某個(gè)粘貼選項(xiàng)上時(shí)，在單元格中會(huì)出現(xiàn)最終粘貼樣式的預(yù)覽。粘貼：將源區(qū)域中的所有內(nèi)容、格式、條件格式、數(shù)據(jù)有效性、批注等全部粘貼到目標(biāo)區(qū)域。公式：僅粘貼源區(qū)域中的文本、數(shù)值、日期及公式等內(nèi)容。公式和數(shù)字格式：除粘貼源區(qū)域內(nèi)容外，還包含源區(qū)域的數(shù)值格式。數(shù)字格式包括貨幣樣式、百分比樣式、小數(shù)點(diǎn)位數(shù)等。無邊框：粘貼全部內(nèi)容，僅去掉源區(qū)域中的邊框。值：將文本、數(shù)

18、值、日期及公式結(jié)果粘貼到目標(biāo)區(qū)域，不復(fù)制格式。格式：僅復(fù)制源區(qū)域中的格式，而不包括內(nèi)容。課堂考勤成績計(jì)算總結(jié)與提高（3）3.自動(dòng)填充v 使用Excel提供的自動(dòng)填充功能，可以極大地減少數(shù)據(jù)輸入的工作量。通過拖動(dòng)填充柄，就可以激活自動(dòng)填充功能。利用自動(dòng)填充功能可以進(jìn)行文本、數(shù)字、日期、公式等序列的填充和數(shù)據(jù)的復(fù)制。（1）自動(dòng)填充或復(fù)制，除了前面介紹的拖動(dòng)鼠標(biāo)方式外，也可以在鼠標(biāo)變?yōu)楹谑謺r(shí)，雙擊鼠標(biāo)左鍵來實(shí)現(xiàn)自動(dòng)填充。（2）也可以在鼠標(biāo)變?yōu)楹谑謺r(shí)，單擊鼠標(biāo)右鍵，拖動(dòng)鼠標(biāo)到需要填充的最后位置，松開鼠標(biāo)，在彈出的快捷菜單中選擇需要的填充模式來實(shí)現(xiàn)自動(dòng)填充。（3）除了可以根據(jù)選擇的單元格或單元格區(qū)域

19、自動(dòng)填充外，還可以通過“開始”選項(xiàng)卡/“編輯”組/“填充”按鈕 /“系列”來實(shí)現(xiàn)等差、等比等序列的自動(dòng)填充。課堂考勤成績計(jì)算總結(jié)與提高（4）4.數(shù)據(jù)有效性v 將A1單元格的輸入范圍為限定為0100，當(dāng)輸入錯(cuò)誤時(shí)，給出“成績范圍為0100！”的提示，同時(shí)取消輸入或要求重新輸入。v 還可以將單元格區(qū)域的內(nèi)容作為有效性輸入的數(shù)據(jù)限制。課堂考勤成績計(jì)算總結(jié)與提高（5）5.條件格式（1）v 對(duì)同一個(gè)單元格（或單元格區(qū)域），如果應(yīng)用有兩個(gè)或兩個(gè)以上的不同條件格式，這些條件可能沖突，也可能不沖突：（1）規(guī)則不沖突：例如，如果一個(gè)規(guī)則將單元格格式設(shè)置為字體加粗，而另一個(gè)規(guī)則將同一個(gè)單元格的格式設(shè)置為紅色，則該

20、單元格格式設(shè)置為字體加粗且為紅色。因?yàn)檫@兩種格式間沒有沖突，所以兩個(gè)規(guī)則都得到應(yīng)用。（2）規(guī)則沖突：例如，一個(gè)規(guī)則將單元格字體顏色設(shè)置為紅色，而另一個(gè)規(guī)則將單元格字體顏色設(shè)置為綠色。因?yàn)檫@兩個(gè)規(guī)則沖突，所以只應(yīng)用一個(gè)規(guī)則，應(yīng)用優(yōu)先級(jí)較高的規(guī)則。v 在設(shè)置多條件的條件格式時(shí)，要充分考慮各條件之間的設(shè)置順序。若要調(diào)整條件格式的先后順序或編輯條件格式，可以通過“條件格式”的“管理規(guī)則”來實(shí)現(xiàn)。如果想刪除單元格或工作表的所有條件格式，可以通過“條件格式”的“清除規(guī)則”來實(shí)現(xiàn)。課堂考勤成績計(jì)算總結(jié)與提高（6）5.條件格式（2）v 可以通過公式來設(shè)定單元格條件格式。選擇“課堂考勤成績”工作表的姓名單元格區(qū)

21、域B5:B40，單擊“開始”選項(xiàng)卡/“格式”組/“條件格式”按鈕/“新建規(guī)則”。在彈出的 “新建格式規(guī)則”對(duì)話框中，選擇規(guī)則類型為“使用公式確定要設(shè)置格式的單元格”，在編輯規(guī)則中輸入公式“=N580”（表示條件為“計(jì)算考勤成績80”），選擇“格式”，設(shè)置為“紅色，加粗”，單擊確定按鈕?？梢钥吹?，計(jì)算考勤成績?cè)?0分以下的姓名就以“紅色、加粗”突出顯示了。其中，公式“=N5100”，公式為“=IF(B3100,GOOD,BAD)”。（2）如果B4單元格中的數(shù)據(jù)大于100并且小于等于200，C4單元格返回結(jié)果為1，否則為0。其對(duì)應(yīng)的邏輯表達(dá)式為“AND(B4100,B4100,B4=200),1,

22、0)”。（3）如果B5單元格中的數(shù)據(jù)小于等于100或者大于200，C5單元格返回結(jié)果為“真”，否則為“假”。其對(duì)應(yīng)的邏輯表達(dá)式為“OR(B5200)”，公式為“=IF(OR(B5200),真,假)”。課程成績計(jì)算知識(shí)點(diǎn)解析（1）1. 絕對(duì)引用（1）v 李明的虛擬貨幣數(shù)=B4*C2，陳思欣的虛擬貨幣數(shù)=B5*C2，彭培斯的虛擬貨幣數(shù)=B11*C2，即某人對(duì)應(yīng)的虛擬貨幣數(shù)=他所持有的游戲幣數(shù)*C2。在進(jìn)行公式復(fù)制時(shí)，必須保證C2的引用不隨公式位置的變化而變化。v 因此需要使用絕對(duì)地址$C$2。課程成績計(jì)算知識(shí)點(diǎn)解析（2）1. 絕對(duì)引用（2）v 如果希望公式中引用的單元格或單元格區(qū)域不隨公式位置的變

23、化而變化，公式中引用的單元格或單元格區(qū)域需要使用絕對(duì)地址，而這種引用稱之為絕對(duì)引用。v 絕對(duì)引用是指當(dāng)把公式復(fù)制到其它單元格時(shí)，公式中引用的單元格或單元格區(qū)域中行和列的引用不會(huì)改變。絕對(duì)引用的單元格名稱中其行和列之前均會(huì)加上$以示區(qū)分，如$A$4表示絕對(duì)引用A4單元格，$A$4稱之為絕對(duì)地址。課程成績計(jì)算知識(shí)點(diǎn)解析（3）2. 四舍五入函數(shù)ROUNDv ROUND函數(shù)返回某個(gè)數(shù)字按指定位數(shù)取整后的數(shù)字。ROUND函數(shù)的語法格式為ROUND(Number,Num_digits)。 Number：需要進(jìn)行四舍五入的數(shù)字。 Num_digits：指定的位數(shù)，按此位數(shù)進(jìn)行四舍五入。如果Num_digit

24、s 大于0，則四舍五入到指定的小數(shù)位。如果Num_digits 等于 0，則四舍五入到最接近的整數(shù)。如果Num_digits 小于0，則在小數(shù)點(diǎn)左側(cè)進(jìn)行四舍五入。例如，ROUND(7.351,0)的結(jié)果是7，ROUND(7.351,1)的結(jié)果是7.4，ROUND(7.351,-1)的結(jié)果是10。課程成績計(jì)算知識(shí)點(diǎn)解析（4）3. IF函數(shù)嵌套（1）v假設(shè)需要根據(jù)游戲積分來進(jìn)行獎(jiǎng)勵(lì)，獎(jiǎng)勵(lì)的規(guī)則是：如果游戲積分大于等于20000分，則獎(jiǎng)勵(lì)積分1000分。如果游戲積分大于等于15000分，則獎(jiǎng)勵(lì)積分500分，否則獎(jiǎng)勵(lì)積分0分。v要實(shí)現(xiàn)上述計(jì)算，可以用IF函數(shù)嵌套來實(shí)現(xiàn)多條件判斷。第1層IF第2層IF進(jìn)

25、入該分支，表示B315000（B3肯定也20000）進(jìn)入該分支，表示B3=20000進(jìn)入該分支，表示B3=15000課程成績計(jì)算知識(shí)點(diǎn)解析（5）3. IF函數(shù)嵌套（2）v李明的游戲積分（C3單元格）里的公式是“=IF(B3=20000,1000,IF(B3=15000,500,0)”，計(jì)算出來的獎(jiǎng)勵(lì)積分為500。課程成績計(jì)算知識(shí)點(diǎn)解析（6）4. 排位函數(shù)RANK.EQ（1）vRANK.EQ 函數(shù)的功能是返回一個(gè)數(shù)字在數(shù)據(jù)區(qū)域中的排位。其排位大小與數(shù)據(jù)區(qū)域中的其他值相關(guān)。如果多個(gè)值具有相同的排位，則返回該組數(shù)值的最高排位。vRANK.EQ 函數(shù)的語法格式為RANK.EQ(Number ,Ref

26、,Order)Number：需要找到排位位置的數(shù)字或單元格。Ref：需要參與排位的數(shù)據(jù)區(qū)域，Ref 中的非數(shù)值型值將被忽略。Order：如果 order 為 0或省略，是按降序排位，否則為升序排位。v要計(jì)算B3單元格在B3:B10單元格區(qū)域中排在第幾位（通常，我們認(rèn)為數(shù)值大的排位為1，因此，該排序應(yīng)該是降序排序），其對(duì)應(yīng)的參數(shù)分別是：Number為B3，表示要找到B3的排位位置。Ref為B3:B10區(qū)域，表示是要找某一個(gè)單元格在B3:B10區(qū)域中的排位。Order為0，表示降序。需要特別注意的是，由于每個(gè)人的積分排名都是在B3:B10單元格區(qū)域中比較，因此Ref參數(shù)需要用絕對(duì)地址$B$3:$B

27、$10。課程成績計(jì)算知識(shí)點(diǎn)解析（6）4. 排位函數(shù)RANK.EQ（2）v C3單元格對(duì)應(yīng)的公式為“=RANKD.EQ(B3, $B$3:$B$10,0)”。v 函數(shù) RANK.EQ 對(duì)重復(fù)數(shù)的排位相同。但重復(fù)數(shù)的存在將影響后續(xù)數(shù)值的排位。例如B3和B6單元格的數(shù)值一樣，它們對(duì)應(yīng)的排位都為 4，B9單元格的排位則為6（沒有排位為5的數(shù)值）。課程成績計(jì)算知識(shí)點(diǎn)解析（7）5. 復(fù)雜條件格式與混合引用v 將排行榜（C列）前3名的整行數(shù)據(jù)（A列至C列）都用“灰色背景”標(biāo)識(shí)出來.第3行數(shù)據(jù)（A3:C3）的所有單元格對(duì)應(yīng)的條件格式可以描述為：如果第3行中的第C列單元格中的數(shù)據(jù)=3（C3=3），那么對(duì)該單元格

28、應(yīng)用“灰色背景”格式。第4行數(shù)據(jù)（A4:C4）的所有單元格對(duì)應(yīng)的條件格式可以描述為：如果第4行中的第C列單元格中的數(shù)據(jù)=3（C4=3），那么對(duì)該單元格應(yīng)用“灰色背景”格式。v從以上分析可以知道，對(duì)第3到第10行（A3:C10）的所有單元格對(duì)應(yīng)的條件格式的公式為“=$C3=3”。在該條件表達(dá)式中，地址“$C3”為混合引用單元格地址。v混合引用是指單元格地址中既有相對(duì)引用，也有絕對(duì)引用?！?C3”標(biāo)識(shí)具有絕對(duì)列C和相對(duì)行，當(dāng)公式在復(fù)制或移動(dòng)時(shí)，保持列不變而行變化。v在用條件“=$C3=3”對(duì)A3:C10的單元格進(jìn)行條件格式判斷時(shí)，A3、B3、C3（第3行）單元格的格式判斷條件為“C3單元格的內(nèi)容是

29、否=3”，在A8、B8、C8單元格（第8行）單元格的格式判斷條件為“C8單元格的內(nèi)容是否=3”。課程成績計(jì)算知識(shí)點(diǎn)解析（7）5. 復(fù)雜條件格式與混合引用v 將排行榜（C列）前3名的整行數(shù)據(jù)（A列至C列）都用“灰色背景”標(biāo)識(shí)出來.第3行數(shù)據(jù)（A3:C3）的所有單元格對(duì)應(yīng)的條件格式可以描述為：如果第3行中的第C列單元格中的數(shù)據(jù)=3（C3=3），那么對(duì)該單元格應(yīng)用“灰色背景”格式。第4行數(shù)據(jù)（A4:C4）的所有單元格對(duì)應(yīng)的條件格式可以描述為：如果第4行中的第C列單元格中的數(shù)據(jù)=3（C4=3），那么對(duì)該單元格應(yīng)用“灰色背景”格式。v從以上分析可以知道，對(duì)第3到第10行（A3:C10）的所有單元格對(duì)應(yīng)的

30、條件格式的公式為“=$C3=3”。在該條件表達(dá)式中，地址“$C3”為混合引用單元格地址。v混合引用是指單元格地址中既有相對(duì)引用，也有絕對(duì)引用?！?C3”標(biāo)識(shí)具有絕對(duì)列C和相對(duì)行，當(dāng)公式在復(fù)制或移動(dòng)時(shí)，保持列不變而行變化。v在用條件“=$C3=3”對(duì)A3:C10的單元格進(jìn)行條件格式判斷時(shí)，A3、B3、C3（第3行）單元格的格式判斷條件為“C3單元格的內(nèi)容是否=3”，在A8、B8、C8單元格（第8行）單元格的格式判斷條件為“C8單元格的內(nèi)容是否=3”。課程成績計(jì)算知識(shí)點(diǎn)解析（8）6. 單元格保護(hù)v 為了防止別人對(duì)工作表的誤操作，可以設(shè)置保護(hù)工作表。但由于默認(rèn)情況下，一旦設(shè)置了保護(hù)工作表，所有單元格

31、都不能被編輯。因此，在必要的時(shí)候，可以通過設(shè)置單元格的鎖定狀態(tài)，來確定哪些單元格不允許被編輯。例如，為了防止對(duì)某些單元格（如G6:G41）進(jìn)行編輯，需要：（1）先將所有單元格設(shè)置為不鎖定。（2）將不允許編輯的單元格（G6:G41）設(shè)置為鎖定。v 這樣，在保護(hù)工作表后，就可以對(duì)未被鎖定的單元格進(jìn)行編輯了。課程成績計(jì)算任務(wù)實(shí)現(xiàn)（1）課程成績計(jì)算任務(wù)實(shí)現(xiàn)（2）2.實(shí)現(xiàn)過程 請(qǐng)參照書本完成，有問題請(qǐng)隨時(shí)舉手示意老師。課程成績計(jì)算總結(jié)與提高（1）1. 數(shù)字格式設(shè)置Excel單元格中的數(shù)據(jù)，總是以某一種形式存在，這稱之為數(shù)字格式。Excel中常用的數(shù)字格式有：（1）常規(guī)：這種格式的數(shù)字通常以鍵入的方式顯示

32、。如果單元格的寬度不夠顯示整個(gè)數(shù)字，常規(guī)格式會(huì)用小數(shù)點(diǎn)對(duì)數(shù)字進(jìn)行四舍五入，常規(guī)數(shù)字格式還對(duì)較大的數(shù)字（12 位或更多位）使用科學(xué)計(jì)數(shù)（指數(shù)）表示法。（2）數(shù)值：這種格式用于數(shù)字的一般表示?？梢灾付ㄒ褂玫男?shù)位數(shù)、是否使用千位分隔符以及如何顯示負(fù)數(shù)。（3）貨幣：這種格式用于一般貨幣值并顯示帶有數(shù)字的默認(rèn)貨幣符號(hào)?？梢灾付ㄒ褂玫男?shù)位數(shù)、是否使用千位分隔符以及如何顯示負(fù)數(shù)。（4）會(huì)計(jì)專用：這種格式也用于貨幣值，但是它會(huì)在一列中對(duì)齊貨幣符號(hào)和數(shù)字的小數(shù)點(diǎn)。（5）日期或時(shí)間：這種格式會(huì)根據(jù)指定的類型和區(qū)域設(shè)置（國家/地區(qū)），將日期和時(shí)間系列數(shù)值顯示為日期值或時(shí)間值。（6）百分比：這種格式以百分?jǐn)?shù)形

33、式顯示單元格的值，可以指定要使用的小數(shù)位數(shù)。（7）分?jǐn)?shù)：這種格式會(huì)根據(jù)指定的分?jǐn)?shù)類型以分?jǐn)?shù)形式顯示數(shù)字。（8）科學(xué)記數(shù)：這種格式以指數(shù)表示法顯示數(shù)字。（9）文本：這種格式將單元格的內(nèi)容視為文本，即使鍵入數(shù)字。（10）特殊：這種格式將數(shù)字顯示為郵政編碼、中文小寫數(shù)字或中文大寫數(shù)字。在“開始”選項(xiàng)卡/“數(shù)字”組中提供了若干常用的數(shù)字格式按鈕，通過它們可以快速設(shè)置被選定單元格的數(shù)字格式：（1）貨幣格式 ：將單元格的數(shù)字設(shè)置為會(huì)計(jì)專用格式，并自動(dòng)加上選擇的貨幣符號(hào)。（2）百分比格式 ：將原數(shù)字乘以100后，再在數(shù)字后加上百分號(hào)。（3）會(huì)計(jì)分隔符格式 ：每3位數(shù)字以“，”隔開，并添加兩位小數(shù)。課程成績計(jì)

34、算總結(jié)與提高（2）2. 函數(shù)ROUND、ROUNDUP與ROUNDDOWNv ROUND函數(shù)：按指定位數(shù)對(duì)數(shù)字進(jìn)行四舍五入。如輸入=round(3.158，2），則會(huì)出現(xiàn)數(shù)字3.16，即按兩位小數(shù)進(jìn)行四舍五入。 v ROUNDUP函數(shù)：按指定位數(shù)向上舍入指定位數(shù)后面的小數(shù)。如輸入=roundup(3.152，2），則會(huì)出現(xiàn)數(shù)字3.16，將兩位小數(shù)后的數(shù)字舍上去。v ROUNDDOWN函數(shù)：按指定位數(shù)舍去數(shù)字指定位數(shù)后面的小數(shù)。如輸入=rounddown(3.158，2,）則會(huì)出現(xiàn)數(shù)字3.15，將兩位小數(shù)后的數(shù)字全部舍掉。 課程成績計(jì)算總結(jié)與提高（3）3. IF函數(shù)嵌套v在IF函數(shù)嵌套時(shí)，不要出

35、現(xiàn)類似15000=B3或大于等于=。（3）如果條件判斷為從小到大，通常使用的比較運(yùn)算符為小于或小于等于=20000,1000,IF(15000=B3=15000,500,IF(B5=20000,1000,0)的結(jié)果課程成績計(jì)算總結(jié)與提高（4）4. 公式審核v為了查看公式的執(zhí)行過程，可以通過“公式”選項(xiàng)卡/“公式審核”組的“公式求值”功能來實(shí)現(xiàn)。例如，查看C5中公式 “=IF(B5=15000,500,IF(B5=20000,1000,0)”的執(zhí)行過程的步驟是：選擇C5單元格，單擊“公式”選項(xiàng)卡/“公式審核”組的“公式求值”按鈕，彈出的“公式求值”對(duì)話框。通過單擊“公式求值”對(duì)話框的“求值”按鈕

36、，可以看到公式的每個(gè)執(zhí)行步驟。v請(qǐng)參見老師的具體操作過程。課程成績統(tǒng)計(jì)知識(shí)點(diǎn)解析（1）1. 套用表格格式vExcel提供了自動(dòng)格式化的功能，它可以根據(jù)預(yù)設(shè)的格式，將選擇的單元格區(qū)域進(jìn)行格式化。這些被格式化的單元格區(qū)域稱之為表格。表格具有數(shù)據(jù)篩選、排序、匯總和計(jì)算等多項(xiàng)功能，并能自動(dòng)擴(kuò)展數(shù)據(jù)區(qū)域，構(gòu)造動(dòng)態(tài)報(bào)表等。v通常，可以先套用表格的預(yù)定義格式來格式化工作表，然后用手工方式對(duì)其中不太滿意的部分進(jìn)行修改，就可以快速完成工作表的格式化。v請(qǐng)參見老師具體操作。2. 統(tǒng)計(jì)函數(shù)MAX、MIN與AVERAGEvMAX函數(shù)是求最大值函數(shù)，例如用來計(jì)算學(xué)生最高成績、員工最高工資等。MAX函數(shù)語法格式為：MAX

37、(number1,number2,.)。其中，number1,number2,.可以是具體的數(shù)值、單元格或單元格區(qū)域等。vMIN函數(shù)是求最小值函數(shù)，MIN函數(shù)語法格式為：MIN(number1,number2,.)。其中，number1,number2,.可以是具體的數(shù)值、單元格或單元格區(qū)域等。vAVERAGE函數(shù)是求平均數(shù)函數(shù)。AVERAGE函數(shù)語法格式為：AVERAGE (number1,number2,.)。其中，number1,number2,.可以是具體的數(shù)值、單元格或單元格區(qū)域等。需要注意的是，number1,number2,.中為空的單元格不會(huì)被計(jì)算，但為0的單元格會(huì)被計(jì)算。課程

38、成績統(tǒng)計(jì)知識(shí)點(diǎn)解析（2）3. 條件統(tǒng)計(jì)函數(shù)COUNTIFv要統(tǒng)計(jì)游戲積分16000的人數(shù)，可以按照如下方式來進(jìn)行手動(dòng)實(shí)現(xiàn)：（1）確定要查找的單元格區(qū)域（B3:B10）。（2）將游戲積分16000的單元格找出來。（3）對(duì)找出來的單元格進(jìn)行計(jì)數(shù)，得到的值就是要統(tǒng)計(jì)的游戲積分16000的人數(shù)。v采用COUNTIF函數(shù)來完成。COUNTIF函數(shù)的語法格式為COUNTIF(Range,Criteria) Range為要統(tǒng)計(jì)的單元格區(qū)域（即第1步中確定的單元格區(qū)域）Criteria為指定的條件（即第2步中給定的條件）得到符合條件的單元格計(jì)數(shù)的結(jié)果（即第3步中的單元格計(jì)數(shù)）。vB11單元格中的公式為“=CO

39、UNTIF(B3:B10, 16000)”。其含義是：對(duì)B3:B10單元格區(qū)域中滿足條件=16000, B3:B10,=15000的單元格繼續(xù)找出B3:B10區(qū)域中81”，而不是引用的B5單元格的數(shù)據(jù)。一旦某個(gè)學(xué)生的成績發(fā)生改變，就會(huì)引起平均分的變化，從而需要修改公式的參數(shù)值。這顯然達(dá)不到所期望的自動(dòng)計(jì)算的效果。v那么，是否可以將參數(shù)Creteria改為“B5”呢？一旦B6單元格中的公式變?yōu)椤?COUNTIF(課程成績!I6:I41,B5)”，得到的結(jié)果就變?yōu)?了，顯然是錯(cuò)誤的。通過“公式審核”功能，可以看到條件不是所期望的“大于B5單元格的值”，而是“大于B5”。v因此，如果希望參數(shù)Cret

40、eria實(shí)現(xiàn)“大于B5單元格的值”，條件應(yīng)該為“&B5”，B6中的公式為“=COUNTIF(課程成績!I6:I41,&B5)”。在公式執(zhí)行時(shí)，首先取B5中的值，然后將其與“”進(jìn)行連接，形成“81”，作為參數(shù)Creteria的值。其中，&為連接符，實(shí)現(xiàn)將2個(gè)數(shù)據(jù)連接在一起。課程成績統(tǒng)計(jì)總結(jié)與提高（2）2. 公式審核v可以通過“追蹤引用單元格”和“追蹤從屬單元格”，可以查看公式引用是否正確。通過“追蹤引用單元格”，工作表當(dāng)中出現(xiàn)藍(lán)色箭頭，此箭頭說明選中單元格的公式所引用的單元格，還可以繼續(xù)通過“追蹤引用單元格”，查看剛才被引用單元格中公式引用其他單元格的情況。通過“追蹤從屬

41、單元格”，可以查看選中單元格中的值將會(huì)影響到哪些單元格，還可以繼續(xù)通過“追蹤從屬單元格”，查看剛才被影響的單元格繼續(xù)影響到其他的單元格中的值。如可以通過“移去箭頭”刪除追蹤引用。v請(qǐng)參見老師具體操作。課程成績統(tǒng)計(jì)總結(jié)與提高（3）3. 用名稱簡化單元格區(qū)域的引用v在進(jìn)行總評(píng)成績計(jì)算時(shí)時(shí)，需要頻繁使用到 “課程成績”工作表的單元格區(qū)域I6:I41，在引用過程中很容易出錯(cuò)。此外，對(duì)于公式“=MAX(課程成績!I6:I41)”也無法看出是計(jì)算總評(píng)成績中的最高分。v因此，可以將“課程成績”工作表的單元格區(qū)域I6:I41命名為“所有學(xué)生總評(píng)成績”，這樣，在需要引用“課程成績”工作表的單元格區(qū)域I6:I41

42、的地方，就可以直接用“所有學(xué)生總評(píng)成績”來替代，從而簡化單元格區(qū)域的引用。如計(jì)算總評(píng)成績中的最高分的公式就可以寫成“=MAX(所有學(xué)生總評(píng)成績)”，簡單明了。課程成績統(tǒng)計(jì)總結(jié)與提高（4）4. 數(shù)據(jù)圖表的類型vExcel 的圖表類型包括：（1）柱形圖：柱形圖通常用于顯示一段時(shí)間內(nèi)的數(shù)據(jù)變化或說明各項(xiàng)之間的比較情況。（2）折線圖：線圖通常用于顯示隨時(shí)間而變化的連續(xù)數(shù)據(jù)。（3）餅圖：餅圖通常用于顯示一個(gè)數(shù)據(jù)系列中各項(xiàng)所占的比例。（4）條形圖：條形圖通常用于比較各項(xiàng)之間的情況。（5）面積圖（6）散點(diǎn)圖（7）股價(jià)圖（8）曲面圖（9）圓環(huán)圖（10）氣泡圖（11）雷達(dá)圖課程成績統(tǒng)計(jì)總結(jié)與提高（5）v5. 圖

43、表工具v在選擇圖表后，會(huì)出現(xiàn)“圖表工具”功能區(qū)，圖表工具功能區(qū)又包括有設(shè)計(jì)、布局和格式3個(gè)子功能區(qū)。設(shè)計(jì)子功能區(qū)主要用于更改圖表類型、變更產(chǎn)生圖表的數(shù)據(jù)區(qū)域、切換圖表的縱橫向坐標(biāo)以及圖表的顯示樣式等。布局子功能區(qū)主要用于在圖表中插入文字及圖片、更改圖表標(biāo)簽（如圖表標(biāo)題、坐標(biāo)軸標(biāo)題等）、更改坐標(biāo)軸格式以及為圖表添加趨勢(shì)線等。格式子功能區(qū)用于設(shè)置圖表形狀、圖表文字格式以及圖表大小等。制作成績通知單知識(shí)點(diǎn)解析（知識(shí)點(diǎn)解析（1 1）1. Word的郵件合并功能v“郵件合并”是將一組變化的信息（如每個(gè)學(xué)生的姓名，總評(píng)成績等）逐條插入到一個(gè)包含有模板內(nèi)容的Word文檔（如未填寫的成績通知單）中，從而批量生成需要的文檔，大大提高工作效率。v包含有模板內(nèi)容的Word文檔稱為郵件文檔（也稱為主文檔），而包含變化信息的文件稱為數(shù)據(jù)源（也稱為收件人），數(shù)據(jù)源可以是Word及Excel表格、Access數(shù)據(jù)表等。v郵件合并功能主要用于批量填寫格式相同、只需修改少數(shù)內(nèi)容的文檔。 “郵件合并” 除了可以批量處理信函、信封等與郵件相關(guān)的文檔外， 還可以輕松地批量制作工資條、準(zhǔn)考證等。制作成績通知單知識(shí)點(diǎn)解析（知識(shí)點(diǎn)解析