Excelのマスタ管理とは？作成・別シート反映まで解説【データ一元管理】

これらの問題は、マスタ管理をすることで解決できます。マスタ管理とは、商品名・単価・取引先名などのデータを1か所にまとめて管理する手法です。データをマスタとして一元化して管理し、これを参照・利用することで、手入力による表記揺れや修正漏れ を防げます。

マスタデータ管理 とは、商品・取引先・社員などの データをマスタテーブルとして1か所に格納 し、ビジネスプロセス全般で 再利用 する手法です。

マスタテーブルはデータベースの構成要素の1つで、「信頼できる唯一の情報源（Single Source of Truth / SSOT）」、つまりマスタデータとして機能します。マスタデータを基準にすることで、システム間やシート間におけるデータの重複・齟齬・散逸を排除できます。

マスタ管理を行わず、すべてのデータを手入力で運用した場合、業務において以下のような重大なリスクが発生します。

• 表記揺れの発生 ：同一商品や同一取引先に対して「株式会社」「（株）」やスペースの有無など 異なる文字列が入力される可能性 がある。その場合、ピボットテーブル等の集計やフィルター抽出が正しく動作しなくなる。
• データの不整合 ：同一の管理番号に対して異なる商品名や単価が手入力され、データベースの整合性が壊れる 。
• 変更時の一括更新不可 ：単価や名称が変更された際、関連するすべてのシートのデータを個別に修正する必要が生じる 。修正漏れの原因になる。

Excelにおけるマスタ管理の構成・データフローは、マスタデータの保管場所（ブック内管理/ブック外管理）によって2つのパターンに分かれます。マスタデータの保管場所がブック外の場合には、Power Queryを使ってブック内にコピーを配置すること（外部参照）が必要になります。マスターデータの格納、Power Queryによる外部参照では、テーブル機能を使用します。

以降のセクションでは、上記画像のA → B → Cの順に、それぞれの手順を解説します。

マスタテーブルを作成するときに重要となる、データベースとして正しく機能させるための設計原則をまとめておきます。

Excelの表をマスタテーブル（データベースのテーブル）として使うには、以下の配置ルールを守る必要があります。

• 1行1レコード ：1つのエンティティ（1件の商品、1社の取引先）に関するすべての情報を1行に集約する。セルの結合は禁止。
• 1列1項目 ：1列には単一のデータ属性（「単価」列には数値のみ、「登録日」列には日付型のみ）を配置し、文字列と数値の混在を排除する。
• 見出しの単一化 ：シートの1行目のみを見出し（フィールド名）とし、2行目以降にデータレコードを記入する。

参考：» セル結合は禁止！総務省に則した作成ルールとやめてほしいエクセル表

主キー（プライマリキー） とは、商品コード・顧客IDなど、各行を 一意に識別するための値 です。テーブル間でのデータの紐づけや、行の並び替え後に順序をリセットする際に使用します。

主キーの設定には以下の要件を満たす、または意識する必要があります。

• 重複禁止 ：重複する値が存在しない文字列または数値を割り当てる。
• 固定値を記入 ：行の並び替え・フィルタリング等で値が変わらないように、関数の使用は非推奨。
• 左端の列に配置 ：可能な限りテーブルの一番左の列（第1列）に配置する。VLOOKUP関数は検索範囲の左端列を検索する仕様のため。

マスタテーブルの例としては、以下のような商品マスタが挙げられます。

【テーブル名：M_商品】

以降の作成手順では、この商品マスタテーブルを例として使用します。

実際にExcelでマスタテーブルを作成する具体的な手順を解説します。

テーブル化すると、構造化参照が使えるようになります。構造化参照では、紐づけがシート名やシートのレイアウトに依存しなくなるため、リンク切れしにくい点がメリットです。

一方で、 テーブル名・列名で紐づけが行われる ため、これらを 変更する場合はリンク切れに注意 してください。特に外部参照のリンクは、気づかずに切れやすいので要注意です。

複数のブックで同じマスタデータを参照する場合は、マスタデータを別のブックに切り出して管理し、参照する構成が有効です。参照するときには、Power Queryを使って外部ブックからマスタデータを取得します。

マスタデータを格納している外部ブックのファイル名は固定しておく必要があります。

ファイルのパス（保存場所）については、下記の2つの運用方法があります。

• 絶対パスでの運用 ：ドライブ名やサーバーのフルパス（例：C:\MasterData\ProductMaster.xlsx）で接続経路を固定する。ブックの保管場所が恒久的に変更されない環境に適している。
• 相対パスでの運用 ：現在のブックから見た相対的な位置関係（例：同一フォルダー内など）を維持して接続する。フォルダー構成や配置変更を行わないことが前提。ブックを別環境へ移動させる場合は、マスタブックと利用先ブックをフォルダーごと一括で移動させる。
  • ただし、Power Queryでの設定に工夫が必要になる

Power Queryを使って、外部マスタを利用先ブックに取り込み、コピーを配置する手順の説明をします。利用先ブック内から以下の操作を行います。

1. [データ] タブの [データの取得] > [ファイルから] > [Excelブックから] を選択し、外部のマスタブックを指定する

   

2. ダイアログでマスタテーブルを選択し、[データの変換] をクリックする

   

3. （任意）Power Queryエディター上で、不要な列や行を削除する

   • データ数が多い場合、データを絞り込むことでパフォーマンス向上につながる

4. [ファイル] タブの [閉じて次に読み込む…] を選択する

   

5. 表示形式で [テーブル] を選択し、利用先ブック内にテーブルを配置する

   

数式・関数とは異なり、Power Queryで取得したデータはリアルタイム更新されません。マスタデータに変更があった場合、手動で「更新」操作を行う必要があります。

更新操作 ：[データ] タブの [すべて更新]（ショートカット：Ctrl + Alt + F5）

更新漏れを防ぎたい場合やブックのデータ容量を削減したい場合は、以下のようなクエリ設定が有効です。

設定画面の表示方法

1. [データ] タブの [クエリと接続] ボタンでクエリ一覧を表示する

2. 外部のマスタを取得するクエリを右クリックし [プロパティ] を選択する

   

設定内容

• [ファイルを開くときにデータを更新する]：ON

  • ブック起動時にデータの更新を自動で行う。更新漏れを防止できる。

• [ブックを保存する前に外部データ範囲からデータを削除する]：ON

  • ブック保存時に、Power Queryで読み込んだデータをブック内に保持しない。ブックのファイル容量を最小限に抑えられる。
  • ※ [ファイルを開くときにデータを更新する] のONが必須。
  

  吉峰

  他のユーザーにブック単体を配布/送付し、外部マスタにアクセスできない環境でデータを表示させる必要がある場合は、データ削除の設定はOFFにしてください。

ブック内にあるマスタテーブル（またはPower Queryで取得したコピー）を参照して、以下のようなExcel機能で利用できます。

XLOOKUP 関数や VLOOKUP 関数の「範囲」引数としてマスタテーブルを指定すると、入力された識別キー（商品コード等）を基に、対応する「商品名」や「単価」を自動で記入できます。

例えば、商品名のセルにXLOOKUP関数を入力し、

• 検索範囲：マスタテーブルの「商品コード」列
• 返される範囲：マスタテーブルの「商品名」列

を指定すれば、商品コードを入力するだけで商品名が自動表示されます。

マスタテーブルの列をプルダウンリスト（ドロップダウンリスト）の選択肢として利用できます。=INDIRECT("商品テーブル[商品コード]") などの数式を元の値に指定することで、実装可能です。

詳細：» Excelドロップダウンリストの編集・別シート・選択方法【入力規則】

マスタテーブルと照合し、マスタに存在しない項目のセルをハイライト表示できます。入力ミスを視覚的に発見しやすくするのに有効です。

詳細：» Excel条件付き書式｜特定の文字・条件の自動色付け【塗りつぶし・ルール設定解説】

本記事の要点は以下の3点です。

Excelをデータベースとして機能させるには、マスタテーブルとトランザクションテーブルを分離する設計が必要です。Excelデータベースの作り方・注意点については、以下の記事にまとめています。

» Excelでデータベースの作成方法｜データ管理の基本ルールから限界まで解説