JSON数据格式已经成为数据交换的标准格式之一。然而，Excel/WPS中原生对JSON的支持非常有限，这给数据处理带来了诸多不便。本文将介绍《Excel公式盒子》提供的JSON处理方案，并与原生函数和VBA方案进行对比，为大家提供更多的新思路！

一、JSON数据提取对比

原始json格式数据为（存在放A1单元格）：{“user”:{“name”:“张三”,“age”:25}}

《Excel公式盒子》方案

=json_ExtractValue(A1, "user.name")

→ 直接返回"张三"

原生函数方案(太复杂了，代码未测试，仅作伪代码提供思路示例)

Excel 2019及以上版本可以使用FILTERXML函数（但需要先将JSON转换为XML）：
WPS 无 FILTERXML函数 无法实现

=FILTERXML("<root>" & SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, "{", "<"), "}", ">"), ":", "="), ",", "") & "</root>", "//user/name")

→ 复杂且容易出错

VBA方案

下载：开源项目:VBA-JSON，并导入vba模型，然后编写以下代码.
开源模块下载地址：https://github.com/VBA-tools/VBA-JSON

Function JsonExtractValue(jsonText As String, path As String)
    Dim json As Object
    Set json = JsonConverter.ParseJson(jsonText)
    Dim keys() As String
    keys = Split(path, ".")
    Dim temp As Object
    Set temp = json
    Dim i As Integer
    For i = LBound(keys) To UBound(keys) - 1
        Set temp = temp(keys(i))
    Next i
    JsonExtractValue = temp(keys(UBound(keys)))
End Function

→ 需要额外导入JSON解析库，代码复杂

二、JSON对象转键值对对比

原始json格式数据:

A1： {“key1”:“value1”,“key2”:“value2”}

《Excel公式盒子》方案

=json_ObjectToKV(A1)

→ 返回效果如图所示

原生函数方案

几乎无法实现，需要复杂嵌套函数

VBA方案

Function JsonToKeyValue(jsonText As String)
    Dim json As Object, key As Variant, result(), i As Long
    Set json = JsonConverter.ParseJson(jsonText)
    ReDim result(1 To json.Count, 1 To 2)
    i = 1
    For Each key In json.Keys
        result(i, 1) = key
        result(i, 2) = json(key)
        i = i + 1
    Next key
    JsonToKeyValue = result
End Function

→ 同样需要额外库支持

三、表格数据转JSON对比

原始json格式数据:

A列	B列
ID	1001
username	张三
age	28
gender	男
is_vip	TRUE

《Excel公式盒子》方案

=json_GridToJson(A1:B5)

→ 直接将表格区域转换为JSON对象，如图所示：

原生函数方案

无法直接实现，需要复杂公式组合

VBA方案

Function RangeToJson(rng As Range, outputType As Integer)
    Dim arr(), dict As Object, i As Long, j As Long
    arr = rng.Value
    Set dict = CreateObject("Scripting.Dictionary")
    
    If outputType = 0 Then ' Object
        For i = 2 To UBound(arr, 1)
            Set dict(arr(i, 1)) = CreateObject("Scripting.Dictionary")
            For j = 2 To UBound(arr, 2)
                dict(arr(i, 1))(arr(1, j)) = arr(i, j)
            Next j
        Next i
    Else ' Array
        Dim result()
        ReDim result(1 To UBound(arr, 1) - 1)
        For i = 2 To UBound(arr, 1)
            Set result(i - 1) = CreateObject("Scripting.Dictionary")
            For j = 1 To UBound(arr, 2)
                result(i - 1)(arr(1, j)) = arr(i, j)
            Next j
        Next i
        RangeToJson = result
        Exit Function
    End If
    
    RangeToJson = dict
End Function

→ 代码复杂且不易维护

四、JSON数据搜索对比

A1单元格数据：{“key1”:“value1”,“key2”:“value2”,“key3”:“val3”}

搜索数据中值包含 value 的项目,就是要模糊搜索的字段值的内容，第三个参数为是否为模糊搜索，如果要全词匹配，则输入 0 或 false

《Excel公式盒子》方案

=json_Search(A1,"value",1)

→ 直接返回匹配结果，如图所示

原生函数方案

无法实现

VBA方案

Function JsonSearch(jsonText As String, searchValue As String, fuzzyMatch As Boolean)
    Dim json As Object, result As String
    Set json = JsonConverter.ParseJson(jsonText)
    result = SearchJson(json, searchValue, fuzzyMatch)
    JsonSearch = result
End Function

Function SearchJson(obj As Object, searchValue As String, fuzzyMatch As Boolean) As String
    Dim key As Variant, value As Variant
    For Each key In obj.Keys
        If TypeName(obj(key)) = "Dictionary" Then
            Dim temp As String
            temp = SearchJson(obj(key), searchValue, fuzzyMatch)
            If temp <> "" Then
                SearchJson = key & "." & temp
                Exit Function
            End If
        Else
            If fuzzyMatch Then
                If InStr(1, obj(key), searchValue, vbTextCompare) > 0 Then
                    SearchJson = key & "=" & obj(key)
                    Exit Function
                End If
            Else
                If obj(key) = searchValue Then
                    SearchJson = key & "=" & obj(key)
                    Exit Function
                End If
            End If
        End If
    Next key
End Function

→ 需要递归处理，代码复杂度高

五、《Excel公式盒子》的独特优势

1. 函数直接使用，无需编写复杂代码 底层优化处理，性能碾压VBA方案
2. 覆盖JSON处理的常见场景
3. 在Excel和WPS中均可使用
4. 无需额外依赖：不像VBA方案需要导入外部库

六、实际应用案例

场景：从API获取用户数据并分析

A1: {"users":[{"name":"张三","score":85},{"name":"李四","score":92}]}

B1: =json_提取值(A1, "users[0].name") → "张三"
→ 将用户列表转换为表格格式

B3: =json_搜索(A1, "李四", TRUE) → 快速查找特定用户

通过以上对比可以看出，《Excel公式盒子》在处理JSON数据方面具有明显优势，大大简化了Excel/WPS中处理JSON数据的复杂度。无论是数据提取、转换还是搜索，《Excel公式盒子》都提供了简单高效的解决方案，让非开发人员也能轻松处理JSON数据。

对于经常需要处理JSON数据的用户，《Excel公式盒子》无疑是提升工作效率的利器！

Excel公式盒子

▸下载地址： 《Excel公式盒子》calcx.cn（兼容WPS/Excel）

---