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

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

【Excel】オートフィルタの設定と解除、条件にあう行を抽出する方法の総復習

Excelのたくさんのデータから条件に合う行を抽出する方法として「オートフィルタ」があります。

オートフィルタを知らない人がExcelを使うと、統一感のないバラバラなデータを入力しがちです。これではいくら高性能なフィルタであっても抽出ができません。オートフィルタを学習するときには、抽出の方法よりも「抽出しやすい表の作り方」を意識しながら学ぶべきです。オートフィルタの使い方を学ぶことによって、あらためてリスト形式で入力することの重要性を理解することができると思います。

今回は、オートフィルタの設定と解除、さまざまなデータの絞り方について出題します。

f:id:waenavi:20190729185739j:plain

 

「Excelオートフィルタ」(この記事は第1回です)
  1. オートフィルタの設定と解除、条件にあう行を抽出する方法の総復習
  2. オートフィルタをさらに深く理解するための応用事例演習
  3. オートフィルタモードの効果とコピー貼り付けとの関係
  4. 並べ替えは並べ替えるだけ、オートフィルタは行数を減らすだけです

 

目次

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

0.リスト形式

オートフィルタが使えるためには、リスト形式の表を入力しなければなりません。詳しくは次の記事をご覧ください。

 

1.オートフィルタの設定と解除

問題

次のリストについてオートフィルタを設定しなさい。また、オートフィルタを解除しなさい。これを繰り返し練習しなさい。

f:id:waenavi:20190729184657j:plain

 

解説

1行目を選択します。

f:id:waenavi:20190729185727j:plain

 

フィルタを設定します。

f:id:waenavi:20190729185729j:plain

 

項目名に三角マークがつきます。

f:id:waenavi:20190729185733j:plain

 

これを「オートフィルタAuto Filter)」といいます。オートフィルタは項目名に設定します。項目名を範囲選択してから設定するのが無難です。

f:id:waenavi:20190729185737j:plain

 

ホームタブにあるフィルタと、データタブにあるフィルタは同じです。

f:id:waenavi:20190729185739j:plain

 

もう一度同じ操作をします。

f:id:waenavi:20190729185742j:plain

 

これでフィルタの解除になります。解除するときは、範囲選択は不要です。

f:id:waenavi:20190729185746j:plain

 

オートフィルタは画面上、項目名に設定されているように見えますが、このシート自体がオートフィルタモードになっています。

f:id:waenavi:20190729185748j:plain

 

1つのシートにつき、1種類のオートフィルタしか設定できません。

f:id:waenavi:20190729185752j:plain

 

変なところに設定してしまった場合には、フィルタモードをいったん解除するため、フィルタを取り消します。

f:id:waenavi:20190729190330j:plain

 

そして、再度やり直します。正しく設定できるように何回も繰り返し練習します。

f:id:waenavi:20190729190335j:plain

 

2.抽出とフィルタのクリア

問題

次のリストについてオートフィルタを設定して左打者だけ抽出しなさい。また、打席に設定されているフィルタをクリアしなさい。

f:id:waenavi:20190729184657j:plain

 

解説

オートフィルタを設定します。

f:id:waenavi:20190729191207j:plain

 

右のチェックを外します。

f:id:waenavi:20190729191209j:plain

f:id:waenavi:20190729191212j:plain

 

これで左だけになります。

f:id:waenavi:20190729191214j:plain

 

このとき行番号が青くなって、途中の行が非表示になっています。

f:id:waenavi:20190729191217j:plain

 

画面の左下に、「12レコード中7個が見つかりました」と表示されます。左打者は7人です。

f:id:waenavi:20190729191221j:plain

 

該当するものだけ表示する操作を「抽出」といいます。抽出されなかった行は非表示になります。この時に設定した抽出条件を、「フィルタ」といいます。

f:id:waenavi:20190729191225j:plain

 

打席に設定されているフィルタをクリアします。

f:id:waenavi:20190729191229j:plain

f:id:waenavi:20190729191232j:plain

 

リストがすべて表示されます。

f:id:waenavi:20190729191235j:plain

 

設定されている抽出条件をなくすことを「フィルタのクリア」といいます。フィルタをクリアすると抽出条件がなくなるので、条件を解除したことになります。オートフィルタの解除とは違います。

f:id:waenavi:20190729191237j:plain

 

3.検索ボックス

問題

次のリストについて「くろまて」「こまだ」「まつもと」「やまくら」の4人を抽出しなさい。また、氏名に設定されているフィルタをクリアしなさい。

f:id:waenavi:20190729184657j:plain

 

解説

選手名ですべて選択のチェックを外します。

f:id:waenavi:20190729192442j:plain

f:id:waenavi:20190729192445j:plain

 

「くろまて」「こまだ」「まつもと」「やまくら」を選択します。

f:id:waenavi:20190729192450j:plain

 

4人になりました。

f:id:waenavi:20190729192453j:plain

 

別解

ところで、検索ボックスに「ま」と入力します。選手名の中に「ま」という文字を含む4人が表示されます。このように対象を絞ることもできます。

f:id:waenavi:20190729192458j:plain

 

選手名からフィルタをクリアします。

f:id:waenavi:20190729192502j:plain

 

選手は全員表示されますが、オートフィルタはそのままです。

f:id:waenavi:20190729192505j:plain

 

4.数値フィルタ(以上・以下・大きい・小さい)

問題

次のリストについて、次の条件でそれぞれ抽出しなさい。

  • 打率0.270以上
  • 打率0.310を超えている
  • 打率3割未満

f:id:waenavi:20190729184657j:plain

 

解説

打率が2割7分以上の選手だけ抽出します。

f:id:waenavi:20190729193417j:plain

 

打率の数値フィルタの中から、「指定の値以上」を選びます。

f:id:waenavi:20190729193420j:plain

 

0.27と入力します。

f:id:waenavi:20190729193423j:plain

 

これで完成です。0.270の人も含まれます。

f:id:waenavi:20190729193427j:plain

 

数値フィルタは、等しい、等しくない、大きい、以上、小さい、以下の中から選びます。

f:id:waenavi:20190729193430j:plain

 

原則として「以」という文字を使うものはその数を含みます。

f:id:waenavi:20190729193433j:plain

 

「以」という文字を使わないものはその数を含みません。

f:id:waenavi:20190729193436j:plain

 

0.310を超えているということは、0.310を含みません。

f:id:waenavi:20190729193438j:plain

 

そして、大きいを選びます。

f:id:waenavi:20190729193442j:plain

 

0.310と入力します。

f:id:waenavi:20190729193445j:plain

 

0.310は含まれません。

f:id:waenavi:20190729193448j:plain

 

3割未満も3割ちょうどを含みません。したがって、小さいを選びます。

f:id:waenavi:20190729193453j:plain

 

0.3と入力します。

f:id:waenavi:20190729193456j:plain

 

これで完成です。

f:id:waenavi:20190729193459j:plain

 

5.該当する列がなければ抽出できない

問題

次のリストについて、英数合計180点以上を抽出しなさい。

f:id:waenavi:20190729193956j:plain

 

解説

抽出するにはオートフィルタを使います。「合計180点以上」を抽出するためには合計を求めなければなりません。抽出の条件となる列が無ければ作らなければなりません。

f:id:waenavi:20190729194714j:plain

 

オートフィルタを設定します。

f:id:waenavi:20190729194717j:plain

 

合計180点以上で抽出します。

f:id:waenavi:20190729194720j:plain

f:id:waenavi:20190729194722j:plain

 

これで完成です。

f:id:waenavi:20190729194726j:plain

 

6.トップテンオートフィルタ

問題

次のリストについて、英数合計の低いほうから5人抽出しなさい。また高いほうから5人抽出しなさい。

f:id:waenavi:20190729193956j:plain

 

解説

点数の上位または下位から抽出するにはトップテンオートフィルタを使います。

f:id:waenavi:20190729221455j:plain

 

下位5項目とします。

f:id:waenavi:20190729221459j:plain

 

これで点数の低いほうから5人が抽出されました。ただし、フィルタは抽出をするだけで、並べ替えをするわけではありませんので、合計点の順番に並ぶわけではありません。元の順番を保ったまま5人が抽出されます。

f:id:waenavi:20190729221502j:plain

 

次にこれを上位5項目にします。

f:id:waenavi:20190729221505j:plain

f:id:waenavi:20190729221508j:plain

 

これで点数の高いほうから5人が抽出されますが、6人になっています。第5位が同点で複数いる場合は、このように6人以上抽出されることがあります。

f:id:waenavi:20190729221516j:plain

 

トップテンオートフィルタで項目数を指定したからと言って、かならずその通りの結果になるとは限りません。

f:id:waenavi:20190729221519j:plain

 

7.ワイルドカード

問題

次のリストについて、受験番号の2桁目が2になっているものを抽出しなさい。

f:id:waenavi:20190729194016j:plain

 

解説

受験番号を条件とします。そこで、合計点に設定されているフィルタをクリアします。

f:id:waenavi:20190729222450j:plain

f:id:waenavi:20190729222454j:plain

f:id:waenavi:20190729222501j:plain

 

同じ列に対して、連続してフィルタをかけるにはクリアをする必要はありません。

f:id:waenavi:20190729222508j:plain

 

しかし、フィルタをかけている状態で、それとは異なる列でフィルタをかけようとする場合、原則として、フィルタをクリアします。

f:id:waenavi:20190729222516j:plain

 

受験番号の1文字目は何でもよく、2文字目が2になっている物を抽出します。「始まる」を選びます。

f:id:waenavi:20190729222539j:plain

f:id:waenavi:20190729222526j:plain

 

この画面に、「?を使って、任意の1文字を表すことができます。」と書いてあります。これを利用します。

f:id:waenavi:20190729222533j:plain

 

?2」と入力します。1文字目は何でもよく、2文字目が2で始まるという意味になります。

f:id:waenavi:20190729222542j:plain

 

これで完成です。

f:id:waenavi:20190729222547j:plain

 

このように、文字を指定せず、何らかの文字を表す記号を「ワイルドカード」といいます。画面の説明のとおり、?は何らかの文字を表しています。ただし、必ず1文字です。

f:id:waenavi:20190729222553j:plain

 

これに対して、アスタリスク(*)は文字数を問いません。何文字でも構いません。これを利用すると次のような別解が考えられます。

f:id:waenavi:20190729222602j:plain

 

別解

「等しい」を選びます。

f:id:waenavi:20190729222647j:plain

 

?2*」と入力します。

f:id:waenavi:20190729222655j:plain

 

1文字目は何でもよく、2文字目が2、そして、3文字目以降はどのような文字が続いてもよいという意味になります。したがって、2文字目が2であることを表します。

f:id:waenavi:20190729222701j:plain

 

8.成績が良いほうが上位とは限らない

問題

この表は、水泳100メートル自由形のレース結果である。オートフィルタを設定して、上位3着までの選手を抽出しなさい。

f:id:waenavi:20190729225612j:plain

 

解説

このレースは、52秒70の選手2名が同タイムで優勝です。

f:id:waenavi:20190729230137j:plain

 

オートフィルタを設定します。

f:id:waenavi:20190729230141j:plain

 

トップテンオートフィルタです。

f:id:waenavi:20190729230144j:plain

 

上位3項目で抽出します。

f:id:waenavi:20190729230147j:plain

 

優勝した選手がいません。これは間違いです。

f:id:waenavi:20190729230155j:plain

 

トップテンオートフィルタの上位とは「数の大きいほう」という意味です。

f:id:waenavi:20190729230204j:plain

 

記録が上位かどうかは関係ありません。特に、水泳や陸上のように時間を競う競技や、ゴルフのようにミスの少なさを競う競技は、数の少ないほうが上位です。

f:id:waenavi:20190729230209j:plain

 

したがって、トップテンオートフィルタでは下位になります。トップテンオートフィルタで下位3項目とします。

f:id:waenavi:20190729230221j:plain

 

これで記録の上位選手3人が抽出されます。

f:id:waenavi:20190729230226j:plain

 

9.OR

問題

次のリストについて、受験番号がAまたはBで始まるものを抽出しなさい。

f:id:waenavi:20190729231003j:plain

 

解説

受験番号がAかBで始まるものを抽出します。

f:id:waenavi:20190730005612j:plain

 

テキストフィルタで「始まる」を選びます。

f:id:waenavi:20190730005615j:plain

 

Aを入力します。Bを入力して、Bで始まるとします。

f:id:waenavi:20190730005619j:plain

 

オートフィルタオプションには、ANDとORがあります。ANDは両方ともあてはまる、つまり、Aで始まり、しかも、Bで始まるものとなります。該当するものはありません。

f:id:waenavi:20190730005623j:plain

 

ORはどちらか一方に当てはまる、つまり、AまたはBで始まるものとなります。

f:id:waenavi:20190730005626j:plain

 

ORにします。

f:id:waenavi:20190730005629j:plain

 

これで完成です。

f:id:waenavi:20190730005634j:plain

 

10.AND

問題

次のリストについて、合計が240点以上250点未満のものを抽出しなさい。

f:id:waenavi:20190729231003j:plain

 

解説

受験番号に設定されているフィルタをクリアします。

f:id:waenavi:20190730011406j:plain

 

240点以上250点未満ということは「240点以上で、しかも、250点より小さい」ということです。

f:id:waenavi:20190730010828j:plain

 

「以上」を選びます。

f:id:waenavi:20190730010832j:plain

 

240以上とします。ANDにします。250より小さいとします。

f:id:waenavi:20190730010836j:plain

 

これで完成です。1人だけです。

f:id:waenavi:20190730010839j:plain

 

11.複数列条件

問題

次のリストについて次の操作をしなさい。

  1. 3教科すべて70点以上の人を抽出しなさい。
  2. 英語のみフィルタをクリアしなさい。
  3. すべてのフィルタをクリアしなさい。

f:id:waenavi:20190729231003j:plain

 

解説

合計点に設定されているフィルタをクリアします。

f:id:waenavi:20190730012550j:plain

 

3教科すべて70点だったということは、英語、数学、理科で、それぞれ70点以上を抽出すればよいです。

f:id:waenavi:20190730012558j:plain

f:id:waenavi:20190730012603j:plain

 

複数の列に抽出条件を設定すると、すべてに当てはまるものだけが抽出されます。つまり、ANDです。

f:id:waenavi:20190730012609j:plain

 

英語、数学、理科にフィルタがかかっている状態です。

f:id:waenavi:20190730012613j:plain

 

このうち、英語のフィルタをクリアします。

f:id:waenavi:20190730012615j:plain

 

英語が55点の人が表示されますが、数学と理科の70点以上のフィルタはそのまま有効です。

f:id:waenavi:20190730012618j:plain

 

条件が設定されている列にはフィルタのアイコンが表示されています。

f:id:waenavi:20190730012618j:plain

 

英語のフィルタを外すとそのアイコン が消えます。

f:id:waenavi:20190730012626j:plain

 

それぞれの列でフィルタをクリアすることもできますが、一括でクリアする方法もあります。

f:id:waenavi:20190730012630j:plain

 

これですべてクリアとなります。

f:id:waenavi:20190730012635j:plain

 

12.データが消えた?

問題

オートフィルタを操作したら次の図のようになった。データを表示させるにはどのような操作をすればよいか。

f:id:waenavi:20190730023053j:plain

 

解説

オートフィルタを設定したときにデータがなくなった場合、まずは、ウィンドウ枠の固定を疑います。

f:id:waenavi:20190730030304j:plain

 

抽出された行が見えなくなっただけの可能性があります。

f:id:waenavi:20190730030307j:plain

 

次に、どの列に条件が設定されているかを見ます。

f:id:waenavi:20190730030310j:plain

 

この場合は数学の条件をクリアするか、すべての条件をクリアすることで、すべてのデータが表示されます。

f:id:waenavi:20190730030312j:plain

f:id:waenavi:20190730030316j:plain

f:id:waenavi:20190730030319j:plain

 

13.フラグを用いた抽出

問題

次の表で、3教科のうち1つでも95点以上をとった人を抽出しなさい。

f:id:waenavi:20190730023223j:plain

 

解説

理科、数学、英語の順に95点以上の条件を付けると、3教科とも95点以上の人が抽出されます。

f:id:waenavi:20190730035042j:plain

f:id:waenavi:20190730035044j:plain

 

該当者なしです。

f:id:waenavi:20190730035047j:plain

 

残念ながら、オートフィルタは手軽に抽出ができる反面、複雑な抽出をすることができません。

f:id:waenavi:20190730035049j:plain

 

3教科のうち1つでも95点を取った人を抽出するということは、このとき、抽出されなかった人は、3教科とも95点をとれていません。

f:id:waenavi:20190730035052j:plain

 

そこで、3教科とも95点未満の人を抽出します。

f:id:waenavi:20190730035056j:plain

f:id:waenavi:20190730035058j:plain

 

7人です。この人たちにマークを付けます。

f:id:waenavi:20190730035102j:plain

 

いったんオートフィルタを解除します。

f:id:waenavi:20190730035104j:plain

 

もう一度オートフィルタを設定しなおします。

f:id:waenavi:20190730035107j:plain

 

マークのついていない人を抽出します。

f:id:waenavi:20190730035109j:plain

 

これで完成です。

f:id:waenavi:20190730035112j:plain

 

別解

95点をとれたかどうかは比較演算子を使って計算することもできます。英語が95以上、数学が95以上、理科が95以上の式をすべて足します。

f:id:waenavi:20190730035543j:plain

f:id:waenavi:20190730035546j:plain

 

0以外を抽出して完成です。

f:id:waenavi:20190730035549j:plain

 

このように複雑な条件の場合は、フラグを立ててから抽出します。

f:id:waenavi:20190730035553j:plain

 


解説は以上です。


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

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

 

「Excelオートフィルタ」(この記事は第1回です)
  1. オートフィルタの設定と解除、条件にあう行を抽出する方法の総復習
  2. オートフィルタをさらに深く理解するための応用事例演習
  3. オートフィルタモードの効果とコピー貼り付けとの関係
  4. 並べ替えは並べ替えるだけ、オートフィルタは行数を減らすだけです

 


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