不会写代码怎么批量做SEO？12个Google Sheets公式把数据活自动化

摘要：做SEO一半时间耗在搬数据上——把GSC导出、Ahrefs关键词、竞品页面标题一遍遍复制粘贴、手动去重、肉眼对表。很多人卡在两头：要么忍着手工慢，要么觉得非学Python不可。其实中间有条被严重低估的路——Google Sheets。它不是Excel的网页版平替，而是一台能抓网页、能连表、能当迷你数据库的免费数据工作台。这篇不按公式名罗列，而是按你每天真正要干的活来拆：抓取竞品页面、清洗关键词、对齐两份导出、捞出速赢词、自己搭一个机会分公式排优先级，再到2025年Google给Sheets原生塞进来的 =AI() 函数怎么批量分类意图。附每个公式的真实写法、什么时候会崩、以及什么时候你该老实上代码。

先说个几乎每个SEO人都经历过的场景。手里一份GSC导出的三千行查询词，另一份Ahrefs导出的关键词搜索量，你要把两份对起来，看看哪些排在第11到20名、展示量又高的词值得推一把。老实人的做法是：肉眼找、手动VLOOKUP不会写就一行行复制、排序、标色，一下午就没了，还容易对错行。

这活的本质是数据处理，不是脑力活。而数据处理这件事，Google Sheets能替你干掉八成。保哥带团队这些年，新人入职第一周不教他们买什么工具，先教十来个Sheets公式——因为工具会换、预算会砍，但把杂乱数据快速拧成能决策的表，这个能力是跟着人走的。这篇就把这套东西一次讲透。

为什么说Sheets是SEO人的数据工作台，不是Excel平替

很多人把Google Sheets当成“云端版Excel”，用它做的事跟Excel一模一样——填表、算总和、画个柱状图。那确实浪费了。对SEO来说，Sheets有三件Excel原生做不到的事：

第一，它能直接抓网页。一个 IMPORTXML 函数就能把任意网址的标题、描述、H1、canonical抓进单元格，不用装插件、不用写爬虫。你想扒竞品五十个落地页的标题看选题缺口？粘五十个网址、拉一个公式，几十秒的事。Excel要做到这个得靠VBA或Power Query，门槛高一截。

第二，它能把散在各处的表连起来。IMPORTRANGE 能跨表引用别人共享给你的整块数据，QUERY 能用类SQL的语法当迷你数据库来查。你的GSC导出、关键词表、爬虫结果可以分开放，用公式实时拼。

第三，它是云端协作 + 免费。一份表甲方、内容、外链同事同时看，改了实时同步，还能拿链接嵌进周报。这些叠加起来，Sheets就成了一台你随时能改造的数据流水线，而不只是一个填格子的地方。如果你连SEO数据体系怎么搭都还没头绪，建议先补一下SEO数据分析怎么入门这套三类工具的地基，再回来看公式，会顺很多。

先打地基：把数据弄进表里的三种方式

公式再花哨，数据进不来都是空谈。SEO场景里，数据进表就三条路，按成本从低到高：

• 手动导出粘贴。GSC、Ahrefs、Semrush都能导CSV，下载再粘进来。最笨但最稳，一次性分析用这个就够。
• IMPORT系函数实时拉。IMPORTXML 抓网页、IMPORTRANGE 连别的表、IMPORTHTML 抓现成的表格或列表。数据是活的，来源页面一变它就跟着变。
• Apps Script连API。当你要定时、要跑GSC/Ahrefs的API、要几千行自动刷新，就得写几行脚本了。这是Sheets的天花板，也是它和纯代码方案的交界。

八成的日常活，前两条就能覆盖。下面按真实任务一个个拆。

抓取类：一个IMPORTXML扒清竞品的on-page元素

IMPORTXML 是SEO人用Sheets最值钱的一个函数。它的逻辑是：给它一个网址，再给它一条XPath——一种在HTML里精确定位某个元素的路径语言——它就把那个元素的内容抓回来。谷歌官方对IMPORTXML的定义是“从XML、HTML、CSV、TSV以及RSS、ATOM等各种结构化数据里导入数据”，而定位靠的就是XPath这套W3C标准的路径查询语言。

SEO最常用的几条，直接抄：

标题：      =IMPORTXML(A2, "//title")
Meta描述：  =IMPORTXML(A2, "//meta[@name='description']/@content")
H1：        =IMPORTXML(A2, "//h1")
Canonical： =IMPORTXML(A2, "//link[@rel='canonical']/@href")
页面所有H2：=IMPORTXML(A2, "//h2")

A2放网址，往下拉，一列网址就变成一列标题。把这几条并排放，一张竞品on-page体检表就出来了：谁的标题写满了、谁的描述空着、谁的canonical指错了，一眼看穿。这套用法是SEO圈的老套路，Ahrefs那份被大量引用的SEO必备公式清单里也把IMPORTXML摆在最前面。保哥常拿它做选题——扒一个词排前二十的所有页面标题，看大家都在讲什么、还漏了什么角度，比一个个点开看快十倍。

但 IMPORTXML 有脾气，三个坑必须知道：

• 它抓的是原始HTML，不执行JavaScript。纯前端渲染（React/Vue那种）的页面，标题是JS塞进去的，它抓回来是空的。这也顺带提醒你：Google某些抓取环节也一样，JS依赖太重的页面本身就有可见性风险。
• 它会限流。一张表里塞太多 IMPORTXML（几十上百个），会陆续报 #N/A 或者转圈加载不出来。诀窍是抓完一批就把结果“选择性粘贴为纯数值”，把公式拆掉，别让几百个公式一直挂在那儿实时请求。
• 对方页面一改版，你的表就崩。公式是实时拉的，目标页面结构变了、网址404了，你的整列数据跟着变空。所以重要数据抓完要及时固化成静态值。

为了不让报错弄花整张表，给它套一层 IFERROR：

=IFERROR(IMPORTXML(A2, "//title"), "抓取失败")

抓不到就显示“抓取失败”四个字，而不是刺眼的 #N/A，后面统计也不会被错误值带崩。

清洗类：几千个关键词去重、归一、拆词根

从各家工具导出的关键词，永远是脏的：大小写不一、前后带空格、大量重复、还混着一堆变体。清洗是选词前绕不开的一步。核心就四个函数：

去重：        =UNIQUE(A2:A)
转小写：      =LOWER(A2)
去首尾空格：  =TRIM(A2)
三合一整列：  =ARRAYFORMULA(UNIQUE(TRIM(LOWER(A2:A))))

最后一条最实用：ARRAYFORMULA 让一条公式作用于一整列而不用往下拉，套着把整列先去空格、再转小写、再去重，一步到位。几千个词的清单，一个公式就洗干净了。

要拆词根、看词里都带了哪些修饰词，用 SPLIT 按空格切开：

=SPLIT(A2, " ")

“户外 便携 储能 电源” 就被切成四个单元格，你能快速统计“便携”“露营”“家用”这些修饰词各出现多少次，判断需求集中在哪。这套清洗做完，才谈得上选词。关键词清单怎么从几千砍到能落地的几百，选词的判断逻辑，可以接着看关键词工具盲区里的捡漏选词法，两件事是连着的。

写作辅助类：LEN控字数、CONCATENATE批量拼meta

数据处理之外，Sheets还能直接帮你批量写、批量控。两个函数最实用。

LEN数字符，守标题和描述的长度红线。标题、meta描述都有大致的字符甜区，写超了在搜索结果里会被截断。给内容同事一张表，标题列旁边拉一个 LEN，超了自动标红，谁都不用记那串数字：

=LEN(A2)
=IF(LEN(A2)>60, "标题偏长", "长度OK")

一列标题的长度合规情况一眼扫完，比一条条数快得多，也比事后被截断了再返工强。

CONCATENATE批量拼装meta和URL。要给几百个产品页套同一个标题模板“产品名 - 品类 | 品牌名”，手工敲会敲到崩溃。用文本拼接一次成型：

=CONCATENATE(A2, " - ", B2, " | 户外储能")
更短的写法： =A2 & " - " & B2 & " | 户外储能"

A列产品名、B列品类，一拉就是几百条现成标题，复制回CMS即可。生成分类URL、批量拼alt文本、给外联邮件套称呼，都是同一招。业内把这类文本拼接和查找函数列为SEO必会，Search Engine Land整理的SEO必备Sheets公式里，CONCATENATE和VLOOKUP都是常年上榜的常客——因为它们直接对应“批量生成元数据”和“给关键词补数据”这两件天天要干的活。

提醒一句：批量套模板容易套出千篇一律的标题，Google对模板化、堆砌的元数据是明确不待见的，拼完最好人工过一遍，把重复感太重的手动改活。

匹配类：把GSC导出和Ahrefs导出对起来

这是SEO里最高频的一个动作：手上有一份GSC的查询词+展示+点击，另一份Ahrefs的关键词+搜索量+难度，你想把它们按关键词对齐，凑成一张既有真实表现、又有市场大盘的完整表。

经典解法是 VLOOKUP：

=VLOOKUP(A2, Ahrefs导出!A:E, 3, FALSE)

意思是：拿A2这个词，去“Ahrefs导出”表的A到E列里找，找到了就返回第3列（比如搜索量）的值，FALSE 表示要精确匹配。往下一拉，GSC那份表就补上了每个词的搜索量。

但 VLOOKUP 有个老毛病：它只能往右找，被查的那列必须在最左边，中间插一列还会错位。更稳的是新一点的 XLOOKUP：

=XLOOKUP(A2, Ahrefs导出!A:A, Ahrefs导出!C:C)

它把“拿什么找”和“返回哪列”分开写，不受列顺序影响，找不到还能自定义返回值。能用 XLOOKUP 就别用 VLOOKUP，少踩一半的错位坑。两份表一对齐，你就有了一张“内部真实表现 × 外部市场大盘”的决策底表。

筛选统计类：QUERY函数当迷你SQL，一句话捞出速赢词

数据凑齐了，接下来是从几千行里精确捞出你要的那一小撮。这时候 QUERY 就是Sheets里被低估得最狠的神器——它让你用类SQL的语法查表，等于在电子表格里塞了个数据库。

比如你要找“排在第11到20名、展示量大于500”的striking distance词（临门一脚、最容易冲首页的那批），一句话搞定：

=QUERY(A1:F, "select A,C,F where F>=11 and F<=20 and C>500 order by C desc", 1)

A是查询词、C是展示量、F是平均排名。这句的意思是：选出查询词、展示、排名三列，只要排名在11到20之间且展示大于500的行，按展示从高到低排。末尾的 1 表示原表有1行表头。结果自动生成一张干净的速赢清单，条件想改随时改一句话，比手动筛选+排序快得多，也不会误删原始数据。

QUERY 能做的远不止这些：按品牌词/非品牌词分组统计、按落地页汇总点击、找出零点击的高展示词，都是一句 select ... where ... group by ... 的事。会用它，你处理几千行数据的效率是不会用的人的好几倍。

提取类：REGEXEXTRACT从URL里抠出你要的片段

有时候你要从一堆网址里批量提信息：这个URL属于哪个子目录？带了哪个utm来源？品牌词有没有出现在slug里？靠手工切太慢，用正则提取函数 REGEXEXTRACT：

抓第一层子目录： =REGEXEXTRACT(A2, "https?://[^/]+/([^/]+)/")
抓utm_source：    =REGEXEXTRACT(A2, "[?&]utm_source=([^&]+)")

括号里那部分就是你要“抠”出来的内容。一列乱七八糟的URL，瞬间被拆成“子目录”这样能分组统计的维度，看清哪个栏目贡献了多少流量。正则这东西写起来容易错，Sheets、GSC、编程语言的正则方言还各有差异，同一条规则换个地方结果可能就变了，这点在正则测试器为什么GSC里调通的正则到别处会变里拆得很细，写复杂正则前值得先扫一眼避坑。

打分类：自己搭一个“机会分”公式，把速赢排成优先级

前面都是搬数据、洗数据。真正拉开差距的，是你能不能用公式把“该先做哪个”这个判断量化下来。速赢多得做不完，凭感觉挑最后就是东一榔头西一棒子。给每个词算一个机会分，让表替你排序。

最实用的一个机会分逻辑：潜在点击增量 ≈ 展示量 ×（目标位置的CTR − 当前CTR）。一个词展示量很大、但当前点击率很低（说明排名还不够靠前），把它往上推能拿到的增量点击就大，值得优先。落成公式：

=B2*(0.28-D2)

B2是展示量，D2是当前点击率，0.28是你假设推到前三名后大致能拿到的点击率。算出来的数越大，越该先动手。你还能再乘一个难度系数（难做的打个折）、加权一个商业价值（交易词权重高），把它调成贴合自己业务的优先级引擎。

举个具体的：A词展示8000、当前点击率只有2%，机会分是8000×（0.28−0.02）＝2080；B词展示1200、当前点击率已经有18%，机会分只有1200×（0.28−0.18）＝120。哪怕B词看着点击率高、A词点击率低得可怜，该先推的反而是A——它盘子大、离该拿的点击差得远，推上去的绝对增量是B的十几倍。光凭感觉，你很可能先去优化那个“看起来还有救”的B，把最大的机会白白漏掉。一列机会分排下来，先干哪十个一目了然，这就是让表替你做判断的意义。

这一步是分水岭：会搬数据的人一抓一大把，能把杂乱数据变成一句“就先干这十个”的人，才是老板和甲方眼里值钱的那个。

把它们串成一条流水线：从GSC导出到速赢清单

单个公式是零件，串起来才是产线。一套最常用的“速赢挖掘”流水线是这样跑的：

1. GSC导出近三个月的查询词+点击+展示+CTR+平均排名，粘进“原始”页。
2. 新建“清洗”页，用 ARRAYFORMULA(TRIM(LOWER(...))) 把查询词洗一遍。
3. 用 XLOOKUP 把Ahrefs导出的搜索量、难度补进来。
4. 加一列机会分公式 =B2*(0.28-D2)。
5. 最后用一句 QUERY 捞出“排名11-20且 机会分排前列”的行，生成最终的速赢清单。

这张表建一次，以后每月把新的GSC导出往“原始”页一粘，后面全自动重算，速赢清单当月就刷新。这就是Sheets的价值——不是省一次力，是把一次性的分析固化成可复用的产线。

再往前一步，这张表还能直接共享给内容和外链同事：谁认领了哪几个速赢词、做到什么进度，就在最终清单旁边加一列状态标注，所有人看的是同一张实时表，省掉来回发文件、对版本号的麻烦。一个人用它省时间，一个团队用它对齐节奏——数据工作台的复利就在这儿。要把这类清单再往正式仪表盘、跨源大数据方向做，那才轮到专门的BI工具上场，日常这一层Sheets扛得住。

可视化类：不用Looker，条件格式和SPARKLINE也能一眼看趋势

数据出来了，给人看还得有点视觉。Sheets里两个轻量招就够应付大部分周报：

条件格式当热力图。选中排名那列，设置“色阶”，排名靠前的自动染绿、靠后的染红，几百行数据的健康度扫一眼就有数，不用画图。CTR、展示量同理，异常值自己跳出来。

SPARKLINE画迷你趋势。在单元格里塞一条微型折线：

=SPARKLINE(B2:M2)

B到M列放某个词过去12个月的排名，这个函数就在一格里画出趋势线，一列词的走势整整齐齐排着看。当然，要做给CMO看的正式仪表盘、要接BigQuery跑大数据，还是得上Looker Studio这类专门的可视化工具；但日常自己看、给同事对齐，Sheets的条件格式加SPARKLINE完全够用，还快。

AI时代的新玩法：=AI()函数批量分类意图、生成meta

2025年Google给Sheets原生加了一个 =AI() 函数（也能写成 =Gemini()），把大模型直接搬进单元格。这是过去那些“10个SEO必备Sheets公式”文章还没来得及写的新变量，对SEO批量活是实打实的省力。几个立刻能用的场景：

批量判意图： =AI("把这个查询词归类成 信息型/商业型/导航型/交易型，只回类型：", A2)
批量写描述： =AI("给这个页面标题写一句80到120字符的中文meta描述：", A2)
批量提实体： =AI("从这段产品描述里提取出品牌、品类、核心卖点，逗号分隔：", A2)

让Gemini先帮你写好一行公式，再往下拉一千行，过去要一条条人肉判断的活，现在批量出结果。官方还在持续给它接搜索结果、扩场景。

但三条红线得画清楚：一是它要付费的Workspace套餐才有，免费账号用不了原生 =AI()，只能靠第三方插件；二是结果必须核验，模型分类意图、写描述会出错也会瞎编，它是助理不是真值，尤其批量跑更要抽检；三是隐私红线，别把客户的敏感数据、未公开的关键词战略喂给来路不明的第三方AI插件，数据出了你的表就不受控了。把 =AI() 当成“批量打草稿”的加速器，最后拍板的还得是人。

别硬用Sheets的边界：什么时候该老实上代码

Sheets很强，但它是有天花板的。撞到下面这几堵墙，就别硬扛了：

• 数据量。Sheets单表有单元格上限，几万行、带一堆实时公式就开始卡，十万行以上基本没法愉快工作。到这个量级，该上BigQuery。
• 实时性和配额。要定时抓、要批量调GSC/Ahrefs的API、要绕开 IMPORTXML 的限流，就得写脚本。GSC URL检查API批量监控收录那套配额下的监控管线就是典型的“Sheets干不了、必须上代码”的活。
• 稳定复用。一次性分析Sheets最快，但要做成天天跑、不能断的生产管线，公式嵌套太深会脆、难维护，不如落成脚本。

公式和脚本之间其实还有个中间选项——现成的Sheets插件。Search Engine Journal盘点过一批让SEO活更省力的Sheets插件，从关键词批量拉取到抓取、排名监控都有现成的，不想写脚本又嫌纯公式繁琐的，可以先看看有没有合手的插件再决定要不要动代码。

真要写代码，Google自己给了台阶：Apps Script的电子表格服务能在Sheets里写脚本，自动化流程、写自定义函数、连外部API，算是从“纯公式”到“纯代码”之间的缓冲带。判断原则很简单：一次性的、临时的、给人看的，用公式；要定时、要大量、要长期稳定的，上脚本。别用一堆嵌套七层的公式去硬撑一条本该用代码写的产线。

出海实战：一次IMPORTXML扒出竞品的选题缺口

讲个手边的例子。一个做户外储能的独立站客户，想进英文市场，卡在不知道该先写哪些内容。保哥带他们做的第一件事，就是打开一张Sheets：把这个词排在谷歌前二十的五十个页面网址全粘进A列，B列拉 =IMPORTXML(A2,“//title”) 抓标题，C列拉 //h1 抓H1，D列抓所有H2。

几十秒后，一张“竞品都在讲什么”的全景表出来了。把H2那列用 SPLIT 拆开、QUERY 一分组，立刻看清：所有页面都在讲“容量怎么选”“能带什么电器”，但几乎没人认真讲“露营场景下的实际续航实测”和“低温掉电”。这两个缺口就是选题机会。这套动作从粘网址到看清缺口，前后不到二十分钟，没写一行代码，也没花一分工具钱。后来那两篇缺口内容都进了首页前五。数据搬运的活交给公式，人的时间省下来干判断——这才是Sheets对SEO人的真正意义。

五个最容易踩的坑

• 把Excel语法硬套进Sheets。两者函数名和参数有差异（分隔符、数组写法都不一样），Excel里调通的公式直接搬过来经常报错，别想当然。
• IMPORTXML抓太多不固化。几百个实时抓取公式挂着，限流报 #N/A 是迟早的事，抓完及时粘成纯数值。
• 依赖外部数据源却不备份。来源页面改版、网址失效，你的表悄无声息地变空，重要数据一定要留静态快照。
• 公式嵌套太深没人看得懂。七层嵌套的“天书公式”你自己三个月后也读不懂，能拆成几步中间列就拆开，可维护性比炫技重要。
• 把敏感数据喂给第三方AI插件。客户数据、未公开的战略词，出了你的表就不受控，用AI功能前先想清楚数据流向。

常见问题解答

IMPORTXML为什么总是返回错误或者空白

最常见三个原因：一是目标页面靠JavaScript渲染，IMPORTXML 抓的是原始HTML，抓不到JS后填的内容；二是一张表里实时抓取公式太多触发了限流，会报 #N/A；三是XPath写错了或者目标页面结构变了。排查顺序是：先给公式套 IFERROR 看是不是报错，再单独测一个网址确认XPath对不对，最后把批量抓取分批做、抓完固化成纯数值。

Google Sheets和Excel做SEO数据，到底该用哪个

要抓网页、要多人实时协作、要连别的在线表格，用Sheets，IMPORTXML 和云端协作是它的独门优势。要处理超大数据集、要跑复杂的本地宏和透视，Excel更稳更快。多数SEO日常活——扒竞品、洗关键词、对齐导出——Sheets更顺手。注意两者函数语法有差异，别直接搬。

不会写代码，能靠Sheets把SEO数据活都干了吗

八成能。抓取、清洗、匹配、筛选、打分、可视化这些日常数据处理，纯靠 IMPORTXML、QUERY、XLOOKUP 这些公式就能覆盖，一行代码不用写。撞到墙的只有三种情况：数据量到十万行以上、需要定时自动跑、需要调用需要鉴权的API，这时候才需要Apps Script或专门的工具。

QUERY函数和VLOOKUP有什么区别，该学哪个

VLOOKUP（或更稳的 XLOOKUP）解决“按一个值去另一张表取对应数据”的匹配问题；QUERY 解决“从一大堆数据里按复杂条件筛选、分组、排序”的查询问题。两个都要会：对齐两份导出用 XLOOKUP，从对齐后的大表里捞速赢词用 QUERY。QUERY 学习曲线略陡但威力大，值得花半天啃下来。

Sheets里的=AI()函数做SEO靠谱吗

当加速器靠谱，当真值不靠谱。批量分类查询意图、批量起meta描述草稿、批量提取实体，它能把一天的活压成几分钟。但它会出错也会瞎编，结果必须人工抽检核验，尤其涉及事实和数字时。另外它需要付费的Google Workspace套餐，免费账号只能用第三方插件，用插件时务必注意别把敏感数据外泄。

用IMPORTXML抓竞品数据，会不会有法律或封禁风险

抓取公开可见的页面元素（标题、描述、H1这类），且频率不高、只用于自己分析，通常在合理范围内。但要注意：别高频大批量抓同一个站（会被限流甚至封IP），别抓需要登录才能看的内容，别把抓来的内容原样搬去发布。IMPORTXML 本身就有限流机制，客观上也不适合当大规模爬虫用，它的定位是轻量、临时的数据采集。

权威参考资料