Excelで高度なフィルターを使用する方法 - 式を使用した基準範囲の例

チュートリアルでは、ExcelでAdvanced Filterを使用する方法を示し、ケースに敏感なフィルターを作成し、2つの列間の一致と違いを見つけ、より小さなリストに一致するレコードを抽出するなど、多くの非自明の基準範囲の例を提供します。

以前の記事では、Excel Advanced Filterのさまざまな側面と、それを使用して行をフィルタリングする方法とロジックについて説明しました。基本がわかったので、あなたの仕事に役立つ可能性のあるより複雑な基準範囲の例を見てみましょう。

• フォーミュラベースの基準範囲を設定する方法
• 高度なフィルター基準の範囲の例
  • テキスト値のケース感受性フィルター
  • 列の平均以上を超える値をフィルタリングします
  • ブランク /非ブランクセルで行をフィルターします
  • 上 /下のnレコードを抽出します
  • フィルターの一致と2つの列間の違い
  • リスト内のアイテムと一致するか、一致しない行をフィルタリングする
  • 週末と平日をフィルタリングします

フォーミュラベースの基準範囲を設定します

このチュートリアルで説明した例の範囲のほとんどには、さまざまな式が含まれるため、それらを適切に設定するための重要なルールを定義することから始めましょう。私を信じてください、この小さな理論はあなたに多くの時間を節約し、式に基づいた複数の条件を含む複雑な基準範囲のトラブルシューティングの頭痛を免れます。

• 基準範囲で使用する式は、 trueまたはfalseに評価する必要があります。
• 基準範囲には、式セルとヘッダーセルの少なくとも2つのセルを含める必要があります。
• フォーミュラベースの基準のヘッダーセルは、テーブル（リスト範囲）の見出しとは空白または異なるものでなければなりません。
• リスト範囲内の各行の式を評価するために、A1のような相対的な参照を使用してデータを使用して最上位セルを参照してください。
• 式が特定の細胞または細胞の範囲に対してのみ評価される場合は、$ a $ 1などの絶対参照を使用してその細胞または範囲を参照してください。
• フォーミュラのリスト範囲を参照する場合、常に絶対セル参照を使用してください。
• 複数の条件を供給するときは、同じ行にすべての基準を入力して、それらをオペレーターと結合し、各基準を別の行に配置してORオペレーターと結合します。

Excel Advanced Filter Criteria範囲の例

次の例では、通常のExcelオートフィルターを使用して実行できない、より複雑なタスクを処理するために、Excelで独自のフィルターを作成する方法を教えてください。

テキスト値のケース感受性フィルター

Excel Autofilterだけでなく、高度なフィルターツールは本質的にはケース非感受性です。つまり、テキスト値をフィルタリングするときに大文字と小文字を区別しないことを意味します。ただし、高度なフィルター基準で正確な関数を使用して、ケースに敏感な検索を簡単に実行できます。

たとえば、バナナとバナナを無視してバナナを含む行をフィルタリングするには、基準の範囲に次の式を入力します。

=EXACT(B5, "Banana")

ここで、Bはアイテム名を含む列、行5が最初のデータ行です。

次に、 [データ]タブの[アドバンスト]ボタンをクリックしてExcel Advanced Filterを適用し、以下のスクリーンショットに示すようなリスト範囲と基準範囲を構成します。基準の範囲には2つのセルのヘッダーセルと式セルが含まれていることに注意してください。

注記。上記の画像と、このチュートリアルのすべてのさらなるスクリーンショットは、明確にするためだけにセルの範囲の式の式を示しています。実際のワークシートでは、フォーミュラセルは、データの最初の行が基準と一致するかどうかに応じて、真または偽のいずれかを返す必要があります。

列の平均以上を超える値をフィルタリングします

数値をフィルタリングする場合、列の平均値の上または下にあるセルのみを表示することをお勧めします。例えば：

平均を上回る亜集合で行をフィルタリングするには、基準の範囲で次の式を使用します。

=F5>AVERAGE($F$5:$F$50)

平均以下の亜一度の行で行をフィルタリングするには、次の式を使用します。

=F5<average></average>

相対的な参照を使用して、データを使用したトップセルを参照し（F5）、および列の見出し（$ 5：$ f $ 50）を除く平均を計算する範囲全体を定義する絶対的な参照を定義することに注意してください。

次のスクリーンショットでは、上記の平均式が実証されています。

Excel番号フィルターに精通している人は、なぜ誰かが高度なフィルターを使用することを気にするのではないかと疑問に思うかもしれません。そうですが、組み込みのExcelフィルターは、またはロジックでは使用できません！

したがって、この例をさらに進めるために、サブトタール（列F）または9月の販売（列E）が平均を上回る行をフィルタリングしましょう。このために、別の行に各条件を入力して、またはロジックで基準範囲を設定します。その結果、上記の平均値が列EまたはFのいずれかのアイテムのリストを取得します。

ブランクまたは非ブランクで行をフィルタリングします

誰もが知っているように、Excelフィルターには空白のセルをフィルタリングするための組み込みオプションがあります。 Autofilterメニューの（ブランク）チェックボックスを選択または解決することにより、1つ以上の列に空のセルまたは非空のセルがある行のみを表示できます。問題は、ブランク用の組み込みのExcelフィルターが、ロジックでのみ動作できることです。

OR Logicで空白または非ブランクセルをフィルタリングする場合、または他の基準と一緒に空白 /非ブランク条件を使用する場合は、次の式のいずれかを使用して高度なフィルター基準範囲を設定します。

ブランクをフィルター：

top_cell = ""

非ブランクをフィルター：

top_cell ""

ORロジックで空白のセルをフィルタリングします

列aまたはbまたは両方の列のいずれかに空白のセルを持つ行をフィルタリングするには、このようにして高度なフィルター基準範囲を構成します。

• =A6=""
• =B6=""

ここで、6はデータの最上位の行です。

wellsまたはwells and logicを使用して、またはwellsを使用して非ブランクセルをフィルタリングする

ExcelのAdvanced Filterが複数の基準でどのように機能するかをさらに理解するために、次の条件でサンプルテーブルの行をフィルタリングしましょう。

• 領域（列a）またはアイテム（列b）のいずれかは、非刃物でなければならず、
• サブトタール（列C）は900を超える必要があります。

違う言い方をすれば、次の条件を満たす行を表示する必要があります。

（ subtotal > 900およびregion = nonblank）または（ subtotal > 900およびitem = nonblank）

すでにご存知のように、Excel Advanced Filter Criteriaの範囲では、ANDロジックと結合された条件を同じ行に入力する必要があります。

この例の1つの基準は式（非blank）で表され、もう1つは比較演算子（亜集合> 900）を含むため、次のことを思い出させてください。

• 比較演算子で形成された基準には、上記のスクリーンショットの亜集合体の基準のように、テーブルの見出しとまったく同じ見出しが必要です。
• フォーミュラベースの基準には、上記のスクリーンショットの非黒い基準のように、空白の見出しセルまたはテーブルの見出しと一致しない見出しのいずれかが必要です。

トップ/ボトムNレコードを抽出する方法

おそらくご存知のように、ビルドインExcel番号フィルターには、上位10個または下部10アイテムを表示するオプションがあります。しかし、上位3値または下部5の値をフィルタリングする必要がある場合はどうなりますか？この場合、次の式を備えたExcel Advanced Filterが役立ちます。

上部Nアイテムを抽出します：

top_cell > = large（ range 、n）

bottom nアイテムを抽出します：

top_cell range 、n）

たとえば、上位3つのサブータルをフィルタリングするには、この式で基準の範囲を作成します。

=F5>=LARGE($F$5:$F$50,3)

下の3つのサブトータルを抽出するには、この式を使用します。

=F5>=SMALL($F$5:$F$50,3)

ここで、 F5は最上位セルであり、小計列にデータがあります（列の見出しを除く）。

次のスクリーンショットは、活動中のトップ3の式を示しています。

注記。リスト範囲に、上/下のNリストに該当する同じ値のいくつかの行が含まれている場合、以下のスクリーンショットに示すように、そのようなすべての行が表示されます。

2つの列間の一致と違いのフィルター

以前の記事の1つは、Excelの2つの列を比較し、それらの間のマッチと違いを見つけるさまざまな方法を説明しました。 Excelフォーミュラ、条件付きフォーマットルール、および上記のチュートリアルでカバーされている重複リムーバーツールに加えて、Excelの高度なフィルターを使用して、2つ以上の列に同じまたは異なる値を持つ行を抽出することもできます。これを行うには、基準範囲に次の単純な式のいずれかを入力します。

• 2列の一致（複製）のフィルター： =B5=C5
• 2つの列の差（一意の値）をフィルタリング： =B5C5

ここで、B5とC5は、比較する2つの列にデータを持つ最上位セルです。

注記。高度なフィルターツールは、同じ行の一致と違いのみを検索できます。列Aにあるが列Bのどこにもないすべての値を見つけるには、この式を使用します。

リスト内の一致アイテムに基づいて行をフィルターします

数百または数千の列のある大きなテーブルがあり、特定の瞬間に関連するアイテムのみを含むより短いリストを受け取ったとします。問題は、テーブル内のすべてのエントリをどのようにして、小さなリストに載っているか、そうでないかということです。

リスト内のアイテムに一致する行をフィルターします

次のCountif式を使用して、より小さなリストにも存在するソーステーブル内のすべてのアイテムを見つけるには。

countif（ list_to_match 、 top_data_cell）

小さいリストが範囲D2：D7にあり、そのリストと比較されるテーブルのアイテムが行10から始まる列Bにあると仮定すると、式は次のようになります（絶対的および相対的な参照の使用に注意してください）。

=COUNTIF($D$2:$D$7,B10)

もちろん、1つの基準でテーブルをフィルタリングすることに限定されません。

たとえば、リストに一致する行をフィルタリングしますが、北部地域のみでは、同じ行に2つの基準を入力して、次のように動作します。

• 地域： ="=North"
• 一致するアイテム： =COUNTIF($D$2:$D$7,B10)

以下のスクリーンショットで見ることができるように、両方の基準に一致するテーブルには2つのレコードしかありません。

注記。この例では、テキスト値の正確な一致基準： <em>="=North</em> "を使用して、指定されたテキストに正確に等しいセルのみを見つけます。単に北の領域基準を（等しい符号と二重引用符なし）に入力すると、Microsoft Excelは、指定されたテキスト、例えば北東または北西から始まるすべてのアイテムを見つけます。詳細については、テキスト値についてはExcel Advanced Filterを参照してください。

リスト内のアイテムと一致しない行をフィルター

小さなリストにないテーブル内のすべてのアイテムを見つけるには、countif式の結果がゼロに等しいかどうかを確認します。

countif（ list_to_match 、 top_data_cell） = 0

たとえば、リストに表示されるテーブル内の北部地域のアイテムをフィルタリングするには、次の基準を使用します。

• 地域： ="=North"
• 非一致アイテム： =COUNTIF($D$2:$D$7,B10)=0

注：

• 一致するリスト=COUNTIF(Sheet2!$A$2:$A$7,B10)別のワークシートに存在する場合は、式にシート名を含めてください。
• 結果を別のシートに抽出する場合は、フィルタリングされた行を別のワークシートに抽出する方法で説明されているように、宛先シートから高度なフィルターを起動します。

週末と平日のフィルター

これまでのところ、当社の高度なフィルター基準範囲の例は、主に数値値とテキスト値を扱っています。さて、デートを操作しているあなたの人々にいくつかの手がかりを与える時が来ました。

組み込みのExcel日付フィルターは、多くのシナリオをカバーする幅広いオプションを提供します。すべてではありませんが、すべてではありません！たとえば、日付のリストが与えられ、平日と週末をフィルタリングするように頼まれた場合、どのように進みますか？

おそらくご存知のように、Microsoft Excelは、特定の日付に対応する曜日を返す特別な平日関数を提供します。また、Excel Advanced Filter Criteria範囲で使用するのはこの機能です。

Excelで週末をフィルタリングする方法

平日の条件では、1つは日曜日の略で、土曜日の6つの略であることに留意してください。

または（平日（日付）= 7、平日（日付）= 1）

この例では、行5から始まる列Bの日付をフィルタリングしているため、週末のフォーミュラは次の形をとります。

=OR(WEEKDAY(B5)=7, WEEKDAY(B5)=1)

Excelで平日をフィルタリングする方法

平日をフィルタリングするには、上記の式を変更して、1（日曜日）と7（土曜日）を除外するようにします。

（平日（日付） 7、平日（日付） 1）

サンプルテーブルでは、次の式が処理します。

=AND(WEEKDAY(B5)7, WEEKDAY(B5)1)

さらに、空白のセルを除外するためにもう1つの条件を追加できます： =B5""

他の方法でワークシートの日付をフィルタリングするには、関連する日付関数を見つけて、高度なフィルター基準範囲で使用することをheしないでください。

まあ、これは複雑な基準でExcelでAdvanced Filterを使用する方法です。もちろん、あなたのオプションはこのチュートリアルで説明した例に限定されません。私たちの目標は、正しい軌道に乗るいくつかの心に強く訴えるアイデアを提供することでした。習得への道が練習で舗装されていることを思い出してください。以下のリンクを使用して例をダウンロードし、それらをよりよく理解するためにそれらを拡張またはリバースエンジニアリングすることをお勧めします。お読みいただきありがとうございます。来週のブログでお会いしましょう！

練習ワークブック

Excel Advanced Filterの例（.xlsxファイル）

以上がExcelで高度なフィルターを使用する方法 - 式を使用した基準範囲の例の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。