我相信你们大多数人都对Sheets有点熟悉（如果不熟悉，它基本上就像Excel一样，但是基于云计算并且完全免费），并且知道它在协作方面有多么强大。
但是，它的能力远远超出了合作。
谷歌Sheets可以用来从网站上抓取数据，创建半自动化的SEO工作流程，操作大数据集（例如Site Explorer导出），自动跟踪推广活动，等等。
在这篇文章中，我将向你介绍10个谷歌表格公式，并向你展示如何将它们用于日常的SEO任务。
让我们从基础开始……
在这个简短的章节中，我将分享三个必须知道的基本公式。
无论我在b谷歌Sheets做什么样的SEO工作，我发现自己几乎每次都在使用这三个公式：
如果;
IFERROR;
ARRAYFORMULA
让我们从IF语句开始。
这非常简单；它用来检查一个条件是真还是假。
语法：=IF（condition, value_if_true, value_if_false）
以下是一个示例电子表格，其中包含关键字列表及其各自的估计搜索量（注意：这些是使用关键字资源管理器收集的）：
让我们假设，假设，我们有一个强大的网站能够排名第一的任何这些关键字。然而，我们只想追求那些可能每月带来500+访问者的关键字（假设我们排名第一）。
根据这项研究，美国排名第一（仅限桌面搜索）的点击率约为29%。
旁注。我们不建议依靠这种方法来估计搜索流量，因为CTR在不同的查询、设备等方面差异很大。这就是为什么我们使用点击流数据来估计流量。
因此，让我们编写一个IF语句，对于可能带来500+访问者的关键字返回“GOOD”（即29%的搜索量大于或等于500），其余的返回“BAD”。
公式如下：
=IF(B2*0.29>=500,“GOOD“,“BAD“)
这是它的作用（用简单的英语）：
它会检查如果B2*0.29（即搜索量的29%）大于等于500；
如果的条件是真正的，则返回“GOOD”。如果这是假，则返回“BAD”。
这对于我们当前的数据集来说非常有效，但看看当我们将一些非数值放入混合时会发生什么：
这是一个错误。
这是因为不可能将非数值乘以0.29（很明显）。
旁注。我添加了一些条件格式，因此，IF语句的计算结果为TRUE的地方，单元格都以绿色突出显示。如果语句的计算结果为FALSE，它们将被突出显示为红色。
这就是IFERROR派上用场的地方。
如果公式出现错误，IFERROR允许您设置一个默认值。
语法：=IFERROR（original_formula, value_if_error）
让我们将其合并到上面的示例中（如果有错误，我们将保留单元格空白），看看会发生什么：
完美——这就是完整的公式！
好的，如果您只处理少量的数据，可以直接跳到下一节。
但是，鉴于本指南是针对SEO的，我将假设您正在定期处理相当大的数据量。
如果是这种情况，我冒昧地猜测您花了太多时间在数百甚至数千个单元格中拖拽公式。
输入:ARRAYFORMULA。
语法:= ARRAYFORMULA (array_formula)
基本上，ARRAYFORMULA将原始公式转换为数组，从而允许您只需编写一个公式就可以跨多行迭代相同的公式。
因此，让我们删除单元格B2中的所有公式，并将当前在单元格B1中的整个公式包装在ARRAYFORMULA中，如下所示：
= ARRAYFORMULA (IFERROR(如果(B2:架B29 * 0.29 > = 500上“好”、“坏”),“ “))
魔法。
这是基本的内容；让我们看一些更有用的公式。
旁注。下面是一个电子表格，展示了这些公式是如何工作的( 注意:包含公式的单元格将以黄色突出显示)。我将在整个帖子中包括更多这些电子表格。
1. 使用REGEXTRACT从字符串中提取数据
REGEXTRACT使用正则表达式从字符串或单元格中提取子字符串。
语法：=REGEXEXTRACT（text, regular_expression）
以下是一些潜在的用例：
从url列表中提取域名（继续阅读以查看示例！）；
提取URL（即不带根域）；
检查URL是否使用HTTP或HTTPS；
从大量文本中提取电子邮件地址；
从url列表中识别包含/不包含特定单词的url（例如包含“/category/guest-post”标记的url）。
让我们假设我们想要从“为我们写”页面url列表中提取根域（即嘉宾帖子机会）。
在B列中，我们可以编写一个REGEXTRACT公式来执行此操作。
这是我们需要的正则表达式语法:^ (?:https ?:\/\/)?(?:[^@\ n] + @) ? (?: www \.)?([^:\/\ n] +)
旁注。如果您不熟悉regex（别担心，我也不擅长），您有两个选择：(I)学习基础知识-查看Regexr.com （ii）谷歌解决方案，无论您需要什么-认真地说，您可以用一点Google找到令人惊讶的东西！
这是我们的最终公式：
= REGEXEXTRACT (A2,“^ (?:https ?:\/\/)?(?:[^@\ n] + @) ? (?: www \.)?([^:\/\ n] +)”)
把它粘贴到单元格B2中，很快，我们就提取出了定义域。
让我们将其封装在ARRAYFORMULA和IFERROR中，以完成整个列。
= IFERROR (ARRAYFORMULA (REGEXEXTRACT (A2:,“^ (?:https ?:\/\/)?(?:[^@\ n] + @) ? (?: www \.)?([^:\/\ n ]+)“)),““)
旁注。下面的电子表格展示了这个公式是如何工作的。
2. 将字符串拆分为多个数据点
SPLIT使用分隔符将字符串分割成多个片段。
语法：=SPLIT（text, delimiter）
以下是一些潜在的用例：
将潜在客户的全名分成“名”和“姓”两列；
将URL拆分为HTTP协议、根域和URL段的3列；
将逗号分隔值的列表拆分为多个列；
将根域拆分为域名和域名扩展（例如。com, .org等）的2列。
我在电子表格中列出了SEO研究院的团队成员（全名）。
下面是一个简单的SPLIT公式，我们可以在单元格B2中使用，将这些划分为名字和姓氏：
=分裂(A2, “ “)
旁注。我们使用空格（即“”）作为分隔符，因为它告诉SPLIT公式在哪里分割字符串。
同样，让我们将其封装在IFERROR和ARRAYFORMULA中，以便用单个公式拆分整个列表。
= IFERROR (ARRAYFORMULA(分裂(A2: ,“ “)),““)
下面是另一个示例公式，将根域拆分为站点名称和域扩展名：
=分裂(A2,“。”)
旁注。这是电子表格。
3. 使用VLOOKUP合并多个数据集
VLOOKUP允许您使用搜索键搜索区域，然后可以从该区域中的特定单元格返回匹配的值。
语法：=VLOOKUP（search_key, range, index_key）
以下是一些潜在的用例：
合并来自多个来源的数据（例如，合并来自单独表格的具有相应SEO研究院 DR评级的域列表）；
检查某个值是否存在于另一个数据集中（例如，检查两个或多个外展前景列表中的重复值）；
将电子邮件地址（从联系人的主数据库中）与潜在客户列表一起拉进来。
让我们假设我们有一个潜在客户列表（即一群人链接到竞争对手的网站，从站点浏览器中提取）。我们也有一个主数据库的联系信息（即电子邮件地址）在另一个电子表格。
Site Explorer导出（注意：我删除了这里的许多列，因为这个示例不需要很多数据）。
主联系人数据库-这是我们将使用VLOOKUP函数查询的数据库。
旁注。我在Site Explorer导出表中添加了两个新的（空的）VLOOKUP数据列（即全名和电子邮件）。这将在接下来的几个屏幕截图中显示。
我们不想浪费时间查找已经拥有的联系信息，所以让我们使用VLOOKUP来查询主数据库，看看我们是否已经拥有这些潜在客户的联系信息。
下面是我们要用到的公式：
=VLOOKUP（D2:D,‘Master contact database‘）
好，让我们对电子邮件列做同样的处理；我们还将这两个公式包装在IFERROR和ARRAYFORMULA中。
=IFERROR（ARRAYFORMULA(VLOOKUP(D2:D,‘Master contact database‘!A:C,3)),““）
旁注。这是电子表格。
4. 使用IMPORTXML从任何网站抓取数据
IMPORTXML允许您从许多结构化数据类型（包括XML、HTML和RSS等）导入数据（使用XPath查询）。
换句话说，你可以在不留下谷歌页的情况下刮网！
语法：=IMPORTXML（url, xpath_query）
以下是一些潜在的用例：
从url列表中抓取元数据（例如标题、描述、h标签等）；
从网页中抓取电子邮件地址；
从网页上抓取社交资料（例如Facebook）；
从RSS提要中抓取lastBuildDate（这是一种非常狡猾的方法，可以看到网站最近更新了多少，甚至不需要加载网站！）
让我们假设我们想要抓取关于关键词研究的文章的元标题。
我们可以在HTML中看到，元标题是：“如何在2017年做关键词研究- SEO研究院指南”。
我们用来获取元标题的XPath查询非常简单：“ //title ”
公式如下：
= IMPORTXML (“ https://ahrefs.com/blog/keyword-research/ “, / /标题)
也可以在公式中使用单元格引用；这使得为一堆url抓取数据变得超级简单。
旁注。不幸的是，IMPORTXML不能与ARRAYFORMULA一起工作，所以需要手动拖拽这个。
IMPORTXML也不局限于抓取基本的元标记；它几乎可以用来刮任何东西。这只是一个了解XPath的例子。
下面是一些可能有用的XPath公式：
提取页面上的所有链接： “ / / @href”；
提取页面上的所有内部链接： “ / /(包含(@href, ‘ domain.com ‘)] / @href”；
提取页面上的所有外部链接： “ / /[不是(包含(@href, ‘ domain.com ‘))) / @href”；
提取元描述： “ / /元[@ name = ‘描述‘]/ @content”；
提取H1: “ / / h1”；
从网页摘录电邮地址： “ / /(包含(@href mailTo:)或包含(@href mailTo:”)]/ @href”；
提取社交档案（如LinkedIn， Facebook, Twitter）： “//a[包含（@href, ‘linkedin.com/in‘）或包含（@href, ’twitter.com/‘）或包含（@href, ’facebook.com/‘）]/@href”；
提取lastBuildDate（从RSS提要）： “ / / lastBuildDate”
您可以通过以下操作（在Chrome中）找到任何元素的XPath：
右键单击>检查>右键单击>复制>复制XPath
旁注。这是电子表格（这里有很多例子：D）
5. 搜索特定值的字符串
SEARCH允许您检查值是否存在于字符串中；然后返回在字符串中第一次找到该值的位置。
语法：=SEARCH（search_query, text_to_search）
下面是一些用例：
检查URL中是否存在特定的子域（这对于批量分类URL列表很有用）；
将关键词按不同的意图分类（如品牌、商业等）；
在URL中搜索特定的、不需要的字符；
在URL中搜索某些单词/短语来对链接前景进行分类（例如“/category/guest-post”，“resources.html”等）。
让我们看一个实际的SEARCH示例。
以下是SEO研究院.com上最热门的300多个页面列表（注：我使用了站点浏览器来收集这些数据）：
旁注。我通过删除一些列来清理上面截图中的数据；实际上，站点浏览器提供给你的信息远不止这些（例如，每个URL的热门关键字、流量、搜索量、位置等）。
URL中带有/blog/的所有页面都是博客文章。假设我想在内容审计期间将这些页面标记为“Blog post”。
SEARCH（与IF语句结合使用——这在本指南前面讨论过）可以在几秒钟内完成；公式如下：
=如果(搜索(“/博客/ A2),“是的”,“”)
让我们将其包装在IFERROR和ARRAYFORMULA中以使其更整洁。
以下是其他一些有用的公式：
在url列表中查找“为我们写作”页面： =如果(搜索(“/ write-for-us / A2),“编写页面”,“”)；
在url列表中查找资源页面： =如果(搜索(“/ resources.html”A2),“资源页面”,“”)；
查找品牌搜索词（在关键字列表中）： =如果(搜索(“brand_name A2),“品牌关键字”,“”)；
识别内部/外部链接（从出站链接列表中）：=IF（SEARCH(“yourdomain.com“,A2),”内部链接”，“外部链接”）；
旁注。这是电子表格（这里也有几个例子！）
6. 使用IMPORTRANGE从其他电子表格导入数据
IMPORTRANGE允许您从任何其他谷歌表导入数据。
它也不一定要在你的谷歌Drive上；它可能属于其他人（注意：如果是这种情况，您需要获得访问该表单的许可！）
语法：=IMPORTRANGE（spreadsheet_ID, range_to_import）
以下是一些用例：
创建基于“主”电子表格的面向客户的表格；
搜索和交叉参考数据跨多个谷歌表（即使用IMPORTRANGE结合vlookup）；
从另一个表中拉入数据用于数据验证；
使用vlookup从“主”电子表格中提取联系人数据
让我们来看一个importange的例子。
下面是一个假设的SEO客户和他们的预算列表：
让我们假设我想在另一个谷歌表中使用这个客户端列表-我可以使用以下公式导入整个数据范围：
= IMPORTRANGE(“SPREADSHEET_KEY”,“‘SheetName ! A2:”)
旁注。在这里可以找到你的电子表格键。
我们还假设您在主电子表格中记录为这些客户构建的链接；在一列中，您有链接URL，在另一列中，您想要记录该链接用于哪个客户端。
你可以使用IMPORTRANGE创建一个包含所有客户端数据验证的下拉列表，如下所示：
当你从主电子表格中添加/删除客户端时，这个下拉菜单会自动更新。
旁注。这是主要的电子表格（和数据电子表格）。
7. 使用SQL查询查询数据集（这个查询功能非常强大！）
QUERY就像打了兴奋剂的VLOOKUP。它允许您使用SQL查询数据，这允许您在数据查询/检索时获得超粒度。
语法：=QUERY（range, sql_query）
以下是一些用例：
查询主链接前景数据库中特定的前景（例如，只查找标记为客人职位机会的前景，DR超过50，并提供联系方式）；
创建超细粒度的面向客户的文档，从“主”电子表格中提取数据；
查询大规模的现场审计，只挑出需要注意的页面。
让我们回到我们的标签“博客文章”列表。
如果我们想把所有标记为“blog post”的url放到一个全新的电子表格中，我们可以使用这个QUERY函数：
=查询(数据!A:B,“select A where B = ‘Blog Post‘“)
旁注。这告诉电子表格选择A列中B列=“Blog Posts”的所有值。
但是假设我们有一个更大的数据集。也许是从站点浏览器导出文件。
这些导出文件的数据量可能相当大，所以让我们假设我们想要提取具有以下属性的所有引用页面的列表：
Dofollow联系;
bbbb50；
反向链接状态=活动（即未标记为“已删除”）；
外部链接计数< 50；
公式如下：
=QUERY(‘DATA - site explorer export‘！2:R,“SELECT E where D b> 50 AND H < 50 AND M = ‘Dofollow‘ AND N < b> ‘REMOVED‘“)
注意：也可以将IMPORTRANGE合并到QUERY函数中；这允许您从其他工作表查询数据。
旁注。这是电子表格。
最终的想法
谷歌Sheets非常强大；这篇文章只触及了你能用它做什么的表面。
我建议你尝试一下上面的公式，看看你能得出什么。我还建议您查看谷歌表单公式的完整库。
但是，这仅仅是个开始：谷歌Sheets还集成了Zapier和IFTTT，这意味着你也可以连接数百个其他工具和服务。
如果你想获得真正的高级，看看Apps script——它非常强大！
如果你对谷歌表单有任何创造性的使用，请在评论中告诉我。我很想听听！