Excel 2016で〜以外の、〜を除いた、〜でないデータを抽出したい


スポンサードリンク


オートフィルターで 〜以外のデータを抽出する 詳細設定で 〜以外のデータを抽出する

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

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

  1. [データ]タブの[フィルター]を実行します。
    Noの列のフィルターボタンをクリックして、1002と1004のチェックボックスをOFFにして、[OK]ボタンをクリックします。
  2. [データ]タブの[フィルター]を実行します。
    Noの列のフィルターボタンをクリックして、[数値フィルター]→[指定の値に等しくない]を選択します。


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

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

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

    1. B1セルは空欄のままにしておきます。B2セルに =AND(A5<>1002,A5<>1004,A5<>1006) と検索条件を数式で入力します。
      • 条件の数式は =NOT(OR(A5=1002,A5=1004,A5=1006)) と書くこともできます。この数式はイコールをNOTで否定しています。
    2. 抽出先のSheet2のセルを選択します(抽出先のSheet2をアクティブにします)。
      [データ]タブの[詳細設定]を実行します。
      抽出先の指定した範囲にチェックを入れます。
      リスト範囲に Sheet1!A4:H54 、検索条件範囲に Sheet1!B1:B2 、抽出先に Sheet2!A1 と設定し、[OK]ボタンをクリックします。
    3. Sheet2に〜以外のデータが抽出できました。(1002以外、1004以外、1006以外のデータであるのが確認できます)
    4. この操作を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
  3. 条件の数式を書き換えるのが面倒・・・       Topへ

    1. 除外するNoをN2セルから下にN4セルまで入力しました。
    2. N1:N4セルを選択して、[ホーム]タブの[テーブルとして書式設定]→「オレンジ、テーブルスタイル(淡色)10」を選択して、テーブルに変換します。
    3. 「テーブルに変換するデータ範囲を指定してください」の範囲が適切であるのを確認します。
      「先頭行をテーブルの見出しとして使用する」にチェックを入れます。
      [OK]ボタンをクリックします。
    4. 名前ボックスをクリックして、「テーブル1」をクリックすると、N2:N4がテーブルになっており、ここのケースでは「テーブル1」という名前が定義されているのを確認することができます。
    5. J2セルに数式で検索条件を入力します。
      数式は =COUNTIF(テーブル1,A2)=0 とテーブル名を利用した数式にしています。
      除外するNoですので、COUNTIF関数でテーブルにNoが見つからなければよい、つまり 0 が返されればよいという考えです。
    6. 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
    7. VBAを実行すると、除外するNoが除かれたデータが抽出できているのがわかります。
    8. そこで、除外するNoを変更してみます。
      N2セル以降のデータを変更しました。
      N5セル以降はデータを入力すると、自動でテーブル1の範囲が広がっているのが確認できます。
      この自動でテーブルの範囲が変更されるのが、ここの重要なポイントです。
      VBAの変更はしないで実行します。
    9. 除外するNo(テーブル1)に入力したデータが除かれて、データが抽出できたのが確認できます。

スポンサードリンク


HomeExcel 2016の使い方の目次|Excel 2016で〜以外の、〜を除いた、〜でないデータを抽出したい