エクセルマクロを0から独学で極める極意~ITを味方に~

エクセルマクロを0から独学で極める極意~ITを味方に~

一緒に独学でマクロを極めて、定時帰宅とスキルアップを目指しましょう!

7本目 「日付データの扱いのいろは」を学ぶ

お題

f:id:Rimux:20210718085825p:plain



使用するファイル

基礎知識

IsDate関数

エクセルの日付はシリアル値という値で管理されています。
シリアル値は、1900年1月1日を「1」としてそこから日付が増えるごとに1ずつ増えていきます。

「A列に入っているデータが日付かどうか」を判定する関数としてVBAにはIsDate関数があります。
これは、Date型(つまりシリアル値)に変換できるか否か判定し、true/falseで返してくれます。
試しに、判定してC列に代入するマクロを書いて実行してみました。


f:id:Rimux:20210718093640p:plain

Sub Date変換可否調べ()

Dim i As Long

For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row

    Cells(i, 3) = IsDate(Cells(i, 1))

Next

End Sub

6行目のカンマ区切り(.)と11行目「1234」だけはDate型に変換することはできませんよ!と返されました。
カンマ区切りの日付の書き方は、他のシステムやWebで比較的使われるケースがあります。
そのため、この書き方だけ別途で文字列変換を施し、Date型に変換できるよう(true)に変えてみましょー!

区切りに使えないカンマ(.)→ 区切りに使ってOKなスラッシュ(/)(要はA2と同じ書き方)へと文字列を置き換えます。

Replace関数

文字列の置き換えにはReplace関数を使います。これは割と使い方が直感的で分かりやすいです。実際に使ってみると・・

’ 対象セルの、”カンマ”を、”スラッシュ”へ
Replace(Cells(6,1),".","/")


大事なことが一点あります。A6以外のA列セルはカンマを含んでいないこの場合に、Replace処理をすべてに行ってよいか、です。
結論、「問題ありません」。カンマを含まないセルはそのままの形で返されます
従って、表のA2セルから全てのA列セルにこのReplace処理をかけて大丈夫です!


A6セルを指定してReplace処理の動作確認をしてみました、ちゃんと変換できていますね!ばっちり


f:id:Rimux:20210718095222p:plain

Sub カンマ→スラッシュ()

Cells(6, 1) = Replace(Cells(6, 1), ".", "/")

End Sub

ここまでの流れをもう一度まとめます。まず

1  A列セルに、カンマ区切りがあればスラッシュに文字列を置き換えます。


2 Replace変換が済んだセルに対して(ここ重要)、IsDateでDate型にできるか、判定します。


3 CDate関数で実際にDate型に変えます。

CDate関数


CDate(expression)

任意の文字列をDate型(シリアル値)に変換します。
変換できない場合はエラーを返します。ですが問題ありません。IsDateで選り分けてTrueを返したものしか、ここには通らせませんからね!



次にこのシリアル値を使って月末日を取得します。

DateSerial


DateSerial(year, month, day)

引数に指定した年、月、日に対応する日付を返す関数です。
シリアル値から年月日を取得するには

年:Year(シリアル値)  例:2021
月;Month(シリアル値) 例;12
日:Day(シリアル値)   例;31

使用例

 DateSerial(2021,7,18) → 2021/7/18

月に0を入れると? → 指定年の前年になる
 DateSerial(2021,0,10) → 2020/12/10

日に0を入れると? → 指定月の先月末日になる
 DateSerial(2022,7,0) → 2022/6/30

ここで頭をひねります。今回B列に欲しい、当月末はどう表すのでしょうか




正解 当月末は

「翌月の、0日」と書きます。

DateSerial ( Year(シリアル) , Month(シリアル)+1 , 0)

Format関数


あとは仕上げにこの月末日を「mmdd」形式(例えば7月2日なら→0702)にフォーマット(書式指定)します。

Format(月末日,"mmdd")

しかし、このように表示形式を「mmdd」としてしまうと、関数の戻り値がそのまま数値としてセルに入り、
数字の頭から0が消え、「0702」ではなく「702」と表示されてしまいます。困りました。

補足
この現象はエクセルシートでもよく遭遇します。試しにどこか空白セルに「0702」と打ってみてください。
セルの表示形式が文字列でなければ、702になってしまったはずです。
これはセルの表示形式を文字列「@」にすれば解決するのですが、別の方法もあります。
それは頭に「’」をつけて入力する方法です。「’0702」と入力してみてください。数値ではなく、文字列として入力されました。
しかし、セルの表示形式をみると「標準」のままです。
つまり、セルの表示形式(いわば見せ方)を文字列にする方法と、セルの書式を文字列にする方法(’つけ)があります。

Format(月末日,"mmdd")

これで4桁の月末表示が完成しました!あとはこれら基礎知識を順番に組み立てていくだけです。

回答

今回はこのような回答になりました。

f:id:Rimux:20210718114422p:plain

学んだこと総括

IsDate関数   文字列が日付型にできるかチェックするもの
CDate関数   日付型に変える
Replace関数  文字列の置換
Format関数   文字の書式指定(数値を文字列として表示するなど)
DateSerial関数 CDateでシリアル値にしたものを、Year()Month()Day()で指定して日付表示にかえる
        月末日を取得するのに (Year(),Month()+1,0) ;全月末の翌月といったようにプログラムは特殊な考え方をする