エクセル（EXCEL）で簡単なデータベースを作る方法
前回はデータベースを一元管理することで仕事が効率化されることを解説してきました。
今回はエクセルで簡単なデータベースを作る方法を３ステップで紹介します。
- リレーショナルデータベースの概念を理解する
- エクセルで複数のテーブルを作る
- 複数のテーブルを組合せて「VLOOKUP関数」で必要なデータを抽出する
一つづつ見ていきましょう。
リレーショナルデータベースの概念を理解する
実際にエクセルで作業を始める前にデータベースについて少し説明します。
エクセルでデータベースの管理をする場合は、リレーショナルデータベースという考え方を元に行います。
事前定義された、関連があるデータ項目の集合体です。この項目は、列と行を持つテーブルのセットとして構成されます。テーブルは、データベースに表現されるオブジェクトに関する情報を保持するために使用されます。テーブルの各列には、特定の種類のデータおよび属性の実際の値を保存するフィールドが保持されます。テーブルの各行は、1 つのオブジェクトまたはエンティティに関連する値のコレクションです。テーブルの各行は、プライマリキーと呼ばれる固有の識別子を使ってマーキングできます。また、外部キーを使用すると、テーブル間で複数の行を関連付けることができます。このデータには、データベーステーブル自体を再編成することなく、さまざまな方法でアクセスできます。
引用：https://aws.amazon.com/jp/relational-database/
複数のテーブルのデータを呼び出します。
各テーブルにはユニークなプライマリーと呼ばれる識別子を持っています。
識別子をキーにデータベース間をつなぐことで新しいテーブルを作成することができます。
この考え方は、例えばACCESSなどのデータベースソフトを利用する場合にも活用できる考え方なのでマスターしておきましょう。
エクセルで複数のテーブルを作る
まずはいくつかのテーブルを準備しましょう。
テーブルも大きく2つの種類に分けることができます。
- マスターテーブル
- トランザクションテーブル
マスターテーブルは、管理者が管理するデータのテーブルです。
マスターテーブルのデータの保守を確実に行うことが有益なデータベースとなるかどうかの分かれ目になります。
トランザクションテーブルは動的なデータを格納するテーブルです。
例えば、お店の売上のように日々データが増えていくようなテーブルのことです。
２種類のテーブルをうまく組みわせることで、便利なデータベースになります。
テーブルの構成とルール
テーブルは3つの要素で出来ています。
- テーブル名
- フィールド名（タイトル）
- レコード（データの本体）
テーブル名はエクセルのシート名にしましょう。フィールド名は1列目に設定します。2列目以降はレコードとしてデータを格納します。
A行は主キーを設定します。主キーは同じテーブル内ではユニークな値（ダブらない値）とすることが大切です。
他のテーブルとの関連付けは主キーで行うことがデータベースをうまく連携するポイントとなります。
テーブルが増えてくると2つテーブルで同じ情報を持ってしまうことがあるので注意して下さい。
データがダブルと保守が大変になる上に、片方のみ更新を忘れデータの不整合が発生しやすくなります。
例）販売に必要なテーブルを考える
製品販売に関するデータベースを作ってきましょう。
マスターテーブルは顧客情報テーブルと商品情報テーブルを準備します。
顧客情報のテーブル
商品情報のテーブル
次に、トランザクションテーブルとして販売実績を入手します。
販売実績のテーブルには、マスターテーブルの主キー以外の項目（顧客名、商品名、製品単価など）はありません。
顧客情報や商品情報は主キーをレコードに記録させるのがポイントとなります。
販売実績のテーブル
複数のテーブルから必要なデータを抽出する
テーブルを連携させる必要なデータを抽出するには「VLOOKUP関数」を利用します。
トランザクションテーブルのレコードにはマスターテーブルの主キーを記録するように設計しています。
主キーをキーとしてマスターテーブルから必要なデータを抽出していきます。
「VLOOKUP関数」ってなに？という人はググってみて下さい。たくさん解説しているサイトが出てきますので。
ここでは、テーブルから必要なデータを抽出するための「VLOOKUP関数」の使い方を解説します。
検索値：抽出したい値を持っているテーブルの主キー
検索範囲：抽出したい値を持っているテーブル全体
列番号：抽出したい値がある列が右から列数（B列なら2、C列なら3）
例）販売実績のテーブルと顧客情報と商品情報のテーブルを連携する
例で上げた販売実績のテーブルと顧客情報と商品情報のテーブルを連携して次のようなテーブルを作ってみよう。
ベースは販売実績のテーブル（トランザクションテーブル）となる。（A列、B列、G列は販売実績のデータ）
顧客名、性別は顧客情報のテーブルから、商品名、商品単価は商品情報のテーブルから抽出する。
それぞれの項目の2行目の関数は下記となっている。
性別(D2)：=VLOOKUP(販売実績!C2,顧客情報!A:E,3,FALSE)
商品(E2)：=VLOOKUP(販売実績!D2,商品情報!A:C,2,FALSE)
商品単価(F2)：=VLOOKUP(販売実績!D2,商品情報!A:C,3,FALSE)
売上金額(H2)：=F2*G2
抽出したデータは色々な使い道が考えられるのではないでしょうか。
- 売上金額の管理
- 売れ筋商品の調査
- 性別別の購買者の割合の調査
管理からマーケティングまで幅広くデータを活用することが出来ます。
この例ではできませんが、地域別の販売動向を調査したい場合は、顧客情報の住所を抽出すればよいです。
データの活用する目的を明確にして、テーブルを設計すれば利便性の高いデータベースの構築ができます。
まとめ
いかがでしたでしょうか？
エクセルでもリレーショナルデータベースの考え方を元にテーブルを管理すれば、十分実用的なデータベースを作れます。
機能はデータベース管理専用のソフトにはかないませんが、小規模なデータの管理ならエクセルでも十分使えます。
データの管理ができてないのならば、この方法でデータの管理を見直しみましょう。