見出し画像

エクセルVBAで内閣府の祝日データCSVを使って楽したい

(ぐだぐだのすいっち)

内閣府が公開してくれている祝日のデータを楽して活用したいと思ったのが始まりです

国民の祝日について - 内閣府 (cao.go.jp)
CSVで公開されているのですが、エクセルに聡くないうちにはどう扱うのが効率が良いのか判りません。
うち的に楽ちんかなと思ったのは

「データ」から「テキストまたはCSVから」メニュー

上書きしてくれるみたいだし、楽ちん楽ちん。
※自宅でやったら上書きしてくれなかった(ToT)環境によって違うのかな？設定の問題？でもクエリーという機能で連携しているようだから更新の手段はあるのかな？まあ、年末に判るでしょう（良いのかそれで）
エクセルにはワークシート関数にWORKDAYと言う関数があって、祝日のデータを絡ませることが出来るみたいで便利そうなんですけど、実は、この方法で読み込むとWORKDAY関数にとってはお役に立てないようです。
シリアル値になってないから反応しないのかな…？うちには判らないので、変換とか絡ませるとめんどくさいので、楽ちんなCSV読み込みを活かす方向でどうするかを考える。
というか、VBAでなんとかするよね？まあ、そうですね

活かしたいこととしてはある月の締め日とか、請求書の到着日とか、支払期限とかの日にちを算出したいこと。
WORKDAY関数は、休日を除外した営業日を返してくれる優れもの。
VBAでWORKDAY関数の機能を再現すれば良いのでは？
でも、考えると意外と面倒だったので、やりたいことにのみ集中して考える。
最初はまず、祝日かどうかを判別するところかな？？？
正攻法でいくのなら読み込んだシートの最終行を調べて2行目から照会して当たったら祝日だよって返す。
万って単位の行数になったら非力なマシンだとうんざりかもだけど、最近のマシンは良くなっているから大丈夫だよね？うん、そう思いましょう。
最終行を調べる方法はいろいろありますが、うちがよく使う手はUsedRange関数を使う手法。
人間が打ち込んだシートだと不正確になりがちだけど、今回のようにCSVを楽ちんな方法で読み込んだ場合は、読み込んだ分のデータしか存在しないのが通常（もちろん、ユーザーが触ってしまえば話は別）なので、UsedRangeで十分。

usedrange.rows.countで最終行が判ります

usedrange.rows.countで引くと範囲内の行数を返してくれるので、これで最終行が判るわけです。
※UsedRangeで得られる範囲は使用されているとエクセルが認識している範囲です。1行目が開いて居る場合は2行目からの範囲という認識の元での行数ということになるので、最終行を示すものとは言い切れない場合が出てきます。こういう恐れがある場合は、UsedRange.addressで範囲を示す文字列で受け取って、後ろの座標を.rowで何行目かを引っ張り出す方が確実です。
CSVから読み込んだシートを眺めたら判ると思いますけど、1955から記載されています。
頭から総当たりしていたら結構なオーバーヘッド処理になりますね(^_^;
祝日の中に、参照する日付が存在するかどうかを調べたいだけなので、後ろから調べましょう。
そしてヒットしたら抜け出てしまえばオーバーヘッドは最小になります。

Function syuk_day(serial_days)
    '祝日なら、それ以外なら0
    Dim ws, xrow, dmm, cnt
    Set ws = ThisWorkbook.Worksheets("syukujitsu")
    xrow = ws.UsedRange.Rows.Count
    For cnt = xrow To 2 Step -1
        If DateValue(serial_days) = DateValue(ws.Cells(cnt, 1)) Then
            syuk_day = 1
            Set ws = Nothing
            Exit Function
        End If
    Next
    Set ws = Nothing
    syuk_day = 0
End Function

set を使ってワークシートを変数に閉じ込めているのは
Thisworkbooks.worksheets("syukujitsu")
と言う呪文を何度も入力したくないからです(^_^;手抜きは大事ですよ(^_^;
このFunctionで大切なのはDateValueを使って、シリアル値に変換していることです。
意外とあるあるなのですが、エクセルさんがお利口すぎて、セルの見た目が日付でも、実際に入力されている値が日付としての値であるとは限らないことです。
CSVをテキストエディタもしくはエクスプローラーのプレビューで見たら判るのですが、

CSVデータのプレビュー

日付は文字列として記載されています。
エクセルは、日付はシリアル値として定義しているので見た目は同じでもVBAから見たら別なものなのです。
だから、同じものかどうかを比較するときは、同じ形式に変換しないと正しく比較できません。
なので、DateValue関数で、シリアル値に変換してから比較しています。
試験に出ますので、要チェックですよ（何の試験？？？）
このFunction役割は、ヒットしたら1を返し、ヒットしなかったら0を返すことです。
メインは、次のFunctinです。

Function xworkday(serial_days)
    Dim cnt, hei_day, tag_day
    For cnt = 0 To 10
        tag_day = DateAdd("d", cnt * -1, serial_days)
        If Weekday(tag_day, vbMonday) < 6 Then hei_day = 0 Else hei_day = 1     '平日は1、休日は0
        If hei_day + syuk_day(tag_day) = 0 Then
            xworkday = tag_day
            Exit Function
        End If
    Next
End Function

For cnt = 0 to 10
のくだりはDo Loopにした方が良いのでしょうけど、うちがDo Loopが大の不得意なので、Forで逃げています(ToT)
いや、Do Loop使うと、なぜか無限ループに入るんですよね…何でなのでしょうね？？？
DateAdd("d", cnt * -1, serial_days)
で、参照する日付を1日ずつ、前にしていきます。
If Weekday(tag_day, vbMonday) < 6 Then hei_day = 0 Else hei_day = 1
で、月曜から金曜までなら、0を、土日なら1を変数hei_dayに代入しています。
そして
If hei_day + syuk_day(tag_day) = 0 Then
で、前述のFuncitonの結果と合算すれば
平日でなおかつ祝日でもない場合は0になります。
土日なら1になり、平日であっても祝日だった場合も1になります。
この結果を返すのがこのFunctionの役目です。エッヘン。

実際に、どう使うかというと、例えば締め日を月末として、この日の前倒しの営業日を算出したいと言う場合には、次のようなFunctionを作れば良いわけです。

Function sime_days(serial_days)
    Dim dmm
    dmm = DateAdd("m", 1, serial_days)
    sime_days = xworkday(DateAdd("d", -1, DateSerial(Year(dmm), Month(dmm), 1)))
End Function

dmm = DateAdd("m", 1, serial_days)
で一ヶ月後のシリアル値を算出して、
DateAdd("d", -1, DateSerial(Year(dmm), Month(dmm), 1))
で一ヶ月後の月の1日の前日を調べる訳です。
同様に請求書発行日として翌月の10日の前倒し営業日を調べる場合は

Function seikyu_in(serial_days)
    Dim dmm
    dmm = DateAdd("m", 1, serial_days)
    seikyu_in = xworkday(DateSerial(Year(dmm), Month(dmm), 10))
End Function

こんな感じに使います。
ついでに支払期限が翌々月の月末問い場合は

Function harai_days(serial_days)
    Dim dmm
    dmm = DateAdd("m", 3, serial_days)
    harai_days = xworkday(DateAdd("d", -1, DateSerial(Year(dmm), Month(dmm), 1)))
End Function

こんな感じでしょうか。
月末を算出する場合は、翌月の1日の前日を、それ以外は○日を与えれば算出できるということになりますね。

エクセルのマクロは便利なもので、Functionで構成されたブロックはユーザー定義関数として、ワークシート関数と同等の取り扱いが出来ます。
つまり

ユーザー定義関数としてこき使いましょう

こんな感じでワークシートで使うことが出来るようになります。
ちなみにですが、この例では、B1に月初の日付を入力してあり、それを参照しています。
で、このB1の部分が
Function sime_days(serial_days)
のserial_daysと置き換えられます。
Function sime_days(B1)
というイメージです。
Functionから、呼び出し元に答えを返すには、Functionの名前を変数に見立てて、その変数に返したい答えを代入することになります。
例えば、Function sime_days(serial_days)の場合は、sime_daysという変数に返したい答えを代入してやればユーザー定義関数の答えとしてセルに反映されます。

何に使うのか？
うちの場合は、職場で月間カレンダーで、土日祝日はグレーにして識別しやすくしていたりを手作業でちまちま作っていたので、楽ちんをするためにです。
締め日や請求書がらみの話は、なんか、お客さんに見えるように毎月日付を明示しなくてはいけないらしく、カレンダー見て判断していたのは面倒じゃないですか？
が発端ですね。
はい、楽をするためなら苦労をいとわない人間です、ハイ。

でも、最近はローコードとか言う存在に脅かされてエクセルさんの出番はめっきり少なくなりましたので、うちの存在価値もどんどん小さくなっていくんだろうな…と、思う今日この頃です。
引きこもって、ずっとマクロで遊んでいたいです。

いいなと思ったら応援しよう！

この記事が参加している募集

#仕事について話そう

116,058件