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

売上集計表サポートVol.2

この集計表サポートの内容は前回の「集計の考え方や注意点Vol.1」の続きで、前回のサポートでは説明しきれていない内容や課題についての説明となります。

関連記事

レジのジャーナルから売上集計表をエクセルで作成したい！集計の考え方や注意点Vol.1 売上集計表作成サポート レジのジャーナルとして出てくる日々の売り上げ情報をエクセルで集計したいと、雑貨小物の販売を……

計算式入力でのミスを減らすには

エクセルの集計表等で合計や平均を求める計算式を入力する際に、同じような計算をするセルに対して毎回数式を入力したりしていませんか？

エクセルに計算式を入力すれば基本的な加減乗除といった四則演算だけでなく、関数を用いることで様々な計算を行うことが可能です。

とはいえ、計算結果を求めたいセルに一つずつ数式を入力しないように心がけることが大事だと思っています。

前回の集計表を作成するVol.1で作成されたエクセルの集計表を確認した際に、合計したいセルの範囲が統一されていなかったり、平均を求めるためにセルの番地を使って計算すべきところを、計算の結果の数値をそれぞれ入力してしったりといった部分が見受けられました。

このような計算をしたいセルに1つずつ数式を入力するといった手間を増やしてしまうと、入力のミスにつながり結果として、正しい集計結果を得ることができない可能性が高くなります。

１つずつ数式を入力することで、その時点での求めたい結果を得ることはできるかもしれませんが、せっかくの便利なエクセルの恩恵を受けるためにも数式の組み立て方と数式のコピーで間違いを減らす方法を考えていただきたいと思います。

1日分の合計・現金・累計計算

1月分の集計で縦方向の計算については、エラーインジケーターや数値を使った計算について数式を入力し、フィルハンドルのドラッグで数式のコピーを行う方法を説明しました。

一方横方向の計算を行っていたO列：合計、R列：現金、S列：累計に関しては、1日毎に数式を入力していると思いながら、数式のコピーについては触れないままでした。

これは1月分の集計表において合計・現金・累計の部分は計算で求めるセルだと考えるべきところ、入力されたシートでは1日、2日、6日・・・といった店休日にあたる日付の売り上げ金額を入力する際に合計・現金・累計部分にも0が入力されていたからです。

確かに店休日であれば、部門１から部門12の売り上げ金額は０ですから、合計や現金、人数は0となります。累計については該当日は売上はなくともその前日までは売上累計があるので、どのように表示すべきか考慮が必要ですがシートでは０となっていました。

O列の合計だけに着目した場合、この合計は該当日の部門1から部門12の売り上げの合計を計算するセルと考えられ、休みだから０を入力してしまうと、このO列の合計で1日から31日までのセルに計算式が入っているセルと０が入っているセルが混在することになります。

同じような計算をするところでは統一しておくことが大事だからという理由で、前回34行目・35行目の数式のコピーの説明をしたわけですから、今回も同様の考え方で進めていきます。

O列：合計

部門１から部門12のセル範囲を合計します、ただし初商いの3日での計算ではなく1月1日の合計を計算します。

＝SUM（C3：N3）

数式を1か所入力したら、そのセルを選択しフィルハンドルを31日の合計部分までドラッグして式のコピーを行います。

R列：現金

O列の合計と同様に1月1日の現金セルで計算を行います。

＝SUM（O3：Q3）

現金列についても数式を1か所入力したら、そのセルを選択しフィルハンドルを31日の現金部分までドラッグして式のコピーを行います。

S列：累計

累計を求めるセルで入力されたそれぞれの数式を確認してみます。この累計についても初商いの1月3日から計算しているのようです。

1月3日は =SUM(R5)、1月4日は =SUM(R5:R6), 1月5日は =SUM(R5:R7),とつづき、1月30日は =SUM(R5:R33)、1月31日は =SUM(R4:R34)と累計計算の基準となるR5のセルは共通で、合算する最後のセルの位置だけが変わっている数式となっています。

1月の31日はR4となっていますが、R5のセルを選択せずR4を選んでしまったようです。店休日として現金の残高が0と入力されているセルなので結果には影響を及ぼしていませんが、こちらも範囲の統一性という面からすればミスの原因になる可能性が高まります。

このような計算式の不統一・整合性不足といったミスを避けるためにも計算式を組み立てることが必要となってくるのです。

ではこの累計の計算を考えてみましょう。この累計も1月1日を起点としてそれぞれの累計を計算します。

入力した数式は ＝SUM（R3：R3）数式内の引数がR3：R3となっており、1月1日の現金の合計から1月1日の現金合計のセルまでという考え方ですが、この場合同一セル番地なので意味が分かりにくいかもしれないので1月2日の累計を計算するセルで考えてみましょう。

入力した数式が ＝SUM（R3：R４）となって数式の引数がR3：R４と1月1日の現金と1月2日の現金を加算する計算式となります。

式を入力したので、数式のコピーを行ってみます。

数式のコピーはできて計算は事項しているようですが、1月31日の累計金額が58,910となっています。現金の1月分の合計はR34のセルで計算されていますが、813,475円となるはずです。計算式を確認してみます。

コピーされた数式が ＝SUM（R32：R33）となっていて、前日にあたる1月30日の現金と1月31日の現金を加算しているだけで、1月の頭からの現金を計算していないことがわかります。

式のコピーでのセルの参照

式をコピーする場合、数式での指定を行っていなければ「相対参照」というセルの参照方式に従って数式をコピーします。今回の累計計算の場合は合計する最初のセルR3を基準として計算を行いたいので、今回はセルの番地を固定する、「絶対参照」というセルの指定方法を使って計算する必要があります。

1月1日の累計を計算するセルで ＝SUM（$R$3：R3）と入力します。＄マークを付けて入力したセルの番地が固定されます。エクセルの説明等ではよくF4キーを押下すると＄マークがつくと記述されているかと思います。

式を入力した後、式のコピーを実行します。

1月31日の累計が現金残高合計と合致しているのがわかると思います。今回累計の計算では絶対参照でセルを指定して計算することで実現できました。

まとめ

エクセルで計算式を入力する際に、コピーして貼り付けとかフィルハンドルでドラッグすれば数式のコピーができますよといった説明を受けたり、エクセルの解説書の記載を目にすることが多く、その方法に従って式をコピーすることが多いと思います。

本来数式のコピーでは相対参照・絶対参照といった、エクセルの数式コピー時の特性を含めた理解が必要なのですが、絶対参照が必要な場面に遭遇することなく作表を行うことが多いためか今回のような絶対参照を知らない・使わないままの方が多いことも事実です。

そのため、本来1か所に数式を入力しその式をコピーするだけで解決するはずの計算を、一か所ずつ入力してしまうといった「手間」をかけてしまうことが増えるようです。

今回も前回と同様数式の組み立て方や、数式コピーについては様々な方法があり、今回のサポートではエクセルの使い方の一例としてお伝えしている点はご了承ください。

手間をかけて計算し、計算の結果が出ていることは大事なことかもしれませんが、その「手間」の中に潜むミスの可能性を見過ごすわけにはいきません。せっかくエクセルという便利なツールを使うわけですから特性を知りそれを使っていくことを目指したいものです。

このサポート説明でメインとなったのはやはり累計部分で、セルの参照方式が複数あり、その参照方式を用いて式を構成・入力することで式のコピーで効率よく集計表を作成できるということです。

このジャーナル集計表では前回と今回の説明で数式の統一を行い、セル参照の不一致といた不整合な部分を取り除くことができました。あとは店休日の入力の考え方を改め、前回営業日数が25日だからと入力してしまった部分の解決を次回の説明で行いたいと思います。

関連記事

レジのジャーナルから売上集計表をエクセルで作成したい！集計の考え方や注意点Vol.3 ジャーナル情報入力のルール化 ジャーナル情報から1か月分の集計表を作成するサポートで、日毎の売り上げ金額の合計や、……

また、相対参照・絶対参照については別途説明する機会を設けたいと思っています。