Excelは表形式で入力できるので、社員一覧表、顧客一覧表、取引一覧表といった一覧表を作成することができます。
何らかのデータを管理したり、チェックリストを作ったりするときに「一覧表作成ツール」としてExcelを使っている人は少なくないでしょう。
この一覧表、きちんとした形でデータが格納されている場合、分析に活用できるデータベースとして機能します。
しかし、Excelをデータベースとして使う場合にはルールがあり、そのルールを守らないと途端に使いにくくなります。
この記事では、Excelで正しくデータベースを作る方法について解説します！
データベースとは
データベースには、いくつかのポイントがあります。
- 一行目が項目名になっていること
- それぞれの列ごとに項目ごとのデータが入っていること
- それぞれの行ごとに1件ごとのデータが入っていること
このような形の一覧表がデータベースとして使うことができます。
1列ごとの項目のことをフィールド、1行ごとの1件のデータのことをレコードと呼びます。
Excelでデータベースを作る際のアンチパターン｜悪いポイントと注意点を解説
Excelで正しいデータベースを作ると、フィルターや並べ替えをすぐにできる形になります。また、ピボットテーブルで集計したり、集計関数を使ってまとめたりすることも困難になります。
ここでは、データベースのお作法に従っていない悪い例を挙げます。
アンチパターン1：そもそもデータベース形式になっていない
たとえば、ある会合への出席者一覧などの印刷物を作成する目的で、以下のようなかたちでデータを入力するケースはあるでしょう。
会合後、上司から「出席者の分析をしておいて！」と言われたとき、これらのかたちでは分析することができません。
ベストプラクティス（下図）のようなかたちでデータベースを構築しておき、Excel関数やマクロで上図のようなアウトプットを作成する方が合理的でしょう。
アンチパターン2：セルが結合されている
セル結合は見た目を整えるうえで非常に便利な機能です。報告資料作成などにおいては重宝しますよね。
しかし、データベース作成においてはアンチパターンと化してしまいます。
セルを結合していると、結合されたセルの値が空白扱いになるので、並べ替えも集計もできなくなってしまうのです。
アンチパターン3：セル結合はされていないが、無意味な文字列が含まれている
同じように、上の項目と同じなので「〃」という記号を埋めていると、そこは「〃」というデータだとExcelは判断してしまいます。
アンチパターン4：項目名が2行にわたって記載されている
このデータでは、項目名が2行に渡っています。あくまでデータベースは項目名が1行目に入っている必要があります。もう一ついけないのは、項目名「2018年」から「2021年」までが2重に入っていることです。
アンチパターン5：シート内でデータベースが複数に分断されている
Excelのデータベースでは一覧表範囲の上下左右に見出しや解説文、他の一覧表があってはいけません。
一覧表の上下左右には1行1列以上のすき間が必要です。Excelが誤認識するのをできるだけ避けるには、1つのデータベースは1シートに作っておくとすると良いでしょう。
アンチパターン6：表記揺れ（表記のゆらぎ）が存在する
その他にも注意することとして、「ABCD株式会社」を「ABCD社」「ABCD㈱」のような表記揺らぎで入力していると、Excelは同じ会社として扱うことができません。
Excelでデータベースを作る手順
次の一覧表は、Excelとして最も正しいデータベースの形です。
このようにExcelで正しいデータベースを作るにはExcelの「テーブル」機能を使うと良いでしょう。
一覧表の範囲の1つのセルをクリックして、Ctrl+Tのショートカットキーを押すと、次のようなメッセージが表示されます。
ここでOKボタンをクリックすると、一覧表のセル範囲が普通のセル範囲と違う特別な「テーブル」の範囲に変わります。テーブルの範囲になると一覧表が縞模様になり、1行目の項目名のセルそれぞれに、フィルターや並べ替えができる下向き三角が表示されます。
もしも、テーブル設定時に結合されたセルがあると、結合は解除され、同じ項目名があれば、項目名の後ろに数字が付くようになり、自動的にExcelが認識できるデータベースの形になります。
テーブルでは、下にスクロールすると列の見出しが項目名になります。
テーブル機能を設定した一覧表には、新しくデータを追加するとテーブルの範囲が自動的に下方向に広がり書式や計算式が反映されます。後からデータが追加されても、新たにテーブル範囲を設定しなおしする必要はありません。
次に考えておきたいのが、テーブルにデータを入力する際に、できるだけ簡単に登録できるようにする設定をしておくと作業が楽になります。
一つは、そのセルをクリックすると下向き三角が表示され、下向き三角をクリックすると選択肢が一覧で表示されるといった仕組みです。
もう一つは、データベースに入力するものは日本語もあれば英数字もありますが、英数字を入力する際に自動的に半角に切り替わる設定をしておくと楽です。
どちらも、「データの入力規則」という機能で設定ができます。
データの入力規則は、「データ」タブの中の「データの入力規則」の中の「データの入力規則の設定」から設定を行います。
設定をしたいセル範囲を選択したら「データの入力規則」を開いて「値の種類」を「リスト」にして値のボックスに選択したい値をカンマ区切りにして入力し、OKボタンをクリックします。
次のように、下向き三角で選択肢を選べるようになります。
すでにどこかのセルに入力されている一覧表で選択したい場合は、その一覧の範囲のセル参照を設定するということもできます。
セルをクリックした時に半角入力になる設定は入力規則の日本語入力タブの日本語入力のオフ英語モードに設定しOKボタンをクリックします。
金額や個数、郵便番号のフィールドに設定しておくとよいでしょう。
Excelでデータベースを作るデメリット・注意点
Excelでデータベースを作れば、そのデータベースを元に、SUMIF関数などでの集計や、ピボットテーブルでの分析、並べ替えやフィルターといった便利な使い方ができます。
その反面、操作ミスによって、データベースが崩れたり消えたりしてデータが正確ではなくなるケースもあります。
並べ替えの時に、1列だけ選んで並べ替えると次のようなメッセージで警告されます。
これで、「現在選択されている範囲を並べ替える」の選択肢を選んで並べ替えると、その部分だけが並べ変わってしまいます。この操作が原因で、ニュースになるような重大なミスも起きていますので注意しましょう。
並べ替えの時は、範囲を選択せず、並べ替えしたい項目の中の一つのセルだけをクリックし、並べ替えをしましょう。
他の注意点として、セルをクリックして何かのキーを押しただけで、そのセルに新たなデータが入力されてしまいます。そのままEnterキーを押すと確定してしまいますので、間違いに気付いたらすぐにESCキーを押す、確定してしまったのであればすぐに元に戻す操作を行いましょう。
無意識のうちにしてしまいがちな操作なので、操作の後はひとつひとつ、しっかり確認する癖をつけましょう。
まとめ
今回は、Excelでデータベースを作る方法を紹介しました。
データがあるからただ入力する、横に長くなるから一つのデータを複数行で入力する、見た目が悪いのでセル結合で整えて入力する、このようなことで作られたデータ一覧表は、Excelが一覧表だと認識することができないのでそのデータを使ったデータ処理ができなくなるのです。
きちんとデータの1行目には項目名を設定する、1行には一件のデータを入力する、1列ごとに項目を入力する、上と同じデータだからといって省略しない、表題や他の一覧表は前後左右に一つ以上のセルを開けて配置する、といったことが必要です。
そのためにはExcelのテーブル機能が力を発揮します。
また、データを追加したり変更したりする時に、出来るだけ簡単に入力できるように入力規則を設定しておくと良いでしょう。
Excelのデータベースは、できれば一つのシートに一つのデータベースとすれば混乱することがなくなります。
Excelでデータベースを作成した場合、ほんの少しの操作ミスでデータベースが崩れてしまうこともあります。慎重な操作をするとともに、バックアップを取っておくなどの対策もしておきましょう。
セルを結合してはいけない、データの1行目に項目を入力するといったルールは、Excelでデータベースを作る場合の注意点ですが、Excelで作成できるものはデータベースだけではなく、グラフなどが記載された集計結果のレポートの場合もありますし、計算書の場合もあります。その場合は、今回のルールの範囲ではないのでセルを結合したりして自由にレイアウトして良いです。
きちんと、用途によってシートを使い分けていく、という使い方が賢いExcelの使い方です。
