excel分析数据:拆分,统计词根频率,总搜索量

最早是看闯哥的一篇文章,是关于关键词数据分析的,方法使用shell实现的.本渣渣不会shell,下面用excel实现与之相同的效果.示范用关键词少,只用与示范作用.
闯哥最终效果图如下所示:
excel分析数据:拆分,统计词根频率,总搜索量

最终实现的效果就是关键词前缀,后缀,前缀后缀对应的搜索量,前缀后缀出现的次数.
思路:
1.先利用5118,凤巢搞一批词,然后利用excel降序.
2.然后提取词根的前缀后缀
3.计算前缀后缀的出现次数.
4.计算前缀后缀的总搜索量.
具体流程:
.关键词挖掘不讲,直接自己搞.
一.关键词前后缀提取
关键词降序排好,需要提取关键词的前缀后缀.
如下图:刚下载的关键词搜索量是在关键词的右边的,现将其放到关键词的左边,主要是提取前缀后缀防止影响后面列的数据变动.
excel分析数据:拆分,统计词根频率,总搜索量

然后选中关键词b列,然后ctrl+f,替换词根为*,我这边词根为租赁,那么处理后为如下图
excel分析数据:拆分,统计词根频率,总搜索量

然后点击excel的数据-分列,选择分隔符号,默认就是分割符号,然后下一步,如下图:
excel分析数据:拆分,统计词根频率,总搜索量

然后选择其他,填*号,如下图
excel分析数据:拆分,统计词根频率,总搜索量

最后点击完成即可,这时关键词前缀后缀就被分开了,如下图:
excel分析数据:拆分,统计词根频率,总搜索量

这时可以将b列标题设置为前缀,c列设置为后缀
excel分析数据:拆分,统计词根频率,总搜索量

二.关键词频率计算
接下来就是要要分别计算前缀后缀的出现的次数了,也就是所谓的频率
操作流程
直接复制b列数据,然后将其粘贴到sheet2中,选中后点击 数据-删除重复项.
excel分析数据:拆分,统计词根频率,总搜索量

删除完成由于b列中用空行,然后降序排列一下去除空格,这时候相当于b列(前缀)的词根出来了.
如下图
excel分析数据:拆分,统计词根频率,总搜索量

接下来先将b列降序去除前缀空格,最后将刚才去重的数据复制到sheet1的d列,作为计算的前缀的词根,
excel分析数据:拆分,统计词根频率,总搜索量

计算前缀频率用到excel countif函数,在E2列输入=COUNTIF(B2:B727,D2) ,然后下拖即可,直到拖到d列最后.
其中B2:B727为前缀关键词的查询范围,D2为查找的关键词.效果如图,这时候前缀的关键词的频率就被计算出来了.
excel分析数据:拆分,统计词根频率,总搜索量

前缀计算完了就需要计算后缀的频率了,操作步奏同计算前缀频率一样,这里不再演示.
三.前缀,后缀频率计算出来了,就剩下计算前缀,后缀的总搜索量了.
计算总搜索量需要用到excel sumif函数,具体操作流程如下:
excel分析数据:拆分,统计词根频率,总搜索量

在f2输入=SUMIF(B2:B727,D2,A2:A727),然后下拖到前缀词根末尾,这时候前缀词根的总搜索量就计算完成了,
excel分析数据:拆分,统计词根频率,总搜索量

比如深圳电脑总搜索量为11,频率是6,那么我们检验一下:
excel分析数据:拆分,统计词根频率,总搜索量

在b列前缀搜索深圳电脑.如下图所示:频率为6,总搜索量是11.证明是正确的.
excel分析数据:拆分,统计词根频率,总搜索量

发表评论

电子邮件地址不会被公开。 必填项已用*标注