excel數(shù)據(jù)處理實踐報告

excel數(shù)據(jù)處理實踐報告成果名稱：excel數(shù)據(jù)處理EXCEL數(shù)據(jù)處理實踐目的1、通過基本商務數(shù)據(jù)分析的講解，充分感受到商務數(shù)據(jù)分析所需要的基本知識和技能2、了解目前商業(yè)數(shù)據(jù)的發(fā)展及應用場景，對自己的職業(yè)定位有個更加清晰二、實踐內(nèi)容1、界面組成：工作簿：包含多個工作表工作簿名稱菜單選項卡（開始、插入、數(shù)據(jù)、公式）功能區(qū)工作區(qū)狀態(tài)欄名稱框編輯欄工作表標簽行名：1、2、3、4列名：A、B、C、D單元格名：列名+行名2、數(shù)據(jù)分析中：工作表的格式規(guī)則由行和列組成一行稱為一條記錄一列稱為一個屬性（字段）、列名就是字段名數(shù)據(jù)表的所有記錄不能重復不允許出現(xiàn)合并單元格的結構（列）3、獲取數(shù)據(jù)的方法法1、手動輸入數(shù)據(jù)：（1）數(shù)據(jù)的類型：文本、數(shù)值、日期（2）文本的輸入：直接輸入eg、當輸入日期2010.10，系統(tǒng)會識別為2010.1，但當單元格設為文本時就會全部顯示出來（3）數(shù)值的輸入：a、較長的數(shù)值：英文單引號（‘）+數(shù)值b、日期的輸入：年月日的順序，年/月/日法2、導入來自其它類型文件的數(shù)據(jù)：文本文檔：設置語言格式設置分隔符設置各列數(shù)據(jù)格式數(shù)據(jù)庫數(shù)據(jù)網(wǎng)站數(shù)據(jù)：網(wǎng)址-數(shù)據(jù)-自網(wǎng)站-選擇導入數(shù)據(jù)區(qū)域（點擊箭頭）-右鍵-數(shù)據(jù)范圍屬性-設置刷新頻率4、清洗數(shù)據(jù)重復數(shù)據(jù)處理數(shù)據(jù)透視表法（統(tǒng)計重復次數(shù)）：選中所有數(shù)據(jù)--插入--數(shù)據(jù)透視表（默認設置）--號碼放到行、號碼放到值--得到號碼的統(tǒng)計數(shù)據(jù)--選擇一個右鍵--排序--得到排序結果菜單刪除法（刪除重復值）：選中所有數(shù)據(jù)--數(shù)據(jù)--數(shù)據(jù)工具--刪除重復值--勾選號碼--發(fā)現(xiàn)重復值并刪除重復值--保留其余條件格式標識法（標記重復值）選中數(shù)據(jù)--開始--條件格式--突出顯示單元格規(guī)則--選出重復值而不刪除高級篩選法（得到重復結果）選中數(shù)據(jù)--數(shù)據(jù)--排序和篩選--篩選（高級）--將篩選結果復制到其他位置--選擇不重復的記錄--復制到（篩選后結果顯示）--得到了去重復的結果，不知道重復幾次函數(shù)（countif）法--得到重復次數(shù)空白列單元格處--公式--插入函數(shù)--找到函數(shù)COUNTIF--RANGE（篩選的范圍）--快捷鍵：ctrl+shift+向下箭頭Criteria--篩選條件--選擇第一個數(shù)據(jù)--點擊小十字得到重復次數(shù)缺失數(shù)據(jù)處理取消合并單元格定位條件：Ctrl+G或F5自動填充：Ctrl+Entereg、選中合并單元格區(qū)域--開始--對齊方式--設置單元格對齊方式合并單元格（√去掉）Ctrl+G定位條件--點擊定位條件--選擇空值--按=和上箭頭（選中）再按Ctrl+Enter進行填充空白數(shù)據(jù)處理法1、查看有空格的區(qū)域--開始--查找和選擇--替換查找內(nèi)容（輸入一個空格）--替換為（不填寫）--全部替換快捷鍵：Ctrl+H法2、用TRIM函數(shù)選擇空白單元格--公式--插入函數(shù)--選擇TRIMText（需要填入的內(nèi)容）--點擊小十字5、數(shù)據(jù)抽取字段拆分菜單法：數(shù)據(jù)--分列選中間段--在該列旁邊插入列--點擊列右鍵插入數(shù)據(jù)--數(shù)據(jù)工具--分列--固定寬度--選擇分列長度舍棄后邊的--點擊另一半（選擇不導入此列）--目標區(qū)域為新插入列（只導入目標列內(nèi)容）注意：插入幾條豎線就分為幾個部分函數(shù)法：MidLeftRightLeft:公式--函數(shù)--left函數(shù)（從左邊開始截取指定位數(shù)）在得出結論的單元格內(nèi)輸入=left（文本區(qū)域，截取長度）eg、（A5,7）Right：與left相似（表示從右邊開始截取指定位數(shù)）Mid:（從左到右開始截取，起始為1）從指定起始位置選擇指定長度截取記錄隨機抽取生成隨機數(shù)：rand（隨機數(shù)列輸入：=rand（）回車生成隨機數(shù)--雙擊小十字）對隨機數(shù)排序：rank（選中排名列第一行輸入rank（）第一個參數(shù)：按照什么排列-選擇第一個隨機數(shù)。第二個參數(shù)：選擇范圍-選擇隨機數(shù)整列。第一個排名生成后雙擊小十字得到全部排名）提出排序結果中前xx名提取出來：vlookup（）vlookup()搜索提取函數(shù) 第1空：要搜索的值 第2空：搜索區(qū)域（在哪里搜索） 注意：要絕對引用（前加$） 第3空：要返回的值所在的列編號 第4空：false（精確匹配）/true（大致匹配）6、數(shù)據(jù)合并列的合并法1、（字段合并：靠左排列文本型數(shù)據(jù)，靠右排列為數(shù)值型數(shù)據(jù)）函數(shù)concatenate（text1,[text2],...）text1,text2可以是文本或者數(shù)值法2、文本連接符&法3、日期連接函數(shù)date（）eg、date(2019,5,5)7、多條件匹配vlookup執(zhí)行原理：從區(qū)域(第2個空)的首列，搜索第1個空的值vlookup(1個號碼，區(qū)域,列號，false) 數(shù)組運算執(zhí)行方法：CTRL+SHIFT+回車8、數(shù)據(jù)計算簡單計算：+、-、*、/日期計算：datedif（起始日期，終止日期，格式）作用：計算起始和終止日期之間所經(jīng)歷的時長 時長格式（y:以年顯示；m：以月顯示；d：以日顯示）Year（）：提取日期中的年份Month（）：提取日期中的月Day（）：提取日期中的日Today（）：返回當前系統(tǒng)日期標準化計算：使一些數(shù)據(jù)的異常值，落入到正常值的0-1的區(qū)間。計算方法：x標準化=（x-最小值）/（最大值-最小值）Max（）求最大值Min（）求最小只9、加權求和：通過加權，讓數(shù)據(jù)的占比均化Sumproduct（）實質(zhì)：各數(shù)據(jù)乘以權重，并求各乘積的和10、分組法1、if函數(shù)分組If（條件判斷，條件為真時的結果，條件為假時的結果）法2、vlookup函數(shù)模糊匹配分組操作：vlookup函數(shù)的第四個填空11、數(shù)據(jù)類型轉(zhuǎn)換行/列轉(zhuǎn)換操作：復制數(shù)字區(qū)域--到目標為止--右擊--選擇性粘貼對話框中，選擇“轉(zhuǎn)置”文本--數(shù)值法1、分列操作：選中數(shù)據(jù)區(qū)域--數(shù)據(jù)--分列--分列到第3步選擇“常規(guī)”類型法2、選擇性粘貼--運算操作：先復制‘1’--選中數(shù)據(jù)--選擇粘貼--運算法3、智能標記數(shù)值--文本法1、分列法2、函數(shù)Text（數(shù)值,‘格式’）常用格式：小數(shù)（0.00）；百分數(shù)（0.0%）；日期（00年00月00日）數(shù)值--日期：分列二維表--維表：使用數(shù)據(jù)透視表制作向?qū)Р僮鳎篴、ALT+D+P（打開向?qū)υ捒颍゜、多重合并數(shù)據(jù)區(qū)域c、自定義頁字段d選擇數(shù)據(jù)區(qū)域f、雙擊數(shù)據(jù)透視表的總計值（可以把二維轉(zhuǎn)一維）3、數(shù)據(jù)分析（1）對比分析（2）結構分析（占比分析）（3）分布分析：根據(jù)分析的目的，將數(shù)值型數(shù)據(jù)進行等距或不等距的分組法1：vlookup方法分組 特點：可以進行不等距分組 操作：用vlookup分組后，用數(shù)據(jù)透視表統(tǒng)計分組結果法2：數(shù)據(jù)透視表分組 特點：只能進行等距分組 操作：直接用數(shù)據(jù)透視表的分組功能完成即可（4）交叉分析目的：從兩個維度對我們的客戶進行分類操作步驟：1.用vlookup確定各客戶，兩個維度的性質(zhì) 2.用數(shù)據(jù)透視表，從兩個維度進行分類，來統(tǒng)計各分類的客戶人數(shù) 3.從一維表的角度，查看客戶分類情況的操作（1.以表格形式顯示；2.重復所有標簽；3.不顯示匯總結果）（5）多表關聯(lián)分析12、數(shù)據(jù)分析1、對比分析(按時間分組) 同比： 環(huán)比： 計算方法：透視表中，值顯示方式--差異百分比2、結構(占比)分析(定性分組)計算方法:透視表中，值顯示方式--列匯總百分比/行匯總百分比3、分布分析(定量分組)法1：VLOOKUP 特點：可以進行不等距分組 法2：透視表分組 特點：只能進行等距分組4、交叉分析(定性分組）5、矩陣分析： 1.先進行定性分組，對數(shù)據(jù)進行平均值的計算 平均值計算方法：在透視表的計算區(qū)域上，右擊--值字段設置--平均值 2.復制透視表數(shù)據(jù)到新的位置 注意：粘貼數(shù)據(jù)時，用‘選擇性粘貼’中的值 3.制作散點（矩陣）圖 方法：選中月平均消費和月平均流量的值--插入--散點圖 注意：不選行/列標簽及總計 4.散點圖x/y坐標軸，移動到平均值交叉位置 方法：在坐標軸上右擊--坐標軸格式，設置即可5.重新繪制x/y的坐標軸。 6.給散點添加標簽 （1）在點上右擊--添加標簽 （2）選中標簽--右擊--設置標簽格式--單元格中的值--選擇文字性的標簽--取消Y值13、多表關聯(lián)分析通過地區(qū)和通訊品牌兩個維度，統(tǒng)計消費用戶數(shù)操作步驟： 1.分析兩個表之間的連接字段（列） 注意：連接字段，就是兩個表的公共列(例如：省份) 2.把兩個表添加到模板中 方法：插入--表格3.插入--透視表--選擇多個表 4.進行多表連接 方法：透視表工具--分析--關系 5.數(shù)據(jù)分析即可 案例：各手機品牌是否開通微信的用戶數(shù) 1.手機品牌，微信 公共列為：號碼2.用透視表做關聯(lián) A、把表放到模板中 B、插入--透視表--添加多個表 案例：各地區(qū)是否開通微信的用戶數(shù) 地區(qū)：地區(qū) 微信：號碼表14、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）餅圖：占比成分操作1:圖表各對象格式設置 方法：在對象上右擊--選擇相應操作、2:圖表布局3:圖表設計(數(shù)據(jù)選擇,圖表類型更改)制作圖表方法：選中做圖表的數(shù)據(jù)--插入--圖表類型--進行圖表設置（2）雙坐標軸圖表用途：數(shù)據(jù)類別為兩個（單位不同或者量差別較大）步驟：1.制作圖表(柱形圖) 2.把較小單位的值，用次坐標軸表示 方法：選中圖表--右擊--設置格式--次坐標軸 3.修改次坐標軸，圖表類型為折線圖（3）格式設置：1.文字大小，方向 2.坐標軸的刻度 3.隱藏次坐標軸15、EXCEL圖表工具（數(shù)據(jù)展現(xiàn)）雙坐標軸圖作用：把量級差別較大或單位不同的數(shù)據(jù)，在一個圖表上表示 操作：選中數(shù)據(jù)--插入--柱形圖設置內(nèi)容：（1）文字格式繪制自坐標軸更改圖表類型添加標簽隱藏次坐標軸目標完成率圖作用：反映業(yè)務目標的完成情況 操作：類似雙坐標軸操作 注意：把完成值，繪制在次坐標軸上格式設置： （1）系列圖形的填充色，線條色，系列間隙寬度 （2）隱藏次坐標軸 （3）給完成值，添加完成率的數(shù)據(jù)標簽雷達圖 作用：系列有2組以上數(shù)據(jù)時，用該圖 操作：選中數(shù)據(jù)--插入--雷達圖 格式設置：系列寬度設置在1以下矩陣圖作用：用兩組相關數(shù)據(jù)，對我們的客戶進行定性分類 操作注意：（1）選擇數(shù)據(jù)（不選行/列標題和平均值） （2）給每個點添加標簽(行標題) （3）移動x/Y坐標軸，到平均值位置 （4）重新繪制x/y坐標迷你圖作用：當數(shù)據(jù)系列比較多時，快速查看每個系列的趨勢或變化情況 操作方法：光標放在放迷你圖位置--插入--迷你圖--選擇類型 設置內(nèi)容：1.圖表樣式2.設置高點，低點條件格式的設置作用：對滿足條件的數(shù)據(jù)，設置相應的格式7、漏斗圖作用：反映一個商業(yè)行為各個環(huán)節(jié)的數(shù)據(jù)變化情況 注意： 1.堆積條形圖 2.將坐標軸逆序 3.占位數(shù)據(jù)的添加、位置、隱藏 4.封閉漏斗：添加系列線8、旋風圖作用：在同一組指標下，對兩組數(shù)據(jù)的比較 注意： 1.堆積條形圖 2.將一組數(shù)據(jù)繪制到次坐標軸 3.將次坐標刻度逆序，并設置主/次坐標刻度最小值為負數(shù)，最大值為負數(shù)絕對值 4.設置內(nèi)容：坐標軸標簽--靠左 次坐標軸--隱藏 主坐標軸刻度--數(shù)字格式（0;0;0） 添加數(shù)據(jù)標簽9、帕累托圖--28原則作用：分析問題發(fā)生的原因定位 注意： 1.繪制柱形圖 2.問題占比繪制在次坐標軸上--折線圖 3.柱形系列圖，設置間距為0 4.折線圖的次橫坐標軸刻度--在刻度線上 5.次坐標軸最大值為110、案例案例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)--165.環(huán)比增長速度=環(huán)比發(fā)展速度-1006.移動平均值：averge()案例2：通過函數(shù)計算1.性別的計算if(mod(mid(身份證號,17,1),2)=0,"女","男")說明：mid()--提取倒數(shù)第2位mod()--倒數(shù)第2位對2求余if()--判斷求余的結果，0時為女，否則為男2.出生日期的計算text(mid(身份證號,7,8),"00年00月00日")說明： text()--將mid提取的8位數(shù)字，以日期形式顯示 mid()--提取中間8位 3.年齡計算 datedif(出生日期,today(),"y") 說明： datedif()--計算兩個日期的時間跨度 today()--返回今天的日期4.工齡的計算 顯示形式：11年3個月 datedif(入職日期,today(),"y")&"年"& datedif(入職日期,today(),"ym")&"個月"案例3：1.顯示開發(fā)工具選項卡 文件--選項--自定義功能區(qū)2.插入分組框 開發(fā)工具--插入--分組框3.插入四個單選按鈕，并編輯文字 插入：開發(fā)工具--插入 編輯文字：在按鈕上右擊--編輯文字4.顯示按鈕編號 在按鈕上右擊--設置格式--控制--鏈接到某個單元格5.offset的使用 作用：把按鈕和數(shù)據(jù)區(qū)域進行