Office Hack

エクセルでデータベース化する方法

  • Release

この記事では、エクセルでデータベース化する方法をご紹介します。

顧客情報等を管理したい場合に専用ソフトを導入しなくても、エクセルでデータベースを作成し管理することが出来ます。

以下では、データベースの様々な作成方法やデータベースから任意のデータを抽出する方法などについてもご紹介していますので参考にしてみて下さい。

エクセルのデータベース関数リスト

エクセルでは「データベース関数」と呼ばれる関数があります。

データベース関数を使うことで、データベースから様々な条件でデータを取り出すことが可能になります。

以下は、エクセルで使えるデータベース関数の一覧です。

関数説明
DAVERAGE関数条件を満たすセルの平均値を返す
DCOUNT関数条件を満たすセルの中で、数値が入力されているセルの個数を返す
DCOUNTA関数条件を満たすセルの中で、空白でないセルの個数を返す
DGET関数指定された条件を満たす値を1つ返す
DMAX関数条件を満たすセルの中で、最大値を返す
DMIN関数条件を満たすセルの中で、最小値を返す
DPRODUCT関数条件を満たすセルに入力されている数値の積を返す
DSTDEV関数指定された条件を満たす行を母集団の標本と見なし、母集団に対する標準偏差を返す
DSTDEVP関数条件を満たす行を母集団全体と見なし、母集団の標準偏差を返す
DSUM関数条件を満たすセルの合計を返す
DVAR関数条件を満たす行を母集団の標本と見なし、母集団に対する分散を返す
DVARP関数条件を満たす行を母集団全体と見なし、母集団の分散を返す

データベースの作り方

様々なデータベースの作り方をご紹介します。

顧客管理データベースを作成する

顧客管理のためのデータベースを作成する方法についてご説明します。

項目を入力する

まずは、どういった項目でデータベース化したいのかを入力していきます。

顧客管理に必要な項目の一例は以下の通りです。管理したい顧客情報によって適宜追加や削除を行いましょう。

  • 会員番号
  • 名前
  • 性別
  • 郵便番号
  • 住所
  • 電話番号
  • DM送付の可否
  • またデータベースとしてエクセルに認識させるには、データベースの上が一行空いている必要があります。

    今回は【2行目】を選択し、それぞれのセルに『会員番号』『名前』『性別』等を入力します。

データを入力する

次にデータベースとしてデザインを整えるために、先にいくつかの『顧客情報』を赤枠で示したように入力します。

項目名のすぐ下(例:3行目)から入力しましょう。

テーブルを設定する

次に、項目名と顧客情報にテーブルを設定します。

テーブルの設定をすることで、エクセルが対象のセル範囲内をデータのまとまりとして認識するためデータベースとして扱いやすくなります。

①【データベース(例:A2セルからG7セル)】、②【挿入】タブ、③【テーブル】の順に選択します。

OKボタンを押す

「テーブルの作成」ダイアログボックスが表示されます。

①「先頭行をテーブルの見出しとして使用する」に【チェックマーク】を入れて、②【OK】ボタンを押します。

データベースが完成する

これで、顧客情報のデータベースが完成しました。

テーブルの設定を行ったため、8行目以降に顧客情報を追加すれば自動的に色分けなどの書式が設定されます。

発注書/納品書からデータベースを作成する

次に発注書や納品書等のデータを管理するためのデータベースを作成する方法をご紹介します。

今回は、発行した納品書の情報をデータベース化していく方法を例に挙げてご説明します。

納品書とデータベースのシートを作成する

まずは、同じブック内に2つのシートを作成します。

シート名にそれぞれ任意の名前を設定しておきます。

今回は、①『納品書』、②『データベース』と入力します。

納品書を作成する

「納品書」のシートを開き、上の画像のような【任意の形式の納品書】を作成しておきます。

ここで作成した納品書がそのまま「データベース」シートでリスト化されていくイメージです。

黄色で塗りつぶししているセルの内容を「データベース」シートで、リスト化していきます。

項目名を入力する

「データベース」シートを開きます。

「納品書」シートの黄色い塗りつぶしをしたセルの内容を「データベース」シートに抽出し、内容をデータベースにコピーしていくことでリストを作っていきます。

まずは、1行目を開けて2行目に納品書から抽出してリスト化したい「項目名」を入力していきます。

【2行目】を選択し、それぞれのセルに『日付』『納品先』『担当』などの項目名を入力していきます。

コピーする

次に、データベースの「項目名」を6行目にコピーします。

6行目以降が実際のデータベースになります。

①【項目名(例:A2セルからK2セル)】を選択し、Ctrl+Cを押します。

②【任意のセル(例:A6セル)】を選択し、②Ctrl+Vを押して貼り付けます。

格子を設定する

次に、上の項目名を見やすくするために罫線の設定をします。

①【A2セルからK3セル】を選択し、②選択したセル範囲上で【右クリック】します。

③「罫線」の【▼】、④【格子】の順に選択します。

数式を入力する

次に、「納品書」シートからそれぞれのセル内容を抽出します。

例えば納品書内の「日付」が「納品書」シートのS2セルにある場合は、【A3セル】を選択して『=納品書!S2』と入力し、Enterを押します。

すべてのセルに数式を入れる

「納品書」シート内の「日付」がA3セルに抽出されました。

同じようにB3セルからK3セルまでの全てのセルに、納品書内の対応する項目を表示させるための数式を入力しましょう。

データを貼り付ける

これで、「納品書」シートから任意の項目の内容を「データベース」シートに抽出することが出来ました。

次に、3行目のデータを7行目以降にコピー&ペーストしてリストを作成していきます。

①【納品書シートからデータを抽出したセル(例:A3セルからK2セル)】を選択し、Ctrl+Cを押します。

②【A7セル】を選択し、Ctrl+Vを押して貼り付けます。

貼り付けのオプションで調整する

そのまま貼り付けるとデータの内容が変化してしまうため、貼り付けのオプションで調整します。

①【貼り付けのオプション】、②【値と数値の書式】の順に選択します。

テーブルを選択する

これで、データをそのまま7行目以降に貼り付けることができました。

次に、データベースにテーブルの設定を行います。

①【データベース(例:A6セルからK16セル)】、②【挿入】タブ、③【テーブル】の順に選択します。

OKボタンを押す

「テーブルの作成」ダイアログボックスが表示されます。

①「先頭行をテーブルの見出しとして使用する」に【チェックマーク】を入れて、②【OK】ボタンを押します。

納品書を変更する

これで、データベースでリスト化を行うための準備ができました。

試しに「納品書」シートで、上の画像のような納品書を作成し、【データベース】シートを選択してきちんと抽出できるか確認してみます。

テーブルにデータを貼り付ける

きちんと3行目に「納品書」シートの内容が抽出されています。

3行目のセル内容を8行目にコピーしましょう。

①【A3セルからK3セル】を選択し、Ctrl+Cを押します。

②【A8セル】を選択して、Ctrl+Vを押し貼り付けます。

値と数値の書式を設定する

そのまま貼り付けるとデータの内容が変化してしまうため、貼り付けのオプションで調整します。

①【貼り付けのオプション】、②【値と数値の書式】の順に選択します。

データベースが完成する

これで、納品書の内容をリスト化するためのデータベースが完成しました。

入力フォームからデータを登録する

エクセルでデータベースを作成した場合に、「フォーム機能」を使用するとデータの入力がスムーズにできます。

方法の詳細は以下の通りです。

その他のコマンドを選択

まずは、上記「顧客管理データベースを作成する」セクションを参考に任意のデータベースを作成しておきましょう。

「フォーム機能」は初期設定では表示されていないため、最初に表示設定を行う必要があります。

①【クイック アクセス ツール バーのユーザー設定】、②【その他のコマンド】の順に選択します。

フォームを選択

「Excel のオプション」ダイアログボックスが表示されます。

①「コマンドの選択」で【すべてのコマンド】を選択します。

②【スクロールバー】を下げて、③【フォーム】を選択し、④【追加】ボタンを押します。

OKボタンを押す

【OK】ボタンを押します。

フォームを選択

これで、クイックアクセスツールバーに「フォーム機能」が表示されました。

次に、フォーム機能を使ってデータの入力を行います。

①【データベース内の任意のセル(例:A2セル)】、②【フォーム】の順に選択します。

新規ボタンを押す

フォーム機能として「Sheet1」ダイアログボックスが表示されました。

【新規】ボタンを押します。

入力して新規ボタンを押す

①それぞれの項目に『新しいデータ(例:会員番号)』を入力し、②【新規】ボタンを押します。

データが反映される

データーベースを見ると、8行目に新しいデータが追加されているのが確認できます。

これで、フォーム機能を使ってデータを入力することが出来ました。

データベースから抽出する

データベースから任意のデータを抽出する方法をご紹介します。

抽出方法1:フィルターを使って抽出する

上記「顧客管理データベースを作成する」セクションを参考に作ったデータベースで、フィルター機能を使ってデータを抽出する方法をご紹介します。

以下の記事では、フィルター機能に関する様々な情報やトラブルが起きた場合の対処法等についてご紹介していますので参考にしてみて下さい。

エクセルのフィルター(オートフィルター)の設定から様々な使い方

フィルターでデータを抽出する

今回は上の画像のようなデータベースから「男」のデータのみを抽出します。

特定のデータのみ選択する

①「性別」の項目名にある【▼】を選択します。

②「男」のみに【チェックマーク】を入れて、③【OK】ボタンを押します。

データが抽出できる

データベースに「性別」が「男」の情報のみが表示されました。

これで、フィルター機能を使ってデータを抽出することが出来ました。

抽出方法2:関数で抽出する

DGET関数を使って、条件に合うデータを取り出す方法をご紹介します。

以下の記事では、DGET関数の詳細や複数条件で抽出を行う方法、DGET関数とVLOOKUP関数の違いなどについてご説明していますので参考にしてみて下さい。

ExcelのDGET関数の使い方|条件を満たすレコードの値を返す

DGET関数を入力する

今回は上の画像のデータベースを使って、I3セルに会員番号を入力したら該当顧客の"DM送付の可否"がJ3セルに表示されるようにDGET関数で数式を作成します。

DGET関数の書式は、「=DGET(データベース,フィールド,条件)」です。

まずは【J3セル】を選択し、「=DGET(」と入力します。

データーベースの引数を設定する

次に、データベースの引数を設定します。

「=DGET(」に続いて『A2:G8,』と入力します。

フィールドの引数を設定する

次に、フィールドの引数を設定します。

「DM送付の可否」をJ3セルに表示したいので、「A2:G8,」に続いて『G2,』と入力します。

条件の引数を設定する

次に、条件の引数を設定します。

「G2,」に続いて『I2:I3)』と入力しEnterを押します。

試しに条件を入力する

これで、DGET関数を使った数式が完成しました。

試しに、I3セルに『101』と入力しEnterを押します。

データが抽出できる

J3セルに会員番号「101」に対応する顧客の「DM送付の可否」が表示されました。

これで、DGET関数を使ってデータを抽出することが出来ました。

抽出方法3:検索フォームで抽出する

上記「入力フォームからデータを登録する」セクションでご紹介した「フォーム機能」では、入力だけでなくデータの検索も可能です。

フォーム機能を使ってデータを抽出する方法は以下のとおりです。

今回は、会員番号から会員情報を呼び出す方法を例に挙げてご説明します。

フォームを選択

まずは、上記「入力フォームからデータを登録する」セクションを参考に、フォーム機能を表示させましょう。

次に、①【データベース内の任意のセル(例:A2セル)】、②【フォーム】の順に選択します。

検索条件を選択

フォーム機能として「Sheet1(2)」ダイアログボックスが表示されました。

【検索条件】ボタンを押します。

任意の条件を入力

検索条件を入力します。

今回は会員番号で会員情報を呼び出したいので、①「会員番号」に『任意の会員番号(例:103)』を入力してEnterを押します。

データが抽出される

「103」の会員番号に該当する会員情報が表示されました。

これで、フォーム機能を使って会員情報を抽出することが出来ました。

[Excel 応用]の関連記事

Excelの名前の定義を削除する方法(削除できない場合の対処も)

エクセルのカメラ機能とは?使い方や使えない場合の対処法

エクセルでQRコードを作成する方法

エクセルの作業グループとは?設定方法と解除方法