7个最有用的Google Sheets Formulas

最近，我一天中最开心的部分是想出我可以在谷歌Sheets中做的很酷的新事情——是的，这绝对意味着我需要更多外出，但也意味着我可以与你们分享我最喜欢的公式。

如何使用Google Sheets的公式

双击要输入公式的单元格。(如果您想要整个行的公式，这可能是一列的第一行或第二行。)
键入等于（=）符号。
输入您的公式。根据数据，谷歌Sheets可能会为您提供一个公式和/或范围。

V-Lookup Google Sheets公式

V-Lookups是迄今为止，当您使用大量数据时，您的工具包中最有用的公式。V-Lookup公式查找数据点 - 例如，博客文章标题或URL - 在一张纸中，并返回另一张数据点的相关信息，如另一张表中的转换率．

例如，如果我想要查看一组特定的博客文章获得了多少流量，我将从谷歌Analytics导出一个列表，然后将该列表放到另一个标签中，并使用V-LOOKUP函数通过URL将视图拉到第一个标签中。

唯一的警告:数据点必须同时存在于两个单元格中，和它必须在第二页的第一栏。

公式：

=VLOOKUP(search_criterion, array, index, sort_order)

让我们浏览一个示例，它应该会让这更容易理解。

在第一份表格中，我列出了博客文章的列表，包括它们的标题、网址和每月的流量。在第二张表中，我有一个谷歌Analytics的报告，平均页面加载时间按URL。我想看看页面速度和性能之间是否有关联。

一个例子：

=VLOOKUP(A2， ' GA Avg. Load Time ' !$1:$1000,2,FALSE)

IFERROR谷歌Sheets公式

当你使用一个超过10%的返回值导致错误的公式时，你的电子表格就会看起来很混乱(见上面的截图)。

为了让您有一个想法，也许您有两个列:一个用于页面访问量，另一个用于CTA点击。您希望看到转化率最高的页面，因此您为按CTA点击(或=B2/C2)划分的页面视图创建第三列。

但是，您的页面中的大约三分之一，没有任何CTA - 所以他们没有得到任何点击。这将显示为#Value！在你的表上，因为你不能划分零。

使用IFError公式让您替换该值！状态与另一个值。我通常使用空格（“”），因此表格尽可能干净。

这是公式:

= IfError（Oricall_Formula，Value_If_Error）

对于上述情况，我的公式是:

= IFERROR ((B2 / C2, " ")

COUNTIF谷歌Sheets公式

COUNTIF公式告诉您给定范围内有多少个单元格满足您指定的标准。有了这个工具，您就再也不用手动计算单元格了。

公式：

=条件统计(范围、标准)

假设我很奇怪有多少博客帖子在这段时间内收到了1,000多个观点 - 我会进入：

=条件统计(C2: C500、“> 1000”)

或者也许我想看看Caroline Forsey写了多少个博客帖子。如果作者在列D列中，我的公式将是：

=条件统计(D2: D500,”卡罗琳Forsey”)

LEN谷歌Sheets命令功能

你是否注意到谷歌Analytics从每个URL中删除“http://”或“https://”?当我想要组合HubSpot和GA的数据时，这给我带来了一个大问题——V-Lookup函数无法工作，因为url是不同的(“//www.emcdepot.com/marketing”和“www.emcdepot.com/marketing”)。

幸运的是，不需要手动更改每个URL。LEN函数允许您调整任何字符串的长度。

= lef（文本，len（text）-n）

=正确(文本,LEN(文本)- n)

所以，让我们说完整的URL在第I列。要删除“https://”字符串，并使其与谷歌Analytics标签中的URL相同，我将使用:

=正确(I2, LEN (I2) 8)

如果要删除单元格中的最后一个字符，只需将右改为左即可。

谷歌表的数组公式

很少需要将公式应用于单个单元格 - 您通常在行或列中使用它。如果将公式复制并粘贴到新的单元格中，Google纸张将自动更改为o参考右侧单元;例如，如果我在单元C2中输入= A2 + B2，则将公式拖动到C3，公式将变为= A3 + B3。

但是这有一些缺点。首先，如果您正在处理大量数据，则拥有数百或数千种公式可以使Google Sheets更慢。其次，如果您更改公式 - 也许现在您想要查看= A2 * B2 - 您必须在每个公式中进行更改。再次，这是耗时的，需要很多处理能力。最后，公式不会自动应用于新行或列。

数组公式解决了这些问题。它是一个公式，一个计算，但是结果被分成多行或多列。这不仅更有效，而且任何更改都会自动应用于所有数据。

ARRAYFORMULA (array_formula)

假设我想看看我们在3月和4月获得了多少非付费流量。这需要从总流量中减去付费流量(从C栏中减去D栏)，然后将总流量相加。两个独立的公式。

或者，我可以使用一个数组公式:

= Arrayformula（SUM（C2：C5-D2：D5）

第二部分SUM(C2:C5-D2:D5)看起来应该有点熟悉。这是一个传统的加法公式——但它适用于一个范围(细胞C2到C5和D2到D5)，而不是单个细胞。

第一部分，=ARRAYFORMULA，告诉谷歌Sheets我们正在将这个公式应用到一个范围。

我还可以使用数组公式来查看3月和4月的更新(不是新内容)的非付费流量。

这是什么样子：

= Arrayformula（Sum（C2，C4-D2，D4）））

IMPORTRANGE谷歌Sheets公式

我用来花费大量时间（和处理能力）手动将大量数据从一个电子表格复制到另一个电子表格。然后我了解了这个方便的公式，它从单独的Google Speets电子表格中导入数据。

假设我们的居民历史优化专家Braden Becker发出了上个月更新的内容的电子表格。我想将数据添加到我们发布的所有内容（新的和历史上优化）的主电子表格中。我会使用这个公式：

IMPORTRANGE (spreadsheet_url range_string)

应该是这样的:

IMPORTRANGE(“https://docs.google.com/spreadsheets/d/abcd123abcd123”、“更新性能! A2: D100使用”)

如何拆分谷歌表中的文本

当您处理相同URL的不同版本时，拆分文本可能会非常有用。

为了让您有一个概念，让我们假设我创建了一个电子表格，其中包含每个在1月和2月获得至少300次浏览的URL。我想比较这两个月，看看哪个博客文章的浏览量更多，更少，或者大致相同。

问题是，如果我做V-LOOKUP之间的两个标签，谷歌表不会识别这些为相同的url:

//www.emcdepot.com/sales/songs-for-maximum-motivation（常规URL）

//www.emcdepot.com/sales/songs-for-maximum-motivation?utm_medium=paid_en& utm_content=songs-for-maximum-motivation& utm_source = getpocket.com& rutm_campaign=pocketprocotion（跟踪URL）

//www.emcdepot.com/marketing/create-infographics-with-free-powerpoint-templates（常规URL）

//www.emcdepot.com/marketing/create-infogubics-with-free-powerpoint-templings?utm_medium=paid_en& utm_content=create-infographics-with-free-powerpoort-templates& utm_source = getpocket.com&urm_campaign=pocketpromotion（跟踪URL）

如果我可以在跟踪URL中的问号后删除所有内容，那就太棒了，所以它们与原始的问题匹配。

这就是分裂文本配方进来的地方。

= split（文本，分隔符，[split_by_each]，[remove_empty_text]）

文本:文本你想分裂(可以是一个字符串的字符,比如//www.emcdepot.com/marketing/create-infographics-with-free-powerpoint-templates?utm_medium=paid_EN&utm_content=create-infographics-with-free-powerpoint-templates&utm_source=getpocket.com&utm_campaign=PocketPromotion,或一个细胞,如A2)

分隔符:您要拆分文本的字符。

Split_by_each:谷歌表认为分隔符中的每个字符是分开的。这意味着如果您用“utm”分割文本，它将分割“u”、“t”和“m”周围的所有字符。在公式中包含FALSE以关闭此设置。

在上面的例子中，下面是我用来将URL的第一部分从UTM代码中分离出来的公式:

=分裂(A2,“?”)

第一部分现在在列B中，UTM代码在列C中。我可以简单地删除列C中的所有内容，并在列B中的url上运行V-LOOKUP。

或者，你可以使用谷歌工作表的“拆分文本到列”功能。突出显示要拆分的数据范围，然后选择“data”>“拆分文本到列”。