計算機應(yīng)用基礎(chǔ)第7章--Excel的基本應(yīng)用—制作成績表課件

1、計算機應(yīng)用基礎(chǔ)(第2版)國家精品課程配套教材 一版榮獲國家精品教材深圳職業(yè)技術(shù)學(xué)院第7章 Excel的基本應(yīng)用制作成績表22022/7/25學(xué)習(xí)目標掌握Excel中各種輸入數(shù)據(jù)的方法。熟練地進行各種公式計算及簡單函數(shù)的使用。掌握單元格格式設(shè)置、單元格數(shù)據(jù)的復(fù)制與粘貼。工作表的重命名、工作表的復(fù)制、移動、刪除及插入。熟練掌握單元格的三種引用方法。掌握數(shù)據(jù)的排序、篩選。第7章 Excel的基本應(yīng)用制作成績表32022/7/25目錄7.1 制作成績表案例分析 7.2 實現(xiàn)方法7.2.1 輸入單科成績表 7.2.2 由多工作表數(shù)據(jù)生成“各科成績表” 7.2.3 成績表的排序和篩選 7.3 案例總結(jié)7.

2、4 課后練習(xí)第7章 Excel的基本應(yīng)用制作成績表42022/7/257.1 制作成績表案例分析任務(wù)的提出 班主任王老師要制作計算機應(yīng)用課程的成績表及“各科成績表” 。解決方案通過數(shù)據(jù)輸入，新建計算機應(yīng)用課程的成績表。通過對工作表的復(fù)制、移動，單元格數(shù)據(jù)的粘貼等完成“各科成績表” 的制作。第7章 Excel的基本應(yīng)用制作成績表52022/7/257.2.1 輸入單科成績表工作簿的建立在工作表“Sheet1”中輸入數(shù)據(jù) 單元格計算 單元格格式設(shè)置 工作表的更名 第7章 Excel的基本應(yīng)用制作成績表62022/7/25工作簿的建立啟動Excel。Excel的工作界面如圖下所示。 第7章 Exce

3、l的基本應(yīng)用制作成績表72022/7/25在工作表“Sheet1”中輸入數(shù)據(jù)輸入“學(xué)號”列數(shù)據(jù) 諸如學(xué)號、郵編、電話號碼等應(yīng)視為文本數(shù)據(jù)，輸入時除了在數(shù)字前加上西文單引號“ ”外，也可以將單元格的格式設(shè)置為“文本”類型。利用填充柄自動填充，可完成“學(xué)號”列數(shù)據(jù)的輸入輸入“姓名”列數(shù)據(jù)對于多次重復(fù)使用的數(shù)據(jù)，可以用“自定義序列” 實現(xiàn)。輸入“性別”列數(shù)據(jù) 如果想在多個單元格中輸入相同的數(shù)據(jù)，只要選擇所有需要包含此信息的單元格，在輸入數(shù)值、文本或公式后，同時按住Ctrl+Enter鍵，同樣的信息會輸入到被選擇的所有單元格中。第7章 Excel的基本應(yīng)用制作成績表82022/7/25單元格計算 Ex

4、cel中的“公式”是指在單元格中執(zhí)行計算功能的等式。Excel中的所有公式都必須以“”號開頭，若無等號，Excel將其理解為正文，所以“”是公式中絕對不可缺少的一個運算符?！啊焙竺媸菂⑴c計算的運算數(shù)和運算符，每一個運算數(shù)可以是常量、單元格或區(qū)域引用、單元格名稱或函數(shù)等。Excel提供了四種類型的運算符：算術(shù)運算符、比較運算符、文本運算符和引用運算符。第7章 Excel的基本應(yīng)用制作成績表92022/7/25單元格格式設(shè)置命令單元格的格式設(shè)置，主要集中在“格式”“單元格”命令中，在“單元格格式”對話框中：“數(shù)字”選項卡，用于設(shè)置單元格中的不同數(shù)值類型。如：貨幣類型、文本類型、百分比類型等?！皩R

5、”選項卡，用于設(shè)置單元格中數(shù)據(jù)的對齊方式。如：水平，垂直對齊、合并單元格、自動換行等?！白煮w”選項卡，用于設(shè)置單元格中數(shù)據(jù)的“字體”、“字形”、“字號”、“顏色”等?！斑吙颉边x項卡，用于設(shè)置單元格或表格的邊框線。“圖案”選項卡，用于設(shè)置單元格的底紋。此外也可以通過“格式”工具欄實現(xiàn)單元格格式的快速設(shè)置。調(diào)整“行高”或“列寬”的命令，則可通過執(zhí)行“格式”“行”命令或“格式”“列”命令實現(xiàn)。第7章 Excel的基本應(yīng)用制作成績表102022/7/25單元格格式設(shè)置操作要求將標題字體設(shè)置為“幼圓”、“加粗”、“18”號，并使標題在A1:G1列合并及居中。將數(shù)據(jù)區(qū)域所有單元格的字號設(shè)置為“10”；垂直

6、對齊方式和水平對齊方式都設(shè)置為“居中”。將表格的外邊框設(shè)置為雙細線，內(nèi)邊框設(shè)置為單細線。為表格列標題區(qū)域添加淺綠色底紋，并設(shè)置垂直對齊方式和水平對齊方式為“居中”。將表格列標題行的行高設(shè)置為30.00（40像素），將“平時成績”列的列寬適當調(diào)窄。將列標題“平時成績、作業(yè)設(shè)計、期末考試”在單元格內(nèi)分兩行顯示。將平時成績、作業(yè)設(shè)計、期末考試三列的列寬調(diào)整為最適合的列寬。通過減少小數(shù)位數(shù)，將“總成績”的結(jié)果以整數(shù)位呈現(xiàn)。第7章 Excel的基本應(yīng)用制作成績表112022/7/25工作表的更名 將當前工作表的名稱“Sheet1”更名為“計算機應(yīng)用” 。雙擊工作表“Sheet1”的標簽；或鼠標指向工作表

7、標簽，單擊鼠標右鍵，從快捷菜單中選擇“重命名”命令。當工作表標簽出現(xiàn)反白（黑底白字）時，輸入新的工作表名“計算機應(yīng)用” ，按Enter鍵確認。第7章 Excel的基本應(yīng)用制作成績表122022/7/25由多工作表數(shù)據(jù)生成“各科成績表”工作表的復(fù)制及移動 工作表的刪除與插入 單元格數(shù)據(jù)的復(fù)制與粘貼 單元格數(shù)據(jù)的移動 函數(shù)及單元格引用 第7章 Excel的基本應(yīng)用制作成績表132022/7/25工作表的復(fù)制及移動將“大學(xué)英語.xls”工作簿中的“Sheet1”工作表，復(fù)制到“成績表.xls”工作簿中的“計算機應(yīng)用”工作表之前，并將復(fù)制后的目的工作表“Sheet1”更名為“大學(xué)英語” 。分別打開“大

8、學(xué)英語.xls”工作簿和“成績表.xls”工作簿。選擇“大學(xué)英語.xls”工作簿中的“Sheet1”工作表作為當前工作表（源表），在“Sheet1”工作表標簽上單擊鼠標右鍵，在彈出的快捷菜單中選擇“移動或復(fù)制工作表”命令，在“移動或復(fù)制工作表”對話框中進行如右圖所示的設(shè)置。第7章 Excel的基本應(yīng)用制作成績表142022/7/25工作表的刪除與插入 刪除“成績表.xls”工作簿中的工作表“Sheet2”、“Sheet3” 。單擊工作表“Sheet2”標簽，在菜單欄中選擇“編輯”“刪除工作表”命令，將“Sheet2”工作表從當前工作簿中刪除。在“成績表.xls”工作簿中的“大學(xué)英語”工作表之前

9、插入一張新的工作表。選定“大學(xué)英語”工作表。在菜單欄中選擇“插入”“工作表”命令，在當前“大學(xué)英語”工作表之前，添加一張新的工作表，同時新工作表成為當前工作表。第7章 Excel的基本應(yīng)用制作成績表152022/7/25單元格數(shù)據(jù)的復(fù)制與粘貼將“成績表.xls”工作簿中“計算機應(yīng)用”工作表的“學(xué)號”、“姓名”、“性別”以及“總成績”列的數(shù)據(jù)復(fù)制到“各科成績表”工作表中 。在“計算機應(yīng)用”工作表中，復(fù)制“總成績”列數(shù)據(jù)到“各科成績表”工作表中時，會出現(xiàn)什么結(jié)果？為什么？如何解決？第7章 Excel的基本應(yīng)用制作成績表162022/7/25選擇性粘貼的用途對于包含公式的單元格來說，通常具有“公式”

10、和“值”兩種屬性。在復(fù)制單元格時，可使用“選擇性粘貼”的方法，任意選擇是只粘貼“值”屬性，還是只粘貼“公式”屬性。此外使用“選擇性粘貼”還可以任意選擇只粘貼格式、批注、列寬等內(nèi)容。第7章 Excel的基本應(yīng)用制作成績表172022/7/25選擇性粘貼的使用方法利用選擇性粘貼，將“計算機應(yīng)用”工作表中“總成績”列的數(shù)據(jù)復(fù)制到“各科成績表”的目標單元格中，并分別將“應(yīng)用文寫作”列數(shù)據(jù)、“大學(xué)英語”列數(shù)據(jù)、“高等數(shù)學(xué)”列數(shù)據(jù)，復(fù)制到“各科成績表”中的相應(yīng)位置 。選擇要復(fù)制的工作表區(qū)域，并執(zhí)行復(fù)制命令。在菜單欄中選擇“編輯”“選擇性粘貼”命令，打開“選擇性粘貼”對話框。進行如右圖所示的設(shè)置。第7章 E

11、xcel的基本應(yīng)用制作成績表182022/7/25單元格數(shù)據(jù)的移動調(diào)整各列成績的排列順序。將各列成績的排列順序調(diào)整為“大學(xué)英語”、“計算機應(yīng)用”、“高等數(shù)學(xué)”、“應(yīng)用文寫作” 。方法一：選擇數(shù)據(jù)來源列。在菜單欄中選擇“編輯”“剪切”命令。選定目標單元格。在菜單欄中選擇“插入”“剪切單元格”命令。方法二：選擇數(shù)據(jù)來源列。將鼠標指針移至選定區(qū)域的邊框，當鼠標指針變?yōu)?形狀后，按住Shift鍵的同時，拖動該區(qū)域到目標位置后，先釋放鼠標，再放開Shift鍵。 第7章 Excel的基本應(yīng)用制作成績表192022/7/25什么是函數(shù)所有函數(shù)都包含三部分：函數(shù)名、參數(shù)和圓括號，以求和函數(shù)SUM來說明。SUM

12、是函數(shù)名稱，從名稱大略可知該函數(shù)的功能及用途是求和。圓括號用來括起參數(shù)，在函數(shù)中圓括號是不可以省略的。參數(shù)是函數(shù)在計算時所必須使用的數(shù)據(jù)。函數(shù)的參數(shù)不僅是數(shù)字類型，它還可以是字符型、邏輯值或是單元格引用。如：SUM(B1,C3)，SUM(D2:G2)等。 圓括號用于括起參數(shù)SUM（number1,number2, .）函數(shù)名稱各參數(shù)之間用逗號分隔第7章 Excel的基本應(yīng)用制作成績表202022/7/25函數(shù)的使用在“各科成績表”工作表中，增加“名次”列，計算每位學(xué)生的總分排名。 選擇目標單元格，單擊編輯欄左邊的“插入函數(shù)”按鈕，打開“插入函數(shù)”對話框，在“或選擇類別”下拉列表框中，選擇“統(tǒng)計

13、” ，如下圖所示。第7章 Excel的基本應(yīng)用制作成績表212022/7/25函數(shù)的使用（續(xù)）在“選擇函數(shù)”列表框中，選擇“RANK”函數(shù)，打開“函數(shù)參數(shù)”對話框，如下圖所示。 單擊此處可獲得該函數(shù)的幫助描述函數(shù)的功能第7章 Excel的基本應(yīng)用制作成績表222022/7/25函數(shù)的使用（續(xù)）在對話框中，將插入點定位在第一個參數(shù)“Number”處，從當前工作表中選擇“H2”單元格；再將插入點定位在第二個參數(shù)“Ref”處，從當前工作表中選擇單元格區(qū)域“H2:H38” 選取參數(shù)顯示插入點所在位置參數(shù)的作用這里顯示函數(shù)計算結(jié)果第7章 Excel的基本應(yīng)用制作成績表232022/7/25RANK函數(shù)

14、的功能RANK函數(shù)是排定名次的函數(shù)，用于返回一個數(shù)值在一組數(shù)值中的排序，排序時不改變該數(shù)值原來的位置。語法格式為：RANK(number,ref,order)共包括三個參數(shù)，其中：Number 為需要找到排位的數(shù)字。Ref 為數(shù)字列表數(shù)組或?qū)?shù)字列表的引用。Ref 中的非數(shù)值型參數(shù)將被忽略。Order 為一數(shù)字，指明排位的方式。如果 Order 為 0或省略，按照降序排列；如果 Order 不為零，按照升序排列。 第7章 Excel的基本應(yīng)用制作成績表242022/7/25使用RANK函數(shù)時出現(xiàn)的問題名次相同，但總分不同第7章 Excel的基本應(yīng)用制作成績表252022/7/25單元格引用時產(chǎn)

15、生問題的原因RANK函數(shù)的第2個參數(shù)本來應(yīng)該是所有學(xué)生的總分單元格區(qū)域“H2:H38”，但到了I5單元格卻變成了“H5:H41”，而在I6單元格又變成了“H6:H42”。說明一旦公式所在的位置發(fā)生了變化，公式中的單元格引用也會有相應(yīng)的變化。這就是“相對引用”，是Excel默認的引用方式。 H5:H41 H6:H42 第7章 Excel的基本應(yīng)用制作成績表262022/7/25如何解決在RANK函數(shù)中，第2個參數(shù)的單元格區(qū)域“H2:H38”表示所有學(xué)生的總分，它不應(yīng)該隨著單元格的復(fù)制而變化。所以這個區(qū)域不能使用“相對引用”。要保證在復(fù)制公式時，單元格區(qū)域“H2:H38”固定不變，就必須使用“絕對

16、引用”！“絕對引用”總是指向固定的單元格或單元格區(qū)域，無論公式怎么復(fù)制也不會改變引用位置。使用“絕對引用”的方法很簡單，只要在“相對引用”的基礎(chǔ)上，在字母和數(shù)字之前分別加上美元符號“$”就行了。例如：把“H2:H38”改為“$H$2:$H$38”。 第7章 Excel的基本應(yīng)用制作成績表272022/7/25解決方法$H$2:$H$38 $H$2:$H$38 復(fù)制公式時，當公式中使用的單元格引用需要隨著所在位置的不同而改變時，應(yīng)該使用“相對引用”，如：RANK函數(shù)中的第1個參數(shù)；復(fù)制公式后，分別為H2，H5，H6；當公式中使用的單元格引用不隨所在位置而改變時，應(yīng)該使用“絕對引用”，如：RANK

17、函數(shù)中的第2個參數(shù)，復(fù)制公式后，仍然為“$H$2:$H$38”。 第7章 Excel的基本應(yīng)用制作成績表282022/7/25單元格引用單元格引用是指公式中指明的一個單元格或一組單元格。公式中對單元格的引用分為相對引用、絕對引用和混合引用。相對引用用“H2”這樣的方式來引用單元格是相對引用。相對引用是指當公式在復(fù)制或移動時，公式中引用單元格的地址會隨著移動的位置自動改變。絕對引用在行號和列號前均加上“$”，如：“$H$2”這樣的方式來引用單元格是絕對引用。當公式在復(fù)制或移動時，公式中引用單元格的地址不會隨著公式的位置而改變?；旌弦没旌弦檬侵竼卧竦刂分屑扔邢鄬σ?，也有絕對引用?！?H2”

18、，表示具有絕對列和相對行，當公式在復(fù)制或移動時，保持列不變，而行變化；“H$2”表示具有相對列和絕對行，當公式在復(fù)制或移動時，保持行不變，而列變化。第7章 Excel的基本應(yīng)用制作成績表292022/7/25其他函數(shù)的使用在“各科成績表”工作表中，計算出各門課程的“班級平均分”。用AVERAGE 函數(shù)完成在“各科成績表”工作表中，計算出各門課程的“班級最高分”及“班級最低分”。分別用MAX及MIN函數(shù)完成在“各科成績表”工作表中，將各門課程的“班級平均分”的結(jié)果四舍五入，保留2位小數(shù)。用ROUND與AVERAGE 的嵌套函數(shù)完成。第7章 Excel的基本應(yīng)用制作成績表302022/7/25嵌套

19、函數(shù)嵌套函數(shù)，就是指在某些情況下，將某個函數(shù)作為另一函數(shù)的參數(shù)使用。例如：AVERAGE 函數(shù)就被當作并ROUND函數(shù)的第一個參數(shù)使用，其意義就是：將平均值的計算結(jié)果進行四舍五入，保留兩位小數(shù)。將函數(shù)AVERAGE(D2:D38)，作為函數(shù)ROUND的第一個參數(shù)第7章 Excel的基本應(yīng)用制作成績表312022/7/25ROUND函數(shù) ROUND函數(shù)返回某個數(shù)字按指定位數(shù)四舍五入后的數(shù)字。語法格式為：ROUND(number,num_digits)共包括兩個參數(shù)：Number 需要進行四舍五入的數(shù)字；Num_digits 指定的位數(shù)，按此位數(shù)進行四舍五入。如果num_digits大于0，則四舍

20、五入到指定的小數(shù)位。如：ROUND(35.385,1)=35.4如果num_digits等于0，則四舍五入到最接近的整數(shù)。如：ROUND(35.385,0)=35如果num_digits小于0，則在小數(shù)點左側(cè)進行四舍五入。如：ROUND(35.385,-1)=40第7章 Excel的基本應(yīng)用制作成績表322022/7/25成績表的排序和篩選 數(shù)據(jù)清單 排序 自動篩選 高級篩選 第7章 Excel的基本應(yīng)用制作成績表332022/7/25數(shù)據(jù)清單數(shù)據(jù)清單是指包含相關(guān)數(shù)據(jù)的一系列工作表的數(shù)據(jù)行。可以理解成工作表中的一張二維表格，例如，我們在前面建立的成績表等。可使用下列數(shù)據(jù)清單元素來組織數(shù)據(jù)。數(shù)據(jù)

21、清單中的列是數(shù)據(jù)庫中的字段。數(shù)據(jù)清單中的列標題是數(shù)據(jù)庫中的字段名稱。數(shù)據(jù)清單中的每一行對應(yīng)數(shù)據(jù)庫中的一條記錄。數(shù)據(jù)清單應(yīng)該盡量滿足下列條件：每一列必須要有列名，且每一列中數(shù)據(jù)必須是相同類型的。避免在一個工作表中有多個數(shù)據(jù)清單。數(shù)據(jù)清單與其他數(shù)據(jù)間至少留出一個空白列和一個空白行。第7章 Excel的基本應(yīng)用制作成績表342022/7/25排序排序并不是針對某一列進行的，而是以某一列的大小為順序，對所有的記錄進行排序。也就是說，無論怎么排序，每一條記錄的內(nèi)容都不容改變，改變的只是它在數(shù)據(jù)清單中顯示的位置。 排序方式有升序和降序兩種。在按升序排序時，Excel使用如下次序：數(shù)字從最小的負數(shù)到最大的正

22、數(shù)進行排序；文本按09、空格、各種符號、AZ的次序排序；空白單元格始終排在最后。在按降序排序時，除了空白單元格總是在最后外，其他的排序次序反轉(zhuǎn)。 第7章 Excel的基本應(yīng)用制作成績表352022/7/25排序操作在“大學(xué)英語”工作表中，將“大學(xué)英語”列的成績按升序排列。（略）在“高等數(shù)學(xué)”工作表中，以“性別”為主要關(guān)鍵字降序排列；以“高等數(shù)學(xué)”為第二關(guān)鍵字降序排列；以“姓名”為第三關(guān)鍵字升序排列。 單擊數(shù)據(jù)清單中的任一單元格。在菜單欄中選擇“數(shù)據(jù)”“排序”命令，打開“排序”對話框。 進行如右圖所示的設(shè)置。第7章 Excel的基本應(yīng)用制作成績表362022/7/25自動篩選在“自動篩選”工作表

23、中，篩選出同時滿足以下四個條件的數(shù)據(jù)記錄：“性別”為“女”；姓“黃”或姓名中最后一個字為“靜”；“計算機應(yīng)用”的成績在80分90分之間；“名次”在前8名。 在菜單欄中選擇“數(shù)據(jù)”“篩選”“自動篩選”命令，此時標題列中自動出現(xiàn)下拉箭頭。 單擊各列的下拉箭頭進行相應(yīng)設(shè)置。要點提示在數(shù)據(jù)清單中進行多次篩選，下一次篩選的對象是上一次篩選的結(jié)果，最后的篩選結(jié)果受所有篩選條件的影響，它們之間的邏輯關(guān)系是“與”的關(guān)系。如果要取消對所有列的篩選，只要執(zhí)行“數(shù)據(jù)”“篩選”“全部顯示”命令；如果要撤消數(shù)據(jù)清單中的自動篩選箭頭，并取消所有的自動篩選設(shè)置，只要重新執(zhí)行“數(shù)據(jù)”“篩選”“自動篩選”命令。第7章 Exce

24、l的基本應(yīng)用制作成績表372022/7/25高級篩選自動篩選無法實現(xiàn)多個字段之間的“或”運算。在這種情況下，只有使用高級篩選才能完成。在高級篩選中，主要是定義以下三個單元格區(qū)域：定義查詢的列表區(qū)域；定義查詢的條件區(qū)域；定義存放查詢結(jié)果的區(qū)域。其中條件區(qū)域的定義最為復(fù)雜，條件的設(shè)置必須遵循以下原則：條件區(qū)域與數(shù)據(jù)清單區(qū)域之間必須有空白行或空白列隔開。條件區(qū)域至少應(yīng)該有兩行，第一行用來放置字段名，下面的行放置篩選條件。條件區(qū)域的字段名必須與數(shù)據(jù)清單中的字段名完全一致,最好通過復(fù)制得到?！芭c”關(guān)系的條件必須出現(xiàn)在同一行；“或”關(guān)系的條件不能出現(xiàn)在同一行 第7章 Excel的基本應(yīng)用制作成績表382022/7/25高級篩選操作在“高級篩選”工作表中，篩選出總分小于200分的女生或總分大于等于300分的男生的記錄。 構(gòu)造篩選條件在進行高級篩選之前，首先必須指定一 個條件區(qū)域。條件區(qū)域與數(shù)據(jù)清單之 間至少應(yīng)留有一 個空白行或一 個空白列分開。如圖1所示。執(zhí)行高級篩選 單擊數(shù)據(jù)清單中的任一 單元格 。在菜單欄中選擇“數(shù)據(jù)”“篩選”“高級篩選”命令，打開“高級篩選”對話框,設(shè)置結(jié)果參見圖2。圖1圖2第7章 Excel的基本應(yīng)用制作成績表392022/7/25要點回顧 在不同工作薄中進行工作表的復(fù)制或移動操作要