Excel の基準範囲を使用した高度なフィルタ (18 アプリケーション)

Microsoft Excel では、 高度なフィルタ オプションは、2 つ以上の基準を満たすデータを探す場合に役立ちます。この記事では、高度なフィルタのアプリケーションについて説明します 基準範囲

ここから練習用ワークブックをダウンロードしてください。

Excel での高度なフィルター基準範囲の 18 のアプリケーション

1.数値と日付に対する高度なフィルター基準範囲の使用

何よりもまず、データセットについて紹介します。列 B 列 Eへ 販売に関連するさまざまなデータを表します。ここで Advanced Filter Criteria Range を実装できます .この例では、数値と日付のフィルター処理に Advanced Filter Criteria Range を使用します。販売数量が 10 を超えるすべてのデータを抽出します .手順を見てみましょう。

• まず、データ タブで 詳細 を選択します 並べ替えとフィルタ のコマンド オプション。 Advanced Filter という名前のダイアログ ボックス

• 次に、表全体を選択します (B4:E14) リスト範囲 .
• セルを選択 (C17:C18) 基準範囲として .
• OK を押します .

• 最後に、数量が 10 を超えるデータのみを確認できます .

注:
1. 少なくとも 2 行の基準を選択してください。

2. フィルタリング基準が適用される関連列のヘッダーを使用します。

2.高度なフィルター基準によるテキスト値のフィルター

数値と日付に加えて、論理演算子を使用してテキスト値を比較できます。このセクションでは、高度なフィルター基準を使用してテキスト値をフィルタリングし、テキストと完全に一致し、特定の文字が先頭にあるようにする方法について説明します。

2.1 テキストの完全一致について

この方法では、フィルタリング 入力テキストの正確な値を返します。新しい列 City とともに次の売上のデータセットがあるとします。 . 「NEW YORK」という都市のデータのみを抽出します .このアクションを実行するには、次の手順を実行してください:

• 最初に、セル C18 を選択します .次の式を挿入してください:

=EXACT(D5," NEW YORK")

• Enter を押します .

• 次に、次のフィルタ基準範囲を選択します:

リスト範囲:B4:F14

基準範囲:C17:C18

• [OK] をクリックします .

• 最後に、都市 「NEW YORK」 のデータのみを取得します。 .

2.1 先頭に特定の文字がある

次に、完全一致ではなく、特定の文字で始まるテキスト値をフィルタリングします。ここでは、単語 'New' で始まる都市の値のみを抽出します .その方法を見てみましょう。

• まず、 高度なフィルタ で基準範囲を選択します ボックス:

リスト範囲:B4:F14

基準範囲:C18:C19

• OK を押します .

• 最後に、「New」 という単語で始まるすべての都市のデータを取得します .

3.高度なフィルター オプションでワイルドカードを使用する

ワイルドカードの使用 キャラクター Advanced Filter Criteria Range を適用するもう 1 つの方法です .通常、Excel には 3 種類のワイルドカード文字があります:

<強い>? (疑問符) – テキスト内の任意の 1 文字を表します。

* (アスタリスク) – 任意の数の文字を表します。

~ (チルダ) – テキスト内のワイルドカード文字の存在を表します。

アスタリスク (*) を使用して、データセット内の特定のテキスト文字列を検索できます .この例では、テキスト 'J' で始まる営業担当者の名前を見つけます .そのためには、次の手順に従う必要があります。

• まず、高度なフィルタを開きます 窓。次の基準範囲を選択してください:

リスト範囲:B4:F14

基準範囲:C17:C18

• OK を押します .

• 最後に、テキスト 'J' で始まる営業担当者の名前のみを取得します .

関連コンテンツ: Excel の高度なフィルター [複数の列と条件、数式とワイルドカードを使用]

4.高度なフィルター条件範囲で数式を適用

Advanced Filter Criteria Range を使用するもう 1 つの方法は、数式を適用することです。この例では、$350 を超える売上高を抽出します。 .以下の手順に従ってください:

• 最初に、セル C19 を選択します .次の式を挿入してください:

=F5>350

• [OK] をクリックします .

この数式は、売上金額が $350 より大きいかどうかを繰り返します。

• 次に、高度なフィルタで次の基準範囲を選択します ダイアログ ボックス:

リスト範囲:B4:F14

基準範囲:C17:C19

• OK を押します .

• したがって、$350 を超える売上高の値のみのデータを確認できます .

5. AND ロジック基準による高度なフィルター

AND ロジックを紹介します 高度なフィルター基準範囲で。このロジックは 2 つの基準を使用します。データが両方の基準を満たす場合、出力値を返します。ここに、次のデータセットがあります。このデータセットでは、ニューヨーク市のデータをフィルタリングします 販売額 >=200 .その方法を見てみましょう。

• まず、高度なフィルタに移動します ダイアログ ボックスで、次の基準範囲を選択します:

リスト範囲:B4:F14

基準範囲:C18:C19

• OK を押します .

• 最後に、ニューヨーク市のみのデータセットを取得します 売り上げがある $250 を超える値 .

6.高度なフィルター基準範囲での OR ロジックの使用

AND のように ロジック、 OR ロジック も 2 つの基準を使用します。 かつ OR の場合、ロジックは両方の条件が満たされた場合に出力を返します 条件が 1 つだけ満たされた場合、ロジックは戻ります。ここでは、都市 New York のデータを取得します。 と テキサス それだけ。以下の手順に従って、このアクションを実行してください:

• 最初に、高度なフィルタを開きます ダイアログボックス。次の基準範囲を入力してください:

リスト範囲:B4:F14

基準範囲:C18:C20

• [OK] をクリックします。

• 最後に、都市 New York のみのデータセットを取得します とテキサス .

7.基準範囲としての AND &OR ロジックの組み合わせ

複数の条件でデータをフィルタリングする必要がある場合があります。 その場合、 AND の組み合わせを使用できます &または 論理。指定された基準に基づいて、次のデータセットからデータを抽出します。このアクションを実行するには、次の手順を実行してください:

• まず、高度なフィルタを開きます ダイアログボックス。次の基準を選択してください:

リスト範囲:B4:F14

基準範囲:C18:C20

• OK を押します。

• したがって、基準に一致するデータセットのみを表示できます。

8.高度なフィルター基準範囲を使用して特定の列を抽出する

この例では、データセットの特定の部分をフィルタリングします。フィルタリング後、フィルタリングされた部分を別の列に移動します。次のデータセットを使用して、以下の手順でこのアクションを実行します。

• まず、高度なフィルタから ダイアログ ボックスで次の条件を選択します:

リスト範囲:B4:F14

基準範囲:C18:C20

• 別の場所にコピーを選択 オプション
• 入力 コピー先 範囲 H8:I10 .
• [OK] をクリックします。

• したがって、H8:I10 でフィルタリングされたデータを取得します

9.フィルタリング後に別のワークシートにデータをコピー

In this example, we will also copy data in another worksheet whereas in the previous example we did it in the same worksheet. Do the following steps to execute it:

• First, go to ‘Another Worksheet-2’ where we will copy data after filtering.

We can see two columns ‘City’ and ‘Sales’ in ‘Another Worksheet-2’ .

• Next, open the ‘Advanced Filter’ dialogue box.

• Then go to ‘Another Worksheet-1’ . Select the following criteria:

List Range:B4:F14

Criteria Range:C18:C19

• Now, select copy to another location オプション

• After that, go to ‘Another Worksheet-2’ . Select Copy to Range B2:C4 .
• OK を押します .

• Finally, we can see the filtered data in ‘Another Worksheet-2’ .

10. Extract Unique Records with Advanced Filter Criteria

In this case, we will extract only the unique values from a specific column. From the following dataset, we will extract unique values of cities in another column. Just do the steps:

• In the beginning, open the Advanced Filter 窓。 Select the criteria

List range:D4:D14

• Next, select the option Copy to another location .
• Then, input Copy to range as H4:H8 .
• Check the box Unique records only .
• OK を押します .

• Finally, we can see the names of cities with unique records only in column H .

11. Find Weekdays with Advanced Filter Criteria Range

We can find Weekdays with Advanced Filter Criteria Range. Here we will use the following dataset to illustrate this process:

• Firstly, select cell C19 . Insert the following formula:

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

• Next, set the following criteria range in the Advanced Filter dialogue box:

List Range:B4:F14

Criteria Range:C18:C19

• OK を押します .

• Finally, we will get the Date values only for weekdays.

🔎 フォーミュラの仕組み

• WEEKDAY(B5)<>1:1 denotes Sunday. This part set the criteria that the date is not Sunday .
• WEEKDAY(B5)<>7:7 denotes Sunday. This part set the criteria that the date is not Saturday .
• AND(WEEKDAY(B5)<>1,WEEKDAY(B5)<>7): Set the criteria that the day is neither Saturday nor Sunday .

12. Apply Advanced Filter to Find Weekend

We can also use the Advanced Filter Criteria Range to find the Weekend from a Date column. Let’s see how to do that using the following dataset:

• In the beginning select cell C19. Insert the following formula:

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

• Enter を押します .

• Next, from the Advanced Filter dialogue box select the following criteria range:

List Range:B4:F14

Criteria Range:C18:C19

• OK を押します .

• So, we can see only the values of the weekend in the Date

13. Use Advanced Filter to Calculate Values Below or Above Average

In this section, we will calculate the below or above average value by using Advanced Filter Criteria Range . Here we will only filter the sales value which is greater than the average sales value.

• First, select cell C19 . Insert the following formula:

=E5>AVERAGE(E5:E14)

• Next, open the Advanced Filter ダイアログボックス。 Input the following criteria range:

List Range:B4:F14

Criteria Range:C18:C19

• OK を押します .

• So, we get only the dataset for sales value greater than the average value.

14. Filtering Blank Cells with OR Logic

If our dataset consists of blank cells, we can extract blank cells by using Advanced Filter .

We have the following dataset. The dataset consists of blank cells . We have set the criteria by using the following formula:

=B5=""

• First, go to the Advanced Filte r dialogue box. Input the following criteria:

List Range:B4:F14

Criteria Range:C17:C22

• OK を押します .

• Finally, we get the dataset that only consists of blank cells.

15. Apply Advanced Filter to Filter Non-Blank Cells using OR as well as AND Logic

In this example, we will eliminate blank cells whereas in the previous example we eliminated the nonblank cells. We have set the following criteria for using the formula:

=B5<>""

• Firstly, go to the Advanced Filter ダイアログボックス。 Insert the following criteria range:

List Range:B4:F14

Criteria Range:C17:G18

• Now press OK .

• So, we get the dataset free from blank cells.

16. Find First 5 Records Using Advanced Filter Criteria Range

Now we will implement the Advanced Filter option for extracting the first 5 records from any kind of dataset. In this example, we will take the first five values of the Sales 桁。 To perform this we will first set the criteria based on the following formula:

=F5>=LARGE($F$5:$F$14,5)

After that, just do the following steps:

• In the beginning, go to the Advanced Filter ダイアログボックス。 Insert the following criteria range:

List Range:B4:F14

Criteria Range:C17:C18

• Hit OK .

• Finally, we get the top five records of the Sales

17. Use Advanced Filter Criteria Range to Find Bottom Five Records

We can use the Advanced Filter option to find the bottom five records also. To find the bottom five records for the Sales column, we will create the following criteria using the below formula:

=F5<=SMALL($F$5:$F$14,5)

Then follow the below steps to perform this action:

• First, insert the following criteria range in the Advanced Filter dialogue box:

List Range:B4:F14

Criteria Range:C17:C18

• その後、OK を押します .

• Lastly, we can see the bottom five values of the Sales

18. Filter Rows According to a List’s Matched Entries Using Advanced Filter Criteria Range

Sometimes we may need to compare between two columns or rows of a dataset to eliminate or keep particular values. We can use the match entry option to perform this kind of action.

18.1 Matches with Items in a List

Suppose we have the following dataset with two columns of cities. We will take only the matching entries between these two columns. In order to do this we will set the following criteria using the below formula:

=C5=E5

Just do the following steps to perform this action:

• In the beginning, open the Advanced Filter オプション。 Insert the following criteria range:

List Range:B4:F14

Criteria Range:C17:C18

• Hit OK .

• Lastly, We can see the same value in two columns of cities.

18.2 Do Not Matches with Items in a List

The previous example was for matching entries whereas this example will filter non-matching entries. We will set the criteria by using the following formula:

=C5<>E5

Let’s see how to perform this:

• First, from the Advance Filter insert the following criteria range:

List Range:B4:F14

Criteria Range:C17:C18

• Then, press OK .

• Finally, we will get the values of cities in Column C and Column E that do not match with one another.

結論

In this article, we have tried to cover all the methods of the Advanced Filter Criteria Range オプション。 Download our practice workbook added to this article and practice yourself. If you feel any confusion or have any suggestions just leave a comment below, we will try to reply to you as soon as possible.

関連記事

• Excel Advanced Filter Not Working (2 Reasons &Solutions)
• 動的高度フィルター Excel (VBA &マクロ)
• VBA で高度なフィルタを使用する方法 (ステップバイステップのガイドライン)