Excel菜鳥職場辦公效率提升秘籍-文檔資料

1、1Excel菜鳥職場辦公菜鳥職場辦公效率提升秘籍效率提升秘籍主講老師：彭祥峰YY課堂：15799318網(wǎng) 址：2如何提高日常數(shù)據(jù)處理效率？如何提高日常數(shù)據(jù)處理效率？樹立一種正確高效使用Excel的基本理念和思路掌握實用的Excel技能和技巧靈活使用常用的函數(shù)了解和掌握一些實用的工具熟練使用數(shù)據(jù)透視表3課課 程程 目目 錄錄前言：前言： 第一部分第一部分 高效數(shù)據(jù)處理高效數(shù)據(jù)處理（讓你的表格標(biāo)準(zhǔn)化、規(guī)（讓你的表格標(biāo)準(zhǔn)化、規(guī)范化，為匯總分析打基礎(chǔ)）范化，為匯總分析打基礎(chǔ)）第二部分第二部分 日常數(shù)據(jù)的計算、提取、匯總、邏輯判日常數(shù)據(jù)的計算、提取、匯總、邏輯判斷及查詢應(yīng)用斷及查詢應(yīng)用第三部分第三部分

2、高效數(shù)據(jù)分析工具及應(yīng)用高效數(shù)據(jù)分析工具及應(yīng)用4前言前言（Excel的使用習(xí)慣） 你設(shè)計表格是否很隨意？想怎么弄就怎么弄？ 你是不是輸入數(shù)據(jù)也很隨意，而不考慮Excel對數(shù)據(jù)格式的要求？ 你是不是特別喜歡設(shè)計多行表頭，還有大量合并單元格？ 你是不是特別喜歡在表格里插入很多合計行和空行？ 你是不是特別喜歡把所有的數(shù)據(jù)（包括計算結(jié)果）都保存在一個工作表，結(jié)果表格有非常多的列？ 你是不是只會簡單的加減乘除，而不會使用函數(shù)進行匯總計算？ 你是不是只會使用簡單的直接單元格鏈接，而不會使用函數(shù)引用數(shù)據(jù)？ 你是不是不注意保護工作表的計算公式？ 你是不是總在使用笨辦法來處理數(shù)據(jù)？5前言前言（Excel的標(biāo)準(zhǔn)化和

3、規(guī)范化）樹立一種正確使用Excel的理念科學(xué)規(guī)范的表格結(jié)構(gòu)科學(xué)規(guī)范的數(shù)據(jù)結(jié)構(gòu)正確區(qū)分和處理兩類Excel表格基礎(chǔ)表報告表正確處理三種Excel數(shù)據(jù) 文本（純文本和數(shù)字型文本） 日期和時間 數(shù)字（純數(shù)字和文本型數(shù)字）6第一部分第一部分 高效數(shù)據(jù)處理高效數(shù)據(jù)處理技能1：快速處理非法日期技能2：非法數(shù)字與純數(shù)字之間的相互轉(zhuǎn)換技能3：快速填充數(shù)據(jù)（定位填充法的應(yīng)用）技能4：刪除數(shù)據(jù)中眼睛看不見的特殊字符技能5：自定義排序與高級篩選的靈活運用技能6：數(shù)據(jù)的快速分列技能7：設(shè)計標(biāo)準(zhǔn)的表格模板，數(shù)據(jù)有效性應(yīng)用技能8：保護工作表指定區(qū)域，以免別人破壞或修改7技能技能1：快速處理非法日期：快速處理非法日期很多人

4、對日期數(shù)據(jù)的輸入是很隨意的，有時侯輸入的“日期”并不是真正的日期。例如：“20140817”、“2014.08.17”注：日期在Excel中是作為數(shù)值儲存在單元格中的，永遠(yuǎn)是正整數(shù)。正確的日期格式：年月日3個數(shù)字之間用減號（-）或者斜杠（/）隔開的。案例01：非法日期的快速轉(zhuǎn)換8技能技能2：非法數(shù)字與純數(shù)字之間的相互轉(zhuǎn)換：非法數(shù)字與純數(shù)字之間的相互轉(zhuǎn)換F在單元格輸入文本型數(shù)字的方法有兩種：在單元格輸入文本型數(shù)字的方法有兩種：方法1：應(yīng)先輸入單引號“ ”，再輸入數(shù)字；方法2：先將單元格設(shè)置為文本，然后正常輸入數(shù)字。F文本型數(shù)字轉(zhuǎn)換為純數(shù)字文本型數(shù)字轉(zhuǎn)換為純數(shù)字利用智能標(biāo)記使用分列工具選擇性粘貼F

5、純數(shù)字轉(zhuǎn)換為文本型數(shù)字純數(shù)字轉(zhuǎn)換為文本型數(shù)字使用Text() 函數(shù)使用分列工具案例02：非法數(shù)字與純數(shù)字的相互轉(zhuǎn)換9技能技能3：快速填充數(shù)據(jù)：快速填充數(shù)據(jù)數(shù)據(jù)區(qū)域內(nèi)如果有空白單元格，在使用數(shù)據(jù)透視表做數(shù)據(jù)分析時就會造成一些數(shù)據(jù)的分析困難，甚至?xí)绊懙綌?shù)據(jù)分析的準(zhǔn)確性。定位填充法步驟：定位填充法步驟：1、選擇整個數(shù)據(jù)區(qū)域2、使用F5健，把數(shù)據(jù)區(qū)域內(nèi)的所有空單元格都選中3、在活動單元格中輸入公式，“=活動單元格上一個單元格的地址”4、按 Ctrl+Enter 組合鍵 案例03：快速填充數(shù)據(jù)10技能技能4：刪除數(shù)據(jù)中：刪除數(shù)據(jù)中看不見的看不見的特殊字符特殊字符從數(shù)據(jù)庫導(dǎo)入的數(shù)據(jù)往往會含有一些眼睛看不

6、見的特殊字符，為了能正確的處理分析，必須把這些特殊字符刪除。步驟：1）復(fù)制單元格中看不見的字符2）打開查找和替換對話框，將特殊字符粘貼進“查找內(nèi)容”輸入框中，“替換為”輸入框中不輸入任何字符，單擊“全部替換”按鈕。案例04 刪除數(shù)據(jù)中看不見的特殊字符11技能技能5：自定義排序與高級篩選的靈活運用：自定義排序與高級篩選的靈活運用排序排序如何按照規(guī)定的格式進行排序？排序的妙用：制作工資條注意事項（有公式時要小心）案例05 自定義排序與高級篩選應(yīng)用篩選篩選如何篩選不重復(fù)數(shù)據(jù)？如何進行多條件下的高級篩選？案例05 自定義排序與高級篩選應(yīng)用12技能技能6：數(shù)據(jù)的快速分列：數(shù)據(jù)的快速分列數(shù)據(jù)分列是在實際工

7、作中經(jīng)常遇到的問題?；痉椒ɑ痉椒ǚ椒?：使用“分列”工具根據(jù)分融符號進行數(shù)據(jù)分列根據(jù)數(shù)據(jù)寬度進行數(shù)據(jù)分列方法2：利用函數(shù)案例06 數(shù)據(jù)的快速分列13技能技能7：設(shè)計標(biāo)準(zhǔn)的表格模板：設(shè)計標(biāo)準(zhǔn)的表格模板使用數(shù)據(jù)有效性規(guī)范控制數(shù)據(jù)的輸入。數(shù)據(jù)有效性的應(yīng)用：案例07 設(shè)計標(biāo)準(zhǔn)規(guī)范的管理表單 14技能技能8：保護工作表：保護工作表指定區(qū)域指定區(qū)域工作表中的有些地方有時是需要保護或設(shè)置修改權(quán)限的，例如：做好的計算公式等。步聚Step1：先解除整個工作表或者指定區(qū)域的鎖定Step2：選擇要保護的公式單元格，鎖定或隱藏Step3：保護工作表案例08 保護工作表15第二部分第二部分 日常數(shù)據(jù)的計算、提取、匯

8、日常數(shù)據(jù)的計算、提取、匯總邏輯判斷及查詢應(yīng)用總邏輯判斷及查詢應(yīng)用技能9：日期的計算（日期函數(shù)的綜合應(yīng)用）技能10：從數(shù)據(jù)字符中提取需要的數(shù)據(jù)（文本函數(shù)綜合應(yīng)用）技能11：日常數(shù)據(jù)的邏輯判斷處理技能12：日常數(shù)據(jù)的高效求和與匯總技能13：日常數(shù)據(jù)的高效查詢16技能技能9：日期計算（日期函數(shù)的綜合應(yīng)用）日期計算（日期函數(shù)的綜合應(yīng)用）3個重要的日期函數(shù)TODAY 計算當(dāng)天日期 用法：=TODAY()EDATE 計算到期日 用法：=EDATE(開始日期，總月數(shù))DATEDIF 計算兩個日期之間的期限 用法： =DATEDIF(開始日期，截止日期，計算類型)計算類型：Y時間段中的整年數(shù);M 時間段中的整

9、月數(shù)。 問題：已知合同簽訂日和期限，如何計算合同到期日？已知付款期限，截止日為下月5日，如何計算付款日？已知生日，如何計算年齡？案列09 常用日期函數(shù)的應(yīng)用17技能技能10：從數(shù)據(jù)字符中提取需要的數(shù)據(jù)：從數(shù)據(jù)字符中提取需要的數(shù)據(jù)F幾個注意事項：幾個注意事項： 輸入文字應(yīng)該注意的幾個問題 正確區(qū)別文本型數(shù)字和純數(shù)字 將不能計算的數(shù)字快速修改為可以計算的純數(shù)字F常用的幾個文本函數(shù)常用的幾個文本函數(shù) LEN( ) 計算字符串的長度 LENB( ) 計算字符串中字節(jié)的長度 LEFT( ) 從左往右取數(shù) RIGHT( ) 從右往左取數(shù) MID() 從指定位置取數(shù)案例10 文本函數(shù)的綜合應(yīng)用18技能技能1

10、1：日常數(shù)據(jù)的邏輯判斷處理：日常數(shù)據(jù)的邏輯判斷處理數(shù)據(jù)的處理和分析，離不開邏輯判斷。常用的邏輯判斷函數(shù)IF，簡單的判斷函數(shù) =IF(判斷表達(dá)式，條件成立的結(jié)果，條件不成立的結(jié)果)嵌套IF，注意邏輯關(guān)系和正確的輸入方法案例11 數(shù)據(jù)的邏輯判斷處理19技能技能12：日常數(shù)據(jù)的高效求和與匯總：日常數(shù)據(jù)的高效求和與匯總 常用的分類匯總函數(shù)SUM： 簡單的無條件求和函數(shù)SUMIF：單條件求和函數(shù)=SUMIF(條件判斷區(qū)域，條件值，實際求和區(qū)域)SUMIF函數(shù)兩種用法：1. 精確匹配匯總 2. 模糊匹配匯總注：注：sumif函數(shù)模糊匹配匯總，使用通配符合連接符函數(shù)模糊匹配匯總，使用通配符合連接符 通配符通

11、配符 * 連接符連接符 &案例12 數(shù)據(jù)的高效求和匯總20技能技能13：日常數(shù)據(jù)的高效查詢：日常數(shù)據(jù)的高效查詢查找數(shù)據(jù)：查找數(shù)據(jù)：FF 從表格中把滿足條件的數(shù)據(jù)查找出來FF 常用函數(shù)：VLOOKUP=VLOOKUP(查找依據(jù)，查找區(qū)域，取數(shù)的列位置，查找模式)FF 注意事項 列結(jié)構(gòu)表格 單條件查詢 從左往右查找 不允許有重復(fù) 不區(qū)分大小寫案例13 日常數(shù)據(jù)的高效查詢21第三部分第三部分 高效數(shù)據(jù)分析工具及應(yīng)用高效數(shù)據(jù)分析工具及應(yīng)用技能14：條件格式的實際應(yīng)用技能15：數(shù)據(jù)透視表的快速創(chuàng)建與布局技能16：數(shù)據(jù)報表的多角度統(tǒng)計分析技能17：數(shù)據(jù)報表的占比分析技能18：數(shù)據(jù)報表中日期、地區(qū)、

12、年齡、工資的組合統(tǒng)計分析應(yīng)用技能19：數(shù)據(jù)透視表中新分析指標(biāo)的添加技能20：相同結(jié)構(gòu)工作表的快速匯總（多表快速匯總）技能21：快速核對數(shù)據(jù)（不同工作表數(shù)據(jù)的快速核銷）22技能技能14：條件格式的實際應(yīng)用：條件格式的實際應(yīng)用條件格式條件格式F 根據(jù)指定的條件，來設(shè)置單元格格式F 應(yīng)用： 跟蹤數(shù)據(jù)變化 合同提前提醒 庫存預(yù)警 美化表格F 注意事項： 正確設(shè)置公式 正確設(shè)置單元格引用方式案例14 條件格式的實際應(yīng)用23技能技能15：數(shù)據(jù)透視表的快速創(chuàng)建與布局：數(shù)據(jù)透視表的快速創(chuàng)建與布局創(chuàng)建數(shù)據(jù)透視表創(chuàng)建數(shù)據(jù)透視表F利用數(shù)據(jù)透視表向?qū)?按照向?qū)Р襟E進行操作缺點：無法實現(xiàn)數(shù)據(jù)源的動態(tài)更新，除非使用動態(tài)數(shù)

13、據(jù)區(qū)域名稱F利用導(dǎo)入數(shù)據(jù)工具制作數(shù)據(jù)透視表 利用Excel的導(dǎo)入數(shù)據(jù)工具制作優(yōu)點：這種數(shù)據(jù)透視表是一種動態(tài)的數(shù)據(jù)透視表，如果工作表的數(shù)據(jù)的增加或減少了，數(shù)據(jù)透視表的數(shù)據(jù)源也隨之發(fā)生改變案例15 數(shù)據(jù)透視表創(chuàng)建與布局24技能技能16：數(shù)據(jù)報表的多角度統(tǒng)計分析：數(shù)據(jù)報表的多角度統(tǒng)計分析案例16 數(shù)據(jù)報表的多角度統(tǒng)計分析源始數(shù)據(jù)多角度分析報表25技能技能17：數(shù)據(jù)報表的占比分析：數(shù)據(jù)報表的占比分析設(shè)置字段顯示方式：對數(shù)據(jù)進行深層分析案例17 數(shù)據(jù)報表的占比分析26技能技能18：數(shù)據(jù)報表組合統(tǒng)計分析應(yīng)用：數(shù)據(jù)報表組合統(tǒng)計分析應(yīng)用組合字段：挖掘深層次分析指標(biāo)按日期（年、季度、月）分組查看按地區(qū)分組查看數(shù)字組合（職工年齡、工資） 組合應(yīng)注意的事項頁字段不能進行組合字段內(nèi)有空白項不能進行組合有非法日期時不能對日期進行組合某個字段內(nèi)有不同類型數(shù)據(jù)時不能組合案例18 數(shù)據(jù)的組合應(yīng)用27技能技能19：數(shù)據(jù)透視表中新分析指標(biāo)的添：數(shù)據(jù)透視表中新分析指標(biāo)的添加加添加自定義計算字段通過自定義計算字段，可以為報表設(shè)計新的考察分析指標(biāo)，對數(shù)據(jù)進行多角度和深層次的分析。案例19 數(shù)據(jù)透視表中新分析指標(biāo)的添加源始數(shù)據(jù)新增指標(biāo)后的分析報表28技能技能20：相同結(jié)構(gòu)工作表的快速匯總：相同結(jié)構(gòu)工作表的快速匯總 使用合并計算