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

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

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

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

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

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

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

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

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

日付自動入力の前提条件

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

日付自動入力の前提条件

  • 日報のシートは一か月分必要で、シート名は日の名前にする。
    → 1日だったら「1」というシート名で「1」~「31」の一か月分用意。
  • 『Sheet1』という名前の固定シートが必要。

日報の全シートに日付が自動で入力できるようにする方法

日付自動入力の手順

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

固定の『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))

日報の全シートに日付が自動で入力できるようにする方法

 

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

↓は『1日』のシートです。

『日付』のところに『2019/5/1』と入力されているのがわかると思います。

日報の全シートに日付が自動で入力できるようにする方法

 

最後の『31日』のシートも『2019/5/31』と入力されています。

日報の全シートに日付が自動で入力できるようにする方法

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

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

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

でも、このままだと『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関数の書式と詳細

今回使っている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関数はシートにあわせた日付を自動入力してくれるわけです。

ちょっと長くてわかりづらいですね^^

まとめ

まとめ

  • CELL関数を使った日付の自動入力は、各シートのシート名を固定(1~31)にする必要がある。
  • シート名が大事なのでシート名を変更されると日付の順番もおかしくなる。
  • でも、シート名を変更できなくする対策もある。(保護)

日報のように日付ごとにシートが分かれるような資料は、最初に日報の定型文(テンプレート)を作る時に一括で日付も入力したくなるものです。

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

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

シート名を取り出す処理がちょっと複雑ですが、他の資料でも流用できるのであれば、どんどん有効利用して楽になりましょう!