Excel纯公式打造自动刷新行情看板：GetStockSource与GetJsonProperty实战

1. 从一张会自己更新的表格说起上班时间想瞄一眼持仓涨跌又不想频繁切窗口、开APP、被同事瞥见屏幕上花花绿绿的K线这个需求其实非常普遍。我身边做运维、做财务、做行政的朋友几乎每个人都有过类似的小心思能不能让手边最不起眼的Excel变成一个能自动刷新行情的小看板答案是能而且核心工具就是Excel自带的公式体系配合一个叫GetStockSource的数据获取函数再加上GetJsonProperty做JSON字段提取整条链路不需要装任何插件、不需要写VBA宏、不需要开通任何付费接口纯公式就能跑起来。这套方案的本质是把外部返回的JSON格式行情数据通过公式拉进单元格再用JSON解析函数把嵌套结构里的字段一层层剥出来最后落到你熟悉的表格里。它解决的核心问题是数据获取和数据解析这两步的自动化让你从手动复制粘贴里彻底解放出来。适合谁来参考只要你会用Excel的基础函数、知道单元格引用是怎么回事就能照着做如果你还懂一点JSON结构那上手会更快。下面我把整套思路、参数细节、踩坑记录全部摊开讲尽量做到你照着抄就能用。2. 整体设计思路与方案选型拆解2.1 为什么选Excel公式而不是插件或脚本很多人第一反应是装个行情插件或者写个Python脚本定时抓数据再写进表格。这两条路我都试过各有各的麻烦。插件的问题在于安装权限、版本兼容、以及公司电脑往往不允许随意装东西Python脚本的问题在于要配环境、要挂后台、要处理定时任务一旦电脑重启或者网络抖动整个链路就断了排查起来还费劲。Excel公式方案的优势在于零依赖——它跑在Excel自己的计算引擎里只要表格开着公式就能重新计算。你不需要额外的运行时不需要管理员权限文件发给同事也能直接用前提是对方Excel版本支持这些函数。对于上班偷偷盯盘这种轻量、即时、低存在感的需求来说公式方案是最贴合的。它的劣势也很明显刷新依赖手动触发或工作簿重算做不到秒级推送但对于看日线、看当前价这种场景完全够用。2.2 GetStockSource与GetJsonProperty的分工这两个函数是整套方案的两根支柱理解它们的分工后面就不会乱。GetStockSource负责取数。你给它一个标的代码或者查询条件它去外部数据源把原始数据拿回来。拿回来的东西通常是一坨JSON文本长这样{data:{code:600519,name:某某,price:1688.00,change:12.34,changePercent:0.74,volume:123456}}GetJsonProperty负责拆数。JSON是嵌套的你不能直接对着一整坨文本做加减乘除必须先把price、change这些字段单独拎出来。GetJsonProperty就是干这个的你告诉它我要data下面的price它就把1688.00这个值返回给你变成一个可以参与计算的数字。提示不同Excel版本或不同数据源对这两个函数的支持情况不一样动手前先确认你的环境里这两个函数能正常返回结果否则后面全是白忙。2.3 数据流的整体走向把上面两步串起来整条数据流是这样的在某个单元格比如A2用GetStockSource拉取某只标的的原始JSON在相邻单元格用GetJsonProperty从A2里提取各个字段用普通公式对提取出来的数字做涨跌颜色、百分比、量比等二次加工设置好刷新方式让数据在你需要的时候更新。这个结构的好处是取数和展示分离。原始JSON放在一个隐藏列或者单独区域展示区只放解析后的干净数字。哪天数据源字段变了你只需要改解析公式展示区不用动。这是我在实际维护中总结出来的最重要的一条结构原则。3. 核心细节解析与实操要点3.1 JSON结构必须先看懂再动手新手最容易犯的错是拿到JSON看都不看就开始写提取公式结果字段名写错、层级搞错返回一堆错误值。JSON的本质是键值对的嵌套你可以把它想象成文件夹最外层是根目录里面可能有data、result、msg这些子文件夹真正的数据藏在某几层里面。以常见的行情返回为例字段可能藏在data下面也可能藏在result.list[0]这种数组里。数组是最容易翻车的地方因为数组有下标第一个元素是0不是1。如果你要取列表里的第一条路径里就得体现下标。我的习惯是拿到JSON先丢进任意一个在线格式化工具里展开看一遍把要用的字段路径用笔记下来再回Excel写公式。这一步花两分钟能省后面半小时的调试。3.2 GetJsonProperty的路径写法要点GetJsonProperty的第二个参数就是路径。路径的写法直接决定你能不能取到值。常见的几种情况单层字段直接写字段名比如price嵌套字段用点号连接比如data.price数组元素带下标比如data.list.0.price或者data.list[0].price具体语法看你的函数实现字段名含特殊字符有些返回的键带横线或空格这种要特别小心可能需要加引号或者转义。我踩过最坑的一次是字段名里有个大写字母我写成小写结果一直返回空。JSON的键是大小写敏感的Price和price是两个完全不同的东西。所以抄字段名的时候宁可复制粘贴不要手打。3.3 单元格引用与批量填充的技巧假设A2是原始JSONB2要取价格公式大概长这样GetJsonProperty(A2, data.price)C2取涨跌幅GetJsonProperty(A2, data.changePercent)如果你要盯多只标的最忌讳的是每行都手写一遍公式。正确做法是把标的代码单独放一列比如A列原始JSON用公式根据代码动态生成解析公式统一写好之后往下拖。这样新增一只标的只需要在代码列加一行其余全部自动带出来。注意往下拖公式时注意哪些引用要锁、哪些要相对。原始JSON那一列通常要锁列不锁行路径字符串如果是固定的就直接写死如果路径也要跟着变那就得用字符串拼接函数动态构造路径。3.4 数字格式化与涨跌颜色取出来的数字默认是文本还是数值取决于函数返回类型。如果发现取出来的价格不能求和、不能比较大小多半是被当成了文本。这时候可以用VALUE函数强制转换或者用乘1的方式GetJsonProperty(...)*1把它变成数字。涨跌颜色用条件格式做最省事选中涨跌幅那一列新建规则大于0显示红色小于0显示绿色A股习惯红涨绿跌如果你看的是别的市场反过来即可。这样一眼扫过去红绿分明比看数字快得多。4. 完整实操流程与关键环节实现4.1 环境确认与函数可用性测试动手第一步先在一个空白单元格里试一下GetStockSource能不能用。输入类似GetStockSource(600519)如果返回一坨JSON文本说明函数可用如果返回#NAME?说明你的Excel不认识这个函数可能是版本问题也可能是需要启用某个加载项。这一步必须先过不然后面全是空中楼阁。4.2 搭建原始数据区我习惯把原始数据区放在表格最右侧或者单独一个sheet命名为raw避免干扰展示区。结构大概是单元格内容说明A2标的代码手动填写或下拉选择B2原始JSONGetStockSource(A2)C2名称GetJsonProperty(B2,data.name)D2现价GetJsonProperty(B2,data.price)E2涨跌幅GetJsonProperty(B2,data.changePercent)这个结构清晰、可扩展。要加字段就在后面继续加列要加标的就往下加行。4.3 展示区的美化与信息浓缩展示区是给你自己瞄一眼用的信息要极度浓缩。我的做法是只保留四列名称、现价、涨跌幅、更新时间。涨跌幅用条件格式上色现价保留两位小数更新时间用NOW()或者数据源返回的时间戳。整个展示区控制在屏幕一屏之内扫一眼就完事不需要滚动。如果你想让展示区更低调可以把字体调成和背景接近的灰色或者把表格做得像一份普通的工作文档这样即使有人路过也不容易注意到。这是很多老哥的实际做法实用性拉满。4.4 刷新机制的设计公式方案最大的短板是刷新。Excel不会自动帮你重新拉数据除非你触发重算。几种常见的触发方式按F9手动重算整个工作簿按CtrlAltF9强制重算所有公式包括易失性函数修改任意单元格触发自动重算前提是开启了自动计算用NOW()这类易失性函数带动其他公式一起重算。我实测下来最省心的是把自动计算打开然后在展示区放一个NOW()单元格。每次你点一下表格、改个无关紧要的单元格整张表就会重算一遍数据也就跟着更新了。频率不用太高看日线的场景几分钟刷一次完全够。提示如果数据源有请求频率限制千万别把重算触发搞得太频繁否则容易被限流返回空值或者错误。4.5 一个可直接复用的最小示例把上面的东西拼起来一个最小可用的表格长这样A2: 600519 B2: GetStockSource(A2) C2: GetJsonProperty(B2,data.name) D2: GetJsonProperty(B2,data.price)*1 E2: GetJsonProperty(B2,data.changePercent)*1 F2: NOW()D2和E2后面的*1是为了强制转成数值。F2的NOW()既是时间戳也是重算的发动机。这套结构你复制到自己的表里改一下代码和字段路径就能跑。5. 常见问题与排查技巧实录5.1 返回 #NAME? 或函数不存在这是最常见的第一道坎。原因通常是Excel版本不支持这两个函数或者函数来自某个未启用的加载项。排查顺序先确认版本再确认加载项最后确认函数名拼写。如果确实不支持那这套方案在你的环境里就跑不通得换思路。5.2 返回空值或 #VALUE!多半是路径写错了。排查方法把原始JSON单独复制出来用格式化工具展开逐层核对字段名和层级。特别注意大小写、数组下标、以及字段是否真的存在。有时候数据源在非交易时段返回的结构和交易时段不一样字段可能缺失这种要用IFERROR包一层避免满屏错误值。5.3 数字变成文本无法计算前面提过用*1或VALUE转换。如果转换后还是不行检查一下取出来的值里是不是带了单位或者千分位逗号这种要先清洗再转换。5.4 数据不刷新先确认自动计算是否开启再确认有没有易失性函数带动重算。如果都正常但还是不刷新可能是数据源本身有缓存或者请求被限流了。这种情况等一会儿再试或者降低刷新频率。5.5 常见问题速查表现象可能原因解决方向#NAME?函数不支持或拼写错误确认版本与加载项#VALUE!路径错误或字段缺失核对JSON结构返回空非交易时段或限流加IFERROR降频数字是文本返回类型为字符串用VALUE或*1转换不刷新自动计算关闭或无易失函数开启自动计算加NOW()涨跌颜色不对条件格式规则写反检查大于小于方向5.6 几条独家避坑心得第一永远给解析公式包一层IFERROR返回一个空字符串或者--这样即使某次请求失败表格也不会满屏红叉看着糟心。第二原始JSON区一定要和展示区分开最好放在单独sheet并隐藏。JSON文本又长又乱混在展示区里既难看又容易误操作。第三字段路径不要硬编码在每一个公式里如果字段多可以在旁边放一列路径文本公式用INDIRECT或者字符串拼接去引用这样改路径只改一处。第四测试阶段先用一只你熟悉的标的确认整条链路通了再批量加。一上来就铺十几只出了问题你都不知道是哪只的哪一步坏了。第五注意合规与场合。这套东西是提升个人效率的工具用在合适的地方、合适的时机别因为盯盘影响了正事那就本末倒置了。6. 把表格变成你自己的行情看板这套方案跑通之后你会发现它的扩展空间比想象中大。比如你可以加一列量比用成交量除以某个均值可以加一列距成本价百分比把自己持仓的成本填进去自动算盈亏甚至可以用条件格式做一个简单的预警当涨跌幅超过某个阈值时整行变色。这些都不需要新函数全是Excel基础公式的排列组合。我自己用下来最深的体会是工具的价值不在于多高级而在于它能不能无缝嵌进你现有的工作流。Excel人人都有、人人会用把行情数据塞进这个最熟悉的容器里学习成本几乎为零这才是它真正的优势。至于刷新频率、字段多少、展示样式都可以按你自己的习惯慢慢调调到顺手为止。