PCメモ

Excelを中心とした業務改善の記録

VBA100本ノック7本目:日付データの扱い

excel-ubara.com

出題内容

#VBA100本ノック 7本目
A列は文字列データ(表示形式が文字列)で日付が入っています。
日付とみなされる場合はB列に月末日付をmmddの形式で出力してください。
日付け以外の場合は空欄にしてください。
例.B2は「0930」と出力する。
※何をもって日付とみなすかも含めて考えてください。

VBA マクロ 日付 IsDate CDate

自分の回答

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本目:セルに計算式

excel-ubara.com

出題内容

#VBA100本ノック 6本目
画像のようにA1から始まる表があります。
D列にB列×C列の計算式を入れてください。
ただし商品コードに"-"の枝番が付いている場合は計算式を入れずそのままにしてください。
例.D2にはA2×B2の計算式を入れる。D4:D5には計算式を入れない。

VBA マクロ 100本ノック

自分の回答

Dim LastRow As Long
Dim i As Long

LastRow = 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

VBA100本ノック 6本目:セルに計算式|VBA練習問題

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本目:セルの計算

excel-ubara.com

出題内容

画像のようにB2から始まる表があります。
B列×C列を計算した値をD列に入れ、通貨\のカンマ編集で表示してください。
ただしB列またはC列が空欄の場合は空欄表示にしてください。
例.D2にはB3×C3の計算結果の値を「\234,099」で表示、D5は空欄

VBA マクロ 100本ノック

 

自分の回答

Dim LastRow As Long
Dim i As Long

LastRow = 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行目までコピペ。

f:id:abv72:20210521213232p:plain

改善後

テーブル化することで、自動的に自動拡張され関数も入力される。

f:id:abv72:20210521213547p:plain

テーブル化したいデータ内で、Ctrl+Tを押すか、挿入タブのテーブルを選択する。

f:id:abv72:20210521213715p:plain

f:id:abv72:20210521213650p:plain

ピボットテーブルのデータソースにテーブルを選択している場合は、データを追加しても自動的にデータソース範囲が拡張される。

 

①カレンダーの日曜を塗りつぶし(条件付き書式)

条件付き書式で塗りつぶしを自動化する。

 

改善前

日曜日の行を手動で塗りつぶしする。

月が変わったら塗りつぶすを消去し、また塗りつぶしし直す。

f:id:abv72:20210521210725p:plain

 

改善後

条件付き書式を設定することで、曜日が日の行は自動で塗りつぶしするようになった。

f:id:abv72:20210521211827p:plain

①ルールを適用する範囲を選択

②条件付き書式から新しいルールを選択

③「B1に入力されている曜日が日の場合」という数式を入力

④書式設定で塗りつぶしを設定する。

 

曜日は=text(値,"aaa")とすることでシリアル値を曜日に変換できる。

f:id:abv72:20210521211931p:plain

 

VBA100本ノック4本目:セルの消去

excel-ubara.com

出題内容

画像のように1行目に見出し、A列に№が入っています。
この表範囲の一部には計算式が入っています。
(画像の最下行とD列には数式が入っています。)
データ行数は毎回変化します。
見出し行とA列№と計算式は残し、定数値だけを消去してください。

f:id:abv72:20210520215236p:plain

自分の回答

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本目:セルの消去

excel-ubara.com

出題内容

画像のように1行目に見出し、A列に№が入っています。
№行数およびデータ行数は毎回変化します。
この表の見出し(1行目)と№(A列)を残して、データ部分のみ値を消去してください。

f:id:abv72:20210518212830p:plain

自分の回答

Range("A1").CurrentRegion.Offset(1,1).ClearContents

解説

Offsetを使用すれば出題内容はクリアできるが、消去範囲がズレる。
IntersectやResizeを使用することで、消去範囲のズレを調整できる。

Intersectメソッド

Intersect(Range("A1").CurrentRegion,Range("A1").CurrentRegion.Offset(1,1)).ClearContents

f:id:abv72:20210518211708p:plain

Resizeプロパティ

Range("A1").CurrentRegion.Offset(1, 1).Resize(.Rows.Count - 1,
.Columns.Count - 1).ClearContents