エクセルで月ごとの売上推移を管理する

　売上の管理をするとき、伝票がたくさんあると煩わしくなり、手間も時間もかかってしまいます。また、手入力が多いと入力間違いも起きやすくなります。

　そこで、日付関数とピボットテーブルを使い、月ごとの推移がわかりやすい売上の管理表を作ってみましょう。最初にかかる手間はありますが、毎月同じファイルを使って月ごとの推移を出すことができるため、後からまとめ直す必要がなくなります。

１）まずはデータを抽出するための準備をする

　最初に、たくさんのデータがあると画面スクロールしたときに項目が見えなくなってしまいますので、項目名が常に確認できるよう表示を変更しておきましょう。

　項目名のすぐ下のセル（A2）を選択してから「表示」タブ（メニュー）をクリックし、「ウインドウ枠の固定」を選択します。これで、項目名が常に表示されるようになりました。

　次に、売上日の書式設定をします。これにより、例えば日付を「4/1」と入力した場合も、表示を「４月１日」とすることができます。これにより、毎回「●月●日」と入力する手間が省けます。

　あらかじめ書式設定をしておけば、列で表示を揃えることができるため整ったデータが作れます。多くの日付を入力する際には、このように書式設定を活用します。

　さらにこの表では、売上日の隣に、月ごとにまとめるための列を追加しました。この列は後から集計をするために必要な列となります。ここに、日付から「月」を抽出する式を入力しておきます。

式：=MONTH(A2)&”月”

　関数「MONTH」を使うと、A列に入っている日付から月を抜き出して表示させることができます。同様に、「YEAR」関数なら「年」を、「DAY」関数なら「日」を表示できます。

　&”月”は、［日付から抽出した数字の後ろに、「月」と文字を付け加える］という意味です。このようにダブルクオーテーションで文字列をはさみ、数式と「&」でつなげれば、数式の結果と文字列を同じセルに表示させることができます。これにより月の列には、「4月」、「5月」と表示されます。

　　１年以上同じシートを使う可能性がある場合には、「=YEAR(A2)&”年”&MONTH(A2)&”月”」としておき、年と月を表示させるようにしておくと便利です。

　もし、A列にデータを入力した時だけB列に月を表示させたい、という場合には、以下のように「IF」関数で条件付けをします。

式：=IF(A2=””,””,MONTH(A2)&”月”)

２）月ごとのまとめを作成する

　次に、月ごとのまとめを作成し、見やすく整理していきます。先ほど作った表を使って月の項目をキーに並べ替えをして、その月の分だけを新しいシートにコピーしても良いのですが、毎月新しいシートを作成したり、コピーしたりする手間が生じてしまいます。さらに、日によって件数のばらつきが出るため、一つの表として見づらくなってしまいます。そこで、日ごとの件数と金額をまとめて表示できる表を作ります。

a)日付を自動入力させる

　まず、新しいシート「4月」を作成します。A1とC1に年と月を入れれば、自動でその月のカレンダーを入力できるように、関数を使って日付欄を作成しましょう。

　ここでは日付関数「DATE（）」を使います

式：＝DATE(A1,C1,1)　【A1は年、C1は月、１は日を示します】

　A1は年、C1は月、１は日を意味します。これでC1に「4」を入力すると4月1日が表示されます。

　2日から28日までは、1日ごとに増えていけば問題ありません。2日の欄に下の式を入力します。

式：=E4+1

　この式を28日の欄まで下方向にコピーすれば、連続した日付を表示できます。

　次に、29日から31日までの日付を表示させます。2月は年によって29日までだったり、4月は30日まで、5月は31日までと、月の日数は毎月変わります。それに対応させるため、数式を組み込んでおきます。

　ここでは、月末を計算する関数「EOMONTH(開始日, 月)」を使います。EOMONTHとは「End Of MONTH」の略で、月の末日を計算するための関数です。

　この表での「開始日」は、2016年4月1日（E4）です。月の部分は、当月の月末を意味する[0]を代入します。

　ただし、28日の下にこの式を入れると、4月30日が表示されてしまいます。そこでif構文を組み合わせます。

●28日のすぐ下の欄

式：=IF(E31=EOMONTH(E4,0),””,E31+1)

　28日が月末であれば空白を、そうでなければ28+1日と表示させます。

●30日／31日の欄

式：=IF(E32=””,””,IF(E32=EOMONTH(E4,0),””,E32+1))

　もし28日が月末で、29日のところが空欄であれば空欄にします。29日がない月は、30日もないからです。さらに、29日が月の末日の場合は空欄にする必要があるため、「IF」関数を追加して条件付けをします。最後に、29日の欄が空欄でなく、同時に29日が月の末日でない場合には「E32+1」で1日足します。

　31日の欄は、30日の欄をコピーするだけで対応できます。この式で、どの月にも対応できるようになりました。

　これで、最初に年と月を入れるだけで、１か月の日付を自動で表示できるようになりました。この表では、4月なので、31日の部分が空白になります。

　5月のシートも、カレンダー通り31日まで表示されます。

ｂ）データをまとめる

　次に日付ごとの受注件数をまとめます。まずは、受注日が「4月1日」に合致するデータの数を数えます。

式：=COUNTIF(売上元帳!$A$2:$A$500,’4月’!E4)

　売上元帳シートにある「売り上げ日」の中から、4月1日の日付のあるものの個数を表示させる数式です。入力途中のデータもあるので、今回は500行までを参照範囲としましたが、「＄A:$A」としてA列をすべて選択しておけば、後から数値を変更しなくても済みます。この時、コピーしても参照元の参照範囲がずれないよう、行列の前に「＄」を入れておくと便利です。セルを選択した後に「F4」キーを押すことでも「＄」を入力できます。

　次に、１日ごとの売上金額をまとめます。条件に一致したデータの合計を出す関数「SUMIF(範囲, 検索条件, [合計範囲])」を使います。

　今回の場合は、以下の数式になります。

式：=SUMIF(売上元帳!$A$2:$A$500,’4月’!E4,売上元帳!$H$2:$H$500)

　売上元帳の売上日の中で、4月1日に合致した受注の金額の合計を表示します。これにより、その日の売上が何件だとしても、合計を表示することができます。

C)月ごとの詳細を集計する

　商品ごとの売り上げ実績をまとめるには、ピボットテーブルを使います。表を作成したいセルをクリックし、「挿入」タブ→ピボットテーブルを選択します。

　分析するデータは、売上元帳のデータの範囲を、項目も含めて選択します。行は余裕を持たせるため、ここでは500としておきます。入力が終わったら、OKを選択します。

　これで、セルの位置に表が作成されます。右側に表示されるナビゲーションで、表示させる項目を選んでいきます。

　フィルターに「月」を、行に「商品名」を、値には「販売台数」と「売上金額」をドラッグします。この時、表の値は個数で表示されますが、知りたいのはそれぞれの「合計」なので、表示を変更します。

　ナビゲーション画面上で「売上金額」を選択すると、メニューが表示されます。ここで「値フィールドの設定」を選択すると、ダイアログボックスが開きます。選択したフィールドのデータを「合計」に変更すれば、データの個数ではなく合計値が表示できます。

　最後に、4月のデータだけを表示させるようフィルター設定をします。「月」の隣に表示されている▼ボタンをクリックし、「4月」を選択すれば完成です。

　これで、4月のデータができあがりました。この表は編集できるので、行ラベルを「商品名」と変更しておきます。

　同じ手順で、5月、6月……と、順次作成していきます。

３）通年のまとめを作成する

　年全体の推移も、ピボットテーブルを使えば簡単に作ることができます。毎月の場合と同じ手順で表を作っていき、「月」の項目を列に入れるだけで完成します。

　ピボットテーブルでも、普通の表と同じように罫線やセルの結合といった作業ができます。より見栄えを良くしたいという場合は、罫線の設定などを変更します。

　この表では、データの入っていない空白の部分も集計の対象になってしまっています。範囲指定を変更すれば良いのですが、日々増えていくデータにあわせて設定し直すのも面倒です。

　そこで、月の部分の▼ボタンを選択し、フィルターをかけておくことで空白を回避できます。空白の項目だけ、チェックボックスを外しておけば完成です。

　数式が多いため、一見すると手間がかかるように思えるかもしれません。しかし、一度シートを作成しておけば、日々の入力だけで集計までが完了します。報告や資料の作成も簡単にできるようになるため、ぜひ一度作ってみてください。