有天，一位做IT運維的朋友小A請我吃飯递沪，還沒上菜就開始吐槽他老板豺鼻。我就邊吃邊了解情況。
原來小A的公司部署了審計設(shè)備款慨，所有員工訪問公司文件的行為都會有日志記錄拘领。老板突然有一天想要看看這個審計報告，小A就給從設(shè)備上導(dǎo)出日志發(fā)給老板樱调。結(jié)果被老板一頓屌！
我讓小A給我看了他發(fā)的內(nèi)容：
我心想：“老板們都是日理萬機届良，每天心急火燎的笆凌，哪有心情一條條看這個東西∈亢”
我跟小A說：“其實我們只要給老板需要的關(guān)鍵信息即可乞而！你知道老板最關(guān)注哪些事情嗎？”
小A：“他最關(guān)心公司資料的保密性了慢显。他之前問過我?guī)讉€問題爪模，我當(dāng)時沒有統(tǒng)計清楚，就沒回答上來荚藻∥莨啵”
我放下筷子說：“好，既然吃了你這頓飯应狱，就幫你找答案吧共郭！”
第二天，我做了3張報表發(fā)給小A疾呻。并跟他說：“其實老板們最愛看圖表除嘹，但是時間不夠。你先用這幾張表回復(fù)老板提的問題岸蜗，讓他心里有個底尉咕。以后再用圖表呈現(xiàn)，做到數(shù)據(jù)可視化璃岳！”
小A看了看效果問：“這是怎么統(tǒng)計出來的！教教我晦款！請你喝咖啡炎功！”
我說：“簡單，都有套路缓溅！”
下面就來講解一下操作過程
整個流程分為5步：找出問題（需求）-> 理解數(shù)據(jù) -> 數(shù)據(jù)清洗 -> 構(gòu)建模型 -> 數(shù)據(jù)可視化
第一步：找出問題（需求）
這個環(huán)節(jié)是定義老板或用戶的需求蛇损，將業(yè)務(wù)需求轉(zhuǎn)化為技術(shù)需求，并且要和當(dāng)事人確認(rèn)坛怪。以避免勞而無功淤齐！
這里老板關(guān)注的誰動了他的文件，可以轉(zhuǎn)化為以下要輸出的報表：
- 哪些項目文件袜匿，訪問的次數(shù)最多更啄，按降序排序。（見圖：“按項目查看居灯，用戶訪問資料記錄.png”）
- 哪些具體的文件祭务，訪問的次數(shù)最多，按降序排序怪嫌。（見圖：“按文件名查看义锥，用戶訪問資料記錄.png”）
- 有哪些員工，在不同的IP地址訪問過文件岩灭。（見圖：“多IP登陸訪問的用戶.png”）
第二步：理解數(shù)據(jù)
我們拿到數(shù)據(jù)源文件后拌倍，開始分析其中包含哪些表格、字段噪径、數(shù)據(jù)類型等柱恤，以便執(zhí)行后續(xù)的數(shù)據(jù)處理。
該日志文件按照7種文件類型找爱，并按照上傳和下載的訪問操作梗顺，分成了不同的sheet表格。其中包含了：客戶圖檔车摄、零件圖檔荚守、工程圖檔、DFM等练般。
每個sheet表格中矗漾，包含序號、專案薄料、文件名稱敞贡、工號、姓名摄职、事件時間誊役、IP等字段获列。
第三步：數(shù)據(jù)清洗
這是最花時間的環(huán)節(jié)，我們會使用如下的處理方法蛔垢，直到將源數(shù)據(jù)清洗成能做報表的樣子：
- 選擇子集
- 列名重命名
- 刪除重復(fù)值
- 缺失值處理
- 一致化處理
- 數(shù)據(jù)排序
- 異常值處理
具體操作：
（在操作前击孩，先復(fù)制一份源文件，并命名為“日志-數(shù)據(jù)清洗.xlsx”鹏漆。如果有誤操作巩梢，還能恢復(fù)數(shù)據(jù)。）
- 選擇子集：
我們先新建一個匯總的sheet艺玲，并將其重命名為“資料上傳下載記錄匯總表”括蝠。表格第一行輸入【序號】、【專案】饭聚、【文件名稱】等”字段忌警，將所有日志sheet都復(fù)制黏貼到一個sheet中。注：因為要將不同sheet的數(shù)據(jù)合并到一個匯總表中秒梳，必須新增一個【文件操作記錄類別】的字段法绵，用以記錄數(shù)據(jù)來源哪一個類別
缺失值處理：
匯總完成后，我發(fā)現(xiàn)有些IP是空值酪碘。為了做報表時朋譬，方便計數(shù)。我們將這些空值都填充為“無記錄”的字符串婆跑。
我們選中IP這一列，然后點擊 開始 選項卡下面的 查找和選擇 庭呜，點擊 定位條件 （快捷鍵Ctrl+G）滑进，在 定位條件 中選中 空值(K) ，確定募谎。
這時候扶关，IP這列的所有空值都會被選中，我們在第一個空值處数冬，輸入“無記錄”节槐，然后快捷鍵Ctrl+enter，其他空值就都會被填充“無記錄”拐纱。-
一致化處理：
在報表中我們還需要展示文件上傳或下載的次數(shù)铜异，所以需要新增一個字段【文件訪問的方式】。
那如何獲取上傳還是下載的數(shù)據(jù)呢秸架？
這里我們使用MID和FIND函數(shù)揍庄，截取【文件操作記錄類別】字段中的“記錄”前面的兩個文字。
截取所需字段.png
到這里东抹，這個元數(shù)據(jù)的清洗工作基本完成~
第四步：構(gòu)建模型
既然磨具都準(zhǔn)備好了蚂子，就可以開始搭積木了沃测。one by one！
- 員工訪問次數(shù)食茎，按照項目排序蒂破。
-
插入透視表：在“資料上傳下載記錄匯總表”中，點擊 插入 選項卡下面的 數(shù)據(jù)透視表 别渔，選中 新工作表(N) 附迷，確定。
插入數(shù)據(jù)透視表.png 設(shè)置透視表：在新建的數(shù)據(jù)透視表中钠糊，將【專案】字段拖入 行標(biāo)簽 區(qū)域挟秤，將【文件訪問方式】字段拖入 列標(biāo)簽 區(qū)域，將【工號】字段再拖入 值標(biāo)簽 區(qū)域抄伍。
-
設(shè)置排序：在左側(cè) 行標(biāo)簽 點擊右鍵艘刚，點擊 其他排序選項，然后截珍，按照工號的計數(shù)降序排序攀甚。
點擊其他排序選項.png
可以看到報表，呈現(xiàn)結(jié)果如下：
員工訪問文件次數(shù)秋度，按照文件名排序。（方法同上钱床，只是 行標(biāo)簽 區(qū)域里面的字段改為【文件名稱】）
-
員工在多IP地址訪問文件的排序荚斯。（方法同上，將【工號】查牌、【姓名】事期、【IP】的字段拖入 行標(biāo)簽 區(qū)域，將【文件訪問方式】字段拖入 列標(biāo)簽 區(qū)域纸颜，將【IP】字段再拖入 值標(biāo)簽 區(qū)域）兽泣。報表呈現(xiàn)員工名下登陸的IP和計數(shù)。
員工多IP訪問記錄.png
可是報表好像跟需求有點不符胁孙。老板關(guān)心的哪些員工在多個IP登陸訪問唠倦，而不是要統(tǒng)計訪問的次數(shù)，能不能讓在多個IP訪問的員工在表格中排序靠前呢涮较？
想到一個思路：我們找出每個員工對應(yīng)每個不同的IP稠鼻，在表格中出現(xiàn)的次數(shù)，就可以匯總排序了狂票。
看來之前的數(shù)據(jù)清洗工作還不完全枷餐！
讓我們繼續(xù)第三步：數(shù)據(jù)清洗（補）
- 刪除重復(fù)值：
為了不影響其他報表，我們將匯總表復(fù)制成一個新的sheet，命名為“資料上傳下載記錄匯總表 (2)”毛肋。
-
先將原數(shù)據(jù)的怨咪，員工列和ip地址列合并成一列。
合并（工號-姓名-IP）.png -
然后刪除 合并（工號-姓名-IP）列 的重復(fù)項润匙。（這時候诗眨，員工對應(yīng)的ip地址，數(shù)據(jù)都是唯一了）
刪除重復(fù)值.png -
再插入數(shù)據(jù)透視表孕讳，利用ip地址計數(shù)來求和匠楚，并倒序排列。就能將多IP登錄的員工靠前排序了厂财。
員工在多IP登錄.jpg
Done芋簿！
總結(jié)：
- 數(shù)據(jù)分析關(guān)鍵是要了解業(yè)務(wù)需求，并轉(zhuǎn)化成可操作的技術(shù)需求璃饱。
- 報表必須呈現(xiàn)關(guān)鍵信息之間的關(guān)系与斤。
- 數(shù)據(jù)清洗是非常耗時，而且這是一個根據(jù)需求反復(fù)調(diào)整的過程荚恶。
