今日を乗り切るExcel研究所

Excel に働かされていませんか

【Excel LAMBDA】日付・時刻操作編(その2)【ユーザー定義関数集】

日付・時刻操作関数集 その2

LAMBDA 関数を使った数式によるユーザー定義関数(カスタム関数)として、実用的でコピペで使える実装例をまとめていきたいと思います。

ユーザー定義関数の登録方法は以下の記事を参照してください。

www.shegolab.jp

本記事では日付・時刻の操作関連のツールとしてよく使いそうな機能を主に集めました。

とはいえ、Excel での実業務には疎いので本当に必要とされているものが足りないかもしれません。 もし、こんな関数が欲しいという要望があったらお知らせください。

 

【使用上の注意】

  • 本記事に掲載している数式は LAMBDA 関数をサポートしてる Excel(現状、 Microsoft 365 版(サブスク版)Excel のみ)で使用可能です。
  • 本ブログに記載のLAMBDA 数式は全て単独で利用できます。必要な関数のみを「名前の定義」で「参照範囲」にコピー&ペーストしてください。
  • 関数名は任意ですが、再帰関数として実装されている関数は名前を変更されると動作しないので注意が必要です。 これらの関数名を変更するには、LAMBDA 式内の再帰呼び出し関数名も合わせて適宜修正する必要があります。
  • 不具合、使い勝手の改善、欲しい機能等ご意見ご希望がありましたら、本記事のコメントか Twitter にてお知らせください。

当月日付リスト

【名前の定義】

  • 名前

    MONTHDATES

  • コメント

    当月の日付を配列で返します。

  • 参照範囲
=LAMBDA(基準日,
    LET(
        lastEOM, EOMONTH(基準日, -1),
        thisEOM, EOMONTH(基準日, 0),
        SEQUENCE(thisEOM - lastEOM, 1, lastEOM + 1)
    )
)

【構文】

MONTHDATES(基準日)

【引数】

引数 内容
基準日 取得したい当月に含まれる任意の日付のシリアル値、あるいはその日付書式文字列

【戻り値】

当月の日付の配列(縦)

【説明】

MONTHDATES 関数は基準日の当月に含まれる日付を配列として返します。

【使用例】

当月カレンダー

【名前の定義】

  • 名前

    CALENDER

  • コメント
  • 参照範囲
=LAMBDA(基準日,
    LET(
        som, EOMONTH(基準日, -1) + 1,
        eom, EOMONTH(基準日, 0),
        calFrom, som - mod(WEEKDAY(som)-1, 7),
        calTo, eom - WEEKDAY(eom) + 7,
        weeks, (calTo - calFrom + 1) / 7,
        SEQUENCE(weeks, 7, calFrom)
    )
)

【構文】

CALENDER(基準日)

【引数】

引数 内容
基準日 カレンダー表示したい当月に含まれる日付のシリアル値、あるいはその日付書式文字列

【戻り値】

当月カレンダーの日付の2次元配列

【説明】

CALENDER 関数は一般的なカレンダー形式でひと月分の日付データを配列として出力します。

結果はスピルします。

左端の列が日曜日になります。

【使用例】

範囲日付配列

【名前の定義】

  • 名前

    DATEARRAY

  • コメント

    開始日から終了日までの日付を配列で返します

  • 参照範囲
=LAMBDA(開始日, 終了日, 間隔日数, 
    LET(
        fromInclusive, INT(開始日),
        toExclusive, INT(終了日),
        step, IF(ISOMITTED(間隔日数), 1, 間隔日数),
        IFS(
            fromInclusive >= toExclusive, "終了日は開始日より後の日付にしてください",
            step <= 0, "間隔日数は1以上にしてください",
            true, LET(
                days, INT((toExclusive - fromInclusive -1)/step)+1,
                SEQUENCE(days, 1, fromInclusive, step)
            )
        )   
    )
)

【構文】

DATERANGE(開始日, 終了日, 間隔日数)
DATERANGE(開始日, 終了日, )

【引数】

引数 内容
開始日 開始日のシリアル値か日付書式文字列
終了日 終了日のシリアル値か日付書式文字列
間隔日数 日付要素の日付間隔。省略可

【戻り値】

開始日から終了日までの日付の配列

【説明】

DATEARRAY 関数は、指定期間の日付を縦方向の配列として返し、結果はスピルします。

期間は「開始日」と「終了日」で指定します。 「終了日」は期間内に含まれません。

「間隔日数」には、出力したい日付の間隔を指定します。 例えば、これに1を指定すると日毎の日付となりますが、2 を指定すると、開始日から1日おきの配列となります。

「間隔日数」を省略(空欄)にすると、デフォルトで 1(1日)が指定されたものとして扱われます。

【使用例】

使用例 結果 コメント
=DATEARRAY(TODAY(), TODAY()+7, 1) 2022/5/1, 2022/5/2,・・・2022/5/7 今日から1週間
=DATEARRAY("2022/1/1", "2023/1/1",) 2022/1/1, 2022/1/2, ・・・2022/12/31 1 年
=DATEARRAY("2022/5/1", "2022/6/1", 7) 2022/5/1, 2022/5/7, ・・・2022/5/28 7日毎

範囲時刻配列

【名前の定義】

  • 名前

    TIMEARRAY

  • コメント

    指定範囲の時刻を配列で返します

  • 参照範囲
=LAMBDA(開始時刻, 終了時刻, 間隔時間,
    LET(
        _serToSec, LAMBDA(ser, ROUND(60*60*24*ser,0)),
        _secToSer, LAMBDA(sec, sec/(60*60*24)),
        fromInclusive, _serToSec(開始時刻),
        toExclusive, _serToSec(終了時刻),
        step, _serToSec(IF(ISOMITTED(間隔時間), "1:00", 間隔時間)),
        IFS(
            fromInclusive >= toExclusive, "終了時刻は開始時刻より未来の時刻にしてください",
            step <= 0, "間隔時刻は1秒以上にしてください",
            true, LET(
                times, INT((toExclusive - fromInclusive - 1)/step) + 1,
                _secToSer(SEQUENCE(times, 1, fromInclusive, step))
            )
        )
    )
)

【構文】

TIMEARRAY(開始時刻, 終了時刻, 間隔時間)
TIMEARRAY(開始時刻, 終了時刻,)

【引数】

引数 内容
開始時刻 開始時刻のシリアル値か時刻書式文字列
終了時刻 終了時刻のシリアル値か時刻書式文字列
間隔時間 時刻要素の時刻間隔。省略可

【戻り値】

指定の期間を間隔時間で刻んだ時刻の配列

【説明】

TIMEARRAY 関数は指定の時間範囲の時刻の配列を生成します。結果はスピルします。

指定の「開始時刻」から「終了時刻」までで「間隔時間」ごとの時刻を縦方向の配列として返します。 終了時刻自体は結果に含まれません。

「間隔時間」を省略(空欄)にすると、1 時間(1h)間隔の時刻配列となります。

時刻や間隔時間の最小単位は 1 秒(sec)です。

【使用例】

使用例 結果 コメント
=TIMEARRAY("9:00", "18:00",1) 9:00, 10:00, 11:00, ・・・,17:00
=TIMEARRAY("9:00", "18:00",) 9:00, 10:00, 11:00, ・・・,17:00 間隔時間省略
=TIMEARRAY("9:00", "18:00", "0:15") 9:00, 9:15, 9:30, ・・・,17:45 15分間隔
=TIMEARRAY("0:00", "1:00", "0:00:01") 0:00:00,
0:00:01,
0:00:02,
・・・,
0:59:59
1秒間隔
=TIMEARRAY(NOW(), NOW()+"0:00:10", "0:00:01") 2022/05/01 12:34:56,
2022/05/01 12:34:57,
2022/05/01 12:34:58,
・・・
2022/05/01 12:35:05
日付込み時刻

 



 

祝日一覧

【名前の定義】

  • 名前

    JPHOLIDAYS

  • コメント

    指定の年の祝日の一覧を取得します。

  • 参照範囲
=LAMBDA(年,
    LET(
        json2array, LAMBDA(json, LET(
            a, TRIM(SUBSTITUTE(json, UNICHAR(10), " ")),
            b, SUBSTITUTE(a, "{", "<a><b k="),
            c, SUBSTITUTE(b, ",", "/><b k="),
            d, SUBSTITUTE(c, ":", " v="),
            e, SUBSTITUTE(d, "}", "/></a>"),
            IF({1,0}, FILTERXML(e, "//@k"), FILTERXML(e, "//@v"))            
        )),
        api, "https://holidays-jp.github.io/api/v1/$year/date.json",
        req, SUBSTITUTE(api, "$year", 年),
        json, WEBSERVICE(req),
        json2array(json)
    )
)

【構文】

JPHOLIDAYS(年)

【引数】

引数 内容
西暦を表す4桁の整数(YYYY)(シリアル値ではありません)

【戻り値】

指定された年の日本の祝日の日付とその名称を配列として返します。 日付データはシリアル値です。

【説明】

年を表す整数(2022など)を引数に指定して JPHOLIDAYS 関数を呼び出すと、当年の日本の祝日1とその名称の一覧が配列で返され、結果はスピルします。

配列は、1列目が祝日の日付で、2列目がその名称です。 結果の配列から日付の列のみを取得したい場合は INDEX 関数などを使ってください。

=INDEX(JPHOLIDAYS(2022),,1)

JPHOLIDAYS 関数は外部サービス(API) から最新の祝日データを取得しているため、使用に際してはインターネットに接続できる環境にある必要があります。

無料で公開されている祝日 API にはいくつかありますが、JPHOLIDAYS 関数では「Holidays JP API」を使用させていただきました。

holidays-jp.github.io

取得できる過去の祝日は 2015 年以降となるようです。

【使用例】

使用例 結果 コメント
=JPHOLIDAYS(2022) (2022年の祝日一覧)
=JPHOLIDAYS(YEAR(TODAY())) (今年の祝日一覧)

祝日一覧は、営業日を算出する NETWORKDAYS 関数や WORKDAYS 関数に渡す祝日のデータとして使用できます。

その場合、INDEX 関数で日付のみの列を切り出してから渡してください。

セル 値/数式 コメント
C2 2022/5/1 月初
D2 =NETWORKDAYS(C2,EOMONTH(C2,0),INDEX(JPHOLIDAYS(YEAR(C2)),,1)) 営業日数

ちょっと使い勝手が悪いですね。

もとから日付のみの配列としたいなら、関数の中身を以下の物で差し替えてください。

=LAMBDA(年,
    LET(
        json2array, LAMBDA(json, LET(
            a, TRIM(SUBSTITUTE(json, UNICHAR(10), " ")),
            b, SUBSTITUTE(a, "{", "<a><b k="),
            c, SUBSTITUTE(b, ",", "/><b k="),
            d, SUBSTITUTE(c, ":", " v="),
            e, SUBSTITUTE(d, "}", "/></a>"),
            IF({1,0}, FILTERXML(e, "//@k"), FILTERXML(e, "//@v"))            
        )),
        api, "https://holidays-jp.github.io/api/v1/$year/date.json",
        req, SUBSTITUTE(api, "$year", 年),
        json, WEBSERVICE(req),
        INDEX(json2array(json),,1)
    )
)

この方が扱いやすいかもしれません。

セル 値/数式 コメント
C2 2022/5/1 月初
D2 =NETWORKDAYS(C2, EOMONTH(C2, 0), JPHOLIDAYS(YEAR(C2)),,1) 営業日数

【注意】
本関数を登録した Excel ファイルを開くと次のようなセキュリティー警告が表示されることがあります。
 

 
これは本関数が WEBSERVICE 関数を使って外部 Web サービス(API)にアクセスしているためですので、使用に際しては「コンテンツの有効化」をして問題ありません。
 
とはいえ一般に、心当たりがないのにこの警告が出た場合には、よく確認して、うかつに「コンテンツの有効化」ボタンを押さないように気を付けましょう。

【解説】日本の祝日データ

日本の祝日のデータは様々なシステムで使用されますが、大本のネタ元は内閣府の「国民の祝日について - 内閣府」 というページで公開されている syukujitsu.csv というファイルです。

1955 年からの来年まで祝日のリストが CSV 形式で提供されています。
年1回、2月ごろに追記更新されるようです。

国民の祝日・休日月日,国民の祝日・休日名称
1955/1/1,元日
1955/1/15,成人の日
1955/3/21,春分の日
1955/4/29,天皇誕生日
 :
 :
2023/8/11,山の日
2023/9/18,敬老の日
2023/9/23,秋分の日
2023/10/9,スポーツの日
2023/11/3,文化の日
2023/11/23,勤労感謝の日
本当は上記 JPHOLIDAYS関数でも、直接ここからデータを取り込みたかったのですが、#VALUE! エラーとなりうまくいきません。 Excel 数式で API からデータを取得するのに使う WEBSERVICE 関数には、取得できるファイルサイズに制限があり、syukujitsu.csv がそれを超えているようです。
 
Excel で syukujitsu.csv を取得したい場合には Power Query (「データの取得と変換」⇒「Web」から)を使うのが正攻法です。 あるいは Power Automate などで CVS ファイルとして定期的にダウンロードしておくのもいいでしょう。

祝日名

【名前の定義】

  • 名前

    JPDATENAME

  • コメント

    日付が祝日ならその名称を返します

  • 参照範囲
=LAMBDA(日付, LET(
    api, "https://api.excelapi.org/datetime/holiday",
    req, LAMBDA(dt, WEBSERVICE(api & "?date=" & INT(dt))),
    MAP(日付, req)
))

【構文】

JPDATENAME(日付)

【引数】

引数 内容
日付 名前を得たい日付、あるいは日付の配列

【戻り値】

指定した日付が祝日だった場合、その名称を返します。 祝日ではなかった場合、空文字列を返します、

【説明】

JPDATENAME 関数は指定の日付が日本の祝日にあたる場合に、その名称を取得することができます。 日付が祝日でなかった場合は空文字列が返ります。

引数には特定の日付のほか、複数の日付を参照や配列として渡すことも可能です。

JPDATENAME 関数も JPHOLIDAYS 関数と同様に、外部のサービス(API)を利用しています。 つまり、使用に際してはインターネットに接続できる環境にある必要があります。

JPDATENAME 関数が使う祝日 API には excelapi.org 様の 「ExcelAPI」 から「祝日の名称」を利用させていただきました。

excelapi.org

日本の祝日が制定された 1949 年以降の祝日が取得できるようです。

【使用例】

使用例 結果 コメント
=JPDATENAME(TODAY()) (今日が祝日ならその名所、そうでないなら空文字列) 今日の名称
=JPDATENAME("2022/05/03") 憲法記念日 日付文字列
=JPDATENAME(A1:A100) (今日が祝日ならその名所、そうでないなら空文字列) セル範囲で複数の日付を渡す

JPDATENAME 関数に本記事で紹介したMONTHDAYS関数の出力を渡せば、当月の祝日を表示することができます。 また、戻りが空文字列かどうかで条件付き書式を設定すれば、祝日の色分けなどにも使えます。

セル 数式 コメント
C3 =MONTHDAYS(C1) 本記事で紹介したMONTHDAYS関数で月の日付をスピル
D3 =JPDATEINAME(C3#) スピル範囲の参照を渡す。D列の列幅を狭める
C2:I32 =$C3<>"" 条件付き書式のルール数式

 



AFE ソース

Excel に Advanced Formula Environment アドインを導入されている上級者のために、本記事掲載の全てのユーザー定義関数を一括登録できるソースを用意しました。

以下のソースを AFE の Editor にコピー&ペーストしてください。 関数名の衝突を避けるため、任意の名前で専用の Namespace を用意することをお勧めします。

// 今日を乗り切るExcel研究所 
// 【Excel LAMBDA】日付・時刻操作編(その1)【ユーザー定義関数集】
// https://www.shegolab.jp/entry/excel-lambda-datetime-02

//-------------------------------------------------------------
// カレンダー
//-------------------------------------------------------------

/**
 * 当月日付リスト
 */
MONTHDATES
=LAMBDA(基準日,
    LET(
        lastEOM, EOMONTH(基準日, -1),
        thisEOM, EOMONTH(基準日, 0),
        SEQUENCE(thisEOM - lastEOM, 1, lastEOM + 1)
    )
)
;


/**
 * 当月カレンダー
 */
CALENDER
=LAMBDA(基準日,
    LET(
        som, EOMONTH(基準日, -1) + 1,
        eom, EOMONTH(基準日, 0),
        calFrom, som - mod(WEEKDAY(som)-1, 7),
        calTo, eom - WEEKDAY(eom) + 7,
        weeks, (calTo - calFrom + 1) / 7,
        SEQUENCE(weeks, 7, calFrom)
    )
)
;


/**
 * 範囲日付配列
 * - 終了日を含まない
 */
DATEARRAY
=LAMBDA(開始日, 終了日, 間隔日数, 
    LET(
        fromInclusive, INT(開始日),
        toExclusive, INT(終了日),
        step, IF(ISOMITTED(間隔日数), 1, 間隔日数),
        IFS(
            fromInclusive >= toExclusive, "終了日は開始日より後の日付にしてください",
            step <= 0, "間隔日数は1以上にしてください",
            true, LET(
                days, INT((toExclusive - fromInclusive -1)/step)+1,
                SEQUENCE(days, 1, fromInclusive, step)
            )
        )   
    )
)
;


/**
 * 範囲時刻配列
 * - 終了時刻を含まない
 */
TIMEARRAY
=LAMBDA(開始時刻, 終了時刻, 間隔時間,
    LET(
        _serToSec, LAMBDA(ser, ROUND(60*60*24*ser,0)),
        _secToSer, LAMBDA(sec, sec/(60*60*24)),
        fromInclusive, _serToSec(開始時刻),
        toExclusive, _serToSec(終了時刻),
        step, _serToSec(IF(ISOMITTED(間隔時間), "1:00", 間隔時間)),
        IFS(
            fromInclusive >= toExclusive, "終了時刻は開始時刻より未来の時刻にしてください",
            step <= 0, "間隔時刻は1秒以上にしてください",
            true, LET(
                times, INT((toExclusive - fromInclusive - 1)/step) + 1,
                _secToSer(SEQUENCE(times, 1, fromInclusive, step))
            )
        )
    )
)
;


/**
 * 日本の祝祭日
 */
JPHOLIDAYS
=LAMBDA(年,
    LET(
        json2array, LAMBDA(json, LET(
            a, TRIM(SUBSTITUTE(json, UNICHAR(10), " ")),
            b, SUBSTITUTE(a, "{", "<a><b k="),
            c, SUBSTITUTE(b, ",", "/><b k="),
            d, SUBSTITUTE(c, ":", " v="),
            e, SUBSTITUTE(d, "}", "/></a>"),
            IF({1,0}, FILTERXML(e, "//@k"), FILTERXML(e, "//@v"))            
        )),
        api, "https://holidays-jp.github.io/api/v1/$year/date.json",
        req, SUBSTITUTE(api, "$year", 年),
        json, WEBSERVICE(req),
        json2array(json)
    )
)
;
// - [国民の祝日について - 内閣府](https://www8.cao.go.jp/chosei/shukujitsu/syukujitsu.csv)
// - [祝日の一覧 \| ExcelAPI](https://excelapi.org/docs/datetime/holiday-list)
// - [Holidays JP API \(日本の祝日API\)](https://holidays-jp.github.io/)
// - [国民の祝日:日本](https://national-holidays.jp/about.html)
// - [[Google カレンダー \- 「日本の祝日」](https://calendar.google.com/calendar/ical/ja.japanese%23holiday%40group.v.calendar.google.com/public/basic.ics)

// 祝日名があると、NETWORKDAYS 関数やWORKDAYS関数に渡すときに使い勝手が悪い。
// 祝日名なしの日付のみの配列を返した方がいいかもしれない。
// JPHOLIDAYS
// =LAMBDA(年,
//     LET(
//         json2array, LAMBDA(json, LET(
//             a, TRIM(SUBSTITUTE(json, UNICHAR(10), " ")),
//             b, SUBSTITUTE(a, "{", "<a><b k="),
//             c, SUBSTITUTE(b, ",", "/><b k="),
//             d, SUBSTITUTE(c, ":", " v="),
//             e, SUBSTITUTE(d, "}", "/></a>"),
//             IF({1,0}, FILTERXML(e, "//@k"), FILTERXML(e, "//@v"))            
//         )),
//         api, "https://holidays-jp.github.io/api/v1/$year/date.json",
//         req, SUBSTITUTE(api, "$year", 年),
//         json, WEBSERVICE(req),
//         INDEX(json2array(json),,1)
//     )
// )
// ;


/**
 * 祝日名
 */
JPDATENAME
=LAMBDA(日付, LET(
    api, "https://api.excelapi.org/datetime/holiday",
    req, LAMBDA(dt, WEBSERVICE(api & "?date=" & INT(dt))),
    MAP(日付, req)
))
;
// - 配列でもOK
// - 祝日の名称 | ExcelAPI https://excelapi.org/docs/datetime/holiday


// EOF

参考資料

holidays-jp.github.io

excelapi.org

関連記事

www.shegolab.jp

変更履歴

  • [2022-06-12] 公開

  1. 「祝祭日リスト」としたかったのですが「国民の祝日について - 内閣府」には「祝日」や「休日」はあっても「祭日」という言葉がありあません。「祭日」は使われなくなったのでしょうか。今はなにか不正確か不適切な表現になっている可能性があるので「祝日リスト」としておきます。