EXCEL分析暑期實訓報告

成果形式：實踐報告成果名稱：EXCEL分析EXCEL分析暑期實訓報告一、實踐目的本次實訓的主要目的就是為了彌補學校對我們在商務數(shù)據(jù)分析方面的教育短板，通過這次培訓加強自己對基本商務數(shù)據(jù)分析的理解，充分感受到商務數(shù)據(jù)分析所需要的基本知識和技能，并且能了解到商務數(shù)據(jù)分析的發(fā)展及應用前景，從而對自己專業(yè)以后的就業(yè)情況進行更一步了解。實訓也是對我們每一個人專業(yè)知識的一種檢驗，它可以讓我們學到更多在課堂上學不到的知識，既開闊了視野又增長了見識，為我們以后進一步走向社會打下堅實的基礎，這是我們走向工作崗位的第一步。二、實踐內(nèi)容數(shù)據(jù)處理在于數(shù)據(jù)分析中是最重要的一步，其相關的工作時間占據(jù)了一個項目的70%以上。數(shù)據(jù)的質(zhì)量，直接決定了模型的預測和泛化能力的好壞。它涉及很多因素，包括：準確性、完整性、一致性、時效性、可信性和解釋性。而在真實數(shù)據(jù)中，我們拿到的數(shù)據(jù)可能包含了大量的缺失值，可能包含大量的噪音，也可能因為人工錄入錯誤導致有異常點存在，非常不利于算法模型的訓練。數(shù)據(jù)清洗的結果是對各種臟數(shù)據(jù)進行對應方式的處理，得到標準的、干凈的、連續(xù)的數(shù)據(jù)，提供給數(shù)據(jù)統(tǒng)計、數(shù)據(jù)挖掘等使用。數(shù)據(jù)預處理的主要步驟分為：獲取數(shù)據(jù)、清洗數(shù)據(jù)、數(shù)據(jù)抽取及合并、計算數(shù)據(jù)、數(shù)據(jù)類型轉換、數(shù)據(jù)分析和數(shù)據(jù)展示（圖表）。實訓過程中也是從這幾個方面進行詳細介紹及具體的方法，如果在一個項目中，在這幾個方面的數(shù)據(jù)處理做的還不錯的話，那對于之后的工作就有了更大的幫助了，并且還能快速達到一個還不錯的結果。三、實踐過程第一步：獲取數(shù)據(jù)方法一為手動輸入數(shù)據(jù)，數(shù)據(jù)輸入的類型有文本，數(shù)值，日期；文本的輸入：直接輸入；數(shù)值的輸入：直接輸入；較長數(shù)值的輸入：英文單引號+數(shù)值（例：’61042319xxxxxxxxxx）;日期的輸入：年月日的順序，年月日之間用斜桿分隔（例：2019/08/12）;方法二為導入來自其他類型文件的數(shù)據(jù)若導入文本文檔：第一步：設置語言格式第二步：設置分隔符第三步：設置各列數(shù)據(jù)格式數(shù)據(jù)庫數(shù)據(jù)網(wǎng)站數(shù)據(jù)第二步：清洗數(shù)據(jù)1、重復數(shù)據(jù)的處理方法一為菜單刪除法（刪除重復值）方法二為標識法（標識值）方法三為高級篩選法（把重復值提取出來，放到其他位置）方法四為countif統(tǒng)計個數(shù)方法五為數(shù)據(jù)透視表2、缺失值的處理（1）、多個單元格中輸入同一個值的方法：選中多個單元格——輸入值——按ctrl+回車（2）、選中多個不連續(xù)空單元格的方法：開始——選擇——定位條件——空值3、空白值的處理方法1：替換方法2：函數(shù)：trim（）第三步、數(shù)據(jù)抽取及合并1、 數(shù)據(jù)抽取數(shù)據(jù)抽取是從數(shù)據(jù)源中抽取數(shù)據(jù)的過程，其分為字段拆分和記錄隨機抽?。?）、字段拆分方法一：菜單法步驟：選中列——數(shù)據(jù)——分列第一步：設置固定寬度第二步：設置分列線第三步：設置各列類型，選擇目標位置方法二：函數(shù)法Mid（）從字符串中，指定位置起，返回指定長度的字符Left（）從字符串中，第一個字符開始，返回指定長度的字符Right（）從字符串中，最后一個字符開始，返回指定長度的字符（2）、記錄隨機抽?、?、生成隨機數(shù)rand（）②、對隨機數(shù)排序rank（）③、把排序結果中，前200名數(shù)據(jù)提取出來Vlookup（）搜索提取函數(shù)第一空：要搜索的值第二空：搜索區(qū)域注意：要絕對引用第三空：要返回的值所在的列編號第四空：false（精確匹配）/true（大致匹配）Rank（）排序函數(shù)第一空：要排序的數(shù)第二空：區(qū)域（要絕對引用）第三空：0（降序）/1（升序）2、 數(shù)據(jù)合并（1）、列的合并方法一：函數(shù)concatenate（）方法二：連接符&方法三：日期連接函數(shù)date（）例：date（2019,8,13）方法四：條件匹配vlookupVlookup的執(zhí)行原理，從區(qū)域（第二個空）的首列，搜索第一個空的值Vlookup（1個號碼，區(qū)域，列號，false）Ppt中的多條件匹配，使用了excel數(shù)組運算數(shù)組運算執(zhí)行方法：ctrl+shift+回車第四步：數(shù)據(jù)計算1、 簡單計算：加（+）減（－）乘（*）除（/）2、 日期計算：datedif（起始日期，終止日期，格式）作用：計算起始和終止日期之間所經(jīng)歷的時長時長格式：y：以年顯示m：以月顯示d：以日顯示Year（）提取日期中的年份Month（）提取日期中的月Day（）提取日期中的日Today（）返回當前系統(tǒng)日期3、 標準化計算：使一些數(shù)據(jù)的異常值，落入到正常的0-1的區(qū)間計算方法：x標準化＝（x－最小值）/（最大值－最小值）Max（）求最大值Min（）求最小值4、 加權求和：通過加權，讓數(shù)據(jù)的占比均化Sumproduct（）實質(zhì)：各數(shù)據(jù)乘以權重，并求各乘積的和5、 分組［0,50）［50,100）［100,∞）方法一：if函數(shù)分組If（條件判斷，條件為真時的結果，條件為假時的結果）方法二：vlookup函數(shù)模糊匹配分組操作：vlookup函數(shù)的第四個空填‘true’第五步、數(shù)據(jù)類型轉換1、 行/列轉換操作：復制數(shù)據(jù)區(qū)域——到目標位置——右擊——選擇性粘貼對話框中，選擇‘轉置’2、 文本到數(shù)值方法一：分列操作：選中數(shù)據(jù)區(qū)域——數(shù)據(jù)——分列——分列第三步選擇‘常規(guī)’類型方法二：選擇性粘貼——運算操作：先復制‘1’——選中數(shù)據(jù)——選擇粘貼——運算方法三：智能標記3、 數(shù)值——文本方法一：分列分列二：函數(shù)text（數(shù)值，‘格式’）常用格式：小數(shù)（0.00）百分比（0.0%）日期（00年00月00日）4、 數(shù)值——日期方法一：分列5、 二維表轉換為一維表方法：使用數(shù)據(jù)透視表制作向導操作：1、ALT+D+P（打開向導對話框）2、多重合并數(shù)據(jù)區(qū)域3、自定義頁字段4、選擇數(shù)據(jù)區(qū)域5、雙擊數(shù)據(jù)透視表的總計值（可把二維轉一維）第六步：數(shù)據(jù)分析1、 對比分析環(huán)比：同一年中，上個月和下個月的數(shù)據(jù)比較（3月－2月）/2月同比：兩年中，同一時間的比較（2012年－2011年）/2011年★注意：數(shù)據(jù)透視表中，計算環(huán)比：選擇‘值顯示方式’為差異百分比，選擇字段為‘注冊時間’，基本項為‘上一個’。計算同比：選擇‘值顯示方式’為差異百分比，選擇字段為‘年’，基本項為‘上一個’。2、結構分析（占比分析）占比：一個項目中，各決定因素所在的比例計算方法：透視表中，值顯示方式——列匯總百分比/行匯總百分比3、分布分析：根據(jù)分析的目的，將數(shù)值型數(shù)據(jù)進行等距或不等距的分組分組：相應值落入到各組中的比率方法一：vlookup方法分組特點：可以進行不等距分組操作：用vlookup分組后，用數(shù)據(jù)透視表統(tǒng)計分組結果方法二：數(shù)據(jù)透視表分組特點：只能進行等距分組操作：直接用數(shù)據(jù)透視表的分組功能完成即可例：0-60不及格60-75及格75-90良好90-100優(yōu)秀4、交叉分析目的：從兩個維度對我們的客戶進行分類操作：（1）、用vlookup確定各客戶，兩個維度的性質(zhì)（2）、用數(shù)據(jù)透視表，從兩個維度進行分類，來統(tǒng)計各分類的客戶人數(shù)（3）、從一維表的角度，查看客戶分類情況的操作（1.以表格形式顯示2.重復所有標簽3.不顯示匯總結果）5、矩陣分析操作：1、先進行定性分組，對數(shù)據(jù)進行平均值的計算平均值的計算方法：在透視表的計算區(qū)域上，右擊——值字段設置——平均值2、復制透視表數(shù)據(jù)到新的位置注意：粘貼數(shù)據(jù)時，用‘選擇性粘貼’中的值3、制作散點（矩陣）圖選中月平均消費和月平均流量的值——插入——散點圖注意：不選行/列標簽及總計4、散點圖x/y坐標軸，移動到平均值交叉位置方法：在坐標軸上右擊——坐標軸格式，設置即可5、重新繪制x/y的坐標軸6、給散點添加標簽（1）、在點上右擊——添加標簽（2）、選中標簽——右擊——設置標簽格式——單元格中的值——選擇文字性的標簽——取消y值6、多表關聯(lián)分析：通過地區(qū)和通訊品牌兩個維度，統(tǒng)計消費用戶數(shù)操作：1、分析兩個表之間為何的連接字段（列）注意：連接字段，就是兩個表的公共列（例如：省份）2、把兩個表添加到模板中方法：插入——表格3、插入——透視表——選擇多個表4、進行多表連接方法：透視表工具——分析——關系5、數(shù)據(jù)分析即可案例：各手機品牌是否開通微信的用戶數(shù)（1）、手機品牌，微信公共列為：號碼（2）、用透視表做關聯(lián)①、把表放到模板中②、插入——透視表——添加多個表案例：各地區(qū)是否開通微信的用戶數(shù)地區(qū)：地區(qū)微信：號碼表７、RFM分析（從三個角度對用戶進行分類）R：最近一次的消費時間（時長）F：最近的消費次數(shù)M：消費額度步驟：1.計算R.F.M的值（用數(shù)據(jù)透視表完成）R：日期最大值F：訂單ID計數(shù)M：金額求平均值用客戶ID定性分組2.把上面透視表結果復制到新表中（粘貼時，使用選擇性粘貼的‘值’）注意：把R用datedif換算成天數(shù)3.對R.F.M評分4.用透視表，對評分結果做分析第七步：數(shù)據(jù)展現(xiàn)EXCEL圖表：餅圖：占比成分操作1：圖表各對象格式設置方法：在對象上右擊——選擇相應操作操作2：圖表布局操作3：圖表設計（數(shù)據(jù)選擇，圖表類型更改）制作圖表方法：選中做圖表的數(shù)據(jù)——插入——圖表類型——進行圖表設置1、 雙坐標圖表用途：把量級差別較大或單位不同的數(shù)據(jù)，在一個圖表上表示步驟：（1）、制作圖表（柱形圖）（2）、把較小單位的值，用次坐標軸表示方法：選中圖表——右擊——設置格式——次坐標軸（3）、修改次坐標軸，圖表類型為折線圖格式設置：①、文字大小，方向②、坐標軸的刻度③、隱藏次坐標軸圖例一2、 目標完成率圖作用：反映業(yè)務目標的完成情況操作：類似雙坐標軸操作★注意：把完成值繪制在次坐標軸上格式設置：（1）、系列圖形的填充色，線條色，系列間隙寬度（2）、隱藏次坐標軸（3）、給完成值添加完成率的數(shù)據(jù)標簽圖例二3、雷達圖作用：系列有2組以上數(shù)據(jù)時，用該圖操作：選中數(shù)據(jù)——插入——雷達圖格式設置：系列寬度設置在1以下圖例三4、矩陣圖作用：用兩組相關數(shù)據(jù)，對客戶進行分類操作：（1）、選擇數(shù)據(jù)（不選行/列標題和平均值）（2）、給每個點添加標簽（行標題）（3）、移動x/y坐標軸到平均值位置（4）、重新繪制x/y坐標圖例四5、迷你圖作用：當數(shù)據(jù)系列比較多時，快速查看每個系列的趨勢或變化情況操作方法：光標放在迷你圖位置——插入——迷你圖——選擇類型設置內(nèi)容：（1）、圖表樣式（2）、設置高點，低點圖例五6、漏斗圖作用：一般用來表示，一個商業(yè)行為的變化過程例如：購物（瀏覽產(chǎn)品——放入購物車——下單——支付——完成）操作：選中數(shù)據(jù)——插入——堆積條形圖注意：（1）、逆序系列標簽方法：選中坐標軸——右擊——設置格式——逆序類別（2）、添加占位數(shù)據(jù)方法：在圖表上右擊——添加數(shù)據(jù)注意：把占位數(shù)據(jù)放到系列數(shù)據(jù)的前面（3）、把占位數(shù)據(jù)的圖形，填充和線條都設置為無（4）、形成封閉的漏斗方法：圖表工具——設計——添加元素——線條——系列線圖例六7、旋風圖作用：展現(xiàn)不同數(shù)據(jù)在同一組指標下比較結果操作：選中數(shù)據(jù)——插入——堆積條形圖注意：（1）、繪制其中一組數(shù)據(jù)到次坐標軸（2）、修改主次坐標軸的刻度最小值：負數(shù)最大值：負數(shù)絕對值（3）、將次坐標軸刻度進行逆序方法：在次坐標軸上右擊——逆序刻度值格式設置：（1）、將坐標軸標簽移動到左側方法：設置標簽位置為‘低’（2）、隱藏次坐標軸方法：設置次坐標軸標簽為‘無’（3）、修改主坐標軸的數(shù)字格式為‘0：0：0’