レジジャーナル集計表サポート【日付・曜日処理数式追加・条件付書式設定・シートの保護】

年度用シート作成に備える

レジジャーナル集計の1か月分の作成で、1か月分のジャーナル情報の入力が終われば次月のシートを準備して入力に備えます。

1か月分の売り上げを入力したシートは、シートのコピーで作成できますが、ジャーナル情報が入力されているシートをコピーした場合、次月分の情報を入力できるように下準備が必要です。

入力する月の年月を設定し、カレンダー部の曜日の入れ直しが必要になります。それだけでなく売上金額、割引金額、電子決済分、来店人数といったジャーナル情報を入力しているので、その入力値の削除をしなければなりません。

日付と曜日処理の手間を省くには

年月の設定と曜日設定では、当月の1日が何曜日なのかを確認し、2日以降の曜日入力処理も集計シートをコピーする度に手入力することも気になりました。

もちろんその月の1日の曜日を入力し、その入力したセルのフィルハンドルのドラッグで七曜を入力することはできますが、せっかくエクセルを使っているので年月を入力すれば、該当月のカレンダー部分が表示されるようにしたいと思ったところです。

カレンダー部についてはさらに、土曜日と日曜日の日付・曜日の書式設定で青色・赤色に設定し週のイメージがわかりやすくなるように設定します。

エラー表示を抑制したい

また、ジャーナル情報入力した値をシートのコピー後削除した場合に、部門ごとの合計を営業日数で除算して平均を求めているセルがありました。

計算に使用する営業日数のセルの数式は、来店人数が入力されているセルの数を計数しています。
その来店人数の入力セルの内容を削除すると、営業日数が０になってしまい、平均の計算で#div/0!のエラーが表示されてしまいます。

エクセルに慣れていない人は、シート状でエラーの表示を見ると、すぐ【削除】してしまうようなので、エラーとなる部分は非表示にします。

数値が入力されているセルの値をまとめて削除する点については、VBA等を組み込めばボタンクリックで削除という方法もありますが、今回はVBAの組み込みなどは行わず、範囲を選択して手動で削除する方向で考えます。

入力した数式を保護したい

また削除の際に、今回処理を行うカレンダー部分や合計、平均、累計など計算式を入力している部分と、日々のジャーナル情報を入力しているセルとを区別し、数式入力部分は単純に消すことができないように設定します。

売上表の中で売上金額から値引き分、電子決済分を減算してレジの現金金額を求める部分がありました。この計算で値引き分と電子決済分はマイナスの値で入力していたのですが、項目名に値引き・電子決済とあるのであえてマイナス値を入力するのではなく値引基金額、電子決済金額を入力し、売上金額から減算するように変更します。

出来上がりシートイメージを準備しました。試されたい方はこちらからダウンロードしてご確認ください。

カレンダー部を作る

年・月入力と書式設定

今まで”2025年1月”と文字列で入力していましたが、シート上部1行目に西暦年を入力セルA1と何月かを入力するセルC1のセルとに分けて設定します。

西暦年2025,2026,2027と年度に応じて入力し、月は１,2,3,4,…10,11,12を入力することで、日付と曜日が決まるのでその数式設定を行います。

ここでセルには【2025】と西暦年の数値を入れますが、セル書式設定から表示形式のユーザー定義で【2025年】と表示するように設定を追加しました。

ユーザー定義で数値に続けて表示する【年】を”年”と文字列を表示するようにしています。

同様に月入力セルも数値入力で、1月、2月、3月と表示するようにセルの書式設定の表示形式を設定します。

DATE関数活用

年・月・日という3つの値からシリアル値を作成するDATE関数を使います。DATE関数については以前説明をしているのでこちらを参考にしてください。

関連記事

エクセルで日付の計算を関数で行う。 date関数を使い年・月・日の値を指定して求める 日付を処理する関数 エクセルには、合計を求める、平均を求める、最大値・最小値を求めるといった様々な計算結果を求める……

カレンダー部の1日を表示するA3セルで【 =DATE( A1, C1, １）】と数式を入力します。　

指定した年、月と月の最初の日の意味で日は１としています。この3つの引数からシリアル値が求められ、日付が表示されます。わかりやすくするため西暦年/月/日で表示するように設定して、セルの幅を広げています。

翌日の2日は【 =Date(A1, C1, 2) 】と数式で求めることができます。この場合A1,C1については1日の数式を作成した際に絶対参照し、日の引数部分だけ入力しなおすことで処理はできるものの、日毎に数式内の引数【日】を2, 3, 4,・・・と値を変えなければなりません。

これでは手間がかかってしまいます。そこで、1日の次の日という計算を行う意味で、　【 =A3+1 】と数式を設定します。【＋１】というのはシリアル値を1増加させるということで、1日加算されることとなり計算すると翌日が求められることになります。

末日となる31日の日付部分まで数式のコピーを行います。日付のみを表示するために、表示形式のユーザ定義で【 d 】と設定します。

曜日についてはA列のシリアル値をつかって曜日を表示できます。そこで曜日部分は日付のセルの値をそのまま使い、 1日の曜日は【 =A3 】、2日の曜日は【 =A4 】と続き、28日の曜日【 =A30 】、29日の曜日【 =A31 】、30日の曜日【 =A32 】、31日の曜日【 =A33 】と数式をコピーします。

そして、曜日を表示させるために、表示形式のユーザ定義で【 aaa 】と設定します。

月末日付の考察

ここまでの数式設定で、31日までの日付と曜日が表示されることになるのですが、月を2月に変えてみましょう。2025年2月は28日までです。わかりやすくするため、日付と曜日だけでなく【 西暦年/月/日 】の表示にしています。

2月28日の次が3月1日、3月2日、3月3日と表示されています。

このように日付の計算を行い、処理をするのがエクセルの特性ともいえる部分です。

1月の日付の表示では、1月28日に＋１日で1月29日となり、さらに＋１日で1月30日となっていましたが、2月の計算では2月28日に＋１日で、2025年はうるう年ではないので2月29日とならず、2月28日の翌日は3月1日となります。そして3月1日に＋１日で、3月2日と計算が行われます。

比較として、昨年2024年はうるう年だったので、2024年2月の指定で日付を確認してみます。

2024年2月28日、2024年2月29日、2024年3月1日、2024年3月2日と、エクセルはうるう年のことを内部的に把握しているので、特に意識することなく期待したとおりの計算結果を表示してくれます。

月毎の末日設定

ここで2025年の2月は28日までだからといって、表示されている3月の日付と曜日の削除は行いません。数式で対応していきます。

1月だけでなく、3月や5月といった大の月は31日まで、そして4月、6月といった小の月は30日まで、2月はうるう年のときに29日までとなります。

しかし、大の月か小の月かといった条件や、うるう年かそうでないかといったことまで考えて処理することを考えてしまうと条件が複雑になってしまうので、処理する条件を違う面から考えてみたいと思います。

まず、28日までは大小の月、うるう年にかかわらず必ず存在するので、日付の末日の処理を行う必要があるのは29日、30日、31日の部分となります。

月が変わることに着目

29日の日付を表示するセルで条件を考えていきます。IF関数を用いるわけですが、年・月を入力し月末にあたる日付の表示を思い出してみましょう。

1月を2月に変更したとき、2月28日の次は3月1日となりました。2月28日に＋１と数式で求めた際に3月に月が変化しました。この3月に変わったことを利用して数式を考えてみたいと思います。

C1のセルで指定した月の29日を表示するセルで、その日付が指定した月のままであれば、その日を表示するが、月が変わったら【表示しない】もしくは、【 “—” 】といった文字列を表示する数式を組み立てていきます。

29日を表示するセルはもともと28日のセルに＋1日する、【 =A30+1 】といった計算を行っています。これはシリアル値となるため、このシリアル値から月の情報をMONTH関数で取り出します。

シリアル値から年・月・日の情報を求める方法について、以前説明をしているのでこちらを参考にしてください。

関連記事

エクセルで日付情報（シリアル値）から関数を使い西暦年・月・日の３つの値を取得するには 日付け関数の活用 仕事でコンピュータを使うのが当たり前になってきて、Officeソフトの文書作成プロプログラムのW……

ここで求める月の情報と、シート上部に設けた月を入力したセルと比較します。

【 = IF( MONTH(A30+1) = C1, A30+1, “—“) 】

この数式を入力することで、29日にあたるセルの処理ができ結果はこのようになります。

同じように数式を考えていきます。28日のセルを基準に考え、2日後、3日後と考えてそれぞれ計算を行うので

30日を表示するセルは【 = IF(MONTH(A30+2) = C1, A30+2, “—“) 】
31日を表示するセルは【 = IF(MONTH(A30+3) = C1, A30+3, “—“) 】

曜日による条件付書式設定

条件付書式を設定した後で、数式を説明する画像を準備しているため、説明用の図では日付と曜日が土曜日は青、日曜日は赤で表示するようになっています。

今回WEEKDAY関数を用いて、日付部分と曜日部分に条件付書式設定を行っています。

日付のセルを選択し、条件付き書式設定に進み【数式を使用して、書式設定するセルを決定】を選択し、そこで数式を入力します。

WEEKDAY関数はシリアル値を引数とし、そのシリアル値が日曜日にあたる場合は１を返し、土曜日にあたる場合は7を返します。

日曜日の設定

【 =WEEKDAY(A3)=1 】と数式を入力し、書式で太字に変更し色を赤にしています。

土曜日の設定

【 =WEEKDAY(A3)=7 】と数式を入力し、書式で太字に変更し色を青にしています。

日曜日と土曜日の数式、書式を設定した後で、その条件付書式設定を適用するセルの範囲を指定【 =$A$3:$B$33 】しています。

関連記事

エクセルで入力した日付と一緒に曜日を表示したい！セルの表示形式変更かTEXT関数使用 エクセル（Excel）入力時の日付と曜日 仕事で使うことが何かと多い、Microsoftのオフィスアプリケーション……

関連記事

エクセルで入力した日付と曜日の表示ができた！さらに土曜日を青色・日曜日を赤色に変える 曜日に色を付けたい エクセルの作表において【日付】の持つ意味合いは大きく、特に西暦や元号での表示方法について考慮す……

エラー表示の回避

来店した人数のセルを削除すると、営業日数が０となり、35行目の平均を求めているセルにエラーが表示されてしまうことを回避していきます。

営業日数が０になることでエラー表示となってしまうことについては、シート自体の設定でエラー表示を行わないようにするという手法もとれますが、今回は数式を使いエラー表示されないようにします。

条件としてはシートをコピーした後で来店人数のセルを削除することにより、営業日数が【０】の場合には、平均を計算するセルには何も表示しないよう設定。営業日数が求められていれば平均を計算するように数式を組み立てます。

平均を求めるのは、部門1～12、合計や割引といった金額、来店人数の平均を求めることになるので、計算に使用する営業日数のセルを絶対参照し【 = IF($U$33=0, “”, C34/$U$33) 】と数式を入力しました。

現金の計算法を変更

割引と電子決済の金額をマイナス値で入力するのではなく、割引金額と電子決済の金額を入力することでレジの現金が求められるように変更しました。

1日の現金を求めるセルで【 = O3 – P3 – Q3 】と入力し、数式をコピーしています。

数式の保護

今回は西暦年と月を入力することで大小の月や、うるう年を考慮した日付表示や曜日の表示の数式を追加しました。また合計、現金、累計、営業日数、平均といったジャーナル情報を入力することによって計算するようにセルに数式を設定しています。

シートをコピーし、次月のジャーナル入力のために金額等を削除する際に、数式が入力されたセルを削除しないように気を付ければよいのですが、間違って消してしまうなんてこともあるかもしれません。

そのようなことを防ぐために、数式を入力した部分を誤って削除しないように保護することができます。ジャーナル情報を入力するところと、数式が入力されたところをエクセルで区別します。

入力するセルのロックを外す

区別するために、ジャーナルの情報を入力するセルを選択します。【 C3からN33, P3からQ33，T3からT33 】

セルの書式設定を表示し、ロックを外します。初期状態ではすべてのセルの保護状態は【ロック】となっています。

エクセルのリボンから【校閲】を選択し【シートの保護】をクリックします。

シートの保護のダイアログが表示されたら【OK】をクリックします。この時パスワードを入力することで、保護解除にパスワードが必要な状態にすることもできます。

シートの保護をすることで、ロックを外したセルの範囲は入力ができ、【ロックを外していないセル】つまり、ロックを外さなかったセルについては保護が働き、編集、削除などできなくなります。

数式の変更や、追加などが必要になった場合は、【校閲】を選択し、【シート保護の解除】をクリックすることで、編集・削除ができるようになります。

まとめ

前回3回に分けてジャーナル情報の入力シートの作成のサポート内容を説明していましたが、その後のサポートでシートコピー時の日付・曜日設定の手間と、計算式によってはエラーが表示されてしまう部分がどうしても気になったため数式設定を追加することになりました。

日付と曜日の処理も行ったことで、シートをコピーして次月の日付・曜日設定も楽になり、年度分のジャーナル情報を入力することについてはほぼできたと思っています。

手つかずの部分は、前年同月の売り上げとの比較です。サポート開始時では前年分のジャーナル情報入力の形ができておらず、売上金額の集計などが手入力だったりと計算で求められていない部分が多かったため、処理ができていません。

構想としては、年度分の12か月分のジャーナル集計は今回作成したシートを使用し、1年分をまとめるシートを追加してその年度の総合計がわかるようにしたいと思っています。

1年分の集計シートを作ることで、年度毎のブックが作られていく形となり、前年度分の情報があれば、その前年度の情報を取得して計算を行うことができます。

エクセルには様々な関数、設定などがあり、今回説明した数式・設定以外でも処理を行うことができると思います。「この方法でなければいけない」というのではなく、使い方の一例として参考にしていただければ幸いです。