スポンサーリンク

Excel VBA(マクロ)で様々な日付表示から元号、年、月、日を抜き出す方法

ブログを読んで頂いている方を対象にオフ会を開催させて頂きます!!
◆8/10(土)オフ会 in 静岡市
セミナー情報です!
◆8/23、9/20 30代半ばからの「税理士試験 & 大学院」セミナー

静岡県三島市の税理士、松井元(@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)↓

貸借対照表(BS)

損益計算書(PL)↓

損益計算書(PL)

Excel VBA による一連の流れを整理しておくと以下のようになっています。

●取り込みたい CSV ファイルを選択し「試算表データ」シートに取り込む

●決算年月日の日付から「元号」「年」「月」「日」を抜き出して「設定画面」シートの会計期間の開始日と終了日を埋める

●他のファイルの財務諸表(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 プロシージャ

Hi_Change プロシージャ

Gengou プロシージャ

以下については、リンク先にある個別のフォームよりお願い致します。-----------------------
◆8/10(土)オフ会 in 静岡市
◆8/23、9/20 30代半ばからの「税理士試験 & 大学院」セミナー
◆メルマガ登録

Nen_Change プロシージャ

3、VBA コード説明

コードの説明をしていきますね!

Hiduke プロシージャ

先にも示した Hiduke プロシージャです。

決算年月日の読み込み

まず、「試算表データ」シートから決算年月日を読みます。

「試算表データ」シートから決算年月日を読む

文字列データだった場合、日付データに変換

以下のように決算年月日が日付データとして扱われる場合と、文字列データとして扱われる場合があるので、両方に対応できるようにしてあります。

●日付データとして扱われる場合 2019/10/31
●文字列データとして扱われる場合 令和1年10月31日

IF 文で決算年月日に「平成」または「令和」の文字列が含まれるか否か判定し、日付データとして扱われているか、文字列データとして扱われているか調べています。

「平成」または「令和」が含まれる場合は、Hi_Change プロシージャによって文字列データを日付データに変換しています。

Year 関数、Month 関数、Day 関数によって決算年月日の日付データから年、月、日を抜き出す

それぞれの関数で、日付データ(2019/10/31)から「年」「月」「日」を抜き出しています。

・Year 関数で「年」
・Month 関数「月」
・Day 関数で「日」

Year 関数、Month 関数、Day 関数は引数を日付データとして、以下のように記述して使います。

・Year(日付データ)

・Month(日付データ)

・Day(日付データ)

・Year 関数は西暦年の数字を

・Month 関数は 1~12 の数字を

・Day 関数は 1~31 の数字を

を返します。

期首の年月日

決算の「年」「月」「日」から期首の「年」「月」「日」を計算しています。

期首の「年」は決算年から1マイナス。

期首の「月」は決算月に1プラス、ただし決算年が 12の場合は1としています。

期首の「日」は 1としています。

「設定画面」シートに元号、年、月、日を出力

「設定画面」シートに期首、期末の元号、年、月、日を出力しています。

開始日・決算日

元号は Gengou プロシージャで計算し、年は Nen_Change プロシージャで西暦を和暦に変換しています。

Hi_Change プロシージャ

先にも示した Hi_Change プロシージャです。

文字列データを日付データに変換するためのプロシージャです。

令和1年10月31日 → 2019/10/31

Replace 関数によって和暦を西暦に置き換えています。

平成30 → 2019
令和1 → 2019
令和2 → 2020
令和3 → 2021
令和4 → 2022


また、「年」「月」の文字を「/」に置き換え、「日」の文字を空白に置き換えています。

Replace 関数は以下のように記述して使います。

Replace(文字列、検索する文字列、置換する文字列)

Replace(”abcde”,”c”,”z”)

このように↑ 記述すると、文字列「abcde」の中に含まれる「c」を「z」に置き換えます。

Gengou プロシージャ

先にも示した Gengou プロシージャです。

年、月から元号を出すためのプロシージャです。

Select Case 文を使って、x(西暦年)で場合分けしています。

●x(西暦年)が 2018 以下の場合 
・・・「平成」

●x(西暦年)が 2019 の場合
・・・y(月)が4以下であれば「平成」、それ以外の場合は「令和」

●x(西暦年)が 2020 以上の場合 
・・・「令和」

Nen_Change プロシージャ

先にも示した Nen_Change プロシージャです。

西暦年を和暦年に変換するためのプロシージャです。

先と同様に、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 静岡駅前にある静岡科学館 るくるに家族で行ってきました。

科学を体験を通して、やさしく学べます ^^

小学校低学年以下の子供がいる家庭にはオススメですね!

また、記事にします。

静岡科学館るくる

以下については、リンク先にある個別のフォームよりお願い致します。-----------------------
◆8/10(土)オフ会 in 静岡市
◆8/23、9/20 30代半ばからの「税理士試験 & 大学院」セミナー
◆メルマガ登録