Excel中从结构不一的JSON字符串提取ActiveServices值求助

问题背景 #

我有数百条单行JSON字符串，需要提取其中ActiveServices对应的value值，但JSON结构不统一，ActiveServices的位置不固定，示例如下：

• 示例1：[{"id":"ActiveServices","type":"count","value":"1"},{"id":"HasActiveService","type":"boolean","value":"true"}]
• 示例2：[{"id":"HasActiveService","type":"boolean","value":"true"},{"id":"ActiveServices","type":"count","value":"16"}]
• 示例3：[{"id":"HasActiveIssue","type":"boolean","value":"true"},{"id":"HasActiveNewIssue","type":"boolean","value":"true"},{"id":"ActiveLowIssues","type":"count","value":"1"},{"id":"ActiveServices","type":"count","value":"1"},{"id":"HasActiveService","type":"boolean","value":"true"},{"id":"ActiveNewIssues","type":"count","value":"1"},{"id":"ActiveIssues","type":"count","value":"1"},{"id":"HasActiveLowIssue","type":"boolean","value":"true"}]

当前使用的Excel公式仅适配部分结构，公式如下：

=TRIM(MID(P4,FIND("value", P4, FIND("""""&Q4&""""", P4))+8,SEARCH(",",P4,FIND("value", P4, FIND("""""&Q4&""""", P4)))-FIND("value", P4, FIND("""""&Q4&""""", P4))-10))

其中P4为JSON字符串单元格，Q4为目标关键词"ActiveServices"，需要通用解决方案。

通用解决方案 #

方案1：使用Excel Power Query（推荐） #

Power Query原生支持JSON解析，能忽略结构位置差异精准提取值，步骤如下：

1. 选中包含JSON字符串的列（如列P），点击数据选项卡 → 从表格/区域，将数据导入Power Query编辑器
2. 在编辑器中选中目标列，点击转换选项卡 → JSON，将字符串解析为JSON结构
3. 展开解析后的列表列：点击列标题旁的展开图标 → 选择扩展到新行，将每个JSON对象拆分为单独行
4. 再次展开对象列：点击展开图标，勾选id和value列后确定
5. 添加筛选：筛选id列等于ActiveServices，此时value列即为目标值
6. 点击关闭并上载，将结果导入Excel表格

方案2：自定义VBA函数 #

如果更习惯用函数调用，可编写VBA函数实现通用提取：

1. 按Alt+F11打开VBA编辑器
2. 插入新模块，粘贴以下代码：

Function GetActiveServicesValue(jsonStr As String) As String
    Dim jsonObj As Object
    Dim item As Object
    
    ' 解析JSON数组
    Set jsonObj = CreateObject("Scripting.Dictionary")
    Set jsonObj = JsonConverter.ParseJson(jsonStr)
    
    For Each item In jsonObj
        If item("id") = "ActiveServices" Then
            GetActiveServicesValue = item("value")
            Exit Function
        End If
    Next item
    
    GetActiveServicesValue = "" ' 未找到时返回空字符串
End Function

注意：需先安装JsonConverter模块，可在VBA编辑器中通过工具→引用添加，或导入对应模块文件

3. 在Excel单元格中调用：=GetActiveServicesValue(P4)，即可返回对应value值

方案3：优化Excel公式（无需工具/代码） #

针对原始公式的局限性，优化为适配所有位置的通用公式：

=TRIM(MID(P4,FIND("""value""",P4,FIND("""ActiveServices""",P4))+8,SEARCH("""",P4,FIND("""value""",P4,FIND("""ActiveServices""",P4))+8)-FIND("""value""",P4,FIND("""ActiveServices""",P4))-8))

该公式先定位ActiveServices的位置，再在其之后找到value字段，最后提取引号内的数值，不受ActiveServices在数组中的位置影响。

内容的提问来源于stack exchange，提问作者Andrew Mizuno