《計(jì)算機(jī)應(yīng)用基礎(chǔ)實(shí)訓(xùn)指導(dǎo)》課件-第4章

第4章Excel2010表格處理實(shí)訓(xùn)一制作產(chǎn)品銷(xiāo)售表實(shí)訓(xùn)二格式化員工工資統(tǒng)計(jì)表實(shí)訓(xùn)三制作學(xué)生成績(jī)表實(shí)訓(xùn)四學(xué)生成績(jī)表的排序和篩選實(shí)訓(xùn)五對(duì)銷(xiāo)售數(shù)據(jù)分類(lèi)匯總實(shí)驗(yàn)六工作表的綜合處理

【實(shí)訓(xùn)目的】

(1)掌握啟動(dòng)和退出Excel的操作方法。

(2)熟悉Excel的工作界面，理解工作簿、工作表和單元格的概念。

(3)熟練掌握Excel工作簿的建立和保存方法。

(4)熟練掌握數(shù)據(jù)錄入方法。實(shí)訓(xùn)一制作產(chǎn)品銷(xiāo)售表

【實(shí)訓(xùn)內(nèi)容】

制作一個(gè)產(chǎn)品銷(xiāo)售表，練習(xí)數(shù)據(jù)的錄入，效果如圖4-1所示。

【實(shí)訓(xùn)要求】

(1)新建一個(gè)工作簿文件，將當(dāng)前的工作簿文件保存在“D:\學(xué)號(hào)-姓名”文件夾中，命名為“實(shí)訓(xùn)一：產(chǎn)品銷(xiāo)售表.xlsx”，關(guān)閉該文件。

(2)重新打開(kāi)創(chuàng)建的“實(shí)訓(xùn)一：產(chǎn)品銷(xiāo)售表.xlsx”文件，在Sheet1工作表中輸入圖4-1所示內(nèi)容。

圖4-1產(chǎn)品銷(xiāo)售表效果①在A1單元格中輸入表格標(biāo)題。

②在A2單元格中輸入“制表日期”，在B2單元格中輸入系統(tǒng)當(dāng)前日期。

③在第3行中使用自動(dòng)填充功能輸入月份。

④在A4～A10單元格中使用自動(dòng)填充功能輸入產(chǎn)品編號(hào)(該編號(hào)是數(shù)字字符串)。

⑤參照效果圖輸入產(chǎn)品及銷(xiāo)售額。

(3)將Sheet1中的表格內(nèi)容復(fù)制到Sheet2的相同區(qū)域中，并將Sheet2重新命名為“銷(xiāo)售表”。

打開(kāi)文件的方法很多，常規(guī)的打開(kāi)方法是執(zhí)行【文件】選項(xiàng)卡中的【打開(kāi)】命令，而使用實(shí)訓(xùn)步驟中的方法可以更快速，但這只適合最近操作過(guò)的文件。另外，還可以在【計(jì)算機(jī)】窗口中直接雙擊要打開(kāi)的Excel文件。

小貼士

(4)在A1單元格中單擊鼠標(biāo)，輸入表格標(biāo)題。

(5)在A2單元格中輸入文字“制表日期”，然后按下鍵盤(pán)中的Tab鍵，進(jìn)入B2單元格，按下Ctrl+;鍵輸入系統(tǒng)當(dāng)前日期。

(6)在第3行的前三個(gè)單元格中分別輸入“產(chǎn)品編號(hào)”、“類(lèi)別”和“一月”，然后將光標(biāo)指向“一月”所在的C3單元格右下角的填充柄，拖動(dòng)鼠標(biāo)至H3單元格處，則自動(dòng)填充了月份，如圖4-2所示。

(7)在A4單元格中輸入“’001”，則輸入的數(shù)字為數(shù)字字符串，并在單元格中居左顯示。用同樣的方法，在A5單元格中輸入“’002”。圖4-2自動(dòng)填充月份

在輸入編號(hào)時(shí)需要特別注意，如果直接輸入編號(hào)，Excel會(huì)默認(rèn)為數(shù)字，例如輸入001，最終會(huì)顯示1，并且居右顯示。所以在輸入時(shí)要先輸入一個(gè)半角符號(hào)“’”，然后再輸入編號(hào)，這樣輸入的編號(hào)才被認(rèn)為是字符。

小貼士

(8)拖動(dòng)鼠標(biāo)同時(shí)選擇A4和A5單元格，然后拖動(dòng)填充柄至A10單元格處，自動(dòng)填充其他產(chǎn)品編號(hào)。

(9)參照效果圖輸入產(chǎn)品及銷(xiāo)售額，輸入時(shí)通過(guò)按鍵盤(pán)中的Tab鍵或方向鍵跳轉(zhuǎn)到相應(yīng)的單元格中。

(10)單擊工作表左上角的全選按鈕，選擇工作表中的所有內(nèi)容，按下Ctrl+C鍵復(fù)制選擇的內(nèi)容。

(11)單擊工作簿左下方的“Sheet2”工作表標(biāo)簽，切換到該工作表中，這時(shí)A1單元格自動(dòng)處于選擇狀態(tài)，按下Ctrl+V鍵粘貼復(fù)制的內(nèi)容。

(12)在Sheet2名稱(chēng)上雙擊鼠標(biāo)，激活工作表名稱(chēng)，輸入新名稱(chēng)“銷(xiāo)售表”，然后按下回車(chē)鍵確認(rèn)。

(13)按下Ctrl+S鍵保存對(duì)工作簿所做的修改。

【實(shí)訓(xùn)目的】

(1)掌握單元格的合并操作。

(2)掌握單元格格式的設(shè)置方法。

(3)熟練設(shè)置行高與列寬。

(4)掌握單元格邊框與填充的設(shè)置。

【實(shí)訓(xùn)內(nèi)容】

制作員工工資統(tǒng)計(jì)表，并對(duì)表格進(jìn)行格式化設(shè)置，效果如圖4-3所示。實(shí)訓(xùn)二格式化員工工資統(tǒng)計(jì)表圖4-3員工工資統(tǒng)計(jì)表效果

【實(shí)訓(xùn)要求】

(1)新建一個(gè)工作簿文件，將當(dāng)前的工作簿文件保存在“D:\學(xué)號(hào)-姓名”文件夾中，命名為“實(shí)訓(xùn)二：?jiǎn)T工工資統(tǒng)計(jì)表.xlsx”。

(2)參照效果圖在Sheet1工作表中輸入文字內(nèi)容。

(3)設(shè)置行高與列寬。

①行高：第1行為30，第2～12行為20。

②列寬：第1列為4，第2列為8，第3列為16，第4～8列為10。

③將單元格區(qū)域A1:H1合并為一個(gè)。

(4)設(shè)置單元格內(nèi)容格式。

①第1行：黑體、18磅、加粗。

②第2行：黑體、11磅、居中顯示。

③其他文字：華文中宋、11磅。

④參照效果圖設(shè)置日期格式和數(shù)字格式。

(5)設(shè)置單元格邊框與底紋理。

①外邊框與第1行下邊框：粗線。

②內(nèi)邊框：細(xì)線。

③填充設(shè)置：第1行淡粉色、第2行淡黃色、其他行淡青色。

【實(shí)訓(xùn)步驟】

(1)啟動(dòng)Excel2010。

(2)按下Ctrl+S鍵，將工作簿保存在“D:\學(xué)號(hào)-姓名”文件夾中，名稱(chēng)為“實(shí)訓(xùn)二：?jiǎn)T工工資統(tǒng)計(jì)表.xlsx”。

(3)在Sheet1工作表中輸入表格內(nèi)容，如圖4-4所示。

(4)在行號(hào)1上單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇【行高】命令，在打開(kāi)的【行高】對(duì)話框中設(shè)置行高為30，如圖4-5所示。圖4-4輸入的表格內(nèi)容圖4-5【行高】對(duì)話框

(5)在行號(hào)2～12上拖動(dòng)鼠標(biāo)選擇多行，參照步驟(4)中的方法，設(shè)置行高為20。

(6)在列標(biāo)A上單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇【列寬】命令，在打開(kāi)的【列寬】對(duì)話框中設(shè)置列寬為4；用同樣的方法，設(shè)置B列為8，C列為16，D～H列為10。

(7)拖動(dòng)鼠標(biāo)同時(shí)選擇A1:H1單元格，在【開(kāi)始】選項(xiàng)卡的“對(duì)齊方式”組中單擊【合并后居中】按鈕，將其合并為一個(gè)單元格，并使內(nèi)容居中顯示。

(8)選擇A1單元格，在【開(kāi)始】選項(xiàng)卡的“字體”組中設(shè)置字體為黑體、字號(hào)為18磅，然后單擊 按鈕將其加粗，如圖4-6所示。

(9)選擇第2行，在“字體”組中設(shè)置字體為黑體，字號(hào)為11磅，然后在“對(duì)齊方式”組中單擊 按鈕，將文字居中顯示。

(10)同時(shí)選擇表格中的A3:H12單元格，設(shè)置字體為華文中宋，字號(hào)為11磅，此時(shí)的表格效果如圖4-7所示。圖4-6設(shè)置字體屬性

(11)同時(shí)選擇表格中的C3:C12單元格，在【開(kāi)始】選項(xiàng)卡的“數(shù)字”組中打開(kāi)【日期】下拉列表，選擇“長(zhǎng)日期”選項(xiàng)。

(12)同時(shí)選擇表格中的D3:H12單元格，單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇【設(shè)置單元格格式】命令，在打開(kāi)的【設(shè)置單元格格式】對(duì)話框中設(shè)置格式如圖4-8所示。

(13)單擊【確定】按鈕，則設(shè)置了數(shù)字格式，如圖4-9所示。圖4-8【設(shè)置單元格格式】對(duì)話框圖4-9設(shè)置數(shù)字格式后的效果

圖4-10表格效果

【實(shí)訓(xùn)目的】

(1)學(xué)習(xí)單元格的引用方法。

(2)掌握公式的表達(dá)方法。

(3)熟練常用函數(shù)的使用方法。

(4)了解圖表的制作。

【實(shí)訓(xùn)內(nèi)容】

制作一個(gè)學(xué)生成績(jī)表，并對(duì)其中的數(shù)據(jù)進(jìn)行計(jì)算，效果如圖4-11所示。實(shí)訓(xùn)三制作學(xué)生成績(jī)表圖4-11學(xué)生成績(jī)表效果

【實(shí)訓(xùn)要求】

(1)打開(kāi)“學(xué)生成績(jī)表.xlsx”，計(jì)算出每人的總成績(jī)、平均分和總評(píng)。

①總成績(jī)?=?語(yǔ)文?+?數(shù)學(xué)?+?外語(yǔ)?+?物理?+?化學(xué)?+?生物。

②平均分?=?總成績(jī)÷6。

③總評(píng)：平均分≥90，優(yōu)秀；平均分≥80，良好；平均分≥60，及格；否則，不及格。

(2)對(duì)整體成績(jī)進(jìn)行分析。

①計(jì)算每科的平均分、最高分、最低分。

②計(jì)算參加考試的“總?cè)藬?shù)”以及各科的“不及格人數(shù)”。

(3)根據(jù)“學(xué)生成績(jī)表”制作圖表，放置在Sheet2工作表中，圖表布局使用“布局5”，圖表標(biāo)題為“學(xué)生成績(jī)圖表”，縱坐標(biāo)軸標(biāo)題為“分?jǐn)?shù)”，如圖4-12所示。圖4-12學(xué)生成績(jī)圖表

【實(shí)訓(xùn)步驟】

(1)啟動(dòng)Excel2010，按下Ctrl+O鍵，打開(kāi)“素材”文件夾中的“學(xué)生成績(jī)表.xlsx”，如圖4-13所示。

(2)在J3單元格中定位光標(biāo)，輸入“＝”，然后輸入“D3+E3+F3+G3+H3+I3”，按回車(chē)鍵可得到求和結(jié)果。圖4-13打開(kāi)的學(xué)生成績(jī)表在這里我們使用公式對(duì)“總成績(jī)”進(jìn)行求和計(jì)算，實(shí)際上更簡(jiǎn)單的方法是使用“自動(dòng)求和”函數(shù) ，使用該函數(shù)時(shí)，Excel將自動(dòng)對(duì)其左側(cè)或上方的數(shù)據(jù)區(qū)域進(jìn)行求和。

小貼士

(3)拖動(dòng)J3單元格的填充柄至J14單元格處，則自動(dòng)復(fù)制了公式并出現(xiàn)計(jì)算結(jié)果。

(4)在K3單元格中定位光標(biāo)，在【公式】選項(xiàng)卡的“函數(shù)庫(kù)”組中單擊 按鈕下方的小箭頭，在打開(kāi)的列表中選擇【平均】選項(xiàng)，然后選擇D3:I3單元格區(qū)域，按下回車(chē)鍵得到求平均結(jié)果。這里要特別注意，選擇【平均】選項(xiàng)后，系統(tǒng)會(huì)將K3單元格左側(cè)的所有數(shù)據(jù)區(qū)域進(jìn)行自動(dòng)求平均計(jì)算，但這里面包括了J3單元格中的總成績(jī)，因此不能直接按下回車(chē)鍵確認(rèn)計(jì)算，需要重新選擇數(shù)據(jù)區(qū)域?yàn)镈3:I3。另外，也可以在K3單元格中直接輸入公式“＝J3/6”。

小貼士

(5)拖動(dòng)K3單元格的填充柄至K14單元格處，自動(dòng)復(fù)制函數(shù)并出現(xiàn)計(jì)算結(jié)果。

(6)在L3單元格中單擊鼠標(biāo)，輸入公式內(nèi)容“=IF(K3>=90,”優(yōu)秀“,IF(K3>=80,”良好“,IF(K3>=60,”及格“,”不及格“)))”，按回車(chē)鍵，單元格中將直接顯示總評(píng)結(jié)果，而編輯框中顯示的是公式，如圖4-14所示。

(7)拖動(dòng)L3單元格的填充柄至L14單元格處，則復(fù)制公式并出現(xiàn)總評(píng)結(jié)果。

接下來(lái)對(duì)表格中的整體成績(jī)進(jìn)行分析，將分析結(jié)果放置在成績(jī)表的下方，如圖4-15所示。圖4-14輸入總評(píng)公式圖4-15要分析的項(xiàng)目

(8)將光標(biāo)定位在D16單元格中，在【公式】選項(xiàng)卡的“函數(shù)庫(kù)”組中單擊 按鈕下方的小箭頭，在打開(kāi)的列表中選擇【平均】選項(xiàng)，則對(duì)其上方的數(shù)據(jù)區(qū)域進(jìn)行求平均，按回車(chē)鍵得到語(yǔ)文學(xué)科的平均分。

(9)拖動(dòng)D16單元格的填充柄至I16單元格處，復(fù)制函數(shù)則出現(xiàn)各學(xué)科的平均分。

(10)將光標(biāo)定位在D17單元格中，在“函數(shù)庫(kù)”組中單擊

按鈕下方的小箭頭，在打開(kāi)的列表中選擇【最大值】選項(xiàng)，然后選擇D3:D14單元格區(qū)域，按回車(chē)鍵則顯示語(yǔ)文學(xué)科的最高分。

(11)拖動(dòng)D17單元格的填充柄至I17單元格處，復(fù)制函數(shù)則出現(xiàn)各學(xué)科的最高分。

(12)將光標(biāo)定位在D18單元格中，在“函數(shù)庫(kù)”組中單擊

按鈕下方的小箭頭，在打開(kāi)的列表中選擇【最小值】選項(xiàng)，然后選擇D3:D14單元格區(qū)域，按回車(chē)鍵則顯示語(yǔ)文學(xué)科的最低分。

(13)拖動(dòng)D18單元格的填充柄至I18單元格處，復(fù)制函數(shù)則出現(xiàn)各學(xué)科的最低分。

(14)將光標(biāo)定位在D19單元格中，在“函數(shù)庫(kù)”組中單擊

按鈕下方的小箭頭，在打開(kāi)的列表中選擇【計(jì)數(shù)】選項(xiàng)，然后選擇D3:D14單元格區(qū)域，按回車(chē)鍵則顯示學(xué)生的總?cè)藬?shù)。

(15)將光標(biāo)定位在D20單元格中，輸入公式“=COUNTIF(D3:D14,"<60")”，按回車(chē)鍵，則單元格中將直接顯示語(yǔ)文學(xué)科的不及格人數(shù)。

(16)拖動(dòng)D20單元格的填充柄至I20單元格處，自動(dòng)復(fù)制函數(shù)并出現(xiàn)各學(xué)科的不及格人數(shù)，如圖4-16所示。圖4-16成績(jī)分析結(jié)果

(17)在成績(jī)表中同時(shí)選擇B2:I14區(qū)域，在【插入】選項(xiàng)卡的“圖表”組中單擊 按鈕，在打開(kāi)的下拉列表中選擇第1種二維柱形圖，則生成了一個(gè)圖表，如圖4-17所示。

(18)在【設(shè)計(jì)】選項(xiàng)卡的“圖表布局”組中單擊右下角的 按鈕，在打開(kāi)的下拉列表中選擇“布局5”。

(19)在圖表中輸入圖表標(biāo)題“學(xué)生成績(jī)圖表”，再輸入縱坐標(biāo)標(biāo)題為“分?jǐn)?shù)”，然后將圖表適當(dāng)放大。

(20)在【設(shè)計(jì)】選項(xiàng)卡的“位置”組中單擊【移動(dòng)圖表】按鈕，彈出【移動(dòng)圖表】對(duì)話框，在【對(duì)象位于】下拉列表中選擇“Sheet2”，如圖4-18所示，然后單擊【確定】按鈕，將圖表移動(dòng)到Sheet2工作表中。圖4-17生成的圖表圖4-18【移動(dòng)圖表】對(duì)話框

(21)按下Ctrl+S鍵保存對(duì)文件所做的修改。

【實(shí)訓(xùn)目的】

(1)了解Excel的數(shù)據(jù)庫(kù)管理功能。

(2)掌握數(shù)據(jù)記錄的排序操作。

(3)掌握數(shù)據(jù)記錄的篩選(自動(dòng)篩選和高級(jí)篩選)操作。

【實(shí)訓(xùn)內(nèi)容】

(1)在“實(shí)訓(xùn)三：學(xué)生成績(jī)表.xlsx”的基礎(chǔ)上完成排序操作。

(2)在“實(shí)訓(xùn)三：學(xué)生成績(jī)表.xlsx”的基礎(chǔ)上完成篩選操作。實(shí)訓(xùn)四學(xué)生成績(jī)表的排序和篩選

【實(shí)訓(xùn)要求】

(1)將完成的“實(shí)訓(xùn)三：學(xué)生成績(jī)表.xlsx”中的內(nèi)容復(fù)制到4個(gè)新表中，并將復(fù)制所得的新表分別重命名為“排序”、“自動(dòng)篩選”、“自定義篩選”和“高級(jí)篩選”。

(2)在“排序”工作表中，以“數(shù)學(xué)”為關(guān)鍵字按遞減方式排序，若數(shù)學(xué)成績(jī)相同，則按“語(yǔ)文”遞減排序，結(jié)果如圖4-19所示。

(3)在“自動(dòng)篩選”工作表中，篩選出“英語(yǔ)”排在前3名的學(xué)生，結(jié)果如圖4-20所示。圖4-19排序結(jié)果圖4-20自動(dòng)篩選結(jié)果

(4)在“自定義篩選”工作表中，篩選出“平均分”在80～90分之間的學(xué)生，結(jié)果如圖4-21所示。

(5)在“高級(jí)篩選”工作表中，篩選出至少有一門(mén)課程不及格的學(xué)生(在輸入篩選條件時(shí)，輸入到同一行中表示“且”的關(guān)系，輸入到不同行中表示“或”的關(guān)系)，如圖4-22所示。圖4-21自定義篩選結(jié)果圖4-22高級(jí)篩選結(jié)果

【實(shí)訓(xùn)步驟】

(1)啟動(dòng)Excel2010，按下Ctrl+O鍵，打開(kāi)實(shí)訓(xùn)三中完成的“學(xué)生成績(jī)表.xlsx”，然后刪除表格下方的內(nèi)容(只保留一個(gè)表格)，如圖4-23所示。

(2)在Sheet1工作表標(biāo)簽上單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇【移動(dòng)或復(fù)制】命令，在打開(kāi)的【移動(dòng)或復(fù)制工作表】對(duì)話框中勾選【建立副本】選項(xiàng)，然后設(shè)置其他選項(xiàng)如圖4-24所示。圖4-23刪除后的學(xué)生成績(jī)表圖4-24【移動(dòng)或復(fù)制工作表】對(duì)話框

(3)單擊【確定】按鈕，則在Sheet2的左側(cè)復(fù)制了一個(gè)工作表，然后將其重新命名為“排序”。

(4)用同樣的方法，再將Sheet1工作表復(fù)制3次，將復(fù)制得到的3個(gè)工作表分別命名為“自動(dòng)篩選”、“自定義篩選”和“高級(jí)篩選”。

(5)切換到“排序”工作表，在數(shù)據(jù)表中定位光標(biāo)，在【數(shù)據(jù)】選項(xiàng)卡的“排序和篩選”組中單擊【排序】按鈕，打開(kāi)【排序】對(duì)話框，然后單擊【添加條件】按鈕，再分別設(shè)置主要關(guān)鍵字為“數(shù)學(xué)”、次要關(guān)鍵字為“語(yǔ)文”，并且都以降序排列，如圖4-25所示。圖4-25【排序】對(duì)話框

(6)單擊【確定】按鈕，則以“數(shù)學(xué)”為關(guān)鍵字按遞減方式排序，當(dāng)數(shù)學(xué)成績(jī)相同時(shí)，則按“語(yǔ)文”遞減排序。

(7)切換到“自動(dòng)篩選”工作表，在數(shù)據(jù)表中定位光標(biāo)，在【數(shù)據(jù)】選項(xiàng)卡的“排序和篩選”組中單擊【篩選】按鈕，則每個(gè)字段右側(cè)都出現(xiàn)了篩選按鈕。

(8)單擊“英語(yǔ)”字段右側(cè)的篩選按鈕，在下拉列表中選擇【數(shù)字篩選】/【10個(gè)最大的值】選項(xiàng)，在彈出的【自動(dòng)篩選前10個(gè)】對(duì)話框中設(shè)置選項(xiàng)如圖4-26所示。單擊【確定】按鈕，則自動(dòng)篩選出英語(yǔ)成績(jī)的前3名。

數(shù)據(jù)排序的規(guī)則：數(shù)值數(shù)據(jù)依數(shù)值大小排序；英文字符采用ASCII碼值比較大??；漢字按拼音首字母的先后順序比較大小，先小后大；日期時(shí)間采用先小后大進(jìn)行比較。另外，如果要對(duì)單列數(shù)據(jù)進(jìn)行排序，可以直接在【數(shù)據(jù)】選項(xiàng)卡的“排序和篩選”組中單擊按鈕或 按鈕。

小貼士

(9)切換到“自定義篩選”工作表，在數(shù)據(jù)表中定位光標(biāo)，在【數(shù)據(jù)】選項(xiàng)卡的“排序和篩選”組中單擊【篩選】按鈕，然后單擊“平均分”字段右側(cè)的篩選按鈕，在下拉列表中選擇【數(shù)字篩選】/【自定義篩選】選項(xiàng)，在彈出的【自定義自動(dòng)篩選方式】對(duì)話框中設(shè)置選項(xiàng)如圖4-27所示。圖4-26【自動(dòng)篩選前10個(gè)】對(duì)話框圖4-27【自定義自動(dòng)篩選方式】對(duì)話框

(10)單擊【確定】按鈕，則篩選出“平均分”在80～90分之間的學(xué)生。

(11)切換到“高級(jí)篩選”工作表，在數(shù)據(jù)表的下方輸入篩選條件，創(chuàng)建條件區(qū)域，如圖4-28所示。由于條件不在同一行中，因此它們之間是“或”的關(guān)系，即可篩選出至少有一門(mén)課程不及格的學(xué)生。

(12)在【數(shù)據(jù)】選項(xiàng)卡的“排序和篩選”組中單擊【高級(jí)】按鈕，在打開(kāi)的【高級(jí)篩選】對(duì)話框中設(shè)置選項(xiàng)如圖4-29所示。單擊【確定】按鈕，篩選出至少有一門(mén)課程不及格的學(xué)生。圖4-28創(chuàng)建條件區(qū)域圖4-29【高級(jí)篩選】對(duì)話框

(13)按下Ctrl+S鍵保存對(duì)文件做的修改。

【實(shí)訓(xùn)目的】

(1)掌握數(shù)據(jù)表的排序操作。

(2)學(xué)會(huì)數(shù)據(jù)表的分類(lèi)匯總方法。

(3)掌握?qǐng)D表的創(chuàng)建與編輯方法。

【實(shí)訓(xùn)內(nèi)容】

(1)按要求對(duì)銷(xiāo)售數(shù)據(jù)進(jìn)行分類(lèi)匯總，結(jié)果如圖4-30所示。實(shí)訓(xùn)五對(duì)銷(xiāo)售數(shù)據(jù)分類(lèi)匯總圖4-30分類(lèi)匯總結(jié)果

(2)根據(jù)分類(lèi)匯總后的數(shù)據(jù)創(chuàng)建圖表。

【實(shí)訓(xùn)要求】

(1)打開(kāi)“銷(xiāo)售數(shù)據(jù).xlsx”文件，將Sheet1工作表中的內(nèi)容復(fù)制到Sheet2工作表中

備用。

(2)以“部門(mén)”為分類(lèi)字段，對(duì)“訂單金額”進(jìn)行求和匯總。

(3)根據(jù)分類(lèi)匯總后的數(shù)據(jù)創(chuàng)建餅形圖表并進(jìn)行編輯，效果如圖4-31所示。

(4)以“銷(xiāo)售人員”為分類(lèi)字段，對(duì)“訂單金額”進(jìn)行求和匯總。

(5)根據(jù)分類(lèi)匯總后的數(shù)據(jù)創(chuàng)建條形圖表并進(jìn)行編輯，效果如圖4-32所示。圖4-31餅形圖表圖4-32條形圖表圖4-33打開(kāi)的銷(xiāo)售統(tǒng)計(jì)表圖4-34排序結(jié)果圖4-35【分類(lèi)匯總】對(duì)話框圖4-36匯總結(jié)果

(6)同時(shí)選擇兩個(gè)銷(xiāo)售部門(mén)的訂單金額匯總數(shù)據(jù)(B13和B24單元格)，在【插入】選項(xiàng)卡的“圖表”組中單擊【餅圖】按鈕，在下拉列表中選擇“分離型三維餅圖”，則生成了一個(gè)圖表。

(7)在【設(shè)計(jì)】選項(xiàng)卡的“圖表布局”組中單擊右下角的按鈕，在打開(kāi)的下拉列表中選擇“布局6”，圖表效果如圖4-37所示。

(8)在圖表上單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇【選擇數(shù)據(jù)】命令，彈出【選擇數(shù)據(jù)源】對(duì)話框，如圖4-38所示。圖4-37圖表效果圖4-38【選擇數(shù)據(jù)源】對(duì)話框

(9)單擊【圖例項(xiàng)(系列)】列表區(qū)中的【編輯】按鈕，彈出【編輯數(shù)據(jù)系列】對(duì)話框，選擇匯總表中的A1單元格，然后單擊【確定】按鈕，返回【選擇數(shù)據(jù)源】對(duì)話框，則完成了圖表標(biāo)題的編輯。

(10)在【水平(分類(lèi))軸標(biāo)簽】列表區(qū)中單擊【編輯】按鈕，參照剛才的操作步驟，選擇匯總表中的D13和D24單元格，完成圖表圖例的設(shè)置。

(11)單擊【確定】按鈕，圖表效果如圖4-39所示。圖4-39圖表效果

(12)在餅形上單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇【設(shè)置數(shù)據(jù)系列格式】命令，在彈出的【設(shè)置數(shù)據(jù)系列格式】對(duì)話框中設(shè)置【餅圖分離程度】為6%，如圖4-40所示，單擊【確定】按鈕，即可降低餅圖分離程度。

(13)切換到Sheet2中，參照前面的操作方法，對(duì)“銷(xiāo)售人員”一列進(jìn)行升序排序，并對(duì)“訂單金額”進(jìn)行求和匯總，如圖4-41所示，然后隱藏細(xì)節(jié)數(shù)據(jù)，只顯示匯總結(jié)果，如圖4-42所示。圖4-40【設(shè)置數(shù)據(jù)系列格式】對(duì)話框圖4-41【分類(lèi)匯總】對(duì)話框圖4-42匯總結(jié)果圖4-43圖表效果

(16)在圖表上單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇【選擇數(shù)據(jù)】命令，彈出【選擇數(shù)據(jù)源】對(duì)話框，參照剛才的操作步驟，選擇匯總表中的A1單元格作為圖表標(biāo)題，選擇4位銷(xiāo)售人員名字作為水平(分類(lèi))軸標(biāo)簽，如圖4-44所示。

(17)單擊【確定】按鈕，則完成了圖表的編輯。

(18)將工作簿另存為“實(shí)訓(xùn)四：學(xué)生成績(jī)表的排序和篩選.xlsx”。圖4-44【選擇數(shù)據(jù)源】對(duì)話框

如何清除分類(lèi)匯總？(1)單擊分類(lèi)匯總數(shù)據(jù)中的任意單元格；(2)在【數(shù)據(jù)】選項(xiàng)卡的“分級(jí)顯示”組中單擊【分類(lèi)匯總】按鈕，則彈出【分類(lèi)匯總】對(duì)話框；(3)在對(duì)話框中單擊【全部刪除】按鈕，即可完成操作。

小貼士

【實(shí)驗(yàn)?zāi)康摹?/p>

(1)掌握工作表的編輯操作。

(2)掌握工作表的格式化設(shè)置方法。

(3)熟練掌握函數(shù)與公式的使用。

(4)掌握數(shù)據(jù)的排序、篩選與分類(lèi)匯總操作。

(5)掌握?qǐng)D表的生成與編輯方法。實(shí)訓(xùn)六工作表的綜合處理

【實(shí)驗(yàn)內(nèi)容】

工作表的復(fù)制與重命名、格式化工作表、使用公式與函數(shù)計(jì)算數(shù)據(jù)、數(shù)據(jù)的排序、篩選與分類(lèi)匯總、圖表的建立等。

【實(shí)驗(yàn)要求】

(1)打開(kāi)“產(chǎn)品銷(xiāo)售表.xlsx”文件，將其中的內(nèi)容復(fù)制到3個(gè)新表中，并將工作表分別重命名為“格式化”、“計(jì)算”、“排序與篩選”和“分類(lèi)與圖表”。

(2)在“格式化”工作表中，對(duì)工作表進(jìn)行格式化設(shè)置。①合并居中單元格A1:D1。

②第一列中日期的格式含有“年月日”。

③行高：第一行為35，其他行為21.75。

④列寬：第一列為18，其他列為10。

⑤內(nèi)容格式：標(biāo)題為“隸書(shū)”、22磅；內(nèi)容為“宋體”、11磅、居中(銷(xiāo)售額一列居右)。

⑥工作表格式：統(tǒng)一的細(xì)邊框，然后第一行填充為淡粉色，其他填充為淡綠色，效果如圖4-45所示。

(3)在“計(jì)算”工作表中，分別計(jì)算出總銷(xiāo)售額、平均銷(xiāo)售額、東北地區(qū)銷(xiāo)售額和木材銷(xiāo)售額，結(jié)果放在相應(yīng)的單元格中，如圖4-46所示。圖4-45格式化后的效果圖4-46計(jì)算結(jié)果

(4)在“排序與篩選”工作表中進(jìn)行以下操作。

①以“日期”為關(guān)鍵字，升序排序。

②用高級(jí)篩選的方法，篩選出銷(xiāo)售額大于1000的記錄，如圖4-47所示。

(5)在“分類(lèi)與圖表”工作表中，以銷(xiāo)售地區(qū)為分類(lèi)字段，對(duì)“銷(xiāo)售額”進(jìn)行求和分類(lèi)匯總，如圖4-48所示。

(6)基于分類(lèi)匯總后的數(shù)據(jù)創(chuàng)建環(huán)形圖表，效果如圖4-49所示。圖4-47篩選結(jié)果圖4-48求和分類(lèi)匯總結(jié)果圖4-49環(huán)形圖表效果

【實(shí)訓(xùn)步驟】

(1)啟動(dòng)Excel2010，按下Ctrl+O鍵，打開(kāi)“素材”文件夾中的“產(chǎn)品銷(xiāo)售表.xlsx”。

(2)將光標(biāo)指向Sheet1工作表標(biāo)簽上，按住Ctrl鍵的同時(shí)向Sheet1的右側(cè)拖動(dòng)鼠標(biāo)，這時(shí)光標(biāo)變?yōu)?形狀，當(dāng)出現(xiàn)一個(gè)小黑三角形時(shí)釋放鼠標(biāo)，則復(fù)制了Sheet1，繼續(xù)向右拖動(dòng)鼠標(biāo)復(fù)制Sheet1兩次，則復(fù)制得到了3個(gè)工作表。

(3)在Sheet1名稱(chēng)上雙擊鼠標(biāo)，激活工作表名稱(chēng)，輸入新名稱(chēng)“格式化”，然后按下回車(chē)鍵確認(rèn)。用同樣的方法，將復(fù)制的3個(gè)工作表分別命名為“計(jì)算”、“排序與篩選”和“分類(lèi)與圖表”，如圖4-50所示。圖4-50復(fù)制與重命名的工作表

(4)切換到“格式化”工作表中，同時(shí)選擇A1:D1單元格，在【開(kāi)始】選項(xiàng)卡的“對(duì)齊方式”組中單擊【合并后居中】按鈕，將其合并為一個(gè)單元格，并使內(nèi)容居中。

(5)同時(shí)選擇A3:A13單元格，在【開(kāi)始】選項(xiàng)卡的“數(shù)字”組中打開(kāi)【日期】下拉列表，選擇“長(zhǎng)日期”選項(xiàng)。

(6)在行號(hào)1上單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇【行高】命令，在打開(kāi)的【行高】對(duì)話框中設(shè)置行高為35；用同樣的方法，設(shè)置其他行的行高為21.75。

(7)在列標(biāo)A上單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇【列寬】命令，在打開(kāi)的【列寬】對(duì)話框中設(shè)置列寬為18；用同樣的方法，設(shè)置其他列的列寬為10。圖4-51表格效果(一)圖4-52表格效果(二)

(10)切換到“計(jì)算”工作表中，在數(shù)據(jù)表的右側(cè)輸入要計(jì)算的銷(xiāo)售額，并填充一種顏色，以便觀察，如圖4-53所示。

(13)在G6單元格中定位光標(biāo)，輸入公式“=D9+D11+D12”，按下回車(chē)鍵，則計(jì)算出東北地區(qū)銷(xiāo)售額。

(14)在G7單元格中定位光標(biāo)，輸入公式“=D5+D6+D7+D10”，按下回車(chē)鍵，則計(jì)算出木材銷(xiāo)售額。

(15)切換到“排序與篩選”工作表中，在“日期”一列中單擊鼠標(biāo)，在【數(shù)據(jù)】選項(xiàng)卡的“排序和篩選”組中單擊

按鈕，對(duì)“日期”一列進(jìn)行升