こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

(EXCEL)重複したデーターの抽出について

EXCEL2003

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

<sheet1>
  A   B   C  D
1 りんご 青森 AAA BBB
2 みかん 愛媛 CCC DDD
3 みかん 青森 AAA BBB
4 りんご 青森 AAA BBB
5 みかん 愛媛 CCC DDD

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

<sheet2>
  A   B   C  D   E
 りんご 青森 AAA BBB  2
 みかん 愛媛 CCC DDD  2
 
ご教示のほどお願いします。

投稿日時 - 2011-06-10 20:25:17

QNo.6800156

すぐに回答ほしいです

質問者が選んだベストアンサー

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

 それでしたら、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以下に貼り付けて下さい。

 以上です。

投稿日時 - 2011-06-12 18:04:47

お礼

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

投稿日時 - 2011-06-12 19:36:34

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(8)

ANo.7

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

ROWS($A$1:$A1)

となっている箇所の

$A

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

ROWS($1:1)

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

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

 申し訳ございません、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行目以下に貼り付けて下さい。

 以上です。

投稿日時 - 2011-06-12 04:23:50

お礼

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

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

 A  B  C  D
りんご 青森 XXX 001
りんご 青森 XXX 002
りんご 青森 XXX 003


A  B  C   D    E
りんご 青森 XXX 001/002/003 3

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

投稿日時 - 2011-06-12 10:38:38

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

》 A列B列が同じでC列、D列が違う場合は重複とならず全部抽出されてしまいます

仰るとおりでした、Sheet2 に、Sheet1 のC列、D列を表示させる限りは。
Sheet2 には、Sheet1 のC列、D列を表示させなければよろしいのでは?(添付図参照))

例えば、Sheet1 の1行目のレコード(りんご、青森)でC列、D列がそれぞれ EEE、FFFだった場合、Sheet2 の「りんご、青森」のC列、D列には何を表示させたいのですか?「EEE、FFF」ですか、それとも「AAA、BBB」ですか?

投稿日時 - 2011-06-11 20:48:06

お礼

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

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

 A  B  C  D
りんご 青森 XXX 001
りんご 青森 XXX 002
りんご 青森 XXX 003

↓抽出結果

 A  B  C    D    E
りんご 青森 XXX 001/002/003 3

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

投稿日時 - 2011-06-12 09:55:51

ANo.5

#2です。
確かに本などでは、1列についてだけで、行う例が多く
2列で行う例は載っているのを見たことがない。
改めてやってみると思うようにならない。
其れでとりあえず2列だけで操作をやれば目的の
組み合わせはえられると思う。
ーーー
私の元の回答のシートを貼り付けた部分に判りにくい部分があるので
下記でやっていただけませんか、
(xxyyzzuuの辺りが判りにくかったかもしれないので)
例データ A1:D6
品物産地項目1項目2  <ーーー項目見出しです
りんご青森AAABBB
みかん愛媛CCCDDD
みかん青森AAABBB
りんご青森AAABBB
みかん愛媛CCCDDD
ーー
F1に「品物」G1に「産地」と入れてください(F2とG2は何も入れない)
ーー
データーフィルターフィルタオプションの設定
指定した範囲に、チェック
リスト範囲 A1:B6  <----A,B列だけ範囲指定する
検索条件範囲 F1:G1 <ーーこのボックスには何も入れなくても良いようだ
抽出範囲 I1:J6   <--結果も2列の指定
重複するレコードは無視する、にチェック
OK
ーー
結果
K1:L4
品物産地
りんご青森
みかん愛媛
みかん青森
ーー
注記
品物産地項目1項目2
りんご青森AAABBB
みかん愛媛CCCDDD
みかん青森EEEFFF
りんご青森GGGHHH
みかん愛媛IIIJJJ
どもやってみました。

投稿日時 - 2011-06-11 17:59:21

お礼

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

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

投稿日時 - 2011-06-12 09:57:49

添付図参照(Excel 2002)

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

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

投稿日時 - 2011-06-10 21:51:00

お礼

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

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

投稿日時 - 2011-06-11 15:33:46

ANo.3

 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行目以下に貼り付けて下さい。

 以上です。

投稿日時 - 2011-06-10 21:37:24

お礼

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

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

投稿日時 - 2011-06-11 15:48:36

ANo.2

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

投稿日時 - 2011-06-10 21:32:27

お礼

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

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

投稿日時 - 2011-06-11 15:31:59

ANo.1

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)

投稿日時 - 2011-06-10 20:35:35

お礼

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

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

投稿日時 - 2011-06-11 15:17:10

あなたにオススメの質問