【Excel】エクセルとJSONを変換する方法（データ形式・API・インポート・エクスポート・構造化データ）

【Excel】エクセルとJSONを変換する方法（データ形式・API・インポート・エクスポート・構造化データ）を理解すると、APIデータ、Webサービスの出力、アプリの設定ファイル、商品データ、顧客情報などを表形式で整理しやすくなります。

JSONは、キーと値の組み合わせでデータを表す構造化データ形式です。

エクセルは表形式でデータを見るのが得意なため、JSONを取り込んで列と行に変換すると、確認、編集、集計がしやすくなります。

この記事では、エクセルにJSONをインポートする方法、Power Queryを使った変換、エクセル表からJSON風データを作る方法、API連携で注意したいポイントを解説します。

エクセルとJSONを変換する結論はPower Queryで構造を展開すること

それではまずエクセルとJSONを変換する基本結論について解説していきます。

JSONをエクセルで表に変換するなら、Power Queryを使ってデータを読み込み、レコードやリストを展開して列にする方法が便利です。

反対に、エクセル表をJSONとして出力したい場合は、関数で文字列を組み立てる方法や、VBA、Officeスクリプト、外部ツールを使う方法があります。

JSONは構造化データの形式

JSONは、項目名と値を組み合わせてデータを表す形式です。

Web API、設定ファイル、アプリのデータ受け渡しなどでよく使われます。

テキスト形式なので軽く、人にも比較的読みやすい点が特徴です。

エクセルは表形式の整理に向いている

エクセルは、行と列でデータを一覧化し、並べ替え、フィルター、集計を行うのが得意です。

JSONを表に変換すれば、APIから取得したデータも通常の一覧表のように扱えます。

JSONの階層構造をエクセルの列に落とし込むことが、変換作業の中心です。

入れ子構造はそのまま表にしにくい

JSONには、1つの項目の中にさらに複数の項目が入る入れ子構造があります。

例えば顧客情報の中に住所、購入履歴、連絡先が入るような形です。

この場合、単純に開くだけでは表にならないため、必要な階層を展開する作業が必要になります。

JSONをエクセルにインポートする方法を確認していきます

続いてはJSONをエクセルにインポートする方法を確認していきます。

エクセルには、JSONファイルを取り込むための機能が用意されています。

特にPower Queryを使うと、複雑なデータでも段階的に整形できます。

データタブからJSONを読み込む

エクセルのデータタブから、データの取得を選び、ファイルからJSONを選択します。

JSONファイルを指定すると、Power Queryエディターが開きます。

ここでデータの中身を確認しながら、表形式に変換していきます。

レコードを展開して列にする

JSONの各項目がレコードとして表示される場合、展開ボタンを使って列に分けます。

名前、価格、在庫数、カテゴリなどの項目を列として表示できます。

必要のない列は削除し、必要な列だけを残すと見やすい表になります。

リストをテーブルに変換する

JSONの中に複数データが配列として入っている場合、リストをテーブルに変換する作業が必要です。

リストを展開すると、複数行のデータとして扱えるようになります。

APIから取得した商品一覧や投稿一覧などは、この形になることが多いでしょう。

JSONの要素	意味	エクセルでの変換イメージ
キー	項目名を表します。	列見出しになります。
値	項目の中身を表します。	セルの値になります。
レコード	複数のキーと値のまとまりです。	1行または複数列に展開します。
リスト	複数データの並びです。	複数行の表に変換します。

APIのJSONデータをエクセルに取り込む方法を確認していきます

続いてはAPIのJSONデータをエクセルに取り込む方法を確認していきます。

WebサービスのAPIでは、JSON形式でデータが返ってくることがよくあります。

Power Queryを使えば、URLからデータを取得してエクセル表に変換できる場合があります。

Webからデータを取得する

データタブからWebから取得を選び、APIのURLを入力します。

認証が不要なAPIであれば、そのままデータを読み込める場合があります。

読み込み後は、JSONファイルと同じようにPower Queryで展開します。

認証が必要なAPIに注意する

APIによっては、APIキー、トークン、ログイン情報が必要です。

認証情報を含むURLやキーを不用意に共有すると、情報漏えいにつながるおそれがあります。

業務データを扱う場合は、社内ルールに従って管理しましょう。

更新機能で最新データにする

Power Queryで取り込んだデータは、更新ボタンで再取得できる場合があります。

在庫、価格、アクセス数、投稿データなど、変化する情報の確認に便利です。

ただし、APIの利用回数制限がある場合は、更新頻度に注意しましょう。

エクセル表をJSONに変換する方法を確認していきます

続いてはエクセル表をJSONに変換する方法を確認していきます。

エクセルで編集したデータを、Webシステムやアプリへ渡すためにJSON形式へしたい場合があります。

エクセル標準機能だけで完全なJSON出力を行うのは少し工夫が必要ですが、関数やスクリプトを使えば対応できます。

関数でJSON風の文字列を作る

少量のデータであれば、セルの値を連結してJSON風の文字列を作れます。

商品名、価格、在庫数などを決まった形でつなげれば、簡易的なデータ出力が可能です。

ただし、ダブルクォーテーションやカンマの扱いを間違えると、正しいJSONとして認識されません。

VBAやOfficeスクリプトを使う

大量の行をJSONに変換する場合は、VBAやOfficeスクリプトを使う方法があります。

表の見出しをキーとして読み取り、各行をレコードとして出力する仕組みを作れます。

定期的に同じ形式でエクスポートする業務では、スクリプト化すると作業時間を減らせます。

外部ツールを使って変換する

一時的な変換なら、CSVやExcelファイルをJSONへ変換できる外部ツールを使う方法もあります。

ただし、個人情報や社外秘データを外部サイトへアップロードするのは避けたほうが安全です。

機密性の低いテストデータで使うか、ローカルで動くツールを検討するとよいでしょう。

JSON変換で失敗しないコツを確認していきます

続いてはJSON変換で失敗しないコツを確認していきます。

JSONとエクセルの変換では、データの階層、文字コード、数値と文字列の違い、空白の扱いでつまずくことがあります。

変換前にデータの形を確認しておくと、後からの修正を減らせます。

列名をわかりやすく整える

エクセル表からJSONに変換する場合、列名がそのままキーになることがあります。

列名に余計な空白や重複があると、扱いにくいJSONになります。

英数字のキー名にそろえると、システム連携で使いやすいでしょう。

数値と文字列を区別する

商品コードや郵便番号は数字に見えても、先頭の0が必要な文字列である場合があります。

エクセルで数値として扱うと、先頭の0が消えてしまうことがあります。

JSONへ変換する前に、数値として扱う項目と文字列として扱う項目を分けて確認することが大切です。

入れ子構造は無理に平らにしすぎない

JSONには、住所の中に都道府県や市区町村があるような階層構造があります。

エクセルで扱うために平らな表へ変換することはできますが、元の構造を失うとシステム側で使いにくくなる場合があります。

編集用の表と出力用のJSON構造を分けて考えると、運用しやすくなります。

まとめ

エクセルとJSONを変換するには、JSONのキー、値、レコード、リストといった構造を理解することが大切です。

JSONをエクセルに取り込む場合は、Power Queryを使ってレコードやリストを展開し、表形式へ変換する方法が便利です。

APIからJSONを取得する場合は、URL、認証情報、利用回数制限、データ更新の仕組みを確認しましょう。

エクセル表をJSONに変換する場合は、関数、VBA、Officeスクリプト、外部ツールなどを目的に合わせて使い分けます。

JSONは構造化データ、エクセルは表形式の整理に強いツールなので、両方の特徴を理解して変換すれば、API連携やデータ管理を効率化できます。