～以外の、～を除いた、～でないデータを抽出したい：Excelの基本操作

～以外の、～を除いた、～でないデータを抽出したい　　　　　　　Topへ

～以外の、～を除いた、～でないデータを抽出したい場合があります。
例えば、下図のようなデータで　Noが1002と1004以外のデータを抽出したいといったケースではオートフィルターでデータを絞り込むことが可能です。
また、いくつものアイテムを除外するには詳細設定を利用することも考えられます。

オートフィルターで～以外のデータを抽出する　　　　　　　 Topへ

[データ]タブの[フィルター]を実行します。
Noの列のフィルターボタンをクリックして、1002と1004のチェックボックスをOFFにして、[OK]ボタンをクリックします。
～以外の、～を除いた、～でないデータを抽出したい01

ちなみにこの操作をマクロの記録でコード化すると、Noの列の各アイテムに等しいとしてデータが抽出されているのがわかります。

Sub Macro1()
'
' Macro1 Macro
'
ActiveSheet.Range("$A$4:$H$54").AutoFilter Field:=1, Criteria1:=Array( _
"1001", "1003", "1005", "1007", "1008", "1009", "1010", "1011", "1012", "1013", "1014", _
"1015", "1016", "1017", "1018", "1019", "1020", "1021", "1022", "1023", "1024", "1025", _
"1026", "1027", "1028", "1029", "1030", "1031", "1032", "1033", "1034", "1035", "1036", _
"1037", "1038", "1039", "1040", "1041", "1042", "1043", "1044", "1045", "1046", "1047", _
"1048", "1049", "1050"), Operator:=xlFilterValues
End Sub

[データ]タブの[フィルター]を実行します。
Noの列のフィルターボタンをクリックして、[数値フィルター]→[指定の値に等しくない]を選択します。
～以外の、～を除いた、～でないデータを抽出したい02

↓

ちなみにこの操作をマクロの記録でコード化すると、Noの列の除きたいアイテムと等しくないとしてデータが抽出されているのがわかります。
この場合は除くアイテムはandまたはorでくくる2つの条件にする必要があります。

Sub Macro2()
'
' Macro2 Macro
'
ActiveSheet.Range("$A$4:$H$54").AutoFilter Field:=1, Criteria1:="<>1002", _
Operator:=xlAnd, Criteria2:="<>1004"
End Sub

詳細設定で～以外のデータを抽出する　　　　　　　 Topへ

[データ]タブの[詳細設定]を実行します。　　　　　　Topへ
1. B2:C3セルに下図のように条件を書き出します。
  「1002以外」という意味で「<>1002」と入力します。不等号を2個並べて、等しくない、という意味になります。「等しくない」は、つまり「以外」ということです。
  「1004以外」とand条件になりますので、「<>1004」は「<>1002」と同じ行に書きます。
  フィルターオプションの設定(詳細設定)はリスト範囲と検索条件範囲を下図のように設定します。
2. Noが1002と1004以外のデータが抽出されました。

条件を数式で書き、詳細設定で別シートに書き出す。　　　　　　Topへ

条件を並べて書き出すのは見た目がちょっと・・・といったケースや、VBAで条件範囲を設定していると、条件を書き換えるたびにコードも書き換えないといけないといった面倒なケースが発生したりします。
そういったときには、検索条件を数式で設定してやればよいと考えます。
例えば、Noが1002、1004、1006以外のセルを別シートに抽出してみます。

B1セルは空欄のままにしておきます。B2セルに　=AND(A5<>1002,A5<>1004,A5<>1006)　と検索条件を数式で入力します。
- 条件の数式は　=NOT(OR(A5=1002,A5=1004,A5=1006))　と書くこともできます。この数式はイコールをNOTで否定しています。
抽出先のSheet2のセルを選択します(抽出先のSheet2をアクティブにします)。
[データ]タブの[詳細設定]を実行します。
抽出先の指定した範囲にチェックを入れます。
リスト範囲に Sheet1!A4:H54　、検索条件範囲に Sheet1!B1：B2　、抽出先に Sheet2!A1　と設定し、[OK]ボタンをクリックします。
Sheet2に～以外のデータが抽出できました。（1002以外、1004以外、1006以外のデータであるのが確認できます）

この操作をVBAのコードにすると、下表のようになります。
コードでは各シートを指定していますので、Shee1から実行しても、Sheet2から実行しても動きます。

Sub Adfilter2()
Worksheets("Sheet2").Range("A:H").Clear

Worksheets("Sheet1").Range("A4:H54").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Worksheets("Sheet1").Range("B1:B2"), _
CopyToRange:=Worksheets("Sheet2").Range("A1"), _
Unique:=False
End Sub

条件の数式を書き換えるのが面倒・・・　　　　　　　Topへ

上のようなケースで、除外したいNoがその都度変化する場合は数式を変更する必要があります。
数式を変更しないで、除外するNoを書き出しだけでマクロを実行すれば必要なデータが抽出できる・・・としたら便利だと思いませんか？
除外するNoを列記して、そのデータ範囲をテーブルにします。テーブルの名前を使って数式を作成すればよいはずです。
では、以降で試してみましょう

除外するNoをN2セルから下にN4セルまで入力しました。
N1:N4セルを選択して、[ホーム]タブの[テーブルとして書式設定]→「オレンジ、テーブルスタイル(淡色)10」を選択して、テーブルに変換します。
「テーブルに変換するデータ範囲を指定してください」の範囲が適切であるのを確認します。
「先頭行をテーブルの見出しとして使用する」にチェックを入れます。
[OK]ボタンをクリックします。
名前ボックスをクリックして、「テーブル1」をクリックすると、N2:N4がテーブルになっており、ここのケースでは「テーブル1」という名前が定義されているのを確認することができます。
J2セルに数式で検索条件を入力します。
数式は　=COUNTIF(テーブル1,A2)=0　とテーブル名を利用した数式にしています。
除外するNoですので、COUNTIF関数でテーブルにNoが見つからなければよい、つまり　0　が返されればよいという考えです。

VBAのコードは下表のようにしています。
データ範囲は　Sheet1のA1:H51
検索条件は　Sheet1のJ1:J2
データの抽出先は Sheet2のA1
としています。

Sub Adfilter()
　　Worksheets("Sheet2").Range("A:H").Clear
　　Worksheets("Sheet1").Range("A1:H51").AdvancedFilter _
　　　　Action:=xlFilterCopy, _
　　　　CriteriaRange:=Worksheets("Sheet1").Range("J1:J2"), _
　　　　CopyToRange:=Worksheets("Sheet2").Range("A1"), _
　　　　Unique:=False
End Sub

VBAを実行すると、除外するNoが除かれたデータが抽出できているのがわかります。
そこで、除外するNoを変更してみます。
N2セル以降のデータを変更しました。
N5セル以降はデータを入力すると、自動でテーブル1の範囲が広がっているのが確認できます。
この自動でテーブルの範囲が変更されるのが、ここの重要なポイントです。
VBAの変更はしないで実行します。
除外するNo(テーブル1)に入力したデータが除かれて、データが抽出できたのが確認できます。