わえなび ワード&エクセル問題集

当サイトはWordとExcelの実力を鍛える最高水準の特訓問題集です。リンクを張るのは自由です。みんなで勉強しましょう!

【Excel】オートフィルタをさらに深く理解するための応用事例演習

Excelのオートフィルタはたくさんのデータの中から条件にあったデータを抽出することができます。オートフィルタの練習をするときも、与えられたリストと与えられた条件があって、思い通りのデータが取り出せたら終わり、といった練習をすると思います。練習用のデータは抽出しやすいようになっていて、テキストの通りに条件を入れたら簡単に抽出できるようになっているのです。

しかし、実際にExcelを使うときには、自分でリストを作り、条件も自分で考えなければなりません。そのときに、オートフィルタがうまく設定できなかったり、どのように活用したらよいのかわからないことがあります。「抽出できたら終わり」というワンパターンな練習にとどまらず、あらゆる状況を想定して練習をしなければなりません。

そこで、今回は、オートフィルタを使った検索とデータの点検、オートフィルタによる抽出がうまくできない理由と対処方法について出題します。

f:id:waenavi:20190803140908j:plain

 

 

目次

問題文のあとに簡単な操作方法を解説していますが、静止画では、わかりにくいと思いますので、最後に動画を載せています。ぜひご覧ください。

0.オートフィルタの基本トレーニング

まずは、オートフィルタの基本を練習しましょう。

 

1.オートフィルタで検索する

問題

縦に大量のデータが入力されている表がある。次のようなデータがあるかどうかを調べなさい(抽出しなくてもよい)。

  1. A列に、「カレー」を含む文字列が何種類あるか調べなさい。
  2. C列に、1~20の数値がすべて存在するか調べなさい。
  3. E列に、数値でないデータがあるかを調べなさい。
  4. G列に、エラーがあるかを調べなさい。

f:id:waenavi:20190803140241j:plain

 

解説

オートフィルタはデータを検索するときにも使えます。オートフィルタを設定すると、昇順で重複なく一覧が表示されます。

f:id:waenavi:20190803140832j:plain

 

特定の文字列を含むものが何種類あるかを知りたいときは、オートフィルタを設定します。検索ボックスで入力することで、検索してデータの有無を確認することができます。

f:id:waenavi:20190803140835j:plain

f:id:waenavi:20190803140839j:plain

 

3種類です。

f:id:waenavi:20190803140846j:plain

 

番号が飛んでいるかどうかを知りたいときも、オートフィルタを使います。

f:id:waenavi:20190803140849j:plain

 

昇順で表示されるので番号がそろっているかどうかが分かります。15がないです。

f:id:waenavi:20190803140852j:plain

 

数値の中に数値でないものがあるかどうかを探すことも可能です。

f:id:waenavi:20190803140858j:plain

 

「なし」と入力されているセルがあることが分かります。このように種類の異なるものを探すことができます。

f:id:waenavi:20190803140903j:plain

 

エラーがあるかどうかもオートフィルタでチェックをすることができます。

f:id:waenavi:20190803140906j:plain

 

エラーは一覧の一番下に表示されます。これがなければエラーがないということです。

f:id:waenavi:20190803140908j:plain

 

2.それぞれのシートに対して1つだけ設定できる

問題

オートフィルタを設定しようとしています。

f:id:waenavi:20190803142739j:plain

 

しかし、並べ替えとフィルターのボタンがグレーアウトで使えません。このようになる理由を述べなさい。

f:id:waenavi:20190803142742j:plain

 

解説

オートフィルタは1つのシート(それぞれのシート)に対して1つしか設定できません。

f:id:waenavi:20190803155158j:plain

 

シートを複数選択している状態で設定することはできません。

f:id:waenavi:20190803155202j:plain

 

また、1つのシートに2つ以上のオートフィルタを設定することもできません。1つのシートに2つ以上のリストを作ること自体が間違いです。

f:id:waenavi:20190803155205j:plain

 

並べ替えとフィルターのボタンがグレーアウトで使えないのは、作業グループ、つまり、シートを複数選択していることが原因です。

f:id:waenavi:20190803155213j:plain

 

作業グループについてはこちらの記事をご覧ください。

 

これを解除します。

f:id:waenavi:20190803155219j:plain

 

これでボタンが使えるようになります。

f:id:waenavi:20190803155223j:plain

 

3.自動で更新されない

問題

英語が90点以上の人を抽出します。

f:id:waenavi:20190803142839j:plain

f:id:waenavi:20190803142905j:plain

 

そのうち1人の点数を修正して、88点としました。しかし、90点以上ではなくなったのに、残っています。90点以上で抽出したのに、88点の人が残る理由を述べなさい。

f:id:waenavi:20190803142842j:plain

 

解説

88点に変えたとき、合計点は即時修正されます。これは、セルの値が変わったタイミングで、シートにある計算式が自動で再計算されるからです。しかし、フィルタはその条件を設定した時点で判断され、それ以降は値が変わってもフィルタが自動で再適用されることはありません。したがって、条件(90点以上)を設定した後で、点数を90点未満に変更しても非表示にはなりません。

f:id:waenavi:20190803155802j:plain

f:id:waenavi:20190803155759j:plain

 

条件を設定した後に、数値を変えた場合、もう一度条件を適用するには、手動で再適用をします。

f:id:waenavi:20190803155805j:plain

f:id:waenavi:20190803155815j:plain

 

4.オートフィルタの適用範囲

問題

さきほどの問題で、受験番号は、AまたはBまたはCから始まる番号です。しかし、受験番号で抽出しようとしたら、BやCがありません。このようになる理由を述べなさい。

f:id:waenavi:20190803143010j:plain

f:id:waenavi:20190803142945j:plain

 

解説

行が非表示になっています。

f:id:waenavi:20190803162923j:plain

 

再表示します。

f:id:waenavi:20190803162926j:plain

 

BとCが出てきません。

f:id:waenavi:20190803162930j:plain

 

行を削除します。

f:id:waenavi:20190803162941j:plain

 

今度は出てきました。

f:id:waenavi:20190803162949j:plain

 

オートフィルタは項目名に設定されます。エクセルは項目名より下を検索して、データがなくなった、つまり、行があいているところを、リストの最後と判断します。したがって、このように行があいていると、それより下は対象外になります。

f:id:waenavi:20190803162953j:plain

 

空白の行でなければよいので、どこかの列に連番を振るという方法もあります。

f:id:waenavi:20190803163002j:plain

 

連番も含めてオートフィルタを設定すれば、正しく動きます。

f:id:waenavi:20190803163006j:plain

 

このようにオートフィルタの適用範囲が明らかにおかしくなることがあります。

f:id:waenavi:20190803163009j:plain

 

オートフィルタの適用範囲が疑わしい時は、いったんオートフィルタを解除して表をすべて選択します。

f:id:waenavi:20190803163012j:plain

 

これで、オートフィルタを設定すれば、正しく認識されます。

f:id:waenavi:20190803163018j:plain

 

5.複数の列を比較して抽出することはできない

問題

この図は、A列とB列の点数を比較して、前回より点数が上がっている人を抽出しようとしている。この操作の誤りを指摘し、正しい操作方法を述べなさい。

f:id:waenavi:20190803143037j:plain

 

解説

前回より点数が増えたものを抽出しようとしています。

f:id:waenavi:20190803172342j:plain

 

しかし、今回の点数を、別の列と比較して抽出することはできません。

f:id:waenavi:20190803172345j:plain

f:id:waenavi:20190803172351j:plain

 

オートフィルタは別の列を参照して条件を設定することはできません。

f:id:waenavi:20190803172356j:plain

 

このような抽出をしたいときは、A列とB列を比較した結果を別の列に求めた後で、その列を使って条件を設定します。この場合、今回の点数から前回の点数を引いて、点数の変化を求めます。

f:id:waenavi:20190803172359j:plain

f:id:waenavi:20190803172402j:plain

 

いったんオートフィルタを解除して、表をすべて選択します。オートフィルタを設定します。

f:id:waenavi:20190803172406j:plain

 

0より大きいとして抽出すれば、点数が上がった人を抽出できます。

f:id:waenavi:20190803172412j:plain

f:id:waenavi:20190803172415j:plain

 

6.オートフィルタとコピー貼り付け

オートフィルタモードとコピー貼り付けの関係については別の記事で解説しています。

 


解説は以上です。


7.動画版はこちら(無料)

この記事は、わえなび実力養成講座「ファンダメンタルExcel」Program 7-16-2、7-17のYoutube動画を書き起こして、加筆修正したものです。

 

 


Copyright(C)2018-2019 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]