EXCELで別シートから値の一致しないデータを抽出する方法

Sheet1のA列に
A1 123
A2 456
A3 789
のデータが入力されており、Sheet2のB列に
B1 666
B2 123
B3 888
B4 999
B5 456
B6 888
のデータが入力されている場合に、
下の表のように、Sheet1のA4以降のセルにSheet2のB列から
・Sheet1のA1～A3に一致しないデータ
・Sheet2のB列で重複しないデータ
・降順
の3点の条件で抽出する方法を関数で教えてください。
よろしくお願いします。

Sheet1
A1 123
A2 456
A3 789
A4 999 <-ここからSheer2のデータでA1～A3以外の値でSheet2の重複をさけて降順で表示させたい
A5 888
A6 666

No.6 回答者： maron--5 回答日時： 2009/12/26 00:43

◆少し、式が長くなりましたが、作業列なしの方法です

A4=IF(ROW(A1)>SUMPRODUCT((Sheet2!$B$1:$B$7<>"")*(COUNTIF($A$1:$A$3,Sheet2!$B$1:$B$7)=0)/COUNTIF(Sheet2!$B$1:$B$7,Sheet2!$B$1:$B$7&"")),"",LARGE(IF(FREQUENCY(Sheet2!$B$1:$B$7,Sheet2!$B$1:$B$7),INDEX(Sheet2!$B$1:$B$7*(COUNTIF($A$1:$A$3,Sheet2!$B$1:$B$7)=0),)),ROW(A1)))
★下にコピー

No.5 回答者： tom04 回答日時： 2009/12/25 19:06

こんばんは！

すでに色々回答が出ていますが・・・

作業列を使う方法の一例です。

↓の画像で右側がSheet2になります。
Sheet2のC・D列を作業用の列にしています。
Sheet2のC1セルに
=IF(AND(COUNTIF(Sheet1!$A$1:$A$3,B1)=0,COUNTIF($B$1:B1,B1)=1),ROW(A1),"")

D1セルに
=IF(COUNT($C$1:$C$100)<ROW(A1),"",INDEX($B$1:$B$100,SMALL($C$1:$C$100,ROW(A1))))

という数式を入れ、C1・D1セルを範囲指定し、D1セルにフィルハンドルで下へずぅ～～～！っとコピーします。

そして、Sheet1のA4セルに
=IF(COUNT(Sheet2!$D$1:$D$100)<ROW(A1),"",LARGE(Sheet2!$D$1:$D$100,ROW(A1)))
という数式を入れ、オートフィルでコピーすると
画像のような感じになります。
尚、数式は100行まで対応できるようにしてみました。

以上、参考になれば幸いですが、
他に良い方法があれば読み流してくださいね。m(__)m

No.4 回答者： KURUMITO 回答日時： 2009/12/25 17:22

シート2のＣ列は作業列としてＣ1セルには次の式を入力して下方にオートフィルドラッグします。

=IF(B1="","",IF(COUNTIF(Sheet1!A$1:A$3,B1)>0,"",IF(COUNTIF(B$1:B1,B1)=1,B1,"")))
シート1のＡ4セルには次の式を入力して下方にオートフィルドラッグします。
=IF(ISERROR(LARGE(Sheet2!C:C,ROW(A1))),"",LARGE(Sheet2!C:C,ROW(A1)))

No.3 回答者： imogasi 回答日時： 2009/12/25 14:30

例の挙げ方だか、A1-A3と３つしか上がってない。

これが現実のデータ数を反映したものか、簡略化したものか、でフィルタオプションの設定の条件の作れる可能性に影響する。
何百もあれば条件に書いていられないだろう。
その点どうか。
出来ることなら関数など使わずにエクセルの操作でやるほうが良いと思う。
関数なら作業列に一旦「A1-A3以外」か「A１－A3と一致」のコードを空き列に作ったほうが考えやすい。
ーー
フィルタ利用
　例データ
Sheet1
123
456
789
ーー
Sheet2
データ
666
123
888
789
999
456
888
789
123
666
945
124
ーーー
Sheet2のE1:G2
データデータデータ
<>123<>456<>789
ーー
A1;A13を範囲指定して
データーフィルターフィルタオプションの設定
指定した範囲
リスト範囲　A1;A13
検索条件範囲　E1:G2
抽出する範囲　I1:I13
重複するレコードは無視する
結果
I列
データ
666
888
999
945
124
あとI列でソート。
Sheet1のA4へコピペ（シート外に直接抽出することはあきらめて）
ーーー
関数では
同じデータで
Sheet２
A列　　　　B列
データ
666666
123
888888
789
999999
456
888
789
123
666
945945
124124
B2の式は
=IF(AND(COUNTIF(Sheet1!$A$1:$A$3,A2)=0,COUNTIF($B$1:B1,A2)=0),A2,"")
下方向に式を複写。
これをつめて集めるのは省略。
Googleでimogasi方式で照会すれば方法は出ている。
データ
A列　　B列　　　C列
6666661
123
8888882
789
9999993
456
888
789
123
666
9459454
1241245
のC列のように、上から連番を振る。
C２の式は　=IF(AND(COUNTIF(Sheet1!$A$1:$A$3,A2)=0,COUNTIF($B$1:B1,A2)=0),MAX($C$1:C1)+1,"")
を入れて縦方向に式を複写する。
この１、２・・をSheet１の「行数ー３」と関連づけてSheet2のA列データを引っ張ってくる。

No.2 回答者： mike_g 回答日時： 2009/12/25 13:04

Fig-1　　　 Fig-2

Sheet2　　　Sheet1
　　B　　　　　 A　　B　 C
1　data　　 1　123
2　666　　　2　456　　　TRUE
3　123　　　3　789
4　888　　　4　data
5　999
6　456
7　888
8

１．Sheet2 の上端に１行を[挿入]して、セル B1 に data と入力
２．Sheet2 のセル B1 を[コピー]して、それを Sheet1 のセル A4 に[貼り付け]
３．Sheet1 のセル C2 に式 =COUNTIF(A$1:A$3,Sheet2!B2)=0 を入力
　　（Sheet1 のセル C1 は空白のままにしておく！）
４．Sheet1 において、次の操作を実行
　４.１.[データ]→[フィルタ]→[フィルタオプションの設定]
　４.２.[抽出先]として“指定した範囲”を指定
　４.３.[リスト範囲]　 → Sheet2!$B$1:$B$7
　４.４.[検索条件範囲] → $C$1:$C$2
　４.５.[抽出範囲]　　 → $A$4
　４.６.“重複するレコードは無視する”にチェック入れ
　４.７.[OK]をクリック

その結果を下左(Fig-3)に示す。
５行目以降を降順に[並べ替え]た後で、４行目を[削除]

Fig-3
Sheet1
　　A
1　123
2　456
3　789
4　data
5　666
6　888
7　999

No.1 回答者： MackyNo1 回答日時： 2009/12/25 11:25

A4セルに以下の数式を入力します。

=MAX(INDEX(ISNA(MATCH(Sheet2!$B$1:$B$6,$A$1:$A$3,0))*Sheet2!$B$1:$B$6,))

A5セル以下には以下の数式を入力し下方向にオートフィルします。

=LARGE(INDEX((ISNA(MATCH(Sheet2!$B$1:$B$6,$A$1:$A$3,0))*Sheet2!$B$1:$B$6),),RANK(A4,Sheet2!$B$1:$B$6)+COUNTIF(Sheet2!$B$1:$B$6,Sheet1!A4))

配列数式なので、エラー処理の数式を含めるとると数式が複雑になるだけでなく計算負荷加が高くなる恐れがあるので省略していますが、もし必要なら条件付書式やIF関数で適宜エラー値を表示しない設定にしてください（現在は該当データがないとエラー表示となります）。