【Excel関数】日付の表示形式が「〇年〇月」の表を作成する方法

こんにちは、ふみです。みなさんは表計算ソフト「Microsoft Excel（エクセル）」で月毎に集計する表に入力する「〇年〇月」を毎回手入力で作成していませんか？
私は来年以降も同じ形式で作成する見込みがない表を作成するときは、日付部分を手入力で作成しています。

CONTENTS

1 「〇年〇月」の表を手入力で作成する
2 「〇年〇月」の翌月以降を関数で表示させる
3 DATE関数について
4 まとめ

「〇年〇月」の表を手入力で作成する

Excelを開いて適当なセルに「2020年11月」と入力してみます。

「2020年11月」とそのまま表示されれば問題ありませんが、違う形式の日付で表示されてしまった場合はユーザー定義する必要があります。

私が使っている Office 2019 のExcelの場合、入力したセルを右クリック→セルの書式設定(F)…でセルの書式設定を開いた後、分類(C):を「日付」、種類(T):から「〇年〇月」を選択して「OK」をクリックすると日付の表示形式を変えることができます。
またはセルの書式設定を開いた後、分類(C):を「ユーザー定義」、種類(T):のウインドウに「yyyy”年”m”月”」を手入力して「OK」をクリックでも同じようになります。

「2020年11月」を表示できたらセルの右下をドラッグしながら右隣のセルに引っ張ると、「2020年12月」、左隣のセルに引っ張ると「2020年10月」と月単位でコピーされます。
これで簡単に月ごとの表を作成できます。

Excelでは「2020年11月」で表示されている状態でも、セルの値は「2020/11/1」と日付を含めた状態になっています。これを整数値に変換すると「44136」です。
「1900/1/1」を1として、そこから44135日加算すると2020/11/1＝44136になります。
日付や時間をExcel関数で自在に操作するには、この規則を理解しておく必要があります。

「〇年〇月」の翌月以降を関数で表示させる

月ごとの表を毎年使う場合、来年は1月から始まる表は「2020年1月」、4月から始まる表は「2020年4月」の「2020年」を「2021年」に変えてからドラッグで右端までコピーすれば、また使えるようになります。
しかし、この方法はコピーのミスで一部の年月が古い年の表示になったまま気づかないケースが発生する可能性があります。
また、複数列のセルを結合している書式で作成していると、日付を手入力したセルの右下をドラッグする方法では月の連番のコピーがうまくいかない場合があります。

そこで翌月以降を自動表示できるようにExcel関数を使って設定すると、左端の年月を変更するだけですべての年月表示を来年のものに更新することができます。
また、数列のセルを結合している書式でも簡単に月の連番のコピーがうまくいくようになります。

やり方は次のとおりです。

１．
日付が記載されている一番左端のセルのアドレスを「L11」とした場合、すぐ右隣のセル（P11※）に次の関数を設定する。（※この例では4列結合で作成した書式なのでL列の隣が4列先のP列となっている）

=DATE(YEAR(L$11),MONTH(L$11)+1,DAY(L$11))

２．
関数を設定したセルP11の右下をドラッグしながら右に引っ張ると、関数がコピーされて来月以降が自動で表示されるようになる。

以上で日付の設定が完了です。
来年以降はセルL11の日付「2020/4/1」を「2021/4/1」に変更するだけで来年用に変わります。

セルL11の行番号に絶対参照を表す「$」を使っているのは、この数式は11行目以外では使用する予定がない（使い道がない）からです。
このように予め行番号に絶対参照を設定しておけば、別の誰かが誤って別の行にコピーして数式を流用するのを防ぐことができます。

DATE関数について

DATE関数は年、月、日を個別に入力して日付を表示させる関数です。
ここではまず、左のセルからYEAR関数、MONTH関数、DAYの関数でそれぞれ年、月、日の整数値を取り出し、月の整数値に1を加算して左のセルの月の来月を表示しています。
2020年12月の右のセルは「=DATE(2020,13,1)」になりますが、DATE関数は「月」を13以上に設定すると年が来年以降に繰り上がるようになっている為、「2021/1/1」になります。

反対にDATE関数の「日」を0にすると月初めの前日になるので、先月の末日になります。
例えば「=DATE(2020,11,0)」と入力すると、2020/10/31になります。