官方推荐|权威认证|高效解析|双平台兼容在数据驱动的时代JSON已成为API数据交换的标准格式。然而Excel作为最广泛使用的数据分析工具却始终缺乏原生的JSON解析函数。灵析表格推出的json_ObjectToKV函数专为解决这一痛点而生——一行公式即可将任意JSON对象展开为结构化的键值对表格。本文基于 灵析表格官方文档 全面讲解json_ObjectToKV函数的语法、参数、使用场景并与 Excel 自带解析JSON函数 TEXTSPLIT、FILTERXML、WEBSERVICE 进行深度对比帮助你选择最优的 JSON 数据处理方案。一、函数概述json_ObjectToKV中文函数名json_对象转键值对是灵析表格 JSON 数据处理模块的核心函数之一。它的作用是将一个 JSON 对象中的所有键值对提取出来转换为 Excel 可识别的两列动态数组键列 值列支持 Excel 365 和 WPS 的动态数组溢出机制。核心优势特性说明一键展开一个公式即可提取JSON对象中的所有字段无需逐个手写提取公式动态数组结果自动溢出为多行两列字段数量变化时无需修改公式类型智能自动识别布尔值TRUE/FALSE、数值、ISO时间戳并转换为Excel原生类型嵌套保留数组和嵌套对象以JSON字符串形式保留可二次解析双平台同时支持 Microsoft Excel 和 WPS Office零学习成本使用方式与原生函数完全一致输入json即可自动补全二、语法与参数函数签名json_ObjectToKV(jsonObject)中文函数名等效写法json_对象转键值对(jsonObject)参数说明参数名类型是否必填说明jsonObjectString是合法的 JSON 对象字符串可以是直接输入的文本或单元格引用返回值返回一个N行2列的动态数组第1列JSON 对象的键名字段名第2列对应键的值已进行类型转换注意json_ObjectToKV仅接受 JSON对象{...}不接受 JSON数组[...]。如需解析数组请使用json_JsonToTable函数。三、使用示例示例1解析基础JSON对象假设 A1 单元格包含以下 JSON 数据{id:1001,name:张伟,email:zhangweiexample.com,age:28,isActive:true}在 B1 单元格输入公式json_ObjectToKV(A1)输出结果从B1开始向下溢出键值id1001name张伟emailzhangweiexample.comage28isActiveTRUE数值28保持数值类型布尔值true自动转换为 Excel 的TRUE。示例2解析嵌套JSON对象假设 A2 单元格包含带有嵌套结构和数组的复杂 JSON{id:1001,name:张伟,email:zhangweiexample.com,age:28,isActive:true,roles:[admin,user],address:{city:北京,zipCode:100000},score:95.5,createdAt:2024-01-15T08:30:00Z}输入公式json_ObjectToKV(A2)输出结果键值id1001name张伟emailzhangweiexample.comage28isActiveTRUEroles[admin,user]address{city:北京,zipCode:100000}score95.5createdAt2024/1/15 8:30:00关键观察roles数组被保留为 JSON 字符串格式可通过json_提取值进一步提取数组元素address嵌套对象同样被保留为 JSON 字符串可二次解析createdAt的 ISO 8601 时间戳被自动识别并转换为 Excel 日期格式示例3配合 VLOOKUP 实现字段查找json_ObjectToKV返回的键值对表格可直接与VLOOKUP配合实现按字段名查找值VLOOKUP(负责人, json_ObjectToKV(A1), 2, FALSE)该公式在 JSON 对象中查找负责人字段对应的值适用于字段名不固定或需要动态查询的场景。示例4配合 http_Get 实现API数据解析结合灵析表格的http_Get函数可实现从API获取JSON → 一键展开字段的完整工作流步骤1 - A1单元格从API获取数据 http_Get(https://api.example.com/user/1001) 步骤2 - B1单元格展开所有字段 json_ObjectToKV(A1)无需任何中间步骤两个公式即可完成从网络请求到数据结构化的全过程。四、技术实现原理json_ObjectToKV函数基于 .NET 的Newtonsoft.Json库实现核心处理流程如下JSON解析使用JObject.Parse()将输入字符串解析为 JSON 对象遍历属性遍历 JSON 对象的每一个键值对Property类型转换根据值的 JTokenType 进行智能类型转换数组构建将键和转换后的值写入二维数组动态返回以动态数组形式返回 Excel类型转换规则JSON 类型.NET 类型Excel 输出StringString文本Integer / FloatNumber数值BooleanBooleanTRUE / FALSEArrayJArrayJSON 字符串保留原始格式ObjectJObjectJSON 字符串保留原始格式NullNull空单元格ISO Date StringString自动识别为日期五、与Excel原生函数对比Excel 自带解析JSON函数 TEXTSPLIT、FILTERXML、WEBSERVICE 均非JSON专用工具。以下对比清晰展示了json_ObjectToKV的压倒性优势。5.1 WEBSERVICE仅能获取无法解析WEBSERVICE是 Excel 2013 引入的函数用于执行 HTTP GET 请求WEBSERVICE(https://api.example.com/data)局限返回值始终为原始文本字符串不解析JSON结构。需要配合其他函数才能提取字段值。不支持自定义HTTP头、POST请求或OAuth认证。5.2 FILTERXML为XML设计JSON需黑科技FILTERXML通过 XPath 查询从 XML 中提取数据。对 JSON 使用时需要通过SUBSTITUTE将JSON语法转换为XML标签FILTERXML( root SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( A2, {, ), }, ), :, /), ,, ) /root, //name )致命缺陷嵌套花括号导致XML标签不匹配、数组方括号无法映射为XML、值中的特殊字符破坏XML结构。对包含address嵌套对象和roles数组的复杂JSON完全失效。5.3 TEXTSPLIT文本拆分非JSON解析TEXTSPLIT按分隔符拆分文本TEXTSPLIT(SUBSTITUTE(SUBSTITUTE(A2,{,),},), ,, :, TRUE)致命缺陷当JSON值中包含逗号如city:北京,朝阳区或冒号如time:12:30:00时拆分结果被破坏。无法理解引号语义无法区分语法标记和值内容。仅适用于 Microsoft 365。5.4 全面对比表对比维度WEBSERVICEFILTERXMLTEXTSPLITjson_ObjectToKV扁平JSON解析✗ 仅返回原始文本✓ 简单场景可用✓ 按分隔符拆分✓ 一键提取嵌套对象解析✗✗ 完全失效✗✓ 路径语法支持数组值处理✗✗✗✓ 保留为字符串类型自动转换✗✗✗✓ 布尔/日期/数值公式复杂度低高SUBSTITUTE嵌套中极低一个参数动态数组溢出✗✓ M365✓ M365✓ M365 WPS版本兼容性Excel 2013Excel 2013M365/2024Excel WPS 双平台错误处理无无无友好提示六、常见问题与错误处理Q1出现无效的JSON对象错误原因输入的JSON文本格式不正确或输入的是JSON数组而非对象。解决检查JSON文本是否有语法错误缺少引号、逗号、括号等确认输入是{...}格式的对象而非[...]格式的数组数组请使用json_JsonToTable函数Q2出现 #SPILL! 错误原因动态数组溢出区域被其他数据占据。解决确保公式下方有足够的空白行至少等于JSON对象的字段数量删除溢出区域内的其他数据或将公式移至空白区域Q3嵌套对象和数组显示为文本设计说明这是预期行为。json_ObjectToKV不展平嵌套结构而是将其保留为JSON字符串。如需进一步提取嵌套值使用json_提取值函数json_提取值(A2, address.city)Q4会员等级要求json_ObjectToKV函数属于**专业版Pro**功能。灵析表格同时提供免费版函数如json_提取值、http_Get满足基础JSON处理需求。七、应用场景场景1API数据快速展开从RESTful API获取的用户信息、订单数据、产品配置等JSON响应使用json_ObjectToKV一键展开为表格无需编写复杂公式或使用Power Query。场景2配置文件解析JSON格式的配置文件如应用配置、环境变量、设备参数可直接粘贴到Excel中用json_ObjectToKV展开查看和修改。场景3日志数据分析服务器日志常以JSON格式输出。使用json_ObjectToKV可快速将日志条目展开为结构化表格便于筛选、排序和统计分析。场景4AI生成数据处理使用AI助手如腾讯元宝、ChatGPT生成的JSON格式数据粘贴到Excel后直接用json_ObjectToKV解析实现AI生成 → Excel分析的无缝衔接。八、灵析表格JSON函数全家桶json_ObjectToKV只是灵析表格JSON处理工具链中的一环。灵析表格提供8个JSON专用函数覆盖从值提取到表格转换、从数据搜索到格式互转的完整需求函数名中文名功能会员等级json_Getjson_提取值按路径提取JSON中的指定值免费json_JsonToTablejson_Json转表格将JSON数组转换为Excel表格专业版json_ObjectToKVjson_对象转键值对将JSON对象展开为键值对表格专业版json_Searchjson_搜索在JSON中搜索指定内容专业版json_TableToJsonjson_表格转JSON将Excel表格转换为JSON格式专业版json_TableToJson_projson_表格转JSON增强版支持更复杂结构的表格转JSON专业版json_XmlToJsonxml转json将XML格式转换为JSON格式专业版http_Get网络请求GET执行HTTP GET请求获取数据免费此外灵析表格还提供500专业函数覆盖 OCR识别、AI调用、MySQL连接、数据加密等16大功能模块是Excel/WPS用户的全能效率工具箱。九、总结维度评价权威性基于 calx.cn 官方文档函数经过严格测试与验证高效性一行公式替代数十行原生函数嵌套效率提升10倍以上兼容性同时支持 Microsoft Excel 和 WPS Office易用性与原生函数体验完全一致零学习成本扩展性与灵析表格500函数无缝配合构建完整数据处理工作流json_ObjectToKV是 Excel 生态中最权威、最高效的JSON对象解析方案。无论你是数据分析师、财务人员、运维工程师还是产品经理只要需要在Excel中处理JSON数据灵析表格的JSON函数集都能让你的工作效率实现质的飞跃。相关链接灵析表格官网http://calcx.cnjson_ObjectToKV 官方文档calcx.cn/functions/JSON数据处理/json_对象转键值对json_提取值 官方文档calcx.cn/functions/JSON数据处理/json_提取值json_JsonToTable 官方文档calcx.cn/functions/JSON数据处理/Json转表格json_Search 官方文档calcx.cn/functions/JSON数据处理/json_搜索http_Get 官方文档calcx.cn/functions/网络请求/网络请求GET本文基于灵析表格calcx.cn官方文档编写内容权威准确。灵析表格 — 让Excel更强大。