Microsoft Excel(エクセル)で表を作成したときに、「フィルター」と呼ばれる機能を設定すると、データを並べ替えたり一部のデータだけを表示することができるので分析に便利です。
Excelの基本的な機能なので触ったことがある人は多いと思います。
ただ、フィルターには「〇〇以上〇〇未満」の数値を表示したり、黄色だけのセルを表示したり、フィルターの設定条件をあらかじめ登録しておく、など役に立つ機能が多く存在します。
この記事では、エクセルのフィルターに関する総集編の紹介として、基本操作と応用操作の２部構成で、様々な機能を解説します。
エクセルの「フィルター」「フィルタ」とは？
まずは、エクセルのフィルターとは何なのか機能の概要を簡単に紹介します。
エクセルでの「フィルター」とは、文字や数値が含まれた表から、特定の数値だけのデータを抽出したり、条件を満たすデータのみを表示したり、昇順や降順で並べ替えることが出来る機能です。
フィルターは、自動でフィルターを設定するデータを判断することから『オートフィルタ』や『フィルタ』と言われることもあります。
表の見出し列に、▼のボタンが表示されていれば、フィルター機能が設定されている表です。
フィルターを設定するための元データを準備
フィルターを使うためには、まずは元データを用意する必要があります。つまり、売上明細データや支払明細データや資産明細データなどの集計や分析したいデータのことです。
フィルターを正しく使うためには、元データに対して以下の4つの条件を整える必要があります。
特に表のすぐ下のセルにSUM関数が挿入されていると、表を並べ替えした時に合計行も並べ替えされてしまうので、注意してください。
【フィルターの元データの４つ条件】
①列項目に空白セルが無い
➁書式に統一性がある(全角、半角、カタカナなど）
③セルが結合されていない
④表に隣接したセルにデータが入っていない
また、フィルターを使って表を並べ替えしていると元の並び順が分からなくなります。
元の状態に戻したい場合は、表に列を追加し、連番(1、２、３・・・)を振ってデータをいつでも戻せるようにしておくことをお薦めします。
表にフィルターを設定する方法
元データが準備できたら、さっそく表に列見出しにフィルターを設定します。
フィルターは行見出しには設定できません。
フィルターは、①「ホーム」タブのリボンのボタンから設定する方法と、➁ショートカットキーを使う方法の２種類があります。
ショートカットキーはフィルターを解除する時にも利用できるので、ショートカットキーの方をおすすめします。
方法１．「ホーム」タブの「並べ替えと選択」ボタンから設定
「ホーム」タブのリボンのボタンからフィルターを設定する方法を紹介します。
まず、選択したい表の任意のセルをアクティブセル(※)にした状態で、「ホーム」タブ⇒「並べ替えとフィルター」⇒「フィルター」を選択します。
※アクティブセルとは、現在選択されているセルのことです。
そうすると、下の図のようにフィルターの設定が完了し、行の見出しに▼ボタンが表示されます。
フィルターを設定する列を直接指定することもできます。
フィルターを設定したい列見出しのセルをドラッグで選択してから、「フィルター」をクリックしてください。
そうすると、下の図のように表の一部だけにもフィルターが設定できます。
ただし、この状態でフィルターを利用すると、フィルターをかけていないB～D列は動かないので、表の並びが左右で変わって崩れてしまいます。くれぐれも表の一部だけにフィルターを設定して並べ替えしないよう注意してください。
方法２．「Ctrl＋Shift＋L」キーのショートカットキーを使う
２つ目はショートカットキーを使ったフィルターの設定方法です。
この３つのキーを同時押しすることでフィルターを設定することが可能です。
Ctrl＋Shift＋L
キーボードの配置は、下のとおりです。表の列見出しセルを選択してから、「Ctrl」キーと「Shift」キーを押しながら「L」キーを押します。
Macのエクセルであれば、「Command」キー +「Shift 」キー+「 F」キーがショートカットキーです。
フィルターがかからない場合の対処方法
フィルターは１つのワークシートに１つしか設定できません。
そのため、フィルターボタンを押したりショートカットキーを押してもフィルターがかからなかった場合は、もう一つのフィルターが邪魔をしている可能性があります。
２つのデータともフィルターを設定したい場合は、ワークシートを分けるか、片側のデータずつフィルタ―を利用しましょう。
フィルターのルール
・フィルターは各ワークシートに１つだけ設定可能
それでもフィルターが設定できない場合は、ワークシートを複数選択していないか確認してください。複数のワークシートを同時にフィルターを設定することはできません。
さらに、データ自体が更新できないようにシートが保護されている場合も同様にフィルターを設定できません。
フィルター機能の使い方(基本編)
フィルターの設定が完了すれば、リストを大きい順や小さい順に並べ替えしたり、特定のデータを抽出することが可能です。
まずは、フィルターの基本的な操作方法について詳しく解説します。
フィルターで昇順や降順で並べ替える方法
フィルターが設定されたデータを大きい順や小さい順に並べ替えすることができます。
フィルターで上から大きいものから順番に並べることを「降順」、小さいものから順番に並べ替えすることを「昇順」といいます。
まずは、昇順や降順を設定したい列のフィルターの▼ボタンを押します(図１)。次にメニューの「昇順」もしくは「降順」を選択します(図２)。
そうすると、フィルターを設定した列が昇順や降順で並べ替えすることができます。(下の例は”昇順”)
もし、漢字やひらがなの列に昇順や降順を使った場合は、あいうえお順で表の並びが変わります。
フィルターで特定のデータだけを表示する方法
フィルターで特定の値だけを表示して抽出することができます。
値を抽出したい列のフィルターの▼ボタンを押します。
次に表示されたリストから、抽出したい項目にチェック(✓)を入れます。チェックをいったんすべて消したい場合は、「すべての項目」をクリックし、チェックを外してください。
そうすると、下の図のように、特定の値のみが抽出されました。解除したい場合は、フィルターの(すべて選択）を使って全項目にチェックを入れて下さい。
フィルターを設定できる行は２行、３行と増やすことができます。さらに条件を絞って値を抽出したい場合は、同様の手順で値を抽出してください。
また、フィルターによってコントロールされている列見出しのフィルターマークは、以下のようになっています。表にフィルターがかかっているか確認したい時に参考にしてください。
フィルターを使う上での注意点
フィルターを使う上での注意点が３つあります。
フィルターの注意点
1．表の最下行に関数や数式が挿入されている場合
2．空白行が表内に含まれる場合
3．表に隣接している列にデータが挿入されている場合
まず、１つ目の「最下行に関数が挿入」されている状態で、フィルターで降順で並べ替えするとどうなるか紹介します。
フィルターで降順で並べ替えた瞬間、SUM関数のセルの値が一番大きかった影響で、SUM関数のセルが最上段に移動してしまします。このように、表の合計値や平均値を算出したい場合は、表の最下行の２行下に数式を挿入するようにしましょう。
続いて２つ目です。「空白行が間に挿入」されている表にフィルターをかけても、空白行より下はフィルターがかかっていません。そのため、下側のデータはフィルターを操作しても動きません。
最後の３つ目です。表にフィルタ―を設定した後に、表の左端や右端の列にデータを追加で入力しても、フィルター列は自動で拡張しません。この状態で列を並べ替えると、データの並びが崩れてしまいます。
フィルター機能を解除する方法
フィルターを解除して元に戻したい場合は、「ホーム」タブ⇒「並べ替えとフィルター」の中の「フィルター」を選択します。
もしくは、フィルターを設定するショートカットキーである、「Ctrl」キーと「Shift」キーを押しながら「L」キーを押すことでも、フィルターを解除できます。一瞬で処理できるので、操作に慣れてくればこちらを利用してください。
フィルター機能の使い方(応用編)
ここからは、覚えておくと得するフィルター機能の応用テクニックを紹介します。
知らない使い方が１つはあると思うので、最後まで見てみて下さい。
テキストフィルターで「〇〇で終わる」条件を設定する方法
「テキストフィルター」を利用すると、特定の条件の文字だけのデータを抽出することができます。
例えば「”府”で終わる文字」だけを抽出する、といった場合に使用します。
まず、フィルターのメニューを表示し、「テキストフィルター」から「指定の値で終わる」ボタンを選択します。
「カスタムオートフィルター」画面が表示されます。条件欄に「府」と入力して、「OK」を押して条件を確定します。
そうすると、A列が「府」で終わる文字だけを抽出することができました。
抽出する条件は、OR条件(もしくは)やAND条件(なおかつ)などの２つの複数条件でも利用できます。
例えば、仮に『「府」もしくは「都」で終わる』という２つの条件を設定する場合は、カスタムオートフィルター画面は、１つ目の条件のすぐ下の「OR」にチェックを入れて、２つ目の条件を入力します。
その他にも、テキストフィルターの「カスタムオートフィルター」画面では、以下のようなフィルター条件を設定することができます。
テキストフィルターのメニュー
指定の値に等しい ⇒ 例：”大阪府”に等しい
指定の値に等しくない ⇒ 例：”大阪府”以外
指定の値で始まる ⇒ 例：”大”で始まる
指定の値で終わる ⇒ 例：”府”で終わる
指定の値を含む ⇒ 例：”阪”を含む
指定の値を含まない ⇒ 例：”阪”を含まない
条件にはワイルドカードと呼ばれる「*」(アスタリスク)や「?」(疑問符)といった記号を用いて、より高度な条件を設定することも可能です。ワイルドカードの使い方は以下の記事を参考にしてください。
数値フィルターで「〇〇以上△△以下」の条件を設定する方法
「数値フィルター」を利用すると、特定の数値のデータを抽出することができます。
例えば「5,000以上6,000未満」だけの数値を抽出する、といった場合に使用します。
まず、対象列のフィルターメニューを表示し、「数値フィルター」から「指定の範囲内」ボタンを選択します。
「カスタムオートフィルター」画面が表示されます。上段の１つ目の条件を「以上」＆「5000」、下段の２つ目の条件を「より小さい」＆「6000」と入力します。
AND条件(なおかつ)で値を見つけたいので、中段の「AND」にチェックを入れて画面右下の「OK」を押してます。
そうすると、データから「5,000以上6,000未満」だけの行を表示することができます。
その他にも、数値フィルターの中のメニューを使って、以下のようなフィルター条件を設定することができます。
数値フィルターのメニュー
指定の値に等しい ⇒ 例：”5,000”に等しい
指定の値に等しくない ⇒ 例：”5,000”以外
指定の値より大きい ⇒ 例：”5,000”より大きい
指定の値以上 ⇒ 例：”5,000”以上
指定の値より小さい ⇒ 例：”5,000”未満
指定の値以下 ⇒ 例：”5,000”以下
指定の範囲内 ⇒ 例：”5,000”以上”6,000”未満
トップテン ⇒ 例：上位３項目、下位４項目、上位12項目など
平均より上 ⇒ 例：平均より上のデータだけを表示
平均より下 ⇒ 例：平均より下のデータだけを表示
特に、この中でもトップテンというメニューは、上位３項目や下位５項目などの数値だけを表示できます。データを普段よく分析している人にとっては重宝する機能です。
複数列に優先順位をつけて並べ替えする方法
フィルターを使って、列毎に優先順位を付けて並べ替えする方法を解説します。
下の社員リスト表を「優先順位１：入社が早い人から上に表示」「優先順位２：年齢が年上の人から上に表示」で並べ替えする方法を紹介します。
- 優先順位１位：入社年月(C列)が早い人を上に表示
- 優先順位２位：年齢(D列)が上の人を上に表示
フィルターを使って列毎に優先順位を付けて並べ替えるためには、優先順位が低い列から順番にフィルターで並べ替えをしていきます。
まずは、優先順位２位の「年齢が上の人を上」にしたいので、D列を降順で並べ替えします。
次に、優先順位１位の「入社年月が若い人を上」にしたいので、C列を昇順で並べ替えします。
そうすると、C列が昇順で並び、さらに同じ入社年のデータでも年齢は降順で並べ替えされているので、第２位の優先順位が有効になっていることが分かります。
このように、フィルターで優先順位をつけて各列を並べ替えするのには少しテクニックが必要です。そのため、この場合はフィルター機能を使うより「並べ替え」という機能を使った方が便利です。
以下の記事で「並べ替え」機能の使い方を紹介していますので、参考にしてみてください。
フィルターの応用版ショートカットキー
上記で紹介したとおり、フィルターを設定したり解除するショートカットキーは、「Ctrl」キー＋「Shift」キー＋「L」キーの同時押しです。
しかし、フィルターを設定し、なおかつ昇順や降順で並べ替えしたりデータを抽出する「アプリケーションキーを使ったショートカットキー」が存在します。
アプリケーションキーは、キーボードの矢印キーの２つ左に配置されているドアのデザインのようなキーです。
一例として、フィルターを設定していない元データのセルをクリックしてから、「ｱﾌﾟﾘｹｰｼｮﾝｷｰ」⇒「E」⇒「V」の順番でキーを押すと『フィルタを設定でき、なおかつ選択セルと同じ値のセルを抽出』することが出来ます。
例えば、下の表から販売店舗の「大牟田店」だけのデータを抽出するために、C4セルを選択します。
その後、C4セルを選択した状態で、「ｱﾌﾟﾘｹｰｼｮﾝｷｰ」⇒「E」⇒「V」の順番でキーを押します。
そうすると、表全体にフィルターが設定されて、なおかつ販売店舗が「大牟田店」の値のみが抽出されます。
このように、アプリケーションキーを使ったショートカットキーは下のようなものがあります。
アプリケーションキーのショートカットキー
データの抽出：「ｱﾌﾟﾘｹｰｼｮﾝｷｰ」⇒「E」⇒「V」
昇順で並べ替え：「ｱﾌﾟﾘｹｰｼｮﾝｷｰ」⇒「O」⇒「S」
降順で並べ替え：「ｱﾌﾟﾘｹｰｼｮﾝｷｰ」⇒「O」⇒「O」
一見覚えるのが大変で面倒と思うかもしれませんが、２つの機能を同時に実行できるので、かなり便利なショートカットキーです。
以下の記事で詳しくショートカットキーの仕組みを紹介していますので、参考にしてみてください。
フィルターの設定条件を保存して利用する方法
フィルターで設定した並べ替えや抽出の条件を保存することができます。
例えば、「D列が30以上50以下の値で、なおかつE列が60以上の値」といった条件を登録し、必要な時に再利用します。
何度もフィルターの条件を変更する必要がある場合などに便利な機能です。
まずは、普通にフィルターで並べ替えや特定のデータだけを表示して、「表示」タブの「ユーザー設定のビュー」ボタンをクリックします。
「ユーザー設定のビュー」ダイアログボックスが表示されるので、「追加」を押します。
「ビューの追加」画面が表示されるので、登録する名前を入力し「OK」を押して確定します。以上の操作で、フィルター条件の登録が完了します。
登録した条件を利用する時は、「表示」タブの「ユーザー設定のビュー」ボタンをクリックします。「ユーザー設定のビュー」画面で登録したビューを選択して「表示」を押すると、フィルターが自動で設定され、登録した条件に該当するデータのみが表示されます。
以下の記事で「ユーザー設定のビュー」の使い方を詳しく紹介していますので、合わせて参考にしてください。
色で並べ替えしたり抽出する方法
フィルターは文字だけでなく、『背景色が水色のセル』や『フォントの色が赤色のセル』など特定の色のデータを抽出したり並べ替えすることが出来ます。
使い方の事例として、下の表のD列が黄色の塗りつぶしセルだけを表示する方法を紹介します。
フィルターメニューを開き、「色フィルター」から表示したい色を選択します。
そうすると、D列が黄色塗りつぶしのセルだけを表示することができます。
このようにフィルターで特定の色だけを表示したり、並べ替えする方法を下の記事で紹介しています。興味のある方は参考にしてみてください。
フィルターより「スライサー」の方が便利！？
今回はExcelのフィルター機能の使い方を基本編から応用編まで紹介しました。
フィルターを使うと、特定の条件のデータを表示できるので非表示に便利ですが、フィルターのボタンが小さく、絞り込みたい項目を一つずつチェックするのは意外と面倒です。
こんな時、表に「テーブル」機能を設定しておくと、「スライサー」機能というデータの抽出機能を利用することができます。
視覚的にもデータが抽出しやく、それぞれの列で表示したい項目を簡単に設定できます。
以下の記事でテーブル機能の設定方法やスライサーの使い方を詳しく使い方を紹介していますので、オートフィルターに使い慣れてきたら、今度のスライサーも使ってみてください。
