練習問題13(文字列関数の練習) メモ
Sub Practice13()
Dim i As Long
Dim strPref As String
Dim strCity As String
Range("G2:I8").ClearContents
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
If InStr(Cells(i, 1), "(") > 0 Then
strPref = Left(Cells(i, 1), InStr(Cells(i, 1), "(") - 1)
Else
strPref = Cells(i, 1)
End If
If InStr(Cells(i, 2), "(") > 0 Then
strCity = Left(Cells(i, 2), InStr(Cells(i, 2), "(") - 1)
Else
strCity = Cells(i, 2)
End If
If Left(strPref, Len(strPref) - 1) = strCity Then
Cells(i, 3) = strPref
Else
Cells(i, 3) = strPref & "(" & strCity & ")"
End If
Next
End Sub
メモ:
型:
整数を入れる変数 → 長整数型(Long) 文字列を入れる変数 → 文字列型(String)
練習問題12(日付関数の練習)) メモ
Sub Pra12()
Dim i As Long
Dim intW As Integer
Range("G2:I8").ClearContents
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
intW = Weekday(DateSerial(Cells(i, 1), Cells(i, 2), Cells(i, 3)), vbMonday)
Cells(intW + 1, 7) = Cells(intW + 1, 7) + Cells(i, 4)
Cells(intW + 1, 8) = Cells(intW + 1, 8) + 1
Next
For i = 1 To 7
Cells(i + 1, 9) = Cells(i + 1, 7) / Cells(i + 1, 8)
Next
End Sub
メモ:
*intW → 変数の名前
*ClearContents→ 数式と文字を削除する
*DateSerial→ (西暦, 月, 日付)
For i = 1 To 7
Cells(i+1,7) = Cells(i+1,7) / Cells(i+1,8)
→ 仮に月曜日だとすれば, Cells(月曜日の+ 平均 売上) = Cells(月曜日の+ 売上合計) / Cells(月曜日の+日数) と覚える