業務でExcelのPower Queryを使用する機会がありました。
Excelの関数やVBAと比べて、手軽に様々なデータソースの読み込みと、データの加工と出力ができるので便利です。
Power Queryを使用した作業の中で翌営業日と前営業日を出力したかったのですが
出力する方法がなかなか見つからなかったので、Power Queryで翌営業日・前営業日を出力する方法をまとめました。
途中で入力する値を変更すると、2営業日後や2営業日前などの任意の営業日も出力することもできます。
祝日マスタの用意 #
祝日マスタは内閣府のページの中段あたりに祝日が記載されたcsvファイルがあるので
ダウンロードしてPowerQueryで読み込みます。
翌営業日・前営業日クエリの作成 #
シートに日付を入力してカレンダーシートを作成します。
1行目にヘッダーとして「日付」と入力し、2行目から対象の日付を入力します。
今回は2024/2/1から2024/2/15を入力しました。
カレンダーシートの1行目の「日付」から2024/2/15のいずれかのセル上で右クリックして
「テーブルまたは範囲からデータを取得」を選択します。
テーブルの作成画面が表示されるので、「OK」をクリックします。
PowerQueryエディターが表示されます。日付列を日付型に変換します。
列の追加タブからカスタム列を追加し、新しい列名を「曜日」にしてカスタム列の式に以下の内容を入力します。
=Date.DayOfWeekName([日付])
参考URL：PowerQuery 日付データから曜日を取得する
曜日列が追加されます。
祝日マスタとデータを結合するため、ホームタブ右側のクエリのマージを選択します。
マージ画面が表示されます。
中段の選択リストで「祝日マスタ」を選択し、カレンダーの日付列と祝日マスタの国民の祝日列を選択してOKをクリックします。
祝日マスタがTableとして結合されます。
祝日マスタ列の展開ボタンをクリックし、「国民の祝日・休日月日」と「元の列名をプレフィックスとして使用します」のチェックを外してOKをクリックします。
日付に祝日が結合されたデータが作成されます。
祝日とマージしたときに日付の順序が日付順に並ばなくなるため、日付を昇順で並び替えます。
列の追加タブから条件列をクリックします。
条件列の入力画面が表示されます。
新しい列名を「休み判定」と入力し、句の追加ボタンを2回クリックして、以下の3つの条件を入力します。
| 列名 | 演算子 | 値 | 出力 |
|---|---|---|---|
| 曜日 | 指定の値に等しい | 土曜日 | true |
| 曜日 | 指定の値に等しい | 日曜日 | true |
| 国民の祝日・休日名称 | 指定の値と等しくない | null | true |
それ以外の場合にfalseと入力します。
休み判定列が追加されます。
休み判定列をFALSEでフィルターすると営業日のみ表示されます。
列の選択で日付列のみ選択します。
列の追加タブからインデックス列をクリックして、インデックス列を追加します。
翌営業日を出力するためにカスタム列を追加し、新しい列名を「翌営業日」にします。
カスタム列の式に以下の内容を入力して翌営業日列を追加します。
+1の部分を変更することによって翌営業日後以外の日付を出力することができます。
2営業日後の日付を出力する場合は+2、3営業日後の日付を出力する場合は+3と入力します。
=追加されたインデックス[日付]{[インデックス]+1}
参考URL：PowerQuery 1行上のセルを参照する（前月比・差分計算）
日付に対応した翌営業日が表示されます。
前営業日を出力するためにカスタム列を追加し、新しい列名を「前営業日」にします。
カスタム列の式に以下の内容を入力して前営業日列を追加します。
-1の部分を変更することによって前営業日後以外の日付を出力することができます。
2営業日前の日付を出力する場合は-2、3営業日前の日付を出力する場合は-3と入力します。
=追加されたインデックス[日付]{[インデックス]-1}
日付に対応した前営業日が表示されます。
翌営業日と前営業日列を選択し、エラーの削除をクリックします。
このエラーの削除で表示したい日付行が削除されてしまったときは、手順の最初のほうで作成したカレンダーシートの日付の範囲を広げてください。
列の選択でインデックス列を外します。
日付に対応した翌営業日と前営業日が表示されます。
翌営業日列と前営業日列を日付型に変更します。
クエリを読み込むと日付に対応した翌営業日と前営業日がシートに出力されます。
