EXCELの重複データの抽出について

質問

1月と2月という2つのシートがあり、それぞれA列に姓、B列に名、C列にメールアドレスが入力されています。
1月と2月を比べてABC列のデータが全く同じものだけを、1月重複分、2月重複分として抽出することが、ExcelまたはAccessでうまくできないため、どなたか教えてください。

回答No.5

imogasi

VBAでやって見ました。
筋書きは、両シートのデータを１表にまとめ、ソートし、
直前と同じものを書き出しています。
Sub test02()
Dim sh1, sh2, sh3, sh4 As Worksheet
Set sh1 = Worksheets("aa2")
Set sh2 = Worksheets("aab3")
Set sh3 = Worksheets("sheet3")
Set sh4 = Worksheets("sheet4")
'-----第１シートのデータを別表に
clm = Val(InputBox("シートデータ最右列は第何列目（数字）＝"))
s1 = InputBox("第１シートデータ最初行＝")
d1 = sh1.Cells(s1, 1).CurrentRegion.Rows.Count
sh1.Activate
sh1.Range(Cells(s1, 1), Cells(d1, clm)).Copy
sh3.Activate
sh3.Range("a1").Select
ActiveSheet.Paste
'-----第２シー絵尾のデータを別表に
s2 = InputBox("第２シートデータ最初行＝")
d2 = sh2.Cells(s2, 1).CurrentRegion.Rows.Count
sh2.Activate
sh2.Range(Cells(s2, 1), Cells(d2, clm)).Copy
sh3.Activate
sh3.Cells(d1 - s1 + 2, 1).Select
ActiveSheet.Paste
'------合体シートをソート
sh3.Cells.Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Key2:=Range("B1") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, SortMethod:=xlPinYin
'------第４シートに重複分を抽出
sh3.Select
k = 1
d3 = d1 - s1 + 1 + d2 - s2 + 1
mk = sh3.Cells(1, 1) & sh3.Cells(1, 2) & sh3.Cells(1, clm)
For i = 2 To d3
ky = sh3.Cells(i, 1) & sh3.Cells(i, 2) & sh3.Cells(i, clm)
If ky = mk Then
For j = 1 To clm
sh4.Cells(k, j) = sh3.Cells(i, j)
Next j
k = k + 1
End If
mk = ky
Next i
End Sub
・もし興味があり、VBEに関してなど、やり方が判らない時は補足します。
・Set sh1 = Worksheets("aa2")
Set sh2 = Worksheets("aab3")
の（）内は質問文内の「１月、２月」などのシート名で、本番のシート名と置換えてください。
・Sheet3は使い捨て用、Sheet4は結果が出るシートですが、用意してから実行すること。
・対象行が両シート合わせて６万を越えるものは不可。
・見出しなどでデータの始まる行が、一概に決められないのでs1 = InputBox("第１シートデータ最初行＝")などで、各シートで計２箇所聞いています。
・clm = Val(InputBox("シートデータ最右列は第何列目（数字）＝"))は例えば、両シートにH列までデータがあれば８と応答してください。
・＃３のご回答の「なお」以下のご考慮はご尤もなのですが、可能性は少ないと思って、考慮していません。
・重複は１行のみ、３重複は２行Sheet4に書き出します。
・少数例でテスト済みですが、テストは不十分でエラーが出ないとも言えませんが、よろしく。

回答No.4

nihonjinn

＞どちらにしても正しい値が返ってきません。
たぶんシート名があっていないとおもいます。一月のデータが入っているシートをここでは「一月」というシート名にしているのですが、それをお使いのシート名に変えてみてください。行番号もしくは空白のいずれかが返るようになるとおもいます。
しかし、losedogさんの回答をみて気づきましたが、この数式ではA列、B列、C列それぞれ単独で重複しているか調べているので「ABC列のデータが全く同じもの」という条件を正しく考慮していない数式になっていませんでした。訂正すると
>各シートのＤ１のセルに，
>=A1&"●"&B1&"●"&C1
としておいてシート二月のE列に
=IF(COUNTIF(一月!D:D,D1)>=1,ROW(),"")
とします。
次に二月重複分をシート2月に抽出するとして
姓の項目は
=IF(ISERROR(SMALL(E:E,ROW())),"",INDEX(A:A,SMALL(E:E,ROW()),1))
名の項目は
=IF(ISERROR(SMALL(E:E,ROW())),"",INDEX(B:B,SMALL(E:E,ROW()),1))
メールアドレスの項目は
=IF(ISERROR(SMALL(E:E,ROW())),"",INDEX(C:C,SMALL(E:E,ROW()),1))
とします。

ここで関数の説明をすると
=IF(COUNTIF(一月!D:D,D1)>=1,ROW(),"")
COUNTIF(一月!D:D,D1)でシート2月のD1のデータが一月のD列に同じデータがいくつあるかを調べます。
ROW()
は計算式があるセルの行位置を出します。
つまりシート2月のD1のデータと同じ物がシート一月のD列にあれば行位置を返し、なければ空白を返します。
=IF(ISERROR(SMALL(E:E,ROW())),"",INDEX(A:A,SMALL(E:E,ROW()),1))
SMALL(E:E,ROW())
ではE列に並んだ行番号で「ROW()」番目に小さい数を抽出します。
ですからこの数式を下の行にコピーしていけば小さい順番で行番号が抽出されるわけですが、一番目、二番目と出していきたいわけですからSMALL(E:E,ROW())が入った数式が二行目から開始するならSMALL(E:E,ROW()-1)としておかなければいけないのです。
INDEX(A:A,---
でA列のSMALL(E:E,ROW())行目のデータを抽出するのです。

回答No.3

losedog

各シートの１行目はタイトル行（Ａ１に「姓」，Ｂ１に「名」，Ｃ１に「メールアドレス」と入力されている）として，

（１）Ａ列・Ｂ列・Ｃ列のデータを結合し，比較用データを作成する。

比較が簡単に出来るよう，Ａ列・Ｂ列・Ｃ列のデータを結合し，Ｄ列に表示します。

各シートのＤ１のセルに，

=A1&"●"&B1&"●"&C1

と入力して，Ｄ１のセルを必要なだけ下へコピーします。

なお，「●」はＡ列・Ｂ列・Ｃ列の区切であることを明確にするために入れてあるだけですので，Ａ列・Ｂ列・Ｃ列で使用しない文字であれば何でも結構です。
（区切り文字を入れないと，例えば「山田　一郎」と「山　田一郎」を同じ姓名と判断してしまいます。）

（２）２月と１月のシートのＤ列を比較し，同じデータを判断

２月のシートのＥ１のセルに，

=COUNTIF('1月'!D:D,)

と入力して，Ｅ１のセルを必要なだけ下へコピーします。

こうすると，「'1月'のシートのＤ列からＤ列までの範囲（'1月'!D:D）」から，「（２月のシートの）Ｄ列のセルとデータが同じセル」を検索し，同じセルの個数をＥ列のセルに表示します。
つまり，Ｅ列が「０」だった行は，１月と重複していないことになります。
なお，同じ月に同じデータが存在しないのであれば，Ｅ列は０か１になります。

（３）重複データのみを抽出する。

まず，計算式のままだと扱いづらいので，２月のシートのＤ列とＥ列を選択（Ｄ１，Ｅ１のセルの上の灰色部分を選択）し，メニューバーの「編集/コピー」を実行し，そのまま（Ｄ列とＥ列を選択したまま）「編集/形式を選択して貼付」を実行し，「値」にチェックしてからＯＫボタンを押せば，計算式が消えて，計算結果が残ります。

次に，「２月」のシートをコピーします。(シート名は何でも結構です。）

コピーしたシートを，Ｅ列で並び替えをします。
こうすると，Ｅ列が「０」の行（１月と重複していない）が一箇所に固まります。

最後に，Ｅ列が「０」の行を削除します。

これで，１月と２月を比較した場合の重複データのみを抽出できると思います。

回答No.2

nihonjinn

ROW()は列番号を返す関数なので
E1,F1,G1などからデータの抽出を開始するならこのままの式でいいですが、
E2,F2,G2などからデータを抽出を開始するのなら式の「ROW()」のところを「ROW()-1」と変えてやってください。

補足

=IF(AND(COUNTIF(一月!A:A,A1)=1,COUNTIF(一月!B:B,一月!B1)=1,COUNTIF(一月!C:C,一月!C1)=1),ROW()-1,"")
で行いましたが、数値として返って来るのは同じデータのセルの数でしょうか？行番号でしょうか？どちらにしても正しい値が返ってきません。また各項目のデータも、FALSEかNUM!が返ってきてしまいます。初心者のため、式の意味と返ってくる値の意味を教えていただけないでしょうか。どうもすいません

回答No.1

nihonjinn

二月のシートに一月と重複しているデータを出すには
二月のD列に
=IF(AND(COUNTIF(一月!A:A,A1)=1,COUNTIF(一月!B:B,一月!B1)=1,COUNTIF(一月!C:C,一月!C1)=1),ROW(),"")
と入れて必要なところまで下にコピーしておきます。
重複しているデータを抽出するには
姓の項目は
=IF(ISERROR(SMALL(D:D,ROW())),,INDEX(A:A,SMALL(D:D,ROW()),1))
名の項目は
=IF(ISERROR(SMALL(D:D,ROW())),,INDEX(B:B,SMALL(D:D,ROW()),1))
メールアドレスの項目は
=IF(ISERROR(SMALL(D:D,ROW())),,INDEX(C:C,SMALL(D:D,ROW()),1))
とし、必要な分だけ下にコピーして置きます。