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