WPS表格的JS宏很多人一听说要写 JavaScript就直接打退堂鼓。我当年被 VBA 折腾得够呛想着 JS 宏又能好到哪里去但真正用起来才发现WPS 对 JS 宏的支持其实相当友好尤其是在做分组引用和字符串替换这类报表自动化操作时写起来比 VBA 还要顺。这个 8-15 编号按我的理解是一次实操任务或者课程编号核心就是三个关键词WPS JS宏、分组引用、替换函数。今天用一个月度分店报表清洗的真实场景把这三个点串起来讲透。无论你是刚开始接触 JS 宏的小白还是已经在用 VBA 但想转型的老用户这篇文章都能给你一套能直接落地的思路和代码。1. 做汇总报表时我为什么转向 JS 宏1.1 场景代入被 VBA 兼容问题逼到墙角说说我遇到的现实问题。公司每个月末都要把各分店发过来的表格汇总成一张总表。分店表格的结构大体相同但总有那么几个店不老实日期写成 2024/6/3 和 2024-06-03 混用店名一会儿带 市 一会儿不带还有一些多余的空格和特殊符号。之前我用 VBA 写过一套清洗和汇总的宏在微软 Office 里跑得挺好但到了 WPS 环境里各种库引用和对象模型不兼容经常莫名其妙报错搞得我每个月月底都要临时修修补补。后来 WPS 推出 JS 宏我在一个项目中试了试发现它不再依赖那些古老的 VB 库环境直接跑在 JavaScript 引擎上。对习惯了写脚本的人来说代码更直观调试也更方便。最关键的是它解决了我跨版本、跨环境运行宏的痛点同一个文件在同事电脑上打开宏照跑不误。从那次之后我就把月度汇总这类重复性任务逐步从 VBA 迁移到了 JS 宏上。1.2 JS 宏的分组引用到底是什么分组引用这个词初听有点抽象其实可以类比成按标签整理文件夹。你把同一类的 sheet 看作一组JS 宏要做的事就是自动识别这个组然后对该组做统一操作或者把组内各 sheet 的关键数据引用到一张总表里。最常见的就是按工作表名称的前缀或者关键字分组例如所有以 华东- 开头的 sheet 属于一个组所有以 华北- 开头的属于另一个组这样就能针对每个区域做独立处理。这个思想在传统 Excel 公式中也存在比如跨工作表引用就是写 1月!B2 这种公式。但在 JS 宏里我们可以动态地拿到所有工作表列表再用逻辑判断去构造引用完全不需要手工维护几百个公式。写代码的人只需要定义好分组的规则之后不管来多少张表宏都会自动帮你归好类这是纯手工作业没法比的。1.3 这套组合能解决哪几类问题分组引用加替换函数的组合主要覆盖三种需求。第一种是批量数据清洗比如统一日期格式、去掉全角空格、规范店名。第二种是跨表汇总把一组工作表中指定单元格的数据动态抓到总表里。第三种是模板化报表生成比如按分组复制模板格式、统一替换模板中的占位符。这三类恰恰是日常办公里最容易消耗人工时间的活也是最容易出错的活。我自己统计过原来这个月度汇总需要一个人花半天时间而且每次手动查找替换都可能漏掉几行。用宏之后不到一分钟完成而且结果稳定可复现。对做运营、财务、HR 报表的人来说节省下来的时间远比你想象的多。这篇文章后面所有的例子都围绕这三类需求展开你完全可以照着改一改就用在自己的场景里。2. 分组引用的 3 种常用实现2.1 按工作表名分组命名规则是最省事的锚点先说最实用的分组办法利用工作表名称。如果你有一定的主动性可以在源头就约定好命名规则比如 华东-上海店、华北-北京店甚至直接叫 01店、02店。但如果同事交上来的表经常乱起名字就只能靠代码去兼容了这时候代码会复杂不少。我用 JS 宏写过一个判断逻辑核心就是遍历 Worksheets 集合然后用正则或 indexOf 去匹配名称。代码大致是这样function groupSheetsByPrefix(prefix) { let wb ThisWorkbook; let result []; for (let i 1; i wb.Worksheets.Count; i) { let ws wb.Worksheets(i); if (ws.Name.indexOf(prefix) 0) { result.push(ws); } } return result; }这段函数把指定前缀的工作表全部装进一个数组返回给调用方。有人可能问为什么要返回数组而不是直接操作因为分组之后我们往往还要二次处理先清洗再汇总。把工作表对象放进数组后面想遍历多少次都行逻辑也更清晰。我实际开发中还会用正则来判断名称比如 /^华东-|^华北-/ 这样的匹配规则灵活性更高。之所以强调命名规范是因为一旦 sheet 名称混乱任何分组逻辑都需要写一堆兼容规则代码复杂度会爆炸。所以我的建议是能用命名解决的绝不用代码硬扛。你可以在团队里做个简短约定三个月后回头看省下的都是自己的头发。2.2 跨表取数的 3 种姿势分组只是第一步更核心的是把组内数据引用到目标位置。这里有 3 种姿势我按推荐程度排序。第一种是直接写单元格公式。这种方式适合数据量不大、且需要响应后续变化的场景简单说就是想让汇总结果跟着源数据自动变。JS 宏里给目标单元格赋值一个字符串公式比如let rng ws.Range(C2); rng.Formula 华东-上海店!B2;第二种方式是用 Range 对象直接取值再赋值。这种适合一次性汇总不保留公式引用跑完宏就定格在当前数据。代码写起来更直观也不容易出现公式刷新的性能负担let src wb.Worksheets(华东-上海店).Range(B2).Value2; wb.Worksheets(汇总).Range(C2).Value2 src;第三种效率最高先把整块区域 Value2 读进数组在内存中处理再一次性写回。比如跨 sheet 拷贝某个区域let data srcWs.Range(A1:D100).Value2; let destRange destWs.Range(A1:D100); destRange.Value2 data;这里要说明一下Value2 读取的是单元格的原始值或者说显示值在 JS 宏里用起来非常顺手。一次读、一次写比逐格循环快了不止一个数量级。第 5 章我还会专门说性能优化这可以说是整个 JS 宏性能优化的第一性原理。2.3 动态分组让宏适配新增的工作表很多报表是会持续更新的这个月有 12 家店下个月也许就有 15 家。写死分组列表肯定不行所以要让宏具备动态识别能力。我的思路是先把所有工作表名称收集起来再用一次遍历完成分类分类规则放在总表的一个隐藏区域里维护比如在 参数 sheet 的 A 列写下分组关键词宏读取这些关键词来动态分组。这样新增一家店只要命名符合规则下次跑宏就会自动纳入。举个例子假设参数表里有三行华东、华北、华南。宏的做法是先读参数再拿参数去给所有工作表归类let paramWs wb.Worksheets(参数); let keywords []; let lastRow paramWs.Cells(paramWs.Rows.Count, 1).End(-4162).Row; // xlUp for (let i 1; i lastRow; i) { keywords.push(String(paramWs.Cells(i, 1).Value2)); }这里 End(-4162) 是向上找非空单元格-4162 是 xlUp 枚举的数值。之后再用这些关键词去遍历工作表匹配上关键词的进组没匹配上的跳过。我实际测试下来维护参数表比改代码省心太多也是我推荐的做法。就算哪天公司组织架构调整你只需要改参数表里的几个关键词宏逻辑一根毛都不用动。3. 替换函数进阶从 Replace 到正则3.1 先从 JS 的 replace 说起JS 宏既然是 JavaScript 环境那字符串处理自然用 JS 原生的 replace 方法。它的基本用法是前一个参数传入要匹配的内容后一个参数传入替换成的字符串。比如下面这段let s 2024年第03期销售报表; let t s.replace(销售, 订单);这段代码只会替换第一个匹配项这是很多新手最容易踩的坑。如果要全局替换就要用到正则表达式或 replaceAll。WPS 的 JS 宏引擎对 ES 规范支持得不错replaceAll 在较新版本里也能用但为了兼容老版本 WPS我更推荐用正则一劳永逸let t s.replace(/销售/g, 订单);这里的 g 标志表示全文替换。正则里还可以做更灵活的操作比如把日期分隔符统一这是清洗 Excel 表格时高频出现的需求let dateStr 2024/6/3; let fixed dateStr.replace(/\//g, -);需要提醒的是replace 返回的是新字符串不会改动原变量。你一定要把返回值接住不然白写了。我在刚开始做宏的时候经常写s.replace(...)然后打印原变量 s发现数据一点没变一度以为宏坏了后来才反应过来是忘了接收返回值。3.2 单元格批量替换的两种路径谈论替换函数不能只停在字符串处理层面。在 WPS 表格里很多时候我们想替换的是单元格内容。第一种路径是直接用 Range.Replace 方法这个和 WPS 界面上的查找替换操作是一回事不过可以在宏里批量执行几百上千个单元格一次搞定let rng wb.Worksheets(数据).Range(A1:D100); rng.Replace(销售一部, 华东销售一部, 2, -4163);参数分别是要查找的内容、替换成的内容、LookAt2 代表整个单元格匹配1 代表部分匹配、SearchOrder-4163 是按行-4164 是按列。说实话这些数字枚举记起来确实烦写代码的时候不注释过两周回来自己都忘了。我现在的习惯是遇到这类枚举值一定在代码旁边写清楚含义这个习惯帮我省了太多回头查找的时间。第二种路径是遍历单元格逐格做字符串处理。这种方式更灵活能配合正则做复杂替换尤其适合那种一个单元格里混着多种格式的场景for (let r 1; r 10; r) { let cell ws.Cells(r, 1); let v cell.Value2; if (typeof v string v.indexOf(销售) -1) { cell.Value2 v.replace(/销售/g, 订单); } }第一种路径适合规则简单的批量替换第二种适合需要判断、清洗、格式化的场景。实际项目中我经常把两者结合使用先用 Range.Replace 做一次粗清洗把最常见的不规范写法统一掉再用循环加正则扫一遍漏网之鱼双保险。3.3 正则替换的高频玩法与转义坑正则替换是进阶玩家的利器但也是最容易出现莫名其妙 bug 的地方。先说几个高频场景都是我从真实报表里提炼出来的。去掉字符串中的非数字字符比如从 编号A-12345 里提取数字let v 编号A-12345; let digits v.replace(/[A-Za-z\u4e00-\u9fa5\-]/g, );把连续空格压缩成单一空格并去掉首尾空白这在清洗店名和备注时几乎必用let clean v.replace(/\s/g, ).trim();统一手机号里的 - 和空格方便后续匹配let phone 138 1234-5678; let unified phone.replace(/[\s\-]/g, );这里最要注意的是转义。如果你要匹配的是半角括号、点号、星号这些特殊字符必须用反斜杠转义。我踩过最大的坑就是替换小数点的场景直接写.会把所有字符都匹配掉数据瞬间全废。正确写法是\.。这类问题往往要等结果校验时才能发现所以跑完宏之后一定要抽查数据别急着关文件。4. 15 分钟搞定一个分组替换的报表清洗宏4.1 需求与数据结构场景我具体化一点。工作簿里有若干分店表命名格式是 分店-城市编号例如 分店-01、分店-02另有一张 总表。分店表内容的 A 列是日期B 列是营业额C 列是店名备注。需求有两个第一把各分店日期统一成 YYYY-MM-DD并且清掉 C 列里多余的空格第二把每个分店 B2 到 B31 的数据汇总到总表的对应分组区域总表 A 列是城市编号。数据结构不复杂但这个宏完整覆盖了分组、替换、引用、性能优化四个层面非常适合当模板。你只需要改一改 sheet 名称和区域范围就能套用到自己手头的报表上。4.2 完整代码实现我把完整代码贴出来每段讲清楚它在干什么。跑之前先把代码贴到 WPS 的 JS 宏编辑器里创建一个新的模块保存然后直接运行函数名就行。function cleanAndSummary() { let wb ThisWorkbook; let totalWs wb.Worksheets(总表); // 收集所有分店工作表按名称前缀分组 let storeSheets []; for (let i 1; i wb.Worksheets.Count; i) { let ws wb.Worksheets(i); if (ws.Name.indexOf(分店-) 0) { storeSheets.push(ws); } } // 遍历每一个分店表做数据清洗 for (let idx 0; idx storeSheets.length; idx) { let ws storeSheets[idx]; // 先处理A列日期统一格式 let lastRow ws.Cells(ws.Rows.Count, 2).End(-4162).Row; // 向上找B列最后一行 for (let r 2; r lastRow; r) { let dateCell ws.Cells(r, 1); let raw dateCell.Value2; if (typeof raw string raw.indexOf(/) -1) { dateCell.Value2 raw.replace(/\//g, -); } // 清理C列的多余空格 let nameCell ws.Cells(r, 3); if (typeof nameCell.Value2 string) { nameCell.Value2 nameCell.Value2.replace(/\s/g, ).trim(); } } // 从分店表读数据写入总表 let storeCode ws.Name.replace(分店-, ); // 拿到类似 01 let destRow findRowByCode(totalWs, storeCode); let srcData ws.Range(B2:B lastRow).Value2; totalWs.Range(B destRow).Resize(lastRow - 1, 1).Value2 srcData; } Application.ScreenUpdating true; alert(清洗和汇总完成共处理 storeSheets.length 个分店表); } function findRowByCode(ws, code) { // 在总表A列找到城市编号所在行 for (let i 1; i 100; i) { let v ws.Cells(i, 1).Value2; if (String(v) code) { return i; } } return -1; }4.3 运行过程与结果验证这段代码的核心逻辑是三层先收集分组再逐组清洗最后汇总引用。先把所有分店表放进 storeSheets 数组这就是分组引用应用的本体接着对每个表做日期和备注的替换函数应用最后按城市编号定位到总表目标行把分店表 B 列的数据一次性写入。写的时候有几个细节值得注意。第一End(-4162)向上找最后一行对我来说比用UsedRange更可靠尤其在源表有格式残留但没数据的情况下。第二我先把srcData读出来再一次性写回避免在循环里逐格赋值这样在几十行数据时感受不明显一旦数据到几千行速度差异会非常明显这就是前面说的性能第一性原理。跑完宏以后一定要抽查数据。我会重点看三类位置日期列里是否还有斜杠、C 列是否还有连续空格、总表汇总行是否和分店表一致。这三类检查全部通过这个宏才算真正完成。如果发现某一行没替换掉99% 的可能是那个单元格内容里有你看不见的特殊字符比如不间断空格正则里用\s通常能覆盖但有时还是需要手动看一下原始字符的 Unicode 编码。我还在代码开头习惯性地加上了Application.ScreenUpdating false这里我示例代码没写全你实际运行的时候可以在函数开头加上一行跑完再恢复。这个开关对于长时间执行的宏非常关键能大幅减少界面刷新带来的卡顿感。5. JS 宏踩坑速查表报错排查与性能优化5.1 常见报错与排查方法WPS JS 宏最让人头疼的就是报错信息不够直观中文报错也就算了代码复杂以后根本不知道错在哪一行。我总结出几类高频问题方便直接对照排查。第一类是对象不存在的报错比如 Worksheet 不存在 或者 无法获取 Worksheet 属性。原因通常是工作表名称写错、或者名称里多了空格。排查时先把所有工作表名字打印出来看一遍一目了然。第二类是 Value2 读取返回 null 或 undefined导致后续 replace 方法报错。这通常是因为单元格没有内容或者单元格内容是日期对象而不是字符串。处理方式就是先判断 typeof 类型再决定是否调用字符串方法这个习惯在清洗脏数据时尤其重要。第三类是 Replace 方法的枚举参数不对。Range.Replace 的第三个参数 LookAt1 代表部分匹配2 代表完全匹配第四个参数 SearchOrder-4163 是按行、-4164 是按列。这组数字我建议直接写注释固化在代码里否则过两周回来自己都忘。我把排查思路整理成一张表方便你收藏备用报错现象可能原因处理建议找不到工作表名称拼写偏差或多余空格打印所有 sheet 名核对replace undefined 报错单元格值为空或非字符串先判断 typeof 再处理没有替换任何内容LookAt 参数设置错误检查是 1 还是 2结合数据特征执行极慢逐格读写在循环内改为整块区域 Read/Write日期格式没变Value2 拿到的是日期对象用格式化函数或转为字符串5.2 性能优化少读写、多看两次、监控时间JS 宏的性能问题几乎都出在循环读写上。我用一组真实数据对比过同样是处理 1 万行数据逐格循环写入耗时要几十秒甚至更久而一次性读数组再写回通常保持在一两秒内。所以我的习惯是凡是需要遍历大量单元格的场景一律先把数据读进二维数组处理完再一次性写回这个思路比任何代码技巧都管用。第二个建议是合理关闭屏幕刷新。在宏开头执行Application.ScreenUpdating false本质是告诉 WPS 不要一有变化就重绘界面。让 CPU 专心跑逻辑而不是忙着画格子。老版本引擎如果没这个属性跳过也不影响逻辑但只要有就尽量用上。第三个建议是监控耗时。我习惯在代码里记录一个开始时间和结束时间let start new Date().getTime(); // 业务逻辑... let end new Date().getTime(); console.log(耗时(ms): (end - start));加这一行成本很低但是能帮你判断优化是否真的有效。很多时候你以为代码写得不错一跑才知道性能瓶颈在哪里有数据有对比优化起来才有方向。5.3 代码维护与后续扩展写 JS 宏和写普通代码一样维护好读比花哨重要。我给自己的硬性要求有三条重要步骤写注释、枚举数字写含义、分组关键词做成参数表。这三条做到位哪怕代码过三个月再翻出来我也能十分钟内看懂当时在干什么。后续扩展的方向我顺便说一下。如果你要处理的是跨工作簿数据可以把当前代码里的ThisWorkbook换成用Workbooks.Open打开的文件路径逻辑基本不变。如果你想把汇总结果自动生成图表也可以在写完数据之后调用 Charts 对象添加图表。如果数据量增长很快还可以把结果导出成 CSV 文件直接交接给团队避免二次加工。把基础的分组引用和替换函数吃透相当于给后续的自动化体系搭好了地基。剩下的扩展都是在这个地基上添砖加瓦的事。最后再分享一个小技巧每次跑完宏不要急着关掉工作表先按一下快捷键或者打开宏编辑器的控制台确认没有潜在报错。尤其是 replace 这类操作如果源数据里有你没见过的特殊字符替换结果很可能不如预期。我踩过几次坑之后养成了固定习惯就是在宏里加一个结果行的标记比如在汇总表末尾写一行生成时间下次打开一眼就能确认这是不是最新跑出来的结果。老实说WPS JS 宏不是万能的VBA 资源多、老项目也多很多公司还在用 VBA。但如果你是在 WPS 环境里做重复性报表、批量清洗和数据汇总JS 宏这套分组引用加替换函数的组合确实能给你省下大量精力。分组引用的核心在于命名规则和动态遍历替换函数的核心在于正则和容错处理两者一搭配按月报汇总这种活儿真没想象中那么难。