静岡県三島市の税理士、松井元（@hajime_matsui）です。
こんにちは！
会計事務所での業務用の Excel ツール（VBA 込み）で、日付に関するデータを扱うことがあります。
セル上の日付から、「元号」「年」「月」「日」を抜き出す必要があることがあります。
さて、今日は以下のような日付に関するデータ
● 2019/10/31
● 令和1年10月31日
から、それぞれ「元号」「年」「月」「日」を抜き出す Excel VBA（マクロ）を紹介します。
このように分解する感じです ↓
・元号 令和
・年 1
・月 10
・日 31
1、Excel VBA（マクロ）で様々な日付表示から元号、年、月、日を抜き出す方法
Excel VBA（マクロ）で行うこと
さて、今回紹介する Excel VBA（マクロ）を組み込んだツールについてお話しします。
事務所で使っているとあるツールでは以下の「設定画面」シートで「CSVファイル取り込み」ボタンを押すと、マクロが実行して取り込みたい CSV ファイルを選択します。
選択した CSV ファイルのデータは、続きのマクロにより開かれて「試算表データ」シートにコピペされます。
そして、その取り込んだデータの中にある決算年月日の日付（上図）から、元号、年、月、日を抜き出して「設定画面」シートの会計期間の日付を埋めます。
そして、元号、年、月、日に分解した日付を使って、他のファイルの財務諸表（BS、PL）などの日付を埋めるようになっています。
貸借対照表（BS）↓
損益計算書（PL）↓
Excel VBA による一連の流れを整理しておくと以下のようになっています。
↓
●決算年月日の日付から「元号」「年」「月」「日」を抜き出して「設定画面」シートの会計期間の開始日と終了日を埋める
↓
●他のファイルの財務諸表（BS、PL）の日付を埋める
日付についての課題
さて、今回お話ししたい課題の内容について少々。
CSV 出力したデータを「試算表データ」シートに取り込む際に、CSVファイルの中身を Excelの「試算表データ」シートにコピペするわけですが、コピペされた日付について
●「文字列データ」として扱われる場合
の2通りの場合があるのです。
なぜいつも同じでないのか理由はよく分かりません。
セルの表示そのものは「令和1年10月31日」のように表示されますが、セルを選択した際に数式バーの表示は「日付データ」だったり「文字列データ」だったりするわけです。
そして、数式バーの表示のとおりに Excel シート上では扱われます。
「日付データ」として扱われている場合 ↓
「文字列データ」として扱われている場合 ↓
常に「2019/10/31」のように日付データで扱われれば、VBA の Year 関数、Month 関数、Day 関数を使うだけで年、月、日を抽出できますが、「令和1年10月31日」のように文字列として扱われる場合には VBA コードに工夫が必要です。
そのあたりのことを以下でお話しします。
2、「日付データ」と「文字列データ」の両方から元号、年、月、日を抜き出す VBA コード
さて、「日付データ」と「文字列データ」の両方から元号、年、月、日を抜き出す VBA コードを以下に示します。
全 VBA コードになります。
1つの Sub プロシージャと3つの Function プロシージャで成り立っています。
【Sub プロシージャ】
・Hiduke プロシージャ
【Function プロシージャ】
・Hi_Change プロシージャ
・Gengou プロシージャ
・Nen_Change プロシージャ
Hiduke プロシージャ の中で3つの Function プロシージャを呼び出しています。
Hiduke プロシージャ
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
Sub Hiduke() '決算年月日' Dim Kessan '決算年月日 分解' Dim Nen Dim Tsuki Dim Hi '期首年月日 分解' Dim Nen_Kisyu Dim Tsuki_Kisyu Dim Hi_Kisyu Worksheets("設定画面").Activate '「試算表データ」シートから決算年月日読み込み' Kessan = Worksheets("試算表データ").Cells(3, 2).Value '平成、令和の文字を含むか判定' If InStr(Kessan, "平成") > 0 Or InStr(Kessan, "令和") > 0 Then Kessan = Hi_Change(Kessan) Else End If '決算年月日から年、月、日を抽出' Nen = Year(Kessan) Tsuki = Month(Kessan) Hi = Day(Kessan) '期首の年月日' Nen_Kisyu = Nen - 1 Tsuki_Kisyu = Tsuki + 1 If Tsuki_Kisyu = 13 Then Tsuki_Kisyu = 1 Else End If Hi_Kisyu = 1 '自 日付の出力' Cells(15, 12).Value = Gengou(Nen_Kisyu, Tsuki_Kisyu) Cells(15, 13).Value = Nen_Change(Nen_Kisyu, Tsuki_Kisyu) Cells(15, 14).Value = Tsuki_Kisyu Cells(15, 15).Value = Hi_Kisyu '至 日付の出力' Cells(16, 12).Value = Gengou(Nen, Tsuki) Cells(16, 13).Value = Nen_Change(Nen, Tsuki) Cells(16, 14).Value = Tsuki Cells(16, 15).Value = Hi End Sub |
Hi_Change プロシージャ
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
Function Hi_Change(x) '「平成」「令和」の文字を消して西暦表示' Hi_Change = Replace(x, "平成30", "2018") Hi_Change = Replace(Hi_Change, "平成31", "2019") Hi_Change = Replace(Hi_Change, "令和1", "2019") Hi_Change = Replace(Hi_Change, "令和2", "2020") Hi_Change = Replace(Hi_Change, "令和3", "2021") Hi_Change = Replace(Hi_Change, "令和4", "2022") Hi_Change = Replace(Hi_Change, "令和5", "2023") Hi_Change = Replace(Hi_Change, "令和6", "2024") Hi_Change = Replace(Hi_Change, "令和7", "2025") Hi_Change = Replace(Hi_Change, "令和8", "2026") Hi_Change = Replace(Hi_Change, "令和9", "2027") Hi_Change = Replace(Hi_Change, "令和10", "2028") '年、月を / に置き換え、日を空白に置き換え Hi_Change = Replace(Hi_Change, "年", "/") Hi_Change = Replace(Hi_Change, "月", "/") Hi_Change = Replace(Hi_Change, "日", "") End Function |
Gengou プロシージャ
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
Function Gengou(x, y) '元号の判断' Select Case x Case Is <= 2018 Gengou = "平成" Case 2019 If y <= 4 Then Gengou = "平成" Else Gengou = "令和" End If Case Is >= 2020 Gengou = "令和" Case Else End Select End Function |
Nen_Change プロシージャ
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
Function Nen_Change(x, y) '西暦、和暦変換' Select Case x Case 2017: Nen_Change = 29 Case 2018: Nen_Change = 30 Case 2019 If y <= 3 Then Nen_Change = 31 Else Nen_Change = 1 End If Case 2020: Nen_Change = 2 Case 2021: Nen_Change = 3 Case 2022: Nen_Change = 4 Case 2023: Nen_Change = 5 Case 2024: Nen_Change = 6 Case 2025: Nen_Change = 7 Case 2026: Nen_Change = 8 Case 2027: Nen_Change = 9 Case 2028: Nen_Change = 10 Case 2029: Nen_Change = 11 Case 2030: Nen_Change = 12 End Select End Function |
3、VBA コード説明
コードの説明をしていきますね！
Hiduke プロシージャ
先にも示した Hiduke プロシージャです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
Sub Hiduke() '決算年月日' Dim Kessan '決算年月日 分解' Dim Nen Dim Tsuki Dim Hi '期首年月日 分解' Dim Nen_Kisyu Dim Tsuki_Kisyu Dim Hi_Kisyu Worksheets("設定画面").Activate '「試算表データ」シートから決算年月日読み込み' Kessan = Worksheets("試算表データ").Cells(3, 2).Value '平成、令和の文字を含むか判定' If InStr(Kessan, "平成") > 0 Or InStr(Kessan, "令和") > 0 Then Kessan = Hi_Change(Kessan) Else End If '決算年月日から年、月、日を抽出' Nen = Year(Kessan) Tsuki = Month(Kessan) Hi = Day(Kessan) '期首の年月日' Nen_Kisyu = Nen - 1 Tsuki_Kisyu = Tsuki + 1 If Tsuki_Kisyu = 13 Then Tsuki_Kisyu = 1 Else End If Hi_Kisyu = 1 '自 日付の出力' Cells(15, 12).Value = Gengou(Nen_Kisyu, Tsuki_Kisyu) Cells(15, 13).Value = Nen_Change(Nen_Kisyu, Tsuki_Kisyu) Cells(15, 14).Value = Tsuki_Kisyu Cells(15, 15).Value = Hi_Kisyu '至 日付の出力' Cells(16, 12).Value = Gengou(Nen, Tsuki) Cells(16, 13).Value = Nen_Change(Nen, Tsuki) Cells(16, 14).Value = Tsuki Cells(16, 15).Value = Hi End Sub |
決算年月日の読み込み
|
1 |
Kessan = Worksheets("試算表データ").Cells(3, 2).Value |
まず、「試算表データ」シートから決算年月日を読みます。
文字列データだった場合、日付データに変換
|
1 2 3 4 5 6 |
If InStr(Kessan, "平成") > 0 Or InStr(Kessan, "令和") > 0 Then Kessan = Hi_Change(Kessan) Else End If |
以下のように決算年月日が日付データとして扱われる場合と、文字列データとして扱われる場合があるので、両方に対応できるようにしてあります。
●日付データとして扱われる場合 2019/10/31
●文字列データとして扱われる場合 令和1年10月31日
IF 文で決算年月日に「平成」または「令和」の文字列が含まれるか否か判定し、日付データとして扱われているか、文字列データとして扱われているか調べています。
「平成」または「令和」が含まれる場合は、Hi_Change プロシージャによって文字列データを日付データに変換しています。
Year 関数、Month 関数、Day 関数によって決算年月日の日付データから年、月、日を抜き出す
|
1 2 3 |
Nen = Year(Kessan) Tsuki = Month(Kessan) Hi = Day(Kessan) |
それぞれの関数で、日付データ（2019/10/31）から「年」「月」「日」を抜き出しています。
・Year 関数で「年」
・Month 関数「月」
・Day 関数で「日」
Year 関数、Month 関数、Day 関数は引数を日付データとして、以下のように記述して使います。
・Year（日付データ）
・Month（日付データ）
・Day（日付データ）
・Year 関数は西暦年の数字を
・Month 関数は 1～12 の数字を
・Day 関数は 1～31 の数字を
を返します。
期首の年月日
|
1 2 3 4 5 6 7 8 9 |
Nen_Kisyu = Nen - 1 Tsuki_Kisyu = Tsuki + 1 If Tsuki_Kisyu = 13 Then Tsuki_Kisyu = 1 Else End If Hi_Kisyu = 1 |
決算の「年」「月」「日」から期首の「年」「月」「日」を計算しています。
期首の「年」は決算年から1マイナス。
期首の「月」は決算月に1プラス、ただし決算年が 12の場合は1としています。
期首の「日」は 1としています。
「設定画面」シートに元号、年、月、日を出力
|
1 2 3 4 5 6 7 8 9 10 11 |
'自 日付の出力' Cells(15, 12).Value = Gengou(Nen_Kisyu, Tsuki_Kisyu) Cells(15, 13).Value = Nen_Change(Nen_Kisyu, Tsuki_Kisyu) Cells(15, 14).Value = Tsuki_Kisyu Cells(15, 15).Value = Hi_Kisyu '至 日付の出力' Cells(16, 12).Value = Gengou(Nen, Tsuki) Cells(16, 13).Value = Nen_Change(Nen, Tsuki) Cells(16, 14).Value = Tsuki Cells(16, 15).Value = Hi |
「設定画面」シートに期首、期末の元号、年、月、日を出力しています。
元号は Gengou プロシージャで計算し、年は Nen_Change プロシージャで西暦を和暦に変換しています。
Hi_Change プロシージャ
先にも示した Hi_Change プロシージャです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
Function Hi_Change(x) '「平成」「令和」の文字を消して西暦表示' Hi_Change = Replace(x, "平成30", "2018") Hi_Change = Replace(Hi_Change, "平成31", "2019") Hi_Change = Replace(Hi_Change, "令和1", "2019") Hi_Change = Replace(Hi_Change, "令和2", "2020") Hi_Change = Replace(Hi_Change, "令和3", "2021") Hi_Change = Replace(Hi_Change, "令和4", "2022") Hi_Change = Replace(Hi_Change, "令和5", "2023") Hi_Change = Replace(Hi_Change, "令和6", "2024") Hi_Change = Replace(Hi_Change, "令和7", "2025") Hi_Change = Replace(Hi_Change, "令和8", "2026") Hi_Change = Replace(Hi_Change, "令和9", "2027") Hi_Change = Replace(Hi_Change, "令和10", "2028") '年、月を / に置き換え、日を空白に置き換え Hi_Change = Replace(Hi_Change, "年", "/") Hi_Change = Replace(Hi_Change, "月", "/") Hi_Change = Replace(Hi_Change, "日", "") End Function |
文字列データを日付データに変換するためのプロシージャです。
令和1年10月31日 → 2019/10/31
Replace 関数によって和暦を西暦に置き換えています。
平成30 → 2019
令和1 → 2019
令和2 → 2020
令和3 → 2021
令和4 → 2022
・
・
・
また、「年」「月」の文字を「/」に置き換え、「日」の文字を空白に置き換えています。
Replace 関数は以下のように記述して使います。
Replace（”abcde”,”c”,”z”）
このように↑ 記述すると、文字列「abcde」の中に含まれる「c」を「z」に置き換えます。
Gengou プロシージャ
先にも示した Gengou プロシージャです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
Function Gengou(x, y) '元号の判断' Select Case x Case Is <= 2018 Gengou = "平成" Case 2019 If y <= 4 Then Gengou = "平成" Else Gengou = "令和" End If Case Is >= 2020 Gengou = "令和" Case Else End Select End Function |
年、月から元号を出すためのプロシージャです。
Select Case 文を使って、x（西暦年）で場合分けしています。
●x（西暦年）が 2018 以下の場合
・・・「平成」
●x（西暦年）が 2019 の場合
・・・y（月）が4以下であれば「平成」、それ以外の場合は「令和」
●x（西暦年）が 2020 以上の場合
・・・「令和」
Nen_Change プロシージャ
先にも示した Nen_Change プロシージャです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
Function Nen_Change(x, y) '西暦、和暦変換' Select Case x Case 2017: Nen_Change = 29 Case 2018: Nen_Change = 30 Case 2019 If y <= 3 Then Nen_Change = 31 Else Nen_Change = 1 End If Case 2020: Nen_Change = 2 Case 2021: Nen_Change = 3 Case 2022: Nen_Change = 4 Case 2023: Nen_Change = 5 Case 2024: Nen_Change = 6 Case 2025: Nen_Change = 7 Case 2026: Nen_Change = 8 Case 2027: Nen_Change = 9 Case 2028: Nen_Change = 10 Case 2029: Nen_Change = 11 Case 2030: Nen_Change = 12 End Select End Function |
西暦年を和暦年に変換するためのプロシージャです。
先と同様に、Select Case 文を使っています。
x（西暦年）が2018年の場合だけは、さらに If 文を使って
y（月）が4以下の場合は「年」を 31 とし、それ以外の場合は「年」を 1 として、平成と令和の変わり目に対応できるようにしています。
3、まとめ
以下のような日付に関するデータ
● 2019/10/31
● 令和1年10月31日
から、以下のようにそれぞれ「元号」「年」「月」「日」を抜き出す Excel VBA（マクロ）を紹介しました。
・元号 令和
・年 1
・月 10
・日 31
VBA で日付を扱うのはなかなか難しいです。
表示と内部での認識が合っていなかったりでけっこう手を焼きます。。
それをある意味強引に何とかしたのか今回の方法になります。
編集後記
昨日（6/30）は、JR 静岡駅前にある静岡科学館 るくるに家族で行ってきました。
科学を体験を通して、やさしく学べます ^^
小学校低学年以下の子供がいる家庭にはオススメですね！
また、記事にします。
