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

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

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

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

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