プロが教えるわが家の防犯対策術!

エクセルのA列とB列とC列で重複するレコードのみを抽出して別の列に表示させたい。

エクセルのA列とB列とC列にそれぞれ1000行くらいのデータがあります。
それぞれの列内には重複レコードがあります。

この条件の中で

「A列とB列とC列に重複するデータすべて」

を抽出したいのですが、どんな方法がありますか。
抽出されたデータで重複レコードの場合は1件のみで表示したいです。

よろしくお願いします。


  A   B   C   抽出 
1-001-002--002--002
2-002-002--005--007
3-003-007--007--008
4-007-008--008--011
5-008-008--010
6-008-010--011
7-011-011--012
8-013-014--013

A 回答 (8件)

式が複雑になるということはそれだけ分かりにくく、計算が重くなるということです。

出来るだけ作業列を使ってわかりやすく処理することが肝要と考えます。
例えばA,B,C列の2行目からお示しのようなデータがあるとします。
D2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(AND(COUNTIF(A$2:A2,A2)=1,COUNTIF(B:B,A2)>0,COUNTIF(C:C,A2)>0),MAX(D$1:D1)+1,"")

D列にはA,B,C列に共通して含まれるデータがあれば上から順に番号が付けられます。その際にもしもA列でダブったデータがある場合には最初に出てきたデータに番号が振られます。

お求めのデータはE列に並べるとしてE2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(ROW(A1)>MAX(D:D),"",INDEX(A:A,MATCH(ROW(A1),D:D,0)))

D列が目障りでしたら列を非表示にすればよいでしょう。
    • good
    • 0
この回答へのお礼

解決しました。ありがとうございました!

お礼日時:2011/02/26 11:32

毎度なのだが、


慣れない関数使わなくてもできる。

A列の値
B列の値
C列の値
こうして1つの列に固めてしてしまえば、同じ数値3行続いてあればABCに存在と判断できる。

B列ではA列にあればマークし、C列ではB列にマークあればさらにマークする。

質問なんかするよりちょっと考えて工夫したらできるのでないか。

長い数式を考えるより悩まなくて済むし自分で出来ることが何よりの収穫。
長い数式は理解しないと保守できない。シンプルであるべき。数式にこだわる必要もない。
    • good
    • 2
この回答へのお礼

ありがとうございました!

お礼日時:2011/02/26 11:34

回答が多数出てから言うのもなんだが、質問の意味が判りにくい?


言い直してみれば、
「各行のA,B、C列で、A=B列、B=C列、A=C列または3列等しい行について、その等しい値を抜き出す。
ただし前に抜き出した値は2度目から表示しない。」
こんなことかな?
ーー
関数では相当複雑になるので
VBAで標準モジュールに
Sub test01()
d = Range("A65536").End(xlUp).Row '最終行
' MsgBox d
Dim l(100)
Dim p '配列のポインタ
p = 1
For i = 1 To d
A = Cells(i, "A"): B = Cells(i, "B"): C = Cells(i, "C")
If A = B Then
x = A
ElseIf B = C Then
x = B
ElseIf C = A Then
x = C
End If
'MsgBox x
'重複あるかチェック
For j = 1 To p
If x = l(j) Then GoTo p1 '同じもの既に有り
Next j
l(p) = x
p = p + 1
p1:
Next i
'--セルに結果セット F列に
For j = 1 To p - 1
Cells(j, "F") = l(j)
Next j
End Sub
重複ペアーは上記では100個までにしているがDim l(100)を見込みで適宜変更。
データ例 F列が結果 質問データの場合結果E列
A列  B列  C列       E列  F列
12222
22577
37788
7881111
881013
810114
111112
131313
414
    • good
    • 0
この回答へのお礼

ありがとうございます。

質問の仕方が悪かったです。すみませんでした。

お礼日時:2011/02/26 11:35

◆関数でならば、


【EXCEL2007以降】
D1=IFERROR(INDEX($A$1:$A$9,SMALL(INDEX((FREQUENCY($A$1:$A$8,$A$1:$A$8)*COUNTIF($B$1:$B$8,$A$1:$A$9)*COUNTIF($C$1:$C$8,$A$1:$A$9)=0)*10^5+ROW($A$1:$A$9),),ROW(A1))),"")
★下にコピー

【EXCEL2003以前】
D1=IF(ROW(A1)>SUMPRODUCT((FREQUENCY($A$1:$A$8,$A$1:$A$8)*COUNTIF($B$1:$B$8,$A$1:$A$9)*COUNTIF($C$1:$C$8,$A$1:$A$9)>0)*1),"",SMALL(INDEX((FREQUENCY($A$1:$A$8,$A$1:$A$8)*COUNTIF($B$1:$B$8,$A$1:$A$9)*COUNTIF($C$1:$C$8,$A$1:$A$9)=0)*10^5+$A$1:$A$9,),ROW(A1)))
★下にコピー
「エクセルでA列B列C列の重複するレコード」の回答画像5
    • good
    • 0
この回答へのお礼

解決しました。ありがとうございます。

お礼日時:2011/02/26 11:35

質問文から判断して私の提示した数式はA列とC列が同じ場合もヒットするようにしましたが、例示のデータでは8行目の「013」が抽出されていませんね。



もしこの条件が必要ないなら、B列のデータだけ比較することになるので、以下のようにはるかに簡単な数式になります。

=INDEX(B:B,SMALL(INDEX((($A$2:$A$1000<>$B$2:$B$1000)*($B$2:$B$1000<>$C$2:$C$1000)*1000+ROW($A$2:$A$1000)),),ROW(A1)))&""
    • good
    • 0
この回答へのお礼

ありがとうございました!

質問分かりにくくてすみませんした。

お礼日時:2011/02/26 11:36

表示データ数が多いと実用的ではありませんが、数式だけで表示する例です。



例えば2行目からデータがあるなら、以下の式を入力して下方向にオートフィルしてください。

=IF(INDEX(A:A,SMALL(INDEX((($A$2:$A$1000<>$B$2:$B$1000)*($B$2:$B$1000<>$C$2:$C$1000)*($A$2:$A$1000<>$C$2:$C$1000)*1000+ROW($A$2:$A$1000)),),ROW(A1)))=INDEX(B:B,SMALL(INDEX((($A$2:$A$1000<>$B$2:$B$1000)*($B$2:$B$1000<>$C$2:$C$1000)*($A$2:$A$1000<>$C$2:$C$1000)*1000+ROW($A$2:$A$1000)),),ROW(A1))),INDEX(A:A,SMALL(INDEX((($A$2:$A$1000<>$B$2:$B$1000)*($B$2:$B$1000<>$C$2:$C$1000)*($A$2:$A$1000<>$C$2:$C$1000)*1000+ROW($A$2:$A$1000)),),ROW(A1))),INDEX(C:C,SMALL(INDEX((($A$2:$A$1000<>$B$2:$B$1000)*($B$2:$B$1000<>$C$2:$C$1000)*($A$2:$A$1000<>$C$2:$C$1000)*1000+ROW($A$2:$A$1000)),),ROW(A1))))&""
    • good
    • 0

[フィルタオプションの設定]による方法(添付図参照)



E2: =(COUNTIF(B$2:B$9,A2)>0)*(COUNTIF(C$2:C$9,A2)>0)

[抽出先]    → “指定した範囲”
[リスト範囲  → $A$1:$A$9
[検索条件範囲] → $E$1:$E$2
[抽出範囲]   → $D$1
“重複するレコードは無視する”にチェック入れ
「エクセルでA列B列C列の重複するレコード」の回答画像2
    • good
    • 0
この回答へのお礼

解決しました。ありがとうございました!

お礼日時:2011/02/26 11:36

こんばんは!


一例です。

↓の画像のように作業用の列を設けています。
(数値だけでなく、文字列の場合も対応できるようにしてみました)

作業列D2セルに
=IF(AND(COUNTIF($A$2:A2,A2)=1,COUNTIF(B:B,A2),COUNTIF(C:C,A2)),ROW(),"")
という数式を入れ、オートフィルでずぃ~~~!っと下へコピー!

結果のF2セルに
=IF(COUNT(D:D)<ROW(A1),"",INDEX(A:A,SMALL(D:D,ROW(A1))))
という数式を入れ、オートフィルで下へコピーすると画像のような感じになります。

参考になれ良いのですが・・・m(__)m
「エクセルでA列B列C列の重複するレコード」の回答画像1
    • good
    • 0
この回答へのお礼

解決しました!
ありがとうございました。

お礼日時:2011/02/26 11:37

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Qエクセルで重複しているデータの抽出のしかたを教えてください。

エクセルで重複しているデータを抽出したいのですが・・。なにぶん初心者なもので簡単な方法があればよいのですが・・。
A列に日付 B列に企業名このデータが300件ほどあります。

 Å列      B列
1月 1日    AAAA社
1月 4日   BBBB社
1月 8日   CCCC社
2月 1日   BBBB社
2月20日    DDDD社
3月 2日   AAAA社

と、あるとしてB列の企業名が重複しているものを抽出したいのですが・・・。この場合 AAAA社とBBBB社ですが。
これを簡単に抽出する方法はないでしょうか?
教えてください お願いいたします。

Aベストアンサー

企業名がセルB2からセルB300まで入力されていると仮定します。
セルC2に IF(COUNTIF($B$2:B2,B2)>1,"*","") を入力し、セルC300までコピーして下さい。すると重複したデータの二番目以降に "*" のマークが付きます。
(注) 数式を下のセルまでコピーするため、COUNTIF($B$2 は絶対参照にして下さい。

Qエクセルで複数列に共通した項目の抽出方法

エクセルで複数列で重複したセルを抽出する方法がわかりません。
具体的には、ABC各列にランダムな6桁の数字がそれぞれ500行づつあり、AとB列で重複(同じ)数字があれば、D列に列挙(重複した個数ではなく、たとえば310257などと重複していた数字)し、またABCの3列ともに重複している数字をE列に列挙するなど、複数列で重複したセルを抽出するにはどういう方法があるんでしょうか?

Aベストアンサー

[フィルタオプションの設定]で可能です。“頭を痛める”関数は一切不要!
簡単のために、列A~Cのデータが「500」行ずつでなく下のように「10」行ずつある場合を考えます。ただし、先頭行は同じ文字列(例えば data)を入力しておきます。

   A    B    C    D    E
1  data  data  data  data  data
2  123456 123457 123462 123461 123462
3  123466 123459 123465 123462 123466
4  123456 123461 123465 123466
5  123461 123462 123465 123456
6  123460 123461 123466
7  123456 123466 123463
8  123462 123461 123465
9  123456 123466 123457
10 123461 123463 123466
11 123461 123456 123458

1.[データ]→[フィルタ]→[フィルタオプションの設定]を実行
2.“指定した範囲”に目玉入れ
3.[リスト範囲]ボックス内にマウスカーソルを置き、範囲 B1:B11 を
  撫で撫で
4.[検索条件範囲]ボックス内にマウスカーソルを置き、範囲 A1:A11
  を撫で撫で
5.[抽出範囲]ボックス内にマウスカーソルを置き、セル D1 をチョー
  ン
6.“重複するレコードは無視する”にチェック入れ
7.[OK]をクリック
8.ステップ1~7の繰り返し。ただし、次のように読み替え
  範囲 B1:B11 → 範囲 C1:C11
  範囲 A1:A11 → 範囲 D1:D5
  セル D1   → セル E1

[フィルタオプションの設定]で可能です。“頭を痛める”関数は一切不要!
簡単のために、列A~Cのデータが「500」行ずつでなく下のように「10」行ずつある場合を考えます。ただし、先頭行は同じ文字列(例えば data)を入力しておきます。

   A    B    C    D    E
1  data  data  data  data  data
2  123456 123457 123462 123461 123462
3  123466 123459 123465 123462 123466
4  123456 123461 123465 123466
5  123461 123462 123465 123456
6  1234...続きを読む

Qエクセルで複数の列が一致する重複データを削除したい

こんにちは。
よろしくお願いします。

今私はエクセルに吐き出した顧客データを整理しているのですが質問があります。

エクセルに出した顧客データの内容は添付イメージの様な感じです。


固定電話、携帯、姓、名で列が分かれています。
この中で

固定、姓、名が一致するデータ
携帯、姓、名が一致するデータ

    で重複が無いかを探したいです。

オートフィルタを使用して確認しようとしていたのですがデータが膨大なので手作業は難しいと判断しました。

どなたかお分かりになる方いましたらご助言をお願いします。

Aベストアンサー

No.2です。仮に

TEL MOBILE LAST_NAME FIRST_NAME
0344445555 09088883333 Yamada Taro -----1
0344445555 (空白) Yamada Taro -----2
(空白) 09088883333 Yamada Taro -----3

という3件のデータがあった場合
1と2は電話番号で重複、1と3は携帯番号で重複していますが
これらを重複とみなすのかどうか?
(それ以前に上記のようなパターンが存在するかどうか?、ですが)

フィルタオプションで重複レコードを除く方法だと、この3件は全て
「重複ではない」とみなされます。その点はご注意下さい。

Q2つのシート間での重複データのチェック

Excelについて教えてください。
以下の2つのシートがあります。

Sheet1
すずき 03-0000-0000
やまだ 03-1111-1111
たなか 03-2222-2222

Sheet2
03-1111-1111
03-4444-4444
03-0000-0000
03-2222-2222

Sheet1には名称と電話番号、Sheet2には別所から抽出した電話番号のみのデータがあります。
Sheet1のデータの中からSheet2に電話番号があるものだけを知りたいのですが、たとえばSheet1の 各行のC列 に Sheet2に一致する番号があった場合は●等付けるにはどうすればよいでしょうか。

よろしくお願いします

Aベストアンサー

シート1のC列に式を入れます。
=IF(ISNA(VLOOKUP(B1,Sheet2!A:A,1,FALSE))=TRUE,"-","●")

【式の説明】
シート1のセルB1の値が、シート2のA列に、存在する場合は●を、存在しない場合は-をセット。

Qエクセル【A列とB列の不一致を知りたいです】

初めまして。
お忙しい中、大変申し訳ございませんが、下記に関して、ご指導のほど、宜しくお願い致します。

***************
【例】
A列    B列

あめ    チョコ
ガム    ポテトチップス
チョコ   あめ
       ガム


という列があり、A列はA1000まで続き、B列はB3000まで続きます。
必ずしも、A列の横に同じお菓子名があるわけではありません。

そこで、A列とB列を比較し、B列にしかないもの(上記「例」ではポテトチップス)のセルに色づけ、もしくはC列に、不一致するものだけ「NG」等が記載される数式を教えてください。

お忙しい中、大変申し訳ございませんが、
何卒宜しくお願い申し上げます。

Aベストアンサー

条件付書式で、B列のセルに
「数式が」
=ISERROR(VLOOKUP(B1,A:A,1,FALSE))
で書式を設定し下にコピーすると、無いものだけが書式変更されます。

同じように、B列の隣のC列に
=IF(ISERROR(VLOOKUP(B1,A:A,1,FALSE)),"NG","")
として下にコピーしても隣にNGと表示できます。

Qエクセルで、条件に一致した行を別のセルに抜き出す方法

エクセルで、指定した条件に一致するセルを含む行をすべて抜き出す方法が知りたいです。

たとえば、

<A列> <B列> <C列>
7/1 りんご 100円
7/2 ぶどう 200円
7/2 すいか 300円
7/3 みかん 100円

このような表があって、100円を含む行をそのままの形で、
別のセル(同じシート内)に抜き出したいのですが。

7/1 りんご 100円
7/3 みかん 100円

抽出するだけならオートフィルターでもできますが、
抽出結果を自動的に、別の場所に、常に表示させておきたいのです。

初歩的な質問だと思いますが、検索しても分からなかったので、よろしくお願いします。

Aベストアンサー

同じ質問が結構よく出てますが、そんなに初歩的でもありません
別シートのA1セルに「100円」と入力し、そのシートの任意のセルに以下の式を貼り付けて下さい。後は、下方向、右方向にコピー。
日付のセル書式は「日付」形式に再設定してください

=IF(COUNTIF(Sheet1!$C:$C,$A$1)>=ROW(A1),INDEX(Sheet1!A:A,LARGE(INDEX((Sheet1!$C$1:$C$500=$A$1)*ROW(Sheet1!$C$1:$C$500),),COUNTIF(Sheet1!$C:$C,$A$1)-ROW(A1)+1)),"")

データ範囲は500行までとしていますが、必要に応じて変更して下さい

QExcelですが、同一データが複数あるとき、検索して、その全部を抽出する方法

Excelですが、検索キーに、同一データ(レコード)が複数あるとき、検索して、その全部を抽出表示する方法を教えてください。

Vlookupは、同一データが複数あるとき、最初の行(レコード)を1つだけ抽出してきます。2つ目、3つ目の行は抽出できませんが、その全部を抽出する方法がありますでしょうか。

例えば、以下の例で、「A株式会社」をキーに検索した場合、<検索結果>シートのように、該当のレコード3つ(行2~4)を抽出して表示するようにしたいのですが、方法はありますでしょうか。よろしくお願いします。

<データシート>
 列A  列B    列C 列C
行1 No 会社名   所属 担当者
行2 1 A株式会社  ○事業部  坂下順人
行3 2 A株式会社  △事業部  滿山友人
行4 3 A株式会社  △事業部  目標達子
行5 4 B株式会社  設計部山下清人
行6 5 B株式会社  営業部横浜美人
行7 6 C株式会社  営業部川崎次郎
行8・・・・・・・・・・・・

<検索結果:別のシートにおいて>
行1 会社名 所属  担当者
行2 A株式会社 ○事業部 坂下順人
行3 A株式会社 △事業部 滿山友人
行4 A株式会社 △事業部 目標達子

Excelですが、検索キーに、同一データ(レコード)が複数あるとき、検索して、その全部を抽出表示する方法を教えてください。

Vlookupは、同一データが複数あるとき、最初の行(レコード)を1つだけ抽出してきます。2つ目、3つ目の行は抽出できませんが、その全部を抽出する方法がありますでしょうか。

例えば、以下の例で、「A株式会社」をキーに検索した場合、<検索結果>シートのように、該当のレコード3つ(行2~4)を抽出して表示するようにしたいのですが、方法はありますでしょうか。よろしくお...続きを読む

Aベストアンサー

#03です
>最後のROW(T1)で、T1を参照しておりますが

ROW(T1)は「1番目」を意味しています。T1セルに何もなくても関係ありません。コピーしたの行はROW(T2)になりますが、これは2番目のという意味です

#REF!エラーになりますか?
私が示した条件と何か変えていませんか?
別シートのA1に「抽出する会社名」を入力してあれば、回答したような結果になるはずですが‥ (テストしてありますので)

ただし2000行もあるならVBAの方が良いかもしれません
きっと「シートが重くなる」と思います

QExcel>複数の列を比較して、重複セルの個数を得る方法

Excelにて

A列:aabb,bbcc,aacc
B列:aacc,bbdd,ccdd
C列:aacc,eehh,ccdd

A,B列間で、重複セル「aacc」の1コ、
B,C列間で、重複セル「aacc」「ccdd」の2コ、
A,C列間で、重複セル「aacc」の1コ、
A,B,C列間で、重複セル「aacc」の1コ

というように、複数の列を比較して、重複セルの個数を得る方法を教えてくださ
い。複数の列では難解なら、2つの列を比較した場合でも結構です。
なお、重複セルの値、例えば「aacc」など得る必要はありません。個数のみで結
構です。宜しくお願い致します。

Aベストアンサー

例えば次のような表になっているとします。
A2セルから下行にA列のデータが、B2セルから下行にB列のデータが、C2セルから下行にC列のデータがあるとします。
D1セルにはA-B、E1セルにはA-C,F1セルにはB-C、G1せるにはA-B-Cと入力します。
D2セルには次の式を入力して下方にオートフィルドラッグします。
=IF(AND(COUNTIF(A$2:A2,A2)=1,COUNTIF(B:B,A2)>0),1+MAX(D$1:D1),"")
E2セルには次の式を入力して下方にオートフィルドラッグします。
=IF(AND(COUNTIF(A$2:A2,A2)=1,COUNTIF(C:C,A2)>0),1+MAX(E$1:E1),"")
F2セルには次の式を入力して下方にオートフィルドラッグします。
=IF(AND(COUNTIF(B$2:B2,B2)=1,COUNTIF(C:C,B2)>0),1+MAX(F$1:F1),"")
G2セルには次の式を入力して下方にオートフィルドラッグします。
=IF(AND(COUNTIF(A$2:A2,A2)=1,COUNTIF(B:B,A2)>0,COUNTIF(C:C,A2)>0),1+MAX(G$1:G1),"")
次に答えを表示させるためにD1セルからG1セルまでを範囲として選び、コピーしたのちにI1セルをアクティブセルにして貼り付けます。
H2セルには重複するセルの数とでも入力します。
I2セルには次の式を入力しL2セルまでオートフィルドラッグします。
=MAX(D:D)

例えば次のような表になっているとします。
A2セルから下行にA列のデータが、B2セルから下行にB列のデータが、C2セルから下行にC列のデータがあるとします。
D1セルにはA-B、E1セルにはA-C,F1セルにはB-C、G1せるにはA-B-Cと入力します。
D2セルには次の式を入力して下方にオートフィルドラッグします。
=IF(AND(COUNTIF(A$2:A2,A2)=1,COUNTIF(B:B,A2)>0),1+MAX(D$1:D1),"")
E2セルには次の式を入力して下方にオートフィルドラッグします。
=IF(AND(COUNTIF(A$2:A2,A2)=1,COUNTI...続きを読む

QB列の値がA列にあるかを調べる関数

excel2010を使っています。
題名の通りなのですが、B列にある値がA列にあるかを関数を使って求めたいと思います。

B列に290個、A列に259個の値が入力されていて、

・B列にあってA列にないもの
・A列にあってB列にないもの

の両方を見つけ出したいのですが、よい関数を教えてください。exact関数ではダメでした。

Aベストアンサー

C1セルに
=IF(COUNTIF(B:B,A1),"","このAはAにあってBに無い")
以下コピー
D1セルに
=IF(COUNTIF(A:A,B1),"","このBはBにあってAに無い")
以下コピー

とそれぞれ埋めて検査します。

QExcelで[表1]にあって、[表2]にないものを抽出する関数

Excelで[表1]にあって、[表2]にないものを抽出する関数

例)[表1]   [表2]
   A社     A社
   D社     D社
   R社     P社
   P社     R社
   D社
   F社
   F社

上記は簡単に書きましたが、表1に重複するものも含め、300社程度あるなかで
表2にリストアップされていない会社を見つける関数やその他方法論があれば
ぜひ教えてください!
(例でいえば、F社を見つける方法です。)

抽出するのは別シートでも、同じシートでも構いません。

Excel2003でも対応できるものであれば、なお嬉しいです。

よろしくお願いします。

Aベストアンサー

Countif関数で同じものがいくつあるか数えさせます。
   A   B        C
  [表1]表2にある数   [表2]
   A社          A社
   D社          D社
   R社          P社
   P社          R社
   D社
   F社
   F社
だとして
B列に =Countif(C:C,A2)
と入れて下までコピィすれば 表2に同じものがいくつあるか出ますので
0 がないものです。


このQ&Aを見た人がよく見るQ&A

このカテゴリの人気Q&Aランキング