Excel で2つの列を比較して、どちらかだけにある値を他の列に抜き出す。
この記事は自分用の覚書です。
■例：2台の無線ルーターに登録してあるMACアドレスを比較して、どちらかにだけある（重複していない）MACアドレスを抜き出す。
・「1号ルーター」に登録されていて、「2号ルーター」にはないMACアドレス
・「2号ルーター」に登録されていて、「1号ルーター」にはないMACアドレス
をそれぞれ抜き出す。
登録されているMACアドレスの数は、1号ルーターには30個。2号ルーターには53個。
（ Excel2013 Home & Business ）
■比較の準備
列見出しとして、
A1 に「1号ルーター」
C1 に「2号ルーター」
E1 に「1号ルーターにあって、2号ルーターにない値」
F1 に「2号ルーターにあって、1号ルーターにない値」
を入力。
A列に「1号ルーター」のMACアドレスを、C列に「2号ルーター」のMACアドレスを貼り付けて準備完了。
■ VBA を使う。
下記のプロシージャーを実行する。
※関数を使って片方にだけある値を見つけることはできる。
B2に次の式を入れて下方へコピー。
=IF(ISERROR(VLOOKUP(A2,$C$2:$C$53,1,FALSE)),"1号ルーターだけにある値","")
D2には次の式を。
=IF(ISERROR(VLOOKUP(C2,$A$2:$A$30,1,FALSE)),"2号ルーターだけにある値","")
Sub hikaku()
Dim i As Long, j As Long, flag As Long
'■A列にあって、C列にはないデータのチェック
''外側の繰り返し>>>>>>
For i = 2 To 30
flag = 0
''内側の繰り返し---
For j = 2 To 53
If Cells(i, "A") = Cells(j, "C") Then
flag = 1
Exit For
End If
Next j
''---内側の繰り返し
If flag = 0 Then
Cells(i, "A").Copy Cells(Rows.Count, "E").End(xlUp).Offset(1, 0)
End If
Next i
''<<<<<<外側の繰り返し
'
'
'■C列にあって、A列にはないデータのチェック
''外側の繰り返し>>>>>>
For i = 2 To 53
flag = 0
''内側の繰り返し---
For j = 2 To 30
If Cells(i, "C") = Cells(j, "A") Then
flag = 1
Exit For
End If
Next j
''---内側の繰り返し
If flag = 0 Then
Cells(i, "C").Copy Cells(Rows.Count, "F").End(xlUp).Offset(1, 0)
End If
Next i
''<<<<<<外側の繰り返し
'
'
'
'表を印刷して目と手で比較を行う場合は、
'A2 に指を置いて、それが C2 と等しいか。C3 と等しいか、C4 と等しいかと C53 まで確認する。
'途中で、A2 の値がC列のなかにあったら、C列はそれ以降は確認しないで、今度は A3 を C列で探す。
'まずはA列にだけある値を見つけたいから。
'このようにA列の値を基準にしてC列を最後まで見て重複がなかったら、
'そのA列の値を丸で囲むなどして、「この値がA列にだけある」ことがわかるように何か印をつけておく。
'
'A列にだけある値の確認が終わったら、今度はC列を基準にしてA列を同じように見ていく。
'
'C2 の値が A2 と等しいか、A3 と等しいか、A4 と等しいかと A30 まで確認する。
'次は、C3 の値をA列で探す。
'このように、A列の値を最後まで見ていって、C列の値がなかったら、
'そのC列の値を丸で囲むなどして、「この値がC列にだけある」ことがわかるように何か印をつけておく。
'
'この作業を VBA のコードに書き表す。
'
'■A列にあって、C列にはないデータのチェック
' For i = 2 To 30
'
' Next i
'の間で、A列のデータに対して繰り返し処理を行う。
'
' For i = 2 To 30
'
' Next i
'の内側にある
' For j = 2 To 53
'
' Next j
'の間で、C列に対して繰り返し処理を行う。
'
'最初に変数flagに 0 を入れて初期化しておく。
'
'A2 を比較の基準として繰り返し処理を行う。
'A2 と C列の値が等しいかのチェックをする。
'
'A2 と C2 が等しいかどうか。
'A2 と C3 が等しいかどうか。
'A2 と C4 が等しいかどうか。
'・
'・
'・
'A2 と C53 が等しいかどうか。
'
'まで確認したら、次は A3 を基準にしてC列との比較が始まる。
'その次は A4 を基準にして、そのまた次は A5 を基準にしてというように
'同じ処理を A30 まで行う。
'
'もし途中で等しい値が見つかったら変数flagに 1 を入れて、
'内側の繰り返しを抜ける。
'「A列にだけある値」を探したいので、C列の途中で同じ値が見つかったら、
'そのA列の値はC列でそれ以降見なくてもよい。
'A列の次の値を基準にして、C列の先頭から見ていくことになる。
'
'C列の途中に同じ値が見つかり変数flagに 1 を入れて内側の繰り返しを抜けると、
' If flag = 0 Then
' Cells(i, "A").Copy Cells(Rows.Count, "E").End(xlUp).Offset(1, 0)
' End If
'が実行されることになるが、
'この場合、変数flagには 1 が入っていて If flag = 0 Then が成り立たないので、
' If から End If までのコードは実行されない。
'
'C列のどこにもA列の値が見つからなかった場合（A列にだけある値が判明した場合）
'変数flagの値は 0 のままなので、
' If flag = 0 Then
' Cells(i, "A").Copy Cells(Rows.Count, "E").End(xlUp).Offset(1, 0)
' End If
'のコードが実行される。
'
'Cells(i, "A").copy でA列の値をコピーする。
'貼り付け先は、E列で入力済みの一番下のセルのさらに1つ下のセル。
'そのように貼り付けることで、常に入力済みのセルの下に値が追加されていくことになる。
'
'Cells(i, "A").Copy Cells(Rows.Count, "E").End(xlUp).Offset(1, 0)
'
'で、E列で入力済みの一番下のセルのさらに1つ下のセルに、
'A列にだけある値がどんどん追加で貼り付けられていく。
'
'Cells(Rows.Count, "E") のコードで、E列の最終セルを指定する。
'エクセルのシートの最大行は 1,048,576行なのでこのコードは
'Cells(1048576, "E")
'ということ。
'
'Cells(Rows.Count, "E").End(xlUp)
'のコードで、
'E列の最終行のセルから上にジャンプして、一番最初の入力済みのセル
'を示している。
'つまり、E列の1行目から見ると、E列の一番下の入力済みのセルということ。
'
'Offset(1, 0) のコードは、行を1つぶん下に移動するけれども、列はそのままという意味。
'
'Cells(Rows.Count, "E").End(xlUp).Offset(1, 0)
'のコードで、E列の一番下の入力済みのセルから1行下のセル
'を指定していることになる。これが貼り付け先。
'
'
'■C列にあって、A列にはないデータのチェック
'やっていることは同じ。
'ただし今度は、C列を基準にしてA列と比較していく。
'
'また、内側の繰り返しを抜けたあとの
'
'Cells(i, "C").Copy Cells(Rows.Count, "F").End(xlUp).Offset(1, 0)
'
'もA列を基準にしてみていたときと少し異なる。
'Cells(Rows.Count, "F") の部分がそれ。
'
'Cells(i, "C").Copy Cells(Rows.Count, "F").End(xlUp).Offset(1, 0)
'のコードでは、F列で入力済みのセルの下に
'C列にだけある値を追加していく動きになる。
'
'
'最終的に、
'■A列にあって、C列にはないデータは、E列に並ぶ。
'
'■C列にあって、A列にはないデータは、F列に並ぶ。
End Sub
| 固定リンク
コメント