Excel在學習中的應用.ppt

第六講：信息技術基礎能力系列3 Excel在學習中的應用,圖書館參考咨詢部 2008年10月7日,本講提綱,問題導入,在你的學習中，你使用Excel做過什么？ 調查問卷錄入分析、成績管理、考勤、通訊錄、統(tǒng)計圖表等。,問題列表,你使用過Excel提供的模板嗎？ 你能說出常用的統(tǒng)計函數(shù)嗎？ 你知道“自動篩選”和“高級篩選”的區(qū)別嗎？你會對數(shù)據(jù)進行“排序”與“篩選”操作么？ 你知道“相對引用”、“絕對引用”和“混合引用”的區(qū)別么？ 你知道“xy函數(shù)圖表”與“統(tǒng)計圖表”的主要區(qū)別么？你能生成相應的圖表么？ 你知道數(shù)據(jù)透視嗎？,基本知識,Excel能夠干些什么？ Excel是目前使用最廣泛的專業(yè)電子表格軟件，可以對數(shù)據(jù)進行處理、統(tǒng)計分析與計算，簡單的數(shù)據(jù)庫管理，能繪制圖表，并能與Internet網(wǎng)絡共享資源。,簡介,界面,工作區(qū),與其他Office軟件（例如Word、PowerPoint）的區(qū)別？ 工具區(qū)的特點：多了與表格制作編輯、修飾相關的工具欄，如“編輯”欄、“名稱”欄、行標與列標等 制表工作區(qū)的特點：只提供制作網(wǎng)格，表格的排版應通過預覽查看 視圖特點： “普通”視圖，用于制作表格 “分頁瀏覽”視圖，用于整體查看表格內容與頁面的關系,窗口環(huán)境的特點,窗口環(huán)境的特點,窗口界面的主要部分 工作簿工作表單元格 工作簿操作：新建、打開、保存 工作表操作：插入、刪除、重命名、移動或復制、選定全部工作表、工作表標簽顏色 單元格 功能：存儲器、計算器和顯示器 名稱：根據(jù)他們所在的行和列命名，例如A1，A2，B1，B2 個數(shù)：每層工作表Sheet含25665536個單元格 格式：右鍵，設置單元格格式。 坐標 行坐標：行標記（165536），查看（ Ctrl+ ， Ctrl+ ） 列坐標：列標記（AIZ），查看（ Ctrl+ ， Ctrl+ ）,3,案例制作與分析,案例制作與分析,案例目標：建立一張某一班學生檔案和考試成績的統(tǒng)計表，并對成績進行各種統(tǒng)計，進行名次的排定，繪制相應的圖表進行成績分析。,創(chuàng)建規(guī)范的數(shù)據(jù)表格,3.1.1設計表格 3.1.2輸入數(shù)據(jù) 3.1.3修飾表格 3.1.4保存并打印數(shù)據(jù),實例：學生檔案,設計表格設計表格流程,準確描述管理目標 確定管理項目名稱 建立表格結構,例如建立一個學生檔案，目標中應包含如下關鍵詞：學號、姓名、性別、出生日期、郵政編碼、家庭住址、聯(lián)系電話等內容。,簡明清晰 目標中應含有一些關鍵詞 用關鍵詞確定管理項目名稱,原則：待管理的項目應獨立成列,必須先建立表頭框架 表頭內容必須為文本值 表頭內容避免空項,設計表格表體結構組成,表體結構,制表人等信息,演示：設計表格結構,輸入數(shù)據(jù),3.1.2.1數(shù)據(jù)類型 數(shù)值型數(shù)據(jù) 包括數(shù)字和結果為數(shù)字的公式，其在單元格中默認的顯示位置為“右對齊”； 數(shù)值型數(shù)據(jù)可以有“常規(guī)”、“數(shù)值”、“貨幣”、“會計專用”、“科學記數(shù)”、“百分比”“分數(shù)”等多種顯示格式； 對于位數(shù)教多的身份證號，有兩種表示方法，其一是采用由字符串引導符“”引導的數(shù)字，其二是采用“文本”顯示格式的“數(shù)字”。 字符串型數(shù)據(jù) 包括漢字、英文字母、空格等，其在單元格中默認的顯示位置為“左對齊”。例如學號。 日期型數(shù)據(jù) 包括日期（時間）和結果為日期（時間）的公式，其在單元格中默認的顯示位置為“右對齊”； 輸入日期時需要用“-”號或“/”號隔開，如“2008-3-21”；輸入時間時，時、分、秒之間用冒號隔開。 邏輯型數(shù)據(jù) 包括TRUE和FALSE和結果為邏輯值的公式，其在單元格中默認的顯示位置為“居中”。,輸入數(shù)據(jù),3.1.2.2數(shù)據(jù)的準確控制 原始數(shù)據(jù)的準確性控制 數(shù)據(jù)管理過程中最基礎的數(shù)據(jù)，或者必須用手工輸入的數(shù)據(jù)，稱為“原始數(shù)據(jù)”。 控制方法： 調用法。打開相關文件，平鋪兩個窗口，用“=”號調用。 數(shù)據(jù)有效性。例如，用序列選擇；在輸入成績時超過100分時提示錯誤信息（示范+練習）。 加工數(shù)據(jù)的準確性控制 凡可以通過計算獲得的數(shù)據(jù)，則稱為“加工數(shù)據(jù)”。此類數(shù)據(jù)的監(jiān)控重點在于公式。 控制方法：公式。確定計算結果位置，再按下“=”號，用格地址建立公式。,輸入數(shù)據(jù),3.1.2.3輸入時常見的操作：自定義格式、自動填充、內容編輯 1.自定義格式 類型：常規(guī)、數(shù)值、貨幣、會計專用、日期、時間、文本等。 例如，學號為0710023011，輸入時就變成了710023011，應將格式設置為“文本”；或者由字符串引導符“”引導的數(shù)字。,輸入數(shù)據(jù),輸入時常見的操作：自定義格式、自動填充 2.自動填充 可自動填充的序列 時間：包括指定增量的日、星期和月，或諸如星期、月份和季度的重復序列。 等差：通過步長值決定數(shù)值的升序或降序 等比：數(shù)值乘以常數(shù)因子,輸入數(shù)據(jù),輸入時常見的操作：自定義格式、自動填充 2.自動填充 自動填充方式 鼠標拖曳方式：最簡單方便，可完成大部分自動填充的工作。 鼠標左鍵拖動方式：先鍵入預填充的前兩、三個辭條，并選中，當鼠標指針變成黑十字形時，按下鼠標左鍵拖曳。 鼠標右鍵拖動方式：先鍵入預填充的前兩、三個辭條，并選中，當鼠標指針變成黑十字形時，按下鼠標右鍵拖曳，松開右鍵時彈出快捷菜單（見右圖），進行選擇即可。 菜單方式：較復雜的自動填充一般采用菜單方式。 在起始單元格鍵入數(shù)據(jù)，并選中該單元格； 執(zhí)行“編輯填充序列”命令，在彈出的對話框中，再進行設置。,輸入數(shù)據(jù),輸入時常見的操作：自定義格式、自動填充 3.內容編輯 操作對象 常規(guī)編輯 特殊編輯,對象 如何選擇 選中狀態(tài),單元格 用 光標 單擊 單元格光標,列 用 光標 單擊 整列反白 行 用 光標 單擊 整行反白,工作表 用 光標 單擊 標簽反白,表格區(qū) 用 光標 拖拉 區(qū)域反白,表文件 用 光標 單擊 標題欄反白,移動 復制 清除,插入 行高/列寬 等高/等寬 隱藏/顯示,插入/刪除；移動/復制；命名,小技巧：輸入文本時需要換段落時怎么辦？,修飾表格,1.對齊方式 作用：保證數(shù)據(jù)的可視性。 類型：水平對齊、垂直對齊、文本控制、從右到左。 例如，表格標題使用“合并居中”；表頭文字加粗“居中”；文字居左、數(shù)字居右等。 2.格線和填充格式 作業(yè)：用表頭、數(shù)據(jù)和統(tǒng)計區(qū)分割方式顯示表格數(shù)據(jù)，增加可讀性。 個性化修飾：用顏色、線型等。 套用格式：可快速規(guī)范表格結構修飾。 3.保護表格 作用：控制表格數(shù)據(jù)使用過程 的安全性。 類型：鎖定和隱藏 鎖定：作用（避免手工輸入導致計算錯誤） 隱藏：作用（保護重要的算法） 方法：第一步，執(zhí)行“單元格格式”命令，在“保護”頁面中設置，第二步，執(zhí)行“工具保護保護工作表”。,通過新建/本機上的模板/Balance Sheet介紹如何修飾,保存并打印數(shù)據(jù),保存數(shù)據(jù) 保存（常規(guī)） 另存為 保存工作區(qū) 其他保存 打印數(shù)據(jù) 預覽表格、簡單調整版面位置、打印輸出,新文檔，命名保存（注意名稱規(guī)則） 舊文檔，覆蓋保存,定位保存 更名保存 改變文檔類型 加密保存,用于更換名稱的保存,制作模板選擇“模板”類型 傳遞障礙時存“文本”類型,用于安全性較強的保存 用“另存為”對話框的“工具”按鈕處理,舉例：創(chuàng)建學生檔案,建立步驟 打開Excel軟件； 重命名工作表名：將鼠標移到sheet1工作表標簽上，單擊鼠標右鍵，在彈出的快捷菜單上單擊“重命名”命令，輸入“學生檔案”； 輸入標題、制表日期 、制表人、上表頭、左表頭； 自定義格式：學號、家庭電話、手機號碼格式為“文本”。 輸入數(shù)據(jù)：“編號”用自動填充，“性別”用數(shù)據(jù)有效性，其他均需準確輸入； 數(shù)據(jù)表格美化：對齊方式（左對齊、居中）合并單元格、填充、邊框線。 保存。,練習,作業(yè)名稱：請使用Excel軟件設計一份調查問卷（問卷內容見Word文檔“大學生心理健康狀況調查問卷.doc”）。 設計要求 利用數(shù)據(jù)有效性，設計下拉式問卷選項。 只允許用戶在選擇題項處可操作，其他均受保護。 問卷形式美觀。,分析數(shù)據(jù)表格,3.2.1運用公式和函數(shù)處理數(shù)據(jù)分析 3.2.2運用圖表處理數(shù)據(jù)分析 3.2.3運用數(shù)據(jù)庫統(tǒng)計功能,運用公式和函數(shù)處理數(shù)據(jù)分析,公式 結構組成：由“=”引導的由數(shù)據(jù)、含有數(shù)據(jù)的單元格或區(qū)域坐標、運算符組成的表達式，Excel的單元格中保存用戶輸入的公式，通過即時計算，按用戶給定的顯示格式顯示其結果。例如，=x+1；=A1+B1等。 公式復制過程中的3種引用關系：相對引用、絕對引用、混合引用。 相對引用：公式拖拉復制過程引用原始單元格取值位置的相對關系。針對有規(guī)律的公式使用此方法。 例如，如果將單元格 B2 中的相對引用復制到單元格 B3，將自動從 =A1 調整到 =A2。 絕對引用：某算術式中存在多個算術項。但是在拖拉復制該公式時，其中某一算術項必須取值于固定的單元格位置。該位置也稱“絕對引用”點。絕對引用符“$”，例如例如 $F$6。 例如，例如，如果將單元格 B2 中的絕對引用復制到單元格 B3，則在兩個單元格中一樣，都是 $F$6。 混合引用：某算術式中存在多個算術項。但是在拖拉復制該公式時，絕對引用位置分別在不同的算術項中。針對公式取值固定位置設置$符號。,舉例：以“練習材料”作為素材,運用公式和函數(shù)處理數(shù)據(jù)分析,函數(shù) 類型,運用公式和函數(shù)處理數(shù)據(jù)分析,函數(shù) 常用函數(shù) 數(shù)學與三角函數(shù) 取整函數(shù)：INT() 余數(shù)函數(shù)：MOD(被除數(shù),除數(shù))：求余數(shù) 隨機數(shù)函數(shù)（無自變量參數(shù)）：ROUND() 圓周率函數(shù)（無自變量參數(shù)）：PI() 正弦、余弦、正切函數(shù)：SIN()，COS()，TAN() 統(tǒng)計函數(shù) 求和、平均值函數(shù)：SUM，AVERAGE 最大值、最小值函數(shù)：MAX，MIN 數(shù)值數(shù)據(jù)計數(shù)函數(shù)：COUNT 數(shù)值和字符串數(shù)據(jù)計數(shù)函數(shù)：COUNTA 條件計數(shù)函數(shù)：COUNTIF(區(qū)域坐標,條件)：求指定區(qū)域中滿足條件的數(shù)據(jù)個數(shù) 條件函數(shù) If函數(shù) ,舉例：對某班學生成績用函數(shù)進行分析,素材：學生檔案和成績.xls，“期末成績”工作表 分析目標及所用的函數(shù)： 每個學生的總成績（SUM） 某學科的最高分、最低分（Max、Min） 班級最高總分、最低總分（先求總分，再用Max、Min） 班級平均分（Average）,運用圖表處理數(shù)據(jù)分析,Excel提供的圖表從數(shù)軸的類別可以分成兩大類：XY函數(shù)圖和統(tǒng)計圖表。 XY函數(shù)圖：其X和Y軸都是數(shù)值型數(shù)據(jù)（其數(shù)據(jù)點位置隨其X和Y的坐標值大小變換）。 統(tǒng)計圖表：其X和Y軸只有一個是數(shù)值型數(shù)據(jù)（其數(shù)據(jù)點位置隨其坐標值大小變換），另一個是字符串型數(shù)據(jù)（其數(shù)據(jù)點位置是等間距的）。 圖表類型 柱形圖、條形圖、折線圖、餅圖、XY散點圖、面積圖、圓環(huán)圖、雷達圖等。 建立基本方法 其一是單擊常用工具欄中的“圖表向導”工具按鈕； 其二是執(zhí)行“插入圖表”命令，兩種方法都打開“圖表向導”對話框，跟隨向導操作即可。 編輯修飾圖表,舉例：對某班學生成績用圖表進行分析,素材：學生檔案和成績.xls， “成績分布”工作表 分析目標及所用的函數(shù)： 各科成績分布圖 統(tǒng)計各成績分數(shù)段人數(shù)（方法1：Countif函數(shù)；方法2：用Frequency函數(shù)） 繪制圓餅圖、柱狀圖 修飾圖表,舉例：對某班學生成績用圖表進行分析,素材：學生檔案和成績.xls，“成績分布”工作表 分析目標及所用的函數(shù)： 查看某三個學生之間三科成績的比較情況 查看某三個學生自身三科成績的比較情況,運用數(shù)據(jù)庫統(tǒng)計功能,3.2.3.1數(shù)據(jù)排序 3.2.3.2數(shù)據(jù)篩選 3.2.3.3數(shù)據(jù)分類匯總 3.2.3.4數(shù)據(jù)透視（本講略）,運用數(shù)據(jù)庫統(tǒng)計功能,3.2.3.1數(shù)據(jù)排序 作用：將數(shù)據(jù)組按某字段內容，設置遞增或遞減的重新排列方式 方法：選擇數(shù)據(jù)表（可以帶標題行，也可以不帶標題行）后，執(zhí)行“數(shù)據(jù)排序”命令，然后在“排序”對話框中根據(jù)需要進行選擇操作。 注意：在排序時要建立序列號，以便恢復原來序列。,運用數(shù)據(jù)庫統(tǒng)計功能,3.2.3.2數(shù)據(jù)篩選 作用：顯示數(shù)據(jù)組中滿足某條件的記錄，隱藏其他 類型：自動篩選和高級篩選 方法 自動篩選的基本方法：選擇數(shù)據(jù)表（必須帶標題行）后，執(zhí)行“數(shù)據(jù)篩選自動篩選”命令，然后單擊標題行各單元格右側的向下箭頭給出篩選條件的基本方法；要顯示所有數(shù)據(jù)，執(zhí)行“數(shù)據(jù)篩選全部顯示”命令。 高級篩選的基本方法：在已有數(shù)據(jù)表基礎上，建立條件區(qū)（第一行含需要的數(shù)據(jù)段名，以下一行或多行輸入條件表達式；然后執(zhí)行“數(shù)據(jù)篩選高級篩選”命令，然后在彈出的“高級篩選”對話框中設置數(shù)據(jù)區(qū)、條件區(qū)和輸出區(qū)坐標。,舉例：對某班學生成績用排序和篩選進行分析,素材：學生檔案和成績.xls 分析目標及所用的函數(shù)： 給學生排名次（rank函數(shù)） 查看語文成績超過85分的都有哪些同學（數(shù)據(jù)篩選自動篩選，自定義） 查看語文、數(shù)學兩門成績超過85分的同學（數(shù)據(jù)篩選高級篩選） 先設置篩選條件 再執(zhí)行高級篩選,運用數(shù)據(jù)庫統(tǒng)計功能,3.2.3.3數(shù)據(jù)分類匯總 類型：結構式分類匯總和篩選式分類匯總 結構式分類匯總 作用：使匯總數(shù)據(jù)可按宏觀或微觀形式查看 設置：先排序，后匯總；選擇匯總項及算法 恢復：在“分類匯總”對話框選擇“全部刪除”項 篩選式分類匯總 作用：使匯總數(shù)據(jù)只按篩選條件查看 設置：先篩選，后統(tǒng)計 恢復：從“篩選”命令中選擇“全部顯示”項,舉例：對高一年級學生成績進行分類匯總,