日付計算の高度な応用 – 便利な関数を駆使しよう

2023.04.06

はじめに

前回のシリーズでは、DATEDIF関数の基本的な使い方や、それを応用した営業日数の計算などを紹介しました。今回のシリーズでは、さらに応用的な日付計算や関数を紹介していきます。効率的な日付計算をマスターし、業務をスムーズに進めることができるようになりましょう。

条件付き日付計算

条件付き日付計算では、特定の条件に合致する日付だけを対象に計算を行います。例えば、ある期間内の休日の数を計算したり、特定の曜日が何回含まれるかを調べることができます。

IF関数と組み合わせた条件付き日付計算

IF関数を利用して、条件に応じて日付計算を行う方法を紹介します。例として、ある期間内に休日が何日あるかを計算してみましょう。

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)>5))

ここで、A1に開始日、B1に終了日を入力します。この式では、WEEKDAY関数を使って曜日を求め、土曜日と日曜日（6と7）が対象となるように条件を設定しています。

この式が少し複雑に見えるかもしれませんが、1つずつ分解して説明しましょう。

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)>5))
  1. まず、INDIRECT(A1&":"&B1) の部分です。これは、セル A1 と B1 に入力された日付を文字列として結合し、その範囲を参照します。例えば、A1 に “2023-01-01” 、B1 に “2023-12-31” が入力されている場合、INDIRECT("2023-01-01:2023-12-31") となり、その期間を参照します。
  2. 次に、ROW(INDIRECT(A1&":"&B1)) の部分です。これは、上記で得られた日付範囲の行番号を配列として取得します。これにより、期間中の日付が配列として得られます。
  3. WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2) の部分では、上記で得られた日付配列に対して、WEEKDAY 関数を適用します。第二引数の 2 は、月曜日を 1 とし、日曜日を 7 とする設定です。これにより、各日付の曜日番号が配列として得られます。
  4. 次に、WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)>5 の部分では、上記で得られた曜日番号が 5 より大きいかどうかを判定します。土曜日が 6 、日曜日が 7 なので、この条件式は土日に該当する場合に TRUE となります。
  5. --(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)>5) の部分では、上記で得られた TRUE/FALSE の配列を、1 と 0 に変換します。具体的には、土曜日および日曜日に該当する場合に 1 、それ以外の場合に 0 が格納された配列が得られます。
  6. 最後に、SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)>5)) の部分では、上記で得られた 1 と 0 の配列の合計を計算します。これにより、土曜日および日曜日の数が求まります。

このように、複数の関数を組み合わせて使用することで、特定期間内の土曜日と日曜日の数を計算することができます。これにより、エクセルを使ってさまざまな日付計算を行うことが可能になります。

これらの計算は応用的なものであるため、実際に試しながら理解していくことが重要です。自分で実際にデータを入力して計算してみることで、より深く理解できるでしょう。

なお、この例では土曜日と日曜日を計算しましたが、他の曜日や条件に応じて計算式を変更することで、様々な状況に対応できます。

複数の日付関数を組み合わせる

複数の日付関数を組み合わせることで、より高度な日付計算が可能になります。ここでは、EOMONTH関数やWORKDAY関数を組み合わせて、特定の期間内で営業日のみを対象にした複雑な日付計算を行う例を紹介します。

=NETWORKDAYS(A1, EOMONTH(A1, 1))

この式では、A1に開始日を入力し、EOMONTH関数を使って1ヶ月後の月末日を求め、NETWORKDAYS関数で営業日数を計算しています。

カスタム日付関数の作成

Excelには、ユーザーが独自の関数を作成する機能があります。これにより、独自の日付計算が可能になります。VBA（Visual Basicfor Applications）を利用して、カスタム日付関数を作成してみましょう。

カスタム日付関数の例：特定の曜日の回数を数える

特定の期間内に指定した曜日が何回含まれるかを計算するカスタム関数を作成してみます。

  1. Excelの「開発」タブをクリックし、「Visual Basic」ボタンを選択してVBAエディタを開きます。
  2. 「挿入」メニューから「モジュール」を選択して新しいモジュールを追加します。
  3. 以下のコードをモジュールに貼り付けます。
Function CountWeekday(StartDate As Date, EndDate As Date, TargetWeekday As Integer) As Integer
    Dim DayCount As Integer
    Dim CurrentDate As Date
    DayCount = 0
    CurrentDate = StartDate
While CurrentDate <= EndDate
    If Weekday(CurrentDate) = TargetWeekday Then
        DayCount = DayCount + 1
    End If
    CurrentDate = CurrentDate + 1
Wend

CountWeekday = DayCount
End Function

これで、カスタム関数「CountWeekday」が作成されました。Excelシート上で以下のように使ってみましょう。

=CountWeekday(A1, B1, 2)

A1に開始日、B1に終了日、2は月曜日を示す数字です。曜日は1（日曜日）から7（土曜日）の整数で指定します。

まとめ

今回のシリーズでは、条件付き日付計算、複数の日付関数を組み合わせた計算、そしてカスタム日付関数の作成について学びました。これらの高度な応用をマスターすることで、日付計算を効率化し、業務をスムーズに進めることができるでしょう。

演習問題

  1. 2023年1月1日から2023年12月31日までの期間に、水曜日が何回あるかを求めてください。
  2. 2023年4月1日から2023年4月30日までの期間に、営業日が何日あるかを求めてください。

解答と解説

    1. 解答: 52回

解説: CountWeekday関数を利用して、次のように計算します。

=CountWeekday("2023-01-01", "2023-12-31",4)

2023年1月1日から2023年12月31日までの期間に、水曜日（4）が52回含まれます。

    1. 解答: 21営業日

解説: NETWORKDAYS関数を利用して、次のように計算します。

=NETWORKDAYS("2023-04-01", "2023-04-30")

2023年4月1日から2023年4月30日までの期間に、営業日が21日あることが分かります。

以上で、シリーズ3の演習問題と解答が終了しました。日付計算に関する高度な知識や技術を身につけることで、より効率的な作業が可能になります。今後も様々な関数や応用例を学び、スキルを磨いていきましょう。

コメント