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

スポンサードリンク

更新:2024/9/7;作成:2016/12/20

FILTER関数で～以外のデータを抽出する
オートフィルターで～以外のデータを抽出する	詳細設定で～以外のデータを抽出する
条件にテーブルを利用する方法

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

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

Filter関数で～以外のデータを抽出する　　　　　 Topへ

Excel for Microsoft 365 (サブスクリプション型)、Excel2021で使用できるFILTER関数でデータを抽出してみます。
詳細設定と似ていますが、条件の書き方が若干異なります。
下図では、No.1002,1004,1006の３つ以外のデータを抽出しています。
C2セルに
=FILTER(Sheet1!A2:H51,
(Sheet1!A2:A51<>1002)*(Sheet1!A2:A51<>1004)*(Sheet1!A2:A51<>1006))
と、入力します。

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

データ内のセルを１つ選択します。
[データ]タブの並べ替えとフィルターグループの[フィルター]を実行します。

Noの列のフィルターボタンをクリックして、1002と1004と1006のチェックボックスをOFFにして、[OK]ボタンをクリックします。

ちなみにこの操作をマクロの記録でコード化すると、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

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

[データ]タブの[詳細設定]を実行します。　　　　　　Topへ

B2:D3セルに下図のように条件を書き出します。
「1002以外」という意味で「<>1002」と入力します。不等号を2個並べて、等しくない、という意味になります。「等しくない」は、つまり「以外」ということです。
「1004以外」「1006以外」とand条件になりますので、「<>1004」「<>1006」は「<>1002」と同じ行に書きます。
データ内のセルを１つ選択します。
[データ]タブの並べ替えとフィルターグループの[詳細設定]を実行します。
選択範囲内を選択します。
リスト範囲は　A4:H54
検索条件範囲は　B1:D2
として、[OK]ボタンをクリックします。
Noが1002と1004、1006以外のデータが抽出されました。

条件を数式で書き、詳細設定で別シートに書き出す。　　　　　　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)に入力したデータが除かれて、データが抽出できたのが確認できます。