Excel Tips ～カレンダーを作る④【祝日の表示～XLOOKUP関数】

めあて

与えられた値を範囲や配列から検索し、それと一致する行にある値を表示させる関数としてVLOOKUP関数というのがありますが、Microsoft365やExcel2021ではXLOOKUP関数があります。
使う目的はほぼ同じなのですが、使い勝手が格段に向上しています。
今回はカレンダーにおいて祝日を表示させるためにつかってみました。

祝日データの取得

ここではWEBSERVICE関数を使いました。

Excelにインターネットからデータを取り込むサイト | ExcelAPI

ExcelAPIの「祝日の日付を取得」と「祝日の名称を取得」を利用して祝日の一覧表を作成しました。
このままではオフラインで使えなくなりそうなので念のため値複写。

文字列⇒シリアル値【DATEVALUE関数】

このとき気をつけておかないといけないのは、このまま値複写をすると日付のデータが文字列になってしまいます。
後ほどXLOOKUP関数の検索値として日付(シリアル値)を使うのでこのままでは思い通りになりません。
ということでDATEVALUE関数でシリアル値に変換します。

DATEVALUE 関数

これを検索する範囲に値複写します。

これでもかまわないのですが、日付表示にしておきます。

ということで祝日を検索する範囲が完成です。

XLOOKUP関数

　B列にあるカレンダーの日付を詮索値として、検索範囲として第1引数にはE列(E2:E18)を、そして戻り値として打2引数にはF列(F2:F18)。そしてXLOOKUP関数のここが便利なところなのですが、検索値が見つからない場合の戻り値(第3引数)まで設定できます。
　これがVLOOKUP関数ならエラー値が表示されてあまり格好良くありません。もちろんエラー値を非表示にすることも他の関数を組み合わせて可能ではあるのですが、見た目がごちゃごちゃしていてスマートではありません。ひいてはメンテナンス性も低くなります。また、VLOOKUP関数では検索列が一番左側になければいけませんでしたが、XLOOKUP関数ではその必要がなくなり、参照するテーブルの作り方も自由度が増しました。

XLOOKUP 関数

　あとは他のセルに複写して使う事を考えて、検索範囲の第1引数と、戻り値範囲の第2引数は絶対参照にしておく必要があります。

応用【祝日の追加】

　応用として、独自の祝日を追加することもできます。

　ここでは祝日の一覧表の最後に2021/11/1に創立記念日を追加しました。そしてカレンダーの同日に表示させているのですが、これを実現するためには、検索および戻り値の範囲を変更する必要が出てきます。いちいち関数の内容を再編集するのも面倒臭いものがあります。
　ということで次回はそういう手間を省く方法の一例を紹介。