いまさら聞けないオートフィルタでデータを抽出する方法まとめ

公開日: 集計

オートフィルタオートフィルタとは、指定した条件に合致する行だけを表示する機能です。

オートフィルタを使うと、このような表があるときに、

filter_1_1

「勘定科目が会議費のものだけを表示させる」
「金額が5,000円以上のものだけを表示させる」
というように、条件に当てはまるものだけを表示させることができます。


オートフィルタ基本編

まず最初に、オートフィルタの使い方と、オートフィルタの解除方法を紹介します。

範囲を選択してオートフィルタを適用する

最初にオートフィルタを適用する範囲を選択します。
その後、リボンから「データ」→「フィルタ」を押します。

filter_1_2

次のように、各列タイトルの右側に▼マークが出てきます。
これで、オートフィルタが使える状態になりました。

filter_1_3

オートフィルタ設定のショートカットキー

オートフィルタを適用する範囲を選択した後に、
「Ctrl+Shift+L」を押すと、オートフィルタが設定できます。

filter_1_4

抽出する値の指定方法

実際に、抽出する値を指定します。

例えば、勘定科目が「会議費」のものだけを表示させるためには、次のようにします。

a)勘定科目列の右横の「▼」を押し、会議費を選択

勘定科目列の右横の「▼」を押すと、入力されている勘定科目の一覧が表示されます。
会議費以外のチェックをはずします。

※「(すべて選択)」のチェックをはずすと、すべてのチェックがはずれます。
 その後、改めて「会議費」にチェックを入れると「会議費」だけチェックができます。

filter_1_5

これで、会議費だけが表示された状態になります。

filter_1_6

抽出する値指定時のショートカットキー

「▼」を押す代わりに、
B1セルを選択した状態で「Alt」+「↓」を押すと、
先ほどと同様に抽出する値を指定する画面が出てきます。

オートフィルタの解除

オートフィルタを解除する場合、

  • ▼マークを表示させたままで、全ての行を表示させる
  • オートフィルタ自体使えないようにする(=▼マークを消す)

のどちらかの方法があります。

▼マークを表示させたままで、すべての行を表示させるには?

リボンから「データ」→「クリア」を選択すると、すべての行が表示されます。

filter_1_7

filter_1_3
※▼マークはついたままの状態で、すべての行が表示されます

オートフィルタ自体使えないようにする(=▼マークを消す)

リボンから「データ」→「フィルタ」ボタンを押すと、▼マークが消えオートフィルタが使えなくなります。

filter_1_8

filter_1_1

フィルタ解除のショートカットキー

先ほど説明した2つのうち、
2つ目の「オートフィルタ自体を使えないようにする(=▼マークを消す)」は、ショートカットキーがあり、
「Ctrl+Shift+L」を押すと「▼マーク」を消すことができます。

一方で、1つ目の「▼マークを表示させたままで、すべての行を表示させる」ショートカットキーはありません

ただし、「Ctrl+Shift+L」はオートフィルタ設定のショートカットでもあります。
そのため、「Ctrl+Shift+L」を2回押すと、いったんオートフィルタが解除され再度オートフィルタが設定されます。

結果的に、多くの場合、1つ目の「▼マークを表示させたままで、すべての行を表示させる」と同じ動作になります。

ただし、オートフィルタをかけたい表に空行がある場合などの場合は、単純に「Ctrl+Shift+L」を2回押しただけだと、オートフィルタの範囲が変わってしまう場合があるので、注意が必要です。

オートフィルタが使えないときの要確認事項

オートフィルタが使えない場合には、次の点を確認してみてください。

保護されたビューで開いている場合

ネットワークドライブや、インターネットからファイルをダウンロードした場合に「保護されたビュー」の状態になっていると、オートフィルタは使えません。

filter_1_9

「編集を有効」にしてから、オートフィルタを使ってください。

複数のシートを選択している場合

複数のシートを選択している場合、オートフィルタは使えません。
1つのシートだけを選択した状態で、オートフィルタを適用してください。

シートの保護をしている場合

シートの保護がかかっている場合、オートフィルタが使えないことがあります。
可能であれば、シートの保護を解除しましょう。

オートフィルタ応用編

ここからは、応用編として、
オートフィルタを使って、やや複雑なことをする方法を紹介していきます。

フィルタ後、表示されている行の合計を計算する

普通の関数を使うと、オートフィルタをかけても、非表示行を含めて計算がされます。
フィルタをかけた後に、表示されている行についてだけ集計をしたい場合には、subtotal関数やaggregate関数を使います。
(参考)知らなかった!subtotal関数の2つの使い方を完全マスター

filter_1_10

抽出条件を手入力する(あるいは、ワイルドカードで指定する)

抽出する条件を、いちいち1つずつ選択するのが面倒な場合には、項目を選択する代わりに

  • テキストフィルター
  • 数値フィルター
  • 日付フィルター

から、手動でフィルターを設定する画面を表示させることができます。

filter_1_11
※テキスト・数値・日付の、どのフィルターが表示されるかは列に入力されている内容によって変わります。

filter_1_12

抽出条件は、通常通り手入力もできますし、「*」「?」などのワイルドカードを使うこともできます。

記号 意味
* 任意の複数文字に対応
? 任意の1文字に対応

オートフィルタで複数列のand条件を指定する

複数列でand条件を指定するには、通常通り、複数列でオートフィルタをかけます。

例えば、

  • 勘定科目:会議費
  • 日付:2016/3/2

の両方の条件を満たすものを抽出したいときには、それぞれの列でオートフィルタをかければOKです。

filter_1_13

オートフィルタで複数列のor条件を指定する

オートフィルタで、複数列のor条件を指定することはできません。

どうしても、複数列のor条件を指定したい、という場合には、条件に該当する行だけ「X」と表示させるような作業用の列を作って、その列に対してオートフィルタを適用します。

たとえば、

  • 勘定科目:会議費
  • 日付:2016/3/2

のどちらか一方の条件を満たす行を抽出したい、という場合には、次のようにします。

1.この条件を満たす行だけ「X」となるような作業列を作る
filter_1_14
2.作業列でオートフィルタをかける
filter_1_15

オートフィルタを使うための表の作り方

最後に、オートフィルタを使うためには、一定のルールに従って、表を作っておかないといけません。

以下、その「ルール」の主なものを開設していきます。

上の行と同じ値を省略しない

上の行と同じ値があるからといって、入力を省略すると、オートフィルタがうまく動きません。

例えば、下の図では「会議費」でオートフィルタをかけています。

3行目、16行目は「会議費」なので、本来は表示してほしいのですが、勘定科目列が空欄になっているせいで、オートフィルタをかけても抽出されません。

filter_1_16

セルの結合は使わない

セルの結合をした場合、エクセルから見ると左上セル以外は「空欄」と判断されます。

その結果、先ほどの「値を省略した場合」と同様に、オートフィルタがうまく動きません。

filter_1_17

オートフィルタまとめ

オートフィルタは、目的のデータだけを表示させるのに便利な機能です。
ぜひ、使いこなせるようになってください。


  • twitter
  • facebook
  • はてなブックマーク

経理事務のためのエクセル基礎講座動画マニュアル無料配布中

「経理事務のためのエクセル基礎講座(初級編)」(動画マニュアル 総収録時間162分)を無料プレゼント中です!

このマニュアルで解説していることを一通り学べば、経理事務を行う上で最低限必要となる知識が得られます。

ご登録者の方には、合わせて、公認会計士が実体験を通して身に付けたエクセルを使う技をメールにてお伝えしていきます!