誰動了你的文件 -- 使用「透視表」分析文件訪問日誌

有天，一位做IT運維的朋友小A請我吃飯，還沒上菜就開始吐槽他老闆。我就邊吃邊了解情況。

原來小A的公司部署了審計設備，所有員工訪問公司文件的行為都會有日誌記錄。老闆突然有一天想要看看這個審計報告，小A就給從設備上導出日誌發給老闆。結果被老闆屌了一頓！

我讓小A給我看了他發的內容：

我心想：「老闆們都是日理萬機，每天心急火燎的，哪有心情一條條看這個東西。」

我就跟小A說：「其實我們只要給老闆需要的關鍵信息即可！你知道老闆最關注哪些事情嗎？」

小A：「他最關心公司資料的保密性了。他之前問過我幾個問題，我當時沒有統計清楚，就沒回答上來。」

我放下筷子說：「好，既然吃了你這頓飯，就幫你找答案吧！」

第二天，我做了3張報表發給小A。並跟他說：「其實老闆們最愛看圖表，但是時間不夠。你先用這幾張表回復老闆提的問題，讓他心裡有個底。以後再用圖表呈現，做到數據可視化！」

小A看了看效果問：「這是怎麼統計出來的！教教我！請你喝咖啡！」

我說：「簡單，都有套路！」

下面就來講解一下操作過程

整個流程分為5步：找出問題（需求）-> 理解數據 -> 數據清洗 -> 構建模型 -> 數據可視化*

第一步：找出問題（需求）

這個環節是定義老闆或用戶的需求，將業務需求轉化為技術需求，並且要和當事人確認。以避免勞而無功！

這裡老闆關注的誰動了他的文件，可以轉化為以下要輸出的報表：

1. 哪些項目文件，訪問的次數最多，按降序排序。（見圖：「按項目查看，用戶訪問資料記錄.png」）

2. 哪些具體的文件，訪問的次數最多，按降序排序。（見圖：「按文件名查看，用戶訪問資料記錄.png」）

3. 有哪些員工，在不同的IP地址訪問過文件。（見圖：「多IP登陸訪問的用戶.png」）

第二步：理解數據

我們拿到數據源文件後，開始分析其中包含哪些表格、欄位、數據類型等，以便執行後續的數據處理。

該日誌文件按照7種文件類型，並按照上傳和下載的訪問操作，分成了不同的sheet表格。其中包含了：客戶圖檔、零件圖檔、工程圖檔、DFM等。

每個sheet表格中，包含序號、專案、文件名稱、工號、姓名、事件時間、IP等欄位。

第三步：數據清洗

這是最花時間的環節，我們會使用如下的處理方法，直到將源數據清洗成能做報表的樣子：

• 選擇子集
• 列名重命名
• 刪除重複值
• 缺失值處理
• 一致化處理
• 數據排序
• 異常值處理

具體操作：

（在操作前，先複製一份源文件，並命名為「日誌-數據清洗.xlsx」。如果有誤操作，還能恢複數據。）

1. 選擇子集：

我們先新建一個匯總的sheet，並將其重命名為「資料上傳下載記錄匯總表」。表格第一行輸入【序號】、【專案】、【文件名稱】等」欄位，將所有日誌sheet都複製黏貼到一個sheet中。註：因為要將不同sheet的數據合併到一個匯總表中，必須新增一個【文件操作記錄類別】的欄位，用以記錄數據來源哪一個類別

2. 缺失值處理：

匯總完成後，我發現有些IP是空值。為了做報表時，方便計數。我們將這些空值都填充為「無記錄」的字元串。

我們選中IP這一列，然後點擊 開始 選項卡下面的 查找和選擇，點擊 定位條件 （快捷鍵Ctrl+G），在 定位條件 中選中 空值(K) ，確定。

這時候，IP這列的所有空值都會被選中，我們在第一個空值處，輸入「無記錄」，然後快捷鍵Ctrl+enter，其他空值就都會被填充「無記錄」。

3. 一致化處理：

在報表中我們還需要展示文件上傳或下載的次數，所以需要新增一個欄位【文件訪問的方式】。

那如何獲取上傳還是下載的數據呢？

這裡我們使用MID和FIND函數，截取【文件操作記錄類別】欄位中的「記錄」前面的兩個文字。

到這裡，這個元數據的清洗工作基本完成~

第四步：構建模型

既然磨具都準備好了，就可以開始搭積木了。one by one！

1. 員工訪問次數，按照項目排序。

• 插入透視表：在「資料上傳下載記錄匯總表」中，點擊 插入 選項卡下面的 數據透視表 ，選中 新工作表(N) ，確定。

• 設置透視表：在新建的數據透視表中，將【專案】欄位拖入 行標籤 區域，將【文件訪問方式】欄位拖入 列標籤 區域，將【工號】欄位再拖入 值標籤 區域。

* 設置排序：在左側 行標籤 點擊右鍵，點擊 其他排序選項，然後，按照工號的計數降序排序。

可以看到報表，呈現結果如下：

2. 員工訪問文件次數，按照文件名排序。（方法同上，只是 行標籤 區域裡面的欄位改為【文件名稱】）

3. 員工在多IP地址訪問文件的排序。（方法同上，將【工號】、【姓名】、【IP】的欄位拖入 行標籤 區域，將【文件訪問方式】欄位拖入 列標籤 區域，將【IP】欄位再拖入 值標籤 區域）。報表呈現員工名下登陸的IP和計數。

可是報表好像跟需求有點不符。老闆關心的哪些員工在多個IP登陸訪問，而不是要統計訪問的次數，能不能讓在多個IP訪問的員工在表格中排序靠前呢？

想到一個思路：我們找出每個員工對應每個不同的IP，在表格中出現的次數，就可以匯總排序了。

看來之前的數據清洗工作還不完全！

讓我們繼續第三步：數據清洗（補）

4. 刪除重複值：

為了不影響其他報表，我們將匯總表複製成一個新的sheet，命名為「資料上傳下載記錄匯總表 (2)」。

• 先將原數據的，員工列和ip地址列合併成一列。

• 然後刪除 合併（工號-姓名-IP）列 的重複項。（這時候，員工對應的ip地址，數據都是唯一了）

• 再插入數據透視表，利用ip地址計數來求和，並倒序排列。就能將多IP登錄的員工靠前排序了。

Done！

總結：

1. 數據分析關鍵是要了解業務需求，並轉化成可操作的技術需求。

2. 報表必須呈現關鍵信息之間的關係。

3. 數據清洗是非常耗時，而且這是一個根據需求反覆調整的過程。

推薦閱讀：