エクセルで２列の順序が違うデータの照合方法

質問

エクセル初心者なので教えてください。

Ａ列、Ｂ列ともに５００行ほどの数値があります。
Ａ、Ｂ列の数値の順序は違います。
Ａ列の数値に対し、Ｂ列の数値で一致しないものだけを
別の列（たとえばＣ列）に一度に自動に表示するようにするには
どうしたら良いのでしょうか？　
簡単な方法があれば、教えてください。

回答No.7

imogasi · ベストアンサー

自称imogasi方式というものを紹介します。
例データ　A2:C８　C列は関数の結果。作業列です。C列第１行は数値データなし、にしてください。
A列　　　B列　　　C列（作業列）
1 3
4 2 1
2 1
3 8
6 12
9 6 2
12 5
(準備）
C２に=IF(COUNTIF($B$2:$B$21,A2)=0,MAX($C$1:C1)+1,"")
と入れて、A列で最下行まで式を複写。
結果
上記C列。
意味は、A列の数値がB列に見つからない場合、上から
連番を振っている。
ーーー
（抜き出し）
C列１，２・・をF列に詰めて！抜き出します。
F2に=INDEX($A$1:$A$20,MATCH(ROW()-1,$C$1:$C$21,0),1)
と入れて、B列のMAX数の行数（上例では２）だけ式を複写します。
結果
F2:F3
4
9
ーーー
B-->A列に対しても同様のことを行う。
すると、B列にありA列に無い数が抜き出せる。
両者あわせて質問の答えになります。
ーー
上記の方法の欠点は
作業列を使う。この質問の特殊性から２列必要。
A->BとB->Aの２系列に分かれて出てしまう。
ということはありますが、理屈は比較的単純なやり方です。
ーー
Googleなどで「imogasi方式」で照会すると、沢山過去のこのコーナーの「抜き出し」問題の質問が出ます。その中には作業列を使わない
関数での回答もある質問もあります。（相当難しいが）興味があれば見てください。
＝＝＝
VBAなどで色々なロジックで出来ますが略。

お礼

回答ありがとうございました。
参考になりました。

回答No.6

jo-zen

ANo.3&4のjo-zenです。検証不足だったようで、若干修正させていただきます。

手順（２）で
　Ｃ２セルに　=IF(COUNT($D$1:D2)>MAX($C$1:C1),MAX($C$1:C1)+1,0)　Ｃ３セル以降にコピー

手順（４）で
　Ｆ１セルに　　=IF(MAX(C:C)>=ROW(),VLOOKUP(ROW(),$C$1:$D$500,2,0),"")
　Ｆ２セル以降にコピー

で大丈夫かと思います。やってみてください。

お礼

回答ありがとうございました。
参考になりました。

回答No.5

mshr1962

数式でなくても良いなら
まずA,B列の先頭行に同じ項目名を付けてください。
「データ」「フィルタ」「フィルタオプションの設定」にて
抽出先：「指定した範囲」を選択
リスト範囲：A列を選択
検索条件範囲：B列の全データを選択
抽出範囲：C1を選択
これでOKを押してください。
※A列に同じ値があり、それらを抽出しない場合は、
「重複するレコードは無視する」にチェックしてください。

お礼

回答ありがとうございました。
参考になりました。

回答No.4

jo-zen

ANo.3のjo-zenです。補足します。

＞欲を言えば、Ｃ列に出力された数値を上から詰めて表示するような
ことが出来ないでしょうか？

⇒すっきりした関数で実現することは無理だと思います。VBAを使って処理する方法もとれなくはありませんが、VBAについての知識が必要になりますので、経験者以外は手を出さない方がいいかと思います。もちろん、ご自身で独学なりされて、できるようになればいいのですが。

関数のみを使う方法だと、作業セルを使うやり方になります。

＃３からの流れでいけば、Ｃ列の前に１列挿入（上にあるＣ列のラベルをクリックし、Ｃ列全体を選択した状態で、右クリックから「挿入」を選べばＯＫです）します。すると今までのＣ列はＤ列になりますが、数式は自動的に整合されます。

Ｃ１セルに　=COUNT($D$1:D1)
Ｃ２セルに　=IF(COUNT($D$1:D2)>C1,MAX($C$1:C1)+1,0)
Ｃ３セル以降はＣ２セルをコピー

してください。すると、Ｄ列に数字が表示されていなければ０（ゼロ）、表示されていれば、それが上から何番目かが表示されると思います。

あとは、例えば　F列に上から詰めて結果を表示させるために、

F1セルに　=VLOOKUP(ROW(),$C$1:$D$500,2,TRUE)
F2セル以降は、F1セルをコピー

で大丈夫かと思います。

お礼

回答ありがとうございました。
参考になりました。

補足

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

F列に上から詰めて結果を表示させようとしたのですがうまくできませんでした。　もう一度確認させてください。
手順として
(1)Ｃ列の前に１列挿入
(2)Ｃ１セルに　=COUNT($D$1:D1)
　Ｃ２セルに　=IF(COUNT($D$1:D2)>C1,MAX($C$1:C1)+1,0)
　Ｃ３セル以降はＣ２セルをコピー
(3)Ｃ列に０と＊番目数字が表示
(4)F1セルに　=VLOOKUP(ROW(),$C$1:$D$500,2,TRUE)
　F2セル以降は、F1セルをコピー

結果→F列には０しか表示されませんでした。
間違った手順があれば教えてください、お願いします。

回答No.3

jo-zen

Ｃ列に上から連続で表示するということでなければ、次のような数式でいいかと思います。Ａ列の数値をＢ列の中で検索して該当しない場合にＣ列に表示するやり方です。１～５００行に数値が入っているとして、

　　C1　　=IF(ISERROR(MATCH(A1,$B$1:$B$500,0))=TRUE,A1,"")
　　以下コピー

Ｂ列の数値をＡ列の中で検索して該当しない場合には対応しませんので、Ｄ列などに、

　　D1　　=IF(ISERROR(MATCH(B1,$A$1:$A$500,0))=TRUE,B1,"")
　　以下コピー

と入れればいいかと思います。

順序が異なるので、難しいところがあります。Ｃ列だけで両方を上から詰めて表示するような関数を考えたかったのですが、・・・。

お礼

回答ありがとうございました。
参考になりました。

補足

jo-zenさん
早速試してみました、ありがとうございました。

欲を言えば、Ｃ列に出力された数値を上から詰めて表示するような
ことが出来ないでしょうか？

回答No.2

mu2011

オートフィルタを使用する方法ですが如何でしょうか。
（１)見出し行がない場合、任意項目名で行挿入
（２）Ｃ列のC2に=COUNTIF(A:A,B2)=0を入力、Ａ・Ｂ列の多い行数分まで下方向にコピー
（３）任意データセルを選択→データ→フィルタ→オートフィルタ→Ｃ列のフィルタボタンでTRUEを選択

お礼

回答ありがとうございました。
参考になりました。

回答No.1

hallo-2007

A1セルと同じ値がB列にない場合に　C1にはA1の値
=IF(COUNTIF(B:B,A1),"",A1)
という形になります。
下フィルしてみてください。

お礼

回答ありがとうございました。
参考になりました。