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

Excel數(shù)據(jù)分析一、實(shí)踐目的1.掌握excel的使用（1）老師講解excel的基礎(chǔ)知識(shí)（2）老師實(shí)際操作簡(jiǎn)單步驟2.實(shí)現(xiàn)對(duì)數(shù)據(jù)的簡(jiǎn)單處理（1）學(xué)習(xí)如何數(shù)據(jù)導(dǎo)入（2）學(xué)習(xí)如何數(shù)據(jù)清洗（3）學(xué)習(xí)如何數(shù)據(jù)抽?。?）學(xué)習(xí)如何數(shù)據(jù)合并（5）學(xué)習(xí)如何數(shù)據(jù)計(jì)算（6）學(xué)習(xí)如何數(shù)據(jù)轉(zhuǎn)換（7）學(xué)習(xí)如何數(shù)據(jù)分析（8）學(xué)習(xí)如何數(shù)據(jù)展現(xiàn)3.熟練掌握所學(xué)內(nèi)容（1）每天完成老師布置內(nèi)容（2）每天對(duì)前一天所學(xué)內(nèi)容進(jìn)行重復(fù)操作達(dá)到熟練4.讓學(xué)生了解掌握數(shù)據(jù)分析對(duì)自己現(xiàn)在的影響老師講到目前高校理學(xué)、工學(xué)研究生均會(huì)涉及到數(shù)據(jù)科學(xué)的內(nèi)容，本科生目前都要學(xué)習(xí)數(shù)據(jù)分析各個(gè)方面的基礎(chǔ)知識(shí)，通過(guò)本次培訓(xùn)本科生可掌握一定的數(shù)據(jù)分析的方法和技能。5.讓學(xué)生了解掌握數(shù)據(jù)分析對(duì)自己以后的影響老師讓學(xué)生了解數(shù)據(jù)分析和挖掘崗位目前的就業(yè)形式和前景及所需相關(guān)技能，了解企業(yè)實(shí)際需求。對(duì)于自身以后的學(xué)習(xí)方向和就業(yè)范圍有初步的印象，為走好數(shù)據(jù)分析方向的職業(yè)道路提供有力的指引。二、實(shí)踐內(nèi)容1.數(shù)據(jù)導(dǎo)入2.數(shù)據(jù)清洗3.數(shù)據(jù)抽取4.數(shù)據(jù)合并5.數(shù)據(jù)計(jì)算6.數(shù)據(jù)轉(zhuǎn)換7.數(shù)據(jù)分析8.數(shù)據(jù)展現(xiàn)實(shí)踐過(guò)程1．6月24日第一天老師講解如何獲取數(shù)據(jù)和對(duì)數(shù)據(jù)的清洗的基本方法。（1）獲取數(shù)據(jù)方法1是手動(dòng)輸入數(shù)據(jù)（數(shù)據(jù)的類型:文本，數(shù)值，日期），文本的輸入可以直接輸入，數(shù)值的輸入若是較長(zhǎng)的數(shù)值則用英文單引號(hào)+數(shù)值，日期的輸入則按年月日的順序且年月日之間用斜桿分隔方法。方法2是導(dǎo)入來(lái)自其它文件的數(shù)據(jù)，導(dǎo)入文本文檔的話需要三個(gè)步驟第1步需要設(shè)置語(yǔ)言格式、第2步設(shè)置分隔、第3步設(shè)置各列數(shù)據(jù)格點(diǎn)擊完成即可。導(dǎo)入數(shù)據(jù)庫(kù)數(shù)據(jù)則需打開(kāi)數(shù)據(jù)庫(kù)點(diǎn)擊你所需要導(dǎo)入的文件點(diǎn)擊確認(rèn)即可。導(dǎo)入網(wǎng)站數(shù)據(jù)在地址欄內(nèi)輸入你所想要獲取數(shù)據(jù)的網(wǎng)址，在網(wǎng)址內(nèi)找到你所需要的數(shù)據(jù)即可。（2）數(shù)據(jù)清洗方法1：菜單刪除法(刪除重復(fù)值）：數(shù)據(jù)--刪除重復(fù)項(xiàng)。方法2：標(biāo)識(shí)法（只能標(biāo)識(shí)重復(fù)值）：選擇要標(biāo)識(shí)的數(shù)據(jù)區(qū)域--開(kāi)始--條件格式--重復(fù)值，選擇列：ctrl+shift+向下方向鍵。方法3：高級(jí)篩選法（可以重復(fù)值篩選出來(lái)），數(shù)據(jù)--高級(jí)篩選。方法4：函數(shù)法COUNTIF（可以計(jì)算值重復(fù)了多少次），函數(shù)操作步驟：第一步選中放結(jié)果單元格，第二步操作公式countif()，作用：統(tǒng)計(jì)某個(gè)區(qū)域中滿足條件的數(shù)據(jù)個(gè)數(shù)。單元格地址的引用有相對(duì)應(yīng)用和絕對(duì)引用，相對(duì)引用是單元格地址隨著公式的移動(dòng)而發(fā)生變化（默認(rèn)的）。絕對(duì)引用是單元格地址不隨著公式的移動(dòng)而發(fā)生變化（在地址前加美元符號(hào)）。方法5：數(shù)據(jù)透視表（excel的一個(gè)插入對(duì)象），概念：從多個(gè)角度對(duì)數(shù)據(jù)進(jìn)行匯總統(tǒng)計(jì)的表格。篩選：篩選數(shù)據(jù)、行/列：分類、值：計(jì)算。2.6月25日第二天老師講解缺失數(shù)據(jù)的處理，空白數(shù)據(jù)處理，數(shù)據(jù)抽取，數(shù)據(jù)合并（1）缺失數(shù)據(jù)處理老師講了選中多個(gè)空值的方法：開(kāi)始--選擇--定位條件--空值。還有多個(gè)單元格輸入同一個(gè)值的方法：選中多個(gè)單元格--輸入值--按CTRL+回車（2）空白數(shù)據(jù)處理方法1：替換（開(kāi)始--替換），查找內(nèi)容中輸入空格，替換為中不輸入內(nèi)容，點(diǎn)擊全部替換即可。方法2：函數(shù)trim的作用是刪除字符串前后空格（3）數(shù)據(jù)抽取我們學(xué)習(xí)了字段拆分和記錄拆分。字段拆分的方法有：方法1：菜單法(數(shù)據(jù)--分列)，注意：向?qū)У?步，文檔類型（固定寬度）向?qū)У?步，不導(dǎo)入列和目標(biāo)區(qū)域的設(shè)置點(diǎn)擊完成即可。方法2：函數(shù)法mid(字符串,開(kāi)始位置,長(zhǎng)度)，left()，right()。記錄拆分則用到了構(gòu)造函數(shù)的方法，第一步在新的一列輸入生成隨機(jī)數(shù)的函數(shù)rand()，第二步又在新的一列用隨機(jī)數(shù)排序函數(shù)rank()，第三步我們提取所需要的排名中的數(shù)據(jù)輸入函數(shù)VLOOKUP()。VLOOKUP()的作用是索提取函數(shù)，在用時(shí)第1空填寫要搜索的值，第2空填寫搜索區(qū)域（要絕對(duì)引用），第3空填寫要返回的結(jié)果所在的列，第4空填寫false(精確)/true(大致）。rank()的作用是排序，在使用時(shí)第1空填寫要排序的值，第2空填寫區(qū)域（要絕對(duì)應(yīng)用），第3空填寫0(降序)/1(升序)。（4）數(shù)據(jù)合并學(xué)習(xí)了列的合并的方法。方法1：函數(shù)concatenate()，方法2：文本連接符&，方法3：日期連接函數(shù)date()例如：date(2019,5,5)3.6月26日第三天老師講解數(shù)據(jù)的計(jì)算，數(shù)據(jù)的轉(zhuǎn)換。（1）數(shù)據(jù)計(jì)算數(shù)據(jù)計(jì)算有簡(jiǎn)單計(jì)算，計(jì)算符號(hào)+-*/。日期的計(jì)算：用到datedif(起始日期,終止日期,“顯示格式”)，顯示格式如下：y是年，m是月，d是日。標(biāo)準(zhǔn)化數(shù)據(jù)：就是把所有值劃分到0-1的區(qū)間內(nèi)，去掉特殊的異常值。max()是求最大值，min()是求最小值，x標(biāo)準(zhǔn)化=(x-min)/(max-min)。加權(quán)求和：為了數(shù)據(jù)所占比重平均化。輸入函數(shù)sumpruduct（）。分組的方法有方法1：運(yùn)用if函數(shù)if(條件判斷,條件為真時(shí)的結(jié)果,條件為假時(shí)的結(jié)果)。方法2：vlookup模糊匹配，注意：第4個(gè)空寫‘true’。（2）數(shù)據(jù)轉(zhuǎn)換數(shù)據(jù)轉(zhuǎn)換有行列轉(zhuǎn)換：復(fù)制數(shù)據(jù)后--選中目標(biāo)位置--選擇性粘貼--轉(zhuǎn)置。文本轉(zhuǎn)換成數(shù)值，方法1：文本乘以1，操作：先復(fù)制1選中要乘以1的所有值，右擊--選擇性粘貼--選擇‘乘法’。方法2：分列方法，操作：選中文本數(shù)據(jù)--數(shù)據(jù)--分列--在向?qū)У?步選擇相應(yīng)的類型方法3：智能標(biāo)記，操作：選中文本后，智能標(biāo)記會(huì)自動(dòng)出現(xiàn)。數(shù)值轉(zhuǎn)換成文本，方法1：分列，在向?qū)У?步中，設(shè)置格式為文本。方法2：函數(shù)輸入text(數(shù)值,"格式")函數(shù)，例如：text(100000,"0.00")保留2位小數(shù)，text(100000/10000,"0.0萬(wàn)")保留1位小數(shù)帶單位萬(wàn)。數(shù)值轉(zhuǎn)換成日期，方法1：分列，向?qū)У?步中，選擇日期格式。方法2：text(數(shù)值,"00年00月00日")。二維表轉(zhuǎn)換成一維表，alt+d+p：打開(kāi)數(shù)據(jù)透視表制作向?qū)?-多重合并計(jì)算區(qū)域--自定義頁(yè)數(shù)（0）--選擇數(shù)據(jù)區(qū)域--完成，雙擊‘總計(jì)’值后，就可以得到一維表的顯示方式。4.6月27日第四天老師講解掌握如何進(jìn)行各種分析和對(duì)各種分析的應(yīng)用（1）數(shù)據(jù)分析數(shù)據(jù)分析包含對(duì)比分析（同比，環(huán)比）、結(jié)構(gòu)分析（占比分析）、分布分析（分組）、交叉分析（矩陣分析）從兩個(gè)維度對(duì)數(shù)據(jù)進(jìn)行分析、多表關(guān)聯(lián)分析、RFM分析（從三個(gè)角度分析客戶的質(zhì)量），R:時(shí)間間隔、F:購(gòu)買頻率、M:購(gòu)買金額。對(duì)比分析包含環(huán)比：例如(2011年3月和2011年2月的差異百分比），(3月-2月)/2月。同比：例如(2012年3月和2011年3月的差異百分比)，(2012年-2011年)/2011年。注意：數(shù)據(jù)透視表中，計(jì)算環(huán)比：選擇‘值顯示方式’為差異百分比，選擇字段為“注冊(cè)時(shí)間”。數(shù)據(jù)透視表中，計(jì)算同比：選擇‘值顯示方式’為差異百分比，選擇字段為“年”。結(jié)構(gòu)分析：某種商品金額，占總金額的百分比，注意：操作透視表中，要明確按誰(shuí)進(jìn)行分類。結(jié)構(gòu)分析（占比分析），占比：一個(gè)項(xiàng)目中，各決定因素所在的比例。分布分析：根據(jù)分析的目的，將數(shù)值型數(shù)據(jù)進(jìn)行等距或不等距的分組。分組：相應(yīng)值落入到各組中的比率。方法1：vlookup方法分組，特點(diǎn)：可以進(jìn)行不等距分組。操作：用vlookup分組后，用數(shù)據(jù)透視表統(tǒng)計(jì)分組結(jié)果，點(diǎn)擊確定后雙擊小十字批量完成。方法2：數(shù)據(jù)透視表分組，特點(diǎn)：只能進(jìn)行等距分組，操作：直接用數(shù)據(jù)透視表的分組功能完成即可。交叉分析的目的是從兩個(gè)維度對(duì)我們的客戶進(jìn)行分類。操作步驟：第一步用vlookup確定各客戶，兩個(gè)維度的性質(zhì)，第二步用數(shù)據(jù)透視表，從兩個(gè)維度進(jìn)行分類，來(lái)統(tǒng)計(jì)各分類的客戶人數(shù)，第三步從一維表的角度，查看客戶分類情況的操作（1.以表格形式顯示；2.重復(fù)所有標(biāo)簽；3.不顯示匯總結(jié)果）。5.6月28日第五天老師講解如何掌握矩陣分析的方法和應(yīng)用（1）矩陣分析1.從月平均消費(fèi)和月平均流量，兩個(gè)角度對(duì)用戶質(zhì)量進(jìn)行分析，注意：透視表中，值字段設(shè)置的計(jì)算方式改為‘平均值。2.復(fù)制數(shù)據(jù)透視表，選擇性粘貼‘值’到其它位置。3.制作散點(diǎn)圖，步驟：選中數(shù)據(jù)區(qū)域--插入--散點(diǎn)圖，注意：選中數(shù)據(jù)是，只選擇月平均消費(fèi)和月平均流量的值。4.將圖表區(qū)域進(jìn)行矩陣劃分，方法：移動(dòng)x軸和y軸，使它們?cè)谄骄滴恢媒徊妗?.圖表格式設(shè)置，方法：在要設(shè)置的相應(yīng)對(duì)象上右擊--設(shè)置XXX格式。6.手動(dòng)繪制x軸和y軸，方法：選中散點(diǎn)圖后，進(jìn)行繪制，作用：可以把繪制圖像和散點(diǎn)圖自動(dòng)組合。7.散點(diǎn)標(biāo)簽的設(shè)置。6.6月29日第六天老師講解如何掌握并應(yīng)用兩種分析方法，學(xué)會(huì)進(jìn)行數(shù)據(jù)展現(xiàn)（1）多表關(guān)聯(lián)分析操作步驟：第一步分析用到的表格，并把各表格添加到模板中，添加模板方法：插入--表格。第二步制作數(shù)據(jù)透視表，建立表之間連接，并進(jìn)行數(shù)據(jù)分析，注意：兩個(gè)表連接的條件是，他們的公共字段。連接方法：插入--數(shù)據(jù)透視表--分析工具選項(xiàng)卡--關(guān)系。（2）RFM分析RFM分析(從三個(gè)角度對(duì)用戶進(jìn)行分類)，R:最近一次的消費(fèi)時(shí)間（時(shí)長(zhǎng)）、F:最近的消費(fèi)次數(shù)、M:消費(fèi)額度。步驟：第一步計(jì)算R,F,M值(用數(shù)據(jù)透視表完成)。R：日期最大值、F：訂單ID計(jì)數(shù)、M:金額求平均值，第二步把上面透視表結(jié)果復(fù)制，到新表中（粘貼時(shí)，使用選擇性粘貼的'值'）注意：把R用datedif換算成天數(shù)，、第三步對(duì)R,F,M評(píng)分，第四步用透視表，對(duì)評(píng)分結(jié)果做分析。(3)數(shù)據(jù)展現(xiàn)制作Excel圖表:制作餅圖：占比成分。操作第一步圖表各對(duì)象格式設(shè)置，方法：在對(duì)象上右擊--選擇相應(yīng)操作。操作第二步圖表布局。操作第三步圖表設(shè)計(jì)(數(shù)據(jù)選擇,圖表類型更改)。制作圖表方法：選中做圖表的數(shù)據(jù)--插入--圖表類型--進(jìn)行圖表設(shè)置。制作雙坐標(biāo)軸圖表。用途為數(shù)據(jù)類別為兩個(gè)（單位不同或者量差別較大），步驟：第一步制作圖表(柱形圖)，第二步把較小單位的值，用次坐標(biāo)軸表示，方法：選中圖表--右擊--設(shè)置格式--次坐標(biāo)軸，第三步修改次坐標(biāo)軸，圖表類型為折線圖。格式設(shè)置：文字大小、方向，坐標(biāo)軸的刻度，隱藏次坐標(biāo)軸。7.7月2日第七天老師講解掌握數(shù)據(jù)展現(xiàn)的各種圖表的制作（1）數(shù)據(jù)展現(xiàn)excel圖表工具包含目標(biāo)完成率圖：它的作用是反映業(yè)務(wù)目標(biāo)的完成情況。操作：類似雙坐標(biāo)軸操作。注意：把完成值，繪制在次坐標(biāo)軸上。格式設(shè)置：系列圖形的填充色，線條色，系列間隙寬度，隱藏次坐標(biāo)軸。最后成值，添加完成率的數(shù)據(jù)標(biāo)簽。雷達(dá)圖的作用：系列有2組以上數(shù)據(jù)時(shí)，用該圖。操作：選中數(shù)據(jù)--插入--雷達(dá)圖。格式設(shè)置：系列寬度設(shè)置在1以下。矩陣圖的作用：用兩組相關(guān)數(shù)據(jù)，對(duì)我們的客戶進(jìn)行定性分類。操作注意：選擇數(shù)據(jù)（不選行/列標(biāo)題和平均值），給每個(gè)點(diǎn)添加標(biāo)簽(行標(biāo)題)，移動(dòng)x/Y坐標(biāo)軸，到平均值位置，重新繪制x/y坐標(biāo)。迷你圖的作用：當(dāng)數(shù)據(jù)系列比較多時(shí)，快速查看每個(gè)系列的趨勢(shì)或變化情況。操作方法為光標(biāo)放在放迷你圖位置--插入--迷你圖--選擇類型。設(shè)置內(nèi)容：圖表樣式，設(shè)置高點(diǎn)，低點(diǎn)。8.7月3日第八天老師講解如何掌握數(shù)據(jù)展現(xiàn)的各種圖表的制作(1)漏斗圖作用是一般用來(lái)表示，一個(gè)商業(yè)行為的變化過(guò)程，例如購(gòu)物（瀏覽產(chǎn)品--放入購(gòu)物車--下單--支付--完成）操作：選中數(shù)據(jù)--插入--堆積條形圖注意：逆序系列標(biāo)簽，方法：選中縱坐標(biāo)軸--右擊--設(shè)置格式--逆序類別。添加占位數(shù)據(jù)，方法：在圖表上右擊--添加數(shù)據(jù)。注意：把占位數(shù)據(jù)放到系列數(shù)據(jù)的前面，把占位數(shù)據(jù)的圖形，填充和線條都設(shè)置為‘無(wú)’，形成封閉的漏斗。方法：圖表工具--設(shè)計(jì)--添加元素--線條--系列線（2）旋風(fēng)圖作用是展現(xiàn)不同數(shù)據(jù)在同一組指標(biāo)下比較結(jié)果。操作1：選中數(shù)據(jù)--插入--堆積條形圖。注意：繪制其中一組數(shù)據(jù)到‘次坐標(biāo)軸’，修改主次坐標(biāo)軸的刻度。最小值：負(fù)數(shù)。最大值：負(fù)數(shù)絕對(duì)值。將次坐標(biāo)軸刻度進(jìn)行‘逆序’，方法：在次坐標(biāo)軸上右擊--逆序刻度值。格式設(shè)置：將縱坐標(biāo)軸標(biāo)簽，移動(dòng)到左側(cè)。方法：設(shè)置標(biāo)簽位置為‘低’。隱藏次坐標(biāo)軸方法：設(shè)置次坐標(biāo)軸標(biāo)簽為‘無(wú)’修改主坐標(biāo)軸的數(shù)字格式為‘0:0:0’方法：數(shù)字--自定義（3）帕累托圖（28原則）作用是分析出現(xiàn)問(wèn)題后，原因的定位分析。操作：選擇數(shù)據(jù)--插入--柱形圖。設(shè)置：設(shè)置柱形圖系列間距為最小。設(shè)置主坐標(biāo)軸刻度最大值為總問(wèn)題數(shù)。添加問(wèn)題百分比數(shù)據(jù)到次坐標(biāo)軸上，設(shè)置次坐標(biāo)軸最大值為100%。添加次橫坐標(biāo)軸，方法：圖表工具--設(shè)計(jì)--添加元素--坐標(biāo)軸--次橫坐標(biāo)軸。設(shè)置次橫坐標(biāo)軸的位置為：刻度線上。9．7月4號(hào)第九天老師分析所給案例中的數(shù)據(jù)（1）案例2同比、環(huán)比、環(huán)比發(fā)展速度=本月/上月。平均環(huán)比發(fā)展速度=所有環(huán)比發(fā)展速度的乘積，開(kāi)12次方，power(product(環(huán)比速度),1/12)，product()求所有參數(shù)的乘積，power()冪函數(shù)，例如：power(2,4)。環(huán)比增長(zhǎng)速度=環(huán)比發(fā)展速度-100。移動(dòng)平均值：averge()。（2）案例3通過(guò)函數(shù)計(jì)算。性別的計(jì)算，if(mod(mid(身份證號(hào),17,1),2)=0,"女","男")說(shuō)明：mid()--提取倒數(shù)第2位，mod()--倒數(shù)第2位對(duì)2求余，if()--判斷求余的結(jié)果，0時(shí)為女，否則為男。出生日期的計(jì)算，text(mid(身份證號(hào),7,8),"00年00月00日")，說(shuō)明：text()--將mid提取的8位數(shù)字，以日期形式顯示，mid()--提取中間8位。年齡計(jì)算datedif(出生日期,today(),"y")。說(shuō)明：datedif()--計(jì)算兩個(gè)日期的時(shí)間跨度。today()--返回今天的日期。工齡的計(jì)算，顯示形式：11年3個(gè)月。datedif(入職日期,today(),"y")&"年"&，datedif(入職日期,today(),"ym")&"個(gè)月"。（3）案例4顯示開(kāi)發(fā)工具選項(xiàng)卡，文件--選項(xiàng)--自定義功能區(qū)。插入分組框，開(kāi)發(fā)工具--插入--分組框。插入四個(gè)單選按鈕，并編輯文字，插入：開(kāi)發(fā)工具--插入，編輯文字：在按鈕上右擊--編輯文字。顯示按鈕編號(hào)，在按鈕上右擊--設(shè)置格式--控制--鏈接到某個(gè)單元格。offset的使用，作用：把按鈕和數(shù)據(jù)區(qū)域進(jìn)行關(guān)聯(lián)的。四、實(shí)踐體會(huì)通過(guò)本次實(shí)訓(xùn)學(xué)習(xí)我充分認(rèn)識(shí)到計(jì)算機(jī)技能對(duì)以后工作的重要性，而Excel作為最基本的計(jì)算機(jī)技能更是我們必須要掌握的。我們可以通過(guò)Excel更加快捷的記錄數(shù)據(jù)、分析數(shù)據(jù)、編輯數(shù)學(xué)公式進(jìn)行快速計(jì)算等等。在學(xué)習(xí)前還去了解了Excel了。Excel是個(gè)人電腦普及以來(lái)用途