７本目 「日付データの扱いのいろは」を学ぶ
基礎知識
IsDate関数
エクセルの日付はシリアル値という値で管理されています。
シリアル値は、１９００年１月１日を「１」としてそこから日付が増えるごとに１ずつ増えていきます。
「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
６行目のカンマ区切り（.）と11行目「１２３４」だけはDate型に変換することはできませんよ！と返されました。
カンマ区切りの日付の書き方は、他のシステムやWebで比較的使われるケースがあります。
そのため、この書き方だけ別途で文字列変換を施し、Date型に変換できるよう(true)に変えてみましょー！
区切りに使えないカンマ（.）→ 区切りに使ってOKなスラッシュ（/）（要はA2と同じ書き方)へと文字列を置き換えます。
Replace関数
文字列の置き換えにはReplace関数を使います。これは割と使い方が直感的で分かりやすいです。実際に使ってみると・・
’ 対象セルの、”カンマ”を、”スラッシュ”へ Replace(Cells(6,1),".","/")
大事なことが一点あります。A6以外のA列セルはカンマを含んでいないこの場合に、Replace処理をすべてに行ってよいか、です。
結論、「問題ありません」。カンマを含まないセルはそのままの形で返されます。
従って、表のA２セルから全てのA列セルにこのReplace処理をかけて大丈夫です！
A6セルを指定してReplace処理の動作確認をしてみました、ちゃんと変換できていますね！ばっちり
Sub カンマ→スラッシュ() Cells(6, 1) = Replace(Cells(6, 1), ".", "/") End Sub
ここまでの流れをもう一度まとめます。まず
１ A列セルに、カンマ区切りがあればスラッシュに文字列を置き換えます。
２ Replace変換が済んだセルに対して（ここ重要）、IsDateでDate型にできるか、判定します。
３ CDate関数で実際にDate型に変えます。
CDate関数
CDate(expression)
任意の文字列をDate型（シリアル値）に変換します。
変換できない場合はエラーを返します。ですが問題ありません。IsDateで選り分けてTrueを返したものしか、ここには通らせませんからね！
次にこのシリアル値を使って月末日を取得します。
DateSerial
DateSerial(year, month, day)
引数に指定した年、月、日に対応する日付を返す関数です。
シリアル値から年月日を取得するには
年：Year(シリアル値) 例：２０２１
月；Month(シリアル値) 例；１２
日：Day(シリアル値) 例；３１
使用例
DateSerial(2021,7,18) → 2021/7/18
月に０を入れると？ → 指定年の前年になる
DateSerial(2021,0,10) → 2020/12/10日に０を入れると？ → 指定月の先月末日になる
DateSerial(2022,7,0) → 2022/6/30
ここで頭をひねります。今回B列に欲しい、当月末はどう表すのでしょうか。
・
・
・
・
・
正解 当月末は
「翌月の、０日」と書きます。
→DateSerial ( Year(シリアル) , Month(シリアル)+1 , 0)
Format関数
あとは仕上げにこの月末日を「mmdd」形式(例えば７月２日なら→0702)にフォーマット（書式指定）します。
Format(月末日,"mmdd")
しかし、このように表示形式を「mmdd」としてしまうと、関数の戻り値がそのまま数値としてセルに入り、
数字の頭から０が消え、「０７０２」ではなく「７０２」と表示されてしまいます。困りました。
補足
この現象はエクセルシートでもよく遭遇します。試しにどこか空白セルに「０７０２」と打ってみてください。
セルの表示形式が文字列でなければ、７０２になってしまったはずです。
これはセルの表示形式を文字列「＠」にすれば解決するのですが、別の方法もあります。
それは頭に「’」をつけて入力する方法です。「’０７０２」と入力してみてください。数値ではなく、文字列として入力されました。
しかし、セルの表示形式をみると「標準」のままです。
つまり、セルの表示形式（いわば見せ方）を文字列にする方法と、セルの書式を文字列にする方法（’つけ）があります。
Format(月末日,"’mmdd")
これで４桁の月末表示が完成しました！あとはこれら基礎知識を順番に組み立てていくだけです。
回答
今回はこのような回答になりました。
学んだこと総括
IsDate関数 文字列が日付型にできるかチェックするもの
CDate関数 日付型に変える
Replace関数 文字列の置換
Format関数 文字の書式指定（数値を文字列として表示するなど）
DateSerial関数 CDateでシリアル値にしたものを、Year()Month()Day()で指定して日付表示にかえる
月末日を取得するのに (Year(),Month()+1,0) ；全月末の翌月といったようにプログラムは特殊な考え方をする
６本目「セル内文字の検出、相対参照による数式挿入」を学ぶ
今回は少し難しいです。そのため基礎知識の内容がヘビーです。しかし一つ一つゆっくり理解していけば、
問題ありません。基礎知識が蓄積されるのに伴い、もちろんマクロでできること、こういうマクロを組みたい！という自分で持てるオリジナルなマクロの構想も広がってくと思いますし、一緒に勉強頑張りましょう！
お題
使用するファイルはこちら
A列セルに「ー」があるとき： A列に枝番「 - 」がついている行のD列には、何も入れない。
A列セルに「ー」がないとき： A列に枝番がついていなければD列に、B列*C列の結果を入れる。
という処理内容のお題です。
基礎知識
Like 演算子
今回のお題では、A列セルの（＝文字列の間に「ハイフン」を含むならば、）という条件式を作る必要があります。そして、そのあとの処理を分岐させます。
指定文字を検出するためには、パターン文字列式を以下の表で覚えましょう。暗記です。
Like演算子は、String（指定した文字列） とパターン文字列式を比較します。
（半角文字は１バイト、全角文字は２バイトとなりますが、ここでは「全角文字（２バイト文字）も一文字として比較」します）
今回のお題では、ハイフンの前後に複数文字ありますね。ここで上の表をみて、ならアスタリスクを使えばいいんだなと！と確認できます。たとえば、これがハイフン前後が一文字A列の表示が「B-2」みたいな感じであれば、上の表を参考にパターン文字列式は「?-?」と指定できますよね。要するに場面に応じた使い分けが必要です。
実際に使ってみましょう。
'A２セルの文字列の間に「ー」が入っていなければ(※前後複数文字) If Not Cells(2, 1).Value Like "*-*" Then
このLike演算子で調べる方法を覚えるのが一つ。
そして同じことを、InStrという関数でも実現できます。
InStr関数は非常に重要なため、あえてこのパターンも見ていきましょう。
InStr関数
‘書き方 []内は省略可能 InStr([start位置,]検索範囲,検索文字列[,モード指定])
これは使用例を見た方が早いです。
InStr("macroDaisuki","a") ⇨「２」
(startを省略しているため、1文字目から検査されます)
スタート位置を指定する場合（これを使うことで２回目以降に現れる位置番号等を取得が可能）
InStr(3,"macroDaisuki","a") ⇨「7」
InStr(2,"macroDaisuki","a") ⇨「2」 (スタート位置は含むことがわかります)
検索した指定した文字列が対象から見つからなければ「０」が返されます。
先ほどLike演算子を用いて書いた「ハイフンを含まなければ」というコードをInStr関数で置き換える・・
'A２セルの文字列の間に「ー」が入っていなければ(※前後複数文字) If InStr(Cells(2, 1),"-") = 0 Then
となります。両方使えるようにしておきましょう。前回までにCurrentRegionを使うやり方で最終行数を取得していました（以下の通り）。
今回は別やり方で取得します
Cells(Rows.Count,1).End(xlUp).Rows.Count
これまた新しい呪文です。難解なものはまずエクセル操作として理解します。
まず、
① エクセルシートを開いて、A列の表の最後の行のセルを選択します
② 次にCtr+↓でエクセルシートの最下行を選択します。（すると、このような莫大な行数のセルが選択されます）
今あなたが選択しているセルがシートの最終行 Cells(Rows.Count,1)です。
ではここから表の最終行に移動するためにはどうしたらよいでしょうか。
そうです！今度は逆にCtr+↑を押せばOKです。(MacならCommand+↑)
③ End(xlUp) (これが「Ctr+↑」の操作をコードに置き換えたものです）
実際に操作してみると、表の最終行が選択されていますね。
あとはトドメです！そのセル（A13）の行数は何行目かを取得します。今までも何回も使ってきた
Rows.Countを使います。
最終的に Cells(Rows.Count,1).End(xlUp).Rows.Count となるわけです。
ぱっと見ごちゃついてて分かりにくいですが、処理ごとに３分割して考えると簡単でしたね！
プログラムにこう命令しています。
Cells(Rows.Count,1) A列のシート最下段をとってきて
.End(xlUp) そこから一気に上にのぼって、表下部に激突して
.Rows.Count 激突した行数を答えて
みたいな感じですかね笑＾＾
ただし、この方法の難点として、A列にどこか一つでも空白や文字などが入ってゴミのセルがあると、
そこで止まって、誤ってそのセルの行数を取得してしまいます。
なので、使うのであれば、A列に表の要素以外の無駄なものが入っていないことが前提条件です。
やはりこの欠点を考慮すると、最終行はEnd(xlUp)で下から測るより、表上余白＋CurrentRegionで表サイズ、つまり上から測ってったほうが無難じゃないの？
と感じました。ですが今後こなすお題の中で、End(xlUp)でしか最終行を取得できないケースがもしあったら、そのときは再考する余地はありですね。
最後にもう一つ大事な概念があります。
FormulaR1C1 （R1C1参照）
計算式を入れたいセルがあるとした際に、そのセルからみたときの相対的な位置を［］で指定することで、セルを参照します。
これも使用例から覚えた方が手っ取り早いです。RはRow（行）、CはColumn（列）を示します。［］をつければ相対指定、付けなければ絶対指定です。
例
計算式を入れるセル：D2
D2にはB2*C2 をいれたい、とします。これを相対指定で書く場合
Range("D2").FormulasR1C1 = "=RC[-2]*RC[-1]"
（D2セルから2列左に戻るとB2、１列左に戻るとC2、といったように、「D2から見た相対的な位置」で示します）例えば、この左辺のD2をD3に書き換えて見てください。すると、
D3 ＝B3＊C3
となりましたよね？右辺は何もいじっていないのに、です！
つまり、今回のお題で言えば左辺をCells(i,4）としてD列固定して、あとはFor分で行数 i を表行数までぶん回してしまえば、
右辺は "=RC[-2]*RC[-1]" と固定しておくだけ、勝手に行が1行ずつズレてくれることになります。。なんとも便利な参照ですね。すばらしすぎる。
回答
基礎知識さえ覚えてしまえば、ここからの回答自体の内容はかなり軽いと思われます。
基礎にはじまり基礎に終わる、もはやスポーツのようですが、それ位、上記基礎知識にぎっしり内容が詰まってます。
それではまず、表の最終行数をCurrentRegionをつかって取得します。
Range("A1").CurrentRegion.Rows.Count
これでD列の「２行目から「Range("A1").CurrentRegion.Rows.Count」行目まで」計算式を入れていけば良いとわかったのでFor文でまわします＿。
Dim i as Long For i = 2 To Range("A1").CurrentRegion.Rows.Count 計算式 Next
このままではA列セルのハイフン有無に関わらず計算式が全てに代入されてしまうので、ハイフンが無いときだけという条件を加えます。
'InStr 関数は指定文字列が対象になければ0を返す if InStr(Cells(i,1),"-")=0 then 計算式 Endif
あとはFormulaR1C1でD列に計算式を代入する処理を書いて動かしてみます
Dim i as Long For i = 2 To Range("A1").CurrentRegion.Rows.Count if InStr(Cells(i,1),"-")=0 then Cells(i,4).FormulaR1C1 = "=RC[-2]*RC[-1]" Endif Next
ちゃんとハイフン有無に従いセル入力できています！これにて６本目のノック終了！今回のノックは重たかったです＾＾；
学んだこと総括
最終行はCurrentRegiontの方法、Cells(Rows.Count,1).End(xlUp)の方法 それぞれ習得
文字列の検出はLike演算子でパターン文字列比較する方法、InStr()関数の方法 それぞれ習得
数式を相対参照で入れる→ Cells(i,~~).FormulaR1C1 = "=RC[]"
For文でiを回せば、右辺は相対的に指定しているためセル入力される数式は自動で変わってくれる
５本目 「セルの計算と表示形式の指定」を学ぶ
お題
作成したファイルはこちらから
基礎知識
繰り返しの処理 For Next
→指定した回数処理をくりかえしたい場合に使用します。
場合分けの処理 if 文
→任意の条件式の真偽(true or false)に応じて異なる処理を行う際に使用します。
NumberFormatLocal
ここが今回の課題で割と難しいところかと思います。
これは表示形式を変えるメソッドです。エクセルではセルに入っている値自体は同じでも
それをいろんな見せ方（形式）で示すことができます。今回のお題では円マークとカンマ区切りをつけました。ついでにいくつか例をあげておきます。
- Range.NumberFormutLocal="@" '文字列で表示する
- Range.NumberFormutLocal="#,###" 'カンマ区切り
- Range.NumberFormutLocal="0.00" ' 小数点２以下まで表示
- Range.NumberFormutLocal="yyyy/mm/dd" '日付表示
- Range.NumberFormutLocal="aaa" '曜日表示
- Range.NumberFormutLocal="ggge" '明治大正昭和平成令和 ●● （例：昭和６０）
とりあえずこれくらい覚えてあとはその都度覚えましょ。
回答
まずは答えを示します。D列にきちんと計算結果、円マーク、カンマ区切りがついています。では、頭から一つずつ追ってみていきましょう。
最初に使いたい変数の宣言です。Option Explicitに怒られないようにね！ データ型 Long の変数としてiを定義しています。
Longは前回勉強しました１とか２とかの「整数 」です。整数なので、例えば小数点のついた数値や「あいうえお」のような文字列はここに入りられません。
これは３行目、４行目、５行目、・・と処理を行なう際の、●行目の●に入る変数です。なので整数で定義しました。i 行目、ということにしておいて、このiをまず最初に３にしてみて、次は４にしてみて、次は５にして・・と処理していきます。
するとおそらく次に、そのiをどこまで増やしていいの？という問題にぶつかります。１２行目までで良い処理を、１０００行目までされてはたまりません、日が暮れまちゃいます。
答えはもちろん表の最後の行まで、です。
ではその「表の最終行」をどうやって取得するか、となり、ここが今回の一番の山場、学びPOINT！です。
頭の中だけで考えるとマクロはなにかと小難しいので、色分けして少し分解しちゃいましょう！
要は最終行は下図のとおり
「見出しより上の空白行（行数特定済みとする） ＋ 見出し以下の表の行数（行数は未特定とする）」
です。
つまり１+１１＝１２行です。ただしここでよっしゃと安直に For i=3 to 12 ~~
と書いてしまうと、１２行までの表にしか対応できません。これではだれも使ってくれません。
そこで増減する表に対応するため、表の行数をマクロでどう表現するか（取得するか）、という考えが必要になります。でも、理屈は簡単で、あくまで先ほどと同じ、
「見出しより上の空白行 ＋ 見出し以下の表の行数」
です。
Dim i As Long
'三行目から（表自体の行数＋表より上の空白行数）行目まで。For とNext で囲まれた処理を繰返す つまり回数は最終行数次第で可変的！嬉しい＾＾
For i = 3 To Range("B2").CurrentRegion.Rows.Count + 1
’単価＝空白 または 数量＝空白 なら
If Cells(i, 2) = "" Or Cells(i, 3) = "" Then
'金額には何も入れない
Cells(i, 4) = ""
’それ以外、つまり単価か数量のどちらかでも、なにか数値が入っている なら
Else
’積算したものをD列に代入する
Cells(i, 4) = Cells(i, 2) * Cells(i, 3)
End If
Next ’」ここまでを繰り返します
’-------------------------------------
’↓D列を計算式で埋めたら以下の処理に移行します
’D列の表示形式に円マークを付け＆カンマで区切る
’(※セルの数値は不変、見え方変えてみるだけです)
Range("D:D").NumberFormatLocal = "¥#,##0"
学んだこと総括
今回はだいぶボリューム感じましたよ（うっぷ）。相変わらず表がでてきたらCurrentRegion様様って感じがします、今のところ。あと私自身、表示形式の理解が浅かったです。マクロで覚える前に、先にエクセルのユーザー設定で触って感覚を掴んでおかねばと痛感し次第です。
ざっっくりまとめると・・
表の行数を知りければ、CurrentRegion.Rows.Count
繰り返したければ For i(忘れずlong型で定義) To 終点 〜〜〜 Next
場合分けたければ if 条件 then ~~ else ~~ Endif
表示形式いじりたければ NumberFormatLocal="" （基本１行ないし１列単位設定）
４本目「定数値のセルのみ削除」を学ぶ
暑い日が続きますね、もう４本目にして頭が沸騰しそうです。ですが、まだ４／」１００です！まだまだ脳みそ酷使し倒してやりましょう
お題
試しに以下のデータを作成しました。
緑色の見出し行とNo列、黄色の数式が入っている領域は消さないように注意します。
それでは、早速やっていきます！
基礎知識
今回の範囲選択は３本目の応用になります。前回のお題では、CurrentRegionで連結したブロックとして表を捉えて、それをoffset(1,1)で、斜めに移動させて見出しを範囲の外に出しました。今回はそれに加えて新たに、「セルに入っているものは定数か数式かの選別」を組み込む必要があります。
SpecialCells
指定した種類と値に一致するすべてのセルを表すRangeオブジェクトを返します。
Rangeオブジェクト.SpecialCells(Type, Value)
SpecialCellsの引数の中でも特に使用頻度が高いと思われるものは以下の通りです。(sがつくつかないは要注意です。SpecialCellsはつきますが、引数のCellにはsがつきません)
- 定数 xlCellTypeConstants
- 数式 xlCellTypeFormulas
- 空白 xlCellTypeblank
- コメント xlCellTypeComments
On Error Resume Next
SpecialCellsでは引数が条件を満たしていなければエラーが発生します。このエラーが発生したときのため、「それ予期してたエラーだから止まらないで、とばして次いっちゃってー」っと一言命令しときます。急に予期せぬエラーがでたらプログラムもびっくりあたふたしちゃうので＾＾
では、以下で実際に使ってみます。
回答
変数の宣言が冒頭から出てきました。
rngという変数を使いたいです、と書くときに「Dim rng as データ型」
と書きます。
データ型はたくさんありますが、最初に覚えるべきは
- 整数 Long
- 文字列 String
- 小数 Double
です。それ以外にもオブジェクトを入れられる型として
Range型やWorksheet型などあります。ここではRangeを格納できるRange型として変数rngを定義しています。ただルールとして、オブジェクト変数にオブジェクトを格納するときは必ずSet をつけなければいけません。データ型の記述を省略すると（例 Dim rng）
なんでも入るVariant型になります。変数を使う時は、できるだけデータ型を指定し、変数を定義したいと思います。
Dim rng As Range 'Rnageオブジェクトを宣言する
Set rng = Worksheets("Sheet1").Range("A1").CurrentRegion.offset(1,1) 'B2:E13の範囲が変数に格納される
On Error Resume Next 'エラーが出てもとまらず次の処理に移行するために必要な記述
rng.SpecialCells(xlCellTypeConstants).ClearContents 'セルが定数値なら消す
実行してみると以下のとおり、数値セルだけ削除することができた。お題クリアー！
今回学んだこと総括
変数の宣言はDIm、データ型は整数Long・文字列String・小数Double
On Error Resume NextとRangeオブ.SpeacialCells(xlCellType~~)の組み合わせコンボでセルに何が入っているかがわかり、入っているセルだけに特定の処理を与えることができた（今回で言えば削除）
３本目 「セルのブロック選択と削除」を学ぶ
それでは今日も元気にやっていきましょー！
今回はセルの消去を学びます。まずは今回のお題から。
出題
準備
出題に沿ったサンプルデータを準備しました。
（ファイルはこちらから）
NO行を１３、１４・・と追加して行ったり、データ列をデータ４、データ５・・
と増やし更新していっても、それに対応した表の中身部分だけが削除されるようにマクロを組みます。
宣言強制の自動挿入
標準モジュールを作る前に、一つ設定します。
Option Explicit(宣言の強制)を標準モジュール作成時に自動挿入するようにします
環境設定
→ オプションが開く
→ 変数の宣言が必要です。に✅
→ OK
右クリックから標準モジュールをいつも通り作成してみます。
無事、自動挿入されました！次回からが楽ちんです。
回答
回答していく前に、基礎知識として必ず押さえたいポイントが２つあります。
- CurrentRegion
- offset
- ClearとClearContents
です。
CurrentRegionとは
選択範囲を自動的に拡大して、 現在の領域全体が含まれるようにします。
現在の選択範囲は、空白の行と空白の列の任意の組み合わせで囲まれた範囲です。
つまり空白行、列で区切られるまで風呂敷を広げに広げたような領域です。
さっきのサンプルファイルで使ってみます。
Range("A1")のCurrentRegionを選択できました！
たとえば、ここからNo９の列の内容を消して再度実行してみると
選択範囲がだいぶ狭まっていることが分かります。これは黄色で塗りつぶしている空白行・空白列でA1の属するブロックが遮られているからです。これはB2のCurrentRegionでも同じ領域となりますし、B6のような空白セルでも同じ領域となります。つまり、このグレーで選択された領域内のセルであれば、どのセルを選ぼうとCurrentRegionで得られる領域は同じになるのです。
変化に対応してセル範囲を指定できる。素晴らしい機能です！しかし、困りました。
よくみると見出し行、見出し列も選択されています。このまま削除すると見出しが消えてしまいます。一行と一列、範囲ごとを斜め下に少しお引っ越ししてもらいたいですね・・・
offset
そこでoffsetの出番です！これは指定した範囲をそのままサイズでずらすために利用します。
Range.Offset(RowOffset, ColumnOffset)
下にずらしたいなら、RowOffsetと書いているところに正の値を
上にずらすなら負の値を入れてください。
右にずらすなら、ColumnOffsetに正の値を、左なら負の値を入れます。
ここでは見出し行、見出し列のところを一つずつずらしたいので
~~~~.offset(1,1) となります。ずらしてみましょう（No９のところは元に戻してね）
よし！ずれました、範囲の大きさそのままで並行移動しているため、一行一列余分に範囲選択される結果となります。しかし、これは対した問題ではありません。というのも、このグレーの範囲は、CurrentRegionで拾ってきた範囲ですので、あぶれた一行一列の中身は、必ずすべて空白列・空白行となるからです。
あとは、このままDeleteボタンをぽちっとおしたいところです。がもちろんそれも自動化します。
ClearとClearContentsの使い方
ここではこの二つの機能を比較するため、書式の削除まで確認するために、以下のように塗りつぶしてください。
結論から言うと両者の違いは以下の通りです。
ClearContentsで消せるもの
数式と文字のみを削除します。（書式等はそのまま残る）
Clearで消せるもの 要するに全部消す
全体（数式・文字・書式・コメント全て）をクリアします。
ではまず一番よく使うClearContentsから。(要注意！消す前に違うシートに複製しておいてください。マクロで操作した内容に対し、「もとに戻す」機能はつかえません)
ちゃんと書式を残して消せました。ここまででお題自体はクリアーです！
ですがClearしたついでにClearも使ってみると・・
うわあ！がっつり骨までもってかれました。とふざけていないで総括へ
学んだこと総括
セル領域をいわば一つのブロックとして指定できるCurrentRegion
範囲を縦横斜めにサイズそのままで並行移動できる、offset
二つ組み合わせれば柔軟な範囲指定が可能！
文字だけ消したいときはClearContents
がっつり骨ごともってきたいときは破壊神Clear
