Excel篇第二節(jié)內(nèi)容.ppt

課程名稱：辦公軟件應(yīng)用,主講教師：楊立影 所在單位：信息技術(shù)系 Email：,本課程主要內(nèi)容,3/44,1.2 Excel數(shù)據(jù)處理,1.2.3 數(shù)據(jù)分類匯總 在處理含有大量數(shù)據(jù)的工作表時(shí)，往往需要對(duì)特定的數(shù)據(jù)進(jìn)行匯總計(jì)算。Excel的分類匯總功能可以用來(lái)協(xié)助進(jìn)行某列數(shù)據(jù)的求和、平均值、最大值、最小值等運(yùn)算。,4/44,1.2 Excel數(shù)據(jù)處理,1.2.3 數(shù)據(jù)分類匯總 進(jìn)行分類匯總前，必須保證數(shù)據(jù)表的各列有列標(biāo)題，并且同一列中應(yīng)該包含相似的數(shù)據(jù)，同時(shí)在數(shù)據(jù)區(qū)域中沒(méi)有空行或者空列。 要進(jìn)行分類匯總的數(shù)據(jù)列已經(jīng)排好序，這個(gè)排序的列標(biāo)題稱為分類匯總關(guān)鍵字。,5/44,1.2 Excel數(shù)據(jù)處理,1.2.3 數(shù)據(jù)分類匯總 例題：在某公司的員工基本信息表中，為了方便統(tǒng)計(jì)各個(gè)部門的員工基本收入情況，現(xiàn)需要對(duì)該表進(jìn)行分類匯總。具體要求如下：,6/44,1.2 Excel數(shù)據(jù)處理,1.2.3 數(shù)據(jù)分類匯總 具體要求如下： 統(tǒng)計(jì)各個(gè)部門員工的平均年齡 統(tǒng)計(jì)各個(gè)部門員工的平均工資 統(tǒng)計(jì)各個(gè)部門男、女員工獎(jiǎng)金的最大值 將分類匯總結(jié)果分頁(yè)打印出來(lái),7/44,1.2 Excel數(shù)據(jù)處理,1.2.3 數(shù)據(jù)分類匯總 解析： 從題目的要求來(lái)看，1和2屬于簡(jiǎn)單的分類匯總，可以放在一起進(jìn)行操作；要求3屬于嵌套分類匯總，有兩個(gè)層次，第一層是“部門”，第二層是“性別”；要求4屬于分類匯總的顯示。,8/44,1.2 Excel數(shù)據(jù)處理,1.2.3 數(shù)據(jù)分類匯總 提示： 在進(jìn)行分類匯總的時(shí)候，分類字段必須是已經(jīng)排好序的字段，否則最后的結(jié)果是不正確的。 匯總方式必須與匯總項(xiàng)匹配，如對(duì)年齡和工資可以求平均值、最大值等，而對(duì)部門則不能。,9/44,1.2 Excel數(shù)據(jù)處理,1.2.4 條件格式 例題：在例1.2.4表中記錄了某一家庭一年的經(jīng)濟(jì)支出狀況，為了更清楚的顯示該家庭本年度的支出情況，請(qǐng)對(duì)超過(guò)3000元的支出標(biāo)注紅色背景，10003000元之間的標(biāo)準(zhǔn)藍(lán)色背景，低于1000元的支出標(biāo)注綠色背景。,10/44,1. Excel安全設(shè)置與數(shù)據(jù)保護(hù),對(duì)工作表的保護(hù) 對(duì)工作簿的保護(hù) 對(duì)單元格的保護(hù),11/44,1. Excel安全設(shè)置與數(shù)據(jù)保護(hù),對(duì)工作表的保護(hù) 方法如下： 選擇“工具”“保護(hù)”“保護(hù)工作表”命令； 輸入密碼并確認(rèn)，確定即可。,12/44,1. Excel安全設(shè)置與數(shù)據(jù)保護(hù),對(duì)工作簿的保護(hù) 方法一：通過(guò)“工具”“保護(hù)”“保護(hù)工作簿”命令； 方法二：通過(guò)“工具”“選項(xiàng)”“安全性”選項(xiàng)卡設(shè)置。,13/44,1. Excel安全設(shè)置與數(shù)據(jù)保護(hù),對(duì)單元格的保護(hù) 例題：對(duì)電信公司用戶基本信息表進(jìn)行保護(hù)，具體要求如下： 整個(gè)工作表中，只有1、2、3月話費(fèi)部分可以修改，其他部分不允許修改； 分別對(duì)工作表和工作簿進(jìn)行保護(hù),14/44,1. Excel安全設(shè)置與數(shù)據(jù)保護(hù),對(duì)單元格的保護(hù) 提示： 單元格的保護(hù)設(shè)置有“鎖定”和“隱藏”兩個(gè)選項(xiàng)； 對(duì)單元格的保護(hù)必須要在整個(gè)工作表被保護(hù)的前提下才能完全實(shí)現(xiàn)其功能。,15/44,1. Excel安全設(shè)置與數(shù)據(jù)保護(hù),對(duì)單元格的保護(hù) 操作步驟： 1)在單元格格式對(duì)話框中取消“鎖定”復(fù)選框； 2)單擊“工具”“保護(hù)”“允許用戶編輯區(qū)域”，新建一個(gè)區(qū)域，應(yīng)用并保護(hù)工作表。,16/44,1.4 Excel發(fā)展特征,隨著人們對(duì)日常數(shù)據(jù)處理工作提出了更高的要求,Excel也呈現(xiàn)出新的發(fā)展特征。XML作為一種常見(jiàn)的數(shù)據(jù)存儲(chǔ)格式，可以實(shí)現(xiàn)各種不同格式數(shù)據(jù)之間的平滑連接。本節(jié)主要介紹XML數(shù)據(jù)結(jié)構(gòu)在Excel中的應(yīng)用。,17/44,1.4 Excel發(fā)展特征,1.4.1 XML數(shù)據(jù)結(jié)構(gòu) XML是Extensible Markup Language的縮寫，以為可擴(kuò)展標(biāo)記語(yǔ)言。XML是由W3C設(shè)計(jì)的用來(lái)自動(dòng)描述數(shù)據(jù)信息的一種新的標(biāo)準(zhǔn)語(yǔ)言。,18/44,1.4 Excel發(fā)展特征,1.4.1 XML數(shù)據(jù)結(jié)構(gòu) 與我們所熟悉的HTML相比，XML沒(méi)有固定的標(biāo)記，也不能描述網(wǎng)頁(yè)具體的外觀，它只是描述內(nèi)容的數(shù)據(jù)形式和結(jié)構(gòu)。,19/44,1.4 Excel發(fā)展特征,1.4.1 XML數(shù)據(jù)結(jié)構(gòu) XML數(shù)據(jù)主要由5個(gè)部分組成： XML文件頭說(shuō)明部分 文檔類型定義部分 XML文件中的元素 XML文件中的屬性 元素和屬性的取值,20/44,1.4 Excel發(fā)展特征,1.4.1 XML數(shù)據(jù)結(jié)構(gòu) XML主要有以下的特點(diǎn)： 半結(jié)構(gòu)化 自描述性 可擴(kuò)展性 靈活性,21/44,1.4 Excel發(fā)展特征,1.4.2 XML在Excel中的運(yùn)用 XML作為一種常見(jiàn)的數(shù)據(jù)存儲(chǔ)格式，可以實(shí)現(xiàn)各種不同格式數(shù)據(jù)之間的平滑的連接。在Excel中可以通過(guò)XML實(shí)現(xiàn)不同的Excel文件之間的連接，以及Excel與其他格式數(shù)據(jù)文件之間的連接。,22/44,1.4 Excel發(fā)展特征,1.4.2 XML在Excel中的運(yùn)用 例題：夜鶯軟件公司將其員工信息保存在Access數(shù)據(jù)庫(kù)中，現(xiàn)在要求每個(gè)月打印員工工資表，請(qǐng)從數(shù)據(jù)庫(kù)中自動(dòng)調(diào)出員工基本信息表，保存為Excel格式，并且數(shù)據(jù)庫(kù)中的信息修改能夠自動(dòng)反映在Excel數(shù)據(jù)表中。,23/44,1.4 Excel發(fā)展特征,1.4.2 XML在Excel中的運(yùn)用 操作步驟如下： 1)將Access數(shù)據(jù)庫(kù)文件導(dǎo)出為XML文件 選擇“文件”“導(dǎo)出”命令； 2)將XML文件導(dǎo)入到Excel中 選擇“數(shù)據(jù)”“XML”“導(dǎo)入”命令；,24/44,2.1 函數(shù)基礎(chǔ)知識(shí) 2.2 函數(shù)的應(yīng)用,第節(jié) Excel高級(jí)函數(shù)運(yùn)用,25/44,2.1 函數(shù)基礎(chǔ)知識(shí),Excel中函數(shù)分為內(nèi)置函數(shù)和擴(kuò)展函數(shù)兩大類。只要啟動(dòng)了Excel，用戶就可以使用內(nèi)置函數(shù)。而對(duì)于擴(kuò)展函數(shù)，需要通過(guò)“工具”“加載宏”菜單命令加載，然后才能使用。,26/44,2.1 函數(shù)基礎(chǔ)知識(shí),函數(shù)的結(jié)構(gòu)： 以函數(shù)名開(kāi)始，后面是左圓括號(hào)、以逗號(hào)分隔的參數(shù)和右圓括號(hào)。如下圖所示：,27/44,2.1 函數(shù)基礎(chǔ)知識(shí),嵌套函數(shù)： 將某一函數(shù)作為另一函數(shù)的參數(shù)使用時(shí)，稱為嵌套函數(shù)。如下所示：,=IF(SUM(E3:G15)500,MAX(E3:G15),0),28/44,2.1 函數(shù)基礎(chǔ)知識(shí),嵌套函數(shù)： 對(duì)于嵌套函數(shù)的使用，必須注意以下兩個(gè)問(wèn)題： 當(dāng)嵌套的函數(shù)作為參數(shù)使用時(shí)，返回的數(shù)值類型必須與參數(shù)的類型一致。 嵌套函數(shù)級(jí)數(shù)的限制。,29/44,2.1 函數(shù)基礎(chǔ)知識(shí),函數(shù)的種類： Excel函數(shù)一共有11類，分別為： 數(shù)據(jù)庫(kù)函數(shù)、日期與時(shí)間函數(shù) 工程函數(shù)、財(cái)務(wù)函數(shù) 信息函數(shù)、邏輯函數(shù) 查詢和引用函數(shù)、統(tǒng)計(jì)函數(shù) 數(shù)學(xué)和三角函數(shù)、文本函數(shù) 自定義函數(shù),30/44,2.2 函數(shù)的應(yīng)用,日期與時(shí)間函數(shù) 1.DATE 用途：返回代表特定日期的序列號(hào) 用法：DATE(year,month,day) 例子：公式“=DATE(2006,12,30)” 返回2006-12-30.,31/44,2.2 函數(shù)的應(yīng)用,日期與時(shí)間函數(shù) 2.DATEVALUE 用途：返回date_text所表示的日期的 序列號(hào)。 用法：DATEVALUE(date_text) 例子：公式“=DATEVALUE(“2006/12/30”)” 返回39081.,32/44,2.2 函數(shù)的應(yīng)用,日期與時(shí)間函數(shù) 3.NOW 用途：返回當(dāng)前日期和時(shí)間。 語(yǔ)法：NOW() 4.TODAY 用途：返回系統(tǒng)當(dāng)前日期 語(yǔ)法：TODAY(),33/44,2.2 函數(shù)的應(yīng)用,文本函數(shù)的應(yīng)用 所謂文本函數(shù)，就是可以在公式中處理文字串的函數(shù)。例如，可以改變大小寫或確定文字串的長(zhǎng)度，可以替換某些字符或去掉某些字符等。,34/44,2.2 函數(shù)的應(yīng)用,文本函數(shù)的應(yīng)用 1.LOWER 用途:將一個(gè)文字串中的所有大寫字母 轉(zhuǎn)換為小寫字母。 用法：LOWER(text) 例子：若A1=“Excel”，則使用下面的公式“=lower(A1)”后返回“excel”.,35/44,2.2 函數(shù)的應(yīng)用,文本函數(shù)的應(yīng)用 2.UPPER 用途:將文本轉(zhuǎn)換為大寫形式。 用法：UPPER(text) 例子：若A1=“Excel”，則使用公式“=upper(A1)”后返回“EXCEL”.,36/44,2.2 函數(shù)的應(yīng)用,文本函數(shù)的應(yīng)用 3.LEN或LENB 用途:LEN返回文本串的字符數(shù)。 LENB返回文本串中所有字符的字節(jié)數(shù)。 用法：LEN(text)或LENB(text) 例子：若A1=“統(tǒng)計(jì)分析人員”，則使用公式“=len(A1)”后返回6，使用公式“=lenb(A1)”后返回12。,37/44,2.2 函數(shù)的應(yīng)用,文本函數(shù)的應(yīng)用 4.SUBSTITUTE 用途:在文字串中用new_text代替old_text。 用法： SUBSTITUTE(new_text,old_text,num),38/44,2.2 函數(shù)的應(yīng)用,文本函數(shù)的應(yīng)用 5.CONCATENATE 用途:將若干文字串合并到一個(gè)文字串中，其功能與“&”運(yùn)算符相同。 用法：CONCATENATE(text1, text2,.) 例如：A1=“excel”，A2=“和統(tǒng)計(jì)”，則 公式“=concatenate(A1,A2)”返回“excel和統(tǒng)計(jì)”。,39/44,2.2 函數(shù)的應(yīng)用,查詢引用函數(shù)的應(yīng)用 查詢引用函數(shù)是Excel中另一類常會(huì)用到的函數(shù)，尤其是在制作動(dòng)態(tài)的Excel圖表過(guò)程中，這類函數(shù)非常好用。在介紹查詢引用函數(shù)前，先來(lái)了解一下引用的知識(shí)。,40/44,2.2 函數(shù)的應(yīng)用,查詢引用函數(shù)的應(yīng)用 引用的作用 在Excel中引用的作用在于標(biāo)識(shí)工作表上的單元格區(qū)域，并指明公式中所使用的數(shù)據(jù)的位置。,41/44,2.2 函數(shù)的應(yīng)用,查詢引用函數(shù)的應(yīng)用 引用的含義 外部引用：不同工作簿中的單元格的引用稱為外部引用。 遠(yuǎn)程引用：引用其他程序中的數(shù)據(jù)稱為遠(yuǎn)程引用。,42/44,2.2 函數(shù)的應(yīng)用,查詢引用函數(shù)的應(yīng)用 引用的含義 相對(duì)引用：在創(chuàng)建公式時(shí)，單元格或單元格區(qū)域的引用通常是相對(duì)于包含公式的單元格的相對(duì)位置。 絕對(duì)引用：如果在復(fù)制公式時(shí)不希望Excel調(diào)整引用，則使用絕對(duì)引用，即加入美元符號(hào)，如$C$1。,43/44,2.2 函數(shù)的應(yīng)用,查詢引用函數(shù)的應(yīng)用 引用的表示方法 A1和R1C1兩種引用樣式 A1(默認(rèn))：引用字母標(biāo)志列和數(shù)字標(biāo)志行。對(duì)單元格的引用如“C24”，對(duì)區(qū)域的引用如“C3:F9”。,44/44,2.2 函數(shù)的應(yīng)用,查詢引用函數(shù)的應(yīng)用 引用的表示方法 R1C1引用樣式：使用R+行數(shù)字和C+列數(shù)字來(lái)指示單元格的位置。 例如，單元格絕對(duì)引用R1C1與A1樣式中的絕對(duì)引用$A$1等價(jià)。如果活動(dòng)單元格是A1，則單元格相對(duì)引用R1C1將引用下面一行和右面一列的單元格,45/44,2.2 函數(shù)的應(yīng)用,查詢引用函數(shù)介紹 1.ADDRESS函數(shù) 用途：用來(lái)按照給定的行號(hào)和列號(hào)建立一個(gè)文本類型的單元格地址。 語(yǔ)法：address(row_num,column_num,abs_num,a1,sheet_text),46/44,2.2 函數(shù)的應(yīng)用,查詢引用函數(shù)介紹 1.ADDRESS函數(shù) 參數(shù)：row_num表示在單元格中引用的行號(hào),column_num表示引用的列號(hào),abs_num指明返回的引用類型：1代表絕對(duì)引用，2代表絕對(duì)行號(hào)相對(duì)列號(hào)，3代表相對(duì)行號(hào)絕對(duì)列號(hào)，4為相對(duì)引用。A1為true，返回A1樣式的引用，a1為false返回R1C1樣式的引用。sheet_text為文本，指明作為外部引用的工作表的名稱。,47/44,2.2 函數(shù)的應(yīng)用,查詢引用函數(shù)介紹 1.ADDRESS函數(shù) 例子,48/44,2.2 函數(shù)的應(yīng)用,查詢引用函數(shù)介紹 2.AREAS函數(shù) 用途：用于返回引用中包含區(qū)域的個(gè)數(shù)，其中區(qū)域表示連續(xù)的單元格組或者某個(gè)單元格。 語(yǔ)法：areas(reference) 參數(shù)：reference表示對(duì)某個(gè)單元格或者單元格區(qū)域的引用。,49/44,2.2 函數(shù)的應(yīng)用,查詢引用函數(shù)介紹 2.AREAS函數(shù) 例子,50/44,2.2 函數(shù)的應(yīng)用,查詢引用函數(shù)介紹 3.CHOOSE函數(shù) 用途：被用于從給定的參數(shù)中返回指定的值。 語(yǔ)法：choose(index_num,value1,value2,) 參數(shù)：reference表示對(duì)某個(gè)單元格或者單元格區(qū)域的引用。,51/4