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

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

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

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

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

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

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

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

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

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

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

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

そう、このボタンです。

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

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

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

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

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

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

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

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

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

このボタンです。

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

①リスト範囲

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

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

②検索条件範囲

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

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

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

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

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

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

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

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

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

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

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

絞り込み結果

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

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

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

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

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

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

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

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

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

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

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

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

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

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

正解はこちらです。

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

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