（EXCEL）重複したデーターの抽出について

質問

EXCEL2003

シート「sheet1」に下記のようなデーターがあります。

＜sheet1＞
　　Ａ　　　Ｂ　　　Ｃ　　Ｄ
1　りんご　青森　ＡＡＡ　ＢＢＢ
2　みかん　愛媛　ＣＣＣ　ＤＤＤ
3　みかん　青森　ＡＡＡ　ＢＢＢ
4　りんご　青森　ＡＡＡ　ＢＢＢ
5　みかん　愛媛　ＣＣＣ　ＤＤＤ

A列とB列が同じ値のものをシート「sheet2」に重複しているデーター
は1行だけになり、Ｅ列にカウントした数が入力されるようにしたいです。
（Ｃ列やD列は同じ値でなくてもＡ列とＢ列が同じ値の時にカウントして抽出したいです）

＜sheet2＞
　　Ａ　　　Ｂ　　　Ｃ　　Ｄ　　　Ｅ
　りんご　青森　ＡＡＡ　ＢＢＢ　　２
　みかん　愛媛　ＣＣＣ　ＤＤＤ　　２

ご教示のほどお願いします。

回答No.8

kagakusuki · ベストアンサー

>Ｄ列の値を結合したいです。

　それでしたら、ANo.7の方法に、更に以下の様に追加して下さい。
　まず、Sheet3のC1セルに次の数式を入力して下さい。

=IF($A1="","",$A1&COUNTIF($A$1:$A1,$A1))

　次に、Sheet3のD1セルに次の数式を入力して下さい。

=IF($A1="","",IF($C1=$A1&1,Sheet1!$D1,VLOOKUP($A1&COUNTIF($A$1:$A1,$A1)-1,$C$1:$D1,2,FALSE)&"/"&Sheet1!$D1))

　次に、Sheet3のC1～D1の範囲をコピーして、同じ列の2行目以下に貼り付けて下さい。

　次に、Sheet2のD1セルに入力している数式を、次の数式と差し替えて下さい。

=IF(ROWS($1:1)>COUNT(Sheet3!$B:$B),"",VLOOKUP($A1&$B1&COUNTIF(Sheet3!$A:$A,$A1&$B1),Sheet3!$C:$D,2,FALSE))

　次に、Sheet2のD1セルをコピーして、Sheet2のD2以下に貼り付けて下さい。

　以上です。

お礼

すごいです！！！完璧です！！！
希望した通りの抽出です。
ありがとうございました。

回答No.7

kagakusuki

回答番号ANo.3です。
　申し訳ございません、何をボケていたのか、ANo.3には幾つかミスが御座いました。
　ANo.3において、数式を入力すべきシート名が、Sheet1となっている箇所は、Sheet2の間違いです。
　但し、数式中にある参照先のシートを表している「Sheet1!」となっている箇所は、間違いでは御座いませんので、数式中において「Sheet1!」となっている箇所は修正しないで下さい。
　又、数式の動作には全く影響はしませんが、数式中で

ROWS($A$1:$A1)

となっている箇所の

$A

の部分は、単なる消去し忘れていただけの部分で、関数の動作には不要な部分ですから、

ROWS($1:1)

に修正して頂いた方が、より望ましいと思います。（この部分は修正して頂かなくとも正常に動作しますが、文字数が増える分だけ、パソコンのメモリーが無駄に使われる事になります）

>Ｅ列がたぶん同じ値の数をカウントしているんだと思うんですが、データーの数と合いません。

　申し訳ございません、Sheet2のE1セルに入力する数式が誤っておりました。

【誤】
=IF(ROWS($A$1:$A1)>COUNT(Sheet3!$B:$B),"",COUNTIF(Sheet3!$A:$A,Sheet3!$A1))

【正】
=IF(ROWS($1:1)>COUNT(Sheet3!$B:$B),"",COUNTIF(Sheet3!$A:$A,$A1&$B1))

　これらの間違いを修正致しますと、 ANo.3は次の様になります。

　まず、適当なシート（例えばSheet3）のA1セルに次の数式を入力して下さい。

=Sheet1!$A1&Sheet1!$B1

　次に、Sheet3のB1セルに次の数式を入力して下さい。

=IF(AND($A1<>"",COUNTIF($A$1:$A1,$A1)=1),ROW(),"")

　次に、Sheet3のA1～B1の範囲をコピーして、同じ列の2行目以下に貼り付けて下さい。

　次に、Sheet2のA1セルに次の数式を入力して下さい。

=IF(ROWS($1:1)>COUNT(Sheet3!$B:$B),"",INDEX(Sheet1!A:A,SMALL(Sheet3!$B:$B,ROWS($1:1))))

　次に、Sheet2のA1セルをコピーして、Sheet2のB1～D1の範囲に貼り付けて下さい。
　次に、Sheet2のE1セルに次の数式を入力して下さい。

=IF(ROWS($1:1)>COUNT(Sheet3!$B:$B),"",COUNTIF(Sheet3!$A:$A,$A1&$B1))

　次に、Sheet2のA1～E1の範囲をコピーして、同じ列の2行目以下に貼り付けて下さい。

　以上です。

お礼

回答ありがとうございます。

ほとんど完璧です。
図々しくあと一つお願いなんですが、Ｄ列の値を結合したいです。

　Ａ　 Ｂ　 Ｃ　　Ｄ
りんご　青森　XXX　001
りんご　青森　XXX　002
りんご　青森　XXX　003
↓

Ａ　 Ｂ　 Ｃ　　　Ｄ　　　　E
りんご　青森　XXX　001/002/003 3

このようにならないでしょうか？

回答No.6

noname#204879

[No.4お礼]へのコメント、

》 Ａ列Ｂ列が同じでＣ列、Ｄ列が違う場合は重複とならず全部抽出されてしまいます

仰るとおりでした、Sheet2 に、Sheet1 のＣ列、Ｄ列を表示させる限りは。
Sheet2 には、Sheet1 のＣ列、Ｄ列を表示させなければよろしいのでは？（添付図参照））

例えば、Sheet1 の１行目のレコード（りんご、青森）でＣ列、Ｄ列がそれぞれ ＥＥＥ、ＦＦＦだった場合、Sheet2 の「りんご、青森」のＣ列、Ｄ列には何を表示させたいのですか？「ＥＥＥ、ＦＦＦ」ですか、それとも「ＡＡＡ、ＢＢＢ」ですか？

お礼

回答ありがとうございます。

確かにC/D列が違う場合は何を抽出するのかわかりませんよね・・・。
実際にはD列はNOが入力されていてA列～C列がまったく同じ値でNoが入力されているD列だけが違う値
が入力されています。

　Ａ　　Ｂ　　Ｃ　　Ｄ
りんご　青森　XXX　001
りんご　青森　XXX　002
りんご　青森　XXX　003

↓抽出結果

　Ａ　　Ｂ　　Ｃ　　　　Ｄ　　　　Ｅ
りんご　青森　XXX　001/002/003　3

このような結果になるよにしたいです。
説明が足りなくてすみません。

回答No.5

imogasi

#2です。
確かに本などでは、１列についてだけで、行う例が多く
２列で行う例は載っているのを見たことがない。
改めてやってみると思うようにならない。
其れでとりあえず２列だけで操作をやれば目的の
組み合わせはえられると思う。
ーーー
私の元の回答のシートを貼り付けた部分に判りにくい部分があるので
下記でやっていただけませんか、
（xxyyzzuuの辺りが判りにくかったかもしれないので）
例データ　Ａ１：Ｄ６
品物 産地 項目１ 項目２　　＜ーーー項目見出しです
りんご 青森 ＡＡＡ ＢＢＢ
みかん 愛媛 ＣＣＣ ＤＤＤ
みかん 青森 ＡＡＡ ＢＢＢ
りんご 青森 ＡＡＡ ＢＢＢ
みかん 愛媛 ＣＣＣ ＤＤＤ
ーー
Ｆ１に「品物」Ｇ１に「産地」と入れてください（Ｆ２とＧ２は何も入れない）
ーー
データーフィルターフィルタオプションの設定
指定した範囲に、チェック
リスト範囲　Ａ１：Ｂ６　　＜－－－－Ａ，Ｂ列だけ範囲指定する
検索条件範囲　Ｆ１：Ｇ１　＜ーーこのボックスには何も入れなくても良いようだ
抽出範囲　Ｉ１：Ｊ６　　　＜－－結果も２列の指定
重複するレコードは無視する、にチェック
ＯＫ
ーー
結果
Ｋ１：Ｌ４
品物 産地
りんご 青森
みかん 愛媛
みかん 青森
ーー
注記
品物 産地 項目１ 項目２
りんご 青森 ＡＡＡ ＢＢＢ
みかん 愛媛 ＣＣＣ ＤＤＤ
みかん 青森 ＥＥＥ ＦＦＦ
りんご 青森 ＧＧＧ ＨＨＨ
みかん 愛媛 ＩＩＩ ＪＪＪ
どもやってみました。

お礼

回答ありがとうございます。

Ｃ／Ｄ列がなければ重複となり抽出されます。
でもＣ／Ｄ列も一緒にsheet2に表示させたいです。

回答No.4

noname#204879

添付図参照(Excel 2002)

Sheet1
　　　　E2: =A2&"_"&B2
　　　　F2: =COUNTIF(E$2:E$10,E2)

Sheet2 をアクティブにして、次の条件で[フィルタオプションの設定]を実行
[抽出先]　　　 → “指定した範囲”
[リスト範囲]　 → Sheet1!$A$1:$F$10
[検索条件範囲] → $G$1:$G$2
“重複するレコードは無視する”にチェック入れ

お礼

回答ありがとうございます。
教えていただいた方法はＡ列～Ｄ列がまったく同じ場合は抽出できますが、
Ａ列Ｂ列が同じでＣ列、Ｄ列が違う場合は重複とならず全部抽出されてしまいます。

EXCELのバージョンは2003です。分かりづらい場所に書いてしまいすみません。

回答No.3

kagakusuki

Excel2007よりも前のバージョンでは、複数条件の抽出を行う際には作業列が必要になると思います。
　まず、適当なシート（例えばSheet3）のA1セルに次の数式を入力して下さい。

=Sheet1!$A1&Sheet1!$B1

　次に、Sheet3のB1セルに次の数式を入力して下さい。

=IF(AND($A1<>"",COUNTIF($A$1:$A1,$A1)=1),ROW(),"")

　次に、Sheet3のA1～B1の範囲をコピーして、同じ列の2行目以下に貼り付けて下さい。

　次に、Sheet1のA1セルに次の数式を入力して下さい。

=IF(ROWS($A$1:$A1)>COUNT(Sheet3!$B:$B),"",INDEX(Sheet1!A:A,SMALL(Sheet3!$B:$B,ROWS($A$1:$A1))))

　次に、Sheet1のA1セルをコピーして、Sheet1のB1～D1の範囲に貼り付けて下さい。
　次に、Sheet1のE1セルに次の数式を入力して下さい。

=IF(ROWS($A$1:$A1)>COUNT(Sheet3!$B:$B),"",COUNTIF(Sheet3!$A:$A,Sheet3!$A1))

　次に、Sheet1のA1～E1の範囲をコピーして、同じ列の2行目以下に貼り付けて下さい。

　以上です。

お礼

回答ありがとうございます。
C/D列の値が違っても抽出できました。
Ｅ列がたぶん同じ値の数をカウントしているんだと思うんですが、データーの数と合いません。

EXCELのバージョンは2003です。分かりづらい場所に書いてしまいすみません。

回答No.2

imogasi

重複の無いリストを付くrのは、関数でも出来るが、操作の方をお奨めする
A,列とB列を組み合わせて、重複しない組み合わせを出す操作
例データ　xx yy yzz uuは項目見出し(エクセル表では必須と考えたほうが良い、リスト範囲やテーブル）
xx yy zz uu
りんご 青森 ＡＡＡ ＢＢＢ
みかん 愛媛 ＣＣＣ ＤＤＤ
みかん 青森 ＡＡＡ ＢＢＢ
りんご 青森 ＡＡＡ ＢＢＢ
みかん 愛媛 ＣＣＣ ＤＤＤ
ーー
条件
F1:G1に
xx yy
条件の中身は入れない。
ーー
操作
データーフィルターフィルタオプションの設定
指定した範囲にチェック
リスト範囲　$A$1:$D$6
検索条件範囲　$F$1:$G$1
抽出する範囲　$I$1:$l$6
choufukusuru
レコードは無視する
結果
xx yy zz uu
りんご 青森 ＡＡＡ ＢＢＢ
みかん 愛媛 ＣＣＣ ＤＤＤ
みかん 青森 ＡＡＡ ＢＢＢ
件数はM2に
=SUMPRODUCT(($A$2:$A$6=I2)*($B$2:$B$6=J2)*1)
下方向に式複写
結果
xx yy zz uu
りんご 青森 ＡＡＡ ＢＢＢ 2
みかん 愛媛 ＣＣＣ ＤＤＤ 2
みかん 青森 ＡＡＡ ＢＢＢ 1
　　２００７ならCOUNTIFS関数が使え、簡単だろう。
エクセルの質問にはバージョンを必ず書くこと。

お礼

回答ありがとうございます。
教えていただいた方法はＡ列～Ｄ列がまったく同じ場合は抽出できますが、
Ａ列Ｂ列が同じでＣ列、Ｄ列が違う場合は重複とならず全部抽出されてしまいます。

EXCELのバージョンは2003です。分かりづらい場所に書いてしまいすみません。

回答No.1

mshr1962

E1=SUMPRODUCT(($A$1:$A$100=$A1)*($B$1:$B$100=$B1)*($C$1:$C$100=$C1)*($D$1:$D$100=$D1))
で下方にコピー

ちなみにEXCEL2007なら新関数COUNTIFS（複数条件のカウント関数）があるので
そちらの方が便利です。
=COUNTIFS($A:$A,$A1,$B:$B,$B1,$C:$C,$C1,$D:$D,$D1)

お礼

回答ありがとうございます。
E1に教えてもらった数式をいれてみましたが、カウントした値が実際のデーターの数と
合わないです。

EXCELのバージョンは2003で文章の一番最初に書いていたのですが、見づらくてすみません。