【Excel基本~応用編】フィルター機能の設定方法と使い方。上手くいかない場合の対処方法も

Microsoft Excel(エクセル)で表を作成したときに、「フィルター」と呼ばれる機能を設定すると、データを並べ替えたり一部のデータだけを表示することができるので分析に便利です。

Excelの基本的な機能なので触ったことがある人は多いと思います。

ただ、フィルターには「〇〇以上〇〇未満」の数値を表示したり、黄色だけのセルを表示したり、フィルターの設定条件をあらかじめ登録しておく、など役に立つ機能が多く存在します。

この記事では、エクセルのフィルターに関する総集編の紹介として、基本操作応用操作の2部構成で、様々な機能を解説します。

エクセルの「フィルター」「フィルタ」とは?

まずは、エクセルのフィルターとは何なのか機能の概要を簡単に紹介します。

エクセルでの「フィルター」とは、文字や数値が含まれた表から、特定の数値だけのデータを抽出したり、条件を満たすデータのみを表示したり、昇順や降順で並べ替えることが出来る機能です。

フィルターは、自動でフィルターを設定するデータを判断することから『オートフィルタ』や『フィルタ』と言われることもあります。

表の見出し列に、▼のボタンが表示されていれば、フィルター機能が設定されている表です。

フィルターが設定されている表
フィルターが設定されている表

フィルターを設定するための元データを準備

フィルターを使うためには、まずは元データを用意する必要があります。つまり、売上明細データや支払明細データや資産明細データなどの集計や分析したいデータのことです。

フィルターを正しく使うためには、元データに対して以下の4つの条件を整える必要があります。

特に表のすぐ下のセルにSUM関数が挿入されていると、表を並べ替えした時に合計行も並べ替えされてしまうので、注意してください。

【フィルターの元データの4つ条件】
①列項目に空白セルが無い
➁書式に統一性がある(全角、半角、カタカナなど)
③セルが結合されていない
④表に隣接したセルにデータが入っていない

フィルターの元データの「良い例」と「悪い例」
フィルターの元データの「良い例」と「悪い例」

また、フィルターを使って表を並べ替えしていると元の並び順が分からなくなります

元の状態に戻したい場合は、表に列を追加し、連番(1、2、3・・・)を振ってデータをいつでも戻せるようにしておくことをお薦めします。

表にフィルターを設定する方法

元データが準備できたら、さっそく表に列見出しにフィルターを設定します。

フィルターは行見出しには設定できません。

フィルターは、①「ホーム」タブのリボンのボタンから設定する方法と、➁ショートカットキーを使う方法の2種類があります。

ショートカットキーはフィルターを解除する時にも利用できるので、ショートカットキーの方をおすすめします。

方法1.「ホーム」タブの「並べ替えと選択」ボタンから設定

「ホーム」タブのリボンのボタンからフィルターを設定する方法を紹介します。

まず、選択したい表の任意のセルをアクティブセル(※)にした状態で、「ホーム」タブ⇒「並べ替えとフィルター」⇒「フィルター」を選択します。

※アクティブセルとは、現在選択されているセルのことです。

フィルターを「フィルター」ボタンで設定する方法
フィルターを「フィルター」ボタンで設定する方法

そうすると、下の図のようにフィルターの設定が完了し、行の見出しに▼ボタンが表示されます。

フィルター完了後の列見出し
フィルター完了後の列見出し

フィルターを設定する列を直接指定することもできます。

フィルターを設定したい列見出しのセルをドラッグで選択してから、「フィルター」をクリックしてください。

表の一部だけをフィルターで設定する方法
表の一部だけをフィルターで設定する方法

そうすると、下の図のように表の一部だけにもフィルターが設定できます。

ただし、この状態でフィルターを利用すると、フィルターをかけていないB~D列は動かないので、表の並びが左右で変わって崩れてしまいます。くれぐれも表の一部だけにフィルターを設定して並べ替えしないよう注意してください。

注意点:表の一部だけをフィルター化
注意点:表の一部だけをフィルター化

方法2.「Ctrl+Shift+L」キーのショートカットキーを使う

2つ目はショートカットキーを使ったフィルターの設定方法です。

この3つのキーを同時押しすることでフィルターを設定することが可能です。

CtrlShiftL

キーボードの配置は、下のとおりです。表の列見出しセルを選択してから、「Ctrl」キーと「Shift」キーを押しながら「L」キーを押します。

フィルターのショートカットキーのキーボード位置
フィルターのショートカットキーのキーボード位置

Macのエクセルであれば、「Command」キー +「Shift 」キー+「 F」キーがショートカットキーです。

フィルターがかからない場合の対処方法

フィルターは1つのワークシートに1つしか設定できません。

そのため、フィルターボタンを押したりショートカットキーを押してもフィルターがかからなかった場合は、もう一つのフィルターが邪魔をしている可能性があります。

2つのデータともフィルターを設定したい場合は、ワークシートを分けるか、片側のデータずつフィルタ―を利用しましょう。

フィルターのルール

・フィルターは各ワークシートに1つだけ設定可能

それでもフィルターが設定できない場合は、ワークシートを複数選択していないか確認してください。複数のワークシートを同時にフィルターを設定することはできません。

さらに、データ自体が更新できないようにシートが保護されている場合も同様にフィルターを設定できません。

フィルター機能の使い方(基本編)

フィルターの設定が完了すれば、リストを大きい順や小さい順に並べ替えしたり、特定のデータを抽出することが可能です。

まずは、フィルターの基本的な操作方法について詳しく解説します。

フィルターで昇順や降順で並べ替える方法

フィルターが設定されたデータを大きい順や小さい順に並べ替えすることができます。

フィルターで上から大きいものから順番に並べることを「降順」、小さいものから順番に並べ替えすることを「昇順」といいます。

まずは、昇順や降順を設定したい列のフィルターの▼ボタンを押します(図1)。次にメニューの「昇順」もしくは「降順」を選択します(図2)。

フィルターで昇順や降順で並べ替えする方法
フィルターで昇順や降順で並べ替えする方法

そうすると、フィルターを設定した列が昇順や降順で並べ替えすることができます。(下の例は”昇順”)

表を昇順で並べ替え
表を昇順で並べ替え

もし、漢字やひらがなの列に昇順や降順を使った場合は、あいうえお順で表の並びが変わります。

フィルターで特定のデータだけを表示する方法

フィルターで特定の値だけを表示して抽出することができます。

値を抽出したい列のフィルターの▼ボタンを押します。

次に表示されたリストから、抽出したい項目にチェック(✓)を入れます。チェックをいったんすべて消したい場合は、「すべての項目」をクリックし、チェックを外してください。

フィルターで一部のデータだけを表示する方法
フィルターで一部のデータだけを表示する方法

そうすると、下の図のように、特定の値のみが抽出されました。解除したい場合は、フィルターの(すべて選択)を使って全項目にチェックを入れて下さい。

フィルターで一部のデータだけが表示されている状態

フィルターを設定できる行は2行、3行と増やすことができます。さらに条件を絞って値を抽出したい場合は、同様の手順で値を抽出してください。

また、フィルターによってコントロールされている列見出しのフィルターマークは、以下のようになっています。表にフィルターがかかっているか確認したい時に参考にしてください。

昇順、降順、抽出のフィルターマークの違い
昇順、降順、抽出のフィルターマークの違い

フィルターを使う上での注意点

フィルターを使う上での注意点が3つあります。

フィルターの注意点

1.表の最下行に関数や数式が挿入されている場合

2.空白行が表内に含まれる場合

3.表に隣接している列にデータが挿入されている場合

まず、1つ目の「最下行に関数が挿入」されている状態で、フィルターで降順で並べ替えするとどうなるか紹介します。

表の最下行にSUM関数
表の最下行にSUM関数

フィルターで降順で並べ替えた瞬間、SUM関数のセルの値が一番大きかった影響で、SUM関数のセルが最上段に移動してしまします。このように、表の合計値や平均値を算出したい場合は、表の最下行の2行下に数式を挿入するようにしましょう。

フィルターによってSUM関数のセルが最上段に移動
フィルターによってSUM関数のセルが最上段に移動

続いて2つ目です。「空白行が間に挿入」されている表にフィルターをかけても、空白行より下はフィルターがかかっていません。そのため、下側のデータはフィルターを操作しても動きません。

フィルターで空白行が入っている場合の注意点
フィルターで空白行が入っている場合の注意点

最後の3つ目です。表にフィルタ―を設定した後に、表の左端や右端の列にデータを追加で入力しても、フィルター列は自動で拡張しません。この状態で列を並べ替えると、データの並びが崩れてしまいます。

フィルターの未設定列にデータを追加
フィルターの未設定列にデータを追加

フィルター機能を解除する方法

フィルターを解除して元に戻したい場合は、「ホーム」タブ⇒「並べ替えとフィルター」の中の「フィルター」を選択します。

フィルターを解除する方法
フィルターを解除する方法

もしくは、フィルターを設定するショートカットキーである、「Ctrl」キーと「Shift」キーを押しながら「L」キーを押すことでも、フィルターを解除できます。一瞬で処理できるので、操作に慣れてくればこちらを利用してください。

フィルター機能の使い方(応用編)

ここからは、覚えておくと得するフィルター機能の応用テクニックを紹介します。

知らない使い方が1つはあると思うので、最後まで見てみて下さい。

テキストフィルターで「〇〇で終わる」条件を設定する方法

テキストフィルター」を利用すると、特定の条件の文字だけのデータを抽出することができます。

例えば「”府”で終わる文字」だけを抽出する、といった場合に使用します。

まず、フィルターのメニューを表示し、「テキストフィルター」から「指定の値で終わる」ボタンを選択します。

フィルターで指定した値で終わる文字を抽出
フィルターで指定した値で終わる文字を抽出

「カスタムオートフィルター」画面が表示されます。条件欄に「府」と入力して、「OK」を押して条件を確定します。

カスタムオートフィルターの条件の設定方法
カスタムオートフィルターの条件の設定方法

そうすると、A列が「府」で終わる文字だけを抽出することができました。

特定の文字で終わるフィルターの使い方
特定の文字で終わるフィルターの使い方

抽出する条件は、OR条件(もしくは)やAND条件(なおかつ)などの2つの複数条件でも利用できます。

例えば、仮に『「府」もしくは「都」で終わる』という2つの条件を設定する場合は、カスタムオートフィルター画面は、1つ目の条件のすぐ下の「OR」にチェックを入れて、2つ目の条件を入力します。

カスタムオートフィルターで複数条件を設定する方法
カスタムオートフィルターで複数条件を設定する方法

その他にも、テキストフィルターの「カスタムオートフィルター」画面では、以下のようなフィルター条件を設定することができます。

テキストフィルターのメニュー

指定の値に等しい ⇒ 例:”大阪府”に等しい

指定の値に等しくない ⇒ 例:”大阪府”以外

指定の値で始まる ⇒ 例:”大”で始まる

指定の値で終わる ⇒ 例:”府”で終わる

指定の値を含む ⇒ 例:”阪”を含む

指定の値を含まない ⇒ 例:”阪”を含まない

条件にはワイルドカードと呼ばれる「*」(アスタリスク)や「?」(疑問符)といった記号を用いて、より高度な条件を設定することも可能です。ワイルドカードの使い方は以下の記事を参考にしてください。

数値フィルターで「〇〇以上△△以下」の条件を設定する方法

数値フィルター」を利用すると、特定の数値のデータを抽出することができます。

例えば「5,000以上6,000未満」だけの数値を抽出する、といった場合に使用します。

まず、対象列のフィルターメニューを表示し、「数値フィルター」から「指定の範囲内」ボタンを選択します。

数値フィルターから「指定の範囲内」を選択
数値フィルターから「指定の範囲内」を選択

「カスタムオートフィルター」画面が表示されます。上段の1つ目の条件を「以上」&「5000」、下段の2つ目の条件を「より小さい」&「6000」と入力します。

AND条件(なおかつ)で値を見つけたいので、中段の「AND」にチェックを入れて画面右下の「OK」を押してます。

カスタムオートフィルターで「〇〇以上かつ△△未満」を設定する方法
カスタムオートフィルターで「〇〇以上かつ△△未満」を設定する方法

そうすると、データから「5,000以上6,000未満」だけの行を表示することができます。

数値フィルターを使った結果
数値フィルターを使った結果

その他にも、数値フィルターの中のメニューを使って、以下のようなフィルター条件を設定することができます。

数値フィルターのメニュー

指定の値に等しい ⇒ 例:”5,000”に等しい

指定の値に等しくない ⇒ 例:”5,000”以外

指定の値より大きい ⇒ 例:”5,000”より大きい

指定の値以上 ⇒ 例:”5,000”以上

指定の値より小さい ⇒ 例:”5,000”未満

指定の値以下 ⇒ 例:”5,000”以下

指定の範囲内 ⇒ 例:”5,000”以上”6,000”未満

トップテン ⇒ 例:上位3項目、下位4項目、上位12項目など

平均より上 ⇒ 例:平均より上のデータだけを表示

平均より下 ⇒ 例:平均より下のデータだけを表示

特に、この中でもトップテンというメニューは、上位3項目下位5項目などの数値だけを表示できます。データを普段よく分析している人にとっては重宝する機能です。

フィルターのトップテンメニューから上位4項目を表示
フィルターのトップテンメニューから上位4項目を表示

複数列に優先順位をつけて並べ替えする方法

フィルターを使って、列毎に優先順位を付けて並べ替えする方法を解説します。

下の社員リスト表を「優先順位1:入社が早い人から上に表示」「優先順位2:年齢が年上の人から上に表示」で並べ替えする方法を紹介します。

フィルターに使う元データ
フィルターに使う元データ
  • 優先順位1位:入社年月(C列)が早い人を上に表示
  • 優先順位2位:年齢(D列)が上の人を上に表示

フィルターを使って列毎に優先順位を付けて並べ替えるためには、優先順位が低い列から順番にフィルターで並べ替えをしていきます。

まずは、優先順位2位の「年齢が上の人を上」にしたいので、D列を降順で並べ替えします。

優先順位2位を先に並べ替え
優先順位2位を先に並べ替え

次に、優先順位1位の「入社年月が若い人を上」にしたいので、C列を昇順で並べ替えします。

最後に優先順位1位の列を並べ替え
最後に優先順位1位の列を並べ替え

そうすると、C列が昇順で並び、さらに同じ入社年のデータでも年齢は降順で並べ替えされているので、第2位の優先順位が有効になっていることが分かります。

優先順位をつけてフィルターの並べ替えが完了
優先順位をつけてフィルターの並べ替えが完了

このように、フィルターで優先順位をつけて各列を並べ替えするのには少しテクニックが必要です。そのため、この場合はフィルター機能を使うより「並べ替え」という機能を使った方が便利です。

以下の記事で「並べ替え」機能の使い方を紹介していますので、参考にしてみてください。

フィルターの応用版ショートカットキー

上記で紹介したとおり、フィルターを設定したり解除するショートカットキーは、「Ctrl」キー+「Shift」キー+「L」キーの同時押しです。

しかし、フィルターを設定し、なおかつ昇順や降順で並べ替えしたりデータを抽出する「アプリケーションキーを使ったショートカットキー」が存在します。

アプリケーションキーは、キーボードの矢印キーの2つ左に配置されているドアのデザインのようなキーです。

アプリケーションキーの写真
アプリケーションキーの写真

一例として、フィルターを設定していない元データのセルをクリックしてから、「アプリケーションキー」⇒「E」「V」の順番でキーを押すと『フィルタを設定でき、なおかつ選択セルと同じ値のセルを抽出』することが出来ます。

アプリケーションキーを使ったフィルターのショートカットの一例(抽出)
アプリケーションキーを使ったフィルターのショートカットの一例(抽出)

例えば、下の表から販売店舗の「大牟田店」だけのデータを抽出するために、C4セルを選択します。

その後、C4セルを選択した状態で、「アプリケーションキー」⇒「E」⇒「V」の順番でキーを押します。

「アプリケーションキー」⇒「E」⇒「V」のショートカットキー
「アプリケーションキー」⇒「E」⇒「V」のショートカットキー

そうすると、表全体にフィルターが設定されて、なおかつ販売店舗が「大牟田店」の値のみが抽出されます。

ショートカットキーでフィルターが設定され、かつ値の抽出も完了
ショートカットキーでフィルターが設定され、かつ値の抽出も完了

このように、アプリケーションキーを使ったショートカットキーは下のようなものがあります。

アプリケーションキーのショートカットキー

データの抽出:「アプリケーションキー」⇒「E」⇒「V」

昇順で並べ替え:「アプリケーションキー」⇒「O」⇒「S」

降順で並べ替え:「アプリケーションキー」⇒「O」⇒「O」

一見覚えるのが大変で面倒と思うかもしれませんが、2つの機能を同時に実行できるので、かなり便利なショートカットキーです。

以下の記事で詳しくショートカットキーの仕組みを紹介していますので、参考にしてみてください。

フィルターの設定条件を保存して利用する方法

フィルターで設定した並べ替えや抽出の条件を保存することができます。

例えば、「D列が30以上50以下の値で、なおかつE列が60以上の値」といった条件を登録し、必要な時に再利用します。

何度もフィルターの条件を変更する必要がある場合などに便利な機能です。

まずは、普通にフィルターで並べ替えや特定のデータだけを表示して、「表示」タブの「ユーザー設定のビュー」ボタンをクリックします。

フィルター条件を保存する方法(ユーザー設定のビュー)
フィルター条件を保存する方法(ユーザー設定のビュー)

「ユーザー設定のビュー」ダイアログボックスが表示されるので、「追加」を押します。

「ユーザー設定のビュー」ダイアログボックス
「ユーザー設定のビュー」ダイアログボックス

「ビューの追加」画面が表示されるので、登録する名前を入力し「OK」を押して確定します。以上の操作で、フィルター条件の登録が完了します。

フィルターの設定条件を登録
フィルターの設定条件を登録

登録した条件を利用する時は、「表示」タブの「ユーザー設定のビュー」ボタンをクリックします。「ユーザー設定のビュー」画面で登録したビューを選択して「表示」を押すると、フィルターが自動で設定され、登録した条件に該当するデータのみが表示されます。

登録したビューを利用する方法
登録したビューを利用する方法

以下の記事で「ユーザー設定のビュー」の使い方を詳しく紹介していますので、合わせて参考にしてください。

色で並べ替えしたり抽出する方法

フィルターは文字だけでなく、背景色が水色のセル』や『フォントの色が赤色のセルなど特定の色のデータを抽出したり並べ替えすることが出来ます。

使い方の事例として、下の表のD列が黄色の塗りつぶしセルだけを表示する方法を紹介します。

フィルターで特定の色のセルだけを表示する方法
フィルターで特定の色のセルだけを表示する方法

フィルターメニューを開き、「色フィルター」から表示したい色を選択します。

色フィルターで特定の色のセルだけを表示
色フィルターで特定の色のセルだけを表示

そうすると、D列が黄色塗りつぶしのセルだけを表示することができます。

フィルターで黄色塗りつぶしセルだけ表示
フィルターで黄色塗りつぶしセルだけ表示

このようにフィルターで特定の色だけを表示したり、並べ替えする方法を下の記事で紹介しています。興味のある方は参考にしてみてください。

フィルターより「スライサー」の方が便利!?

今回はExcelのフィルター機能の使い方を基本編から応用編まで紹介しました。

フィルターを使うと、特定の条件のデータを表示できるので非表示に便利ですが、フィルターのボタンが小さく、絞り込みたい項目を一つずつチェックするのは意外と面倒です。

こんな時、表に「テーブル」機能を設定しておくと、「スライサー」機能というデータの抽出機能を利用することができます。

視覚的にもデータが抽出しやく、それぞれの列で表示したい項目を簡単に設定できます。

スライサーは視覚的にもサイズ的にもデータを抽出しやすい
スライサーは視覚的にもサイズ的にもデータを抽出しやすい

以下の記事でテーブル機能の設定方法やスライサーの使い方を詳しく使い方を紹介していますので、オートフィルターに使い慣れてきたら、今度のスライサーも使ってみてください。