《案例實踐五Excel》PPT課件.ppt

電子報表處理軟件Excel 案例描述 二 Excel數(shù)據(jù)計算 參考效果圖 任務(wù)1 在各 成績表 中計算 平時成績 計算各位同學(xué)的總評 其計算規(guī)則為 平時成績15 期中成績15 期末成績70 并按總評對其進行單科排名 總分和名次放入所對應(yīng)的單元格 任務(wù)2 在每個課程成績工作表中 計算課程各分數(shù)段分布人數(shù)及比例 平均分 標(biāo)準(zhǔn)方差 計算平均分和標(biāo)準(zhǔn)方差時 小數(shù)點后保留2位小數(shù) 任務(wù)3 參考效果圖 從各課程成績表中引用總評成績 并填入 成績匯總表 中各相應(yīng)單元格 計算單科平均分 各科總分 任務(wù)4 參考效果圖 在 成績匯總表 中 按總分進行排名 并利用函數(shù)判斷各科是否超過單科平均分 預(yù)備知識 Excel中的計算公式公式的使用運算符單元格引用 絕對引用 相對引用 混合引用 出錯信息函數(shù)函數(shù)基本知識常用函數(shù) SUM AVERAGE MAX MIN COUNT 選擇性粘貼數(shù)據(jù) 任務(wù)完成 任務(wù)1步驟1 選擇 成績表 用AVERAGE公式求得平時成績填入H列相應(yīng)單元格 其余同學(xué)的平時成績可利用填充柄來完成計算 選擇H3單元格 將鼠標(biāo)指針移動至該單元格右下角位置 出現(xiàn)黑色十字形時拖動至相應(yīng)需要填充的最后一個單元格 步驟2 選中單元格K3 在編輯欄內(nèi)輸入 ROUND H3 15 I3 15 J3 70 2 按 回車 鍵確認 求得總評成績 四舍五入 保留整數(shù) 用填充柄向下填充 完成所有學(xué)生的總評成績輸入 步驟3 選中單元格L3 輸入 RANK K3 K 3 K 32 0 回車確認 求得的排名依次填入相應(yīng)單元格 任務(wù)完成 任務(wù)2步驟1 在每個課程成績工作表中 在學(xué)生成績列表下方建立各成績段分布人數(shù)及比例平均分和標(biāo)準(zhǔn)方差的相應(yīng)單元格 步驟2 利用SUMPRODUCT函數(shù)計算各個分數(shù)段分布人數(shù) 比如 SUMPRODUCT K3 K32 90 K3 K32 100 可以計算90 100分段的人數(shù) 同時計算該分數(shù)段的人數(shù)分布比例 步驟3 選定K3 K32 用AVERAGE公式求得該課程的平均分 步驟3 選定K3 K32 用STDEV公式求得該課程的標(biāo)準(zhǔn)方差 任務(wù)完成 任務(wù)3步驟1 選擇 成績匯總表 用VLOOKUP公式從各課程成績表中引用總評成績并填入相應(yīng)單元格 比如 在編輯欄中輸入 VLOOKUP A3 高等數(shù)學(xué)成績 A 3 K 32 11 0 回車確認 再利用填充柄完成其余同學(xué)的總評成績 步驟2 重復(fù)步驟1 完成其他四門課程總評成績的輸入 步驟3 選中單元格C33 單擊編輯欄中的 插入函數(shù) 按鈕 在彈出的 插入函數(shù) 對話框中選擇 AVERAGE 函數(shù) 在打開的AVERAGE 函數(shù)參數(shù) 對話框中的第一個參數(shù) Number1 文本框中輸入C3 C32 也可以通過選擇區(qū)域完成輸入 單擊 確定 按鈕完成第一位同學(xué)高等數(shù)學(xué)平均分的計算 步驟4 其余同學(xué)可利用填充柄來完成計算 步驟5 總分的計算可參考步驟3和步驟4 將AVERAGE函數(shù)換成SUM函數(shù) 并修改相應(yīng)的數(shù)據(jù)區(qū)域C3 G3即可 任務(wù)完成 任務(wù)4步驟1 選中單元格K3 在編輯欄內(nèi)輸入 RANK J3 J 3 J 32 0 按 回車 鍵確認 求得按總分的排名 用填充柄向下填充 完成所有學(xué)生的排名 步驟2 選中單元格L3 在編輯欄內(nèi)輸入 IF AND E3 E 33 F3 F 33 G3 G 33 H3 H 33 I3 I 33 是 否 IF函數(shù)是判斷條件 第一個參數(shù) 條件為真返回 是 為假返回 否 以此求得單科是否超過平均分 用填充柄向下填充 完成對所有同學(xué)的判斷 案例描述 三 工作表格式化任務(wù)1 設(shè)置 學(xué)生信息 表標(biāo)題格式為隸書 22號字 并要求合并及居中 任務(wù)2 將 成績匯總表 中的 單科平均分 保留兩位小數(shù) 并將各列以 最合適列寬 顯示 任務(wù)3 將 成績匯總表 中各科成績低于60分的學(xué)生成績用紅色的字體 黃色的底紋顯示 任務(wù)4 為 學(xué)生信息 表中數(shù)據(jù)所在區(qū)域加田字框 粗線外框 細線內(nèi)框 預(yù)備知識 工作表的格式化數(shù)字的格式化字體的設(shè)置設(shè)置列寬與行高數(shù)據(jù)對齊方式設(shè)置設(shè)置工作表的邊框與底紋自動套用格式設(shè)置條件格式的設(shè)置 任務(wù)完成 任務(wù)1步驟1 選定 學(xué)生信息 表 步驟2 選定A1 G1單元格區(qū)域 利用 格式 菜單下的 單元格 命令 或直接右擊選中的單元格區(qū)域 在彈出的快捷菜單中選擇 設(shè)置單元格格式 彈出 單元格格式 對話框 步驟3 在 單元格格式 對話框中 單擊 字體 選項卡 設(shè)置相應(yīng)的字體為隸書 字號為22 步驟4 單擊 對齊 選項卡 水平對齊方式選擇 居中 并選中其中的 合并單元格 復(fù)選框 最后單擊 確定 按鈕 任務(wù)完成 任務(wù)2步驟1 選定 成績匯總表 步驟2 選定C33 G33單元格區(qū)域 單擊 格式 菜單下的 單元格 命令 或直接右擊選中的單元格區(qū)域 在彈出的快捷菜單中選擇 設(shè)置單元格格式 彈出 單元格格式 對話框 步驟3 在 單元格格式 對話框中 單擊 數(shù)字 選項卡 在 分類 列表框中選中 數(shù)值 設(shè)置小數(shù)位數(shù)為2 單擊 確定 按鈕 步驟4 選定整張 成績匯總表 中的數(shù)據(jù)區(qū)域 單擊 格式 菜單下的 列 最合適的列寬 命令 即可完成最合適的列寬的設(shè)置 任務(wù)完成 任務(wù)3步驟1 選定 成績匯總表 步驟2 選定C3 G32單元格區(qū)域 單擊 格式 菜單下的 條件格式 命令 彈出 條件格式對話框 步驟3 在 條件格式 對話框中的 條件1 欄中的3個組合框中分別選擇或輸入 單元格數(shù)值 小于 60 步驟4 單擊 條件格式 對話框中的 格式 按鈕 在彈出的 單元格格式 對話框的 字體 選項卡中選擇紅色字體 在 圖案 選項卡中選擇黃色底紋 最后單擊 確定 按鈕 任務(wù)完成 任務(wù)4步驟1 選定 學(xué)生信息 表 步驟2 選定數(shù)據(jù)區(qū)域A1 G32 單擊 格式 菜單下的 單元格 命令 或直接右擊 設(shè)置單元格格式 命令 彈出 單元格格式 對話框 步驟3 在 單元格格式 對話框中選擇 邊框 選項卡 在該選項卡中先選擇粗線的線條樣式 再單擊 預(yù)置 欄內(nèi)的 外邊框 按鈕 同樣再選擇細線的線條樣式 單擊 預(yù)置 欄內(nèi)的 內(nèi)部 按鈕 最后單擊 確定 按鈕 案例描述 四 數(shù)據(jù)庫管理與分析任務(wù)1 將 成績匯總表 中的 各門課程成績 和 總分 列數(shù)據(jù)復(fù)制至 成績表備份 中對應(yīng)區(qū)域 在 姓名 列后插入一列 專業(yè) 并從 學(xué)生信息 表中復(fù)制相關(guān)信息 填入相應(yīng)單元格 任務(wù)2 將 成績表備份 表中的數(shù)據(jù)按總分降序排列 若總分相同的 則按 大學(xué)英語 降序排列 任務(wù)3 查看 成績備份表 中專業(yè)是 軟件工程 且總分大于400分同學(xué)的相關(guān)信息 任務(wù)4 查看 成績備份表 中不及格的學(xué)生的相關(guān)信息 并將其復(fù)制到該表下方的相關(guān)區(qū)域 案例描述 四 數(shù)據(jù)庫管理與分析 續(xù) 任務(wù)5 在 成績備份表 中按專業(yè)對 計算機導(dǎo)論 課程進行平均分的分類匯總 任務(wù)6 在 成績備份表 中創(chuàng)建一個圖表 顯示每個課程的各個專業(yè)平均分比較 顯示在J3 R20區(qū)域 要求以 各專業(yè)課程平均分比較 為標(biāo)題 以 專業(yè)平均值 作為圖例項 圖例位于圖表右方 任務(wù)7 在 成績匯總表 中 建立一張數(shù)據(jù)透視表 要求按專業(yè)比較 高等數(shù)學(xué) 課程男 女生的平均成績 預(yù)備知識 數(shù)據(jù)庫的概念數(shù)據(jù)庫 字段 記錄 數(shù)據(jù)清單創(chuàng)建數(shù)據(jù)清單的規(guī)則記錄的排序記錄的篩選記錄的分類匯總 圖表基本概念建立圖表基本步驟數(shù)據(jù)點和數(shù)據(jù)系列坐標(biāo)軸標(biāo)志分類圖例 任務(wù)完成 任務(wù)1步驟1 選中 成績匯總表 選定數(shù)據(jù)區(qū)域C2 H32 并右擊 在快捷菜單中選擇 復(fù)制 命令 選擇 成績備份表 中的C2單元格 右擊從彈出的快捷菜單中選擇 選擇性粘貼 命令 在彈出的 選擇性粘貼 對話框中選定粘貼 數(shù)值 并單擊 確定 按鈕 步驟2 選擇 成績備份表 單擊 姓名 列后一列的列標(biāo) 本任務(wù)為C 選中整列 右鍵彈出快捷菜單 選擇 插入 命令 在 姓名 列后就生成了一空列 在單元格C2輸入字段名稱 專業(yè) 再從 學(xué)生信息 表中將 專業(yè) 相關(guān)信息復(fù)制到產(chǎn)生的空列中 也可利用VLOOKUP函數(shù) 任務(wù)完成 任務(wù)2步驟1 選擇 成績表備份 數(shù)據(jù)表數(shù)據(jù)清單中的任意一個單元格 單擊 數(shù)據(jù) 菜單下的 排序 命令 彈出 排序 對話框 步驟2 在彈出的 排序 對話框中選擇主要關(guān)鍵字為 總分 選中 降序 次要關(guān)鍵字為 大學(xué)英語 并選中 降序 最后單擊 確定 按鈕 完成排序操作 任務(wù)完成 任務(wù)3步驟1 選擇 成績表備份 工作表數(shù)據(jù)清單所在區(qū)域中的任一單元格 單擊 數(shù)據(jù) 菜單下的 篩選 自動篩選 命令 此時在每個列標(biāo)題右側(cè)出現(xiàn)一個向下的箭頭 步驟2 在 專業(yè) 字段名右側(cè)下拉列表框中選擇 軟件工程 此時系統(tǒng)將篩選出軟件工程專業(yè)的所有記錄 步驟3 在 總分 字段名右側(cè)下拉列表框中選擇 自定義 在彈出的 自定義自動篩選方式 對話框中輸入相應(yīng)條件 總分大于400 單擊 確定 按鈕后 數(shù)據(jù)清單中顯示的信息即是任務(wù)中所需的信息 任務(wù)完成 任務(wù)4步驟1 在 成績表備份 數(shù)據(jù)清單所在區(qū)域以外空白位置設(shè)置一個條件區(qū)域 本任務(wù)在D34 H39區(qū)域設(shè)置 步驟2 選中 成績表備份 數(shù)據(jù)清單所在區(qū)域中的任一單元格 單擊 數(shù)據(jù) 菜單下的 篩選 高級篩選 命令 彈出 高級篩選 對話框 步驟3 設(shè)置相關(guān)參數(shù) 列表區(qū)域 的選擇默認 將數(shù)據(jù)源所在的區(qū)域全選 條件區(qū)域 在本任務(wù)中選擇E34 I39區(qū)域 方式 設(shè)置為 將篩選結(jié)果復(fù)制到其他位置 并在 復(fù)制到 欄內(nèi)輸入用來顯示符合條件的信息的區(qū)域 可只輸入該區(qū)域的首個單元格 本任務(wù)中選擇A40 最后單擊 確定 按鈕 任務(wù)完成 任務(wù)5步驟1 選中 成績表備份 工作表數(shù)據(jù)清單 單擊 專業(yè) 字段所在列數(shù)據(jù)區(qū)域任一單元格 利用常用工具欄中的 升序排列 按鈕或 降序排列 按鈕將其進行排序 步驟2 將活動單元格定位于 成績表備份 數(shù)據(jù)區(qū)域任一位置 單擊 數(shù)據(jù) 菜單下的 分類匯總 命令 彈出 分類匯總 對話框 步驟3 在彈出的 分類匯總 對話框中設(shè)置 分類字段 為 專業(yè) 匯總方式 為平均值 在 選定匯總項 中選中 計算機導(dǎo)論 其他選項設(shè)置為默認 最后單擊 確定 按鈕 任務(wù)完成 任務(wù)6步驟1 選定 成績備份表 步驟2 參照任務(wù)5 按專業(yè)對五門課程進行平均分的分類匯總 步驟3 選中 成績匯總表 數(shù)據(jù)區(qū)域中任何一個單元格 單擊 插入 菜單下的 圖表 命令 或者直接單擊常用工具欄上的 圖表向?qū)?按鈕 彈出 圖表向?qū)?4步驟之1 圖表類型 對話框 此處Excel提供了多種不同的圖表類型 我們選擇圖表類型為 柱形圖 子圖表類型為 簇狀柱形圖 并單擊 下一步 按鈕 步驟4 在彈出的 圖表向?qū)?4步驟之2 圖表源數(shù)據(jù) 對話框中 用戶可單擊此處的 數(shù)據(jù)區(qū)域 右側(cè)的 壓縮對話框 按鈕來選擇數(shù)據(jù)區(qū)域 本任務(wù)選擇C2 H2 C6 H6 C11 H11 C16 H16 C23 H23 C32 H32 C38 H38 按實際情況填寫 區(qū)域 用戶還可以選擇系列產(chǎn)生在行或是列 此處選擇 行 單擊 下一步 按鈕 任務(wù)完成 任務(wù)6 續(xù) 步驟5 在彈出的圖表向?qū)?4步驟之3 圖表選項 對話框中 選擇標(biāo)題選項卡 設(shè)置圖表標(biāo)題為 各專業(yè)課程平均分比較 再選擇 圖例 選項卡 選擇圖例位于右方 單擊 下一步 步驟6 在彈出的圖表向?qū)?4步驟之4 圖表位置 對話框中 可以確定圖表的位置 用戶可把圖表放在一張新的工作表中插入到工作簿文件中 稱為圖表工作表 也可讓圖表與原始數(shù)據(jù)放在一起 稱為嵌入式圖表 在本任務(wù)中選擇默認 嵌入式圖表 單擊 完成 按鈕 步驟7 改變步驟5中插入圖表的大小和位置 將其放置到J3 R20區(qū)域 任務(wù)完成 任務(wù)7步驟1 選定 成績匯總表 插入 專業(yè) 和 性別 列 步驟2 選中 成績匯總表 數(shù)據(jù)清單中任一單元格 單擊 數(shù)據(jù) 菜單 選擇 數(shù)據(jù)透視表和數(shù)據(jù)透視圖 命令 打開 數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)?3步驟之1 對話框 在該對話框中選中數(shù)據(jù)源類型為 MicrosoftOfficeExcel數(shù)據(jù)列表或數(shù)據(jù)庫 在所創(chuàng)建的報表類型中選中 數(shù)據(jù)透視表 單擊 下一步 按鈕 步驟3 在彈出的 數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)?3步驟之2 對話框中 選中要建立數(shù)據(jù)透視表的數(shù)據(jù)源區(qū)域 本任務(wù)中選擇A2 J32區(qū)域 然后單擊 下一步 按鈕 任務(wù)完成 任務(wù)7 續(xù) 步驟4 在彈出的 數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)?3步驟之3 對話框中 確定透視表的顯示位置 本任務(wù)中選擇在現(xiàn)有工作表中顯示數(shù)據(jù)透視表 并可設(shè)置布局 步驟5 若步驟3中單擊了 布局 按鈕 彈出 數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)?布局 對話框 在該對話框中 用戶可將右邊的字段名拖到左邊的圖表結(jié)構(gòu)圖上 構(gòu)造數(shù)據(jù)透視表 本任務(wù)是將 專業(yè) 字段拖到行上 性別 字段拖到列上 高等數(shù)學(xué) 字段拖到數(shù)據(jù)區(qū)域中 并雙擊數(shù)據(jù)區(qū)域字段選擇匯總方式為平均值 單擊 確定 按鈕回到 數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)?3步驟之3 對話框 最后單擊 完成 按鈕 案例描述 五 工作表的打印任務(wù)1 為 學(xué)生信息 表添加頁腳 中間位置內(nèi)容為總頁數(shù)和頁碼 靠右位置內(nèi)容為當(dāng)前的制作日期 任務(wù)2 設(shè)置 學(xué)生信息 表上 下頁邊距各3厘米 并水平居中 任務(wù)3 對以上設(shè)置進行打印預(yù)覽 滿意后打印輸出 預(yù)備知識 頁面設(shè)置頁面頁邊距頁眉 頁腳工作表打印區(qū)域打印輸出 任務(wù)完成 任務(wù)1步驟1 選定 學(xué)生信息 表 單擊 文件 菜單下的 頁面設(shè)置 對話框 單擊 頁眉 頁腳 選項卡 本任務(wù)中單擊 自定義頁腳 按鈕來自定義頁腳 步驟2 在彈出的 頁腳 對話框的中部位置輸入文本 總頁數(shù) 并單擊相應(yīng)圖標(biāo)按鈕 再輸入文本 當(dāng)前頁 并單擊相應(yīng)圖標(biāo)按鈕 在靠右位置輸入文本 制作日期 并單擊相應(yīng)圖標(biāo)按鈕 最后單擊 確定 按鈕回到 頁面設(shè)置 對話框并單擊 確定 按鈕 任務(wù)完成 任務(wù)2步驟1 選定 學(xué)生信息 表 單擊