[Excel]セルに表示したシート名を応用する
記事学び
こんにちは。かづきchanです。
Excelで日毎・月毎の記録をまとめている時や、スケジュール表を使っている時、「シート名」の月や日付に応じて、スケジュール表の日付や曜日を、自動的に変動させたい事があります。
今回は「シート名（月日）」から数字を抜き出して応用する方法について紹介していきます。
※シート名等をセルに表示する方法は、下記の記事をご覧ください。
※その他スケジュール作成関連は、下記の記事にまとめています。
■具体的にどういう事？
ちょっと見づらいかもしれませんが・・・
「5月」シートの名前を「6月」に変えるだけで・・・
こうなります。
もちろん、元のシートをコピーして「6月」に変えても同じです。
※曜日や祝日に応じて条件付き書式で背景色を変えています。
■「シート名」から数字を取り出す
シート名「5月」のうち、「5」を数字として取り出すことが出来れば、DATE関数で日付データを作成するのは簡単です。
=RIGHT(CELL("filename",$A$1),LEN(CELL("filename",$A$1))-FIND("]",CELL("filename",$A$1)))
↑これが「シート名を反映させる」式だとすると↓こう変えます。
=SUBSTITUTE(RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"))),"月","")*1
増えた部分は、太字の部分です。
シート名の「5月」のままでは"月"の文字が邪魔で、「5」を使って日付データに変換できませんので、SUBSTITUTE関数で”月”を空白("")にしています。
（ここでLEFT関数で取り出そうとすると、1桁・2桁の違いが面倒なので）
ただ、それだけでは"文字列"として扱われてしまうのですが、最後に「*1」と、1を掛けることで数値になってくれます。
■体のいい場所に「シート名の数値」の数式を置いておく
取り出した数値「5」を色々と他のセル（日付など）に使えるように、出来るだけその状態のままで”A1”セル等、わかりやすいどこかの場所に上記の数式を入力して置いておいてください。
見た目が悪いなら、文字色を白色などにすれば見えないし気にならない！！
・・・とまぁ、個人で使っている場合など、それで本当にいい場合はもちろんそれでもいいのですが、社内ファイルなどの場合、流石にそれはちょっと恰好が悪い・・・という事もあると思います。
毎月同じ文言の見出しなどがあれば、そこで使ってしまいましょう。
例えば、「■○月のスケジュール」（○の部分は数字）と、毎月”A1”セルに書かれているとします。
それであれば、A1セルに先ほどの「数字だけ取り出したセル名」の数式
=SUBSTITUTE(RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"))),"月","")*1
を入力します。
右端に寄った「5」が表示されていると思うので、右クリックなどで「セルの書式設定」を開き、「表示形式」の「ユーザー定義」を選択します。
そこに、「"■"0"月のスケジュール"」と入力して[OK]ボタンを押せばOK。
【書き方】前後に入れたい文字は、半角の"と"で括る。数字部分は0と入力。
”A1”セルに「■5月のスケジュール」と表示されたと思います。
ただ、データとしては「数値」のままなので、文字が右端に寄っていると思いますので、必要に応じて左寄せなどの配置調整をしてください。
■”1日”の日付データをDATE関数で用意する
いよいよ、スケジュール表の日付のうち”１日”の部分を用意しましょう。
1日の部分（画像例では”B3”セル）に以下の式を入力します。
=DATE(2021,$A$1,1)
DATE関数を使って、「2021」で年を、「$A$1」で月（シート名から取り出した5）を、「１」で日付を指定しています。
「2021」の部分は、もし他にどこかのセルで入力してあるなら、そこから引用してきてもいいと思います。
■”2日”以降の日付データを用意する
月の最終日が30日以内の場合に、翌月の1日も表示されても別にいいのであれば、2日のセル（画像例では”C3”セル）に「=B$3+1」を入力して、あとは31日分まで右にコピーしてください。
※1日と同じ式を入力しても表示されます。・・・が多分少し重いです。
月の最終日が30日以内なら、その後は空白にしておきたいなら、下記の式を入力して、31日分までコピーしてください。
=IF(MONTH(B$3+1)<>$A$1,"",B$3+1)
これは、「前日の次の日（当日）の月が、A1セル「5」じゃなかったら空白、そうではない場合は当日」を表示しています。
■枠線も最終日までにあわせたい場合
シート名にあわせて自動で日付を用意してくれた場合、あとは問題となるのは枠線です。最終日にあわせて表示したり非表示にしたいのであれば、あらかじめスケジュール表の日付部分をすべて、「条件付き書式」で設定してしまいましょう。
スケジュール部分を選択して、数式部分は「=B$3<>""」と入力すればOK。
日付が表示されている”B3”セルが空白以外の時に、外枠を引くようにします。
■あとがき
今回は、「シート名」から数値として月の数字を取り出し、日付として自動的に反映される方法を紹介しました。
イメージ通りの背景変更設定ができましたでしょうか？
ブログに書いている内容への感想やご質問等ありましたら、お気軽にメッセージをいただければ幸いです。
※その他スケジュール作成関連やり方は、下記の記事にまとめています。
