ExcelFILTER関数の使用方法

Office 365は、XLOOKUP、SORT、FILTERなどの優れた機能を提供します。

Excelでデータをフィルタリングする場合、Office 365より前の世界では、ほとんどの場合、Excelの組み込みフィルター、またはせいぜい高度なフィルター、または複雑なSUMPRODUCT式に依存していました。データセットの一部をフィルタリングする必要がある場合、これは多くの場合、複雑な回避策です。

しかし、新しいFILTER機能により、条件に基づいてデータセットの一部をすばやく簡単にフィルタリングできるようになりました。

このチュートリアルでは、新しいFILTER機能がどれほど素晴らしいか、そしてそれを使ってできるいくつかの便利なことを紹介します。

ただし、例に入る前に、FILTER関数の構文を簡単に見てみましょう。

これらの新機能をExcelで取得したい場合は、次のことができます。Office365にアップグレードする（内部プログラムに参加して、すべての機能/式にアクセスします）

Excelフィルター関数–構文

FILTER関数の構文は次のとおりです。

= FILTER（array、include、[if_empty]）

• 配列 -これは、データがあり、そこから一部のデータをフィルタリングするセルの範囲です。
• include -これは、どのレコードをフィルタリングするかを関数に指示する条件です。
• [空の場合] –これはオプションのパラメーターであり、FILTER関数が結果を検出しなかった場合に何を返すかを指定できます。デフォルトでは（指定されていない場合）、＃CALCを返します。間違い

ここで、フィルター関数のいくつかの驚くべき例と、それなしでは非常に複雑だったフィルター関数で何ができるかを見てみましょう。

例1：XNUMXつの条件（領域）に基づいてデータをフィルタリングする

次のようなデータセットがあり、米国内のすべてのレコードのみをフィルタリングするとします。

これを行うFILTER式は次のとおりです。

= FILTER（$ A $ 2：$ C $ 11、$ B $ 2：$ B $ 11 = "US"）

上記の式はデータセットを配列として受け取り、条件は$ B $ 2：$ B $ 11 =” US”です。

この条件により、FILTER関数は列Bのすべてのセル（範囲を持つセル）を調べ、この条件に一致するレコードのみをフィルター処理します。

また、この例では、元のデータとフィルター処理されたデータを同じシートに配置しましたが、別々のシートやブックに配置することもできます。

フィルタ関数は、結果の動的配列を返します（つまり、値を返す代わりに、他のセルにオーバーフローする配列を返します）。

このためには、結果が空になる領域が必要です。範囲内の任意のセル（この例ではE2：G5）にすでに何かがあり、関数は#SPILLエラーを返します。

また、これは動的配列であるため、結果の一部を変更することはできません。結果またはセルE2（数式が入力されている場所）を含む範囲全体を削除できます。これらは両方とも、結果配列全体を削除します。ただし、単一のセルを変更（または削除）することはできません。

上記の数式では、範囲値をハードコーディングしましたが、セルに入れて、そのセルを範囲値で参照することもできます。

たとえば、次の例では、セルI2に範囲値があり、それを数式で参照しています。

=FILTER($A$2:$C$11,$B$2:$B$11=I1)

これにより、数式がさらに便利になります。セルI2の範囲値を変更するだけで、フィルターが自動的に変更されます。

セルI2にドロップダウンを設定して、選択するだけで、フィルタリングされたデータを即座に更新することもできます。

例2：XNUMXつの基準（より大きいまたはより小さい）に基づいてデータをフィルター処理する

フィルタ関数で比較演算子を使用して、特定の値よりも大きいまたは小さいすべてのレコードを抽出することもできます。

たとえば、以下に示すデータセットがあり、売上が10000を超えるすべてのレコードをフィルタリングするとします。

次の式でこれを行うことができます。

=FILTER($A$2:$C$11,($C$2:$C$11>10000))

配列引数は、データセット全体、この場合は条件（$ C $ 2：$ C $ 11> 10000）を参照します。

式は、各レコードの列Cの値をチェックします。値が10000より大きい場合はフィルタリングされ、それ以外の場合は無視されます。

10000未満のすべてのレコードを取得する場合は、次の式を使用できます。

=FILTER($A$2:$C$11,($C$2:$C$11<10000))

また、FILTER式を使用してより創造的になることができます。たとえば、売上に基づいて上位XNUMXつのレコードをフィルタリングする場合は、次の式を使用できます。

=FILTER($A$2:$C$11,($C$2:$C$11>=LARGE(C2:C11,3)))

上記の数式は、LARGE関数を使用して、データセット内でXNUMX番目に大きい値を取得します。次に、その値をFILTER関数条件で使用して、売上がXNUMX番目に大きい値以上のすべてのレコードを取得します。

例3：複数の条件（AND）を使用したデータのフィルタリング

次のデータセットがあり、売上値が10000を超える米国内のすべてのレコードをフィルタリングするとします。

これはAND条件であり、10000つのことを確認する必要があります。地域が米国にある必要があり、売上がXNUMXを超える必要があります。XNUMXつの条件のみが満たされる場合は、結果をフィルタリングしないでください。

地域として米国を使用し、売上が10000を超えるレコードをフィルタリングするフィルター式は次のとおりです。

=FILTER($A$2:$C$11,($B$2:$B$11="US")*($C$2:$C$11>10000))

請注意，標準（稱為包含參數）是 ($B$2:$B$11=”US”)*($C$2:$C$11>10000)

XNUMXつの条件を使用していて、両方が真である必要があるため、乗算演算子を使用してXNUMXつの条件を組み合わせました。これにより、0と1の配列が返されます。ここで、1は、両方の条件が満たされた場合にのみ返されます。

一致するレコードがない場合、関数は#CALCを返します。間違い。

意味のあるもの（エラーではない）を返したい場合は、次のような式を使用できます。

=FILTER($A$2:$C$11,($B$2:$B$11="USA")*($C$2:$C$11>10000),"Nothing Found")

ここでは、XNUMX番目のパラメーターとして「notfound」を使用しました。これは、一致するレコードが見つからない場合に使用されます。

例4：複数の基準（OR）を使用したデータのフィルタリング

FILTER関数の「contains」パラメーターを変更して、OR条件（任意の条件が真になる可能性がある場合）をチェックすることもできます。

たとえば、以下に示すデータセットがあり、国が米国またはカナダであるレコードをフィルタリングするとします。

これを行うための式は次のとおりです。

=FILTER($A$2:$C$11,($B$2:$B$11="US")+($B$2:$B$11="Canada"))

上記の式では、加算演算子を使用してXNUMXつの条件を加算していることに注意してください。これらの各条件はTRUEとFALSEの配列を返すため、いずれかの条件が満たされた場合にTRUEになる結合配列を追加できます。

別の例として、国が米国であるか、売上高が10000を超えるすべてのレコードをフィルタリングする場合があります。

次の式はこれを行います：

=FILTER($A$2:$C$11,($B$2:$B$11="US")+(C2:C11>10000))

注：FILTER関数でAND条件を使用する場合は、乗算演算子（*）を使用し、OR条件を使用する場合は、加算演算子（+）を使用します。

例5：平均より上/下のレコードのデータをフィルタリングする

FILTER関数の数式を使用して、平均より上または下の値を持つレコードをフィルタリングおよび抽出できます。

たとえば、以下に示すデータセットがあり、平均を超える売上値を持つすべてのレコードをフィルタリングするとします。

これは、次の式で実行できます。

=FILTER($A$2:$C$11,C2:C11>AVERAGE(C2:C11))

繰り返しますが、平均以下の場合は、次の式を使用できます。

=FILTER($A$2:$C$11,C2:C11<AVERAGE(C2:C11))

例6：偶数レコード（または奇数レコード）のみをフィルタリングする

偶数行または奇数行のすべてのレコードをすばやくフィルタリングして抽出する必要がある場合は、FILTER関数を使用してそれを行うことができます。

これを行うには、FILTER関数で行番号を確認し、行番号の条件に一致する行番号のみをフィルタリングする必要があります。

以下のようなデータセットがあり、このデータセットからレコードのみを抽出したいとします。

これを行うための式は次のとおりです。

=FILTER($A$2:$C$11,MOD(ROW(A2:A11)-1,2)=0)

上記の式は、MOD関数を使用して、各レコードの行番号（ROW関数によって指定される）をチェックします。

式MOD（ROW（A2：A11）-1,2）= 0は、行番号が偶数の場合はTRUEを返し、奇数の場合はFALSEを返します。最初のレコードが2番目の行にあり、11番目の行を最初のレコードとして扱うように行番号を調整するため、ROW（A1：AXNUMX）の部分からXNUMXを引いたことに注意してください。

同様に、次の式を使用してすべての奇数レコードをフィルタリングできます。

=FILTER($A$2:$C$11,MOD(ROW(A2:A11)-1,2)=1)

例7：数式を使用してフィルター処理されたデータを並べ替える

FILTER関数を他の関数と一緒に使用すると、さらに多くのことができます。

たとえば、FILTER関数を使用してデータセットをフィルタリングする場合、SORT関数を使用して並べ替えられた結果を取得できます。

次のようなデータセットがあり、売上が10000を超えるすべてのレコードをフィルタリングするとします。この関数でSORT関数を使用すると、結果のデータが売上に応じて並べ替えられるようになります。

次の式はこれを行います：

=SORT(FILTER($A$2:$C$11,($C$2:$C$11>10000)),3,-1)

上記の関数は、FILTER関数を使用して、売上が10000を超える列Cのデータを取得します。次に、SORT関数のFILTER関数によって返された配列を使用して、売上に応じてデータを並べ替えます。

SORT関数の3番目のパラメーターはXNUMXで、XNUMX番目の列に従ってソートします。1番目のパラメーターは-XNUMXで、データを降順で並べ替えます。

したがって、これらはExcelでFILTER関数を使用する7つの例です。

このチュートリアルがお役に立てば幸いです。