和風スパゲティのレシピ

日本語でコーディングするExcelVBA

トップ > 日付・時刻 > 4月から3月を連番に変換してコードを整理する

4月から3月を連番に変換してコードを整理する

日付・時刻 サンプルコード VBA

日本企業でエクセルを使う場合、「年度」で日付を管理したい場面が非常に多いです。

年度を求めたりといった、直接的な計算もさることながら、

For m = 4 To 12
   月ごとの処理
Next

For m = 1 To 3
   ほどんど同じコードで、yを1引いたり、mに12を足したりした処理
Next

こういうループや条件分岐もまた、年度のためにやっている処理ですね。

ワークシートを月ごとにコピーしたり、月別の集計表を計算したり、
並びが「4,5,6,…11,12,1,2,3」にならないといけない場合などでよく登場します。

これをもっとすっきりしたコードにするために、
「4,5,6,7,8,9,10,11,12,1,2,3」⇔「1,2,3,4,5,6,7,8,9,10,11,12」を対応させる関数を考えましょう。

ソースコード

' 月→第n月
Function 月を年度内の月番号に変換する(ByVal 月 As Long) As Long
    If 月 >= 4 And 月 <= 12 Then
        月を年度内の月番号に変換する = 月 - 3
    ElseIf 月 >= 1 And 月 <= 3 Then
        月を年度内の月番号に変換する = 月 + 9
    Else
        Call Err.Raise(1000, , "月が1～12の値でありません。")
    End If
End Function

' 第n月→月
Function 年度内の月番号を月に変換する(ByVal 月番号 As Long) As Long
    If 月番号 >= 1 And 月番号 <= 9 Then
        年度内の月番号を月に変換する = 月番号 + 3
    ElseIf 月番号 >= 10 And 月番号 <= 12 Then
        年度内の月番号を月に変換する = 月番号 - 9
    Else
        Call Err.Raise(1000, , "月番号が1～12の値でありません。")
    End If
End Function

使い方

「４～１２月からは３を引いて、１～３月には９を足している」
だけですので、解説は省きます。

冒頭のFor文は↓のように書き換わります。

Dim 月No As Long
For 月No = 1 To 12
    Dim m As Long
    m = 年度内の月番号を月に変換する(月No)
    
    年度順に処理したい、月ごとの処理
    
Next

ループが1本になって、スッキリしましたね。


他の使用例としては、

月別集計表サンプル

こんな表で、月に該当するセルアドレスを指定するとき、

Cells(R, 2 + 月を年度内の月番号に変換する(m))

Range("B3").Offset(, 月を年度内の月番号に変換する(m))

' ↑ 8月を入れるとG3、2月を入れるとM3になるセルアドレスを、一本の式で取得できる。

という風に、月ごとの列番号の指定を、1発でできるようになったりします。


年度を扱う他のユーティリティ兄弟たち
　・「年度」を使って日付を求めるDateSerial関数
　・日付から「年度」を求める関数
はめちゃくちゃ便利で、それと比べるとこの「月の連番化」は、
わかりづらくて使い勝手もそこまで良くないです。

でも、たまーに登場したときは、20行くらいのIfブロックを消したり、超長い計算式を1本にしたり、いぶし銀な働きをする関数ですので覚えてあげてください。

頭の片隅に置いておくと、いつか幸せになれるかもしれません。

関数名について

関数名の命名の基本は、

です。


なんですが、この関数は例外に当たります。

その例外とは「コードを読みやすくするために、式を短くすることが使命の関数」です。


冒頭のコードでは、 「月を年度内の月番号に変換する」という関数名でしたが、


月別集計表サンプル

この表って、「対象のセルアドレスに、1を足す」とかやりそうなんですよね。

そのとき、

Cells(R, 2 +月を年度内の月番号に変換する(m)) = Cells(R, 2 + 月を年度内の月番号に変換する(m)) + 1

Cells(R, 2 + 月連番(m)) = Cells(R, 2 + 月連番(m)) + 1

絶対下の方がわかりやすいですよね？


計算式やセルアドレスの内部に頻出する関数は、「短さ＝可読性」になるので、丁寧な関数名にこだわりすぎないようにしましょう。


今回の関数名は、「月連番」と「Rev月連番」あたりがいい感じだと思います。

書き直したコピペ用のコードを再掲して終わりますね。

' 月を年度内の連番で扱うための変換関数
' 対応：「4,5,6,7,8,9,10,11,12,1,2,3」⇔「1,2,3,4,5,6,7,8,9,10,11,12」
Function 月連番(ByVal 月 As Long) As Long
    If 月 >= 4 And 月 <= 12 Then
        月連番 = 月 - 3
    ElseIf 月 >= 1 And 月 <= 3 Then
        月連番 = 月 + 9
    Else
        Call Err.Raise(1000, , "月が1～12の値でありません。")
    End If
End Function
Function Rev月連番(ByVal 月No As Long) As Long
    If 月No >= 1 And 月No <= 9 Then
        Rev月連番 = 月No + 3
    ElseIf 月No >= 10 And 月No <= 12 Then
        Rev月連番 = 月No - 9
    Else
        Call Err.Raise(1000, , "月Noが1～12の値でありません。")
    End If
End Function