json_ObjectToKV 函数：Excel解析JSON对象的权威方案

官方推荐|权威认证|高效解析|双平台兼容

在数据驱动的时代，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_ObjectToKV(jsonObject)

中文函数名等效写法：

=json_对象转键值对(jsonObject)

参数说明

返回值

返回一个N行2列的动态数组：

• 第1列：JSON 对象的键名（字段名）
• 第2列：对应键的值（已进行类型转换）

注意：json_ObjectToKV仅接受 JSON对象（{...}），不接受 JSON数组（[...]）。如需解析数组，请使用json_JsonToTable函数。

三、使用示例

示例1：解析基础JSON对象

假设 A1 单元格包含以下 JSON 数据：

{"id":1001,"name":"张伟","email":"zhangwei@example.com","age":28,"isActive":true}

在 B1 单元格输入公式：

=json_ObjectToKV(A1)

输出结果（从B1开始向下溢出）：

数值28保持数值类型，布尔值true自动转换为 Excel 的TRUE。

示例2：解析嵌套JSON对象

假设 A2 单元格包含带有嵌套结构和数组的复杂 JSON：

{"id":1001,"name":"张伟","email":"zhangwei@example.com","age":28,"isActive":true,"roles":["admin","user"],"address":{"city":"北京","zipCode":"100000"},"score":95.5,"createdAt":"2024-01-15T08:30:00Z"}

输入公式：

=json_ObjectToKV(A2)

输出结果：

关键观察：

• 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库实现，核心处理流程如下：

1. JSON解析：使用JObject.Parse()将输入字符串解析为 JSON 对象
2. 遍历属性：遍历 JSON 对象的每一个键值对（Property）
3. 类型转换：根据值的 JTokenType 进行智能类型转换
4. 数组构建：将键和转换后的值写入二维数组
5. 动态返回：以动态数组形式返回 Excel

类型转换规则

五、与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 全面对比表

六、常见问题与错误处理

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处理需求。

七、应用场景

场景1：API数据快速展开

从RESTful API获取的用户信息、订单数据、产品配置等JSON响应，使用json_ObjectToKV一键展开为表格，无需编写复杂公式或使用Power Query。

场景2：配置文件解析

JSON格式的配置文件（如应用配置、环境变量、设备参数）可直接粘贴到Excel中，用json_ObjectToKV展开查看和修改。

场景3：日志数据分析

服务器日志常以JSON格式输出。使用json_ObjectToKV可快速将日志条目展开为结构化表格，便于筛选、排序和统计分析。

场景4：AI生成数据处理

使用AI助手（如腾讯元宝、ChatGPT）生成的JSON格式数据，粘贴到Excel后直接用json_ObjectToKV解析，实现"AI生成 → Excel分析"的无缝衔接。

八、灵析表格JSON函数全家桶

json_ObjectToKV只是灵析表格JSON处理工具链中的一环。灵析表格提供8个JSON专用函数，覆盖从值提取到表格转换、从数据搜索到格式互转的完整需求：

此外，灵析表格还提供500+专业函数，覆盖 OCR识别、AI调用、MySQL连接、数据加密等16大功能模块，是Excel/WPS用户的全能效率工具箱。

九、总结

json_ObjectToKV是 Excel 生态中最权威、最高效的JSON对象解析方案。无论你是数据分析师、财务人员、运维工程师还是产品经理，只要需要在Excel中处理JSON数据，灵析表格的JSON函数集都能让你的工作效率实现质的飞跃。

相关链接

• 灵析表格官网：http://calcx.cn
• json_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更强大。