Excel高級運用和數(shù)據(jù)分析

1、Excel高級運用和數(shù)據(jù)分析蔣曉君1Contents目 錄數(shù)據(jù)分析 Excel的函數(shù)運用Excel的圖表制作 2課程開發(fā)背景員工問題一：個人素質水平分布不均衡，對管理工具的使用程度不一問題二：缺乏主動學習和運用管理工具的意識問題三：有工作熱情，卻管理水平不高，無法創(chuàng)造高績效問題四：一線管理人員對管理工具的運用不熟練，導致工作繁忙而效率不高綜述：以上問題的出現(xiàn)，表明員工應加強對管理工具的學習和運用，常用的管理工具有Excel，Word等，我們本次課程以講解Excel為例。3Excel常用函數(shù)回顧我們平時經(jīng)常使用的Excel函數(shù)有：SUM：求和函數(shù)AVERAGE：平均值函數(shù)COUNT：記數(shù)函數(shù)MA

2、X：求最大值函數(shù)MIN：求最小值函數(shù)另外，就是平時使用的四則運算 但這僅僅是Excel功能使用的冰山一角，Excel軟件可實現(xiàn)的功能要比這強大很多，只是我們沒有去學習和運用它。4Excel實用函數(shù)教學函數(shù)運用心得面對一個完全陌生的Excel函數(shù)時，我們應處于一個平和的心態(tài)。面對一個完全陌生的Excel函數(shù)時，我們應知道在哪可以得到幫助，如下圖：先打開一個Excel表格，通過這我們可以查詢到所有函數(shù)的信息和說明5Excel實用函數(shù)教學函數(shù)運用心得仔細閱讀函數(shù)下方的注解，我們可輕而易舉的了解每個函數(shù)的用途，如下圖：最后就是通過不斷的實踐，不斷的提高自己的Excel的運用能力。6Excel實用函數(shù)教

3、學vlookup函數(shù)（一）函數(shù)格式:函數(shù)由四部分組成,(第一、建立連接的單元格；第二、數(shù)據(jù)表范圍;第三、在數(shù)據(jù)表中需要查找引用的數(shù)據(jù)列號;第四、順序判斷語句false,若使用中數(shù)據(jù)按數(shù)字大小或按字母排列時可不加false )舉例：VLOOKUP為查找函數(shù),本例中須將家庭住址表中的住址按姓名引入到班組表中.如下圖：通過vlookup函數(shù)從其他的表格中引入“家庭住址”7Excel實用函數(shù)教學vlookup函數(shù)（二）我們先在同一Excel文件中建立一個家庭住址表，姓名欄按順序排列，如下圖：8Excel實用函數(shù)教學vlookup函數(shù)（三）函數(shù)說明:VLOOKUP為查找函數(shù),本例中須將家庭住址表中的住址

4、按姓名引入到班組表中.函數(shù)格式:函數(shù)由四部分組成,(第一、建立連接的單元格,本例為姓名;第二、數(shù)據(jù)表范圍,本例數(shù)據(jù)表為家庭住址表A和B列;第三、在數(shù)據(jù)表中需要查找引用的數(shù)據(jù)列號,本例須引用第二列家庭住址,故列號為2;第四、順序判斷語句false,若使用中數(shù)據(jù)按數(shù)字大小或按字母排列時可不加false )9Excel實用函數(shù)教學vlookup函數(shù)（四）在原班組表的“家庭住址”欄處，引用vlookup公式，具體如下圖所示：=VLOOKUP(B2,家庭住址表!A:B,2,FALSE)運用范圍：當我們要進行較大量的數(shù)據(jù)查找，但原始的數(shù)據(jù)資料的排序又異常混亂時，可采用vlookup函數(shù)進行查找。10Exc

5、el實用函數(shù)教學sumif函數(shù)（一）函數(shù)格式:函數(shù)由三部分組成,(第一、數(shù)據(jù)表范圍；第二、連接表范圍;第三、取值范圍，及要將哪一部分數(shù)據(jù)求和 )舉例： sumif函數(shù)為滿足條件的求和函數(shù),本例中將工號相同的有效產量進行求和.如下圖：將工號相同的有效產量求和11Excel實用函數(shù)教學sumif函數(shù)（二）我們先在同一Excel文件中建立一個班組目錄表，如下圖：12Excel實用函數(shù)教學sumif函數(shù)（三）函數(shù)說明: sumif函數(shù)為滿足條件的求和函數(shù),本例中須將原數(shù)據(jù)總表出現(xiàn)相同工號員工的有效產量相加，函數(shù)由三部分組成,(第一、數(shù)據(jù)表范圍，即數(shù)據(jù)總表中工號所在欄；第二、連接表范圍，即班組目錄表中工

6、號所在欄;第三、取值范圍，表示要將哪一部分數(shù)據(jù)求和，本例中取值范圍為數(shù)據(jù)總表中的“有效產量”欄 )13Excel實用函數(shù)教學sumif函數(shù)（四）在原班組目錄表的“有效產量”欄處，引用sumif函數(shù)，具體如下圖所示：=SUMIF(數(shù)據(jù)總表!A:A,班組目錄!A:A,數(shù)據(jù)總表!C:C)運用范圍：當我們從系統(tǒng)中導出大量的原始數(shù)據(jù)時，可能出現(xiàn)相同工號多個有效數(shù)據(jù)，可采用vlookup函數(shù)進行求和，得出單一目標的有效數(shù)據(jù)匯總。一般情況下，因為導出的數(shù)據(jù)大多為文本型，多與value函數(shù)使用。14Excel函數(shù)運用心得解讀順序（一） 當我們遇到復合函數(shù)時，我們會被長串的公式給嚇倒，從而影響我們的使用。如何有

7、效的解讀函數(shù)，特別是較長的復合函數(shù)？ 步驟如下：1、先確定函數(shù)所屬的層次，如：=SUMA5，SUM（A1，A3）+SUM（B1，B3）。根據(jù)運算規(guī)則，我們知道第一步先操作SUM（A1，A3），第二步操作SUMA5，SUM（A1，A3）和SUM（B1，B3），根據(jù)操作的順序來確定每一層次2、在確認層次后，根據(jù)操作步驟的順序，一步步進行操作，如： =SUMA5，SUM（A1，A3）+SUM（B1，B3），先操作SUM（A1，A3）15Excel函數(shù)運用心得解讀順序（二）舉例：如下圖，這是我們考核信息表中的一部分截圖。16Excel函數(shù)運用心得解讀順序（三）實際上班工時一欄中的公式為：= F2+IF

8、(G2=0,0,G2)-M2-N2-H2-SUMIF(其他假!B:B,現(xiàn)場考核匯總表!A:A,其他假!E:E)+SUMIF(上班工時表明細!A:A,現(xiàn)場考核匯總表!A:A,上班工時表明細!G:G)，我們只需先劃分層次，后按操作的順序一步步操作即可。17Contents目 錄數(shù)據(jù)分析 Excel的函數(shù)運用Excel的圖表制作 18Excel的圖表制作目的 Excel軟件除了強大的函數(shù)功能外，還有對數(shù)據(jù)進行顯形化的圖表制作功能，如下圖：圖表的類型很多，但主要的目的就是為了將數(shù)據(jù)更好的通過圖表的方式顯形化，使我們更容易對數(shù)據(jù)進行分析和處理。19Excel的圖表制作步驟（一）舉例：以柱形圖為例，操作步

9、驟如下A、在Excel表格上新建立數(shù)據(jù)表B、選取整個數(shù)據(jù)表后，點擊插入圖表20Excel的圖表制作步驟（二）CD圖表類型選擇21Excel的圖表制作步驟（三）EF可填寫表頭、X軸和Y軸的名稱22Excel的圖表制作步驟（四）GH23以上幾個步驟就是Excel圖表的制作過程，這其中還有很多的細微操作都未在課程中講解，各位可以在平時使用時多加嘗試。24Contents目 錄數(shù)據(jù)分析 Excel的函數(shù)運用Excel的圖表制作 25數(shù)據(jù)的概念數(shù)據(jù)分析的概念呼叫中心數(shù)據(jù)分析技術數(shù)據(jù)分析在呼叫中心運營中的運用數(shù)據(jù)分析目錄26 數(shù)據(jù)不僅僅是指數(shù)值型的，也可以是非數(shù)值型的。比如指標的表現(xiàn)形態(tài)既可以是數(shù)值型的，

10、也可以是非數(shù)值型，即文字型的。數(shù)值型的數(shù)據(jù)有非數(shù)值型的數(shù)據(jù)有數(shù)據(jù)的定義27按時間狀況分類依據(jù)測量尺度來分類定類數(shù)據(jù)定序數(shù)據(jù)定距數(shù)據(jù)定比數(shù)據(jù)離散數(shù)據(jù)和連續(xù)數(shù)據(jù)思考：某天或某一個月的呼入量，我們是怎么分類的呢？ ？數(shù)據(jù)分類28 好的報表的要求格式上內容上數(shù)據(jù)源上報表與報告的區(qū)別數(shù)據(jù)報表29思考：夢網(wǎng)短信退訂，1000人次；隨意行業(yè)務咨詢，比上月增長了26%；客戶代表平均每小時接聽電話數(shù)量，第一名比最后一名多4.3個電話。數(shù)據(jù)分析的目的在于發(fā)現(xiàn)問題，解釋原因和關系，以及尋找可能的解決辦法。沒有方向，數(shù)據(jù)分析更是無從下手，沒有比較，數(shù)據(jù)本身毫無意義 數(shù)據(jù)分析的目的30設定業(yè)務目標確定KPIKPI追蹤分

11、析分解KPI制定行動計劃誰做什么需要什么資源什么時間完成效果數(shù)據(jù)分析的流程31數(shù)據(jù)預處理橫縱向比較法均值和離差分析法趨勢分析法周期分析法相關分析法原因和影響分析法帕雷托分析六西格瑪問題解決模式SPC過程控制數(shù)據(jù)分析方法32數(shù)據(jù)收集數(shù)據(jù)甄別數(shù)據(jù)審核數(shù)據(jù)排序 例如:12580的接入話務量報表和人工子業(yè)務話務量之間的關系數(shù)據(jù)預處理33橫向比較法縱向比較法案例：選石頭思考：1、拒絕挑石頭；2、指出兩塊石頭都是黑色的；3、選一塊石頭，然后犧牲自己。橫縱向比較分析法34案例分析： 可以怎么分析以下的產量數(shù)據(jù)？ 35均值的定義離差的定義正態(tài)分布及函數(shù)眾數(shù)：是一組數(shù)據(jù)當中出現(xiàn)次數(shù)最多的變量值。中位數(shù)：是一組數(shù)

12、據(jù)按照大小排序后，處于正中間的位置的變量值。均值：就是全部數(shù)據(jù)的算術平均值。方差：是各變量值與其均值的離差平方的平均數(shù)。標準差：方差的平方根就是標準差。樣本的方差：總體的方差是用數(shù)據(jù)個數(shù)或總頻數(shù)來除離差的平方和，而樣本的方差是用樣本數(shù)據(jù)的個數(shù)或總頻數(shù)減去1 去除離差的平方和。幾個定義均值與離差分析法36正態(tài)分布函數(shù)Normdist( x,mean,standard_dev,comulative)實際頻次Frequency(Data_array,bins_array)均值、中值、標準離差率、標準差、眾數(shù)均值與離差分析法37 比如Y=XX的表述方式，描述了數(shù)據(jù)的均值和離散程度，正態(tài)分布是最常見的分

13、布情況，在近似于正態(tài)分布情況下：當X=1S，數(shù)據(jù)68.3%在XX區(qū)間，84.1%小于 XX；當X=2S，數(shù)據(jù)95.5%在XX區(qū)間，97.7%小于 XX；當X=3S，數(shù)據(jù)99.7%在XX區(qū)間，99.9%小于 XX。正態(tài)分布38案例分析： 話務量的正態(tài)分布39定義趨勢分析法主要是研究數(shù)據(jù)隨時間變化情況?？煞譃榫€性趨勢和非線性趨勢。原理事物的特性沿過去、現(xiàn)在和未來的時間過程延續(xù)發(fā)展；影響該事物發(fā)展趨勢的條件在預測期內是不變的。 趨勢分析法40周期性分析法定義通常呼叫中心話務量隨著時間而波動，而且每天不同時點、每周不同天數(shù)、每月不同天數(shù)等波動的周期都有一定的規(guī)律性。分析方法先總量后解剖周期分析法41案

14、例分析： 結合趨勢分析和周期分析，預測2007年話務量42月份來話量月份百分比預測200720042005200611762222970.074234421992473190.081737931902813140.083838942412252910.080837551662222770.071033061912363040.078036272202773540.090942282142714030.094844092012543820.0894415102062633170.0839389111972543290.0833387122112713440.0882409總計2412302339

15、311.00004641答案43不相關負線性相關正線性相關非線性相關完全負線性相關完全正線性相關相關分析是定量和定性分析的重要統(tǒng)計方法，相關關系（correlation）是指變量之間的不確定的依存關系。相關分析法44 問題的特性總是受到一些因素的影響，我們通過腦力激蕩找出這些因素，并將它們與特性值一起，按相互關聯(lián)性整理而成的層次分明、條理清楚，并標出重要因素的圖形就叫特性要因圖。因其形狀如魚骨，所以又叫魚骨圖（以下稱魚骨圖），它是一種透過現(xiàn)象看本質的分析方法。原因與影響分析（魚骨圖的應用）45案例：超長話務原因分析46定義在分析管理對象在經(jīng)濟或技術上的不同價值，并據(jù)此排序和分類，區(qū)別重點和一般

16、,以選擇最佳管理方式的一種管理方法，又稱成本比重分析法，其對不同類的對象投入不同的管理力量，對起決定性影響的主要對象進行重點管理，以提高管理的經(jīng)濟效益。ABC分析思考：如何做一個帕雷托圖？網(wǎng)絡覆蓋邊界漫游優(yōu)惠通話質量漫游互聯(lián)互通業(yè)務使用超長、超短話單其它總計167671448179906951292924992214572662961032帕雷托分析法476 sigma是研究流程變量與流程能力間關系的；通過連續(xù)的測量分析改進控制循環(huán)，使流程能力不斷提高并最終達到或超過6 sigma水平。六西格瑪問題解決模式48六西格瑪問題解決模式49找問題（Select a problem and descr

17、ibe it clearly） 研究現(xiàn)時生產方法（Study the Present System） 找出各種原因（Identify Possible causes） 計劃及制定解決方法（Plan and implement a solution） 檢查效果（(Evaluate effects) 把有效方法制度法（Standardize any effective solutions） 檢討成效并發(fā)展新目標（Reflect on process and develop future plans） 當以上問題解決后，總結其成效，并制定解決其它問題的方案。6 sigma管理的實施方法 50 SPC即統(tǒng)計過程控制（Statistical Process Control）。SPC主要是指應用統(tǒng)計分析技術對生產過程進行實時監(jiān)控，科學的區(qū)分出生產過程中產品質量的隨機波動與異常波動，從而對生產過程的異常趨勢提出預警，以便生產管理人員及時采取措施，消除異常，恢復過程的穩(wěn)定，從而達到提高和控制質量的目的。SPC過程控制51運營效率監(jiān)控與改進為什么進行監(jiān)控？監(jiān)控什么？從哪里拿數(shù)據(jù)？怎么樣分析？業(yè)務指標分析投訴分析業(yè)務咨詢分析業(yè)務受理分析呼入總量：來了！ 總呼入電話數(shù)量是多少？相關呼