ライブリストの作成方法を学んだ後、Excel のフィルター ボタンに過度に依存するのをやめました

Excel でデータを操作することに時間を費やしたことがある場合は、おそらくフィルター処理に遭遇したことがあるでしょう。これは、必要なレコードに焦点を当て、データのサブセットを分析し、他のすべてを表示しないようにする簡単な方法です。ただし、Excel の通常のフィルター ボタンにはいくつか煩わしい点があります。ボタンを使用するとワークシートの行全体が非表示になり、元のデータセットが見えにくくなる可能性があり、終了時にクリアする必要のあるフィルターが残ります。

ここで FILTER 関数が役に立ちます。この関数は、一致するレコードの別のライブ リストを作成するため、元のデータは表示されたままで、変更されないままになります。そして、いくつかの気の利いた小さなトリックを使えば、FILTER は想像以上に柔軟になります。

FILTER はデータから別のライブリストを作成します

ソースをそのままにしておきます

クレジット: Lucas Gouveia/ハウツー ギーク

FILTER は 3 つの引数を使用します。

=FILTER(
array,
include,
(if_empty)
)

最初の引数、 arrayは、テーブル全体、単一の列、または複数の隣接する列のいずれであっても、FILTER が返すデータです。 2番目、 includeは、結果にどの行を含めるかを Excel に指示します。オプションの 3 番目の引数、 if_emptyでは、一致するレコードがない場合に表示される内容を指定できます。

FILTER は、ソース データが Excel テーブルである場合に特にうまく機能します。 A2:D101 などの固定範囲を指す代わりに、テーブルの名前と構造化参照を使用できるため、ソース データに追加された新しい行が数式で自動的に選択されます。

重要な問題が 1 つあります。それは、FILTER 式自体が Excel テーブルの外にある必要があるということです。その結果は数式の下と横のセルに反映され、動的配列はテーブル内に反映されません。このため、結果がこぼれるのに十分なスペースがあることを確認してください。そうしないと、Excel は #SPILL! を返します。エラー。

それが設定されたら、数式の構築を開始できます。

単一の条件から始める

1 つのセルで結果を制御しましょう

私はゴルフのスコアを追跡するために、GolfData という名前の Excel テーブルを使用しています。テーブルの隣に、セル F2 にコース名を含むデータ検証ドロップダウン メニューを備えた小さな基準領域があります。コースを選択すると、FILTER 関数はそこでプレーしたすべてのラウンドを返します。

コース名を数式にハードコードすることもできますが、セル参照を使用すると、数式を編集せずに条件を変更できます。この場合、F2 にはドロップダウン メニューから選択したコースが含まれています。

式は次のとおりです。

=FILTER(
GolfData,
GolfData(Course)=F2,
"No rounds found"
)

最初の引数は、FILTER に全体を返すように指示します。 ゴルフデータ テーブルの 2 番目のテーブルでは、テーブル内の各行をチェックします。 コース 列は F2 で選択したものと一致し、3 番目の列は一致するものがなかった場合に返すテキストを指定します。

結果は、選択したコースに一致する行のみを含む、別のライブ リストとしてワークシートに反映されます。ドロップダウンの選択を変更すると、それに応じてリストも変更されます。

FILTER はスピル範囲を作成するため、スピル範囲演算子 (#）。たとえば、FILTER 数式が F5 で始まる場合、 =ROWS(F5#) 数式によって現在返されるレコードの数をカウントします。 F2 でコースを変更すると、カウントが自動的に更新されます。

ニーズの変化に応じて条件を追加する

FILTER はいくつかの基準をチェックできます

単一の条件は便利ですが、最初からやり直すことなく、同じ式をより選択的にすることができます。

今回はG2とH2に開始日と終了日を追加しました。これで、結果をフィルタリングして、これら 2 つの日付の間にある選択したコースのラウンドを表示できるようになりました。良い点は、これらの条件を既存の式に追加するだけで済むことです。

=FILTER(
GolfData,
(GolfData(Course)=F2) *
(GolfData(Date)>=G2) *
(GolfData(Date)<=H2),
"No rounds found"
)

の * 記号は表す そして ここでのロジックは、3 つの条件がすべて満たされる必要があることを意味します。つまり、結果に行を含めるには、次の条件を満たす必要があります。

1. 選択したコースに合わせて、 そして

2. 開始日以降であること、 そして

3. 終了日以前であること。

これは、私が FILTER で気に入っている点の 1 つです。要件がより具体的になるにつれて、同じ基本式を拡張し続けることができます。結果を絞り込むたびに別のフィルタリング設定を作成する必要はありません。

さまざまな基準を組み合わせて一致させる

必要なときに条件を積み重ねてください

気象条件を追加することで、これをさらに一歩進めることができます。 I2 に別のデータ検証ドロップダウン メニューを追加し、データセット内の 4 つの気象カテゴリを含めました。ここで、選択したコースに一致し、日付範囲内にあり、選択した天候で開催されたラウンドのみが必要です。

式は次のようになります。

=FILTER(
GolfData,
(GolfData(Course)=F2) *
(GolfData(Date)>=G2) *
(GolfData(Date)<=H2) *
(GolfData(Weather)=I2), 
"No rounds found"
)

基準セルからコース、日付、または天気を変更でき、流出リストは自動的に更新されます。数式はソース データから分離されているため、元のデータセットも同時に表示できます。

どちらかの条件で済む場合は OR を使用します

プラス記号はロジックを変更します

ここまではすべての条件を満たす必要がありました。しかし、2 種類の天候のいずれかでラウンドが行われるのを見たい場合はどうすればよいでしょうか?コースと日付の基準はそのままにしますが、J2 に 2 つ目の天気ドロップダウン メニューを追加します。これで、たとえば、I2 で「晴れ」、J2 で「曇り」を選択できるようになりました。

式は次のとおりです。

=FILTER(
GolfData,
(GolfData(Course)=F2) *
(GolfData(Date)>=G2) *
(GolfData(Date)<=H2) *
((GolfData(Weather)=I2) + (GolfData(Weather)=J2)),
"No rounds found"
)

の + を表します または ここのロジックでは、どちらの気象条件でも一致を返すことができます。したがって、行を返すには、次のことを行う必要があります。

1. 選択したコースに合わせて、 そして

2. 開始日以降であること、 そして

3. 終了日以前であること、 そして

4. マッチ どちらか I2で選択した天気 または J2で選んだ天候。

これが、私が徐々に FILTER 式を構築するのが好きな理由です。何かを理解したら、 * そして + これにより、ソース データを手動で何度もフィルターしたり、ワークシート内の行全体を非表示にしたり、元のデータを見えにくくしたりすることなく、非常に具体的な結果リストを作成できます。

フィルターを次のレベルに引き上げる

行を一時的に非表示にしたい場合は、今でも Excel の通常のフィルター ボタンを使用しています。ただし、特定の条件を満たすレコードの個別のライブ リストが必要な場合、FILTER を使用すると、より柔軟な作業方法が得られます。また、これを UNIQUE や SORTBY などの他の動的配列関数と組み合わせて、同じライブリストのアプローチをさらに進めて、さらに便利な動的リストを作成することもできます。