7本目 「日付データの扱いのいろは」を学ぶ
基礎知識
IsDate関数
エクセルの日付はシリアル値という値で管理されています。
シリアル値は、1900年1月1日を「1」としてそこから日付が増えるごとに1ずつ増えていきます。
「A列に入っているデータが日付かどうか」を判定する関数としてVBAにはIsDate関数があります。
これは、Date型(つまりシリアル値)に変換できるか否か判定し、true/falseで返してくれます。
試しに、判定してC列に代入するマクロを書いて実行してみました。
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処理の動作確認をしてみました、ちゃんと変換できていますね!ばっちり
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桁の月末表示が完成しました!あとはこれら基礎知識を順番に組み立てていくだけです。
回答
今回はこのような回答になりました。
学んだこと総括
IsDate関数 文字列が日付型にできるかチェックするもの
CDate関数 日付型に変える
Replace関数 文字列の置換
Format関数 文字の書式指定(数値を文字列として表示するなど)
DateSerial関数 CDateでシリアル値にしたものを、Year()Month()Day()で指定して日付表示にかえる
月末日を取得するのに (Year(),Month()+1,0) ;全月末の翌月といったようにプログラムは特殊な考え方をする