Excel在學(xué)習(xí)中的應(yīng)用.ppt

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