エクセルで一致しないものを抜き出す方法

質問

またエクセル関数の質問があります。
セルA1:A6にA～Fがランダムに入力されています。
セルB1:B6にA～Fの一部がランダムに入力されています。空白もあります。
別なセルに一致しない記号を表示させたいのですが....
例
　 A B C D
1 F C B
2 D 　 D
3 B A
4 A F
5 E
6 C E

別なセル（上記ではC1、C2）に表示させられますか？
A列とB列の記号は変化します。
　よろしくお願いします。

回答No.7

sakenomo · ベストアンサー

＃２です。

>　ShiftキーとCtrlキーで｛｝はどのような意味なのでしょうか？

この操作で、数式バーに入力した数式が、配列数式になります。配列数式については、ヘルプやＨＰを検索するといろいろありますので、そちらを参考にしてください。

＃５さんがすばらしい回答をだされていて、いまさら恥ずかしいのですが、#NUM を表示しないようにする、ちょっとセコい方法を思いついたので。

＃２の数式の内、２ケ所の６を７に変えます。これだけでは#NUMが０になるだけですが、セルＡ７にスペースを入力すれば、#NUMが消えます（実際にはスペースが入ります）。

=INDEX($A$1:$A$7,SMALL(IF(COUNTIF($B$1:$B$6,$A$1:$A$6)=0,ROW($A$1:$A$6),7),ROW()),0)

お礼

配列数式というんですね。初めて知ったので調べてみました。なんとなく意味が分かったような気がします。
　実は、私の作ろうとしているものは一致しないものの数が決まっているので、例で言えば　c1とc2で大丈夫だったんです。でも有難う御座いました。もし、決まっているのが分かっていたら、もっと短い式になったのかな？
　式をまだ解読出来ないでいますが、コピ－して範囲設定だけをかえたら出来ました。有難う御座います。

回答No.9

inte-nori

ちょっと長くなってしまいますが、#4のコードの説明を書いてみます。
内容がわかれば改造などもやりやすいと思うので。

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)　…[1]
　　If Target.Column <= 2 Then　…[2]
　　　　a = 1　…[3]
　　　　Range("C1:C6").ClearContents　…[4]
　　　　For i = 65 To 70　…[5]
　　　　　　If (Range("B1:B6").Find(Chr(i)) Is Nothing) = _
　　　　　　　Not (Range("A1:A6").Find(Chr(i)) Is Nothing) Then　…[6]
　　　　　　　　　　Cells(a, 3).Value = Chr(i)　…[7]
　　　　　　　　　　a = a + 1　…[8]
　　　　　　End If　…[9]
　　　　Next　…[10]
　　End If　…[11]
End Sub　…[12]

[1]…開いているブックの中のシートが変更されたら、下記の命令を実行します。同時に、変更されたシートを「Sh」（今回は使用していません）、変更されたセルを「Target」とします。

[2]…もし、Targetの列番号が２以下だったら（データが入力されたのがA列かB列だったら）[11]との間に挟まれた命令を実行します。

[3]…「a」という変数に1を入れます。

[4]…C1:C6の範囲の値をクリアします。

[5]…「i」という変数に65を入れ、70になるまで[10]との間を繰り返します。（1回繰り返すごとに「i」の値が1ずつ増えます）

[6]…もしB1:B6の中の「A」の有無と、A1:A6の中の「A」の有無が一致していなかったら、[9]との間に挟まれた命令（[7]と[8]）を実行します。
※　Chr(i)は「コード番号が「i」の文字」を表します。一回目の[5]～[10]のループの中では「i」は65なので、コード番号が65である「A」を表します。二回目のループではコード番号が66である「B」を表します。

[7]…上からa番目、左から3番目のセルに「A（一回目のループの場合）」を入力します。

[8]…次の文字を2行目に表示するようにするため、「a」に1を足します。

[9]、[10]、[11]…それぞれ、[6]、[5]、[2]の命令の終了部分を表します。

[12]…すべての命令の終了を表します。

つまり、[5]～[10]の繰り返しの中で、「A」（コード65）から「F」（コード70）までを一文字ずつ判定しているわけです。わかりにくくてすみません。

お礼

詳しい説明有難う御座います。まだこの式を解読するまでに至っていませんが、これだけ詳しく書いてあれば私でも理解できるような気がします。がんばります。

回答No.8

sakenomo

＃７です。すみません、訂正です。

> ＃２の数式の内、２ケ所の６を７に変えます。

１ヶ所の６と""を７に、でした。

回答No.6

inte-nori

#4の補足です。

#4のコードでは、ブック内のすべてのシートに対して有効となってしまうので、
特定のシートのみに使用する場合には「２．」の手順において「ThisWorkbook」ではなく
その上の「シート名」で右クリック＞「コードの表示」として、コードの一行目を

Private Sub Worksheet_Change(ByVal Target As Range)

に変えてください。

回答No.5

ja7awu

余分な部分に #NUM を表示しないようにする場合です。

式を入力後、Ctrl+Shift+Enter で確定します。

=IF(COUNTA(A$1:A$6)-SUM(COUNTIF(A$1:A$6,B$1:B$6))<ROW(),"",INDEX(A$1:A$6,SMALL(IF(COUNTIF(B$1:B$6,A$1:A$6)=0,ROW(A$1:A$6),""),ROW()),0))

お礼

すごいです。有難う御座います。

回答No.4

inte-nori

VBAなんですが・・

１．「ツール」＞「マクロ」＞「Visual Basic Editor」を開きます。
２．左上のほうの「ThisWorkbook」というところで右クリックをして、「コードの表示」を選択します。
３．右側の大きな画面に下記のコードを入力します。（コピー＆貼り付けでＯＫ）

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
　　If Target.Column <= 2 Then
　　　　a = 1
　　　　Range("C1:C6").ClearContents
　　　　For i = 65 To 70
　　　　　　If (Range("B1:B6").Find(Chr(i)) Is Nothing) = _
　　　　　　　Not (Range("A1:A6").Find(Chr(i)) Is Nothing) Then
　　　　　　　　　　Cells(a, 3).Value = Chr(i)
　　　　　　　　　　a = a + 1
　　　　　　End If
　　　　Next
　　End If
End Sub

４．下のタスクバーのボタンでエクセルの画面を表示してください。

ちなみに、このコードでは「Ａ列にあってＢ列にないもの」と「Ｂ列にあってＡ列にないもの」は同じ扱いとなります。

お礼

ありがとう御座います。出来ました。
　VBAが得意なんですね！
　理解できるか不安ですが、今度わたしも勉強してみます。

回答No.3

imogasi

（データ）Ａ１：Ｂ７に
f c
d
b a
a f
e
c e
g
（関数式）
Ｃ１セルに=IF(ISERROR(MATCH(A1,$B$1:$B$10,0)),A1,"")
Ｄ１セルに=IF(C1="","",ROW()-COUNTBLANK($C$1:C1))
Ｅ１セルに=OFFSET($C$1,MATCH(ROW(E1),$D$1:$D$10)-1,0)
それぞれ下の行へ複写してください。
$d$10の10に意味はありません、行が増えることを考えた
だけで、最下行以上ならいくらでもよい。
（結果）Ｅ１：Ｅ３に
d
b
g
になります。
実はＥ列は３行目で複写を止めないとおかしくなります。もう少し工夫が必要なのでしょうが、複雑になるのでここで止めます。
それと余分な列を使うのがキズ。

お礼

ありがとう御座います。

回答No.2

sakenomo

下の式をＣ１にコピーして、数式バーにカーソルがあるうちにShiftキーとCtrlキーを同時に押しながら、Enterキーを押します。その後Ｃ６までオートフィル。

該当する文字が無い場合は、#NUM!というのがでてしまいますが、いかがでしょうか。

=INDEX($A$1:$A$6,SMALL(IF(COUNTIF($B$1:$B$6,$A$1:$A$6)=0,ROW($A$1:$A$6),""),ROW()),0)

お礼

出来ました。ありがとう御座います。
私の作っているエクセルに応用できるかTEST中です。今、内容を理解しようとしているのですが、ShiftキーとCtrlキーで｛｝はどのような意味なのでしょうか？

回答No.1

papayuka

C1とC2にと言うと思い浮かびませんが、、

C1に下記のように入れてC6までコピーすると、C2とC3にＤとＢが出ます。

=IF(ISNA(MATCH(A1,$B$1:$B$6,0)),A1,"")

A列のアルファベットが重複している（A1～A6が全てＢとか）場合は重複分だけ出てしまいますが、、、

お礼

表示させるセルが決まっていますが、簡単な式でこんなこと出来るなんて、びっくりです。
　やっぱり考え方（やり方）でいくらでも簡単にできるのですね。
　ありがとう御座います。