85本目 請求日から入金予定日を算出

VBA100本ノック 85本目を動画で解説しています。Excelの神髄さんの模範解答はコチラ

YouTube

作成した動画を友だち、家族、世界中の人たちと共有

www.youtube.com

---

問題

請求日から入金予定日を算出するユーザー定義関数を作成。
「取引先マスタ」「支払パターン」を基に決定します。
支払パターン
・締め日：5,10,15,20,25,末
・支払月：0~6月後 ※n=0は当月、n=1は翌月
・支払日：5,10,15,20,25,末
入金日が土日祝日の場合は前日にする。

↓「取引先マスタ」シート

↓「支払パターン」シート

↓「祝日マスタ」シート

↓ユーザー定義関数を入れてこの結果になればOK！

解答

Function 入金予定日(取引先 As String, 請求日 As Date) As Date

    Dim wsマスタ As Worksheet: Set wsマスタ = Sheets("取引先マスタ")
    Dim ws支払P As Worksheet: Set ws支払P = Sheets("支払パターン")
    Dim ws祝日マスタ As Worksheet: Set ws祝日マスタ = Sheets("祝日マスタ")
    Dim F As WorksheetFunction: Set F = WorksheetFunction
    Dim 支払P, 締め日, 支払月, 支払日
    支払P = F.XLookup(取引先, wsマスタ.Range("A:A"), wsマスタ.Range("C:C"), "")
    締め日 = F.XLookup(支払P, ws支払P.Range("A:A"), ws支払P.Range("B:B"), "")
    支払月 = Left(F.XLookup(支払P, ws支払P.Range("A:A"), ws支払P.Range("C:C"), ""), 1)
    支払日 = F.XLookup(支払P, ws支払P.Range("A:A"), ws支払P.Range("D:D"), "")
     
    If 締め日 = "末" Then
        締め日 = DateSerial(Year(請求日), Month(請求日) + 1, 0)
    Else
        If Day(請求日) > 締め日 Then
            締め日 = DateSerial(Year(請求日), Month(請求日) + 1, 締め日)
        Else
            締め日 = DateSerial(Year(請求日), Month(請求日), 締め日)
        End If
    End If
    If 支払日 = "末" Then
        支払日 = DateSerial(Year(締め日), Month(締め日) + 支払月 + 1, 0)
    Else
        支払日 = DateSerial(Year(締め日), Month(締め日) + 支払月, 支払日)
    End If
    
    Do Until F.CountIf(ws祝日マスタ.Range("A:A"), 支払日) = 0 And Weekday(支払日, 2) < 6
        支払日 = 支払日 - 1
    Loop
    
    入金予定日 = 支払日

End Function

今回はシンプルに難しかった(考える内容が多かった)です…

考えるべき要件が多くて実務で活かせそうなことが満載でした☆

考え方・流れ

①取引先コード、請求日を引数にとるファンクションを作成

➁引数の”取引先”を使って、「支払パターン」シートから必要な情報を取得。

③正確な締め日を求める。
※支払パターンマスタでは”末”的な情報しか載っていない→日付を求める

この辺りは少し音声をゆっくりにしてます…。
当たり前のことを言ってるだけのはずなのに…ムズイ…。

④支払い日を求める
※締め日が求められたらこっちは100倍簡単かな？

⑤支払日が土日祝のいずれかだった場合の分岐

Do Loopさんで、支払日が土日祝じゃなくなるまで”支払日-1″を繰り返せばOK。
以上で完成っす～

あとがたり

おはこんばんちは。uぷ主です。
85本目、請求日から支払予定日を算出する問題でした。

今回もムズイ！
ムズイというか…考えることが多すぎでござる！

なんで月によって開始する曜日が違うねん…とか、なんで毎月の日数って違うねん…とか、なんで祝日があるねん…とか、なんで企業ごとに支払い締め日が違うねん…などなど。

ま、でも実際に働いてたらこんなことばっかりですよねw
こういうことを全部手動でやるのか、しっかりとロジックを立てて関数やVBAでやるのか。
これをできるかどうかが事務職の腕の見せ所なのかな～と思います。

ちなみにuぷ主が実際にこの作業をやるとしたら…
もうちょっとシート上で関数やらマスタやら当てて…なんやかんやするかなw

VBAで全部やろうとするとま～じでムズイ…！！
てかむずかった！

その分、今回も非常に勉強になりましタ～～～～！
次からはVBAでもなんやかんやできそうです！

ありがとうございました。

そして最後までご視聴ありがとうございました。