EXCEL分析暑期實(shí)訓(xùn)報(bào)告

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