Excelのフィルター機能で、今までできなかった「OR」条件でのフィルターを実行する[フィルターの詳細設定]

詳細条件フィルターでタイムカードのデータを抽出 Excel(エクセル)
Excel(エクセル)
スポンサーリンク/Sponsored Link

今日は、ある生徒さんのご相談をもとにした記事です。

この方の職場では、直行直帰で勤務した日はタイムカードの打刻がないため、後から本人に確認して勤怠のデータを追加しているそうです。加えて、打刻忘れの確認もしなければならないので、月末になるとタイムカードのデータとにらめっこして、漏れているところを探しているのだそうです。

そこで、これをなんとかExcelで、「サッッ・・・(*’▽’) 」とやってしまえないかなぁ???

というのが、ご相談の内容です。

簡単そうにみえますが、意外と、「えっ・・・・それ、できないんだっけ???」と思うような、Excelのフィルター機能の盲点をついたようなご相談でしたので、以下その方法をまとめてみます。

スポンサーリンク/Sponsored Link

まずは必要な処理の内容を確認。

職場のタイムカードシステムから、打刻データがエクセル(csv形式)で出力されているとします。例えばこんなぐあいです。

タイムカードの生データです

何か所か、退勤打刻だけ漏れていたり、連続して打刻がない日がありますね。

タイムカードの空欄

このように、出勤・退勤のいずれかでも空欄になっているレコードを、もれなく抽出する、というのが、今回やろうとする処理です。

スポンサーリンク/Sponsored Link

フィルター機能でササッとできるか、と思いきや・・・

たくさんあるデータから、特定の条件で抽出するのは、Excelでは「フィルター機能」を使いますね。

フィルターのボタンそう、このボタンです。

さっそくやってみましょう。

普通のフィルター機能では、「出退勤両方が空白」しか抽出できない

まず「出勤打刻が空白」のセルを抽出します。

出勤打刻の空白でフィルタ

つづいて、「退勤打刻が空白のセル」を抽出します。

退勤打刻の空白をフィルタ

結果がこれです。さあどうでしょう?? 目的は達せられたでしょうか?

フィルター結果

さきほどあった、退勤だけが空白になっているレコードが抽出できていないことが分かります。

このように、通常よく使う方法のフィルター機能では、複数の条件を「全て満たす」レコードしか抽出できないのです。(つまり「AND」条件)

スポンサーリンク/Sponsored Link

「フィルターの詳細設定」を使う

ではどうするかというと、「フィルターの詳細設定」を使います。

フィルターの詳細設定ボタンこのボタンです。

これをクリックすると、次のようなダイアログが出ます。見慣れない画面ですが、これをきちんと理解すれば、かなり複雑なフィルターがかけられるようになりますので、じっくり見てみましょう。

フィルターオプション

①リスト範囲

ここには、フィルターで絞り込みたいセル範囲を入力するのですが、データの中のセルを選択してさえあれば、Excelが自動で表全体の範囲を入れてくれます。Excelが範囲を間違えたときだけ、手動で選択すればOKです。

この項目は、普通のフィルターと同じですので、分かりやすいと思います。

②検索条件範囲

問題はこちらです。上の画像では、何やら範囲指定が入っていますが、これは別シートに次のように作成した範囲が入っています。

検索条件範囲

暗号のようですが、これはつまり、複数の検索条件をExcelのシート上で表現し、フィルターに入力するためのデータなのです。

検索条件範囲の書き方

最初の行は、必ず元の表と同じ項目名をコピーします。全部でなくてもいいのですが、必要な部分については元の表と正確に同じである必要があります。

二行目以降は、検索条件を書いていくのですが、

  • 同じ行の中は「同時に満たす(AND)」
  • 行どうしは「どれかを満たす(OR)」

というルールで書いていきます。

また、今回は空白セルを検出するため、少し変わった書き方になっています。

  • 50より大きい、という場合→ 「>50」
  • 50より小さい、という場合→ 「<50」
  • 50以上、という場合→ 「>=50」
  • 50に等しい、という場合→ 「50」  (等しい、という条件の場合、”=”の記号は不要です)
  • 50ではない、という場合→ 「<>50」
  • 文字列「提出済み」と一致、という場合→ 「=”提出済み”」

といったルールなのですが、ここでは空白に一致させるため、いまの例の「50」とか「”提出済み”」の部分が、何もない(空白)になっているのです。

この検索条件範囲を適切に書くことで、かなり複雑な条件で絞り込むことが出来ます。

※実は、この説明だけでは設定できない条件があります。詳しくは、この記事のさいごの 「余談」 をご覧ください。

スポンサーリンク/Sponsored Link

絞り込み結果

新しい絞り込み結果

片方だけ空欄の日も、両方空欄の日も、もれなく抽出されています。

スポンサーリンク/Sponsored Link

できなかった条件のフィルターも、これでできますね!

設定方法がややこしいため、意外と知られていない、この「詳細条件フィルター」。使いこなせば、今までできなかった条件のフィルターも実行することができます。今まで、何度もフィルターボタンを押して、条件を変えながら見るしかなかったシートも、いっぱつで目的の条件がすべて適用できます。

この例では、分かりやすいようにcsvに出力されている項目をだいぶ省略しています。実際には、もっとたくさんの列があるのではないかと思います。

ぜひ、この方法をご自分の実際に使っているファイルに置き換えて、やってみてください!!

スポンサーリンク/Sponsored Link

余談・同一項目で複数の条件を同時に満たすようなフィルター

では、次のような条件は、どうやって設定したらいいでしょうか???

「出勤打刻が、8:40~8:50の間にあって、外出打刻がない日を抽出」

同じ「出勤打刻」の項目に、「8:40以降」と「8:50以前」の、二つの条件を設定しないといけません。

「8:40以降」と「8:50以前」は、「同時に満たす」でなければなりません。「いずれかを満たす」では、ダメなんです。さきほどの例に照らすと、

検索条件範囲の書き方

と、いうルールですので、

同一の項目で2つの条件

この「出勤打刻」の下の1つのセルに、2つの条件を書かないといけない状況なんです。

少しExcelができる方なら、「AND関数を使えばいいんじゃ・・・」と思われるかもしれませんが、この「詳細条件フィルター」機能の条件範囲ではAND関数が使えないんです。(通常のセル内での演算とは違う記述ルールが適用されている)

さあ、どうしたらできるでしょう!?!?!?!?

正解はこちらです。

同一の項目名を複数並べます

絞り込み結果がこちらです

絞り込み結果

いかがですか? これで、設定できない条件はなくなったと思います。

 

パソコン教室・キュリオステーション志木店からのお知らせ
レッスンはオンラインで受講できます

パソコン教室・キュリオステーション志木店では、本年よりオンラインでの在宅レッスンを実施しております。
教室の全コースがオンラインで受講可能。実際にインストラクターがご対応いたします。
1時間の無料体験レッスンはいつでも予約できます。詳しくは公式ページをご覧ください。

スポンサーリンク/Sponsored Link
キュリオステーション志木店運営をフォローする
志木駅前のパソコン教室・キュリオステーション志木店のブログ

コメント

  1. bom より:

    ご教授お願いたい致します。

    vlookup関数やindex&match関数で、参照先の範囲や求める列のところがフィルター設定されています。

    例えば=VLOOKUP(A1,Sheet1!$A$2:$B$100,2,FALSE)などで$A$2:$B$100の2列目がフィルターになっている場合です。
    求めたいものが、フィルターにはいっていて、その中から選べるようになってます。

    ただ、なぜか、フィルター設定になっていて、すべてに選択がされていても
    参照先の列には、なにも表示されていません。

    vlookup関数でちゃんと2列目から参照したい値はとってくるのですが、
    なんで、2列がすべて表示されていないのかがわかりません。

    ご回答お願い致します。

    • コメントありがとうございます、なるほど、VLOOKUPなどの「検索範囲」に一体何が入っているのが、全貌をワークシート上でどうやっても見られない状態、という理解であってますでしょうか。
      原因はいただいたお話しだけでははっきり分かりませんが
      当店の経験上、例えばこんなことがありました。 フィルターをかけたまま運用していて、ふと、フィルターを解除してすべてを見たいと思ったのに、解除しても一部どうしても見えない。非表示になっているわけでもなく、摩訶不思議な状態になっていました。
      結局真相はよくわからないのですが、どうもExcelの不具合であったようでした。
      最終的にどうやって見たかというと、マクロを組みました。
      Sub lineheight()
      Dim i as Integer
      for i=1 to 3000 ‘ 必要な行数分
      Activesheet.Rows(i).RowHeight = 18
      Next i
      End Sub
      こうやって、強制的にマクロで行の高さを設定すると、やっと見ることができた事例がありました。
      なぜか、範囲指定して行の高さを変更しても変わらず、こうやって一行一行操作すると変えられたのですから、これは不可思議としか言いようがありません。何だったのかいまだに分かりませんが、もしかしたらこの方法が最後は使えるかもです…

  2. おとうさん より:

    頭が悪いのでさっぱりわからない…
    行の状態を表すカラムを追加してそのカラムでフィルタしています…

    • 作業列を追加できる環境では、それが最も簡単だと思います。
      どうしても作業列が作れない場合に、この記事の通りに一回やってみて下さい。
      フィルターをかける際に、フィルター機能のダイアログボックス内にさまざまな条件を入力しますが、複数フィールドの条件一括指定はどうしてもできないのです。
      それを、なんと「別の場所のセル範囲に入力して、その範囲を与えることで条件指定とする」という方法が存在するということです。

タイトルとURLをコピーしました