Excel使いこなしの基礎と応用


Excelで万年カレンダー仕様の予定表を作る(5)

カレンダーの祝日と年中行事を自動で表示させるように設定しましょう。VLOOKUP関数で祝日名を検索し、IFERROR関数で平日に返されるエラー値を処理します。

スポンサーリンク

2016年から2018年までの祝日表と年中行事表はダウンロードファイルの別Sheetに添付してあります。2018年以降も、年号の数字を入れ替えれば自動で切り替わります。

スケジュール表作成のプロセス


Step5. 祝日と年中行事を自動表示

step-arrow

祝日表・年中行事表に名前を定義

参照する祝日と年中行事の日付・名前の一覧表は別に用意する必要があります。
祝日の一覧表は計算式で祝日から振替休日までのシリアル値を取得して作成しますが、ちょっと面倒なのでここでは省きます。ダウンロードファイルの別Sheetに添付してある表をご利用下さい。
祝日表年中行事表
上図の赤線で囲ったエリア(曜日・祝日名/行事名 2016~18年)にはそれぞれ「祝日表」「年中行事表」と名前を定義し、数式の中で使います。

名前の定義は範囲を選択して「名前ボックス」に入力するか、「数式」タブの「名前の定義」から行えます。 ▶名前の定義について詳しく
祝日表・年中行事表の名前の管理

年中行事」の表にプライベートなイベント(誕生日や結婚記念日)を挿入することもできますし、表を丸ごと入れ替えてもOK。
その場合、セル構成を変えずに使うなら、「曜日」のセルに=IF($F3="","",DATE($F3,イベントの月の数値,イベントの日の数値))
の数式を入れて下さい。
(例)誕生日:4月10日 ⇒ =IF($F3="","",DATE($F3,4,10))
誕生日を挿入した年中行事表

祝日のセルにIFERROR+VLOOKUP関数を挿入

祝日の最初のセル(C3)をクリック。
祝日の入る最初のセルをクリック
「数式」タブ「関数ライブラリ」の「論理▼」から「IFERROR」をクリック。
論理リストからIFERRORをクリック
IFERROR関数の引数」ダイアログボックスが出たら、「」にカーソルがあることを確認して、「名前ボックス▼」から「VLOOOKUP」をクリック。
IFERROR関数の引数ダイアログボックスの値にカーソルを置いてVLOOKUPをクリック
(ここまででVLOOKUP関数は使用しているので、リストの中に入っていると思いますが、なければ最下部の「その他の関数」をクリックして「検索/行列」から探して下さい)

VLOOKUP関数の引数」ダイアログボックスが開いたら、「検索値」にカーソルがあることを確認して、最初の曜日のセル(B3)をクリック。
VLOOKUP関数の引数ダイアログの検索値に曜日セルを指定
範囲」のボックスに上記で定義した「祝日表」という名前を入力。
列番号」には半角で「2」と入力。(祝日表の左から2列目を参照)
祝日表の左から2番めを参照
指定された検索方法」には半角で「0」と入力。0=FALSE 検索値と完全一致で検索する。1=TRUE 完全ではないが近い値も検索する)
▶VLOOKUP関数の設定をもっとわかりやすく

引数を全て入力↓し終えても、「OK」は押さず、
VLOOKUP関数の引数の全ての引数を入力
数式バーに表示されている数式の中の「IFERROR」をクリックします。
数式の中のIFERRORをクリック

IFERROR関数の引数」ダイアログボックスに戻るので、「」に設定したばかりのVLOOKUP関数が表示されているのを確認して、「エラーの場合の値」に空欄を示す「""(ダブルクォーテーション)」を入力します。
IFERROR関数の引数ダイアログでエラーの場合にダブルクォーテーション
ここで、「OK」しましょう。

最終的な式は「=IFERROR(VLOOKUP(B3,祝日表,2,0),"")
このセルに表示される正当な値はB3セルの年月日で検索して祝日表から取得した祝日名で、もし、取得できずに不正な値が入力されたらエラー値を表示せずに空欄にする
▶IFERROR関数をもっとわかりやすく

「C3」セルに「元旦」と表示されましたね?
後はこのセルを33行目までドラッグしてオートフィルコピーすれば完了です。
祝日のセルをフィルコピー
年中行事」の方は「VLOOKUP関数」の「範囲」を「年中行事表」にするだけで、後は全て同じ。

スポンサーリンク

祝日で作った式「=IFERROR(VLOOKUP(B3,祝日表,2,0),"")」をコピーして「D3」セルに貼り付け、式の中の「祝日表」を「年中行事表」に替えればいいだけです。
=IFERROR(VLOOKUP(B3,年中行事表,2,0),"")

後はD3セルを33行目までドラッグしてオートフィルコピーすれば完了。
年中行事のセルをフィルコピー

▶次は、条件付き書式とWEEKDAY関数で土・日・祝日を塗り分けします

Officeで作れるもの