Excel表格處理技巧1

第頁第4章表格處理軟件Excel2019電子表格軟件Excel是進行數(shù)據(jù)處理的常用軟件。主要以表格的方式來完成數(shù)據(jù)的輸入、計算、分析、制作圖表和統(tǒng)計。本章是主教材的第4章的配套實驗，通過10個實驗分別介紹表格的編排、數(shù)據(jù)的計算、統(tǒng)計分析和圖表的生成等Excel的操作方法和步驟。4.1Excel2019的基礎(chǔ)知識實驗4-1Excel的啟動、退出和界面介紹實驗?zāi)康蘑僬莆誆xcel應(yīng)用程序快捷方式的建立。②通過快捷方式掌握Excel的啟動和退出。③熟悉Excel的工作界面。④練習(xí)選擇工作表和單元格區(qū)域的方法。實驗內(nèi)容建立Excel的快捷方式，并利用該快捷方式啟動Excel，觀察Excel用戶界面，切換工作表標(biāo)簽，確認(rèn)及取消單元格輸入內(nèi)容，查看選中工作表及單元格區(qū)域情況。實驗步驟①執(zhí)行“開始”|“搜索”|“文件或文件夾”命令，打開“搜索結(jié)果”窗口，搜索的Excel應(yīng)用程序文件名為“Excel.exe”。②在搜索結(jié)果中選定Excel應(yīng)用程序文件，在桌面為其創(chuàng)建快捷方式。③雙擊“Excel快捷方式”圖標(biāo)，打開Excel應(yīng)用程序，認(rèn)識Excel工作界面：標(biāo)題欄、菜單欄、工具欄、編輯欄、工作表標(biāo)簽、工作表窗口、單元格和任務(wù)窗格等，如圖4-1所示。④單擊Sheet1工作表標(biāo)簽，在A1單元格中輸入字符“大學(xué)計算機基礎(chǔ)”，然后單擊編輯按鈕“√”確認(rèn)輸入，同時查看名稱框中的單元格名稱“A1”；在A2單元格中輸入字符“上機實驗”，然后單擊“×”按鈕取消輸入。⑤分別單擊“全選”、“行號”、“列標(biāo)”按鈕，查看選中全部工作表、一行、一列的情況。=6\*GB3⑥按下Shift鍵，單擊A1單元格，再單擊F8單元格，查看選擇連續(xù)單元格區(qū)域的情況；按下Ctrl鍵，選中A1:B7和C3:E6單元格區(qū)域，查看選擇不連續(xù)單元格區(qū)域的情況。=7\*GB3⑦分別單擊Sheet2和Sheet3工作表標(biāo)簽，在不同的工作表中輸入不同的數(shù)據(jù)，掌握切換不同工作表的方法。=8\*GB3⑧單擊工具欄上的“關(guān)閉”按鈕或執(zhí)行“文件”|“關(guān)閉”命令，將新建的工作簿文件保存到“D:\練習(xí)”文件夾中（如果該文件夾不存在，請自建），文件名為“基礎(chǔ)知識.xls”，退出Excel應(yīng)用程序。標(biāo)題欄標(biāo)題欄菜單欄常用工具欄格式工具欄名稱框編輯欄活動單元格工作表區(qū)列標(biāo)行號任務(wù)窗格編輯按鈕圖4-1Excel2019窗口全選按鈕工作表標(biāo)簽分割條狀態(tài)欄4.2工作簿的建立和管理實驗4-2保存、打開和保護工作簿實驗?zāi)康蘑僬莆展ぷ鞑敬蜷_和保存的方法。②掌握工作簿設(shè)置打開、修改權(quán)限密碼的方法。實驗內(nèi)容在磁盤上建立一個名稱為“管理工作簿.xls”的工作薄，并將它設(shè)置權(quán)限密碼。實驗步驟①在“D:\練習(xí)”文件夾中雙擊打開名稱為“基礎(chǔ)知識.xls”的工作簿。②執(zhí)行“文件”|“另存為”命令，在“另存為”對話框中，將工作簿文件名改為“管理工作簿.xls”。③單擊“另存為”對話框中“工具”按鈕，在彈出的菜單中選擇“常規(guī)選項”命令，設(shè)置打開權(quán)限密碼為“12345”，設(shè)置修改權(quán)限密碼為“54321”。④執(zhí)行“文件”|“關(guān)閉”命令，退出Excel應(yīng)用程序。⑤在“D:\練習(xí)”文件夾中雙擊“管理工作簿.xls”文件，分別輸入打開權(quán)限密碼和修改權(quán)限密碼，查看兩種密碼的不同權(quán)限。4.3工作表的建立實驗4-3Excel數(shù)據(jù)的輸入實驗?zāi)康蘑僬莆諗?shù)值、文本、日期等數(shù)據(jù)的輸入。②掌握自動填充數(shù)據(jù)的方法。實驗內(nèi)容圖4圖4-2輸入數(shù)據(jù)在“輸入數(shù)據(jù)”工作薄的sheet1中輸入如圖4-2所示的數(shù)據(jù)內(nèi)容。實驗步驟啟動Excel2019窗口，新建一個工作簿文件。強制換行：單擊“Sheet1”工作表標(biāo)簽，在單元格A1中輸入“河南財經(jīng)政法大學(xué)”。“河南財經(jīng)”輸完后，按“Alt+Enter”鍵，對單元格中的內(nèi)容進行強制換行，再輸入“政法大學(xué)”。圖4-3設(shè)置自動換行自動換行：在單元格A2中輸入“2019北京奧運會”，此時文本長度超出單元格寬度，若單元格A2中有內(nèi)容，則超出單元格寬度的文本內(nèi)容會被隱藏掉?？稍O(shè)置長文本在單元格中自動換行。選定單元格A2，執(zhí)行“格式”|“單元格”命令，在彈出的如圖4-3所示的“單元格格式”對話框中選擇“對齊”選項卡，在“文本控制”區(qū)域選中“自動換行圖4-3設(shè)置自動換行輸入文本型和數(shù)值型數(shù)據(jù)：在單元格A3中輸入電話號碼，若直接輸入“”，單元格中顯示3716456199，故應(yīng)將其作為文本輸入。輸入時，先輸入英文的單撇號“’”，即鍵入：“’”；輸入數(shù)值型數(shù)據(jù)時，若輸入數(shù)據(jù)的長度超過單元格寬度，Excel2019自動改用科學(xué)計數(shù)法表示，在C3單元格中輸入“4102000”，數(shù)據(jù)顯示為“4.1011E+17”輸入分?jǐn)?shù)：若在單元格A4中直接輸入“3/5”，則顯示為“3月5日”，輸入分?jǐn)?shù)時，應(yīng)先輸入0和一個空格，再輸分?jǐn)?shù)，即在單元格A4中鍵入：“03/5。”輸入日期和時間：在單元格B4中直接輸入“4/5”，則顯示為“4月5日”；在C4單元格按下“Ctrl”+“；”組合鍵輸入當(dāng)前日期；在D4按下“Ctrl”+“Shift”+“；”自動填充系統(tǒng)定義好的序列：在A5單元格中輸入“Sunday”，選中A5單元格，將鼠標(biāo)指針移向填充柄向右拖動到要填充數(shù)據(jù)的最后一個單元格G5，依次填充序列中其他內(nèi)容。圖4-4輸入序列填充數(shù)列：首先在起始的兩個單元格區(qū)域A6和B6分別輸入開始的兩個數(shù)據(jù)“1”和“4圖4-4輸入序列填充等比序列：在A7單元格中輸入初始數(shù)據(jù)3，將鼠標(biāo)指針指向A7單元格右下角的填充柄，并按住鼠標(biāo)右鍵再向右拖動到要填充的單元格位置G7，松開右鍵時，在出現(xiàn)的快捷菜單中選擇“序列”命令，在出現(xiàn)的的“序列”對話框中，選中序列產(chǎn)生在“行”，序列類型選擇“等比序列”，步長值設(shè)置為“2”，終止值設(shè)置“96”，如圖4-4所示，然后單擊“確定”填充自定義序列：首先選擇“工具”|“選項”命令，出現(xiàn)如圖4-5所示的“選項”對話框。單擊“自定義序列”選項卡，在“自定義序列”列表框中選中“新序列”，在“輸入序列”列表框中分別輸入“北京”、“上?！薄ⅰ班嵵荨?、“南京”、“杭州”，每輸入一項按一下回車鍵。輸入完后單擊“添加”按鈕，將輸入的數(shù)據(jù)系列添加到“自定義序列”中，然后單擊“確定”按鈕，關(guān)閉對話框。圖4-5添加自定義序列在單元格A8中輸入“北京”，將鼠標(biāo)指針移向該單元格區(qū)域右下角的填充柄向右拖動到要填充數(shù)據(jù)的最后一個單元格G8，可以依次圖4-5添加自定義序列eq\o\ac(○,11)保存工作簿：將工作簿文件以“數(shù)據(jù)輸入”文件名保存到“D:\練習(xí)”文件夾中。實驗結(jié)果樣式實驗結(jié)果樣式如圖4-2所示。實驗4-4Excel工作表的計算實驗?zāi)康蘑僬莆諉卧褚谩＂谡莆展竭M行數(shù)據(jù)運算。③掌握內(nèi)置的常用函數(shù)的使用。實驗內(nèi)容建立一個名為“數(shù)據(jù)計算”的工作簿，計算和編輯表中的數(shù)據(jù)。實驗步驟在“D:\練習(xí)”文件夾中創(chuàng)建一個文件名為“數(shù)據(jù)計算”的工作簿。在“Sheet1”工作表中輸入如圖4-6所示的數(shù)據(jù)內(nèi)容。提示：“職工號”列中的數(shù)據(jù)用自動填充序列方式填充。圖4-6“數(shù)據(jù)計算”的原始數(shù)據(jù)圖4.6圖4-6“數(shù)據(jù)計算”的原始數(shù)據(jù)圖4.6“教師工資表”原始數(shù)據(jù)提示：小時課酬利用單元格地址絕對引用方法計算。計算應(yīng)發(fā)工資：應(yīng)發(fā)工資=基本工資+課時費。利用COUNT函數(shù)計算總?cè)藬?shù)。利用COUNTIF函數(shù)分別計算教授、副教授、講師的人數(shù)利用AVERAGE函數(shù)分別計算基本工資、課時數(shù)、課時費、應(yīng)發(fā)工資的平均值。提示：計算以上這些字段的最高值和最低值的方法和計算平均值的參數(shù)一樣，最高值的函數(shù)名為Max，最低值的函數(shù)名為Min。圖4圖4-7計算好的數(shù)據(jù)實驗結(jié)果樣式實驗結(jié)果樣式如圖4-7所示。4.4工作表的編輯和格式化操作實驗4-5工作表的編輯實驗?zāi)康蘑僬莆展ぷ鞅淼臄?shù)據(jù)修改、復(fù)制、移動和刪除。②掌握工作表中單元格、行、列的插入和刪除。③學(xué)會為單元格添加批注。實驗內(nèi)容圖4-8“編輯工作表”原始數(shù)據(jù)建立一個名為“編輯工作表”的工作簿，對其中的名稱為“工資表”的工作表圖4-8“編輯工作表”原始數(shù)據(jù)實驗步驟在“D:\練習(xí)”文件夾中建立一個名稱為“編輯工作表”的工作薄。在“工資表”中輸入數(shù)據(jù)內(nèi)容，如圖4-8所示。將其中的工作表“Sheet1”改名為“工資表”。將姓名為“劉朝陽”所在的分公司改為“北京”。刪除姓名為“楊柳青”的行，將“部門”這一列移動到“分公司”后面。在首行前插入兩行，在A1單元格中輸入文字“誠信財務(wù)軟件公司”，在A2單元格中輸入文字“員工薪水表”。分別將姓名為“杜永寧”、“于洋”、“趙玲玲”的單元格插入批注“分公司經(jīng)理”。計算員工薪水：薪水=工作時數(shù)×小時報酬。選中A1:H1單元格區(qū)域，單擊“格式”工具欄上的“合并并居中”工具按鈕，設(shè)置字體為“黑體”、加粗、18號字；選中A2:H2單元格區(qū)域，再次單擊“格式”工具欄上的“合并并居中”工具按鈕，使兩行標(biāo)題都跨行居中顯示。保存所做編輯操作。圖4-9編輯好的工作圖4-9編輯好的工作表實驗結(jié)果樣式如圖4-9所示。實驗4-6工作表的格式化實驗?zāi)康?1\*GB3①學(xué)會設(shè)置數(shù)據(jù)的顯示格式。②學(xué)會設(shè)置表格的邊框和底紋。=3\*GB3③掌握條件格式的用法。圖4-10圖4-10復(fù)制工作表對實驗4-4建立的“數(shù)據(jù)計算”工作薄中的工作表的數(shù)據(jù)進行格式化設(shè)置，使用條件格式功能將“應(yīng)發(fā)工資”介于5000元和6000元之間的數(shù)據(jù)加上灰色底紋，給表格加上合適的邊框和底紋。實驗步驟打開“數(shù)據(jù)計算”工作簿，用鼠標(biāo)右鍵單擊“Sheet1”工作表標(biāo)簽，出現(xiàn)的快捷菜單中選擇“移動或復(fù)制工作表”命令，在“移動或復(fù)制工作表”對話框中的“工作簿”下拉列表框中選擇“新工作簿”，在“建立副本”復(fù)選框中打上勾，單擊確定按鈕，如圖4-11所示，這時就將“Sheet1”工作表復(fù)制到新的工作簿了。關(guān)閉“數(shù)據(jù)計算”工作簿，將新工作簿的“Sheet1”工作表標(biāo)簽改名為“工資表”。在第一行插入標(biāo)題“教師工資表”，并將A1:H1單元格區(qū)域跨行居中顯示，設(shè)置標(biāo)題字體為華文彩云、紅色、加粗、20號字，黃色加上12.5灰色圖案底紋。將工作表中所有數(shù)據(jù)居中對齊顯示。將“應(yīng)發(fā)工資”前面加上“￥”符號，并利用條件格式命令將“應(yīng)發(fā)工資”介于5000元和6000元之間的數(shù)據(jù)加上灰色底紋：選定H3:H15單元格區(qū)域，執(zhí)行“格式”|“條件格式”菜單命令，在彈出的如圖4-10所示的“條件格式”對話框中的“條件1”下拉列表框中選擇“單元格數(shù)值”，在條件運算符下拉列表框中選擇“介于”，在條件值框中輸入“5000”到“6000”，單擊“格式”按鈕，在“單元格格式”對話框選擇“圖案”選項卡，然后選擇灰色底紋，單擊“確定”按鈕，返回“條件格式”對話框，再按一次“確定圖4-11“條件格式”對話框選定A17:A19單元格區(qū)域，利用“格式”|“單元格圖4-11“條件格式”對話框選中A2:H19單元格區(qū)域，利用“格式”|“單元格”|“邊框”命令,將數(shù)據(jù)表加上紫羅蘭顏色的雙線外邊框，藍色單線內(nèi)邊框。保存工作簿，文件名為“教師工資表”。實驗結(jié)果樣式圖4-12教師工資表實驗結(jié)果樣式如圖圖4-12教師工資表4.4數(shù)據(jù)管理實驗4-7數(shù)據(jù)管理實驗?zāi)康蘑倭私鈹?shù)據(jù)管理功能。②掌握數(shù)據(jù)排序的方法。③掌握數(shù)據(jù)篩選的方法。=4\*GB3④掌握分類匯總的方法。實驗內(nèi)容在工作表中進行數(shù)據(jù)排序、篩選和分類匯總，并進行簡單的圖表制作。實驗步驟打開實驗4-5所建的“編輯工作表”工作簿，將它以“數(shù)據(jù)管理”文件名另存。將工作表標(biāo)簽“Sheet2”重命名為“數(shù)據(jù)排序”，將“Sheet3”重命名為“自動篩選”，添加二個工作表，一個命名為“高級篩選”，一個命名為“分類匯總”將“工資表”表中的A3:H17單元格區(qū)域的數(shù)據(jù)復(fù)制到其他四個工作表的A1:G15區(qū)域。圖4-13排序后的結(jié)果在“數(shù)據(jù)排序”工作表進行數(shù)據(jù)排序：單擊“數(shù)據(jù)排序”工作表中的任意單元格，執(zhí)行“數(shù)據(jù)”|“排序”命令，在出現(xiàn)的“排序”對話框中按“薪水”為第一關(guān)鍵字升序，“小時報酬”為第二關(guān)鍵字降序，“工作時數(shù)”圖4-13排序后的結(jié)果在“自動篩選”工作表進行自動篩選：單擊“自動篩選”工作表中的的任意單元格，執(zhí)行“數(shù)據(jù)”|“篩選”|“自動篩選”命令，篩選出分公司=“鄭州”并且“薪水”大于等于6000元并小于等于20000元的員工。篩選結(jié)果如圖4-14所示。圖4-14自動篩選后的結(jié)果在“高級篩選”工作表中篩選出工作時數(shù)等于160，或者薪水小于圖4-14自動篩選后的結(jié)果圖4-15設(shè)置條件區(qū)域單擊“高級篩選”工作表標(biāo)簽，在j1:k3單元格將篩選條件輸入到如圖4-圖4-15設(shè)置條件區(qū)域圖4-16高級篩選對話框單擊數(shù)據(jù)區(qū)域中的任意單元格，選擇“數(shù)據(jù)”|“篩選”|“高級篩選”命令，出現(xiàn)如圖4-16所示的“高級篩選”對話框，選中“將篩選結(jié)果復(fù)制到其他位置”單選按鈕，設(shè)置“列表區(qū)域”地址范圍為$A$1:$H$15，“條件區(qū)域”地址范圍為$J$1:$K$3，選擇將篩選后的結(jié)果“復(fù)制到”A17單元格，在“選擇不重復(fù)的記錄”復(fù)選框中打上勾，單擊“確定”按鈕。高級篩選后的結(jié)果如圖4-17圖4-16高級篩選對話框圖4-19分公司薪水求和匯總圖4-18分公司薪水最大值圖4-17高級篩選后的結(jié)果在“分類匯總”工作表中按照“分公司”以“最大值”和“圖4-19分公司薪水求和匯總圖4-18分公司薪水最大值圖4-17高級篩選后的結(jié)果按“分公司”排序：單擊“分類匯總”工作表中的“分公司”字段下的任意一個單元格（如：D4單元格），單擊常用工具欄中的升序排序按鈕。計算每個分公司中薪水的最大值：執(zhí)行“數(shù)據(jù)”|“分類匯總”命令，在彈出的如圖4-18所示的“分類匯總”對話框中選擇“分類字段”為“分公司”，“匯總方式”為“最大值”，選定“匯總項”為“薪水”，單擊“確定”按鈕。計算每個分公司薪水的總值：執(zhí)行“數(shù)據(jù)”|“分類匯總”命令，在彈出的如圖4-19所示的“分類匯總”對話框中選擇“分類字段”為“分公司”，“匯總方式”為“求和”，選定“匯總項”為“薪水”。將“替換當(dāng)前分類匯總”復(fù)選框?qū)慈サ簦瑔螕簟按_定”按鈕。匯總結(jié)果如圖4-20。圖4-21“圖表類型“對話框圖4-20分類匯總后的結(jié)果以“薪水”為數(shù)據(jù)，在“原始數(shù)據(jù)清單”工作表中生成如圖4-圖4-21“圖表類型“對話框圖4-20分類匯總后的結(jié)果單擊“工資表”的工作表標(biāo)簽，選擇B3:B17和H3:H17單元格區(qū)域。單擊常用工具欄的“圖表向?qū)А卑粹o，在彈出的如圖4-21所示的“圖表向?qū)Р襟E1-圖表類型”對話框中選擇“自定義圖表類型”選項卡下的“分裂的餅圖”，然后一直單擊圖表向?qū)У摹跋乱徊健保趫D表向?qū)ё詈蟮牟襟E中單擊“完成”，即可建立成圖表。

圖4-22完成后圖4-22完成后的“分裂的餅圖”效果實驗4-8Excel數(shù)據(jù)圖表化實驗?zāi)康?1\*GB3①掌握嵌入圖表和獨立圖表的創(chuàng)建。=2\*GB3②掌握圖表的編輯。=3\*GB3③掌握圖表的格式化。實驗內(nèi)容圖4-23“教師工資表”圖表為實驗4-6所建的“教師工資表”中的“基本工資”和“應(yīng)發(fā)工資”數(shù)據(jù)生成“圖4-23“教師工資表”圖表實驗步驟打開實驗4-6所建的“教師工資表”工作薄。建立各位教師的“基本工資”和“應(yīng)發(fā)工資”的簇狀柱形圖表：建立圖表的數(shù)據(jù)在“工資表”的B2:B15、E2:E15和H2:H15單元格區(qū)域選擇。圖表為“標(biāo)準(zhǔn)類型”選項卡下的的“柱形圖”，子圖表類型為“簇狀柱形圖”。圖表標(biāo)題為“教師工資表”，分類（X）軸標(biāo)題為“姓名”，數(shù)值（Y）軸標(biāo)題為“工資”，其它選項選擇默認(rèn)值。建立的圖表如圖4-23所示。對“教師工資表”圖表進行如下格式化操作：圖4.-24“圖表區(qū)格式”對話框修飾圖表的邊框和底紋：選定圖表區(qū)，單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選定“圖表區(qū)格式”命令，在出現(xiàn)如圖4-24所示的“圖表區(qū)格式”對話框中單擊“圖案”選項卡，邊框選定“自定義”、“圓角”，并選用最粗的紅色線形；單擊“填充效果”圖4.-24“圖表區(qū)格式”對話框設(shè)置圖表標(biāo)題的格式：在圖表標(biāo)題區(qū)單擊鼠標(biāo)右鍵，選中“圖表標(biāo)題格式”命令，將標(biāo)題設(shè)置為黑體、加粗傾斜、黃色14號字。改變圖例的位置：在圖例區(qū)單擊鼠標(biāo)右鍵，選中“圖例格式”命令，單擊“圖案”選項卡，將圖例邊框改為帶陰影的邊框，單擊“位置”選項卡，將圖例位置移動到圖表底部。改變圖表的位置：在圖表區(qū)單擊鼠標(biāo)右鍵，選中“位置”命令，將圖表“作為新工作表插入”，使嵌入式圖表變獨立的圖表。實驗結(jié)果樣式圖4-25修飾好的圖表效果實驗結(jié)果樣式如圖4-25所示。圖4-25修飾好的圖表效果4.7綜合應(yīng)用實例實驗4-9多張工作表的編輯實驗?zāi)康蘑僬莆斩鄰埞ぷ鞅淼臄?shù)據(jù)輸入及編輯。②掌握多張工作表的地址引用。③掌握條件格式的應(yīng)用。實驗內(nèi)容在多張工作表中進行數(shù)據(jù)輸入、計算和表格修飾。圖4-圖4-26平時、期中、期末成績的原始數(shù)據(jù)實驗步驟創(chuàng)建一個文件名為“多張工作表編輯”的工作簿。將工作表標(biāo)簽“Sheet1”重命名為“平時成績”，將“Sheet2”重命名為“期中成績”，將“Sheet3”重命名為“期末成績”，添加一個工作表為“總評同時向工作表中輸入相同的數(shù)據(jù)：單擊第一張工作表“平時成績”的工作表標(biāo)簽，然后按下Shift鍵，再單擊最后一張工作表“總評成績”的工作表標(biāo)簽，使四張工作表全部選中，成為一個工作組，這時在任意一個工作表中輸入數(shù)據(jù)，可以同時向四張工作表輸入如圖4-13中所示的相同的數(shù)據(jù)。注意：“平時成績”、“期中成績”、“期末成績”三張工作表的每位同學(xué)每門功課的成績要按照圖4-26所示分別輸入。圖4-27總評成績的原始數(shù)據(jù)在“總評成績”工作表要單獨增加“總分”和“平均”二列字段，如圖4-27所示圖4-27總評成績的原始數(shù)據(jù)計算“總評成績”的各門功課的成績：總評成績=平時+期中成績×10%+期末成績×70%，計算“總評成績”的總分和平均分，并按平均分降序排序。設(shè)置“總評成績”工作表的標(biāo)題格式為“