月を指定すると、1日から月末まで日付を自動表示してくれるスケジュール表をつくる方法

スプレッドシートをつかった処理はいろいろありますが、なかでも日付に関連する処理は多いですよね。

今回はよく作成するであろう、スケジュール表で役に立つ処理をご紹介します。

こんな人におすすめ!
  • 1ヶ月ごとのスケジュール表やカレンダーを作っているが、毎月日付を手動で更新するのが面倒。
  • 月を指定するだけで、その月の日次の売上報告書を自動作成したい。

1日から月末までの日付を自動表示させる方法の解説

横軸がスタッフ、縦軸が日付になっている売上報告書を作るケースで解説します。

1トリガーとなる月選択のプルダウンリストをつくる

最初に、自動表示のトリガーとなる月指定の仕組みを作ります。
今回は「データの入力規則」をつかって、プルダウンリストから月を選択する方式を採用します。

まず、別シートに毎月1日のリストを作成します。

その後、売上報告書のシートに戻り、プルダウンリストを設置したいセルを選択した状態で

「データ」>「データの入力規則」

と進みます。

このとき、データ範囲を「’月リスト’!A:A」と範囲指定しておくと、あとでリストに項目を追加したときにわざわざデータ範囲を修正する手間がなくなります。

ポイント!
  • プルダウンリストのリスト範囲は、あとで項目が増える可能性を考慮し、行全体を範囲指定しておく。

これで、トリガーとなるプルダウンリストの設置は完了です。

21日の数式を記述する

1日は、さきほど作ったプルダウンリストのセルを指定します。

=[プルダウンリスト]

これで、プルダウンリストを変えると、それに連動して1日目の日付も自動で変わるようになりました。

32日から28日までは、前日の日付に1を足す

2日から28日までは、前日の日付に対して、シンプルに1を足していく処理をします。

=[前日]+1

429日の処理

さて、ここから少し難しくなっていきます。
というのも、2月は28日までしかないので、先程と同じように前日の日付に+1する数式を入れてしまうと、3月1日が表示されてしまいます。

したがって、ここでは

  • 直前の日付が月末の場合は、空白
  • そうではない場合は、1を足す

というIF関数を組みます。

また、月末の表現はEOMONTH関数を使います。

=IF([前日]=EOMONTH([前日],0),“”,[前日]+1)

530日と31日の処理

30日と31日も先程と同様です。
閏年の2月が29日までだったり、30日までの月もあったりするので、IF関数を使用します。

ただ、さきほどと違って、直前の日付が空白セルになるケース(たとえば2月は29日が空白)がありますので、それも考慮にいれないといけません。

そこで

  • 直前の日付が月末もしくは空白の場合、空白
  • そうではない場合は、1を足す

というIF関数を組みます。

=IF(OR([前日]=“”,[前日]=EOMONTH([前日],0)),“”,[前日]+1)

6動作を確認する

プルダウンリストの月を変化させてみて、正確に1日から月末まで表示されることを確認してみましょう。

 

おすすめの記事