カレンダーテーブル活用による経過営業日の算出

Excelのワークシートには NETWORKDAYS 関数をはじめ、営業日を計算する便利な関数が用意されています。

しかしPower Queryには、それに相当する専用関数は用意されていません。

そのため一見すると、

  • 経過営業日を計算したい
  • 土日を除外したい
  • 祝日も考慮したい
  • 独自の休日ルールを反映したい

といった要件は難しそうに見えます。

ところがPower Queryでは、関数に頼らず「カレンダーテーブル」を作成することで、営業日計算を非常に柔軟に実現できます。

この記事では、

  • M言語のリスト機能
  • カレンダーテーブル
  • 祝日テーブルとの結合
  • 条件列
  • カスタム関数

といったPower Queryならではのテクニックを組み合わせて、経過営業日を算出する方法を解説します。

単に営業日数を求めるだけでなく、

「起算日は含めるのか?」

「祝日をどう扱うのか?」

「会社独自の休日を考慮するのか?」

といったルールにも柔軟に対応できるため、実務で応用しやすい考え方が身につきます。

Power Queryの真価である「データを作って計算する」という発想を、ぜひ体験してみてください。

この記事でできること

✓ Power Queryで経過営業日を算出できる

✓ Power QueryでNETWORKDAYS相当の処理を実現できる

✓ カレンダーテーブルを作成できる

✓ M言語の連続リストを作成できる

✓ 「..」演算子を利用できる

✓ 日付リストを生成できる

✓ リストをテーブルへ変換できる

✓ Date.DayOfWeekName関数を利用できる

✓ 曜日情報を取得できる

✓ 土日を除外した営業日計算ができる

✓ 祝日を考慮した営業日計算ができる

✓ 祝日テーブルを活用できる

✓ マージ機能を利用できる

✓ 条件列で営業日フラグを作成できる

✓ if式を利用できる

✓ 0・1フラグによる集計ロジックを理解できる

✓ 起算日を含む・含まないを調整できる

✓ 終了日を含む・含まないを調整できる

✓ インデックス列を利用した制御ができる

✓ List.Max関数を利用できる

✓ 独自ルールの営業日計算を構築できる

✓ カスタム関数化できる

✓ 複数レコードを一括計算できる

✓ Power Queryの関数化設計を学べる

✓ カレンダーテーブルを他の分析にも応用できる

ポイント

今回のポイントは関数などで一律に計算するのではなく、日付を並べたうえで計算を行います

上の画像のように該当する日付を並べて、曜日に変換し「0,1」を並べます

そして、最後に「0,1」の列を合計します

この合計が経過営業日です

ですので、後から条件式の追加などが容易です

上の画像では祝日が考慮されていませんが、祝日の考慮はもちろん、その他の条件の考慮も容易に行えます

カレンダーテーブルの作成

カレンダーテーブルの作成はリスト作成⇒テーブルに変換の流れで作成します

「..」を使用すると連続した値のリストが作成できます

この仕組みを利用して、他クエリからまずは連続した日付のリストを作成します

他のクエリとは下の画像のような2つの日付を記したクエリになります

期間_単数

こちらのクエリの各列の1行目を参照して、空のクエリでリストを作成します

*{0}で1行目を参照します

ここではNumber.From関数で一旦数値に変換しています

= {Number.From(期間単数[期間A]{0})..Number.From(期間単数[期間B]{0})}

連続した数値を作成した後は、テーブルに変換します

後は日付形式に変換を行います

曜日への変換

曜日への変換は「Date.DayOfWeekName関数」を使用します

曜日だけではなく、祝日も考慮が必要です

祝日については別途テーブルを作成しておく必要があります

こちらは作成した空のクエリのマージします

そして、曜日との集約を「条件列」で行います

これで「0,.1」を並べる列を作成する準備ができました

0,1列の作成及び合計

0,1を並べる列はカスタム列作成画面でif関数を使用して作成します

これで0,1が並ぶ列/Digitを作成することができました

尚、上記の式ですと「起算日の曜日」が休日でない場合には自動的に計算に含まれています

それでは本題に戻りますが、最後にこちらのDigit列を対象にして、変換タブから合計処理を行います

これで経過営業日を算出できました

ちなみに祝日クエリをマージした時に、日付の並びが変わってしまいますので、並び替えを行っておくと計算過程が分かりやすいです

計算方法の調整

起算日には曜日・祝日に関わらず計算に入れる(0を立てる)、もしくは計算に入れないなどの調整にはインデックス列を使用します

上記の画像のように、起算日の行になるのは「インデックス列」の1になります

ですので、if式をインデックスが1の時には値が0(もしくは1)になるように修正します

逆に計算の終点の日を曜日・祝日に関わらずに計算に入れる場合にはLis.Max関数を使用します

複数行計算する場合

複数行にて計算する場合には、カスタム関数を使用します

前述のクエリの詳細エディタを開き、コードに記号を埋め込んで関数化します

={Number.From(myDate[起算日A]{0})..Number.From(myDate[起算日B]{0})}

⇒{Number.From(A)..Number.From(B)},

上の画像のAとBの記号が、下の画像の各列と対応します

そして、記号を埋め込んで完了ボタンを押すと、関数は完成です

こちらの関数を複数行あるクエリで呼び出します

この際、前述の記号に各該当列を割り当てます

これで、各行に関数が割り当てられます

後は関数で作成した列を展開して終了です

<まとめ>

今回は経過営業日をPower Query独自のカレンダーテーブルにより算出する方法を解説しました

もしかしたら、上記の内容で「この画像の起算日の計算式は?」という内容もあるかもしれませんが、ご容赦頂ければと思います

今回の方法ですと、ワークシート関数で一律に計算するより、分かりやすく、カスタマイズしやすいです

ぜひ、手を動かしながら試してみて頂きたいと存じます!

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

コメントを残す

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です