實習五_1 中文電子表格Excel 一、實習目的1掌握Excel電子表格的基本概念；2掌握Excel函數和公式及應用；3掌握Excel單元格的格式設置及報表設計；4掌握圖表的創建、編輯、修飾等操作；5掌握數據分析與管理的方法。二、知識點1基本概念工作薄、工作表、單元格、單元格名稱、單元格區域、數據類型、字符（文本）型數據、數值型數據、日期型數據。2單元格、工作表與工作薄的相互關系3Excel中數據的錄入技巧數據有效性驗證方法、自動填充方式及自定義序列。4數據及單元格格式設置 字體、字號、對齊方式、數字格式、單元格格式、條件格式。5Excel公式及單元格的引用 運算符及優先級、單元格的四種引用方式。6函數應用與幫助文件通過以下函數的使用，并學習使用Excel幫助文件。常用函數：SUM；AVERAGE；IF；COUNT；RAND；RANK等數學與三角函數：INT；ROUND；ABS等查找與引用函數：LOOKUP；VLOOKUP；HLOOKUP；CHOOSE等文本函數：LEFT；MID；RIGHT等7數據圖表掌握柱狀圖、折線圖、圓餅圖的創建方法及使用條件。8數據分析與管理方法數據排序、數據篩選、分類匯總及數據透視表。9Excel的頁面設置與打印三、實習內容及要求（一）學生成績表制作（如圖5-1所示）步驟及要求如下：1新建一個工作薄，在sheet1的第一行錄入表格標題；2在第二行錄入表頭；3學號可以通過自動填充獲得，注意數據類型；4姓名可以從實習素材中獲得；5語文成績請手工錄入，錄入前加入有效性驗證，數據范圍50-100之間，并設置合理的出錯警告，如圖5-2所示；圖5-26其它各門課成績用隨機函數生成，范圍在50-100之間，結果如圖5-3所示；7選中區域A1:L19，復制到剪貼板，選中Sheet2中的A1單元格，再利用選擇性粘貼，將所有數據粘貼到Sheet2。注意，在選擇性粘貼對話框中，請選中只粘貼“數值”（默認為粘貼“全部”），如圖5-4所示。后面的所有工作均在Sheet2中完成。8用求和函數求出總分，用求均值函數分別求出各學生的平均分及各課程的平均成績。并將平均成績列設置成只保留2位小數；將課程平均成績設置成保留1位小數。9利用排序函數RANK按總分降序。10利用IF函數生成獎學金等級，規則為：前3名“一等獎”，4-6名“二等獎”，7-13名“三等獎”，其他“四等獎”。11其它表格格式按樣表處理。最后通過打印預覽看到的排版效果要和樣表一致。圖5-312將sheet2的工作表名改為“學生成績表”圖5-413全部處理完成后將工作薄保存，文件名為“Excel作業.xls”。（二）Excel報表制作 1從實習素材中獲得“派車單.xls”文件，打開后，將全部數據復制到前一個工作薄“Excel作業.xls”的sheet3中。進行以下操作。2完成表格中所有的運算，公式為： 運費=（里程+等時10）單價 合計收費=運費+高速費 司機提成=里程0.153紙張大小設置（根據內容決定紙張大小及方向）：文件/頁面設置(如圖5-5所示)。4進行單元格格式設置： （1）添加表格線：格式/單元格可以在格式工具欄上找到相應的工具按鈕，完成選定單元格或區域的邊框設置，也可以在單元格格式中對邊框進行設置，如圖5-6所示。（2）設置適合的行高和列寬在“格式/行/行高”菜單中設置表格行高，如圖5-7所示。（3）打印預覽，查看初步排版效果，并在其中單擊“頁邊距”按鈕對各行寬度及上下左右邊距進行調整，如圖5-8和圖5-9所示。（4）表標題設置為：“西北農林科技大學通勤車統計”，黑體，24磅（5）頁腳設置為第X頁，共X頁。如圖5-10，5-11。（6）回到編輯畫面，文件頁面設置工作表打印標題設置表頭，使每一頁都有表頭。將文檔第一行設置為表頭，如圖5-12所示。（7）報表設計和排版最終結果，打印預覽如圖5-13所示：（9）全部處理完成后將工作表sheet3命名為“報表設計”。（三）Excel數據分析與管理 從實習素材中獲得“新生名單.xls”文件，將全部數據復制到前一個工作薄“Excel作業.xls”的sheet4中。進行以下操作。（1）數據篩選功能選擇“數據”/ “篩選”/ “自動篩選”命令，進入篩選狀態，在要建立篩選的字段名下拉列表框中選擇要篩選的確切值；或單擊“自定義”按鈕，在彈出的對話框中 輸入篩選條件。要求：找出與自己在同一個高中畢業的學生或與自己在同一個縣（市）的學生人數，并將結果寫在實驗總結中，同時將工作表sheet4命名為“數據篩選”。 （2）數據透視表：可以對多個字段進行分類匯總從實習素材中獲得“新生名單.xls”文件，將全部數據復制到前一個工作薄“Excel作業.xls”的sheet5中。任務：統計各民族中男女生人數 要求：將數據統計結果的工作表命名為“數據透視表”。（四）數據的圖表化打開上面建立的工作簿“Excel作業.xls”，新建工作表sheet5、sheet6、sheet7，自己設計表格及數據，并分別用柱狀圖、折線圖、圓餅圖對數據進行圖表化。并將工作表分別命名保存為“柱狀圖”、“折線圖”、“圓餅圖”。（五）單元格引用打開上面建立的工作簿“Excel作業.xls”，新建工作表sheet8，錄入表5-1的數據，計算Z。表5-1 Z=X*Yx y1015202530354045505560001011010100注意：在公式復制時，單元格地址的引用方式。要求：全部處理完成后將該工作表保存命名為“單元格地址引用”。（六）計算大學生綜合測評智育成績從實習素材中獲得“綜合測評成績.doc”文件，打開后進行以下操作。（1）在工作簿“Excel作業.xls”中，新建工作表sheet9，sheet10, sheet11，將“課程信息表”中內容復制到sheet9中，命名為“課程信息表”，將“學習成績表”中內容復制到sheet10中，命名為“學習成績表”。（2）將學號、姓名再復制一份到sheet11中，并根據公式算出智育成績：（3）將sheet11按照智育成績由大到小的順序排列，最終命名為“智育成績”（七）郵件合并“郵件合并”常用于將數據表中的各行數據批量轉成格式化的Word文檔,從而高效率地完成各種批量作業。比如大學錄取通知書、批量制作個人名片等、會議通知。使用郵件合并功能，就可以將幾百份、甚至幾千份、幾萬份郵件迅速處理完畢。要求：利用“郵件合并”功能制作畢業論文（設計）打分匯總表。操作步驟如下：（1）制作數據源。從原始材料素材庫下載“畢業答辯成績.xls”文件，將數據復制到上面建立的工作簿 “Excel作業.xls”的sheet13中。進行以下操作： 利用公式計算剩余字段中的數據，其中字段“答辯成績占70%”、“評閱教師成績占20%”、“指導教師成績占10%”分別表示按指定比例換算后的成績，將最終計算結果保存，sheet13命名為“郵件合并數據源” （2）制作主文檔。在Word中畢業論文（設計）打分匯總表，格式如下：畢業論文（設計）打分匯總表學院（系）： 專業班級： 學生姓名： 學號： 畢業論文（設計）題目打分所占比例得分備注指導教師打分10%評閱人打分20%答辯平均得分70%總評得分備注（3）進行郵件合并，最終匯總表中成績保留一位