Excel業務カイゼンブログ

Excelには便利なフィルター機能が備わっています。手軽にデータの絞り込みができる機能なのですが、Excelにはこの機能とは別に「フィルターオプション」というフィルター機能があります。

考えてみると、「フィルター」機能があれば事足りるかもしれません。なぜ他に「フィルターオプション」機能があるのでしょうか。

今回は「フィルターオプション」を解説しながら、フィルター機能との違いを紹介します。

実際に操作するファイルはこちらからダウンロードできます。

フィルターオプションに必要な範囲

フィルターオプションには「リスト範囲」と「条件範囲」の２つのセクションが必要で、さらに「抽出範囲」というセクションも指定する場合があります。

リスト範囲

１行目に項目名が入っている元データの一覧表のことです。

項目名は同じものが２つあってはいけません。

また、空白列があってもいけません。

検索条件範囲

フィルターオプション特有の範囲です。

検索の条件が、１行目に項目名が入っていて、２行目以降に検索の条件が入っています。

項目名はデータベースの項目名のどれかを選びます。同じ項目がいくつあってもよいです。

検索条件は、次のように設定します。

• そのものを検索する場合は、「=」に続けてその内容
  「りんご」を選択する場合は「=りんご」
• そうではないものを検索する場合は、「<>」に続けてその内容
  「りんご」以外を選択する場合は「<>りんご」
• 以上を表す場合は「>=」に続けてその数値
  100以上を選択する場合は「>=100」
• 以下を表す場合は「<=」に続けてその数値
  500以下を選択する場合は「<=500」
• その数値を含めない、より大きいを表す場合は「>」に続けてその数値
  50％より大きいを選択する場合は「>50%」
• その数値を含めない、より小さい（未満）を表す場合は「<」に続けてその数値
  0.25未満を選択する場合は「<0.25」

日時を比較する場合は、""で日付データを囲み、">=2010/10/15"、"<11:45"、">2015/3/1"のような形式で指定します。

空白セルを指定する場合は「=」だけ入力し、空白以外は「<>」と指定します。

検索条件にはワイルドカードが使えるので、「*アメリカ*」とすれば、「アメリカ」を含む文字列を検索し、「*県」であれば「県」で終了する文字列を検索します。

また、検索条件は計算式でも指定でき、セルA1に「100」と入力されていた場合、「=">="&A1」という計算式で指定することで、A1の値以上という条件にすることができます。

横に並んでいる条件は「なおかつ（AND）」検索、縦に並んでいる条件は「または（OR）」検索になります。

この検索条件は、商品名がバターで価格が300円以上か、マーガリンで在庫が20より少ないデータを抽出する条件になります。

このような複雑な指定は「フィルター」機能ではできません。

検索条件はデータベース同様、空白列があってはいけません。

抽出範囲

どの項目を抽出したいか、データベースの項目名から選び出して列方向に並べたもので、それぞれの項目の該当したデータがその下に抽出される範囲です。

必ず１行で、空白列があってはいけません。

指定しなくても構いませんが、指定しない場合は、抽出先に抽出されず、データベースで指定した範囲の検索対象外の行が非表示になります。

抽出範囲をデータベースや検索範囲の上に設定すると、結果を上書きしてしまうので、別の列かそれらの下に設定することに注意しましょう。

フィルターオプションの使い方

まず、フィルターオプションを使うには上記の「データベース」「検索条件範囲」、必要であれば「抽出範囲」が必要となります。

はじめは、「データベース」「検索条件範囲」のみを作って操作してみます。

ダウンロードしたファイルをご覧ください。

架空の北極の気温のデータがデータベースとして記載されています。４行目が項目名になっていて、各項目名で同じものはなく、また空白列もないので、正しいデータベースの形になっています。

抽出範囲を設定しない「天気が晴」のデータを取り出すフィルターオプションを実践します。

条件範囲をデータベース範囲の上に作成するので、４行目から８行目に行を挿入します。

セルC9をセルA４に貼り付けます。

ここから条件を入力していきます。

「晴」のものを指定するので、「’（シングルクォーテーション）」に続けて、「=晴」と入力します。

本来は、「=晴」とだけ入力したいのですが、「=」で始まるため数式として認識されてしまうので、文字列として入力するために「’」をはじめに入力します。

セルA5に「'=晴」と入力します。

これで「天気が晴」のデータを取り出すという条件となります。

ここまででフィルターオプションの準備が終わりました。

［データ］タブの［並べ替えとフィルター］グループの中の［詳細設定］をクリックします。

機能名は「フィルターオプション」なのですが、リボン上に「フィルターオプション」とは書いていませんので、リボン上では「詳細設定」というボタンを覚えておきましょう。

「フィルターオプション」のダイアログボックスが表示されます。

今回は、抽出範囲は設定しませんので、［抽出先］は［選択範囲内］です。

このデータベースのリスト範囲は、A9からE130の範囲です。

［詳細設定］をクリックする前に、この範囲の中のセルを１つ選択しておくことで自動的に範囲がセットされますが、データベース内に空白行がある場合は正しく選択されないこともあるので、設定された範囲が正しいかは、きちんと確認しましょう。

検索条件範囲は、項目名とその条件ですので、今回はA4からA5の範囲です。

それぞれの範囲にシート名が入る場合もあります。その場合、シート名が入ったままでもかまいません。

では、OKボタンをクリックしてみましょう。

「晴」のデータだけが表示され、それ以外の行は非表示になりました。

抽出されると、行番号が青文字になります。

今度は、「天気が晴でなおかつ気温が-2度以上」を抽出してみましょう。

検索条件を同じ行に入力すれば「なおかつ」の条件になります。

セルB9をセルB４に貼り付け、セルB5に「>=-2」と入力します。

これで条件が「天気が晴」でなおかつ「気温が-2度以上」となります。

［詳細設定］をクリックし、データベース範囲は先ほどと同じA9からE130で、検索条件範囲はA4からB5の範囲で抽出してみましょう。

天気が晴で平均気温が-2度以上のデータが抽出されました。

では、「天気が晴で-2～-1度」はどのような抽出をすればよいのでしょうか。

この場合、検索条件が「天気が晴」なおかつ「-2度以上」なおかつ「-1度以下」という3条件のAND検索になります。

つまり、気温の条件がB列の他にC列にも必要で、その条件が１行に入っていなければなりません。

検索条件の項目は、同じ項目をいくつも指定できます。

セルB4をセルC4にコピーし、セルC5に「<=-1」と入力します。

［詳細設定］をクリックし、データベース範囲は先ほどと同じA9からE130で、検索条件範囲はA4からC5の範囲で抽出してみましょう。

「天気が晴で-2～-1度」のデータを抽出できました。

次は、「天気に晴」の他に「晴が含まれる」データも絞り込みましょう。

この場合、「天気が晴」は、「晴が含まれる」の中に含まれるので、セルA4の「晴」の条件を、「晴が含まれる」という条件に変更します。

「なんとか晴なんとか」という条件にするので、「*晴*」という条件になります。

「*」は「なにかの文字列」を示し、ワイルドカードと呼ばれます。

「何かの文字列 晴 何かの文字列」、つまり「晴」が含まれるという条件になるのです。

ワイルドカードを使う時は「=」は不要です。

セルC９をセルC4にコピーし、セルC5に「*晴*」と入力しましょう。

［詳細設定］をクリックし、データベース範囲は先ほどと同じA9からE130で、検索条件範囲は先ほどと同じA4からC5の範囲で抽出してみましょう。

では、OKボタンをクリックしてみましょう。

「-2度以上」なおかつ「-1度以下」なおかつ「天気に晴が含まれる」データが表示されました。

今度は、上記の条件以外にも、「天気に雪が含まれている」場合は抽出対象とします

検索条件が「天気に晴が含まれる」なおかつ「-2度以上」なおかつ「-1度以下」という３条件のAND検索の他に、それか（または）、「天気に雪が含まれている」場合というOR条件も含まれます。

OR検索の場合は、行を分ける必要があります。

今回は、上記の５行目に設定した条件の他に、「天気に雪が含まれている」という条件が別の行に分けて入力されている必要があります。

セルC6に「*雪*」と入力すれば、天気が「何かの文字列 雪 何かの文字列」という条件になります。

［詳細設定］をクリックし、データベース範囲は先ほどと同じA9からE130で、検索条件範囲はA4からC6の範囲で抽出してみましょう。

では、OKボタンをクリックしてみましょう。

「-2度から-1度で」なおかつ「天気に晴が含まれる」データか、「天気に雪が含まれている」データが表示されました。

最後に、「-2度以上」なおかつ「-1度以下」のデータの中で「天気に晴か雪が含まれる」場合の例を紹介します。

この条件をExcelの検索条件として考える場合は、「-2度以上」なおかつ「-1度以下」なおかつ「天気に晴が含まれる」か（または）、「-2度以上」なおかつ「-1度以下」なおかつ「天気に雪が含まれる」という全部で６つの条件が入ります。

「または」の条件両方に同じ「なおかつ」が入るのがポイントです。

今の状態からであれば、B5からC5の範囲をB６からC６の範囲にコピーすればこの条件になります。

［詳細設定］をクリックし、データベース範囲は先ほどと同じA9からE130で、検索条件範囲も先ほどと同じA4からC6の範囲で抽出してみましょう。

では、OKボタンをクリックしてみましょう。

「-2度から-1度で」なおかつ「天気に晴が含まれる」か（または）、「-2度から-1度で」なおかつ「天気に雪が含まれる」データが表示されました。

このように、「なおかつ」「または」を組み合わせることにより、より複雑な抽出も可能になります。

フィルターオプションの解除

絞り込まれたデータを元に戻してすべてのデータを再表示するには、［データ］タブの［並べ替えとフィルター］グループの中の［クリア］をクリックします。

絞り込まれたデータが解除されました。

抽出範囲への抽出

該当するデータは、抽出範囲へ別途リスト出力することもできます。

抽出範囲には、抽出項目を記載します。

今回は、該当データの「年月日」と「日照時間」の２項目をセルH9から始まる範囲に出力します。

セルA9をセルH9へ、セルE9をセルI9へコピーします。

［詳細設定］をクリックし、データベース範囲は先ほどと同じA9からE130で、検索条件範囲も先ほどと同じA4からC6の範囲です。

抽出先を「指定した範囲」とすると、抽出範囲が入力できるので、H9からI9を選択します。

では、OKボタンをクリックしてみましょう。

セルH9からの範囲に該当データの「年月日」と「日照時間」のデータをリストできました。

H10からI17に抽出されたデータをいったん削除しましょう。

行数指定の抽出範囲

抽出範囲を１行だけではなく、タイトル行と併せて複数行選択した場合、該当したデータを指定した範囲の分だけ抽出します。

タイトル行を含めて全6行を抽出範囲として指定した場合、5データだけ抽出します。

［詳細設定］をクリックし、データベース範囲は先ほどと同じA9からE130で、検索条件範囲も先ほどと同じA4からC6の範囲で、抽出先を「指定した範囲」、抽出範囲は、タイトル行の他に5データを指定するのでH9からI14を選択します。

抽出後は抽出範囲が小さすぎる旨のメッセージが表示されます。

「はい」なら続きのデータを表示し、「いいえ」なら表示しません。今回は「いいえ」にしましょう。

タイトル行の他に、5データが抽出されました。

抽出範囲を１つの空白セルのみ選択した場合

抽出範囲を空白の１つのセルを指定した場合は、データベース範囲の項目をそのままで検索結果を抽出します。

［詳細設定］をクリックし、データベース範囲は先ほどと同じA9からE130で、検索条件範囲も先ほどと同じA4からC6の範囲で、抽出先を「指定した範囲」、抽出範囲は、空白セル１つを選択するのでK9を選択します。

セルK9からの範囲にデータベース範囲の項目をそのままで検索結果が抽出されました。

重複データ

元のデータベースに全く同じデータが２行以上あった場合、「重複するレコードは無視する」のチェックを入れることで、はじめに検索された１種類のみを抽出できます。

名前範囲の自動設定

フィルターオプションを設定すると、指定した範囲に名前が付く場合があります。

検索条件範囲には「Criteria」、抽出範囲には「Extract」になり、名前ボックスをクリックすると、その設定を見ることができます。

また、「フィルターオプション」のダイアログボックスで2回目以降に指定した時に、セル範囲ではなく、この名前が入力される場合があります。

もし違うセル範囲を指定したい場合は、指定しなおせば問題ありません。

まとめ

フィルターオプション機能は、フィルター機能に対して手順も多く、面倒に感じるかもしれません。しかしこの課題も、Excelのマクロで手順を記録すればワンボタンで結果を出すことができるようになります。

フィルター機能では、より複雑なOR検索による絞り込みができず、ユーザーがいろいろな方法を使わなければなりませんが、フィルターオプションではどんな複雑な条件でも絞り込む設定ができます。

今回紹介した検索条件範囲は、集計に便利なデータベース関数を使う時にも必要な考え方です。