エクセルでデータベースを作る方法｜表の5つのルールと、限界が来るサイン

エクセルのデータベースとは、1行を1件にした表のことです

データベースとは、情報を検索や集計がしやすい形で整理して、ためておく仕組みです。エクセルでこれを作るというのは、顧客、案件、商品などを1行＝1件、1列＝1項目に並べた表を作ることです。見た目が整った帳票ではなく、あとから絞り込める「元のデータ」を作ります。

見た目を優先した表は、データベースには向きません。月ごとの表を縦に積んだり、セルを結合して見出しを作ったりした表は、人が読むには分かりやすくても、並べ替えや集計をかけると崩れます。次の図は、同じ内容を2通りに書いた例です。

表を崩さないための5つのルール

エクセルのデータベースは、表の作り方で決まります。見出しを1行にする、1行を1件にする、1セルに1項目、空行・空列・結合セルを作らない、表記を1通りにする、の5つを最初に決めておくと、テーブル機能も関数もピボットテーブルも、そのまま使えます。あとから直すより、最初の1行目を決めるほうがずっと手間がかかりません。

最初に決めるのは「1行が何を表すか」です。取引先ごとなのか、案件ごとなのか、請求ごとなのかが決まらないまま列を増やすと、あとで集計の単位が合わなくなります。迷うときは、「この表を数えたとき、何の件数になるのが自然か」を考えます。

エクセルでデータベースを作る5つの手順

ルールに沿った表ができたら、エクセルの標準機能を順に使います。特別な追加機能は要りません。

テーブルにすると、行が増えても範囲がずれません

表の中のセルを1つ選んで、Ctrl＋Tを押します。見出し行があることを確認して決定すると、表が「テーブル」になります。以後、下に行を足すと、書式と数式が自動で広がります。「範囲を選び直し忘れて、集計から最後の数行が漏れた」という事故を防げます。

プルダウンで入力の表記をそろえます

状態や担当者のように、選択肢が決まっている列は、入力規則でプルダウンにします。自由入力にすると、「対応中」「対応中 」（末尾の空白）「対応中。」が別の値として数えられます。選択肢はテーブルの別シートに書いておき、そこを参照させると、あとから追加しても全行に反映されます。

別の表から引くときは、名前でなくIDで結びます

取引先の住所や担当者を、案件の表に毎回手で書き写していると、変更があったときに片方だけ古くなります。取引先の表に通し番号のIDを持たせ、案件の表にはIDだけを書いて、XLOOKUPで取引先名を引きます。会社名は表記ゆれや社名変更が起きますが、IDは変わりません。表を分けてIDで結ぶ考え方は、スプレッドシートのデータベース化で、受注台帳を4つの表に分ける手順として詳しく書いています。

エクセルのデータベースで、作った表から探す・取り出す・重複を見つける

データベースは、入れた情報を探し出せて初めて役に立ちます。エクセルの表では、目的に応じて次の機能を使い分けます。件数が数千行までで入力者が少ない顧客管理や案件管理の一覧なら、この4つで日常の検索はまかなえることが多いです。件数や運用によっては足りなくなります。

重複は入る前に防ぐ

重複は、増えてから消すより、入る前に防ぐほうが確実です。IDの列に重複を禁止する入力規則（ユーザー設定の数式で COUNTIF を使う方法）を入れておくと、直接入力した場合は同じIDの二重登録を止められます。ただし、入力規則はセルへの直接入力だけを検査し、貼り付けやコピー、オートフィルで入れた値は検査されません。定期的に条件付き書式の「重複する値」で確かめてください。会社名のように表記ゆれがある列は、完全一致の重複検査ではすり抜けるため、IDで見分ける前提にしておきます。

入力の手間を減らす

入力の手間を減らしたいときは、入力用の「フォーム」を使います。Excelには、1件ずつ入力・検索できる標準の「フォーム」コマンドがありますが、初期状態ではリボンに出ていないので、クイックアクセスツールバーに追加して使います。入力者が増える場合は、Microsoft Formsなど別の入力画面から表に集める方法もあります。ただし、Microsoft Formsなどで複数人が入力する運用になったら、編集する場所の重なりや入力ミスを運用だけで防ぎにくくなり、エクセルを専用のデータベースへ移すサインに近づいています。

共有ファイルの注意

複数人で見る表は、OneDriveまたはSharePoint Onlineに置き、共同編集に対応するExcelで開けば、同じブックを同時に編集できます。変更も作業中に表示されます。

Microsoftの説明では、2人が同じ箇所を変更した場合は、例外はあるものの、一般に最後に保存された変更（自動保存を含む）が優先されます。共同編集は同時作業には有効ですが、同じ箇所の編集や誤入力を自動で防ぐ仕組みではありません。必要なら担当する領域やシートを分けます。出典：Microsoft サポート「Excel ブックの共同編集を使用して同時に共同作業を行う」

専用のデータベースと比べると、エクセルが弱いのは4点です

エクセルは自由度が高い反面、データを守る仕組みは持っていません。個人で使うぶんには問題にならなくても、複数人で使い始めると、次の4点で差が出ます。

なお、1シートで扱える量にも上限があります。上限そのものはExcelの最大行数は1,048,576行で説明していますが、業務で困るのは、行数に届くずっと前の、人が増えたときのずれのほうです。

エクセルで管理すると起きること

同じ進捗を複数の表へ転記すると、どれが最新か分からなくなります

エクセルで案件を管理しながら、部署用や報告用の表にも同じ進捗を書き写すと、更新する場所が増えます。一つの表だけを直した時点で内容が食い違い、どの表が最新かを毎回確かめる作業が必要になります。

部門ごとに表を分けると、質問のたびに集計が始まります

部門ごとに別のエクセルで数字を作ると、全社の合計を一つの表から出せません。「今月いくら」と聞かれるたびにファイルを集める作業が始まるなら、数字がないのではなく、元にする表が決まっていない状態です。

移すかどうかは、3つのサインと、移す先の条件で決めます

エクセルのデータベースが限界に来たかどうかは、行数ではなく次の3つで見分けます。

• 入力する人が3人以上になった。担当範囲を分けないと、同じ箇所を同時に書き換える機会が増える。
• 別ファイルへの転記が出てきた。同じ数字を複数のファイルに手で書いている。
• 集計のたびに数字がずれる。誰かが直したことが、ほかの人に伝わらない。

サインが出たら、移す先を選びます。移す先ごとに向く条件が違うので、1つを最良とせず、次の表で自社の条件に合うものを選びます。

移すときは、既存データの整理も必要です。支援先では、別の営業管理システムからデータを移した際に、数万件規模の会社データのうち1割ほどが重複していました。移行前に1件を見分けるIDと入力ルールを決め、重複の判定材料を集めた確認シートで整理しました。

どの選択肢でも、先に決めるのは「1件とは何か」と「誰が、何で入力するか」です。これが決まっていないと、移した先でも同じ表の混乱が起きます。移す対象の見極めと進め方はエクセル業務のシステム化に、判断の基準はExcelでの管理、いつ卒業すべきかにまとめています。

効果は「転記の回数」と「数字を直した回数」で測ります

表のルールを決めたら、月に1回、2つを数えます。同じ内容を別の場所へ手で書き写した回数と、集計の数字を後から直した回数です。ルールを守った表では、どちらも減っていくはずです。減らないなら、表の作り方ではなく、入力する人の運用に原因があります。

来週は、いちばん使っている表の「1行の意味」を書き出します

いま一番よく使っているエクセルの表を開き、「1行が何を表すか」を1文で書いてみてください。書けない、または行によって意味が違うなら、そこが直す場所です。続けて、見出しが1行か、結合セルや空行がないかを確かめます。ここまでで、データベースとして使える表かどうかが分かります。

SalesDockは、成長に業務・データ・仕組みが追いつかない中小企業の経営管理基盤を、月額で整える支援をしています。エクセルの表をデータベースとして整える話は、その入口の一つです。