ExcelでJSON整形・変換する方法

ExcelでJSONデータを扱う実用的な方法と具体例をご紹介します

ExcelでJSONを処理する3つの方法

1. Power Queryを使用する方法

Excel 2016以降で利用可能なPower Queryを使用してJSONデータを取り込み・整形できます：

「データ」タブ →「データの取得」→「ファイルから」→「JSONから」を選択
JSONファイルを選択して読み込み
Power Queryエディターで必要な列を選択
「閉じて読み込む」でExcelに取り込み

JSONデータ例：

{
  "employees": [
    {
      "name": "山田太郎",
      "age": 30,
      "department": "開発部"
    },
    {
      "name": "鈴木花子",
      "age": 25,
      "department": "営業部"
    }
  ]
}

Excel変換後：

name	age	department
山田太郎	30	開発部
鈴木花子	25	営業部

2. VBAを使用する方法

VBAスクリプトを使用してJSONデータを処理できます：

Sub JSONToExcel()
    ' JSONパーサーライブラリの参照設定が必要
    ' VBA-JSON: https://github.com/VBA-tools/VBA-JSON
    
    Dim jsonText As String
    Dim json As Object
    Dim item As Object
    Dim row As Long
    
    ' JSONテキストを読み込み
    jsonText = ReadTextFile("C:\data.json")
    
    ' JSONをパース
    Set json = JsonConverter.ParseJson(jsonText)
    
    ' ヘッダー行を設定
    Range("A1").Value = "name"
    Range("B1").Value = "age"
    Range("C1").Value = "department"
    
    ' データを書き込み
    row = 2
    For Each item In json("employees")
        Cells(row, 1).Value = item("name")
        Cells(row, 2).Value = item("age")
        Cells(row, 3).Value = item("department")
        row = row + 1
    Next item
End Sub

Function ReadTextFile(filePath As String) As String
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    With fso.OpenTextFile(filePath)
        ReadTextFile = .ReadAll()
        .Close
    End With
End Function

3. アドインを使用する方法

Excel用のJSON変換アドインを使用する方法：

Power JSON Editor

JSONデータの読み込み、編集、エクスポートが可能なアドイン
JSON Converter

ExcelデータとJSONの相互変換に特化したアドイン

ExcelからJSONへの変換

ExcelデータをJSONに変換するVBAスクリプト：

Sub ExcelToJSON()
    Dim ws As Worksheet
    Dim lastRow As Long, lastCol As Long
    Dim headers() As String
    Dim jsonStr As String
    Dim i As Long, j As Long
    
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    
    ' ヘッダー行を取得
    ReDim headers(1 To lastCol)
    For j = 1 To lastCol
        headers(j) = ws.Cells(1, j).Value
    Next j
    
    ' JSON配列を作成
    jsonStr = "{"
    jsonStr = jsonStr & Chr(34) & "data" & Chr(34) & ": ["
    
    ' データ行を処理
    For i = 2 To lastRow
        jsonStr = jsonStr & "{"
        For j = 1 To lastCol
            jsonStr = jsonStr & Chr(34) & headers(j) & Chr(34)
            jsonStr = jsonStr & ": "
            jsonStr = jsonStr & Chr(34) & ws.Cells(i, j).Value & Chr(34)
            If j < lastCol Then jsonStr = jsonStr & ","
        Next j
        jsonStr = jsonStr & "}"
        If i < lastRow Then jsonStr = jsonStr & ","
    Next i
    
    jsonStr = jsonStr & "]}"
    
    ' 結果を出力
    Debug.Print jsonStr
End Sub

高度な使用方法

Power Queryの高度な変換

let
    Source = Json.Document(File.Contents("data.json")),
    employees = Source[employees],
    #"変換されたテーブル" = Table.FromList(
        employees, Splitter.SplitByNothing(), null, 
        null, ExtraValues.Error
    ),
    #"展開された列" = Table.ExpandRecordColumn(
        #"変換されたテーブル", "Column1", 
        {"name", "age", "department"}
    )
in
    #"展開された列"

Power Queryの高度な変換を使用すると、複雑なJSONデータも効率的に処理できます。

よくある質問

Q: 日本語を含むJSONデータを処理する際の注意点は？

A: VBAでJSONを処理する場合は、文字エンコーディングをUTF-8に設定し、Power Queryを使用する場合は自動的に文字コードが処理されます。

Q: 大量のデータを含むJSONファイルを処理するには？

A: Power Queryを使用すると、メモリ効率よく大量のデータを処理できます。必要に応じてデータを分割して処理することも検討してください。

Q: ネストされたJSONデータを処理するには？

A: Power Queryの「列の展開」機能を使用するか、VBAで再帰的な処理を実装することで、ネストされたJSONデータを処理できます。

オンラインJSON整形ツールを使用する