コスパ村のあんぱんです。
10年で4社、真剣に総務をやっていました。
この記事は、
「エクセルでデータベースを作成したいけど」「なにをどう作れば良いのか全くわからない」という中小企業の管理者向けの記事です。
パソコン教室などで習った知識を実戦でどう使えば良いのか、プロの私がズバッと解説します。
目次
【問題】エクセルでデータベースを作成したい
以前、会社コスパ向上のためには管理が必要であり、管理の対象は「モノ（データ含む）・仕事・人」だという記事を書きました。
（支払申請を例に解説！管理職はどうすれば役割を果たせるか（2020年11月5日））
管理ができない会社は生産性が低いです。
データベースは「モノ」（お金、請求書、商品、原材料、社内申請書など）を管理するための強力なツールです。
全然大丈夫、実戦を知っているかどうかだけの問題です。
この記事を見ればあなたもすぐエクセルでデータベースを構築して生産性を上げる事ができます。
在庫数、支払金額などの管理について
- システム導入は必要ないのでエクセルで乗り切りたい
- csvに貼付けてシステムへそのままインポートできる形のデータベースをエクセルで作りたい
- パソコン教室で習った関数を、実戦の仕事でどう使えば良いのかわからない
こんな方はこの記事をご覧ください
【結論】一番重要なのは1ファイルの中のデータベースは1シートだけにまとめること
データベース設計で一番重要なのは、1つのエクセルファイルの中のデータベースは1シートだけにまとめる事です。
これは絶対に守るべきです。
これができるかどうか ≒ データベースを作れるかどうか、であり、1つに集約されているからこそデータベースです。
（集計シートは目的ごとに作っても良い）
未熟な会社ではよく100店舗の情報を100シートに分けていたりしますが、それでは管理になりません。
| エクセルのデータベースは以下5工程で作成します。 ①エクセルのデータベースを何に使うのか、目的（ゴール）を明確化 ②目的に直結するエクセル集計シートを作る ③エクセルのデータベースの項目を決定 ④データベースを関数で集計シートへ ⑤あとはデータベースにレコードを記入していくだけで集計表に勝手に自動集計されていってしまう |
全てを網羅した1つのデータベースシートから、各集計シートへ関数で情報を飛ばしましょう。
（データベースシートも集計シートも、他人に見せたり渡したりしますから印刷範囲などは設定しておいてください。）
以下、詳述します。
【詳細】
エクセルのデータベースを何に使うのか、目的（ゴール）を明確化
ナニゴトもゴールから逆算しないとアサッテの方向に全速力で向かって瓦解します。
例として、100店舗ある会社の「省エネ法の定期報告（※）をする」を目的として話を進めましょう。
100店舗について、毎月の電気・ガスなどの使用量を、国に報告するのが目的です。
※省エネ法は、一定規模以上の事業所に対して電気・ガスなどの使用量を報告させている（守らなければ罰則有）
電気の使用量、100店舗分もどうやって管理しますか？この程度のモノの管理にお金かけてシステムなんか入れたらコスパ最悪ですよね。
エクセルでデータベース管理しましょう。
目的に直結するエクセル集計シートを作る
電気ガスの使用量は、電気会社や電力会社からcsvファイルなどで一括ダウンロードできる店舗はまだ良いですね。
毎月検針票を見て手入力しなければいけない店舗もあるので作業量は膨大なのでツライです。
手間ですが一度力を入れて強力なデータベースを作って、スピードを蓄積してしまいましょう。
はい本題。
省エネ法の定期報告に直結する集計シートとしては、「年度ごと、エネルギー種類ごと、月ごと、店舗ごとの使用量」が必要です
ですので、集計シートは以下のイメージです。
こんな集計シートなら、O列の小計の全店合計値を「目的である定期報告書」に記載するだけですよね。
あとはデータベース情報を関数でこの表に飛ばせば良いだけです。
では、どんなデータベースを蓄積すればこの表ができるでしょうか。
エクセルのデータベースの項目を決定
データベースの項目としては、1.「通し番号」2.目的に直結する「集計シートに必要な情報」（必須）3.補足情報 4.「どうせいつか必要となる情報」の4種類です。
つまり、以下のイメージです。
ここでの「通し番号」は、11から383が飛んでますが、これはもちろんあくまでも今見やすくするため。
電気もガスも、4号店も37号店も、全部一緒の一つのシートに入れるんですよって説明するためです。
実際には、続き番号を入れてくださいね。
通し番号（A列）
「通し番号」（A列）は管理のために必ず入れてください。
2020年11月19日現時点ではエクセルの限界で1万レコードを越えたあたりから非常に動作が重くなります。
後々1万レコードずつデータベースを分ける必要が出た場合、行数ではレコードを管理できなくなります。
集計シートに必要な情報
上の集計表から必要な情報を抜き出しましょう。
- 店舗名（B列）
- 使用エネルギーの種類（C列）
- 何年度の使用分か（F列）
- 何月分の使用分か（G列）
- エネルギー使用量（H列）
- エネルギー使用の単位（I列）
ですね。
補足情報
データベースだけで独立して意味がわかるように、情報を補足しましょう。
ここでは以下を入れないと、データベースを見ただけでは何のことやらさっぱりわからなくなってしまいます。
- 燃料の詳細（D列）
- 電気やガスの供給会社会社（E列）
どうせいつか必要となる情報
問題は「どうせいつか必要となる情報」。これは未来を予測して、いつか必要な時になって改めて調べようとすると大変な労力が必要になる情報をついでに一緒に入力しておきます。
例えば今回であれば、
どうせいつかこの実績をもとにボリュームディスカウントを効かせてコスト削減をしようとするでしょうからついでに金額も入れておきましょう。
そして、どうせいつか異常値がでたときなどに何月何日から何月何日を何月分にしているのかを、経理や店舗責任者から聞かれますので検針日も入れておきましょう。
「どうせいつか必要となる情報」はある程度経験が必要ですので、初めはあまりお気になさらず。
無くても十分ですし、このやり方を続けていればきちんと発想できるようになります。
データベースを関数で集計シートへ
それではデータベースシートの情報を集計シートへ関数で自動計算させましょう。
まず集計シートのF5セルを見て下さい
青枠内はデータベースのシートですが、
データベースシートの「通し番号389番」に「1号店」の「電気」について「2019年」「7月の使用料が」「5216.0」「kwh」
であるというレコードがあります。
このデータベースシートのH390セルにある「5216.0」が、集計シートのF5セルに反映されています。
これはどうやるのでしょう。
F5セルにSUMIFS関数を入れる
集計シートF5セルの中の関数を見てみましょう。
SUMIFS関数が入っています。
F5セルSUMIFSの（）の中の意味
緑枠内をご覧ください。F5セルには
『 =SUMIFS(DB!$H:$H,DB!$B:$B,$B5,DB!$G:$G,F$4,DB!$F:$F,$B$1,DB!$C:$C,$B$2) 』
と書いてあります。
この意味は、データベースシートを青色、集計シートを赤色とすると、
「②の中で② かつ ③の中で③ かつ ④の中で④ かつ ⑤の中で⑤ の情報について ①を全部足した数を答えて！」
という意味です。
だから、データベースシートのH390セルにある「5216.0」が、集計シートのF5セルに反映されるんですね
絶対参照と相対参照に気を付けながらF5セルをC5セル～N104セル（以下ピンク部分）にコピペ
ほら一丁あがり。
できました？
あとはデータベースにレコードを記入していくだけ
大変ですが一度この仕組みさえ作ってしまえば、あとは勝手にどうしても自動集計されていってしまう事になります。
【注意点】データベースの記載の文字列統一
データベースは、文字列を統一的に記載しないと正しく集計できず、全く意味がないです。
統一的記載のため、なるべく言葉はリストを作ってプルダウン選択をオススメします。
以下統一できているか確認！
- 半角or全角
- スペースの有無
- 言葉の並び順
- 文字列
【まとめ】
エクセルでデータベースを作成するには
- 絶対に1シートだけに集約する
- 5工程で作成、関数はSUMIFSを使う
- 文字列を統一的に記載するために、リストを活用する
でした
「面倒くさっ！」という方は外注総務で作成も行っています。