Excelで在庫管理表と請求書を作成|データベースの作成(1)

ボットテーブルの使い方をマスターできれば、Excelの基本操作に関してはもうほぼマスターできているといっていいと思います。

あとは次項から解説するデータベース系の操作と様々な関数を使ってみるといったところでしょう。Excelの応用編 では主に小技的な「かゆいところに手が届く」機能について解説します。

本項では在庫管理表と請求書と銘打っていますが、実際にはこれまでの知識で、自分独自の在庫管理表や請求書を作成できる力はもうすでにあるはずです。

例えば「在庫管理表」などは、前項の「練習課題9」で学習したアンケート集計のように在庫の出入を日付ごとに1行ずつ蓄積していき、任意の時点でピボットテーブル機能で集計すれば、簡単に集計を求めることができます。

とはいうものの、今後、データを蓄積して集計したり分析したり、Excelを使いこなしていくためには、蓄積されたデータ集合である「データベース」というものの知識を得ておく必要があります。

データベースというと難しそうに聞こえますが、本格的なデータベースを構築するという意味ではなく、データベースを構築するにあたり、

あとでその蓄積されたデータを利用しやすいようにデータの格納様式を考えることができる

という意味です。つまり、前項でも少し触れましたが、

適正な入力フォーマット(形式)を最初に作成すること

が重要で、そのつくり方を理解していることが大切です。フォーマット(形式または様式)とは表題の「テンプレート」と同義語でも使われます。データを入力する「ひな形」という意味です。

このフォーマットを適正に構成することができれば、あとはデータをそのフォーマットに格納(入力)するだけです。フォーマット次第で、効率・非効率に大きく影響します。

したがって本項では、データベースを作成するための予備知識と、テンプレートというものの考え方に重点をおいて学習したいと思います。

例題
例題11 在庫管理表と請求書

例題11はシートが3つあります。「商品管理表」シートは、それぞれの商品についてコード分類し、属性情報を書き込んできます。「在庫管理表」シートでは、日付ごとの入荷および出荷の推移を記録しています。そして、「請求書」シートは、そのまま入力すれば請求書になる様式となっています。

シートを3つに分けているのには理由があります。このように、在庫管理などのデータベース化(データの集合)をするときには、

ひとつのシートになんでもまとめて詰め込むと、かえって管理しにくくなる

からです。基本的なデータベースのつくりかたは、それぞれのレコード(行のこと。この場合は商品)に固有のコード番号を付与し、そのコード番号のみによって管理します。

つまり、極力、コード番号以外の情報(商品名など)を他のシートに入力させないようにするわけですが、具体的には、例題のように商品の属性(名称やメーカー、原価などの情報)を管理するシート、商品の動き(日付ごとの取り引きの推移)を管理するシートに役割をわけて入力していきます。

そうすることによって、例えば、メーカーの住所が変更になった、原価が変わった、などといった場合には「商品管理表」シートの金額だけを変更すれば済むからです。これがひとつのシートにまとめて管理していた場合には、該当する箇所全部を修正しなければなりません。すなわち、不変的なコード番号以外の情報は各シートに重複入力しない というのが理想的なつくりかたです。

Excelは簡単なデータベース機能を持っていますが、OfficeにはExcelのほかにAccess(アクセス)というデータベース専用ソフトがあります。Accessではこのように役割ごとに分けたシートを「テーブル」といい、それぞれのテーブルは重複しない固有のコード番号でひも付けられます。

この固有のコード番号を「キー」と呼び、キーによって各テーブルはつながります。このような関係で構築されるデータベースを「リレーショナルデータベース(関係データベース)」といいます。(Excelでは実際にひも付けませんが、Accessではひも付けて管理します)

用語を覚える必要はありませんが、基本的なデータベースの考え方を知っておいてください。

さて、では例題11に戻りますが、今回はすべてのデータが入力してありますので、解説にそってデータ値を変更したり、関数の引数を変更してみてください。

今回は、目新しい機能や関数は登場しません。データベースの考え方と、こういった使い方もあるよ、といった参考にしていただければと思います。

まず、「商品管理表」シートは下図のとおり、連番で振った固有の「製品コード」と対応する「製品名」、「原価」、「販売価格」などの製品情報が記入されたシートです。

Excelの「商品管理表」シートのイメージ

したがって、例えば「略称」とか、「値引額」などといった、製品情報が追加されたときには、任意で追加していくことができるようになっています。この表は、固有のコード番号「製品コード」に対応させた属性情報、コードの情報をあらわした表になります。つまり、基本的に大きく中味が変化することはありません。

このように、コード表を元データとして作成しておくと、他のシートではコードのみを活用してデータを蓄積していくことが容易になります。「在庫管理表」シートは、日々の取り引きの状況履歴をあらわしており、このシートにデータが蓄積されていくことになります。

Excelの「在庫管理表」シートのイメージ

上図の「製品名」および「メーカー」は、「商品管理表」シートを参照して、VLOOKUP関数を使用して自動的に表示させています。(VLOOKUP関数について詳しくは、簡単な関数の挿入(2) を参照してください)

VLOOKUP関数をセルに入力しているイメージ

したがって、例えばメーカー名称が変更した場合は、「商品管理表」シートのメーカー名を修正すればこのシートの名称もともなって修正され、二重修正の手間を省略できるようにしています。

この例題で使用している関数は、VLOOKUP関数くらいです。もし在庫管理表に追加表示させたい項目があればは、VLOOKUP関数により「商品管理表」シートを参照して表示させるようにします。

在庫管理表の列の「入庫数量」と「出庫数量」は、任意の値を手入力する列であり、「当月在庫」は「入庫数量-出庫数量」の式を埋め込んでいます。

そして、唯一複雑といえる計算式を埋め込んでいるのが「収支」の列になります。下図は「H4」セルの計算式のイメージですが、どのような式が読み取れるでしょうか?式の記述はずらずらと長いですが、単純な計算式です。

VLOOKUP関数を含む計算式のイメージ

式を読み取ると、「商品管理表」シートの4列目×「F」列の値から「商品管理表」の3列目×「E」列の値を引いた差額を求める式になっています。

つまり、(「販売価格」×「出庫数量」)-(「原価」×「入庫数量」)となり、「もうけ」をあらわしています。ここでもVLOOKUP関数を使っていますが、この場合も金額変更による二重修正の手間を省くためです。「商品管理表」シートの値を変更して、収支が変動することを確認してください。

これで、データベースとして機能させるためのデータ集計の骨子は完成です。このルールにしたがってデータを入力してデータが溜まるとデータベースとなります。

つまり、これら「商品管理表」と「在庫管理表」はデータを蓄積していくための考えられたフォーマットであり、データが蓄積されればデータベースとなります。

また、手入力の箇所(入庫数など)の値を消したり、名称を変更したりすれば、在庫管理表として他の場面に流用することができるはずです。

このように、データを入力する前にのちの利用のことを考えてフォーマットを作成する考え方を身につけておくと、作成したフォーマットは、様々な場面に応用できる「テンプレート」となり得るため、多岐にわたって効率的な運用ができるようになります。

さて、これから先のデータの加工・抽出については様々な方法がありますが、詳しくは次項から解説します。ひとまず、本項ではデータベースを構築する基本的な考え方を身につけてください。

データ加工の一例として、これまで学習した知識を利用すると下図のように加工できます。

ピボットテーブル機能により在庫数を求めたイメージ

ピボットテーブル機能を使って、それぞれバラバラに入力されていたデータをまとめ、商品ごとに当月の在庫数を求めています。

このように、データベースのデータはいろいろな角度から集計・分析されます。ピボットテーブル機能はそれを実現する便利なツールになります。(ピボットテーブルについて詳しくは、レポートの作成 を参照してください)

次に、「請求書」を作成してみましょう。「請求書」はデータベースではありません。最小限の値を手入力するだけで請求書として機能させるためのテンプレートです。

このシートも、少し改変すれば様々な場面で利用できる請求書のテンプレートとなります。テンプレートを作成するということは、あとで利用することを考えるということであり、様々なテンプレートを作成することによってデータの扱い方が身についてくると思います。

では実際に作成してみましょう。例題の「請求書」にも目新しい関数は登場しません(同様にVLOOKUP関数のみ)が、多少の小技を使っています。

まず、一見して違和感を感じた方もあるかと思いますが、セルの枠線を消しています。

これは、黄色に着色した部分だけに値を入力させるために、感覚的にわかりやすくしているという理由と、列や行の幅や高さを細かく調整しているために、枠線があると見づらいというのが理由です。

セルの枠線を消すとすっきりとしてかなり見やすい画面になります。ちなみに、枠線を表示させると下図のように目障りな感じがあります。

セルの枠線を表示させているイメージ

セルの枠線を消すには、「ファイル」タブの「オプション」を選択して、「Excelのオプション」ウィンドウを表示させます。

「ファイル」タブのメニューのイメージ

次に、「Excelのオプション」ウィンドウの「詳細設定」より、スクロールバーを動かして、下図のとおり「表示」の項目の「次のシートで作業するときの詳細設定」の項目の「枠線を表示する」のチェックを外します。

「Excelのオプション」ウィンドウのイメージ

上図のとおり、「請求書」シートが選択されていることを確認してください。これで「請求書」シートからはセルの枠線が消去されます。(他のシートでは表示されていることも確認してください)

次に、下図の「ご請求日」と「お支払期限」の箇所です。

この箇所には「ご請求日」には関数、「お支払期限」には計算式を記述しています。「ご請求日」は本日の日付を表示させる関数「TODAY関数」を記述しています。

セルに「TODAY関数」を記述しているイメージ

TODAY関数は読んで字のごとく、今日の日付を表示させる関数です。このシートを開いているまさに今の日付が表示されるわけです。記述はとても簡単で、「関数の挿入」ウィンドウの「日付/時刻」から「TODAY」関数を選択するだけです。

「関数の挿入」ウィンドウのイメージ

この関数は引数を必要としない(当日の日付を返すのみ)ので、下図のようなメッセージウィンドウが表示されますが、そのまま「OK」をクリックします。

関数の引数がない関数で表示されるメッセージのイメージ

また、単純に「=TODAY()」とセルに直接記述しても構いません。今日の日付がセルに表示されたら、セルの書式設定と表示形式 で解説のとおり、「セルの書式設定」画面の「表示形式」タブより、「日付」のリストのなかから、例題のような和暦の表示形式に変更します。(実際には、「お支払期限」の式を記述してからまとめて変更してください)

「セルの書式設定」ウィンドウの「表示形式」タブのイメージ

そして、「お支払期限」のセルには「ご請求日」の日付、つまり本日から「15日後」の日付を表示させています。

「お支払期限」のセルに入力された数式のイメージ

このように、単純にセルに「+15」で15日後の日付が自動的に表示されます。なぜ、日付に15を足すと計算できるのかというと、実際にはExcelの内部では日付も数値に置き換えられて計算されているからです。つまり、先ほど表示形式を変更したように、変更する前のもとの値が数値としてあるということです。

表示形式を「標準」にもどすと、「平成24年6月4日」は「41064」という値となり、それに「15」足しているわけですから、「平成24年6月19日」は「41079」という値になります。

「セルの書式設定」ウィンドウの「表示形式」タブのイメージ

この値のことを「シリアル値」といいます。シリアル値は日付・時刻をあらわす数値です。シリアル値を計算に使うので、日付同士の計算が可能になるというわけです。

関数のなかには、セルに表示されている値をシリアル値に変換させる関数などがあります。このシリアル値の概念を知っておいてください。

次に、宛名・氏名の箇所です。例題には「鰐淵春樹様」とセルに表示されていますが、実際には下図のとおり、セルには「鰐淵春樹」までが入力されていて「様」は入力されていません。

セルに入力された文字に「様」の文字を自動的付記しているイメージ

つまり、自動的に「様」を付記させているのです。これも同様に「セルの書式設定」の「表示形式」から設定することができます。

ただ、さすがに「様」を自動的に付記するといった形式は用意されていないので、自身で形式を作成する必要があります。任意の形式を作成するには下図のとおり分類のなかから「ユーザー定義」を選択します。

「セルの書式設定」ウィンドウの「表示形式」タブのイメージ

ユーザー定義を選択すると、様々な形式のテンプレートが表示されます。このなかから「@(アットマーク)」を探します。

すると、「種類」のところのテキストボックスが直接編集できるようになります。「@」はセルの値をあらわします。セルの値に「様」を付記するわけですから、上図のように「@" 様"」と直接記述しましょう。

この「""」は、IF関数 のところでも解説しましたが、文字列をあらわしています。「スペース」と「様」を「""」で囲み、セルの値に「スペース」と「様」が付記される形式の完成です。

したがって、「鰐淵 春樹」とセルに入力されたら、セルの値である「@」は「鰐淵 春樹」となり、これに「スペース」と「様」が付記されて、「鰐淵 春樹 様」と表示されるようになります。

以降の箇所は、VLOOKUP関数を用いて、コード番号に対応させて「商品名」と「金額」を自動表示されているだけなので、解説は割愛したいと思います。「明細書」の黄色に着色した部分に適当に商品コードを入力して確認してみてください。

最後に、印刷について少し小技を紹介します。

基本的にこのような形式の請求書は、カラーではなく白黒印刷が多いと思います。また、白黒印刷であっても例題のように黄色でセルを着色してしまうと、その部分がグレー色で印刷されてしまいます。

この着色部分(塗りつぶし)を印刷させないようにする方法を紹介します。

この方法を使うと、例えば入力させたくない箇所を黒く塗りつぶしておく、データの入力時に注意すべき箇所は赤色で強調しておく、などといった使い方ができます。

こういった印刷方法は単純に「白黒印刷」と呼ばれています。(プリンタ側の白黒印刷機能ではないので注意)白黒印刷を指定するには、「ページレイアウト」タブより、「ページ設定」画面を表示させ、「シート」タブの「白黒印刷」にチェックを付けます。

「ページ設定」ウィンドウの「シート」タブのイメージ

すると、基本的にどのようなプリンターで印刷しても白黒文字と、塗りつぶしのない形式で印刷することができます。適当に塗りつぶしてみて、プレビューを確認してみてください。

これで本項での学習は終了です。今回は練習課題は特にありません。例題をテンプレートとして、身の回りにあるもの、例えば、漫画や雑誌、釣り具などの管理表(簡易データベース)を作成し、集計・分析してみてください。

更新履歴

2012年6月5日
ページを公開。
2017年12月29日
ページをSSL化によりHTTPSに対応。

参考文献・ウェブサイト

当ページの作成にあたり、以下の文献およびウェブサイトを参考にさせていただきました。

文献
なし
ウェブサイト
なし