Excel數(shù)據(jù)分析處理二

會計學(xué)1Excel數(shù)據(jù)分析處理二數(shù)據(jù)處理與分析

1、數(shù)據(jù)處理與分析的概念2、數(shù)據(jù)處理3、數(shù)據(jù)常用分析方法第1頁/共54頁數(shù)據(jù)處理與分析

何謂數(shù)據(jù)分析數(shù)據(jù)分析是指用適當?shù)慕y(tǒng)計分析方法對收集來的大量數(shù)據(jù)進行分析，將它們加以匯總和理解并消化，以求最大化地開發(fā)數(shù)據(jù)的功能，發(fā)揮數(shù)據(jù)的作用。數(shù)據(jù)分析是為了提取有用信息和形成結(jié)論而對數(shù)據(jù)加以詳細研究和概括總結(jié)的過程。數(shù)據(jù)分析的分類：描述性數(shù)據(jù)分析：初級的數(shù)據(jù)分析探索性數(shù)據(jù)分析：高級的數(shù)據(jù)分析，如相關(guān)分析、回歸分析等驗證性數(shù)據(jù)分析：高級的數(shù)據(jù)分析，如假設(shè)檢驗等數(shù)據(jù)分析的作用現(xiàn)狀分析現(xiàn)階段的整體情況；企業(yè)各項業(yè)務(wù)的構(gòu)成原因分析現(xiàn)狀為什么會發(fā)生預(yù)測分析第2頁/共54頁數(shù)據(jù)分析六步曲明確分析目的和思路收集數(shù)據(jù)數(shù)據(jù)處理數(shù)據(jù)分析數(shù)據(jù)展現(xiàn)（圖表展現(xiàn)）報告撰寫幾個常用指標和術(shù)語平均數(shù)絕對數(shù)與相對數(shù)百分比與百分點百分點是指不同時期以百分數(shù)的形式表示的相對指標的變動幅度。頻數(shù)與頻率比例與比率倍數(shù)與番數(shù)同比與環(huán)比第3頁/共54頁對數(shù)據(jù)的理解字段與記錄字段是事物或現(xiàn)象的某種特征。記錄是事物或現(xiàn)象某種特征的具體表現(xiàn)。數(shù)據(jù)類型字符型數(shù)據(jù)數(shù)值型數(shù)據(jù)第4頁/共54頁數(shù)據(jù)表二維表

二維表第5頁/共54頁利用數(shù)據(jù)透視表實現(xiàn)二維表轉(zhuǎn)化為一維表Step1：在自定義功能去添加“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А?。Step2：單擊“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А卑粹o。Step3：點選多重合并計算數(shù)據(jù)區(qū)域，單擊下一步。Step4：選中“創(chuàng)建單頁字段”，并點擊下一步。Step5：選定區(qū)域并添加，單擊下一步。Step6：選擇結(jié)果輸出，點擊完成。Step7：雙擊數(shù)據(jù)透視表的最后一個單元格即可獲得一維數(shù)據(jù)表。第6頁/共54頁數(shù)據(jù)處理數(shù)據(jù)清洗清除不必要的重復(fù)數(shù)據(jù)填充缺失數(shù)據(jù)檢測邏輯錯誤數(shù)據(jù)數(shù)據(jù)加工排序：單關(guān)鍵字排序、多關(guān)鍵字排序、自定義排序分類匯總：一級分類匯總、多級分類匯總篩選：自動篩選、自定義篩選、高級篩選數(shù)據(jù)計算：利用函數(shù)和公式以及求和工具進行運算數(shù)據(jù)抽樣抽樣：利用函數(shù)以及抽樣分析工具進行抽樣第7頁/共54頁數(shù)據(jù)清洗重復(fù)數(shù)據(jù)的處理利用COUNTIF函數(shù)判斷當前值是否與歷史值相同并計數(shù)利用高級篩選點選不重復(fù)的記錄條件格式法“開始”“條件格式”“突出顯示單元格規(guī)則”“重復(fù)值”刪除重復(fù)數(shù)據(jù)“數(shù)據(jù)”“數(shù)據(jù)工具”組中的“刪除重復(fù)項”通過對字段的降序排序通過自定義篩選字段缺失數(shù)據(jù)處理用樣本統(tǒng)計量代替，例如平均值用統(tǒng)計模型計算出來的值代替直接刪除保留缺失值的記錄，但不做具體的分析檢查數(shù)據(jù)邏輯錯誤IF函數(shù)法第8頁/共54頁數(shù)據(jù)分析方法現(xiàn)狀分析對比分析法平均分析法綜合評價分析法原因分析分組分析法結(jié)構(gòu)分析法交叉分析法杜邦分析法漏斗圖分析法矩陣關(guān)聯(lián)分析法預(yù)測分析相關(guān)性分析回歸分析移動平均、指數(shù)平滑第9頁/共54頁對比分析法所謂對比分析法，是指將兩個或兩個以上的數(shù)據(jù)進行比較，分析它們的差異，從而揭示這些數(shù)據(jù)所代表的事物發(fā)展變化情況和規(guī)律性?？煞譃殪o態(tài)比較和動態(tài)比較靜態(tài)比較是在同一時間條件下對不同總體指標的比較，如不同部門、地區(qū)、國家的比較，也叫橫向比較。動態(tài)比較是在同一總體條件下對不同時期指標數(shù)值的比較，也叫縱向比較。注意：比較的指標要一致比較的對象要有可比性第10頁/共54頁平均分析法平均分析法是指運用計算平均數(shù)的方法來反應(yīng)總體在一定時間、地點條件下某一數(shù)量特征的一般水平。平均的指標有算術(shù)平均數(shù)、幾何平均數(shù)、調(diào)和平均數(shù)、眾數(shù)和中位數(shù)等。綜合評價分析法鑒于簡單分析的局限性，影響分析結(jié)果的因素是多個時，需要對每個因素確立一個權(quán)重，最后計算一個合計值進行綜合評價。分組分析法根據(jù)數(shù)據(jù)分析對象的特征，按照一定的標志，把數(shù)據(jù)分析對象劃分為不同的部分和類型來進行研究，以揭示其內(nèi)在的聯(lián)系和規(guī)律性。確定組數(shù)組距分組整理匯總分析差異第11頁/共54頁結(jié)構(gòu)分析法結(jié)構(gòu)分析法是指被分析總體內(nèi)的各部分與總體之間進行對比的分析方法，即總體內(nèi)各部分占總體的比例，屬于相對指標。交叉分析法通常用于分析兩個變量之間的關(guān)系，即同時將兩個有一定聯(lián)系的變量及其值交叉排列在一張表格內(nèi)，使各變量值成為不同變量的交叉結(jié)點，形成交叉表，從而分析交叉表中變量之間的關(guān)系。漏斗圖分析法漏斗圖是一個適合業(yè)務(wù)流程比較規(guī)范、周期比較長、各流程環(huán)節(jié)涉及復(fù)雜業(yè)務(wù)過程比較多的管理分析工具。這是一種最直觀的表現(xiàn)形式，很快能發(fā)現(xiàn)業(yè)務(wù)流程中存在的問題環(huán)節(jié)。第12頁/共54頁矩陣關(guān)聯(lián)分析法矩陣分析法是指根據(jù)事物的兩個重要屬性作為分析的依據(jù)，進行分類關(guān)聯(lián)分析，找出解決問題的一種分析方法。以屬性A為橫軸，以屬性B為縱軸，在兩坐標軸上分別按照某一標準進行刻度劃分，構(gòu)成四個象限，將要分析的每個事物對應(yīng)投射到這四個象限內(nèi)，進行交叉分類分析。低高重要性滿意度高第13頁/共54頁EXCEL2010圖表的使用1、介紹幾種常用圖表2、幾種稍復(fù)雜圖表的繪制與圖表美化第14頁/共54頁幾種常用圖表圖表的概念所謂圖表可分為圖和表，圖即指用圖形來表示數(shù)據(jù)，表即用表格來表示數(shù)據(jù)。使用圖表能夠使表達更加形象化，突出重點，體現(xiàn)出專業(yè)化基本圖表：表格：一維表格、二維表格等柱形圖：簇狀柱形圖、堆積柱形圖、百分比堆積柱形圖折線圖：普通折線圖、堆積折線圖、百分堆積折線圖以及帶數(shù)據(jù)標

記的折線圖餅圖：餅圖、分離型餅圖、復(fù)合餅圖、復(fù)合條餅圖條形圖：簇狀條形圖、堆積條形圖、百分比堆積條形圖面積圖：普通面積圖、堆積面積圖、百分比堆積面積圖散點圖：僅帶數(shù)據(jù)標記的散點圖、帶平滑線和數(shù)據(jù)標記的散點圖…氣泡圖：二維、三維氣泡圖雷達圖：普通雷達圖、帶數(shù)據(jù)標記的雷達圖、填充雷達圖第15頁/共54頁1、表格當需要呈現(xiàn)的數(shù)據(jù)在3個系列以上時，用圖形表示有點繁瑣且不容易看清楚，這時用表格是一個良好的選擇。你所不知道的表格的那些事突出顯示單元格Step1：選中列或者行Step2：“開始”“樣式”中的“條件格式”Step3：選中“突出顯示單元格規(guī)則”進行設(shè)置項目選取Step1：選中列或者行Step2：“開始”“樣式”中的“條件格式”Step3：選中“項目選取規(guī)則”進行設(shè)置第16頁/共54頁你所不知道的表格的那些事（續(xù)）數(shù)據(jù)條數(shù)據(jù)條的長度代表單元格的值大小。Step1：選中表中的列或行Step2：“開始”“樣式”中的“條件格式”Step3：選中“數(shù)據(jù)條”進行顏色的選擇圖標集按照閾值進行分類別，每個圖表代表一個范圍的值Step1：選中表中的列或行Step2：“開始”“樣式”中的“條件格式”Step3：選中“圖標集”進行圖表的選擇迷你圖它是工作表單元格中的一個微型圖表，可提供對數(shù)據(jù)的形象表示。Step1：選定要制作迷你圖的單元格Step2：單擊“插入”，在“迷你圖”組中選擇折線圖或者柱形圖第17頁/共54頁第18頁/共54頁2、柱形圖柱形圖用來表示一段時間內(nèi)數(shù)據(jù)的變化或者描述各項目之間數(shù)據(jù)比較。它分為簇狀柱形圖、堆積柱形圖、百分比堆積柱形圖。其表現(xiàn)形式有二維柱形圖、三維柱形圖、圓柱圖、圓錐圖和棱錐圖，其本質(zhì)沒有多大差別，僅僅是柱形的形狀各不相同。簇狀柱形圖：第19頁/共54頁堆積柱形圖百分比堆積柱形圖第20頁/共54頁3、折線圖折線圖是顯示數(shù)據(jù)的變化趨勢，它強調(diào)的時間性和變動率?？煞譃檎劬€圖、堆積折線圖以及百分比堆積折線圖。按照有數(shù)據(jù)標記的可分為帶數(shù)據(jù)標記的折線圖、帶數(shù)據(jù)標記的堆積折線圖以及帶數(shù)據(jù)標記的百分比堆積折線圖。折線圖：第21頁/共54頁帶數(shù)據(jù)標記的折線圖第22頁/共54頁4、餅圖餅圖是以一個圓形分割成多個部分并填充不同的顏色或圖案，可以非常直觀的顯示出數(shù)據(jù)占總數(shù)的百分比情況。普通的餅圖一般只能顯示一個序系列的數(shù)據(jù)比例關(guān)系，若有多個系列，則只會顯示其中的一個系列。一般餅圖只顯示五個部分，若多于五個，則利用復(fù)合餅圖或者復(fù)合條餅圖。餅圖：第23頁/共54頁復(fù)合餅圖第24頁/共54頁5、條形圖條形圖是顯示個項目之間的對比，主要用于表現(xiàn)個項目之間的數(shù)據(jù)差額。與柱形圖類似。可分為簇狀條形圖、堆積條形圖、百分比堆積條形圖。其表現(xiàn)形式有二維條形圖、三維條形圖、圓柱圖、圓錐圖和棱錐圖，其本質(zhì)沒有多大差別，僅僅是條形的形狀各不相同簇狀條形圖第25頁/共54頁堆積條形圖百分比堆積圖第26頁/共54頁6、面積圖面積圖是顯示每個數(shù)據(jù)的變化量，強調(diào)數(shù)據(jù)隨時間變化的幅度。面積圖第27頁/共54頁堆積面積圖百分比堆積面積圖第28頁/共54頁7、散點圖用于展示成對的數(shù)據(jù)之間的關(guān)系。將每一對數(shù)字鐘的第一個數(shù)字繪制在垂直軸上，將另一個數(shù)字繪制在水平軸上，常用于繪制函數(shù)圖象。散點圖：第29頁/共54頁帶平滑線和數(shù)據(jù)標記的散點圖第30頁/共54頁8、氣泡圖氣泡圖是散點圖的一種特殊類型，它在散點的基礎(chǔ)上附加了數(shù)據(jù)系列。氣泡圖：第31頁/共54頁9、雷達圖雷達圖是用于顯示數(shù)據(jù)系列相對于中心點以及相對于彼此數(shù)據(jù)類別間的變化，它的每一個分類擁有自己的坐標軸，這些坐標軸由中心向外輻射，同一系列中的數(shù)據(jù)值由折線連接起來。常用于多重數(shù)據(jù)比較。雷達圖：第32頁/共54頁幾種稍復(fù)雜圖表的繪制與圖表美化1、平均線圖平均線圖是對比分析的典型圖例。Excel繪制法：Step1：準備數(shù)據(jù)，增加一列平均值；Step2：選中數(shù)據(jù)插入柱形圖；Step3：將“平均值”系列轉(zhuǎn)化為折線圖，單擊右鍵，選擇“更改系列圖表類型”，把柱形圖改為折線圖。Step4：圖表美化，設(shè)置坐標標題與圖表標題。第33頁/共54頁2、雙坐標圖在圖表中有兩個及其以上的系列時，并且它們的量綱不同或者數(shù)據(jù)差異較大時，在同一縱坐標軸下就無法很好的展現(xiàn)出數(shù)據(jù)原本的面貌，可采用雙坐標圖來繪制。(1)柱形+折線繪制方法：Step1：選擇數(shù)據(jù)繪制柱形圖；Step2：單擊任一“收入”柱子，單擊鼠標右鍵設(shè)置數(shù)據(jù)系列格式，在“系列繪制在”選項下，單擊“次坐標軸”；Step3：選擇“收入”柱子，單擊右鍵，選擇“更改系列圖表類型”，選擇“帶數(shù)據(jù)標記的折線圖”；Step4：在“布局”標簽內(nèi)的坐標軸標簽內(nèi)設(shè)置左右坐標軸的說明。第34頁/共54頁(2)雙柱形圖使用占位法繪制Step1：添加兩個占位數(shù)據(jù)列；Step2：制作柱形圖；Step3：設(shè)置最右邊的兩個序列為次坐標軸；Step4：改占位數(shù)據(jù)全部為0。第35頁/共54頁3、豎形折線圖豎形折線圖主要在市場研究、咨詢等公司使用較廣泛，用它來展示產(chǎn)品功能、品牌形象等在消費者心中的評價，可用于分析與比較多個不同產(chǎn)品、項目在每個指標方面的表現(xiàn)，得出它們在每個指標屬性偏向。繪制方法：Step1：選擇需要統(tǒng)計的兩列數(shù)據(jù)作“帶平滑線和數(shù)據(jù)標記的散點圖”；Step2：分別添加輔助系列1與輔助系列2；Step3：利用JwalkChartTools工具給輔助列1和2添加標簽；Step4：把輔助列隱藏，同時把橫縱坐標也隱藏；Step5：刪除圖例和網(wǎng)格線。第36頁/共54頁4、瀑布圖用于表示企業(yè)成本構(gòu)成、變化等情況。利用占位法繪制。Step1：設(shè)置占位數(shù)；Step2：選中數(shù)據(jù)插入“堆積柱形圖”；Step3：隱藏占位系列；Step4：隱藏縱坐標軸；Step5：添加數(shù)據(jù)標簽。第37頁/共54頁5、排列圖（帕累托圖）排列圖是按照發(fā)生頻率的高低順序繪制的直方圖。二八法則：事物的80%都是次要的，其余20%是最重要的。排列圖的特點：1)：按數(shù)值降序排列的柱形圖，折線圖上的數(shù)據(jù)是累計百分比數(shù)據(jù)；2)：折線圖從0開始，并且位于第一個柱子上的最下角；3)：折線圖的第二個點位于柱形圖的第一個柱子的最右上角；4)：折線圖的最后一個數(shù)值為100%，位于圖的最右上角。繪制方法：Step1：準備數(shù)據(jù)，算好累計數(shù)與累計百分比；Step2：插入“簇狀柱形圖”；Step3：更改為次坐標軸，更改為“帶數(shù)據(jù)標記的折線圖”；Step4：設(shè)置次要橫坐標軸“顯示從左到右坐標軸”，然后設(shè)置位置坐標軸在刻度線上并隱藏次要橫坐標軸；Step5：設(shè)置次從坐標軸最大值和柱形圖間隔；Step6：設(shè)置主要縱坐標軸最大值，添加折線圖數(shù)據(jù)標簽，刪除圖例和網(wǎng)格線。第38頁/共54頁排列圖實例：第39頁/共54頁6、成對條形圖（旋風(fēng)圖）旋風(fēng)圖應(yīng)用的主要情形：同一事物在某個活動、行為影響前后不同指標的變化；兩個類別之間不同指標的比較；同一事物在某個條件變化下，另一個指標也受影響隨之變化。繪制方法：Step1：插入“簇狀條形圖”；Step2：更改為“次坐標軸”；Step3：設(shè)置兩個橫坐標軸最大值與最小值，并設(shè)置坐標軸格式代碼為“0;0;0”，顯示非負數(shù)；Step4：逆序上方的橫坐標軸，并隱藏上方的橫坐標軸；Step5：設(shè)置縱坐標軸，設(shè)置坐標軸標簽為“低”；Step6：添加“數(shù)據(jù)標簽”，刪除網(wǎng)格線。第40頁/共54頁成對條形圖示例第41頁/共54頁7、漏斗圖繪制方法：Step1：選擇需要表示的數(shù)據(jù)列，插入“堆積條形圖”；Step2：逆序縱坐標軸；Step3：顛倒系列次序；Step4：隱藏占位序列；Step5：刪除圖例和網(wǎng)格線。40%75%67%85%第42頁/共54頁8、矩陣圖繪制方法：Step1：插入“僅帶數(shù)據(jù)標記的散點圖”；Step2：設(shè)置橫縱坐標軸分別與縱軸和橫軸的交叉點值為平均值；Step3：設(shè)置橫軸和縱軸的最大值和最小值，并隱藏橫軸和縱軸的坐標標簽；Step4：利用工具添加標簽；Step5：插入箭頭和標簽說明；Step6：對象限內(nèi)的點進行顏色設(shè)置。低高重要性滿意度高第43頁/共54頁數(shù)據(jù)處理與分析案例精解企業(yè)年度效益分析與客戶管理第44頁/共54頁設(shè)計思路分析在企業(yè)經(jīng)營過程中，需要對企業(yè)的效益進行分析，分析企業(yè)產(chǎn)品的供求關(guān)系，并對企業(yè)客戶進行有效的管理，才能使得企業(yè)的銷售業(yè)績不斷攀升。因此，需要對企業(yè)的產(chǎn)品、客戶進行深入的分析，大力開發(fā)具有競爭力、市場需求量大的產(chǎn)品，另外需要培育和維護企業(yè)大客戶的戰(zhàn)略合作關(guān)系。(1)、分別按照客戶和產(chǎn)品匯總銷售數(shù)據(jù)，匯總季度數(shù)據(jù)和年度銷售數(shù)據(jù)；(2)、分析年度效益，使用圖表