エクセルでシート毎に日付を自動入力する方法（CELL関数編）

業務日報や営業日報などのように1シートが1日分になっているようなエクセルの場合、シートに自動で日付が入力されるようになったら便利ですよね。

例えば、2019年5月分の日報の場合、1シート目には「2019/05/01」、2シート目には「2019/05/02」というように、シートごとに日付も自動で変わっていくようなもの。

一か月分のシートにいちいち手で日付を入力するのは非効率的であり、間違う可能性もあるので、できれば自動で確実に入力させたい。

今回は、マクロ（VBA）を使わず関数だけで全シートに日付を自動入力させる方法を紹介します。

シート名を決められた名前に固定する前提条件はありますが、簡単に日付を入力することができるので、日報を作成する時などにはぜひ参考にしてください。

また、関数を一切使わないシンプルな式だけで日付を自動入力する方法もあります。

CELL関数を使って全シートに自動で日付を入力する方法

これから紹介する方法は、日報の開始日付を決めればそれにあわせて一か月分の日付が全シートに入力されます。

日付自動入力の前提条件

日付自動入力の方法を紹介する前に、前提条件を説明します。

日付自動入力の手順

はじめに、日報の開始日付を決めます。

固定の『Sheet1』のA1セルに開始日付を入力。（例として2019/5/1を入力）

次に、『Sheet1』以外のシートを全て選択します。
どのシートでもいいので、選択したら見出しのところで右クリックして『すべてのシートを選択』を選択。

『Sheet1』も含めて全シート選択されるので、『CTRL』を押しながら『Sheet1』シートをクリック。

これで、『Sheet1』は選択から外れるので、日報シートだけが選択された状態になります。

日報シートの日付を自動入力させたいセルでダブルクリックし、関数が入力できる状態にします。

以下の関数式を入力。

=DATE(YEAR(Sheet1!$A$1), MONTH(Sheet1!$A$1), MID(CELL("filename",A1), FIND("]", CELL("filename",A1)) +1, 2))

すると、日付が全シートに自動で入力されます。

以上が、関数を使ってシートごとに自動で日付を入力する手順です。

日付＋曜日の形式で表示させてみる

ここまでで無事、全シートに日付が自動で入力されました。

日付だけじゃなくて、下のエクセルのように曜日まで表示させたい場合は、セルの書式設定を変更すれば簡単に表示させることができます。

しかも、全シートを選択した状態で一発で書式変更できるので、ここでその手順を紹介したいと思います。

最初に、全シートを選択します。
どのシートでもいいので、選択したら見出しのところで右クリックして『すべてのシートを選択』を選択。

次に、日付のセル（C3）を選択。

これで、全シートが日付のセル（C3）を選択された状態になります。

続いて、『セルの書式設定』ダイアログを表示させるショートカットキー『CTRL』＋『1』（テンキーは不可）を押します。
※マウスを使って、右クリックのメニューから『セルの書式設定』を選択してもOK。

『表示形式』タブ（①）→『ユーザー定義』（②）→『種類』に以下の定義を追加。

yyyy"年"m"月"d"日("aaa")"

書式を変更すると、下のエクセルのようにセルに『＃＃＃＃』が表示されることがあります。

これは曜日を追加したことで、曜日込みの日付がセルに入り切らなくなったことを示しているので、日付のセル幅を広げてあげます。

C列とD列の間にマウスを置いて、下の画像ような形になったらダブルクリック！

これで、曜日付きの日付が全シートに入力されます。

日付の形式を変更したい場合は、ユーザー定義で指定できる

日付には西暦や和暦などがあり、和暦だと『令和元年6月30日』のように元年で表示させる形式もあります。

次の記事では、いろんな種類の日付形式を紹介しているので、日付の形式を変更したい場合はぜひ参考にしてください。

シートの移動や自動日付の変更をさせたくない場合は保護をかける

ここまで、自動で日付が入力できる方法を紹介しました。

でも、このままだと『Sheet1』の開始日付を誤って変更されたりすると、せっかく自動で入力できた日付がおかしくなります。

また、シート名の変更も同様です。

セルやシートに保護をかける方法を次の記事で詳しく解説しています。ぜひ参考にしてください。

開始日付が変更されないように『Sheet1』シートを隠す

手順は、『Sheet1』を選択し、右クリックメニューから『非表示』を選択するだけです。

このように、シートの見出しから『Sheet1』がなくなるので、間違っても開始日付が変更されることはありません。

なお、『Sheet1』を隠しても、日報の日付はちゃんと表示されます。

隠していたシートを復活させる場合は、適当なシートを選択して、右クリックメニュから『再表示』を選択します。

自動で日付を入力する関数の解説

今回紹介した日付入力の式は以下の関数を使っています。

=DATE(YEAR(Sheet1!$A$1), MONTH(Sheet1!$A$1), MID(CELL("filename",A1), FIND("]", CELL("filename",A1)) +1, 2))

長すぎてよくわかりませんね＾＾

これをわかりやすく分解したのが、以下の画像です。

DATE関数は、3つの引数（年、月、日）を受け取って、日付の数値であるシリアル値を返します。（日付が返されると思ってください）

これにより、DATE関数を入力したセルに求まった日付が自動で入力されるわけです。

DATE関数の書式は次の通り。

今回使っているDATE関数の『年』、『月』の引数は、YEAR関数とMONTH関数を使っているので、『年と月を求めているんだな』というのがなんとなくわかります。

でも、最後の『日』の引数（日報のシート名を求めている）は、3つの関数（MID、CELL、FIND）を使っていて何やっているのかよくわかりませんよね＾＾

MID関数の解説

MID関数は、第一引数の文字列の、第二引数で指定する開始位置から、第三引数で指定する文字の数だけ、文字を取り出します。

わかりやすい例で説明すると、

MID(“ABCD”, 3, 2)と書いたら、”ABCD”（第一引数）の3番目（第二引数）から2文字分（第三引数）を取り出すので、答えは”CD”となります。

MID(
CELL("filename", A1),
FIND("]", CELL("filename", A1)) + 1,
2)

CELL関数の解説

CELL関数は、第一引数に”filename”を指定すると、エクセルファイルのフルパス名（シート名まで）を返してくれます。（第二引数はA1などの任意のセルの位置でよい）

例えば、エクセルファイル（”CELL.xlsx”）がCドライブのtempフォルダにあった場合、CELL(“filename”, A1)は『C:\temp\[CELL.xlsx]1』が返ってきます。

[エクセルファイル名]の次の『1』はシート名です。

FIND関数の解説

次に、FIND関数は第二引数の文字列の中から第一引数の文字を検索し、見つかった位置を返します。

FIND(“]”, CELL(“filename”, A1))では、CELL関数が先程の『C:\temp\[CELL.xlsx]1』を返すとして、[CELL.xls]の『]』の位置を返します。

そして、ここで使ったFIND関数にさらに1を加えている（+ 1）ので、シート名の位置になるように調整しています。

最後に、MID関数の最後の引数（取り出す文字数）では2文字分を指定。

ここまでずいぶん長い解説になりました＾＾

まとめると、このMID関数は

『C:\temp\[CELL.xlsx]1』の部分の最後のシート名を取り出そうとしているわけです。

シート名はシートごとに変わるので、シート名が『1』だと1が、シート名が『10』だと10が取り出されます。

年、月、シートごとの日を引数としてDATE関数で日付を入力する

MID関数が『シート名=日』を返すことがわかりました。

これにより、DATE関数は次のような引数をもらいます。

DATE（『Sheet1』のA1セルにある年、『Sheet1』のA1セルにある月、そのシートのシート名＝日）

この引数をもらうことで、DATE関数はシートにあわせた日付を自動入力してくれるわけです。

今回紹介した方法を使えばシートにあわせて日付を自動入力できるので、かなり便利だと思いますよ。

手でいちいち入力するよりは、間違いもなくかなり効率いい方法です。

また、関数を一切使わないシンプルな式だけで日付を自動入力する方法もあります。