Excel：月別の平日数と休日数の表を1つの数式で表示する

動的配列数式とスピル機能を利用して1つの数式で月別の平日数と休日数の表を1つの数式で表示してみた数式ワンライナーネタです。

作成した数式

以下はC2に入力された日付からその年の月別の平日数と休日数の配列を返す数式です。

=LET(
  年, C2,
  祝日, LET(
    JSON, WEBSERVICE("http://api.national-holidays.jp/"&年),
    TMP, SCAN(
      JSON, SEQUENCE(365),
      LAMBDA(C,_, IFERROR(RIGHT(C, LEN(C)-SEARCH("""date"": """, C)-8), ""))),
    MID(FILTER(TMP, TMP<>""), 1, 10)
  ),
  T, SEQUENCE(12, 3)-1,
  月, INT(T/3)+1,
  開始日, DATE(年, 月, 1),
  終了日, EOMONTH(開始日, 0),
  月日数, 終了日-開始日+1,
  平日数, NETWORKDAYS.INTL(開始日, 終了日, 1, 祝日),
  休日数, 月日数-平日数,
  SWITCH(MOD(T, 3)+1, 1, 月, 2, 平日数, 3, 休日数)
)

B5に数式を入力して表示される結果は以下です。

2022年の月別の平日数と休日数の表



年	2022

月	平日数	休日数
1	20	11
2	18	10
3	22	9
4	20	10
5	19	12
6	22	8
7	20	11
8	22	9
9	20	10
10	20	11
11	20	10
12	22	9

数式の説明

ざっくりですが数式の説明を書いていきます。

平日数はNETWORKDAYS.INTL関数を利用して求めました。

NETWORKDAYS.INTL関数は開始日、終了日、週末番号、祝日の一覧を引数に指定して稼働日を求めることができる関数です。

平日数を求めるにはその開始日と終了日にある祝日の日付の一覧が必要です。

祝日の一覧を取得する部分

祝日の一覧は前もって取得、入力しておいて利用するのが普通とは思いますが、今回はワンライナーネタですので数式を使って取得することにしました。

祝日の一覧の取得にはWEBSERVICE関数で「国民の祝日：日本」の国民の祝日 APIを利用させていただきました。

国民の祝日：日本

当サイトでは、日本の「国民の祝日」を各システムで利用するためのWebインターフェースを提供しています。

national-holidays.jp

特定年の一覧取得（YYYY形式）APIにリクエストをすると以下のJSON形式のレスポンスが返却されます。（見やすいようにインデントしています）

[
  { "date": "2022-01-01", "name": "元日", "type": "国民の祝日" },
  { "date": "2022-01-10", "name": "成人の日", "type": "国民の祝日" },
  { "date": "2022-02-11", "name": "建国記念の日", "type": "国民の祝日" },
  { "date": "2022-02-23", "name": "天皇誕生日", "type": "国民の祝日" },
  { "date": "2022-03-21", "name": "春分の日", "type": "国民の祝日" },
  { "date": "2022-04-29", "name": "昭和の日", "type": "国民の祝日" },
  { "date": "2022-05-03", "name": "憲法記念日", "type": "国民の祝日" },
  { "date": "2022-05-04", "name": "みどりの日", "type": "国民の祝日" },
  { "date": "2022-05-05", "name": "こどもの日", "type": "国民の祝日" },
  { "date": "2022-07-18", "name": "海の日", "type": "国民の祝日" },
  { "date": "2022-08-11", "name": "山の日", "type": "国民の祝日" },
  { "date": "2022-09-19", "name": "敬老の日", "type": "国民の祝日" },
  { "date": "2022-09-23", "name": "秋分の日", "type": "国民の祝日" },
  { "date": "2022-10-10", "name": "スポーツの日", "type": "国民の祝日" },
  { "date": "2022-11-03", "name": "文化の日", "type": "国民の祝日" },
  { "date": "2022-11-23", "name": "勤労感謝の日", "type": "国民の祝日" }
]

このレスポンスから日付部分を切り取って祝日の一覧を取得します。

SEQUENCE関数で1から365までの連番を作成しSCAN関数で"date": "より前を段々と削除した後にそれぞれMID関数で日付部分10桁を切り取って取得しました。

=LET(
  JSON, WEBSERVICE("http://api.national-holidays.jp/2022"),
  TMP, SCAN(
    JSON, SEQUENCE(365),
    LAMBDA(C, _, IFERROR(RIGHT(C, LEN(C)-SEARCH("""date"": """, C)-8), ""))),
  MID(FILTER(TMP, TMP<>""), 1, 10)
)

XML形式のレスポンスを返すAPIであればFILTERXML関数を利用できますが今回はJSON形式のため、かなり力技になってしまいました。JSONPathでフィルタリングできるFILTERJSON関数が欲しいです。

平日数、休日数を求める部分

つづいて平日数、休日数を表示する部分ですが、まずSEQUENCE関数で12行3列の0始まりの0から35の連番Tを作成しました。

それぞれの連番の値についてINT(T/3)+1でその値が何行目にある値かを求めてそれを月としました。

T, SEQUENCE(12, 3)-1,
月, INT(T/3)+1,

各月について以下でその月の開始日と終了日を求めてその日付から月の日数、平日数、休日数を求めています。

開始日, DATE(年, 月, 1),
終了日, EOMONTH(開始日, 0),
月日数, 終了日-開始日+1,
平日数, NETWORKDAYS.INTL(開始日, 終了日, 1, 祝日),
休日数, 月日数-平日数,

NETWORKDAYS.INTL関数の週末番号には1の「土曜日と日曜日」を指定しています。

最後に連番の値についてMOD(T, 3)+1でその値が何列目かを求めてSWITCH関数で1列目に月、2列目に平日数、3列目に休日数を表示しています。

SWITCH(MOD(T, 3)+1, 1, 月, 2, 平日数, 3, 休日数)

まとめ

動的配列数式とスピル機能を使うことでエクセルの数式を作成するのが楽しくなりますね。

参考リンク