レジのジャーナルから売上集計表をエクセルで作成したい！集計の考え方や注意点Vol.3

PCサポート PCソフトウェア

この記事はだいたい 15 分前後で読めます。

この記事のタイトルとURLをコピーする

目次 Outline

1. ジャーナル情報入力のルール化
2. 次月分シートの準備
3. まとめ

ジャーナル情報入力のルール化

ジャーナル情報から1か月分の集計表を作成するサポートで、日毎の売り上げ金額の合計や、現金残高の計算、部門毎の1か月分の総売り上げや来客人数を求める計算しました。

1か月分の情報を入力し計算するだけの集計表であれば、最初に確認した売上集計表の状態で金額は求めることができているので良いのかもしれませんが、今後集計していく月数が増えることやエクセルの機能を効率的に使うことを考え、計算するセルの範囲の統一や式の統一していくことが大切だと説明をしてきました。

現在の3か月分に加え4月、5月・・・と集計表が増えていくことに備え、ジャーナル情報の集計表への入力ルールを考えたいと思います。入力のルール化に伴い当月の営業日数も計数する方法も追加します。

Vol.1のサポートでは部門１から部門12と、総売り上げ、割引・電子決済・現金・人数の平均を求める計算で、集計表の中に営業日数を表すセルがなかったこともあり、直接 25 という営業日数で除算するという数式を入力しました。

集計表のどこかに営業日数を数えて25日間だということを入力し、その日数でそれぞれの合計を除算するという方法も考えられますが、せっかくなのでエクセルの機能、つまりは関数を用いて営業日数を求めたいのです。

店休日は金額・人数を入力しない

店舗が休業日の場合に部門毎の売り上げ金額・割引・電子決済・人数の列に、数値として０が入力されていました。

確かに売り上げは０ですし、来客人数も０なので間違った入力ではないのですが、入力の０は営業日にもかかわらず売上が０なのか、お休みで０なのか判断できません。

営業日と店休日とを明確に分けるために、店休日のジャーナルの情報入力時に、「金額の入力は行わず空白のままとする（０数値入力をしない、文字列も入力しない）」とルールを定めたいと思います。

合計や現金残高といった計算で求めるセルに対しても０が入力されていたので、計算で求めるセルに対しては0を入力するのではなく合計を計算するように計算式を入力するように変更します。

集計シートの1月分を確認すると０が入力されている1日・2日・6日・14日・20日・27日は店休日と考えられるので、部門１から部門12、割引・電子決済・人数のセルに何も入れない空白状態とします。

合計・現金・累計列は共通化した合計を求める計算式式を入力、合計・現金ともに０となり、累計金額は前日と同額の金額が表示されます。

合計金額０や累計金額が表示されていても計算で求めた結果が表示されている部分は数式を消すことなく残しています。これは1月分のシートを使って次月用を作成する際に活用することを考えています。

このように定めたルールに従い、店休日には該当する金額・人数セルには入力しないで空白状態態にすることで、部門ごとの売り上げ金額・来店人数のセルについて営業日と店休日を判断しやすくなります。

このルール化は1例で、店休日は情報を入力しないとしましたが、日付か曜日列の隣に別途店休日がわかるように【休】といった文字列を入力するとかで識別する等他にも識別する方法はあると思います。

人数列を使って営業日数を求める

エクセルの COUNT() 関数は引数に、数値の個数を数えたいセルの範囲を指定することで、その指定範囲に存在する数値の個数を数えてくれる関数です。

このCOUNT() 関数を使って、人数が入力されているセル：営業日、セルが空白：店休日とルール化によって分かれた T列：人数 の部分で営業日数を計数します。

ルール化により部門・割引・電子決済の列いずれにおいても求めることができますが、今回は右端列に位置する人数列で計算したいと思います。

営業日数を計算する式をU33セルで計算します ＝COUNT（T3：T33）

上記式は「T3のセルからT33のセルの範囲において、セル内に数値が入力されている個数を求めてください」という意味になります。

COUNT()関数を使うときに、数値を数えようとしているのセルの範囲はどこなのかを意識して使うようにしてください。

セル内の情報とは

セル内の状態は大きく3つにわけられます。

何も入力されていない空白の状態
数値が入力されている状態
文字列が入力されている状態

ルール化により人数の列では、営業日は人数が入力され店休日はセルが空白なので、営業日数として数えるのは人数の数値が入力されたセルとなります。

計算の結果は25という値が求められます。この求めた値を営業日数として平均を求める計算を行います

部門１での営業日平均は＝C34/U33 （部門1の1か月分の合計を今回求めた営業日数で除算する）

と、入力しセルのフィルハンドルをドラッグしてコピーしてしまうと、コピーの時に割り算の除数のセルの位置がずれてしまい正しく計算されません。今回営業日数を求めたのはU33セル1か所ということで、除数となるセルの位置参照を固定する必要があります。

＝C34/$U$33 （部門1の1か月分の合計を今回求めた営業日数のセルの参照を固定して除算する）

数式内で除数となるU33の参照方法を絶対参照で入力し、フィルハンドルでドラッグし数式コピーで求めることができます。

営業日数セルを使わずに計算

営業日数を表すセルを別途設けた上で計算しましたが、平均を求めるセル内でCOUNT（）関数を使い計算することも可能です。

＝C34/COUNT(T3:T33)　で計算できますがこのままでは不十分で、数式をコピーすることを考慮し、
＝C34/COUNT($T$3:$T$33)　と、人数列の数値の個数を計算するセルの範囲を絶対参照する必要があります。

このように入力して計算しても同じ結果を得ることができます。営業日数を可視化するか数式内に入れてしまうかは選べますが、可視化しておいたほうが見た目としてはわかりやすいと思います。

次月分シートの準備

1月のシートで行った計算式の設定や、店休日識別のためのルール化に則り入力された０の削除、営業日数の算出といった処理を2月と3月のシートにも行います。

2月分シート確認

2月分をみてみます。2月1日はお休みだったのでしょうか、すべて空白になっていました。ただ7日と21日がお休みだったのか0が入力されています。何も入力しない場合と０を入力している場合のようにいわゆる入力の「ゆらぎ」は気になるところです。

累計のセルは1日毎に計算式を入力していたようで、合計は部門の縦方向の合計となっていますが2月1日のセルが除外されていました。

平均は合計の金額を25で除算するという数式が部門ごとに入力していたようで結果は間違いではないのですが、この31行目での部門１から部門12の合計計算は縦方向のセルの範囲で計算しているのに、O列の合計だけ、部門１から部門12の合計を横方向に合計した計算式になっているため縦方向で計算するのか、横方向で計算するのかの相違をエクセルがチェックして注意を促しています。

いわゆる合計の合計は縦方向で合計しても横方向で合計しても同一の結果となりますが、1月の集計表での計算と2月シートでの計算が異なることは避けたいところです。統一化を図る意味で同じ縦方向での合計をする計算で求めたいと思います。

計算式を入力する際に2月1日から28日までの計算を行うように変更します。また店休日にあたる部門の金額等はすべて削除し空白状態に整えていきます。