EXCEL WORKDAY関数とWEBSERVICE関数等で「今日が営業日である事」を判定

Excel 関数のコーディング事例をご紹介しています。

VBAは使用していないのですが、関数をネスト(入れ子)にする事は多用していますので判読性がし難い事と存じますが、なるべく分かり易くお伝えできればと考えています。

今回のテーマは「今日が営業日である事を確認する」になります。

Excel 2016バージョン2106(ビルド 14131.20320)を使用しています。

営業日と非営業日の捉え方

世間一般的には、「入金にまつわる話」が土日祝日の場合は「直前の営業日」、「支払にまつわる話」であれば「直後の営業日」である事が多いと思います。

このように「お金にまつわる話」の場合は「入金や支払の営業日」が明記されていた方が当事者としては話にぶれが生じ難くなる認識です。

ただしこの場合に意識しなければならない営業日というのは個々の会社の営業日というよりも銀行の営業日になるのではないでしょうか?

そうなりますと日本の銀行の休業日がどのようになっているのか?Googleで検索してみると、「銀行法施行令」という政令で規定されているようです。

実際に「銀行法施行令」を調べてみると、第五条で休日が定められている事が分かりましたので、政令が変更にならない限りは銀行の休業日は変わらない事になります。

上記に記載されている銀行の休日の規定は下記になります。

※銀行の営業所の休日については第五条2項での定めがありますが、ここでは省略しています。

WORKDAY関数で営業日を判定するためには休日テーブルが必要

Microsoft OfficeサポートでのWORKDAY関数で説明されている書式は下記になります。

「休日」は省略できますが、省略した場合は「週末のみが稼働日数計算から除外」される事になります。

ところで上記の週末とは何を表しているのでしょうか?

実際にWORKDAY関数を動かしてみると週末が「土曜日・日曜日」になる事が分かりますが、上記の説明書式の中には明記されていません。

ただしWORKDAY関数には兄弟のようなWORKDAY.INTL関数(INTLはinternationalの省略語であると推察致します)が存在しています。

このWORKDAY.INTL関数では省略可能な引数として「週末」が設定されていて「週末にする曜日 (稼働日と見なされない日) 」を指定する事ができます。

しかも、この引数「週末」を省略した場合、週末の曜日は「土曜日と日曜日」と書かれています。

従いまして、WORKDAY関数では引数の「週末」が省略されているために週末が「土曜日と日曜日」になっているのではないか?と思われます。

2021年日本の銀行の休日テーブル

2021年は東京オリンピック開催のために国民の祝日が法律で変更されていますが、日本の銀行の年末年始休暇を含めた休日テーブルは下記の表のようになる認識です。

WEBSERVICE関数とFILTERXML関数を使って休日テーブルを作る

本章の見出しはかなり省略した表現になっていますが、正しくは「WEBSERVICE関数を使ってサーバーに置かれているXML形式で記述された休日テーブルのデータをHTTPS(プロトコル)でExcelのセルの中に読み込んで、そのデータに基づきFILTERXML関数を使ってシート上に休日テーブルを作る」になります。

「セルにデータを読み込む」というのはセキュリティ的に大丈夫なのか?と思われるかもしれません。

Officeサポートの「Excelの仕様と制限」のページを見ると、セルに含む事ができる文字数は制限されていて最大で32,767文字になります。これは当然ですが前章の注意事項の値と同じです。

仮に最大限の文字数のデータが取り込まれたとしても、セルの中に閉じ込められた状態であれば悪さのしようがありませんのでご安心ください。

WEBSERVICE関数がHTTPS(プロトコル)でExcelのセルの中に読み込むタイミング

ただし一つ注意が必要なのはWEBSERVICE関数がネットワーク越しにデータをセルに読み込むタイミングです。

WEBSERVICE関数をセルに入力・更新する時、「①セルに直接入力・更新する」、かまたは、「②セルを選択してから数式バーに入力・更新する」のいずれかになると思います。

この時フォーカスの当たっているカーソルの位置は「①の場合はセル」、「②の場合は数式バー」になります。

「フォーカス」が当たっている状態で、かつ日本語入力の状態でなければ「Enter」キーを押すことで入力・更新した内容が「実行」されます。②の場合は数式バーの左隣に配置されている「レ」ボタンをクリックすれば「実行」されます。

逆に実行したく無い時は、①・②ともに「Esc」キーを押すか、②の場合は数式バーの「レ」ボタンの左隣に配置されている「×」ボタンをクリックすることでキャンセルされます。

つまりネット上に置かれたXMLファイルが更新されただけではセルに取り込まれたデータは変わらず、またWEBSERVICE関数が入力されたXLMSファイルを開き直したり、該当シートで数式タブからシート再計算をしたりしてもデータが置き換わる事はありません。

休日テーブルの置き場所

ところで「どこのサーバーに置かれているか?」も気になる事と存じます。

べき論で言えば「銀行法施行令」を主管する金融庁が銀行の休日テーブルを管理して金融庁のサーバーで運用していただけるのが有り難いのですが、ざっくりとGoogleで検索した範囲ではそのようなページは見つかりませんでした。

という事で、今回は弊社が借りているレンタルサーバー上にXML形式で記述された休日テーブルのデータを置いています。

https://neverendworks.com/news/data/HolidayTable.xml

neverendworks.com

あくまでも検証目的のためですので、お含み置きいただければ幸いです。

2021年XML形式の日本の銀行休日テーブルの先頭から15行目までの抜粋

<?xml version="1.0" encoding="UTF-8"?>
<Worksheet xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="HolidayTable.xsd">
<Row>
<Data>2021-01-01</Data>
<Note>(金)</Note>
<Name>元日</Name>
</Row>
<Row>
<Data>2021-01-02</Data>
<Note>(土)</Note>
<Name>年始休暇</Name>
</Row>
<Row>
<Data>2021-01-03</Data>
<Note>(日)</Note>

上記XMLデータの2行目にXMLスキーマ(.xsd)ファイルとの連携の記述がありますが、今回の記事の中では有効ではありません。

ExcelにおけるXMLスキーマに関する話題につきましては別の記事にてご紹介いたします。

なお上記XMLデータのタグの構成は下記のようになります。

FILTERXML関数を沢山使って表形式に加工する

セルの中に閉じ込められた状態のままではWORKDAY関数で休日リストとして指定する事はできないので、FILTERXML関数を沢山の箇所で使用してXMLデータが表の形になるように整えます。

FILTERXML関数はMicrosoft Officeサポートでは「指定されたxpathを使用してXMLコンテンツから特定のデータを返します」と説明されていますが、そうなりますと「xpath (XML Path Language)」を知らないとFILTERXML関数を使いこなせない事になります。

ただし前章のような比較的単純な構造のXMLからデータを取得するのであれば難しく考える必要はなく、しかもセルの中に落とされたデータに対する処理になるので「とりあえず試してみる」というスタンスで十分です。

なお今回使用している「xpath」は「//Row[番号]/各要素」という1つのパターンだけです。

この構文の説明を下記に簡単にまとめます。

xpath表記	説明
//	xpathではルートタグからタグ構成の順番に書き表します。 「Worksheet→Row→Data or Note or Name」 実際のロケーションパスとしては次の形式になります。 /Worksheet/Row/Data /Worksheet/Row/Note /Worksheet/Row/Name ただしこれだと決まりきったWorksheetの分が繰り返しとなり冗長になるため、 //を使用してWorksheetを省略し途中からロケーションパスを書き表す事ができ ます。 ※なお冗長というのであればRowの部分も該当するのですが、Rowを省略しない理由 は次の説明で記述します。
Row[番号]	同一のロケーションパスで同一のタグが繰り返し使用されているような場合に、 何番目に表れるかを指定して取り出すことができます。 今回であれば//に続けてRowというタグが繰り返し使用されています。 このような場合、何番目のRowか?を指定するためにRow[番号]の形で書き表す 事ができます。 例えば3番目であれはRow[3]になります。 そのためにRowを//で省略することはできません。

なおもう少し分かり易い説明が必要な場合は、最近更新されているお勧めできるサイトが見つからなかったために、お手数ですがGoogleで「xpath」を検索をして見ていただければ幸いです。

Excelシートでの各関数の入力例

下図を横にA列・B列・C列、縦に1行・2行・3行と続くExcelのシートとすると、

• 「A1」セルにWEBSERVICE関数を入力
• 「A2」セル・「B2」セル・「C2」セルに「A1」セルのXML形式文字列から、1番目の要素「日付」・要素「曜日」・要素「休日名」を返すためのFILTERXML関数が入力されています。
• 「A3」セル・「B3」セル・「C3」セルには2番目の各要素を返すためのFILTERXML関数が入力されています。
• 3番目以降も同様なFILTERXML関数が入力されています。

◢	A	B	C
1	=WEBSERVICE(“https://neverend…
2	=FILTERXML(A1,”//Row[1]/Data”)	=FILTERXML(A1,”//Row[1]/Note”)	=FILTERXML(A1,”//Row[1]/Name”)
3	=FILTERXML(A1,”//Row[2]/Data”)	=FILTERXML(A1,”//Row[2]/Note”)	=FILTERXML(A1,”//Row[2]/Name”)

実装例

「読み込まれた休日テーブルに何日分の休日の登録があるか?がハッキリしない」事を前提にした数式をA列・B列・C列の各2行目から25行目まで入力した時の実装例になります。

• 

WORKDAY関数で「今日が営業日である事」を判定する

随分回り道をしてしまいましたが、WORKDAY関数で指定された日(例えば今日)が営業日である事を判定するロジックの話に入ります。

ただし「スマートなやり方」と言うよりも「下手なやり方」にあたるかもしれません。

仮に今日が直近に祝日が無い週の週末日(土曜日・日曜日)の土曜日であったとします。

1.土曜日の1日前の営業日は金曜日になります。
2.金曜日の翌営業日は月曜日になります。
3.指定された日は土曜日ですが、2で求められたのは月曜日なので今日は非営業日になります。

次の例は、A1セルに今日の日付が入力されています。

A2セルではA1セルの日付が営業日であればそのまま今日の日付を返し、非営業日であれば翌営業日を返す数式になります。

◢	A
1	=TODAY()
2	=IF(WORKDAY(WORKDAY(A1,-1,holiday),1,holiday)=A1,A1,WORKDAY(A1,1,holiday))

※「holiday」という「名前」は休日テーブルの日付列の参照範囲に定義されています。

まとめ

WEBSERVICE関数が入力されたXLSXファイルをコピーしたり、別のフォルダーに移動した時は下記のようなセキュリティの警告がメッセージバーに表示されます。

• 

メッセージバーに表示される「コンテンツの有効化」ボタンをクリックしないとWEBSERVICE関数は#VALUE!のエラーを返します。

セキュリティの警告はその内容に応じて表示する・しないを設定する事はできません。

下記Microsoft Officeサポートのページでは「セキュリティの警告」自体を無効にするしか方法が無いために、「セキュリティの警告」を無効化することは非推奨となっています。

従いまして、今回のようにWEBSERVICE関数が理由で「セキュリティの警告」が表示された場合は「コンテンツの有効化」ボタンをクリックして頂けますようお願い致します。

---

昔はWEBSERVICE関数のようなプル型の操作が必要になる手法は、ITスキルの問題からあまり受け入れられなかった記憶です。

そのために基本はプッシュ型で極力人の手を煩わせないものが指向され、つまりはマクロ(VBA)でプログラムすることが前提で、今回のような話は土俵にも上らなかった事と存じます。

ただし今どきのITリテラシーが向上した方々に向けてであれば、十分対応していただける範囲内であり、定められた指標を更新する必要があるようなExcelの資料を作っている場合は、有効な手法になるのではないでしょうか?

ただしIT部門やITに詳しい人によって「指標の更新が運用されている」必要がありますが…

以上最後までご一読いただき誠にありがとうございました。