エクセルについて

質問

Sheet1のA列に任意の6桁の数字が300列ほどあります。

Sheet2のA列には任意の6桁の数字が150列ほどあります。

Sheet2のA列の数字はSheet1のA列に全件含まれています。

Sheet1のA列でSheet2のA列の数字が含まれていないもののみ
抜き出すかわかりやすいように色を変えるなどしたいのですが
どうしたらよいでしょうか？？

よろしくおねがいしますm(__)m

回答No.1

hallo-2007 · ベストアンサー

>Sheet1のA列でSheet2のA列の数字が含まれていないもののみ
>抜き出すかわかりやすいように色を変えるなどしたいのですが

抜き出す
Sheet1のB列に
=COUNTIF(Sheet2!A:A,A1)
入れて下までコピィすると　あるものが１以上　ないものには０が表示されますので
後で、オートフィルターで、０のみ表示させると含まれていないもののみ
色を変える
Sheet1のA列選択して、書式＝＞条件付書式のウィザードで
数式が　を選択
式には　=COUNTIF(Sheet2!A:A,A1)=0
書式のボタン　パターン　ご希望の色選択　OK
で含まれていないものに色がつきます。

補足

早速のご回答、ありがとうございます！

抜き出すのはできました。

条件付き書式でもやってみたのですが

「条件付き書式で他のワークシートまたはブックの参照は使用しません。」

とでてきてしまうのですが
どうしてでしょうか？

回答No.5

hallo-2007

No1です。
>「条件付き書式で他のワークシートまたはブックの参照は使用しません。」
>とでてきてしまうのですが
失礼致しました。条件付書式では直接は別のシートを指定する事は（Sheet2!A:A,A1のように）出来ませんでした。
忘れていました。
Sheet2のA列を選択して、ツールバーの挿入＝＞名前で　適当な名前をつけておきます。
仮に　検索値　と名前を定義した場合
証券付書式の式は　=COUNTIF(検索列,A1)=0
と入れてください。
逆に含まれているもの場合は
=COUNTIF(検索列,A1)>0　ですが、
=COUNTIF(検索列,A1)　と省略してもかまいません。

お礼

たびたびありがとうございます！感謝です！！

回答No.4

imogasi

例データA1:A７
A列　　　B列
ー 作業列
1 1
2
3 2
4 3
5
6
7 4
B列の式
=IF(ISERROR(MATCH(A2,Sheet2!$A$2:$A$100,0)),MAX($B$1:B1)+1,"")
Sheet2において、見つからない行に連番を振る。
Sheet2
A列
2
5
6
Sheet3
のA2に
=INDEX(Sheet1!$A$1:$A$100,MATCH(ROW()-1,Sheet1!$B$1:$B$100,0),1)
結果
1
3
4
7
#N/A
＃N/Aは=IF(ROW()-1>MAX(sheet1!$B$1:$B$1),"",上記式）で出ないようになる。
上記は条件抜き出しの一方法で、自称imogasi方式。

お礼

急ぎの件だったためNO.1さんので対応しました。
ありがとうございました<(_ _)>

回答No.3

xls88

>Sheet2のA列には任意の6桁の数字が150列ほどあります。
以下の手順で名前を定義します。
上記セル範囲を選択しておいて、数式バーの左端にある「名前ボックス」をクリック
例えば、検査値 と入力してEnter

>Sheet1のA列に任意の6桁の数字が300列ほどあります。
上記セル範囲を選択しておいて
条件付き書式で
数式が
条件式は、
=COUNTIF(検査値,$A1)=0
書式設定ボタン＞パターン＞希望の色を選択＞OK
のようにしてみてください。

お礼

急ぎの件だったためNO.1さんので対応しました。
ありがとうございました<(_ _)>

回答No.2

pkh4989

VBA で如何ですか。

Sub 抜き出し()
　　Dim wh1　　　　 As Worksheet
　　Dim wh2　　　　 As Worksheet
　　Dim c　　　　　 As Range
　　Dim f　　　　　 As Range
　　Dim wR　　　　　As Integer
　　'
　　Set wh1 = Worksheets("Sheet1")
　　Set wh2 = Worksheets("Sheet2")
　　wR = 0
　　With wh1
　　　　For Each c In Range("A1:A" & .Cells(Rows.Count, "A").End(xlUp).Row)
　　　　　　Set f = wh2.Range("A1:A" & wh2.Cells(Rows.Count, "A").End(xlUp).Row).Find(c.Value)
　　　　　　If f Is Nothing Then
　　　　　　　　.Cells(c.Row, "A").Interior.ColorIndex = 42　　 '←色設定
　　　　　　　　wR = wR + 1
　　　　　　　　.Cells(wR, "B") = c.Value　　　　　　　　　　　 '←B列へ展開
　　　　　　End If
　　　　Next
　　End With
End Sub

'マクロ貼付
(1) Alt+F11 (ツール → マクロ → Visual Basic Editor) →「挿入」→「標準モジュール」で表示される画面に貼り付け
(2) 実行は、(F5を押す)又は、シート画面に戻って Alt＋F8を押してマクロ一覧からマクロ名を選択して実行

お礼

急ぎの件だったためNO.1さんので対応しました。
ありがとうございました<(_ _)>