有天，一位做IT运维的朋友小A请我吃饭，还没上菜就开始吐槽他老板。我就边吃边了解情况。
原来小A的公司部署了审计设备，所有员工访问公司文件的行为都会有日志记录。老板突然有一天想要看看这个审计报告，小A就给从设备上导出日志发给老板。结果被老板一顿屌！
我让小A给我看了他发的内容：
用户上传下载文件日志.jpeg
我心想：“老板们都是日理万机，每天心急火燎的，哪有心情一条条看这个东西。”
着急上火.png
我跟小A说：“其实我们只要给老板需要的关键信息即可！你知道老板最关注哪些事情吗？”
小A：“他最关心公司资料的保密性了。他之前问过我几个问题，我当时没有统计清楚，就没回答上来。”
我放下筷子说：“好，既然吃了你这顿饭，就帮你找答案吧！”
第二天，我做了3张报表发给小A。并跟他说：“其实老板们最爱看图表，但是时间不够。你先用这几张表回复老板提的问题，让他心里有个底。以后再用图表呈现，做到数据可视化！”
按项目查看，用户访问资料记录.png
按文件名查看，用户访问资料记录.png
多IP登陆访问的用户.png
小A看了看效果问：“这是怎么统计出来的！教教我！请你喝咖啡！”
我说：“简单，都有套路！”
教你个套路.png
下面就来讲解一下操作过程
整个流程分为5步：找出问题（需求）-> 理解数据 -> 数据清洗 -> 构建模型 -> 数据可视化
第一步：找出问题（需求）
这个环节是定义老板或用户的需求，将业务需求转化为技术需求，并且要和当事人确认。以避免劳而无功！
这里老板关注的谁动了他的文件，可以转化为以下要输出的报表：
- 哪些项目文件，访问的次数最多，按降序排序。（见图：“按项目查看，用户访问资料记录.png”）
- 哪些具体的文件，访问的次数最多，按降序排序。（见图：“按文件名查看，用户访问资料记录.png”）
- 有哪些员工，在不同的IP地址访问过文件。（见图：“多IP登陆访问的用户.png”）
第二步：理解数据
我们拿到数据源文件后，开始分析其中包含哪些表格、字段、数据类型等，以便执行后续的数据处理。
该日志文件按照7种文件类型，并按照上传和下载的访问操作，分成了不同的sheet表格。其中包含了：客户图档、零件图档、工程图档、DFM等。
每个sheet表格中，包含序号、专案、文件名称、工号、姓名、事件时间、IP等字段。
第三步：数据清洗
这是最花时间的环节，我们会使用如下的处理方法，直到将源数据清洗成能做报表的样子：
- 选择子集
- 列名重命名
- 删除重复值
- 缺失值处理
- 一致化处理
- 数据排序
- 异常值处理
具体操作：
（在操作前，先复制一份源文件，并命名为“日志-数据清洗.xlsx”。如果有误操作，还能恢复数据。）
- 选择子集：
我们先新建一个汇总的sheet，并将其重命名为“资料上传下载记录汇总表”。表格第一行输入【序号】、【专案】、【文件名称】等”字段，将所有日志sheet都复制黏贴到一个sheet中。注：因为要将不同sheet的数据合并到一个汇总表中，必须新增一个【文件操作记录类别】的字段，用以记录数据来源哪一个类别
选择子集.png
-
缺失值处理：
汇总完成后，我发现有些IP是空值。为了做报表时，方便计数。我们将这些空值都填充为“无记录”的字符串。
我们选中IP这一列，然后点击 开始 选项卡下面的 查找和选择 ，点击 定位条件 （快捷键Ctrl+G），在 定位条件 中选中 空值(K) ，确定。
这时候，IP这列的所有空值都会被选中，我们在第一个空值处，输入“无记录”，然后快捷键Ctrl+enter，其他空值就都会被填充“无记录”。 -
一致化处理：
在报表中我们还需要展示文件上传或下载的次数，所以需要新增一个字段【文件访问的方式】。
那如何获取上传还是下载的数据呢？
这里我们使用MID和FIND函数，截取【文件操作记录类别】字段中的“记录”前面的两个文字。
截取所需字段.png
到这里，这个元数据的清洗工作基本完成~
第四步：构建模型
既然磨具都准备好了，就可以开始搭积木了。one by one！
- 员工访问次数，按照项目排序。
-
插入透视表：在“资料上传下载记录汇总表”中，点击 插入 选项卡下面的 数据透视表 ，选中 新工作表(N) ，确定。
插入数据透视表.png
-
设置透视表：在新建的数据透视表中，将【专案】字段拖入 行标签 区域，将【文件访问方式】字段拖入 列标签 区域，将【工号】字段再拖入 值标签 区域。
-
设置排序：在左侧 行标签 点击右键，点击 其他排序选项，然后，按照工号的计数降序排序。
点击其他排序选项.png
按照工号的计数降序排序.png
可以看到报表，呈现结果如下：
员工访问文件次数，按项目排序.png
-
员工访问文件次数，按照文件名排序。（方法同上，只是 行标签 区域里面的字段改为【文件名称】）
-
员工在多IP地址访问文件的排序。（方法同上，将【工号】、【姓名】、【IP】的字段拖入 行标签 区域，将【文件访问方式】字段拖入 列标签 区域，将【IP】字段再拖入 值标签 区域）。报表呈现员工名下登陆的IP和计数。
员工多IP访问记录.png
可是报表好像跟需求有点不符。老板关心的哪些员工在多个IP登陆访问，而不是要统计访问的次数，能不能让在多个IP访问的员工在表格中排序靠前呢？
想到一个思路：我们找出每个员工对应每个不同的IP，在表格中出现的次数，就可以汇总排序了。
看来之前的数据清洗工作还不完全！
让我们继续第三步：数据清洗（补）
- 删除重复值：
为了不影响其他报表，我们将汇总表复制成一个新的sheet，命名为“资料上传下载记录汇总表 (2)”。
-
先将原数据的，员工列和ip地址列合并成一列。
合并（工号-姓名-IP）.png
-
然后删除 合并（工号-姓名-IP）列 的重复项。（这时候，员工对应的ip地址，数据都是唯一了）
删除重复值.png
-
再插入数据透视表，利用ip地址计数来求和，并倒序排列。就能将多IP登录的员工靠前排序了。
员工在多IP登录.jpg
Done！
总结：
- 数据分析关键是要了解业务需求，并转化成可操作的技术需求。
- 报表必须呈现关键信息之间的关系。
- 数据清洗是非常耗时，而且这是一个根据需求反复调整的过程。
网友评论