【Excel】エクセルでデータベースを作る方法は？表の作成から活用まで解説（SQL・テンプレート・データベース関数）

Excelは身近な表計算ソフトですが、入力規則と列の役割を整えることで、顧客情報、商品台帳、売上履歴などを管理する小規模なデータベースとして活用できます。

単に表を並べるだけでは検索や集計で困りやすいため、1行を1件のデータ、1列を1種類の項目として扱う考え方が重要です。

この記事では、Excelでデータベース用の表を作成し、データを入力、抽出、集計し、必要に応じてSQL的な考え方にもつなげる方法を解説します。

エクセルでデータベースを作る方法【テーブル化から開始】

それではまず、Excelでデータベースを作るための基本操作について解説していきます。

データベースに向く表の形

Excelでデータベースを作る場合、最初に決めたいのは表の単位です。

顧客台帳なら1行を1社、商品台帳なら1行を1商品、注文履歴なら1行を1回の注文として扱います。

1つの行に複数件の情報を書き込まないことが、後から並べ替えや抽出を正確に行うための条件です。

たとえば顧客名のセルに、青木商店と木村企画をまとめて入力すると、それぞれの顧客として検索できません。

同様に、住所、電話番号、担当者名を1セルに詰め込むと、必要な項目だけを集計しにくくなります。

列には顧客ID、顧客名、地域、登録日、購入額のように、意味が明確な見出しを付けましょう。

1行目にヘッダーを置くと、Excelのテーブル機能やデータベース関数が項目名を識別しやすくなります。

表をテーブルへ変換する操作

入力した範囲を選択し、挿入タブのテーブル、またはホームタブのテーブルとして書式設定を選択します。

表示されたダイアログで、先頭行をテーブルの見出しとして使用する項目にチェックが入っていることを確認します。

ここでOKを押すと、通常のセル範囲がExcelテーブルへ変わります。

テーブル化すると、見出しにフィルターボタンが付き、最終行の下へ入力したデータも範囲に自動追加されます。

数式が列単位で自動コピーされるため、計算列を含むデータベースでは特に効果的です。

テーブル名はテーブルデザインタブから変更できます。

顧客情報なら顧客台帳、売上情報なら売上履歴のように、内容が分かる名前にすると数式やPower Queryで参照しやすくなります。

テンプレートとして保存する工夫

毎月同じ形式の台帳を作るなら、見出し、入力規則、計算式、テーブル設定を整えたブックをテンプレートとして保存します。

ファイルタブから名前を付けて保存を選び、ファイルの種類をExcelテンプレートにすると、新しいファイルを作るたびに元のひな形を保てます。

テンプレートには説明用のサンプル行を残すより、実データを削除した状態で保存するほうが誤入力を防げます。

入力する人が複数いる場合は、入力欄の色、必須項目、日付形式を統一しておきましょう。

見出しの名称を途中で変えないことも、長く使うデータベースでは大切です。

データ入力ルールと正規化の考え方

続いては、データベースの品質を左右する入力ルールと正規化の考え方を確認していきます。

空白行と結合セルを使わない理由

データベース範囲の途中に空白行があると、Excelが表の終わりと判断する場合があります。

フィルター、並べ替え、ピボットテーブルの対象範囲が分断される原因になるため、レコードの間に空白行を挟まないようにします。

見た目を整える目的でセルを結合することも避けましょう。

結合セルは並べ替え、コピー、貼り付け、テーブル化でエラーや意図しない配置ずれを生むことがあります。

区切りを付けたいときは、罫線、行の高さ、テーブルスタイルを使う方法が安全です。

データの表と印刷用の帳票は別シートに分けると、見た目と機能を両立できます。

入力規則による表記ゆれの防止

地域を東京、東京都、東京支店のように自由入力すると、同じ地域でも別の値として集計されます。

データタブのデータの入力規則を使い、リストから選択する設定にすると表記ゆれを抑えられます。

地域一覧を別シートに作り、東京、大阪、福岡などの候補を縦方向に並べて参照すると管理しやすくなります。

日付列は日付形式、金額列は数値または通貨形式として入力します。

文字列として保存された数値はSUM関数の対象にならないことがあるため、列ごとにデータ型をそろえる意識が必要です。

複数表に分ける正規化

顧客名、住所、担当者を注文履歴の全行へ繰り返し書くと、住所変更時に多数のセルを修正しなければなりません。

このような重複を減らすため、顧客台帳、商品台帳、注文履歴を別々のテーブルに分けます。

注文履歴には顧客名ではなく顧客ID、商品名ではなく商品IDを記録します。

顧客IDや商品IDを共通のキーとして使えば、必要なときにXLOOKUP関数で名称や単価を取得できます。

これはデータベース設計でいう正規化の基本に近い考え方です。

小規模なExcelファイルでも、同じ情報を何度も保存しない設計を意識すると、修正漏れが少なくなります。

フィルターと並べ替えによるデータ抽出

続いては、登録したデータから必要な情報を素早く取り出すフィルターと並べ替えを確認していきます。

オートフィルターによる条件指定

テーブルの見出しにある下向き矢印をクリックすると、その列の値を選択して絞り込めます。

地域列で東京だけにチェックを残せば、東京の顧客だけを表示できます。

数値フィルターでは、購入額が10000以上、日付フィルターでは今月、先月、指定期間といった条件も利用可能です。

フィルターは行を削除する機能ではなく、条件に合わない行を一時的に非表示にする機能です。

元の一覧を保ったまま確認できるため、日常的な検索に向いています。

抽出後に件数を確認するときは、ステータスバーやSUBTOTAL関数を使うと便利です。

並べ替えで優先順位を見つける方法

購入額の大きい顧客を確認したい場合は、購入額列のフィルターボタンから降順を選びます。

日付列を昇順に並べれば、古い注文から新しい注文までを時系列で確認できます。

複数条件で並べ替える場合は、データタブの並べ替えを開き、まず地域、次に購入額のように優先順位を追加します。

テーブル全体を選択してから操作すれば、行の各項目がずれずに移動します。

一部の列だけを選んで並べ替えると、顧客名と金額の対応が崩れる危険があります。

FILTER関数による別表への抽出

Microsoft 365などではFILTER関数を使い、条件に一致するデータを別の場所へ自動表示できます。

この式では、テーブル名が顧客台帳であり、地域列が東京の行だけを抽出します。

1行目にヘッダーがあるテーブルを参照しているため、列名を使って条件を指定できます。

FILTER関数の結果は元データの変更に連動するので、検索結果を別シートへ表示したい場合に便利です。

抽出先のセル周辺には結果が広がるため、あらかじめ空けておくことが必要です。

データベース関数と集計数式の活用

続いては、データベース関数と集計数式を使って、表の内容を数値として活用する方法を確認していきます。

DSUM関数による条件付き合計

DSUM関数は、指定した条件を満たすデータだけを合計するデータベース関数です。

一般的なSUMIFS関数でも条件付き合計はできますが、条件をセル範囲として用意できる点がDSUM関数の特徴です。

A1からE10には1行目のヘッダーを含むデータベース範囲を指定します。

購入額は合計する列の見出しであり、G1からG2には地域と東京のように、見出しと条件値を縦に入力します。

条件範囲の1行目の見出しは、元表の見出しと完全に一致させる必要があります。

DSUM関数では、条件範囲にも見出しを含める点が、通常の合計関数と異なる重要なポイントです。

A1:E10  顧客データベースの見出しを含む範囲
G1     地域
G2     東京
H1     東京地域の購入額合計
H2     =DSUM(A1:E10,"購入額",G1:G2)

SUMIFS関数とCOUNTIFS関数の使い分け

条件付き合計を簡潔に記述したい場合はSUMIFS関数が便利です。

この式は、顧客台帳テーブルの地域列が東京である行の購入額を合計します。

COUNTIFS関数では、同じ条件に合う件数を数えられます。

顧客数、特定期間の注文数、未対応案件数などを把握したいときに役立ちます。

テーブル参照を使う数式は、行が増えても参照範囲を手動で広げる必要がありません。

データが継続的に追加される台帳では、固定のセル範囲よりも管理負担を減らせます。

XLOOKUP関数による台帳の連携

注文履歴に商品IDだけを入力し、商品名や単価を商品台帳から呼び出すと、重複入力を減らせます。

[@商品ID]は、現在の注文行に入力された商品IDを意味します。

商品台帳の商品ID列から一致する値を探し、対応する商品名列を返す仕組みです。

見つからない場合は未登録と表示するため、入力ミスにも気付きやすくなります。

IDで入力し、名称や単価は参照して表示する運用は、データベースを正確に保つ基本です。

SQLとPower Queryを使うデータ活用

続いては、Excelの表をより大きなデータ活用へ発展させるSQLとPower Queryについて確認していきます。

SQLの基本的な役割

SQLは、データベースに保存された情報を検索、追加、更新、集計するための言語です。

Excelのフィルターは画面上で条件を指定しますが、SQLでは命令文で必要な列や条件を記述します。

このようなSQLは、顧客台帳から顧客名と購入額を取り出し、地域が東京のデータに限定する考え方を表します。

Excelだけで小規模な台帳を管理する場合でも、テーブルを分けてIDで結び付ける考え方はSQLデータベースへ移行するときに役立ちます。

Excelの表を整えておくことは、将来Accessやクラウドデータベースを使う準備にもなります。

Power Queryによるデータの取り込み

Power Queryは、CSV、別のExcelファイル、フォルダー内の複数ファイルなどからデータを取り込み、整形する機能です。

データタブのデータの取得から、ファイルやフォルダーを選んで取り込みを開始します。

Power Queryエディターでは、不要な列の削除、列名の変更、日付形式の変換、複数表の結合などを行えます。

一度処理手順を設定すれば、次回は更新を実行するだけで新しいデータに同じ処理を適用できます。

毎月届く売上CSVをまとめる作業では、手作業のコピーと貼り付けを減らせるでしょう。

Power Queryは元データを直接書き換えず、読み込み時に加工するため、作業履歴を保ちやすい点も利点です。

ピボットテーブルによる集計表

テーブル内のセルを選び、挿入タブからピボットテーブルを選択すると、集計用のレポートを作成できます。

地域を行、購入額を値に配置すれば、地域別の購入額合計が表示されます。

登録日を列やフィルターに配置すると、月別、担当者別、商品別などの切り口も作れます。

元のテーブルにデータを追加した後は、ピボットテーブルの更新を実行しましょう。

データベースをテーブル化していれば、新しい行を集計対象に含めやすくなります。

データベース運用時のエラー対策

続いては、作成したExcelデータベースを安全に使い続けるためのエラー対策を確認していきます。

重複データの確認

顧客IDや注文IDの重複は、二重登録や集計の誤りにつながります。

ホームタブの条件付き書式から重複する値を選ぶと、同じIDを色で目立たせられます。

COUNTIF関数を使い、件数が2以上の値を確認する方法もあります。

結果が1なら通常は重複なし、2以上なら同じIDが複数登録されている可能性があります。

削除する前に、どちらが正しいデータかを必ず確認することが大切です。

バックアップと共有設定

重要な台帳は、上書き保存だけに頼らず、日付を含むファイル名で定期的にバックアップを残します。

OneDriveやSharePointで共有する場合は、共同編集の履歴機能を利用できるため、誤操作から戻しやすくなります。

ただし、複数人が同じ項目を同時に編集すると、入力内容の確認が必要になることがあります。

編集担当者、更新日時、修正理由を記録する列を作ると、データの変更経緯を追いやすくなります。

ファイルが重くなる場合の整理

数万行を超えるデータ、複雑な数式、画像、過剰な書式設定は、Excelファイルを重くする要因です。

使っていないシート、不要な画像、過去の中間集計表を整理すると、動作が改善する場合があります。

古い履歴は年度別のブックやCSVへ分け、現在利用するデータだけを主ファイルに残す運用も有効です。

さらに大きなデータ量を扱う場合は、Power Query、Access、SQL Serverなどの導入も検討対象になります。

まとめ エクセルでデータベースを作る方法

Excelでデータベースを作るときは、1行を1件、1列を1項目として整理し、1行目に見出しを置くことから始めます。

表をテーブル化すれば、フィルター、数式の自動入力、参照範囲の拡張を利用しやすくなります。

入力規則で表記ゆれを防ぎ、顧客IDや商品IDを使って台帳を分けると、重複データや修正漏れを減らせます。

日常の検索にはフィルター、条件付き集計にはSUMIFSやDSUM、別表への抽出にはFILTER関数が役立ちます。

SQLの考え方やPower Query、ピボットテーブルを組み合わせれば、Excelのデータベースは分析や定期レポート作成にも活用できます。

まずは小さな台帳を正しい形で作り、入力ルールを守って運用することが、使いやすいデータベースへの近道です。