上機(jī)練習(xí)二課件

上機(jī)練習(xí)二CONTENTS

任務(wù)描述

CONTENTS

任務(wù)描述

CONTENTS

任務(wù)描述

CONTENTS

任務(wù)描述

CONTENTS

任務(wù)分析

通過(guò)分析該任務(wù)可知，完成該任務(wù)，需要掌握以下幾點(diǎn)：（1）能夠創(chuàng)建表格，并能夠?qū)Ρ砀襁M(jìn)行格式設(shè)置。（2）會(huì)使用相關(guān)的函數(shù)和公式對(duì)數(shù)據(jù)進(jìn)行統(tǒng)計(jì)。（3）能夠使用Excel2010提供的圖表功能繪制折線(xiàn)圖。（4）會(huì)對(duì)數(shù)據(jù)進(jìn)行篩選，并能夠進(jìn)行條件查找。CONTENTS

任務(wù)實(shí)現(xiàn)

（1）建立表格文件，在A1單元格中輸入“班級(jí)考試成績(jī)表”，選擇A1：L1單元格區(qū)域，對(duì)其進(jìn)行合并居中，并設(shè)置其中文字字體為“隸書(shū)”，文字大小為36。（2）在A2：L2單元格區(qū)域中輸入表頭信息，設(shè)置其為居中對(duì)齊，設(shè)置文字格式為“宋體，14，加粗”，單元格填充背景設(shè)置為“淺藍(lán)”。（3）在A3單元格中輸入學(xué)號(hào)“‘10301”，向下拖動(dòng)鼠標(biāo)至A23單元格，實(shí)現(xiàn)學(xué)號(hào)的自動(dòng)填充，然后填寫(xiě)其他各科成績(jī)。（4）選中K3單元格，輸入公式“=SUM（C3:J3）”，求出第一名學(xué)生的總分，然后拖拽鼠標(biāo)復(fù)制公式至K23單元格，完成所有總分的計(jì)算。CONTENTS

任務(wù)實(shí)現(xiàn)

（5）選中L3單元格，輸入公式“=RANK（K3$K$3:$K$23）”，求出第一名學(xué)生的名次，然后復(fù)制公式至L23單元格，完成所有學(xué)生名次的統(tǒng)計(jì)。說(shuō)明：此處在統(tǒng)計(jì)學(xué)生名次的時(shí)候，沒(méi)有使用常用的排序操作，而是使用了RANK函數(shù)，該函數(shù)的基本功能是返回一個(gè)數(shù)字在數(shù)字列表中的排位，其基本格式為：RANK（數(shù)字，引用區(qū)域）。此案例操作中，就是求一個(gè)單元格的數(shù)值，在一個(gè)區(qū)域中（在總分?jǐn)?shù)據(jù)區(qū)域內(nèi)）的排序位置，此處使用的是絕對(duì)引用。CONTENTS

任務(wù)實(shí)現(xiàn)

（6）在A3：L23單元格區(qū)域中隔行設(shè)置填充背景，依次為“淡藍(lán)”和“無(wú)填充顏色”。（7）設(shè)置A3：K23單元格區(qū)域的文字格式為“宋體，14”，并居中顯示，設(shè)置L3：L23單元格區(qū)域的文字格式為“宋體，14，傾斜”。（8）選擇C3：J23單元格區(qū)域，切換到“開(kāi)始”選項(xiàng)卡，在“樣式”組中單擊“條件格式”下拉按鈕，在彈出的下拉列表中選擇“突出顯示單元格規(guī)則”→“介于”選項(xiàng)，在彈出的“介于”對(duì)話(huà)框中，分別將“介于59.9到0”設(shè)置為“淺紅填充色深紅色文本”，將“介于80到100”設(shè)置為“自定義格式”，如圖4-3所示。接著在彈出的“設(shè)置單元格格式”對(duì)話(huà)框“字體”選項(xiàng)卡中，選擇字形為“加粗”。CONTENTS

任務(wù)實(shí)現(xiàn)

CONTENTS

任務(wù)實(shí)現(xiàn)（9）選擇A2：L23單元格區(qū)域，切換到“開(kāi)始”選項(xiàng)卡，在“字體”組中單擊“邊框”下拉按鈕，在下拉列表中選擇“線(xiàn)條顏色”為“淺藍(lán)”后，再次單擊“邊框”下拉按鈕，在彈出的下拉列表中選擇“所有框線(xiàn)”選項(xiàng)，即為表格設(shè)置淺藍(lán)色內(nèi)外邊框。（10）選中O4單元格，輸入“成績(jī)分析”，從Q4單元格至X4單元格依次輸入各個(gè)科目名稱(chēng)，其文字格式設(shè)置為“宋體，14，加粗”，填充背景設(shè)置為“紫色，強(qiáng)調(diào)文字顏色4，淡色40%”，居中顯示文字，從O5單元格至O9單元格依次輸入“平均分”“最高分”“最低分”“及格率”和“優(yōu)秀率”，其文字設(shè)置為“宋體，14”，居中顯示文字。（11）依次選擇O4：P4、O5：P5、O6：P6、O7：P7、O8：P8和O9：P9單元格區(qū)域，將它們?cè)O(shè)置為“合并后居中”。（12）選中Q5單元格，輸入公式“=AVERAGE（C$3:C$23）”，計(jì)算語(yǔ)文科目的平均分，然后拖拽鼠標(biāo)復(fù)制公式至X4，計(jì)算其他科目的平均分。CONTENTS

任務(wù)實(shí)現(xiàn)（13）選中Q6單元格，輸入公式“=MAX（C$3:C$23）”，統(tǒng)計(jì)語(yǔ)文科目的最高分，然后拖拽鼠標(biāo)復(fù)制公式至X6，統(tǒng)計(jì)其他科目的最高分。（14）選中Q7單元格，輸入公式“=MIN（C$3:C$23）”，統(tǒng)計(jì)語(yǔ)文科目的最低分，然后拖拽鼠標(biāo)復(fù)制公式至X7，統(tǒng)計(jì)其他科目的最低分。（15）選中Q8單元格，輸入公式“=COUNTIF（C$3:C$23，">=60"）/COUNTIF（C$3:C$23，">0"）”，計(jì)算語(yǔ)文科目的及格率，然后拖拽鼠標(biāo)復(fù)制公式至X8，計(jì)算其他科目的及格率。（16）選中Q9單元格，輸入公式“=COUNTIF（C$3:C$23，">=80"）/COUNTIF（C$3:C$23，">0"）”，計(jì)算語(yǔ)文科目的優(yōu)秀率，然后拖拽鼠標(biāo)復(fù)制公式至X9，計(jì)算其他科目的優(yōu)秀率。（17）從O5：X9單元格區(qū)域隔行設(shè)置填充背景，依次為“紫色，強(qiáng)調(diào)文字顏色4，淡色80%”和“無(wú)填充顏色”。CONTENTS

任務(wù)實(shí)現(xiàn)（18）選擇Q5：X5單元格區(qū)域，切換到“開(kāi)始”選項(xiàng)卡，單擊“數(shù)字”組的組按鈕，在彈出的“設(shè)置單元格格式”對(duì)話(huà)框中，切換到“數(shù)字”選項(xiàng)卡，在“分類(lèi)”列表框中選擇“數(shù)值”選項(xiàng)，在右側(cè)窗格中設(shè)置小數(shù)位數(shù)為2位。（19）選擇Q8：X9單元格區(qū)域，按照（9）的方法，在“數(shù)字”選項(xiàng)卡的“分類(lèi)”列表框中選擇“百分比”選項(xiàng)，然后設(shè)置小數(shù)位數(shù)為2位。（20）選擇Q5：X9單元格區(qū)域，設(shè)置其中字體為“宋體，12”，并居中顯示。（21）選擇O4：X9單元格區(qū)域，按照步驟1中的（9），為表格設(shè)置內(nèi)外邊框顏色為“紫色，強(qiáng)調(diào)文字顏色4，淡色80%”。CONTENTS

任務(wù)實(shí)現(xiàn)（22）選中O11單元格，輸入“各科各分?jǐn)?shù)段人數(shù)”，然后選擇O11：X11單元格區(qū)域，進(jìn)行“合并及居中”，其中文字格式設(shè)置為“宋體，14，加粗”。選中O12單元格，輸入“分?jǐn)?shù)段”，從Q12單元格至X12單元格，依次輸入各個(gè)科目名稱(chēng)，其文字格式設(shè)置為“宋體，14，加粗”，填充背景設(shè)置為“水綠色，強(qiáng)調(diào)文字顏色5，淡色40%”，居中顯示文字，從O13單元格至O21單元格依次輸入“滿(mǎn)分”“90-99”“80-89”等分段信息，其文字設(shè)置為“宋體，14，加粗”，居中顯示文字。（23）依次選擇O13：P13、O14：P14、O15：P15、……、O21：P21單元格區(qū)域，設(shè)置“合并及居中”。（24）從O13：X21單元格區(qū)域隔行設(shè)置填充背景，依次為“水綠色，強(qiáng)調(diào)文字顏色5，淡色80%”和“無(wú)填充顏色”。（25）從單元格N11至單元格N21，依次輸入100、99.9、89.9、79.9、69.9、59.9、49.9、39.9和29.9，作為FREQUENCY函數(shù)的分段數(shù)據(jù)，如圖4-4所示。CONTENTS

任務(wù)實(shí)現(xiàn)

CONTENTS

任務(wù)實(shí)現(xiàn)

（26）選擇Q13：Q21單元格區(qū)域，切換到“公式”選項(xiàng)卡，在“函數(shù)庫(kù)”組中單擊“插入函數(shù)”按鈕，彈出“插入函數(shù)”對(duì)話(huà)框，在“或選擇類(lèi)別”下拉列表框中選擇“全部”選項(xiàng)，在“選擇函數(shù)”列表框中選擇FREQUENCY選項(xiàng)，然后單擊“確定”按鈕，彈出“函數(shù)參數(shù)”對(duì)話(huà)框，在Data_array文本框中輸入C3：C23，在Bins_array文本框中輸入N13：N21，將數(shù)據(jù)區(qū)域改為混合引用C$3：C$23，將分段區(qū)域改為絕對(duì)引用$N$13：$N$21，如圖4-5所示。CONTENTS

任務(wù)實(shí)現(xiàn)

單擊“確定”按鈕后，在編輯欄上按下Ctrl+Shift+Enter組合鍵，保證公式以數(shù)組的形式輸入（公式兩端會(huì)自動(dòng)加上“{}”，此時(shí)不可用手工添加），公式輸入之后按Enter鍵，統(tǒng)計(jì)結(jié)果就會(huì)自動(dòng)顯示在區(qū)域內(nèi)，如圖4-6所示。然后在區(qū)域右下角按住鼠標(biāo)左鍵，拖拽復(fù)制公式至整個(gè)統(tǒng)計(jì)區(qū)域，完成統(tǒng)計(jì)操作。CONTENTS

任務(wù)實(shí)現(xiàn)

說(shuō)明：此處操作用到了FREQUENCY函數(shù)，該函數(shù)的主要功能是計(jì)算數(shù)值在某個(gè)區(qū)域內(nèi)的出現(xiàn)頻率，然后返回一個(gè)垂直數(shù)組，其主要格式如下：FREQUENCY（data_array，bins_array）其中，data_array是一個(gè)數(shù)組或?qū)σ唤M數(shù)值的引用，為它計(jì)算頻率；Bins_array是一個(gè)區(qū)間數(shù)組或?qū)^(qū)間的引用，該區(qū)間用于對(duì)data_array中的數(shù)值進(jìn)行分組。因?yàn)樵摵瘮?shù)返回一組數(shù)據(jù)，因此，本步操作選取了一個(gè)區(qū)域輸入公式。（27）選擇O12：X21單元格區(qū)域，為表格設(shè)置內(nèi)外邊框顏色為“水綠色，強(qiáng)調(diào)文字顏色5，淡色40%”。（28）選擇Q13：X21單元格區(qū)域，按照步驟1中（8）的方法，在“大于”對(duì)話(huà)框中，設(shè)置為大于“0”的單元格設(shè)置格為“黃填充色深黃色文本”。（29）將鼠標(biāo)指針置于第N列邊界處，拖動(dòng)鼠標(biāo)縮小列寬，直至將其隱藏。將表格數(shù)據(jù)居中顯示。CONTENTS

任務(wù)實(shí)現(xiàn)

（30）選擇B2：E23單元格區(qū)域，切換到“插入”選項(xiàng)卡，在“圖表”組中單擊“折線(xiàn)圖”下拉按鈕，在彈出的下拉列表中選擇“帶數(shù)據(jù)標(biāo)記的折線(xiàn)圖”選項(xiàng)，即可新建圖表。（31）切換到“圖表工具-布局”選項(xiàng)卡，在“標(biāo)簽”組中單擊“圖表標(biāo)題”下拉按鈕，在彈出的下拉列表中選擇“圖表上方”選項(xiàng)，圖表中會(huì)顯示“圖表標(biāo)題”編輯框，在標(biāo)題框中輸入標(biāo)題文字“語(yǔ)數(shù)外成績(jī)分析圖”，設(shè)置“主要橫坐標(biāo)軸標(biāo)題”為“坐標(biāo)軸下方標(biāo)題”，輸入內(nèi)容為“姓名”，設(shè)置“主要縱坐標(biāo)軸標(biāo)題”為“豎排標(biāo)題”，輸入內(nèi)容為“成績(jī)”。（32）切換到“圖表工具-布局”選項(xiàng)卡，在“坐標(biāo)軸”組中單擊“網(wǎng)格線(xiàn)”下拉按鈕，在彈出的下拉列表中選擇“主要橫網(wǎng)格線(xiàn)”→“無(wú)”選項(xiàng)。（33）雙擊“數(shù)值軸”（Y軸），系統(tǒng)顯示“設(shè)置坐標(biāo)軸格式”對(duì)話(huà)框，在該對(duì)話(huà)框的左側(cè)窗格中選擇單擊“坐標(biāo)軸選項(xiàng)”選項(xiàng)，在右側(cè)窗格中分別設(shè)置“最大值”為“固定，100”、設(shè)置“主要刻度單位”為“固定，10”，單擊“關(guān)閉”按鈕，如圖4-7所示。CONTENTS

任務(wù)實(shí)現(xiàn)

CONTENTS

任務(wù)實(shí)現(xiàn)

（34）通過(guò)浮動(dòng)工具欄，可以將圖例中的文字設(shè)置為9號(hào)字，適當(dāng)調(diào)整圖表和圖例到一定位置和大小，使分類(lèi)軸文字能夠水平顯示。（35）單擊表格區(qū)域，切換到“開(kāi)始”選項(xiàng)卡，在“編輯”組中單擊“排序和篩選”下拉按鈕，在彈出的下拉列表中選擇“篩選”選項(xiàng)，表格的表頭項(xiàng)目上將會(huì)出現(xiàn)“篩選”下拉按鈕。單擊對(duì)應(yīng)的“篩選