Excelのフィルター機能を使いこなそう!基本から応用まで徹底解説

前回、Excelの集計関数を使って大量のデータを簡単にまとめる方法をご紹介しましたが、あの記事ではワークシート上のすべてのデータが対象でした。では、データの一部だけを抽出し、その抽出したデータだけを集計したい場合はどうすればよいのでしょうか?

Excelでは、列にフィルターを設定することで、条件に一致しない行を自動的に非表示にできます。さらに、特殊な関数を使えば、フィルターで表示中のデータだけを対象に集計することも可能です。

本記事では、Excelでフィルターを作成する手順と、組み込み関数を使ってフィルター後のデータを集計する方法を、順を追って詳しく解説していきます。

Excelでシンプルなフィルターを作成する

Excelのフィルターには、単純なフィルターと複雑なフィルターの2種類があります。まずは基本となるシンプルなフィルターから始めましょう。

フィルターを活用する際のコツとして、表の最上行を見出し(ラベル)行として用意しておくことをおすすめします。必須ではありませんが、フィルター操作が格段にやりやすくなります。

フィルターの設定方法

以下のようなサンプルデータがあるとし、「City(都市)」列にフィルターをかけたいとします。手順はとても簡単です。リボンの「データ」タブを開き、「フィルター」ボタンをクリックするだけです。事前にデータ範囲を選択したり、先頭行をクリックしたりする必要はありません。

「フィルター」をクリックすると、最上行の各列の右端に小さなドロップダウンボタンが自動的に追加されます。

並べ替えと絞り込み

次に、City列のドロップダウン矢印をクリックしてみましょう。いくつかのオプションが表示されます。

ダイアログの上部では、City列の値を基準に行全体を素早く並べ替えられます。ここで注意したいのは、並べ替えを行うとCity列の値だけでなく行全体が移動するという点です。おかげで、データの整合性は崩れません。

また、表の先頭に「ID」列を追加し、1から連番を振っておくのも有効なテクニックです。元の順序に戻したくなったときは、ID列で並べ替えるだけで簡単に復元できます。

これで、表全体がCity列の値に基づいて並べ替えられました。現時点ではまだ行は非表示になっていません。続いて、フィルターダイアログ下部のチェックボックスを見てみましょう。この例では、City列には3つの一意な値しかなく、その3つがリストに表示されています。

ここで2つの都市のチェックを外し、1つだけ残してみます。すると、表示されるのは8行のデータだけで、残りの行は非表示になりました。左端の行番号を見ると、非表示の行数に応じて横線が飛び飛びになり、行番号の色が青色に変わっていることがわかります。これがフィルターがかかった状態の目印です。

数値フィルターで条件を追加する

さらに別の列でもフィルターをかけて、結果を絞り込んでみましょう。C列には各家族の人数が入力されており、「2人より多い家族」だけを表示したいとします。

C列のドロップダウン矢印をクリックすると、同じようにチェックボックスの一覧が表示されます。しかし今回は、「数値フィルター」→「指定の値より大きい」を選択します。このメニューには、他にもさまざまな条件オプションが用意されています。

新しいダイアログが表示されるので、ここにフィルターの条件値を入力します。AND条件やOR条件を組み合わせて複数の条件を指定することも可能です。例えば「2より大きく、かつ5ではない」といった指定もできます。

これでデータは5行だけになりました。「ニューオーリンズの家族で、かつ3人以上」という条件ですね。とても簡単です。なお、列のフィルターを解除したいときは、ドロップダウンを開いて「○○からフィルターをクリア」リンクをクリックするだけでOKです。

以上がシンプルなフィルターの基本です。直感的に使えて、結果もわかりやすいのが魅力です。次は、「フィルターオプション(詳細設定)」ダイアログを使った、より高度なフィルターを見ていきましょう。

Excelで高度なフィルター(詳細設定)を作成する

より複雑な条件でフィルターをかけたい場合は、「フィルターオプション」ダイアログを使用する必要があります。

例えば、こんな条件を考えてみましょう。

• ニューオーリンズ在住で家族が2人より多い OR
• クラークスビル在住で家族が3人より多い AND
• メールアドレスが .EDU で終わる世帯のみ

このような条件は、シンプルなフィルターでは実現できません。

条件範囲の準備

高度なフィルターを使うには、シートの構成を少し変えます。データ範囲の上に数行を挿入し、見出しラベルを一字一句正確にコピーして最上行に貼り付けます。

高度なフィルターの仕組みは以下の通りです。まず、挿入した行の各列に条件を入力し、その後「データ」タブの「並べ替えとフィルター」グループにある「詳細設定」ボタンをクリックします。

条件の入力ルール

それでは、具体的にどのように条件を入力するのか見ていきましょう。今回の例では、ニューオーリンズまたはクラークスビルのデータだけを表示したいので、E2セルとE3セルにそれぞれ都市名を入力します。

ここで重要なルールがあります。異なる行に入力した条件は「OR(または)」として扱われます。次に、「ニューオーリンズの家族は2人より多い」「クラークスビルの家族は3人より多い」という条件を加えます。C2セルに「>2」、C3セルに「>3」と入力しましょう。

「>2」と「New Orleans」が同じ行にあるため、これらはAND(かつ)の関係になります。3行目も同様です。最後に、メールアドレスが.eduで終わる世帯だけに絞り込みます。D2セルとD3セルの両方に「*.edu」と入力してください。「*」はワイルドカードで、任意の文字列を意味します。

フィルターの実行

条件の入力が完了したら、データ範囲内の任意のセルをクリックしてから「詳細設定」ボタンを押します。先にデータ範囲をクリックしておけば、「リスト範囲」は自動的に認識されます。続いて、「検索条件範囲」欄の右側にある小さなボタンをクリックします。

A1からE3までの範囲を選択し、同じボタンをもう一度クリックしてダイアログに戻ります。「OK」をクリックすると、データがフィルターされます!

ご覧のとおり、すべての条件に一致した結果はわずか3件になりました。なお、検索条件範囲の見出しは、データ本体の見出しと完全に一致している必要がある点にご注意ください。

この手法を使えば、さらに複雑な条件の組み合わせも自由自在です。いろいろ試して、目的の結果を引き出してみてください。最後に、フィルター後のデータに対して集計関数を適用する方法をご紹介します。

フィルター後のデータを集計する方法

ここで、「フィルターで表示中のデータの家族人数を合計したい」という場面を考えてみましょう。まず、リボンの「クリア」ボタンでフィルターを解除します。ご安心ください。「詳細設定」ボタンを押して「OK」をクリックするだけで、いつでも同じフィルターを再適用できます。

データ範囲の下に「合計」というセルを作成し、SUM関数で家族人数の合計を求めてみます。この例では「=SUM(C7:C31)」と入力しました。

全世帯を対象にすると、合計は78人です。それでは、先ほどの高度なフィルターを再適用してみましょう。

おっと!正しい答えは11人のはずなのに、合計は依然として78人のままです。これはなぜでしょうか?

実は、SUM関数は非表示の行を無視しないため、隠れた行も含めた全データで計算が行われてしまうのです。幸い、非表示行を除外して集計できる関数が用意されています。

SUBTOTAL関数を使う

1つ目はSUBTOTAL関数です。この関数を使う前に、まずフィルターをクリアしておきましょう。

フィルターを解除したら、「=SUBTOTAL(」と入力します。すると、多数の選択肢を含むドロップダウンリストが表示されます。この関数では、第1引数に数値で集計方法の種類を指定します。

今回のように合計(SUM)を出したい場合は「9」を入力するか、ドロップダウンから選択します。その後、カンマを打ち、集計対象のセル範囲を指定します。

Enterキーを押すと、フィルター解除状態では先ほどと同じ78が表示されます。しかし、ここでフィルターを再適用すると…なんと11が表示されました!

完璧です!まさに期待通りの動作です。これで、フィルター条件を変更しても、常に表示中の行だけが反映された集計結果を得られます。

AGGREGATE関数を使う

SUBTOTAL関数とほぼ同じ働きをするもう一つの関数がAGGREGATE関数です。違いは、「非表示の行を無視する」ことを明示的に指定する引数が追加されている点です。

第1引数は使用する集計関数の種類で、SUBTOTALと同様に「9」がSUMを表します。第2引数で非表示行を無視するために「5」を指定します。第3引数はSUBTOTALと同じく、集計対象のセル範囲です。

AGGREGATE関数や、MODE・MEDIAN・AVERAGEなどの統計関数の詳しい使い方については、集計関数に関する記事もぜひ参考にしてください。

本記事が、Excelでフィルターを作成・活用するための良いスタート地点となれば幸いです。ご不明な点があれば、お気軽にコメントをお寄せください。