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