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