Excel高級應(yīng)用技術(shù)課件

1、Excel高級應(yīng)用第1頁，共25頁。2Excel能做什么？圖表創(chuàng)建條件格式數(shù)據(jù)有效性EXCEL1234圖表Factor 2計算公式函數(shù)宏篩選自動篩選高級篩選排序分析數(shù)據(jù)透視分類匯總第2頁，共25頁。一、圖表制作技巧2022/8/4體驗Excel電子表格表格創(chuàng)建的應(yīng)用技巧圖表的類型與分析目標用表格數(shù)據(jù)創(chuàng)建分析圖表圖表的修飾與編輯技巧關(guān)于圖表應(yīng)用的綜合練習(xí)第3頁，共25頁。案例：創(chuàng)建工資統(tǒng)計表輸入數(shù)據(jù)內(nèi)容填充法輸入月份復(fù)制粘貼導(dǎo)入數(shù)據(jù)用語音進行數(shù)字校對工資統(tǒng)計表的編輯查找與替換表格區(qū)域命名語言公式快速定位填充（自定義序列）選項行列的編輯與調(diào)整插入/刪除行或列調(diào)整行高和列寬工作表的控制工作表的命名工

2、作表的添加和刪除多工作表控制間隔表Ctrl連續(xù)表Shift統(tǒng)計工資發(fā)放情況按月份進行單向統(tǒng)計按季度進行雙向統(tǒng)計按年份進行三維統(tǒng)計4第4頁，共25頁。表格格式數(shù)字格式千元數(shù)字格式特殊貨幣符號的設(shè)置RMB#,#0.00文本型數(shù)字格式的設(shè)置電話號碼、會計科目文字格式單元格中個別文字格式修飾藝術(shù)字對齊方式跨列居中與合并居中自動換行與人工換行邊框和背景自動套用格式自定義樣式保護工作表單元格的保護工作表的保護利用定位功能保護工作表的公式隱藏公式允許用戶編輯保護區(qū)域條件格式條件格式的預(yù)警效果條件格式的應(yīng)用突出預(yù)警公式應(yīng)用多個工作表的批量修改5案例 修飾多部門的銷售統(tǒng)計表 第5頁，共25頁。圖表的類型與分析目

3、標直方圖突出顯示數(shù)據(jù)間差異的比較情況線形圖分析數(shù)據(jù)間趨勢的變化餅形圖描述數(shù)據(jù)間比例分配關(guān)系的差異雷達圖顯示多組有內(nèi)部關(guān)聯(lián)的數(shù)據(jù)對比 組合圖表可進行多組數(shù)據(jù)關(guān)系的綜合分析2022/8/46第6頁，共25頁。用表格數(shù)據(jù)創(chuàng)建圖表的步驟確定圖表的分析目標明確表格數(shù)據(jù)的取值范圍使用圖表向?qū)Т_定圖表類型對圖表進行編輯和修飾2022/8/47案例：用圖表展示銷售情況數(shù)據(jù)添加刪除的技巧： 通過菜單選項添加 直接用數(shù)據(jù)拖拉數(shù)據(jù) 刪除圖表中的數(shù)據(jù)圖表的修飾技巧 修改邊框 修改數(shù)據(jù)點或圖塊 修飾文本或數(shù)字 改變坐標軸（顯示方向、刻度） 添加標記（數(shù)據(jù)點、數(shù)據(jù)表、備注） 圖表的組合編輯技巧 間隔數(shù)據(jù)的圖表制作技巧不同

4、類型的直方圖表現(xiàn)不同的管理目標(柱形圖、條形圖) 第7頁，共25頁。圖表的修飾與編輯技巧改變圖表框的大小移動圖表框、圖表對象修飾圖表中的對象圖表框、標題框框線、軸線、刻度線、圖形線系列圖塊、獨立圖塊為圖表添加標記數(shù)值型標記表格式標記備注式標記2022/8/48第8頁，共25頁。關(guān)于圖表應(yīng)用的綜合練習(xí)設(shè)置第2Y軸用于將兩組相差懸殊的數(shù)據(jù)組合在同張表中利用兩個刻度分離兩組數(shù)據(jù)使相差懸殊的數(shù)據(jù)更易于閱讀綜合練習(xí)進度表2022/8/49第9頁，共25頁。EXCEL填寫報表的15種不良習(xí)慣：報表題目、表頭不完整或語意不確切（多在時間、主體和報表內(nèi)容等方面）；報表布局、數(shù)據(jù)項目順序不合理；鉤稽關(guān)系不明確甚

5、至錯誤；數(shù)據(jù)缺失、漏填漏報；忘記注明金額單位和制表人等項目；部門名稱或員工姓名出現(xiàn)錯字、別字；部門名稱或員工姓名等項目用字每次不能保持一致；為了美化格式或由于輸入手誤，字串中間、前后使用空格；為求得格式好看，大量使用“合并居中”功能；不論是文字數(shù)據(jù)還是數(shù)字數(shù)據(jù)一概“居中對齊”；文字和數(shù)字的字體、大小和格式不合適、不統(tǒng)一；行列的寬、高不適合，或是過寬或是過窄以至數(shù)字不能正確顯示；字體或行列使用多種顏色，且搭配失當、雜亂；大量使用框線致使版面呆板；時間項目不按照EXCEL系統(tǒng)的標準格式填寫，且未能保持統(tǒng)一；標點符號全角、半角混用（如左右括號的輸入）；公式計算結(jié)果如為錯誤值符號，一概保留，不加以清除

6、。2022/8/410第10頁，共25頁。練習(xí)在“各部銷售統(tǒng)計”的“一月”、“二月”、“三月”三張工作表中，將其中的數(shù)字型單元格區(qū)域的“B4:D6”設(shè)置為“貨幣”格式，并添加”RMB”保護工作表并隱藏公式設(shè)置條件預(yù)警信息11第11頁，共25頁。二、函數(shù)應(yīng)用、高級算法及宏處理2022/8/4公式的創(chuàng)建與引用關(guān)系函數(shù)的分類幾種常用函數(shù)介紹函數(shù)功能的自學(xué)方法宏第12頁，共25頁。公式的創(chuàng)建與引用關(guān)系創(chuàng)建公式的方法：“=”號引用關(guān)系：“$”絕對引用 相對引用混合引用2022/8/413第13頁，共25頁。Excel函數(shù)簡介Excel函數(shù)其實是預(yù)定義的公式，它們使用稱為參數(shù)的特定數(shù)值按特定的順序或結(jié)構(gòu)進

7、行計算。嵌套函數(shù)就是指在某些情況下，您可能需要將某函數(shù)作為另一函數(shù)的參數(shù)使用。函數(shù)的結(jié)構(gòu)以函數(shù)名稱開始，后面是左圓括號、以逗號分隔的參數(shù)和右圓括號。公式選項板幫助創(chuàng)建或編輯公式的工具，還可提供有關(guān)函數(shù)及其參數(shù)的信息。單擊編輯欄中的編輯公式按鈕，或是單擊常用工具欄中的粘貼函數(shù) 按鈕之后，就會在編輯欄下面出現(xiàn)公式選項板。2022/8/414第14頁，共25頁。Excel函數(shù)分類1數(shù)據(jù)庫函數(shù)當需要分析數(shù)據(jù)清單中的數(shù)值是否符合特定條件時，可以使用數(shù)據(jù)庫工作表函數(shù)。2日期與時間函數(shù)通過日期與時間函數(shù)，可在公式中分析和處理日期值和時間值。3工程函數(shù)工程工作表函數(shù)用于工程分析。4財務(wù)函數(shù)財務(wù)函數(shù)可以進行一般

8、的財務(wù)計算，如確定貸款的支付額、投資的未來值或凈現(xiàn)值等5信息函數(shù)可以使用信息工作表函數(shù)確定存儲在單元格中的數(shù)據(jù)的類型。6邏輯函數(shù)使用邏輯函數(shù)可以進行真假值判斷，或者進行復(fù)合檢驗。7查詢和引用函數(shù)當需要在數(shù)據(jù)清單或表格中查找特定數(shù)值，或者需要查找某一單元格的引用時，可以使用查詢和引用工作表函數(shù)。8數(shù)學(xué)和三角函數(shù)通過數(shù)學(xué)和三角函數(shù)，可以處理簡單的計算，例如對數(shù)字取整、計算單元格區(qū)域中的數(shù)值總和或復(fù)雜計算。9統(tǒng)計函數(shù)統(tǒng)計工作表函數(shù)用于對數(shù)據(jù)區(qū)域進行統(tǒng)計分析。10文本函數(shù)通過文本函數(shù)，可以在公式中處理文字串。11用戶自定義函數(shù)如果要在公式或計算中使用特別復(fù)雜的計算，而工作表函數(shù)又無法滿足需要，則需要創(chuàng)

9、建用戶自定義函數(shù)。2022/8/415第15頁，共25頁。統(tǒng)計函數(shù)求平均值A(chǔ)verage Trimmean Averagea求區(qū)域中數(shù)據(jù)的頻率分布FREQUENCY求數(shù)據(jù)集的滿足不同要求的數(shù)值的函數(shù)求數(shù)據(jù)集的最大值MAX與最小值MIN求數(shù)據(jù)集中第K個最大值LARGE與第k個最小值SMALL求數(shù)據(jù)集中的中位數(shù)MEDIAN求數(shù)據(jù)集中出現(xiàn)頻率最多的數(shù)MODE用來排位的函數(shù)RANK統(tǒng)計學(xué)常用函數(shù) 方差VAR等16第16頁，共25頁。查詢與引用函數(shù)引用的作用在Excel中引用的作用在于標識工作表上的單元格或單元格區(qū)域，并指明公式中所使用的數(shù)據(jù)的位置。通過引用，可以在公式中使用工作表不同部分的數(shù)據(jù)，或者在

10、多個公式中使用同一單元格的數(shù)值。還可以引用同一工作簿不同工作表的單元格、不同工作簿的單元格、甚至其它應(yīng)用程序中的數(shù)據(jù)。Vlookup函數(shù)可以根據(jù)搜索區(qū)域內(nèi)最左列的值，去查找區(qū)域內(nèi)其它列的數(shù)據(jù)，并返回該列的數(shù)據(jù)，對于字母來說，搜索時不分大小寫。所以，函數(shù)VLOOKUP的查找可以達到兩種目的：一是精確的查找。二是近似的查找。2022/8/417第17頁，共25頁。Vlookup2022/8/418第18頁，共25頁。文本/日期/時間函數(shù)大小寫轉(zhuǎn)換函數(shù)LOWER UPPER PROPER取出字符串中的部分字符LEFT MID RIGHT去除字符串的空白 TRIM字符串的比較 EXACT合并文本函數(shù)

11、CONCATENATE將數(shù)值轉(zhuǎn)換為按指定數(shù)字格式表示的文本 TEXT取出當前系統(tǒng)時間/日期信息 NOW TODAY取得日期/時間的部分字段值 HOUR DAY MONTH YEAR2022/8/419第19頁，共25頁。邏輯函數(shù)來判斷真假值，或者進行復(fù)合檢驗的Excel函數(shù)，我們稱為邏輯函數(shù)。AND、OR、NOT、FALSE、IF、TRUEIF函數(shù)用于執(zhí)行真假值判斷后，根據(jù)邏輯測試的真假值返回不同的結(jié)果。2022/8/420示例：制作美觀簡潔的人事分析表第20頁，共25頁。示例的函數(shù)解釋出生日期CONCATENATE(19,MID(E17,7,2),/,MID(E17,9,2),/,MID(E

12、17,11,2)司齡（年資）CONCATENATE(DATEDIF(H17,TODAY(),y),年,DATEDIF(H17,TODAY(),ym),個月)司齡（年資）TEXT(DATEDIF(H17,TODAY(),y),00)&年&TEXT(DATEDIF(H17,TODAY(),ym),00)&個月性別IF(VALUE(RIGHT(E17,3)/2=INT(VALUE(RIGHT(E17,3)/2),女,男)21第21頁，共25頁。數(shù)學(xué)函數(shù)與求和有關(guān)的函數(shù)SumSumifSumproduct四舍五入函數(shù)Round數(shù)字相乘，并返回乘積值 Product計算給定區(qū)域內(nèi)滿足特定條件的單元格的數(shù)目 Countif2022/8/4