第5章EXCE基本應用課件

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

37、X(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,.中為空的單元格不會被計算，但為0的單元格會被計算。課

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

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

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

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

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

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