數(shù)據(jù)分析很簡單快速掌握Excel函數(shù)數(shù)據(jù)透視表和動態(tài)圖表市公開課一等獎百校聯(lián)賽特等獎課件

數(shù)據(jù)分析很簡單

—快速掌握Excel函數(shù)、

數(shù)據(jù)透視表和動態(tài)圖表

主講韓小良第1頁關于我們E訊東方簡介E訊東方，實名:上海倍訊企業(yè)管理咨詢有限企業(yè)。是專門為中外企業(yè)提供特色高品質Excel培訓和咨詢等相關服務一家專業(yè)機構。我們培訓特色是：結合企業(yè)實際情況設計針對性案例來解決問題，并注重培訓后續(xù)服務，使培訓真正能達到“今天學習，明天應用”效果；真正實現(xiàn)參加者、組織者、投資者三方滿意。我們優(yōu)勢：專業(yè)資深教授講師，保證培訓效果實用進階系統(tǒng)課程，保證課程質量免費多樣課后服務，保證客戶滿意我們信念：專業(yè)實效創(chuàng)新責任我們宗旨：了解客戶，服務客戶，發(fā)展客戶服務領域:公開課和企業(yè)內(nèi)訓涵蓋如下方面：財稅管理,員工信息管理,招聘管理，薪酬管理,考勤管理,銷售管理,市場分析,生產(chǎn)管理,倉儲物流管理,以及采購管理等；針對企業(yè)實際需求提供解決方案式咨詢輔導包括：人力資源管理體系建立和金融行業(yè)模型建立，企業(yè)財務模版，財稅軟件等。E訊東方，是企業(yè)管理向科學化，規(guī)范化和高效化發(fā)展路上，必不可少忠實伙伴！第2頁課程體系第3頁關于課程課程宗旨：幫您樹立一個正確高效使用Excel基本理念和思緒為您提供一套高效實用Excel技能和方法課程特點：系統(tǒng)性：囊括利用Excel進行高效日常管理各方面內(nèi)容。全方面性：全方面介紹了Excel工具在日常管理中經(jīng)典應用，兼顧Excel使用方法技巧介紹和應用思緒啟發(fā)，重視點面結合。實用性：重視Excel在管理中實際應用，培訓內(nèi)容緊密結合實際案例。精選數(shù)十個源自企業(yè)管理過程中經(jīng)典案例，極具代表性和實用價值，反應了當代企業(yè)中亟需處理問題，含有非常強針對性。示范性：強調動手實踐，使每個學員有充分動手機會，及時處理學習中問題。在進行實例分析時一步一步地遞進，清楚易懂，既方便學員了解管理決議分析過程，又使學員真正掌握Excel強大功效。細致性：講課認真、細致，輔導主動、耐心，使每個學員都能夠掌握所講內(nèi)容。無憂性：完善課后服務，隨時與老師聯(lián)絡，及時幫助企業(yè)學員處理實際工作中碰到問題和難題。第4頁經(jīng)典課程第5頁韓小良老師介紹韓小良：資深實戰(zhàn)型Excel培訓講師；Excel應用處理方案教授和管理系統(tǒng)軟件開發(fā)設計師；陸續(xù)開發(fā)出了財務、銷售、人力資源、工資等管理、客戶管理、進銷存等基于Excel管理軟件。Excel應用方面著作第一人，出版了近40部關于Excel企業(yè)管理高效應用方面實用書籍。從事Excel研究及應用多年，對Excel及ExcelVBA在企業(yè)管理中應用有著較深研究和獨特認識，對Excel及VBA在管理中高級應用培訓有著豐富實戰(zhàn)經(jīng)驗，尤其是Excel及VBA在企業(yè)財務、會計、銷售、人力資源、金融財務等管理中應用。韓老師含有豐富實際管理經(jīng)驗和極強Excel應用開發(fā)能力；已經(jīng)為數(shù)千家大中型企業(yè)進行了Excel培訓和應用方案處理咨詢服務，在業(yè)界享受很高評價，深得客戶認可。第6頁我們客戶服務過客戶包含但不限于：

金融證券：中國銀行、浦發(fā)銀行、廣發(fā)銀行、民生銀行、上海農(nóng)商銀行、聯(lián)合證券、萬家基金、國金證券、上海太平資產(chǎn)管理企業(yè)、齊魯證券房地產(chǎn)：雅居樂地產(chǎn)、南益地產(chǎn)集團、北京城建、朗詩集團、新興集團醫(yī)藥醫(yī)療：上海醫(yī)藥集團、強生醫(yī)療器械（蘇州）、上海博萊科信誼藥業(yè)、蘇州輝瑞動保、豪森藥業(yè)、山東綠葉制藥、四川邁克生物能源電力：中國石化、勝利油田、北京國華能源投資、南方電網(wǎng)通信：廣東移動、上海電信、湖北聯(lián)通、泰州移動、齊齊哈爾移動、上海青浦電信網(wǎng)絡媒體：湯森路透、baidu總部、Google（中國）總部、西安華商報社、北京東方口岸、麥考林國際郵購生產(chǎn)制造企業(yè)：中糧集團、統(tǒng)一集團、光明乳業(yè)、東風雪鐵龍、揚州亞普汽車部件、摩根新材料、海斯特叉車、普茨邁斯特機械、南京華德火花塞、廣東歐普照明、蘇州珈農(nóng)生化（丁家宜）、上海三菱電梯、浙江中煙集團、安徽中煙、南京愛立信熊貓、上海施耐德電氣、偉福科技工業(yè)（武漢）、無錫華潤上華科技、嘉興阿克蘇諾貝爾涂料、常州瓦盧瑞克鋼管、貴州銀燕集團、上海晶澳太陽能光伏、江蘇德邦化工集團、北京奧瑞金種業(yè)、蘇州瀚德汽車產(chǎn)品、蘇州賽發(fā)過濾科技、蘇州GIF研發(fā)中心、江蘇交通科學研究院、南瑞集團、南京勁草時裝、上海SKF、日本佳能、浙江巨人控股、無錫工裝自控、上海柴油機股份、徐州圣戈班、無錫日新電機、鄭州煤機股份、蘇州道森礦采機械、上海圣戈班、揚州太平洋重工、蘇州金螳螂、中材科技、中國電子科技集團、中儲股份、中化（寧波）集團、浙江盾安熱工科技，廈門林德叉車、中鋁國際工程、北京萬源瀚德、南通恒輝手套、江陰吉利貝克尼、蒙牛集團、新興集團其它：ACCA、歐力士租賃、天目湖旅游企業(yè)，上海哈特佛德咨詢第7頁課程目錄第1部分公式函數(shù)基礎知識第2部分日期數(shù)據(jù)分析第3部分處理文本數(shù)據(jù)第4部分數(shù)據(jù)邏輯判斷第5部分數(shù)據(jù)分類匯總計算第6部分數(shù)據(jù)查找與引用第7部分快速掌握數(shù)據(jù)透視表技術第8部分快速掌握動態(tài)圖表技術第8頁第1部分公式函數(shù)基礎知識相對引用和絕對引用主要性創(chuàng)建復雜公式實用方法保護隱藏工作表中公式函數(shù)基本語法和注意事項快速準確輸入嵌套函數(shù)創(chuàng)建高效數(shù)據(jù)分析公式基本標準第9頁相對引用和絕對引用主要性相對引用：復制公式時，單元格引用發(fā)生相對位移絕對引用：復制公式時，單元格引用不發(fā)生位移合理設置相對引用和絕對引用，能夠快速復制公式案例練習案例01相對引用和絕對引用第10頁創(chuàng)建復雜公式實用方法當計算公式要用到很多函數(shù)，需要創(chuàng)建復雜嵌套函數(shù)公式時，能夠采取分解綜正當基本步驟Step1：先分解公式，確保每步都準確無誤Step2：再綜合成一個公式Step3：最終完善公式案例練習案例02分解綜正當創(chuàng)建復雜公式第11頁保護隱藏工作表中公式工作表中計算公式必須保護起來，以免不小心破壞公式。那些沒有公式單元格，能夠不保護，隨時進行編輯。基本方法Step1：先解除整個工作表或者指定區(qū)域鎖定Step2：選擇要保護公式單元格，鎖定或隱藏Step3：保護工作表案例練習案例03保護隱藏工作表中公式第12頁函數(shù)基本語法和注意事項函數(shù)語法函數(shù)名(參數(shù)1，參數(shù)2，參數(shù)3，……)函數(shù)能夠有參數(shù)，也能夠沒參數(shù)參數(shù)能夠是數(shù)值、引用、名稱、表示式、另外一個函數(shù)全部標點符號都必須是半角字符學習函數(shù)三要素函數(shù)是干什么函數(shù)怎么用函數(shù)用在什么地方第13頁快速準確輸入嵌套函數(shù)嵌套函數(shù)：一個函數(shù)里嵌套一個相同函數(shù)或者不一樣函數(shù)快速準確輸入嵌套函數(shù)基本方法方法1：分解綜正當方法2：使用函數(shù)參數(shù)對話框和名稱框案例練習案例04快速準確輸入嵌套函數(shù)第14頁創(chuàng)建高效數(shù)據(jù)分析公式基本標準盡可能使用函數(shù)公式盡可能簡單必要時使用輔助計算區(qū)域盡可能不要使用數(shù)組公式第15頁第2部分日期數(shù)據(jù)分析日期函數(shù)三弟兄高效協(xié)議管理計算年紀和工齡計算固定資產(chǎn)折舊綜合練習測驗第16頁日期函數(shù)三弟兄today：計算當日日期使用方法：=today()edate：計算到期日使用方法：=edate(開始日期，總月數(shù))datedif：計算兩個日期之間期限使用方法：=datedif(開始日期，截止日期，格式代碼)第17頁高效協(xié)議管理怎樣快速計算協(xié)議到期日？EDATE函數(shù)EOMONTH函數(shù)怎樣對協(xié)議進行動態(tài)跟蹤，提前提醒？條件格式案例練習案例05高效協(xié)議管理第18頁計算年紀和工齡計算年紀和工齡DATEDIF函數(shù)案例練習案例06計算年紀和工齡第19頁計算固定資產(chǎn)折舊固定資產(chǎn)折舊計算動態(tài)、任意選擇月份考慮會計制度要求使用函數(shù)EOMONTHDATEDIFIF案例練習案例07計算固定資產(chǎn)折舊第20頁第3部分處理文本數(shù)據(jù)文本函數(shù)六弟兄快速將數(shù)字和日期格式快速分列規(guī)則漢字和數(shù)字從身份證號碼自動提取信息使用FIND函數(shù)進行含糊匹配查找綜合練習測驗

第21頁文本函數(shù)六弟兄len：計算字符串長度（字符個數(shù)）使用方法：＝len(字符串)left：從字符串左邊第一個開始，向右取指定個數(shù)字符使用方法：＝left(字符串,字符個數(shù))right：從字符串右邊第一個開始，向左取指定個數(shù)字符使用方法：＝right(字符串,字符個數(shù))mid：從字符串中某個字符開始，向右取指定個數(shù)字符使用方法：＝mid(字符串,起始位置,字符個數(shù))text：把數(shù)字轉換成指定格式文本使用方法：＝text(字符串,格式代碼)find：查找指定字符串在另外一個字符串中位置使用方法：＝find(查找字符串,字符串,查找起始位置)第22頁快速將數(shù)字和日期格式轉換數(shù)字格式，能夠使用text函數(shù)案例練習案例08快速將數(shù)字和日期格式第23頁快速分列規(guī)則漢字和數(shù)字下面問題怎樣快速處理：怎樣將規(guī)則漢字和數(shù)字分開？怎樣將漢字姓名和英文姓名分開？使用函數(shù)len、lenb、left、mid、right案例練習案例09快速分列規(guī)則漢字和數(shù)字第24頁從身份證號碼自動提取信息怎樣輸入身份證號碼，自動提取個人信息，比如出生日期、性別、戶口所在地？案例練習案例10從身份證號碼提取信息第25頁使用FIND函數(shù)進行含糊匹配查找FIND函數(shù)在處理文本數(shù)據(jù)時非常有用，能夠進行含糊查找，也能夠進行數(shù)據(jù)分列。案例練習案例11復雜情況下數(shù)據(jù)分列第26頁第4部分數(shù)據(jù)邏輯判斷邏輯函數(shù)四弟兄快速準確輸入嵌套IF函數(shù)處理公式錯誤值，讓表格更清楚綜合練習：處理重復考勤刷卡數(shù)據(jù)問題研究：嵌套IF無法處理了怎么辦？第27頁邏輯函數(shù)四弟兄if：基本邏輯判斷使用方法：=if(條件表示式,條件成立時結果,條件不成立時結果)and：把幾個條件組合成一個與條件使用方法：=and(條件1,條件2,條件3,……)or：把幾個條件組合成一個與條件使用方法：=or(條件1,條件2,條件3,……)iserror：判斷一個值是否為錯誤值使用方法：=iserror(值)第28頁快速準確輸入嵌套IF函數(shù)快速準確輸入嵌套if基本步驟先捋清楚邏輯流程采取正確方法輸入嵌套if案例練習案例12快速準確輸入嵌套IF函數(shù)第29頁處理公式錯誤值，讓表格更清楚處理錯誤值，目標是讓表格清楚美觀，又不影響匯報重復使用。使用函數(shù)聯(lián)合使用if函數(shù)和iserror函數(shù)僅僅使用iferror函數(shù)案例練習案例13處理公式錯誤值第30頁綜合練習：考勤刷卡/指紋數(shù)據(jù)處理怎樣對從刷卡機導出考勤刷卡數(shù)據(jù)進行處理，把重復刷卡數(shù)據(jù)刪除，保留唯一數(shù)據(jù)？怎樣從導出指紋數(shù)據(jù)進行統(tǒng)計分析，計算每個人遲到、早退、全勤次數(shù)？案例練習案例14考勤刷卡-指紋數(shù)據(jù)處理第31頁問題研究：嵌套IF無法處理了怎么辦？

當IF嵌套超出7層時，必須采取其它方法來處理數(shù)據(jù)判斷問題了，比如：使用條件表示式使用VLOOKUP使用SUMIF使用SUMIFS使用SUMPRODUCT等等案例練習案例15使用其它工具處理嵌套if無法處理問題第32頁第5部分數(shù)據(jù)分類匯總計算分類匯總函數(shù)三弟兄兩種主要分類匯總問題SUMIF函數(shù)兩種最實用計算方法SUMIFS函數(shù)適用條件與練習神奇SUMPRODUCT函數(shù)三弟兄優(yōu)缺點比較綜合練習1：管理費用動態(tài)管控模版綜合練習2：員工信息多維度分析與流動性動態(tài)分析模版綜合練習測驗第33頁分類匯總函數(shù)三弟兄SUMIF：單條件求和使用方法：=SUMIF(判斷區(qū)域,條件值,求和區(qū)域)SUMIFS：多條件求和使用方法：=SUMIFS(求和區(qū)域,判斷區(qū)域1,條件值1,判斷區(qū)域2,條件值2,……)SUMPRODUCT：單條件求和、多條件求和、單條件計數(shù)、多條件計數(shù)使用方法：=SUMPRODUCT(數(shù)組1,數(shù)組2,數(shù)組3,……)第34頁兩種主要分類匯總問題求和單條件求和多條件求和計數(shù)單條件計數(shù)多條件計數(shù)第35頁SUMIF函數(shù)兩種最實用計算方法SUMIF函數(shù)兩種使用方法：準確匹配匯總含糊匹配匯總案例練習案例16SUMIF函數(shù)應用第36頁SUMIFS函數(shù)適用條件與練習SUMIFS函數(shù)適用條件：與條件必須已經(jīng)有這么條件列案例練習案例17SUMIFS函數(shù)應用第37頁神奇SUMPRODUCT函數(shù)SUMPRODUCT函數(shù)是一個多功效函數(shù)，能夠進行各種條件下求和與計數(shù)計算，也能夠進行愈加復雜匯總、數(shù)據(jù)查找、排名等。需要使用其它技能條件表示式TEXT函數(shù)案例練習案例18SUMPRODUCT函數(shù)第38頁三弟兄優(yōu)缺點比較請您總結一下，sumif、sumifs、sumproduct三個函數(shù)各有什么優(yōu)缺點第39頁綜合練習1：管理費用動態(tài)管控模版練習：將從NC導出管理費用科目余額表進行匯總。案例19管理費用動態(tài)管控模版

第40頁綜合練習2：員工信息多維度分析

與流動性動態(tài)分析模版練習：對員工屬性進行分析，并建立員工流動性分析模版案例20員工信息綜合分析

第41頁第6部分數(shù)據(jù)查找與引用查找引用函數(shù)五弟兄VLOOKUP函數(shù)MATCH函數(shù)和INDEX函數(shù)INDIRECT函數(shù)OFFSET函數(shù)滾動靈活查找技術：制作靈活明細查詢表滾動實時匯總技術：快速匯總個數(shù)不定大量工作表滾動累計匯總技術：查看任意月份當月數(shù)和累計數(shù)綜合應用：制作員工簡歷查詢表綜合練習測驗

第42頁查找引用函數(shù)五弟兄VLOOKUP：依據(jù)指定條件從指定區(qū)域指定列取數(shù)使用方法：=VLOOKUP(查找依據(jù),查找區(qū)域,取數(shù)列位置,準確查詢or含糊查詢)MATCH：查找指定數(shù)據(jù)在指定區(qū)域位置使用方法：=MATCH(查找值,查找區(qū)域,查找模式)INDEX：從指定區(qū)域指定行和指定列取數(shù)使用方法：=INDEX(指定區(qū)域,指定行,指定列)INDIRECT：把字符串表示單元格地址轉換為真正引用使用方法：=INDIRECT(字符串表示單元格地址)OFFSET：從指定參考單元格開始向下（向上）、向右（向左）偏移，得到一個新單元格或者單元格區(qū)域使用方法：=OFFSET(參考單元格,偏移行,偏移列,新區(qū)域行數(shù),新區(qū)域列數(shù))第43頁VLOOKUP函數(shù)VLOOKUP函數(shù)只能用于滿足以下條件場所：查詢表是列結構查詢條件是單條件查詢方向是從左往右查詢條件不允許有重復查詢條件是不區(qū)分大小寫VLOOKUP函數(shù)兩種基本使用方法準確查詢含糊查詢案例練習案例21VLOOKUP函數(shù)各種應用第44頁MATCH函數(shù)和INDEX函數(shù)聯(lián)合使用MATCH函數(shù)和INDEX函數(shù)，能夠處理絕大多數(shù)數(shù)據(jù)查問詢題。案例練習案例22MATCH-INDEX函數(shù)應用第45頁INDIRECT函數(shù)把字符串表示單元格地址轉換為真正引用比如，單元格C3中保留文本“A2”，而此文本恰好是單元格A2名字，那么公式=INDIRECT(C3)就得到單元格A2數(shù)據(jù)比如，公式=INDIRECT(“sheet1!C5”)就間接得到工作表Sheet1單元格C5數(shù)據(jù)。

第46頁OFFSET函數(shù)從指定參考單元格開始向下（向上）、向右（向左）偏移，得到一個新單元格或者單元格區(qū)域。比如，公式=OFFSET(A1,3,4)就得到單元格E4引用，也就是引用該單元格數(shù)據(jù)比如，公式=OFFSET(A1,3,4,2,6)就得到單元格區(qū)域E4:J5引用問題研究：怎樣總是計算最近三個月平均值？怎樣計算截止到指定月份累計數(shù)？第47頁滾動靈活查找技術

制作靈活明細查詢表VLOOKUP函數(shù)和MATCH函數(shù)最大缺點是無法查詢重復數(shù)據(jù)，所以在制作明細表時就無能為力了。利用滾動查找技能，能夠快速制作任何條件下明細表。關鍵技能：INDIRECT函數(shù)MATCH函數(shù)INDEX函數(shù)IF函數(shù)條件表示式條件格式案例練習案例23數(shù)據(jù)滾動查找技術第48頁滾動實時匯總技術

快速匯總個數(shù)不定大量工作表當要匯總工作表數(shù)目不定時，怎樣把這些工作表數(shù)據(jù)匯總到一個工作表上？利用滾動匯總技能，能夠快速制作大量不定工作表數(shù)據(jù)匯總和分析。關鍵技能INDIRECT函數(shù)案例練習案例24快速匯總個數(shù)不定工作表

第49頁滾動累計匯總技術

查看任意月份當月數(shù)和累計數(shù)怎樣從一個預算表和實際執(zhí)行表中，把指定月份當月數(shù)查詢出來，并計算截止到該月份累計值？關鍵函數(shù)MatchOffsetSumif案例練習案例25任意查詢當月數(shù)和累計數(shù)第50頁綜合應用：制作員工簡歷查詢表怎樣依據(jù)員工簡歷信息，制作動態(tài)簡歷查詢表，既能夠顯示建立信息數(shù)據(jù)，又能顯示圖片？案例練習案例26制作動態(tài)員工檔案卡第51頁第7部分快速掌握數(shù)據(jù)透視表技術創(chuàng)建數(shù)據(jù)透視表基本方法和注意事項數(shù)據(jù)透視表美化建立多維度統(tǒng)計分析報表建立深層次統(tǒng)計分析報表建立多層統(tǒng)計分析報表建立多指標計分析報表利用數(shù)據(jù)透視表快速匯總大量工作表利用數(shù)據(jù)透視表快速查對兩個表格數(shù)據(jù)第52頁創(chuàng)建數(shù)據(jù)透視表基本方法和注意事項怎樣創(chuàng)建數(shù)據(jù)透視表首先標準化、規(guī)范化表格數(shù)據(jù)使用正確方法制作數(shù)據(jù)透視表（要考慮數(shù)據(jù)源改變，方法是否正確）錯誤做法選擇整列區(qū)域選擇整個工作表區(qū)域

案例練習案例27數(shù)據(jù)透視表基本練習第53頁數(shù)據(jù)透視表美化制作基本數(shù)據(jù)透視表是極難看，需要進行美化。主要技能使用“數(shù)據(jù)透視表選項”對話框使用“字段設置”對話框使用快捷菜單命令第54頁建立多維度統(tǒng)計分析報表經(jīng)過設置字段分類匯總方式，能夠建立多維度統(tǒng)計分析報表第55頁建立深層次統(tǒng)計分析報表經(jīng)過設置字段顯示方式，能夠建立深層次統(tǒng)計