ExcelでCDケースにピッタリの月間カレンダーを作る−3完

いきさつ(前置き)はこちら

§1.カレンダーのダウンロード
  1. 麻里府発 > まりふのひとの公開OneDrive > Excelでカレンダー と進み、
  2. 月間カレンダーI.xlsx を右クリック > ダウンロード を選択し、
  3. 保存/名前を付けて保存 する。
    • 保存先は任意。説明の都合上、ここでは ドキュメント\Excelでカレンダー としている。

 ダウンロードしたファイルはコピーし、名前を変えて(例えば、my月間カレンダー.xlsx 等)使ってください。

§2.使い方
◆月間カレンダー シート

  1. セルB4 に西暦年 を入力し、
  2. セルH4 に月 を入力すると、
  3. 該当年月のカレンダーが表示される。
    • セルC4 は、以下の式が入力してある。
       “=TEXT(DATE(B4,H4,1),"(ggge)年")”
ヒント
  • 2〜3行は非表示になっている。変更しなければ表示しても可。
     但し、印刷する時は表示にしないと、CDケースに納まらない。(たぶん)
  • セルの書式設定は変更可。
  • 6行目以下のセルには全て計算式・関数が入っている。
    • セルB4(年)、セルC4、セルH4(月)は、計算式・関数が壊れないように移動可。
  • 例えば第1週の上段(行7)は、予定表シートの「予定1」を VLookup関数で取ってきている。
    =IFERROR(VLOOKUP(IF(ISNUMBER(B$6),DATE($B$4,$H$4,B$6),""),予定表!$B$2:$D$1048576,2,FALSE),"")
  • 例えば第1週の下段(行8)は、予定表シートの「予定2」を VLookup関数で取ってきている。
    =IFERROR(VLOOKUP(IF(ISNUMBER(B$6),DATE($B$4,$H$4,B$6),""),予定表!$B$2:$D$1048576,3,FALSE),"")

  • 2021年1月または5月は、テストデータとして全てに予定が入る。
◆予定表 シート

  • A列:No. ‥‥ 自由に使える。
  • B列:年月日 ‥‥ 予定を入れる日付
    • セルの書式設定は、ユーザー定義で“yyyy/mm/dd(aaa)” としてある。
    • 条件付き書式で、土日の色を変えている。
    • 条件付き書式/セルの強調表示ルール/重複する値 で、背景色を変えている。
  • C列:予定1 ‥‥ 予定の上段に入れる文字列
    • 文字列の制限はない。カレンダーのセルの初期設定は「縮小して全体を表示」にしてある。
  • D列:予定2 ‥‥ 予定の下段にいれる文字列
  • その他 ‥‥ VLookup関数で参照するための条件
    1. 年月日は昇順に並べて置く必要がある。
    2. 年月日が重複していると、下の予定は無視される。
    3. 予定のフォントの設定は無視される。
    • 2021年のデータ
      • テストのために作成したもので、削除が必要。


日本の祝日

 例えば「日本の祝日カレンダー」には、今日現在で 2017年〜2021年の祝日が載っている。

追加休日日付祝日
12021年1月01日(金)元日
22021年1月11日(月)成人の日
32021年2月11日(木)建国記念の日
 2021年3月20日(土)春分の日
42021年4月29日(木)昭和の日
52021年5月03日(月)憲法記念日
62021年5月04日(火)みどりの日
72021年5月05日(水)こどもの日
82021年7月19日(月)海の日
92021年8月11日(水)山の日
102021年9月20日(月)敬老の日
112021年9月23日(木)秋分の日
122021年10月11日(月)体育の日
132021年11月03日(水)文化の日
142021年11月23日(火)勤労感謝の日
152021年12月23日(木)天皇誕生日


表は 2021年の祝日テーブルを模したものであるが、「日付」に曜日が入っており「文字列」となっているで、単純に予定表シートにコピペすることはできない。

 そこで、Excel2013で上表を予定表シートの形式に合わせてみた。

  1. 上表を新しいシートのセルA1にコピペする。
  2. D列に「年月日」の列を作る。
  3. D列に「日付」の“日”までの文字数を求める。‥‥ フラッシュフィルでは作れない?
    1. セルD2に “=search(” と入力し、
    2. [関数の挿入]をクリックする。
    3. 検索文字列に “日” を入力する。
    4. 対象にカーソルを移し、セルB2をクリックする。
    5. [OK]
    6. 「10」と表示される。
  4. D列に「日付」の“日”までを切り出す。
    1. セルD2をアクティブにし、
    2. 数式バーの「SEARCH("日",B2)」を切り取る。
    3. 数式バーに“=left(” と入力し、
    4. [関数の挿入]をクリックする。
    5. 文字列にカーソルを移し、セルB2をクリックする。
    6. 文字数にカーソルを移し、貼り付ける。
    7. [OK]
    8. 「2021年1月01日」と表示される。
  5. D列の文字列をシリアル値に変換する。
    1. セルD2をアクティブにし、
    2. 数式バーの“LEFT(B2,SEARCH("日",B2))” を切り取る。
    3. 数式バーに“=datevalue(” と入力し、
    4. [関数の挿入]をクリックする。
    5. 日付文字列にカーソルを移し、貼り付ける。
    6. [OK]
    7. 「44197」と表示される。
  6. セルD2の式をコピーして完成させる。
  7. 列を入れ替える。
    • A列:そのまま
    • B列:年月日
    • C列:祝日
    • D列:日付
  8. 完成したデータを予定表シートに追加する。
    1. A,B,C列のデータ部のみを選択・コピーし、
    2. 予定表シートに追加貼り付けする。
    3. 貼り付けのオプションは[値]とする。(必須)


≪いきさつ≫
  1. 2018.09.01 ExcelでCDケースにピッタリの月間カレンダーを作る−1
  2. 2018.09.02 ExcelでCDケースにピッタリの月間カレンダーを作る−2
  3. 2018.09.04 ExcelでCDケースにピッタリの月間カレンダーを作る−3完(このページ)



 9月2日のブログ「ExcelでCDケースにピッタリの月間カレンダーを作る−2」で
  • 昨日のカレンダーは計算式のみで作ってあり、単純なカレンダーとなっている。
  • これに「祝日」と自分の予定を加えたものにしたい。
と書いた。「14日(iPS-77の日)迄には作りたいなぁ〜」と思っていたが、その時が突然、やってきた。

  • 台風21号の暴風域に入る確率(tenki.jp 04日09:00解析) 
  • 田布施町の天気/1時間天気(tenki.jp 04日12:00発表)