はじめに
- 「コピペ の手間や、コピペ元を探すムダな時間を減らしたい 」
- 「どれが最新データか わからない ！ 一元管理 して最新情報をすぐに利用したい」
- 「既存の方法を活かしつつ、 業務改善 や DX化 を進めたい」
毎日の仕事で、こんな「ムダな作業」に疲れていませんか？
実は、Excelの 標準機能 にある「 テーブル 」と「 Power Query 」の2つを活用するだけで、
なぜなら、
- テーブル （データを置く場所） に格納した情報を、
- Power Query （データの取得・加工を担う機能） を使って、
外部の別ファイル（ブック）から必要な形で自由に活用できるようになるからです。
職場で散乱するファイルから必要なデータを手作業で漁り、
最新情報か確認しながら、コピペを使った手作業でデータをまとめるという手間のかかる作業を経験していました。
「もっと早くこの方法を知っていたら、どれだけの時短になっていたか」
「会社の 問題を解決し、貢献できた のでは」
と思います。
この記事では、
- データを探し、コピペをする手間を減らし、 自動化 できる
- 情報を 最新に保ち 、再利用しやすくできる
- 最小限のコスト で、データ活用（プチDX化）を進められる
このように、 テーブルとPower Queryを中心としたExcelの機能の使い方 を身につけるだけで、Excelを使ったデータベース運用が実現できます。
業務効率化・時短の方法にはいくつかありますが、
Excelでデータベース運用とは？
ここでの「データベース運用」とは、「単なるデータのテーブル化」ではなく、
データベースとは？Excelでの位置づけ
データベース とは、決められた規則によって整理されたデータの集合のことです。
ここでは、Excelの「 単一テーブル 」または「 複数テーブルの集まり 」のことをデータベースと呼ぶこととします。
つまり
- 「複数のテーブルを持つ 1つのブック 」
- 「1つのテーブルを持つ ブックの集まり 」
のどちらもデータベースとして扱うということです。
データベースの概念上、「単一テーブル = データベース」として扱われることは少ないです。 ただし、ごく小規模なデータベースは単体のテーブルのみで構成されるケースがあるため、 ここでは 「Excelの単一テーブル」もデータベース として扱います。
Excelでデータベース運用のメリット
Excelでデータベース運用をすることには、以下の3つの大きなメリットがあります。
- データを探し、コピペする手間を減らし、 自動化 できる
- 情報を 最新に維持 し、再利用しやすくできる
- コストを最小限 にして、データ活用（プチDX化）を進められる
1. データを探し、コピペする手間を減らし、自動化できる
手作業によるデータの検索やコピペがなくなり、 自動化 できます。
手作業でのコピペが減るため、データの正確性も向上します。
「貼り付け間違い」や「行ズレ」といった ヒューマンエラー を防げます。
古い情報を使う確率も激減します。
2. 情報を最新に維持し、再利用しやすくできる
データはデータベースに集約されるため、 データが一元管理 され情報が分散しません。
常に最新に保たれたマスタデータになるため、 参照元の場所が明確 になり、再利用性が高まります。
「コピペしたデータをさらにコピペした結果、間違っていた」
というようなミスがなくなります。
3. コストを最小限にして、データ活用（プチDX化）を進められる
新しいシステムの申請やインストールは不要です。
これまでにExcelで作成してきた 既存の資産も活かせる ため、
コストを最小限に抑えてデータ活用（プチDX化）を進めることができます。
実践するにはPower Queryが必要
Excelでデータベース運用を行うためには、
Power Queryとは？
Power Queryは「 データの取得・加工・配送 」を行える、Excelの標準機能です。
基本的にはGUI操作（マウス操作）のみで行えますが、
以下のメリットがあります。
- コードなしで基本的な操作ができ、覚える量は少なく使える。
- コードも使えるため、細かくカスタマイズできる。
生成AIとも相性が良く、コード生成や解析を任せられる。 - 段階的な処理内容が 視覚的に確認 できる。
属人化・ブラックボックス化しにくい 。
学習コストは低いですが、活用の幅は広く、コストパフォーマンスが高いのでオススメのツールです。 本記事では、このPower Queryをメインに使っていきます。
業務効率化に使える他の方法・機能について知りたい方は、 こちらのまとめを参考にしてください。
「元に戻す（Undo）」は使えない
Power Query上でマウス操作によって追加した処理を取り消すときに、
「元に戻す」（Ctrl + z）は基本的に使えません
（ただし、数式バーにMコードを直接入力している場面では、入力中のテキストに対して使用できます）。
追加した処理を取り消す場合は、
右側の適用したステップ欄の中の追加されたステップ（だいたい一番下）を削除します。
Excelでデータベース運用を実現する5つの操作
データベースを構築し、活用するための 5つの操作（基本2つ + 応用3つ） を解説します。
-
基本の2操作
最低限のデータベース運用を可能にする - (操作I) テーブルの作成
- (操作II) テーブルの参照
-
応用の3操作
データの連携・加工の幅を広げる - (操作III) テーブルへのルール付け：
入力値の制限、候補の表示、入力ミスのハイライト - (操作IV) テーブルの結合（横）：
テーブルに列を追加（関連データの自動挿入、必要データの抽出） - (操作V) テーブルの結合（縦）：
テーブルに行を追加（一覧の作成、膨大なデータの使用）
- (操作III) テーブルへのルール付け：
操作I（基本）：テーブルの作成
データはテーブルに格納することで、
操作手順は次の通りです。
1行目に見出し、2行目以降にデータを入力します。
- データ範囲のセルを選択
- 挿入タブのテーブルボタンを選択（Ctrl + T）
- テーブルの作成ウィンドウでOKを選択。
任意ですが、後でわかりやすくなるので推奨です。
テーブルを選択 > テーブルデザインタブのプロパティエリアのテーブル名:欄に記入。
テーブルに格納するデータにはいくつかの要件がありますが、
-
テーブル化の要件：
1行目をタイトル（ヘッダー）とし、データが途切れなく縦に並んでいる必要がある。 -
テーブルのメリット・便利な機能：
- 構造化参照 ：
数式に「セルB2」ではなく売上金額といった「項目名」で参照できるようになり、
セル移動や変更に強くなる。
「項目名」は「行データ全体」を指し、 参照範囲が自動拡張 するので、
参照先の数式を変更することなくデータを追加できる。 - フィルタ・並び替え ：
ヘッダーに自動でフィルタボタンが付き、操作が簡単になる。
- 構造化参照 ：
操作II（基本）：テーブルの参照
テーブルに格納したデータは、 Power Query を使うことで、
- データタブのデータの取得プルダウン > ファイルから > Excelブックからを選択
- 参照するブックを選択
ナビゲーターウィンドウで参照するテーブルを選択 > 読み込み先ボタン横のプルダウン > 読み込み先...を選択
データのインポートウィンドウで下記を選択 > OKボタン
- テーブルを選択
- 既存のワークシートを選択して、テーブルを配置するセル位置を選択
- （新規シートに配置する場合は新規ワークシートを選択）
- このデータをデータモデルに追加するは未チェック
- （Power Pivot/リレーションシップを使用する場合はチェックを入れる）
データの更新方法
Power Queryで参照したデータは、参照元のデータに変更があっても「更新」操作（下記のどちらか）をしないと参照先のデータは古いままです。
- データタブの すべて更新ボタンを押す (Ctrl + Alt + F5)
- テーブルを右クリック > 更新を選択
「ブックを開いたときに更新」「一定の時間間隔で更新」の設定も可能です。
注意点として、参照先ブックのパスは固定しておく必要があります。 参照先ブックを移動したりファイル名を変えるとリンクが切れ、 「更新」操作ができなくなります。
Power Query以外にもデータの参照方法がありますが、
- 別ブックの参照 → Power Queryを使う
- ✅ 参照先が別ブックでも 安定 に動作
- ❌ 同期に1クリック必要（自動同期は設定可能）
- 同ブックの参照 → 直接参照（セル参照、構造化参照、名前参照）を使う
- ✅ リアルタイム 同期
- ❌ 参照先が別ブックの場合は 不安定
- 「入力規則」「条件付き書式」などで構造化参照を使う → INDIRECT関数を使う
- ✅ リアルタイム 同期
- ❌ 参照先が別ブックの場合は 不安定
- ❌ 多用するとパフォーマンスが 低下
操作III（応用）：テーブルへのルール付け
テーブルへの ルール付け は、主にデータ入力時において、
活用できる場面には主に2つ あり、データベースの品質向上と利用の幅拡大に重要です。
- データベース への データ入力時
- データベース を使った データ入力時
テーブルにルール付けを行うことで、主に以下のメリットが得られます。
- データ入力の効率化
- 入力値を制限したり、プルダウンで候補を表示させたりできます。
- データ整合性の向上とミスの防止
- 入力ミスや 表記ゆれ を防ぎ、データ間の 整合性 を高めます。
- 意図しない入力値を ハイライト表示 する機能と合わせることで、データ入力の質がさらに向上します。
実装に使用するExcel機能
- 入力規則 ：
- 入力値の制限、プルダウンでの候補表示、「記入例」などの説明書き表示ができる。
- ただし、コピペを使った入力時の警告が分かりにくいという欠点がある。
- 条件付き書式 ：
- セルの値に応じて色やアイコン表示を設定できる。
- コピペを使った入力時や、複雑なルール設定時に対応しやすい/対応できる。
基本的には「入力規則」を使用し、 対応しきれない部分があれば「条件付き書式」を併用するのが効果的です。
入力規則の設定手順
「リスト内のどれか」のみに入力制限する場合の設定方法を説明します。
「整数のみ」「日付のみ」のような設定も可能ですが、 「リスト」以外では、 入力候補をプルダウン表示できないので注意が必要です。
入力値の候補リストとなるテーブルを用意する
-
【「データベース への データ入力」にルール付けする場合】
ルールに利用できるデータベースがあれば 、それを使用する。 なければ新規にテーブルを作成する（わかりやすくテーブル名をつけることを推奨）。 -
【「データベース を使った データ入力」にルール付けする場合】
ルールとして利用するデータベースを、データ入力欄と同一ブック上に配置する （操作I：テーブルの作成を参照）。
- ルール設定したいセルを範囲選択する。
- 【「データベース への データ入力」にルール付けする場合】
データベース上の列を選択する。 - 【「データベース を使った データ入力」にルール付けする場合】
データ入力欄を選択する。
- 【「データベース への データ入力」にルール付けする場合】
- データタブのデータの入力規則ボタンを押す。
- 条件タブの入力値の種類で「リスト」を選択。
- 空白を無視するを未チェック（空白を許可しない場合）
- ドロップダウンリストから選択するをチェック
- 元の値に=INDIRECT("テーブル名[見出し名]")を記入
- ※ テーブル名,見出し名は、Step1で用意したもの（参照したいリスト）に置き換える
- 入力時メッセージタブのセルを選択したときに入力時メッセージを表示するにチェックを入れる
- タイトルと入力時メッセージに「入力すべきもの」や「入力例」を記入。
OKボタンを押す。
応用することで、複合表示プルダウン、連動プルダウンなども作成できます。
「入力規則」では、コピペを使った入力を厳密に制限できません。 警告は出ますが分かりにくいので、 より強調させたい場合には、「条件付き書式」を併用します。
条件付き書式の設定手順
「リスト内のどれか」以外のデータがあるときに、
次の時に有効です。
- コピペを使ったデータ入力時の警告を
分かりやすくしたいとき - 「2つのセルのどちらか一方のみ記入」のような
複雑なルール設定にも対応しやすくしたいとき
- ルール設定するセルを範囲選択する。
- 【「データベース への データ入力」にルール付けする場合】
データベース上の列を選択する。 - 【「データベース を使った データ入力」にルール付けする場合】
データ入力欄を選択する。
- 【「データベース への データ入力」にルール付けする場合】
- ホームタブの条件付き書式プルダウン > 新しいルールを選択。
- ※ 修正する場合はルールの管理を選択 > 修正するルールを選択 > ルールの編集ボタン
- ルールの種類を選択してください欄で数式を使用して、書式設定するセルを決定を選択。
- 次の数式を満たす場合に値を書式設定欄で
=COUNTIF(INDIRECT("テーブル名[見出し名]"), 選択セル)=0を入力。 - ※ テーブル名,見出し名： Step1で用意したものに置き換える
- ※ 選択セル： ルール範囲するセル範囲の先頭セル（相対参照）
- 書式ボタンを選択。
- 塗りつぶしタブの背景色欄で赤色を選択（色はお好みで）。
- （任意） 表示形式タブの分類欄でユーザー定義を選択
種類欄に"❌ "G/標準;"❌ "G/標準;"❌ "G/標準;"❌ "G/標準※ を入力。
- アイコン表示が不要な場合は、この表示形式設定は不要
- OKボタンを押す。
OKボタンを押す。
※ 表示形式の設定「"❌ "G/標準」について
- 4つ連続で記入しているのは、それぞれ「正の数値のとき」「負の数値のとき」「0のとき」「文字列のとき」の表示形式を設定しており、文字列形式のときにもアイコンが付くようにするためです。
- 「❌」アイコンを記入するにはWin + .キーを押し、「バツ」で検索して選べば挿入できます。
操作IV（応用）：テーブルの結合（横）
関連データを挿入したり、テーブル内から必要なデータを抽出したりするときに、
複数テーブルのフィールド（列）間の関連付けができます。 いわゆる「リレーショナルデータベース」のようなことができるようになります。 データ構造ごとにテーブルを分割して管理できるようになり、設計次第で保守・メンテナンス性を向上させられます （例：更新頻度の異なるデータごとに分けてテーブルを管理する、など）。
参照 元 テーブル（列を増やしたいテーブル）に、
-
データタブのデータの取得プルダウン > ファイルから > Excelブックからを選択
-
参照するブックを選択
-
ナビゲーターウィンドウで参照するテーブルを選択 > 読み込み先ボタン横のプルダウン > 読み込み先...を選択
-
データのインポートウィンドウで下記を選択 > OKボタン
- 接続の作成のみを選択
- このデータをデータモデルに追加するは未チェック
- （Power Pivot/リレーションシップを使用する場合はチェックを入れる）
結合する参照元テーブルのセルを選択 > データタブの テーブルまたは範囲からを選択
- Power Queryエディター上のクエリ欄で参照 元 テーブルを選択状態にする。
- ホームタブの クエリのマージを選択 。
- マージウィンドウで下記を設定し、OKを選択。
- 中央付近のプルダウン： 参照 先 のテーブルを選択。
- 上下のテーブル： 各テーブルで、関連付ける列を選択。
- 結合の種類： 左外部（最初の行すべて、および2番目の行のうち一致するもの）を選択。
- あいまい一致を使用してマージを実行する：未チェック
結合した参照 先 テーブルは、1つの列に複数列がまとめられているので、
- 追加された参照 先 テーブルの列の見出し内のボタンを押す。
- 下記の設定をする > OKボタン
- 「展開」/「集計」選択欄： 「展開」を選択。
- 列の選択欄： 取得したい列を選択。
- 元の列名をプレフィックスとして使用します： 未チェック。
- （参照先と参照元のテーブル間で、見出しの重複が発生する場合はチェックを入れる）
最終的に不要な列を削除します。
- 不要な列の見出しをクリックする
（Shif + クリックで範囲選択、Ctrl + クリックで複数選択可能）。 - ホームタブの列の削除ボタンを押す。
- ファイルタブの 閉じて次に読み込む...を選択
- データのインポートウィンドウで下記を選択 > OKボタン
- テーブルを選択
- 既存のワークシートを選択して、テーブルを配置するセル位置を選択
- （新規シートに配置する場合は新規ワークシートを選択）
- このデータをデータモデルに追加するは未チェック
- （Power Pivot/リレーションシップを使用する場合はチェックを入れる）
同様の処理を行う方法にはいくつかありますが、
- 関数（VLOOKUP, XLOOKUP, INDEX+MATCH） ：
- ✅ リアルタイム 同期
- ❌ 参照先が別ブックの場合は 不安定
- Power Query（マージ、フィルタ） ：
- ✅ 参照先が別ブックでも 安定 に動作
- ❌ 3個以上のテーブル間の結合は少し 手間
- ❌ 同期に1クリック必要（自動同期は設定可能）
- Power Pivot（リレーションシップ） ：
- ✅ 参照先が別ブックでも 安定 に動作（Power Queryとの併用）
- ✅ 設定が視覚的に分かりやすい。3個以上のテーブル間の結合も 容易
- ❌ 同期に1クリック必要（自動同期は設定可能）
操作V（応用）：テーブルの結合（縦）
同じ形式の複数テーブルを1つにまとめるときは、テーブルを縦方向に結合します。
バラバラのデータからリスト（一覧）を作成したり、 膨大なデータを扱うために一度分割したファイルを再結合したりするときに活用できます。
同じ形式（同じテーブル名、列名）のテーブルを持つ複数のブックから、
- データタブのデータの取得プルダウン > ファイルから > フォルダーからを選択
- 複数ブックの入ったフォルダを選択
-
結合横のプルダウン > 結合および読み込み先...を選択
-
ファイルの結合ウィンドウで下記を設定 > OKボタン
- 左側の欄：参照するテーブルを選択
- エラーのあるファイルをスキップする：チェック
フォルダ内のファイルの名前に注意
フォルダには、最低1つはファイル名が半角英数字から始まるファイルを配置しましょう。
以下の両方に当てはまる場合、結合および読み込み先...を選択しても次に進まない可能性があります。
- フォルダ内のファイルを開いている
- フォルダ内のすべてのファイルの名前が日本語で始まっている
ここのステップで問題がなかったとしても、後者に当てはまる場合は、
「フォルダ内のファイルを開いているときに、テーブル更新ができない」
という不具合が生じる可能性があります。
データのインポートウィンドウで下記を選択 > OKボタン
- テーブルを選択
- 既存のワークシートを選択して、テーブルを配置するセル位置を選択
- （新規シートに配置する場合は新規ワークシートを選択）
- このデータをデータモデルに追加するは未チェック
- （Power Pivot/リレーションシップを使用する場合はチェックを入れる）
Power Query以外で、同様の処理を行う方法は限定的です。
- 関数（VSTACK, HSTACK） ：
- ✅ リアルタイム 同期
- ❌ 参照先が別ブックの場合は 不安定
- ❌ 古いExcelでは使用できない（2024以上 / Microsoft 365のみ対応）
- Power Query（結合） ：
- ✅ 参照先が別ブックでも 安定 に動作
- ✅ 複数ファイルの一括取得が可能
- ❌ 同期に1クリック必要（自動同期は設定可能）
もし、複数人で同時にデータ入力・編集する必要がある場合は、 Microsoft Accessなどのデータベース専用ソフトにデータを格納し、 Excelで参照・集計するという方法を検討できます。 Accessを利用すれば、同時編集が可能になります。
Excelでデータベース運用のデメリット・課題
Excelは便利ですが、データベース専用ソフトではないため、苦手なこともあります。
Excelのみでデータベース運用をした場合のデメリットは以下の通りです。
- 【Accessで解決 可能 な項目】
- 同時 編集 ができない（同時 参照 はExcelのみでもできる）
- 厳格なデータ規則・整合性の管理や複雑なデータ構造の取り扱いができない
- 【Accessで解決 不可能 な項目】
- リアルタイム同期が難しい
- 大量データの取り扱いや効率的管理ができない
（ExcelはPower Queryで大容量を扱えるが、PCスペックに依存。
Accessは1ファイル2GB上限あり） - セキュリティ対策が頑強でない（ファイルのコピー・閲覧ができてしまう）
他のソフト・ツールの検討ステップ - まずはAccessから
Excelのみのデータベース運用に課題が生じたときは、
検討する手順は以下の通りです。
課題がAccessで解決可能なものがメインの場合、 データの保管先をAccessに置き換えることを検討する。
❌ デメリット：
- Accessの学習コスト、システム構築・導入コストが発生する。
- 属人化・ブラックボックス化の対策が必要。
「大量データの取り扱いや効率的管理」「セキュリティ対策」が必要な場合、 データの保管先をSQL Serverなどの本格的なデータベースに置き換えることを検討する。
❌ デメリット：
- 使用するデータベースマネジメントシステム（場合によっては＋サーバー管理方法）の学習コストやシステム構築・導入コストが発生する。
- 属人化・ブラックボックス化の対策が必要。
-
【パターン1：「リアルタイム同期」が必要な場合】
Googleスプレッドシートのような、リアルタイム同時編集可能な専用ツールへの移行を検討する。
❌ デメリット：
- データベースとの連携機能が標準搭載でない場合は、システム構築コストが発生し、属人化・ブラックボックス化の対策も必要。
-
【パターン2：「大量データの取り扱いや効率的管理」「信頼性・セキュリティ対策」が必要な場合】
データベースを持った専用のデータ管理ツールへの移行を検討する。 Excelは、一部での使用にとどめる。
❌ デメリット：
- 導入コスト、教育・運用コストが必要。
- カスタマイズ性が低い。
- コストパフォーマンスが低い可能性がある。
自社でシステム構築をする限り、構築に時間がかかり、信頼性を担保しづらい面はあります。
「とにかく手間をかけずに、Excelによるデータベース運用の課題を解決したい」のであれば、
Step3の「全面的な他のツールへの移行」が有効になるでしょう。
できる限りExcelをベースに運用し、データの保管場所だけをExcel以外に変更できれば、 既存の作業用ブックの操作感や集計ロジックをそのまま活かしつつ、Excelのデメリットを解消できます。
Excelでデータ活用の加速ができる
テーブルとPower Queryを中心とした5つの操作 が使えるようになると、
- 基本の2操作 → 最低限のデータベース運用が可能になる（転記・コピペの自動化も）。
- 応用の3操作 → データベースの活用の幅が広がり、データ管理がより楽になる。
ただし、Excelのみの使用で以下は難しいです。
- データの同時編集、リアルタイム同期
- 厳格なデータ規則・整合性の管理、複雑なデータ構造の取り扱い
- 大量データの効率的管理
その場合は、他のツールとの併用・完全乗り換えを検討する必要があるでしょう。