VBAでグループごとや条件で連番を振る方法をご説明します。
連番を振る方法がいくつかありますが、Excel関数の「IF関数とEXACT関数」を利用して連番を振る方法と、VBAの「StrComp関数」とロジックの組み合わせで連番を振る方法をご説明します。
また、連番を振るために前処理として並び替えが必要となります。
VBAのロジックでどうにかする場合は、並び替えは必要ないですがロジックが複雑になりますので、支障がない場合は並び替えする事をおすすめします。
並び替えについては次のご覧ください。
1.IF関数とEXACT関数とは
まずは今回使用するExcel関数の「IF関数」と「EXACT関数」を、簡単にですがご説明します。
IF関数とは
IF関数はExcelでもよく利用される代表的な関数で、条件式を指定した結果が真か偽かで処理を分ける時に使用します。
次のように記述します。
「=IF(A1=B1,真の場合,偽の場合)」
EXACT関数とは
EXACT関数はライトなExcelユーザーにはあまり馴染みのない関数かもしれません。
文字と文字を比較する際に使用します。
比較した文字が同じ場合はTrueを、違う場合はFalseを返します。
Excelで使用する場合は、次のように記述します。
「=EXACT(A1,B1)」
文字と文字の比較であれば、「=A1=B1」のようにイコールでも良いと思われますが、イコールは大文字、小文字を判別しない特徴があります。
ひらがな、カタカナの大文字、小文字の「あいう」と「ぁぃぅ」は区別されます。
しかし、EXACT関数は大文字、小文字も判別してくれるためFalseを返します。
A1に「ABC」、B1に「abc」と入力されていた場合もTrueを返します。
より厳密に比較したい場合に使用します。
2.IF関数とEXACT関数で連番を振る方法
ExcelのIF関数とEXACT関数を使用して連番を振る方法です。
数式をセルに直接入力します。
数式としては次のようになるよう記述します。
「=IF(EXACT(A1,A2),D1+1,1)」
数式の意味としては、A1とA2が同じなら1つ上のセルの数字+1を、違う場合は1を返します。
次のようにランダムに並んだA列にグループ、B列に商品名、C例に連番を振るといったデータを使用します。
サンプルコード
連番を振る前に前処理としてA列とB列で並び替えをしています。
並び替え後にループで数式をセルに入力します。
Sub Sample1()
Dim i As Long
With ActiveSheet
'並び替え条件をクリアする
.Sort.SortFields.Clear
'グループ名
.Sort.SortFields.Add _
Key:=ActiveSheet.Cells(1, 1), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal
'商品名
.Sort.SortFields.Add _
Key:=ActiveSheet.Cells(1, 2), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal
With .Sort
.SetRange Range(Cells(1, 1), Cells(13, 3))
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'=IF(EXACT(A1,A2),D1+1,1)となるように数式を作成する
For i = 2 To 13
.Cells(i, 3).Formula = "=IF(EXACT(A" & i - 1 & ",A" & i & "),C" & i - 1 & "+1,1)"
Next i
End With
End Sub
3.VBAのStrComp関数とロジックで連番を振る方法
VBAのStrComp関数とロジックで連番を振る方法です。
こちらは数式を使わずに値のみを取得して、セルに入力します。
StrComp関数は文字列を比較する関数です。
次のように記述します。
「StrComp(文字列1, 文字列2, 比較モード) 」
比較モードで「vbBinaryCompare」を指定すると、大文字、小文字を区別して比較します。
「vbTextCompare」を指定すると、区別しません。
省略するとデフォルトとして「vbBinaryCompare」が指定されたことになり、区別されます。
比較した文字列が同じ場合は返り値が0になります。
サンプルコード
連番を振る前に前処理としてA列とB列で並び替えをしています。
並び替え後に「StrComp関数」で文字列を比較した結果で1もしくは加算をします。
Sub Sample2()
Dim i As Long
With ActiveSheet
'並び替え条件をクリアする
.Sort.SortFields.Clear
'グループ名
.Sort.SortFields.Add _
Key:=ActiveSheet.Cells(1, 1), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal
'商品名
.Sort.SortFields.Add _
Key:=ActiveSheet.Cells(1, 2), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal
With .Sort
.SetRange Range(Cells(1, 1), Cells(13, 4))
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'StrComp(文字列1, 文字列2)でグループの文字を比較します。
For i = 2 To 13
If StrComp(Cells(i, 1), Cells(i - 1, 1)) = 0 Then
.Cells(i, 3) = .Cells(i - 1, 3) + 1
Else
.Cells(i, 3) = 1
End If
Next i
End With
End Sub
