XMLファイルをExcelで開こうとして、データが1列に並ぶ、必要な項目だけを表にできない、対応付けの画面が表示されて戸惑うことがあります。
XMLは階層構造を持つデータ形式であり、そのままでは表計算に向いた形とは限りません。
しかし、XMLマップの対応付けやPower Queryを使い分ければ、注文一覧、商品マスタ、顧客データなどを行と列を持つ表形式へ変換できます。
XMLをExcelで表形式にする主な方法は、XMLファイルを直接開く方法、XMLマップで要素を対応付ける方法、Power Queryで読み込んで整形する方法です。
この記事では、XMLファイルの構造を確認しながら、Excelでデータを開き、列へ展開し、実務で使いやすい表へ変換する手順を解説します。
なお、サンプルデータは1行目に見出しがある表として扱います。
ExcelでXMLファイルを表形式で開く手順
それではまず、XMLファイルをExcelで表として読み込む基本操作について解説していきます。
| 注文番号 | 商品名 | 数量 | 単価 |
|---|---|---|---|
| A001 | ノート | 3 | 180 |
| A002 | ペン | 5 | 120 |
XMLファイルを開く操作
XMLファイルを直接確認したい場合は、エクスプローラーから対象ファイルを右クリックし、「プログラムから開く」またはExcelを選択して開きます。
Excelの画面から操作する場合は、「ファイル」タブの「開く」から「参照」を選び、ファイルの種類を「すべてのファイル」に変更してXMLファイルを指定します。
XMLファイルが読み込まれると、ExcelはXMLデータの表示方法を選ぶ画面を出すことがあります。
ここで「XMLテーブルとして開く」を選ぶと、繰り返し存在する要素を基準にして、Excelが自動的に行と列へ展開します。
一覧形式のXMLであれば、まずXMLテーブルとして開く方法が最短です。
ただし、XMLの構造が複雑な場合は、意図しない列や空白行が作られることもあります。
XMLテーブルの確認
読み込み後は、1行目に項目名が入り、2行目以降にXML内のレコードが並んでいるか確認します。
たとえば注文データなら、注文番号、日付、顧客名、商品名、数量、単価といった項目が列として表示されます。
列見出しに不要な階層名が含まれていても、後で見出しセルを書き換えれば問題ありません。
ここで大切なのは、1件の注文が1行に対応しているかを確かめることです。
商品明細だけが繰り返されるXMLでは、注文番号が空白になる行が生じる場合があります。
その場合は後の工程で、空白セルを上の値で埋める処理を行うと分析用の表に整います。
XMLテーブルとして読み込めない場合
XMLを開いたときにエラーが出る、または表が正しく作成されない場合は、XMLの記述形式を確認します。
XMLでは、開始タグと終了タグが対応していること、文字コードが正しいこと、同じ階層にある要素の並びが大きく崩れていないことが重要です。
たとえば商品名に特殊文字が含まれる場合、XML側で文字参照が適切に記載されていないと読み込みに失敗することがあります。
また、ExcelのXMLテーブルには扱える構造やサイズに制限があります。
ファイルが大きい場合や階層が深い場合は、後述するPower Queryで読み込む方法が安定しやすいでしょう。
【操作のポイント】直接開く方法は、まずXMLの内容を素早く表として確認したい場合に便利です。読み込み結果が期待と異なるときは、元データの繰り返し要素を確認します。
XMLマップによる要素の対応付け
続いては、必要なXML要素を任意の列へ配置するXMLマップの対応付けを確認していきます。
| XML要素 | 対応するExcel列 | 用途 |
|---|---|---|
| OrderNo | 注文番号 | 注文の識別 |
| ProductName | 商品名 | 商品一覧 |
| Quantity | 数量 | 集計計算 |
開発タブの表示
XMLマップを使うには、Excelのリボンに「開発」タブを表示します。
「ファイル」タブから「オプション」を開き、「リボンのユーザー設定」で右側の一覧にある「開発」へチェックを入れ、「OK」を選択します。
開発タブが表示されたら、「XML」グループ内の「ソース」をクリックします。
画面右側にXMLソース作業ウィンドウが開けば準備完了です。
XMLソースは、XML要素とワークシート上のセルを結び付けるための画面です。
Microsoft 365のExcelでも、環境やファイル形式によってXML関連の機能が表示されない場合があります。
XMLスキーマの追加
XMLソース作業ウィンドウで「XMLマップ」をクリックし、「追加」を選択します。
ここではXMLファイルそのもの、またはXML Schema Definition形式のXSDファイルを指定します。
XMLファイルを追加した場合、Excelがファイル構造を基にスキーマを作成するか確認するメッセージを表示することがあります。
内容を確認して追加すると、左側のツリーにルート要素や子要素が表示されます。
ツリー内で繰り返し表示される要素は、一般に表の行へ展開しやすい候補です。
たとえば「Orders」の下に複数の「Order」があり、その中にOrderNoやProductNameがある構造なら、Orderが1行分のレコード候補になります。
要素をセルへドラッグする操作
XMLソースのツリーから、必要な要素をワークシートのセルへドラッグします。
OrderNoをA1、ProductNameをB1、QuantityをC1へドラッグすると、それぞれの要素を列見出しとするXMLテーブルを作成できます。
要素を個別に配置するより、繰り返し要素をシートへドラッグして表を作り、その後に列を調整するほうが操作しやすいケースもあります。
対応付け後の表は、XMLのインポートとエクスポートの両方に利用できる可能性があります。
ただし、Excel側で列の順番を変えたり、マップ外のセルを混在させたりすると、XMLへ書き戻す際に制約が生じることがあります。
【操作のポイント】XMLマップでは、データの繰り返し単位を先に見極めることが重要です。1行として扱う要素を間違えると、列や行の構造が崩れます。
Power QueryによるXMLデータの展開
続いては、階層の深いXMLを表へ変換しやすいPower Queryの操作を確認していきます。
| 注文番号 | 顧客名 | 商品名 | 金額 |
|---|---|---|---|
| A001 | 田中商店 | ノート | 540 |
| A002 | 山本株式会社 | ペン | 600 |
Power Queryは、XMLの入れ子構造を確認しながら列を展開できる機能です。元XMLを更新しても、クエリを更新することで同じ整形手順を再利用できます。
XMLファイルの取得
Excelの「データ」タブを開き、「データの取得」から「ファイルから」を選び、「XMLから」をクリックします。
ファイル選択画面で対象のXMLを指定すると、ナビゲーター画面が表示され、XML内にあるテーブル候補や要素が一覧に出ます。
ここでプレビューを確認し、必要なデータを含む項目を選択します。
ナビゲーターで「データの変換」を選ぶと、読み込み前にPower Queryエディターで内容を整えられます。
読み込み前に変換画面へ進むことで、不要な列や複雑な入れ子構造を整理できます。
テーブル列とレコード列の展開
Power Queryエディターでは、列にTableやRecordと表示されることがあります。
Tableは複数行の表データ、Recordは項目名と値の組み合わせを持つ1件分のデータを示します。
これらの列見出し右側にある展開ボタンをクリックすると、内部に含まれる項目を列として取り出せます。
展開する項目を選ぶ画面では、必要な列だけへチェックを入れます。
元の列名を接頭辞として使用するチェックを外すと、見出しが短くなり、ワークシートで見やすくなることがあります。
TableやRecordを一段ずつ展開することが、XMLを平坦な表へ変換する基本操作です。
XMLの階層が「注文」→「明細」→「商品」のように続く場合は、注文を展開し、次に明細を展開し、最後に商品のRecordを展開します。急いで一度に整えようとせず、各段階でプレビューを確認します。
読み込み先の指定
整形が終わったら、「ホーム」タブの「閉じて読み込む」を選択します。
既存のワークシートへ配置したい場合は、「閉じて読み込む先」を選び、「テーブル」と「既存のワークシート」を指定します。
たとえばA1を指定すれば、A1から見出し付きのExcelテーブルとして取り込まれます。
以後は元のXMLファイルを差し替えた後、「データ」タブの「すべて更新」を実行することで変換結果を更新できます。
毎月届くXML形式の売上データなどでは、この更新機能が特に便利です。
【操作のポイント】Power Queryでは、読み込み後の表を手作業で整える前に、クエリ側で不要列の削除や展開を済ませます。更新時の作業を減らせます。
XML変換後の表データの整形
続いては、変換したXMLデータを集計や検索に使いやすい表へ整える方法を確認していきます。
| 注文番号 | 数量 | 単価 | 金額 |
|---|---|---|---|
| A001 | 3 | 180 | 540 |
| A002 | 5 | 120 | 600 |
空白セルの補完
XMLの親要素にある注文番号や顧客名を展開した場合、子要素の明細行では親要素の値が空白になることがあります。
このような表では、空白セルを上の値で埋めると、各明細行に注文情報を持たせられます。
Power Queryでは対象列を選び、「変換」タブから「フィル」内の「下へ」を選択します。
ワークシート上で補完する場合は、空白セルを選択して数式バーに上のセルを参照する式を入力します。
A2が注文番号でA3が空白の場合、A3に =A2 を入力します。その後、オートフィルで下方向へコピーすると、上の値を引き継ぐ形式で補完できます。
ただし、元データに本来空白である値が含まれる場合もあるため、補完前にデータの意味を確認しましょう。
数値と日付のデータ型
XMLから読み込んだ数量、単価、日付は、見た目が数字でも文字列として扱われることがあります。
文字列のままではSUM関数で合計できなかったり、日付順の並べ替えが正しく動かなかったりします。
Power Queryでは列見出し左側のデータ型アイコンから、「整数」「小数」「日付」など適切な型を選択します。
Excel上で数値に直す場合は、セルの警告マークから「数値に変換」を選ぶ方法があります。
数値と日付の型を整えることは、XML変換後の集計精度に直結します。
金額列を計算する場合、D2に =B2*C2 を入力します。B列を数量、C列を単価としているため、1行目のデータ行では数量と単価の積が金額になります。D2の右下にあるフィルハンドルを下へドラッグすれば、各行の参照先を自動調整して計算できます。
テーブル化と列見出しの整理
変換後の範囲を選択し、「挿入」タブの「テーブル」をクリックすると、フィルターと自動拡張を使えるExcelテーブルになります。
「先頭行をテーブルの見出しとして使用する」にチェックが入っていることを確認します。
XML由来の見出しが長い場合は、内容が伝わる日本語の見出しへ変更します。
たとえば「OrderDetail_ProductName」を「商品名」、「OrderDetail_UnitPrice」を「単価」とすると、集計表やピボットテーブルで扱いやすくなります。
列見出しは、後から作る数式、グラフ、ピボットテーブルの基準になる重要な情報です。
【操作のポイント】表に変換した直後は、空白、データ型、見出しの3点を確認します。この段階で整えておくと、集計時のエラーを防ぎやすくなります。
XMLの読み込みエラーと対処
続いては、XMLファイルをExcelへ変換できないときに確認したい原因と対処を解説していきます。
| 症状 | 主な原因 | 確認方法 |
|---|---|---|
| 読み込みエラー | XML構文の不整合 | タグと文字コード |
| 列が増えすぎる | 複雑な階層 | Power Queryの展開順 |
XMLの構文エラー
XMLはタグの対応関係が厳密な形式です。
開始タグは必ず終了タグで閉じる必要があり、タグの入れ子順も正しくなければなりません。
たとえばOrderの中にItemを置いた場合、Itemを閉じてからOrderを閉じます。
終了タグの名前が異なる、アンパサンドなどの予約文字が適切に変換されていない、といった状態ではExcelが読み込めないことがあります。
XMLをメモ帳やコードエディターで開き、エラー行が示されている場合はその周辺を確認します。
Excelのエラーは、XML側のデータ不備を知らせている場合があります。
名前空間とスキーマの違い
XMLには名前空間が設定されていることがあります。
名前空間は、同じ名前の要素を区別するための仕組みで、XMLの先頭付近にxmlnsとして記載されます。
この設定があるXMLでは、単純なXMLマップで想定通りに要素が表示されないことがあります。
また、XML Schema Definition形式のXSDが別途用意されている場合は、XMLファイルではなくXSDをXMLマップへ追加したほうが、項目の定義を正確に扱えることがあります。
取引先や外部システムからXMLとXSDが一緒に提供されているなら、両方を保管しておくとよいでしょう。
大容量XMLへの対応
レコード数が多いXMLは、直接開くとExcelの動作が遅くなることがあります。
ワークシートには行数の上限があり、大量の明細をすべて展開できないケースもあります。
この場合はPower Queryで必要な列だけを選び、不要な階層や空白列を早い段階で削除します。
さらに、日付で対象期間を絞る、データベースへ取り込む、複数ファイルに分割するといった対応も有効です。
大量データを扱うときは、まずPower Queryで列の選択と行のフィルターを行い、Excelシートへ読み込む量を抑えます。元データ全体を必要としない集計では、必要な範囲だけを取り込む設計が重要です。
【操作のポイント】エラー時はExcelの設定だけでなく、XMLの構文、名前空間、スキーマ、ファイル容量を順番に確認します。
XML変換後の集計と更新
続いては、表形式へ変換したXMLデータを集計し、次回以降の取り込みへ活用する方法を確認していきます。
| 商品名 | 数量 | 単価 | 金額 |
|---|---|---|---|
| ノート | 3 | 180 | 540 |
| ペン | 5 | 120 | 600 |
数式による金額計算
XMLに数量と単価だけが含まれている場合、Excel側で金額列を追加すると集計しやすくなります。
数量がB列、単価がC列、金額がD列であり、1行目が見出しの場合、D2には次の数式を入力します。
=B2*C2
この式は、B2の数量とC2の単価を掛け算し、D2に金額を表示します。
Excelテーブルにしている場合は、数式を1行入力するだけで同じ列の下方へ自動入力されることがあります。
通常範囲なら、D2を選択してフィルハンドルを下へドラッグし、オートフィルで数式をコピーします。
数式の参照先は行ごとに自動で変化するため、複数の明細をまとめて計算できます。
ピボットテーブルによる集計
XMLから変換した表を選択し、「挿入」タブの「ピボットテーブル」を選ぶと、商品別や月別の集計を作成できます。
フィールド一覧で商品名を行、金額を値のエリアへドラッグすれば、商品ごとの売上金額を集計できます。
注文日を行やフィルターへ配置すれば、期間別の確認も可能です。
元のXMLが定期的に更新される場合でも、Power Queryの更新後にピボットテーブルを更新すれば、最新のデータに基づく集計へ切り替えられます。
XMLを表へ変換する目的は、単に表示することではなく、検索、計算、集計へ利用することにあります。
更新できるファイルの管理
Power QueryでXMLを取り込んだブックでは、元ファイルの保存場所を変えると更新エラーになることがあります。
毎月同じ形式のXMLを使う場合は、専用フォルダを作り、同じファイル名で差し替える運用にすると管理しやすくなります。
クエリと接続の画面からデータソースの設定を確認すれば、参照先の変更もできます。
変換用ブックには、元XMLの保存先、更新手順、列の意味をシート内に記録しておくと、担当者が変わっても作業を引き継ぎやすくなります。
【操作のポイント】定期的にXMLを受け取る業務では、Power Queryの変換手順を保存し、更新操作だけで最新表を作れる状態にしておくと効率的です。
まとめ エクセルでXMLを表形式へ変換する手順
ExcelでXMLを変換するには、単純な一覧形式ならXMLファイルを直接開き、複雑な階層を持つデータならPower Queryを使う方法が便利です。
XMLマップでは、XMLの要素をExcelのセルや列へ対応付けられます。
対応付けは、XMLの構造を理解したうえで必要な項目だけを表にするための機能です。
Power QueryではTableやRecordを段階的に展開し、不要な列を削除し、数値や日付のデータ型を整えることで、集計しやすい表を作れます。
変換後は、空白セルの補完、見出しの整理、Excelテーブル化、数式による金額計算を行うと、検索やピボットテーブルで活用しやすくなります。
XMLの読み込みでエラーが出た場合は、タグの対応、文字コード、名前空間、スキーマ、ファイル容量を確認しましょう。
毎月や毎週のXMLデータを扱う場合は、Power Queryのクエリを保存し、「すべて更新」で同じ変換処理を繰り返せるようにする方法がおすすめです。
