2019年11月12日 | 标签:

 

工作中会收到大量的信息和数据,里面很很多干扰的信息,我们可以通过excel去整理,使用排序,过滤筛选,获得自己想要的数据,但是这样还是不够直观,我们可以通过图表化进行表示
但是如果数据量足够的大,我们需要看的图其实也很多,但是报告的篇幅显示,我们是无法展示上百张图
这个适合就能凸显动态图表的好处
什么是动态图表呢,相比传统的静态图表,我们可以点击不同的按钮,按照我们需要的时间范围,以及数据的类型查看
举个例子
我们需要查看公司最近5年的销量,那么就是按年查看数据(x轴),y轴就是公司销售
但是我们需要按月查看x轴就要改了,甚至是按周去查看
并且我不满足按公司去看,我需要按部门去看销售,甚至是不同的小组去看
这些如果传统的只做需要上百张图
例如时间维度x轴就是 按 年,月,周,日  这4种变化
按指标维度y轴 就是,公司,部门,小组,假设公司有5个部门,每个部门有3个小组
整个数据量就是4*5*3 60多种组合  需要60个表才看看完
但是通过动态图表 我们只用做一个即可
下面我会另外起一篇 如何具体制作
本篇主要是研究了2天的心得
1,动态图表需要使用 excel开发选项卡的控件,如下拉框,滚动条等
2,动态滚动条的作用是2个,提供图表上可以点击切换的选项,比如时间下拉框里面由按 年,月,周,日,比如指标下拉框里有公司 部门,小组等,还有一个作用是反馈用户点击的选择的值,比如点了年,会输出1,点了月会输出2, 这样可以给后期使用公司判断提供参数,我们可以写if判断,根据输出的选项的值 ,生成一个为图表的数据列,这样的数据列就是可以给图表引用
3,做动态图的核心难点也最容易出错 包括我自己花了几个小时都一致晕的地方就是动态数据列
首先,图表的数据选项区域是无法使用公式,比如if或者offset,这里我至少耗费了4个小时去尝试在图表的数据选择区域如x轴区域,y轴区域去修改公式,其次,必须使用名称,并且在名称中是可以使用if或者offset公式来定义区域,这里建议新手用if比较简单,名称的名字一定不能使用数字,否则会报错,这里我也耗费了3个小时,应该不知道这个原因,一直绕,最后,单元格名词前面和名词前面,一定要使用sheet名词,并且在图表选择区域也一定要输入sheet名,这里是第3个坑 我也耗费了2个小时 老是添加不进去
注意上上面几点,剩下 就很简单,熟悉下offset公式,用它 先定位一个单元格,然后再使用它的后面的参数,生成一块区域
对于方向感差的人 就记得 先移行 再移列,再区域划定也是先划定行,再划定列
如 offset(a1,偏移行(下是加,上是减,和行号变化方向一致,偏移列,右移是加fan’zhi
工作中会收到大量的信息和数据,里面很很多干扰的信息,我们可以通过excel去整理,使用排序,过滤筛选,获得自己想要的数据,但是这样还是不够直观,我们可以通过图表化进行表示
但是如果数据量足够的大,我们需要看的图其实也很多,但是报告的篇幅显示,我们是无法展示上百张图
这个适合就能凸显动态图表的好处
什么是动态图表呢,相比传统的静态图表,我们可以点击不同的按钮,按照我们需要的时间范围,以及数据的类型查看
举个例子
我们需要查看公司最近5年的销量,那么就是按年查看数据(x轴),y轴就是公司销售
但是我们需要按月查看x轴就要改了,甚至是按周去查看
并且我不满足按公司去看,我需要按部门去看销售,甚至是不同的小组去看
这些如果传统的只做需要上百张图
例如时间维度x轴就是 按 年,月,周,日  这4种变化
按指标维度y轴 就是,公司,部门,小组,假设公司有5个部门,每个部门有3个小组
整个数据量就是4*5*3 60多种组合  需要60个表才看看完
但是通过动态图表 我们只用做一个即可
下面我会另外起一篇 如何具体制作
本篇主要是研究了2天的心得
1,动态图表需要使用 excel开发选项卡的控件,如下拉框,滚动条等
2,动态滚动条的作用是2个,提供图表上可以点击切换的选项,比如时间下拉框里面由按 年,月,周,日,比如指标下拉框里有公司 部门,小组等,还有一个作用是反馈用户点击的选择的值,比如点了年,会输出1,点了月会输出2, 这样可以给后期使用公司判断提供参数,我们可以写if判断,根据输出的选项的值 ,生成一个为图表的数据列,这样的数据列就是可以给图表引用
3,做动态图的核心难点也最容易出错 包括我自己花了几个小时都一致晕的地方就是动态数据列
首先,图表的数据选项区域是无法使用公式,比如if或者offset,这里我至少耗费了4个小时去尝试在图表的数据选择区域如x轴区域,y轴区域去修改公式,其次,必须使用名称,并且在名称中是可以使用if或者offset公式来定义区域,这里建议新手用if比较简单,名称的名字一定不能使用数字,否则会报错,这里我也耗费了3个小时,应该不知道这个原因,一直绕,最后,单元格名词前面和名词前面,一定要使用sheet名词,并且在图表选择区域也一定要输入sheet名,这里是第3个坑 我也耗费了2个小时 老是添加不进去
注意上上面几点,剩下 就很简单,熟悉下offset公式,用它 先定位一个单元格,然后再使用它的后面的参数,生成一块区域
对于方向感差的人 就记得 先移行 再移列,再区域划定也是先划定行,再划定列
如 offset(a1,偏移行(下是加,上是减,和行号变化方向一致,偏移列,右移是加fan’zhi
没有评论 (9 views)
2019年11月10日 | 标签:

 

项目目的

有一个客户的网站上有上万的页面,我们需要对所有页面进行扫描并且分析是否包含敏感词,避免被搜索引擎降权和违规风险
项目背景
客户提供了一个xml版本的 sitemap的文件,包含网站的全部URL
关于如何获得网站的全部url 可以参考这边文章  免费sitemap 工具生成10万页面XML格式网站地图
思路分析
可用利用excel简单过滤,即可得到所有网站URL,大概一万多个页面,需要通过工具或者写代码的方式抓取页面的内容,并且结合分析每个页面的内容是否包含敏感词库,所以可以分3个步骤来完成
1,获取网页的所有内容,放到excel的一列中,每行第一个单元格放url,第2个单元格放页面所有可见内容,第3个单元格用来判断是否包含敏感词
2,把敏感词库放在excel的另外一个sheet中,合并一列
3,利用公式,遍历网站的内容,并且每个页面的内容去匹配判断是否包含敏感词库里每一行结果
实施细节
Part1, 获取页面内容,
 我们本来使用python来抓取页面,但是考虑到需要大量页面的抓取,需要考虑的因素比较多,从0开始写代码 时间成本太长,需要些各种异常情况判断,如301,302,404 ,502等,并且通过python抓取的内容,需要存放在csv中,这需要额外去增加读写csv的代码,在没有熟悉成熟的框架的情况,该方法不合适(主要没实际掌握如何使用爬虫框架,只会简单语法),于是相当利用网站现场有的工具来爬取页面,免费的用的比较久的网页爬虫软件,推荐使用火车头抓取工具,老品牌,以前学习过,类似的工具应该也有,访问网址http://www.locoy.com,下载最新版本,免费版本有些限制,如不能添加抓取内容的高级规则比如正则过滤等,还有抓取的数据不能使用mysql,也不支持导出为excel,不过后面会有办法解决
下载安装好火车头爬虫后,使用抓取向导,记住使用根据url来抓取,

查看更多精彩图片

然后设定提供的url是内容页(否则会打开你提供的url吧里面所有的url都抓取,按就不是一万个页面,是好几万)然后设置抓取内容规则,
 主要先使用body 开头以及body结尾
 再添加字符串前缀补上一个<标签
 再使用去除html
 再替换所有的空格
 既可以得到全部网站文字
查看更多精彩图片
设置好爬取的速度,3秒打开一个页面然后开2个线程,同时开2个页面比较好
大概20分钟就可以把1w个页面全部抓取好
part2, 将火车头的数据导出数据到excel
我们使用的是免费的火车头版本,是不能导出数据,查询得知,她的数据文件名后缀是db3,既为sqlite格式的
查看更多精彩图片
然后我们走了很多弯路去吧db3的文件转换为excel,先使用sqlitetoexcel,根本不好用
然后使用sqlite expert也无法打开,很多软件是收费版本,无法导出
最后才知道一个波兰的良心软件 sqlite studio
https://sqlitestudio.pl/index.rvt
这个比较好,可以支持打开db3文件并且导出为csv
然后我打开以后是乱码,没关系 使用记事本打开 另存为utf8
查看更多精彩图片
然后就可以复制到excel中
至此,即可完成抓取整站内容到excel的工作
查看更多精彩图片

接下来就是简单的excel公式通过小找到去匹配关键词库,是否包含即可
excel的公式方法在前面有讲到
http://cotine.blog.hexun.com/115744120_d.html
使用的是模糊匹配(大匹配小)

项目目的

有一个客户的网站上有上万的页面,我们需要对所有页面进行扫描并且分析是否包含敏感词,避免被搜索引擎降权和违规风险
项目背景
客户提供了一个xml版本的 sitemap的文件,包含网站的全部URL
关于如何获得网站的全部url 可以参考这边文章  免费sitemap 工具生成10万页面XML格式网站地图
思路分析
可用利用excel简单过滤,即可得到所有网站URL,大概一万多个页面,需要通过工具或者写代码的方式抓取页面的内容,并且结合分析每个页面的内容是否包含敏感词库,所以可以分3个步骤来完成
1,获取网页的所有内容,放到excel的一列中,每行第一个单元格放url,第2个单元格放页面所有可见内容,第3个单元格用来判断是否包含敏感词
2,把敏感词库放在excel的另外一个sheet中,合并一列
3,利用公式,遍历网站的内容,并且每个页面的内容去匹配判断是否包含敏感词库里每一行结果
实施细节
Part1, 获取页面内容,
 我们本来使用python来抓取页面,但是考虑到需要大量页面的抓取,需要考虑的因素比较多,从0开始写代码 时间成本太长,需要些各种异常情况判断,如301,302,404 ,502等,并且通过python抓取的内容,需要存放在csv中,这需要额外去增加读写csv的代码,在没有熟悉成熟的框架的情况,该方法不合适(主要没实际掌握如何使用爬虫框架,只会简单语法),于是相当利用网站现场有的工具来爬取页面,免费的用的比较久的网页爬虫软件,推荐使用火车头抓取工具,老品牌,以前学习过,类似的工具应该也有,访问网址http://www.locoy.com,下载最新版本,免费版本有些限制,如不能添加抓取内容的高级规则比如正则过滤等,还有抓取的数据不能使用mysql,也不支持导出为excel,不过后面会有办法解决
下载安装好火车头爬虫后,使用抓取向导,记住使用根据url来抓取,

查看更多精彩图片

然后设定提供的url是内容页(否则会打开你提供的url吧里面所有的url都抓取,按就不是一万个页面,是好几万)然后设置抓取内容规则,
 主要先使用body 开头以及body结尾
 再添加字符串前缀补上一个<标签
 再使用去除html
 再替换所有的空格
 既可以得到全部网站文字
查看更多精彩图片
设置好爬取的速度,3秒打开一个页面然后开2个线程,同时开2个页面比较好
大概20分钟就可以把1w个页面全部抓取好
part2, 将火车头的数据导出数据到excel
我们使用的是免费的火车头版本,是不能导出数据,查询得知,她的数据文件名后缀是db3,既为sqlite格式的
查看更多精彩图片
然后我们走了很多弯路去吧db3的文件转换为excel,先使用sqlitetoexcel,根本不好用
然后使用sqlite expert也无法打开,很多软件是收费版本,无法导出
最后才知道一个波兰的良心软件 sqlite studio
https://sqlitestudio.pl/index.rvt
这个比较好,可以支持打开db3文件并且导出为csv
然后我打开以后是乱码,没关系 使用记事本打开 另存为utf8
查看更多精彩图片
然后就可以复制到excel中
至此,即可完成抓取整站内容到excel的工作
查看更多精彩图片

接下来就是简单的excel公式通过小找到去匹配关键词库,是否包含即可
excel的公式方法在前面有讲到
http://cotine.blog.hexun.com/115744120_d.html
使用的是模糊匹配(大匹配小)
没有评论 (25 views)
2019年11月9日 | 标签:

 

今天在处理Excel碰到一个很奇葩棘手的问题,一个空白单元簿 明明没有任何内容,但是缺有100多万行

导致整个excel文件非常大,虽然可以删除整个sheet来解决 本着刨根问底的精神以及如果这个表格里面有重要
信息不能删除 但是又发现表格内容很大 必须要处理的目的
经过网上搜索,试过了以下办法
使用快捷键ctr+shif+下  选择下面所有的行 去掉格式,删除行
都无法奏效
也试过定位单元格 查看是否含有隐藏的对象等也找不到
最后还是在excelhome里面的大神看到
把所有的行高度设置为14.25 按下保存即可
果然就正常, 不得不佩服大神啊
总结
excel文件比较大,一定是有大量空白单元行,需要删除
传统的办法是去掉格式,在删除行即可(按下ctr+下即可知道是否删完)
但是有时候 如果设置了行高,这种办法是无效的 必须要把行高设置回默认值 行高14.25 或者查看新的表格的行高和列宽
这样excel才会 聪明的知道 这个单元格是没东西的(否则他会对行高恋恋不忘)
查看更多精彩图片

查看更多精彩图片

查看更多精彩图片

查看更多精彩图片

得到

今天在处理Excel碰到一个很奇葩棘手的问题,一个空白单元簿 明明没有任何内容,但是缺有100多万行

导致整个excel文件非常大,虽然可以删除整个sheet来解决 本着刨根问底的精神以及如果这个表格里面有重要
信息不能删除 但是又发现表格内容很大 必须要处理的目的
经过网上搜索,试过了以下办法
使用快捷键ctr+shif+下  选择下面所有的行 去掉格式,删除行
都无法奏效
也试过定位单元格 查看是否含有隐藏的对象等也找不到
最后还是在excelhome里面的大神看到
把所有的行高度设置为14.25 按下保存即可
果然就正常, 不得不佩服大神啊
总结
excel文件比较大,一定是有大量空白单元行,需要删除
传统的办法是去掉格式,在删除行即可(按下ctr+下即可知道是否删完)
但是有时候 如果设置了行高,这种办法是无效的 必须要把行高设置回默认值 行高14.25 或者查看新的表格的行高和列宽
这样excel才会 聪明的知道 这个单元格是没东西的(否则他会对行高恋恋不忘)
查看更多精彩图片

查看更多精彩图片

查看更多精彩图片

查看更多精彩图片

得到

没有评论 (13 views)