VBA100本ノック7本目:日付データの扱い
出題内容
#VBA100本ノック 7本目
A列は文字列データ(表示形式が文字列)で日付が入っています。
日付とみなされる場合はB列に月末日付をmmddの形式で出力してください。
日付け以外の場合は空欄にしてください。
例.B2は「0930」と出力する。
※何をもって日付とみなすかも含めて考えてください。
自分の回答
Dim LastRow As Long
Dim i As king
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To LastRow
If IsDate(Cells(i, 1)) = True Then
Cells(i, 2) = Format(Cells(i, 1), "mmdd")
Else
End If
Next i
mmdd形式には変換されるが、月末日付になっていない。
このままだと入力者が日付だと思って入力している数値を変換できない。(2020.10.5など)
解説
Dim i As Long
Dim d As Variant
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
d = Replace(Cells(i, 1).Value, ".", "/")
If IsDate(d) Then
d = CDate(d)
Cells(i, 2) = Format(DateSerial(Year(d), Month(d) + 1, 0), "'mmdd")
Else
Cells(i, 2) = ""
End If
Next
CDate(d)とすることで、変数dを日付型に変換できる。
d = Replace(Cells(i, 1).Value, ".", "/")を日付型に変換している。
変換できない場合はCells(i, 2) = ""となる。
日付判定できる≠シリアル値
IsDateでTrue判定になったとしても、そのままMonth関数で変換はできない。
CDateでシリアル値に変換する必要がある。
DateSerial(Year(d), Month(d) + 1, 0) 日付を0にすると前月末日になる。
EoMonthを使っても良いかも。
EOMONTH関数(月数だけ前後の月の最終日)|エクセル入門
そのそも日付以外は入力できないように、入力を制限しておくと良い。
VBA100本ノック6本目:セルに計算式
出題内容
#VBA100本ノック 6本目
画像のようにA1から始まる表があります。
D列にB列×C列の計算式を入れてください。
ただし商品コードに"-"の枝番が付いている場合は計算式を入れずそのままにしてください。
例.D2にはA2×B2の計算式を入れる。D4:D5には計算式を入れない。
自分の回答
Dim LastRow As Long
Dim i As LongLastRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To LastRow
If Cells(i, 1) Like "*-*" Then
Else
Cells(i, 4) = Cells(i, 2) * Cells(i, 3)
End If
Next i
Cells(i, 4) = Cells(i, 2) * Cells(i, 3) とすると計算式ではなく計算結果が入力されてしまう。
解説
範囲内に同一数式を入力したい場合は、相対参照で一括入力した方が良い。
R1C1参照を使用する。
Dim i As Long
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
If Not Cells(i, 1).Value Like "*-*" Then
Cells(i, 4).FormulaR1C1 = "=RC[-2]*RC[-1]"
End If
Next
Cells(i, 4).FormulaR1C1 = "=RC[-2]*RC[-1]" とすることで、相対参照の計算式をセルに入力できる。
R1C1参照を使うと、基準セルからの位置で計算式を入れることができる。
R:Row(行)
下方向へ正の数で増えていく。
C:Column(列)
右方向へ正の数で増えていく。
R | Rの後に何も記述しなければ同一行 |
C | Cの後に何も記述しなければ同一行 |
R1 | 数値の絶対行数 |
C1 | 数値の絶対列数 |
R[1] | [数値]分行位置をずらした行 |
C[1] | [数値]分列位置をずらした列 |
VBA100本ノック5本目:セルの計算
出題内容
画像のようにB2から始まる表があります。
B列×C列を計算した値をD列に入れ、通貨\のカンマ編集で表示してください。
ただしB列またはC列が空欄の場合は空欄表示にしてください。
例.D2にはB3×C3の計算結果の値を「\234,099」で表示、D5は空欄
自分の回答
Dim LastRow As Long
Dim i As LongLastRow = Cells(Rows.Count, 2).End(xlUp).Row
For i = 3 To LastRow
If Cells(i, 2) = "" Or Cells(i, 3) = "" Then
Else
Cells(i, 4).Value = Cells(i, 2) * Cells(i, 3)
Cells(i, 4).NumberFormatLocal = "¥#,##0"
Next i
Cells(Rows.count,2).End(xlUp).Rowで最終行を求めてループさせる。
Cells(i,2)とCells(i,3)が空欄でなければCells(i,2)とCells(i,3)をかけて書式設定をする。
今回は2列目に最後までデータが入っているが、データがかけてしまうと列によって最終行が変わってしまう。
解説
CurrentRegion.Rows.Countとすると表の範囲の行数を取得することができる。
For i = 3 To Range("B2").CurrentRegion.Rows.Count + 1
If Cells(i, 2) = "" Or Cells(i, 3) = "" Then
Cells(i, 4) = ""
Else
Cells(i, 4) = Cells(i, 2) * Cells(i, 3)
End If
Next
Columns("D").NumberFormatLocal = "¥#,##0"
②自動で関数を下の行に自動入力(テーブル)
テーブル機能を使って自動で範囲を拡張し、関数を自動入力する。
改善前
データを追加した後に、関数を下までコピペする。
(例)6行目に入力後、C列に入力されているVLOOKUP関数を6行目までコピペ。
改善後
テーブル化することで、自動的に自動拡張され関数も入力される。
テーブル化したいデータ内で、Ctrl+Tを押すか、挿入タブのテーブルを選択する。
ピボットテーブルのデータソースにテーブルを選択している場合は、データを追加しても自動的にデータソース範囲が拡張される。
①カレンダーの日曜を塗りつぶし(条件付き書式)
条件付き書式で塗りつぶしを自動化する。
改善前
日曜日の行を手動で塗りつぶしする。
月が変わったら塗りつぶすを消去し、また塗りつぶしし直す。
改善後
条件付き書式を設定することで、曜日が日の行は自動で塗りつぶしするようになった。
①ルールを適用する範囲を選択
②条件付き書式から新しいルールを選択
③「B1に入力されている曜日が日の場合」という数式を入力
④書式設定で塗りつぶしを設定する。
曜日は=text(値,"aaa")とすることでシリアル値を曜日に変換できる。
VBA100本ノック4本目:セルの消去
出題内容
画像のように1行目に見出し、A列に№が入っています。
この表範囲の一部には計算式が入っています。
(画像の最下行とD列には数式が入っています。)
データ行数は毎回変化します。
見出し行とA列№と計算式は残し、定数値だけを消去してください。
自分の回答
Dim LastRow As Long
LastRow = Cells(Rows.Count,1).End(xlUP).Row -1
Range("B2:" & "C" & LastRow).ClearContents
ClearContentsでB2:C11を消去している。
解説
消去するのは計算式ではなく、定数値。
SpecialCells(xlCellTypeConstants)を使うことで、選択範囲中の定数値のみを対象にすることができる。
Dim rng As Range
Set rng = Range("A1").CurrentRegion.Offset(1, 1)
On Error Resume Next
rng.SpecialCells(xlCellTypeConstants).ClearContents
①Range("A1").CurrentRegionで選択範囲はA1:D12
②Offset(1,1)で№列と見出し行を範囲から外し、範囲はB2:D12となる。
③SpecialCells(xlCellTypeConstants)を使い、B2:D12内の定数値のみを消去することができる。
VBA100本ノック 3本目:セルの消去
出題内容
画像のように1行目に見出し、A列に№が入っています。
№行数およびデータ行数は毎回変化します。
この表の見出し(1行目)と№(A列)を残して、データ部分のみ値を消去してください。
自分の回答
Range("A1").CurrentRegion.Offset(1,1).ClearContents
解説
Offsetを使用すれば出題内容はクリアできるが、消去範囲がズレる。
IntersectやResizeを使用することで、消去範囲のズレを調整できる。
Intersectメソッド
Intersect(Range("A1").CurrentRegion,Range("A1").CurrentRegion.Offset(1,1)).ClearContents
Resizeプロパティ
Range("A1").CurrentRegion.Offset(1, 1).Resize(.Rows.Count - 1,
.Columns.Count - 1).ClearContents