"Shopify+Paid Social +SEO+邮件营销+数据"电商课程正在录制中..

关键词挖掘大师系列4 – Excel函数的应用(付Excel应用表单)

三月 26, 2020 by 王海峰 - [ Updated on 2020/05/08 ]

数据是SEO的核心,尤其是在处理和分析大量数据时,就需要介入Excel函数执行数据过滤功能,第一,解救需要数小时才能手动完成的任务,即提升工作效率,第二,方便我们进行数据整合和分析。

在这篇文章中,老王将介绍常见的Excel函数/公式,让你在搜索引擎优化过程中,特别是关键词数据的处理上占据主动。

此外,我将其他常用到的“Excel在SEO数据分析”的函数和它的应用整理成表格,而不是以博客文章的方式,这样的学习会更简单直接!

如何使用VLOOKUP函数

下表老王准备了模拟的原始数据:
vlookup函数

在另外一个工作表中,是残缺的数据,我要从原始数据中,找到这些缺少的数据并进行填充:
vlookup函数应用

以查找“competition”为例,首先创建一个表格,单击顶部单元格(或 CTRL + HOME),使用快捷方式:CRTL + SHIFT + DOWN 选中第一列的全部数据,向右选中其他列的数据,然后按CTRL + L创建表格。
vlookup函数创建表格

为这个表格创建名字“source”:
excel函数创建表格

原始数据表格已完成,然后同样的方式,为目标工作表创建表格,并命名为“target”(也可以保留默认命名),最后,你可以写入vloopup函数了,需要使用的是原始数据表中的第4列数据为例,在目标工作表找到第4列的第一个空白行,用此函数:=VLOOKUP(A2,source,4,0),

  • A2代表的是你要查找这个值对应的数据
  • source代表原始数据表
  • 4代表要从原始数据表中的第4列返回的值
  • 0代表完全匹配

vlookup 删除N/A值,需要使用到iferror函数: IFERROR(VLOOKUP(value,table,2,0),“message”), 这个message可以是你想设置的任何信息,如Not Found,也可以是空白信息,IFERROR(VLOOKUP(value,table,2,0),””)

如何使用数据透视表(Pivot tables)

数据透视表用于汇总、分析、浏览和呈现汇总数据
可以展开和折叠相关数据,你也可以移动这些数据对其进行排序,筛选和分组操作,从而方便你对数据进行快速的理解和展示。老王模拟了一些数据作为演示,它包括了关键词名称,排名,搜索量和URL等数据。
创建数据透视表

你也可以看到这样的表格是不直观,也不利于理解的,下一步,选中我们要处理的数据,然后点击顶部的“insert”(插入),点击选项卡最右侧的“PivotTable”(数据透视表)
pivot table

你将会在新的工作表的右侧,看到“数据透视表字段”,在这里你可以添加筛选器,列,行,和值。如果你是这样的设置,你将会看到下表的显示:
数据透视表设置

通过这样的设置,关键词的搜索量是首先按Opportunity来展示,然后按URL来展示,这些数据的显示变得更有层次,然后你可以通过点击加减符号来获得更多有序的信息。

SEO关键词数据分析中其他常用的函数整理

sumproduct

综合权重排名
关键词的总体排名表现如何去评估呢?因为关键词的排名变化并不能代表实际的因排名变化带来的结果,如果一个低搜索量的词排名下降,高搜索量的词排名上升,如果用average函数去评估,那么关键词数据有可能是下降的,但实际上,这个排名变化对网站流量的整体影响是正面的。这个时候,用sumproduct函数把关键词排名和搜索量结合起来,再除以总的搜索流量,就可以得到一个权重排名值。

Office 官方教程:SUMPRODUCT

sumifs

Excel的sumifs函数的主要作用是按多个条件对指定单元格求和,常见的应用是计算含有某个关键词的搜索之和。
函数学习链接:excel中SUMIFS函数的公式语法及使用方法实例教程, office官方教程:SUMIFS

countif

Excel中的countif函数用于统计满足某个条件的单元格的数量,常见的应用是计算含有某个关键词的出现次数。和搜索之和结合在一起分析某一个话题的热门程度和竞争程度。
函数学习链接:COUNTIF函数,你会用吗?, Office官方教程:COUNTIF

len

这个函数在关键词分析中主要的应用是计算关键词单词个数,即关键词长度,你可以通过关键词长度来进行筛选:
计算关键词长度值

提取唯一值

在数据整理归类过程中,在某一个区域内有重复值,你需要把唯一的值提取出来。如何实现?
函数学习链接:Excel中如何从列中提取唯一值的列表

总结

希望你在本篇中学习到了新的知识,能够帮助你熟练应用Excel函数,来处理和分析基础的SEO数据。老王准备了一份SEO常用Excel函数的文档,你可以下载作为手头参考。我后续将开发视频教程,带你更直观的去使用如何把excel函数相关知识点运用到seo优化上来,以及你需要掌握的excel的知识点,还应包含数据的排序和过滤,这非常重要。

下一篇,老王将继续展开关键词挖掘 – 关键词的整理和应用。

王海峰

希望能帮助你建立一套属于自己Online money making的英文网络营销系统,我在过去已经解决过的问题和相关知识点的整理,都会在这里和你分享...

发表评论

avatar
  Subscribe  
Notify of
error: 禁止转载 !
微信号:arvinwang1987