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