實驗七Excel電子表格的綜合實驗

1、實驗七 Excel電子表格的綜合實驗實驗?zāi)康恼莆諗?shù)據(jù)列表的排序、篩選與計算。掌握數(shù)據(jù)的分類匯總。掌握頁面設(shè)置與美化。實驗內(nèi)容新建EXCEL工作簿文件，請分別在其Sheet1和Sheet2輸入以下內(nèi)容(可以復(fù)制)，并以你的：學(xué)號+姓名+Excel實驗七.xls保存文件。成績統(tǒng)計表姓名性別高等數(shù)學(xué)計算機英語總分劉飛雷男69 78 77 李小利女78 81 78 趙季云女63 49 98 高紅女69 72 69 趙波男63 49 87 劉紅女45 56 85 王浩明男66 62 56 王明男66 62 56 孫名松男4923 71李剛男87 78 96 孫云飛男69 78 97 劉書紅女88 56

2、85 李飛男69 78 90 劉雨女85 67 75 1.在Sheet1中對數(shù)據(jù)表中填寫數(shù)據(jù)的單元格進行有效性驗證，以保證用戶輸入數(shù)據(jù)過程中少犯錯誤，當(dāng)有錯誤發(fā)生時，計算機會提醒輸入者檢查自己的輸入。數(shù)據(jù)表中需要對各門課程成績進行限制，要求輸入的數(shù)據(jù)必須是0至100之間的整數(shù)，否則出現(xiàn)出錯警告對話框“數(shù)據(jù)超限，請檢查！” 提示: 執(zhí)行菜單命令【數(shù)據(jù)】/【有效性】2.在Sheet1中的使用“格式”菜單中“自動套用格式”中的“彩色2”格式，并設(shè)置該表格的外框線為雙線邊框，內(nèi)框線為單實線，所有數(shù)據(jù)和文字居中顯示，對該表按“總分”進行按降序排列。3.將Sheet2中的數(shù)據(jù)按性別排序，先男生、后女生，性

3、別相同的按總分降序排列。并按性別分別求出男生、女生的各科平均分（保留1位小數(shù)）。、提示: 1可利用菜單“數(shù)據(jù)/ 排序”，進行相應(yīng)的設(shè)定。 2將光標(biāo)定位在該表格中的任意單元格中，選擇菜單“數(shù)據(jù)/分類匯總”，本例中分類的字段為性別，匯總的字段為三門課程的分?jǐn)?shù)及總分，匯總的方式為求平均值。 4.在原有分類匯總的基礎(chǔ)上，再匯總出男女學(xué)生的人數(shù)。按如下樣張所示，分級顯示及編輯匯總數(shù)據(jù)。提示: 1.將光標(biāo)定位在該表格中的任意單元格中，選擇菜單“數(shù)據(jù)/分類匯總”，進行相應(yīng)的設(shè)定，如下圖。2. 將生成的匯總項名稱“男平均值”、“女平均值”、“男計數(shù)”，“女計數(shù)”，改為“男生平均分”、“女生平均分”、“男生人數(shù)

4、”，“女生人數(shù)”如樣張所示。樣張如下：5.對Sheet2進行如下頁面設(shè)置，并進行打印預(yù)覽：紙張大小為A4，文檔打印時水平居中，上、下頁邊距為3厘米。設(shè)置頁眉“分類匯總表”、居中、粗斜體，設(shè)置頁腳為當(dāng)前日期，靠右安放。打印工作表的行號和頁號，打印網(wǎng)格線。提示: 執(zhí)行菜單命令【文件】/【頁面設(shè)置】，如下圖所示。6. 將作業(yè)服務(wù)器實驗文件夾中的“工資表”下載到自己的存儲設(shè)備中，然后將其復(fù)制到本次作業(yè)文件的Sheet3中。表中的基礎(chǔ)數(shù)據(jù)（基本工資、加班金額、病假扣款、事假扣款）是由財務(wù)人員人工輸入，其他數(shù)據(jù)可以通過如下規(guī)則編制公式或函數(shù)計算求得。 （1） 應(yīng)發(fā)金額 = 基本工資 + 加班金額 （2）

5、失業(yè)保險金 = 基本工資 * 0.01 （3） 養(yǎng)老保險金 = 基本工資 * 0.014 （4） 應(yīng)稅所得額的計算?！皯?yīng)稅所得額”就是需要納稅的那部分收入，每個人的應(yīng)稅所得額等于其“應(yīng)發(fā)工資”減去“病假扣款”、“事假扣款”、“失業(yè)保險金”、“養(yǎng)老保險金”，再減去1000元后的值，如果這個值小于1000，那么應(yīng)稅所得額為0；如果這個值大于1000元，那么應(yīng)稅所得額為超出部分的數(shù)額。公式為： =IF(G4-H4-I4-J4-K41000,0,G4-H4-I4-J4-K4-1000)（5） 扣稅金=應(yīng)稅所得額*0.1（6） 扣款合計 = 病假扣款 + 事假扣款 + 失業(yè)保險金 + 養(yǎng)老保險金+扣稅金

6、 （7） 實發(fā)金額 = 應(yīng)發(fā)金額 扣款合計按照上述規(guī)則設(shè)置公式，請將該工資表中的所有空白單元格計算出相應(yīng)數(shù)據(jù)。7、為了使表中各行記錄的顯示更加容易區(qū)分，經(jīng)常采用隔行顯示的格式，就是為單元格隔行添加不同的底紋顏色，如下樣張所示?？衫谩案袷健辈藛沃械摹皸l件格式”來完成。在工資表的名稱框中輸入“A4:O21”后按回車鍵，快速選中工作表中全部記錄數(shù)據(jù)，執(zhí)行菜單欄【格式】|【條件格式】，在打開的對話框中，在“條件1”下拉列表中選擇“公式”選項，在后面的文本框中輸入公式：=MOD(ROW(),2)=0公式的含義是：如果行號除以2沒有余數(shù)，則此行為偶數(shù)行，否則為奇數(shù)行。單擊“格式”按鈕，選擇圖案顏色。單擊【確定】。底紋顏色可自定。樣張如下：8、將該上述工資表復(fù)制到sheet4中，利用自動篩選功能分析數(shù)據(jù)。所謂篩選，是指在表格中選取符合一定條件的數(shù)據(jù)。在sheet4中篩選查找“銷售部”、“事假扣款”為0，且“實發(fā)金額”在1400元以上的所有記錄。執(zhí)行菜單【數(shù)據(jù)】|【篩選】|【自動篩選】。9. 將sheet1至 sheet4分別更名為中文名字并設(shè)置工作表標(biāo)簽顏色，名稱顏色自定。10.為工資簿添加密碼。工資表中保存了一個企業(yè)中