實(shí)踐報(bào)告EXCEL數(shù)據(jù)分析

成果形式：實(shí)踐報(bào)告EXCEL數(shù)據(jù)分析EXCEL數(shù)據(jù)分析實(shí)踐目的1、彌補(bǔ)高校在商務(wù)數(shù)據(jù)分析方面的短板，此次培訓(xùn)通過基本商務(wù)數(shù)據(jù)分析的講解，讓高校學(xué)生充分感受到商務(wù)數(shù)據(jù)分析所需要的基本知識(shí)和技能2、讓學(xué)生了解到目前商業(yè)數(shù)據(jù)的發(fā)展及應(yīng)用場(chǎng)景，對(duì)自己的職業(yè)定位有個(gè)更加清晰的認(rèn)識(shí)。實(shí)踐內(nèi)容EXCEL數(shù)據(jù)預(yù)處理EXCEL數(shù)據(jù)計(jì)算EXCEL數(shù)據(jù)分析EXCEL圖表數(shù)據(jù)展示EXCEL分析實(shí)戰(zhàn)實(shí)踐過程EXCEL數(shù)據(jù)預(yù)處理獲取數(shù)據(jù)（一）數(shù)據(jù)分析工作表的格式規(guī)則由行和列組成：一行稱為一條記錄，一列稱為一個(gè)屬性(字段),列名就是字段名，數(shù)據(jù)表的所有記錄不能重復(fù)，不允許出現(xiàn)合并單元格的結(jié)構(gòu)（列）。（二）獲取數(shù)據(jù)的方法方法1.手動(dòng)輸入數(shù)據(jù)的方法：數(shù)據(jù)是的類型:文本，數(shù)值，日期；文本的輸入：直接輸入；數(shù)值的輸入；較長的數(shù)值：英文單引號(hào)+數(shù)值；日期的輸入：年月日的順序，年月日之間用斜桿分隔。方法2.導(dǎo)入來自其它類型文件的數(shù)據(jù)：文本文檔：第1步，設(shè)置語言格式；第2步，設(shè)置分隔符；第3步，設(shè)置各列數(shù)據(jù)格式。數(shù)據(jù)清洗重復(fù)數(shù)據(jù)的處理1、菜單刪除法（刪除重復(fù)值）選中所有數(shù)據(jù)--數(shù)據(jù)--數(shù)據(jù)工具--刪除重復(fù)值2、標(biāo)識(shí)法（標(biāo)識(shí)值）選中數(shù)據(jù)(號(hào)碼)--開始--條件格式--突出顯示單元格規(guī)則--重復(fù)值。告訴了哪些號(hào)碼重復(fù)但是沒有去掉重復(fù)值。3、高級(jí)篩選法(把重復(fù)值取出來，放到其他位置)選中數(shù)據(jù)(號(hào)碼)--數(shù)據(jù)--排序和篩選--篩選(高級(jí))將篩選結(jié)果復(fù)制到其他位置---選擇不重復(fù)的記錄--復(fù)制到(篩選后結(jié)果顯示)。4、數(shù)據(jù)透視表（統(tǒng)計(jì)重復(fù)次數(shù)）選中所有數(shù)據(jù)--插入--數(shù)據(jù)透視表(默認(rèn)設(shè)置)--號(hào)碼放到行號(hào)碼放到值--得到號(hào)碼的統(tǒng)計(jì)數(shù)據(jù)--選擇一個(gè)右鍵--排序?？梢缘玫脚判虻慕Y(jié)果。缺失值的處理多個(gè)單元格中輸入同一個(gè)值的方法：選中多個(gè)單元格--輸入值--按ctrl+回車。選中多個(gè)不連續(xù)空單元格的方法：開始--選擇--定位條件--空值?？瞻字档奶幚矸椒?：替換方法2：函數(shù)：trim（）EXCEL數(shù)據(jù)抽取數(shù)據(jù)抽取字段拆分方法1：菜單法步驟：選中列--數(shù)據(jù)--分列第1步，設(shè)置固定寬度；第2步，設(shè)置分列線；第3步，設(shè)置各列類型，選擇目標(biāo)位置。方法2：函數(shù)法mid():從字符串中，指定位置起，返回指定長度的字符。left():從字符串，第1個(gè)字符開始，返回指定長度的字符。right():從字符串，最后1個(gè)字符開始，返回指定長度的字符。記錄隨機(jī)抽取1.生成隨機(jī)數(shù):rand()2.對(duì)隨機(jī)數(shù)排序:rank()3.把排序結(jié)果中，前200名數(shù)據(jù)提取出來:vlookup()vlookup()搜索提取函數(shù)第1空：要搜索的值；第2空：搜索區(qū)域（在哪里搜索）；第3空：要返回的值所在的列編號(hào)；第4空：false(精確匹配)/true(大致匹配)。rank()：排序函數(shù)第1空：要排序的數(shù)；第2空：區(qū)域（要絕對(duì)引用）；第3空：0(降序)/1(升序)。EXCEL數(shù)據(jù)計(jì)算數(shù)據(jù)合并一．字段匹配1、單條件匹配：vlookup第一個(gè)參數(shù)號(hào)碼表--A2元素第二個(gè)參數(shù)：匹配區(qū)域第三個(gè)參數(shù)：返回?cái)?shù)據(jù)對(duì)應(yīng)查找數(shù)據(jù)表第四個(gè)參數(shù)：精確匹配0，模糊匹配1=VLOOKUP()2、多條件匹配：vlookupvlookup的執(zhí)行原理：從區(qū)域(第2個(gè)空)的首列，搜索第1個(gè)空的值，vlookup(1個(gè)號(hào)碼，區(qū)域,列號(hào)，false)。ppt中的多條件匹配，使用了excle數(shù)組運(yùn)算。數(shù)組運(yùn)算執(zhí)行方法：CTRL+SHIFT+回車。二．?dāng)?shù)據(jù)計(jì)算簡單計(jì)算如：+-*/日期計(jì)算datedif(起始日期，終止日期，格式)作用：計(jì)算起始和終止日期之間所經(jīng)歷的時(shí)長。時(shí)長格式：y：以年顯示m：以月顯示d：以日顯示year()：提取日期中的年份month()：提取日期中的月day()：提取日期中的日today()：返回當(dāng)前系統(tǒng)日期標(biāo)準(zhǔn)化計(jì)算（使一些數(shù)據(jù)的異常值，落入到正常的0-1的區(qū)間）計(jì)算方法：x標(biāo)準(zhǔn)化=(x-最小值)/(最大值-最小值)max()：求最大值min()：求最小值加權(quán)求和（通過加權(quán)，讓數(shù)據(jù)的占比均化）函數(shù)法：Sumproduct()實(shí)質(zhì)：各數(shù)據(jù)乘以權(quán)重，并求各乘積的和數(shù)據(jù)分組方法1：IF函數(shù)--IF(條件,滿足條件結(jié)果,不滿足條件結(jié)果)方法2：vlookup函數(shù)模糊匹配分組操作：vlookup函數(shù)的第4個(gè)空填‘true’。EXCEL數(shù)據(jù)分析一．?dāng)?shù)據(jù)類型轉(zhuǎn)換（一)行/列轉(zhuǎn)換操作：復(fù)制數(shù)據(jù)區(qū)域--到目標(biāo)位置--右擊--選擇性粘貼對(duì)話框中，選擇‘轉(zhuǎn)置’。（二）文本到數(shù)值方法1：分列操作：選中數(shù)據(jù)區(qū)域--數(shù)據(jù)--分列--分列第3步選擇‘常規(guī)’類型。方法2：選擇性粘貼--運(yùn)算 操作：先復(fù)制‘1’--選中數(shù)據(jù)--選擇粘貼--運(yùn)算。方法3：智能標(biāo)記（三）數(shù)值到文本方法1：分列方法2：函數(shù)：text(數(shù)值,'格式')常用格式：小數(shù)（0.00）百分比(0.0%)日期（00年00月00日）（四）數(shù)值到日期方法：分列（五）二維表轉(zhuǎn)換為一維表方法：使用數(shù)據(jù)透視表制作向?qū)Р僮鳎?.ALT+D+P(打開向?qū)?duì)話框)； 2.多重合并數(shù)據(jù)區(qū)域； 3.自定義頁字段； 4.選擇數(shù)據(jù)區(qū)域； 5.雙擊數(shù)據(jù)透視表的總計(jì)值(可以把二維轉(zhuǎn)一維）。二．?dāng)?shù)據(jù)分析（一）對(duì)比分析（日期，時(shí)間分組）1、環(huán)比：同一年中，上個(gè)月和下個(gè)月的數(shù)據(jù)比較，如(3月-2月)/2月。2、同比：兩年中，同一時(shí)間的比較，如(2012年-2011年)/2011年。注意：數(shù)據(jù)透視表中，計(jì)算環(huán)比：選擇‘值顯示方式’為差異百分比。選擇字段為“注冊(cè)時(shí)間”，基本項(xiàng)為“上一個(gè)”；數(shù)據(jù)透視表中，計(jì)算同比：選擇‘值顯示方式’為差異百分比。選擇字段為“年”，基本項(xiàng)為“上一個(gè)”。（二)結(jié)構(gòu)分析（占比分析） 占比計(jì)算：一個(gè)項(xiàng)目中，各決定因素所在的比例結(jié)構(gòu)占比分析（定性分組）（三）分布分析方法1：vlookup方法分組特點(diǎn)：可以進(jìn)行不等距分組。操作：用vlookup分組后，用數(shù)據(jù)透視表統(tǒng)計(jì)分組結(jié)果。方法2：數(shù)據(jù)透視表分組特點(diǎn)：只能進(jìn)行等距分組。操作：直接用數(shù)據(jù)透視表的分組功能完成即可。（四）交叉分析(將消費(fèi)人群進(jìn)行分類)1、目的：從兩個(gè)維度對(duì)我們的客戶進(jìn)行分類。2、操作步驟：a.用vlookup確定各客戶，兩個(gè)維度的性質(zhì)。b.用數(shù)據(jù)透視表，從兩個(gè)維度進(jìn)行分類，來統(tǒng)計(jì)各分類的客戶人數(shù)。c.從一維表的角度，查看客戶分類情況的操作（1.以表格形式顯示；2.重復(fù)所有標(biāo)簽；3.不顯示匯總結(jié)果）。數(shù)據(jù)分析（一）矩陣分析(將消費(fèi)人群進(jìn)行分類)操作步驟：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)簽。a.在點(diǎn)上右擊--添加標(biāo)簽。b.選中標(biāo)簽--右擊--設(shè)置標(biāo)簽格式--單元格中的值--選擇文字性的標(biāo)簽--取消Y值。（二）多表關(guān)聯(lián)分析通過地區(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)；a.把表放到模板中。b.插入--透視表--添加多個(gè)表。案例：各地區(qū)是否開通微信的用戶數(shù)。地區(qū)：地區(qū)。微信：號(hào)碼表。（七）RFM分析(從三個(gè)角度對(duì)用戶進(jìn)行分類)R:最近一次的消費(fèi)時(shí)間（時(shí)長）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、餅圖：占比成分操作1:圖表各對(duì)象格式設(shè)置（方法：在對(duì)象上右擊--選擇相應(yīng)操作。）；操作2:圖表布局；操作3:圖表設(shè)計(jì)(數(shù)據(jù)選擇,圖表類型更改)。制作圖表方法：選中做圖表的數(shù)據(jù)--插入--圖表類型--進(jìn)行圖表設(shè)置。2、坐標(biāo)軸圖表用途：數(shù)據(jù)類別為兩個(gè)（單位不同或者量差別較大）操作步驟：1、制作圖表(柱形圖)；2、把較小單位的值，用次坐標(biāo)軸表示；方法：選中圖表--右擊--設(shè)置格式--次坐標(biāo)軸3、修改次坐標(biāo)軸，圖表類型為折線圖格式設(shè)置：1、文字大小，方向；2、坐標(biāo)軸的刻度；3、隱藏次坐標(biāo)軸。EXCEL數(shù)據(jù)展現(xiàn)數(shù)據(jù)展現(xiàn)excel圖表工具1）雙坐標(biāo)軸圖作用：把量級(jí)差別較大或單位不同的數(shù)據(jù)，在一個(gè)圖表上表示。操作：選中數(shù)據(jù)--插入--柱形圖。設(shè)置內(nèi)容：1.文字格式；2.繪制自坐標(biāo)軸；3.更改圖表類型；4.添加標(biāo)簽；5.隱藏次坐標(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)漏斗圖作用：一般用來表示，一個(gè)商業(yè)行為的變化過程。例如：購物（瀏覽產(chǎn)品--放入購物車--下單--支付--完成）。操作：選中數(shù)據(jù)--插入--堆積條形圖。注意：1.逆序系列標(biāo)簽(方法：選中縱坐標(biāo)軸--右擊--設(shè)置格式--逆序類別)。2.添加占位數(shù)據(jù)(方法：在圖表上右擊--添加數(shù)據(jù))。注意：把占位數(shù)據(jù)放到系列數(shù)據(jù)的前面。3.把占位數(shù)據(jù)的圖形，填充和線條都設(shè)置為‘無’；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)簽為‘無’）；3.修改主坐標(biāo)軸的數(shù)字格式為‘0:0:0’（方法：數(shù)字--自定義）。8)帕累托圖（28原則）作用：分析出現(xiàn)問題后，原因的定位分析。操作：選擇數(shù)據(jù)--插入--柱形圖。設(shè)置:1.設(shè)置柱形圖系列間距為最??；2.設(shè)置主坐標(biāo)軸刻度最大值為總問題數(shù)； 3.添加問題百分比數(shù)據(jù)到次坐標(biāo)軸上,設(shè)置次坐標(biāo)軸最大值為100%；4.添加次橫坐標(biāo)軸(方法：圖表工具--設(shè)計(jì)--添加元素--坐標(biāo)軸--次橫坐標(biāo)軸)；5.設(shè)置次橫坐標(biāo)軸的位置為：刻度線上。EXCEL案例分析案例1：1）同比;2）環(huán)比;3）環(huán)比發(fā)展速度=本月/上月;4）平均環(huán)比發(fā)展速度=所有環(huán)比發(fā)展速度的乘積，開12次方;power(product(環(huán)比速度),1/12)。product():求所有參數(shù)的乘積。power():冪函數(shù)。例如：power(2,4)--16。5)環(huán)比增長速度=環(huán)比發(fā)展速度-100；6)移動(dòng)平均值：averge()。案例2：通過函數(shù)計(jì)算1）性別的計(jì)算if(mod(mid(身份證號(hào),17,1),2)=0,"女","男")。說明：mid()--提取倒數(shù)第2位；mod()--倒數(shù)第2位對(duì)2求余；if()--判斷求余的結(jié)果，0時(shí)為女，否則為男。2）出生日期的計(jì)算text(mid(身份證號(hào),7,8),"00年00月00日")。說明：text()--將mid提取的8位數(shù)字，以日期形式顯示； mid()--提取中間8位。 3）年齡計(jì)算datedif(出生日期,today(),"y")。 說明：datedif()--計(jì)算兩個(gè)日期的時(shí)間跨度； today()--返回今天的日期。4）工齡的計(jì)算 顯示形式：11年3個(gè)月。 datedif(入職日期,today(),"y")&"年"&； datedif(入職日期,today(),"ym")&"個(gè)月"。案例3：1）顯示開發(fā)工具選項(xiàng)卡 文件--選項(xiàng)--自定義功能區(qū)。2)插入分組框 開發(fā)工具--插入--分組框。3)插入四個(gè)單選按鈕，并編輯文字 插入：開發(fā)工具--插入。 編輯文字：在按鈕上右擊--編輯文字。4)顯示按鈕編號(hào) 在按鈕上右擊--設(shè)置格式--控制--鏈接到某個(gè)單元格。5)offset的使用 作用：把