Excel でデータをフィルター処理する方法
私は最近、Excel で集計関数を使用して大量のデータを簡単に集計する方法に関する記事を書きましたが、その記事ではワークシート上のすべてのデータが考慮されていました。データのサブセットのみを見て、データのサブセットを要約したい場合はどうしますか?
Excel では、フィルターに一致しない行を非表示にするフィルターを列に作成できます。さらに、Excel の特別な関数を使用して、フィルター処理されたデータのみを使用してデータを要約することもできます。
この記事では、Excel でフィルターを作成する手順と、組み込み関数を使用してフィルター処理されたデータを要約する手順について説明します。
Excel で簡単なフィルターを作成する
Excel では、単純なフィルターと複雑なフィルターを作成できます。簡単なフィルターから始めましょう。フィルタを使用するときは、ラベルに使用する 1 行を常に上部に配置する必要があります。この行は必須ではありませんが、フィルターの操作が少し簡単になります。
上に偽のデータがあり、City でフィルターを作成したい 桁。 Excelでは、これは非常に簡単です。 [データ] をクリックしてください。 タブをクリックし、[フィルタ] をクリックします。 ボタン。シート上のデータを選択する必要も、最初の行をクリックする必要もありません。
[フィルター] をクリックすると、最初の行の各列の右端に小さなドロップダウン ボタンが自動的に追加されます。
次に、City 列のドロップダウン矢印をクリックします。以下で説明するいくつかの異なるオプションが表示されます。
上部では、City 列の値ですべての行をすばやく並べ替えることができます。データを並べ替えると、City 列の値だけでなく、行全体が移動することに注意してください。これにより、データは以前と同じようにそのまま維持されます。
また、ID という名前の列を一番前に追加し、1 からワークシートの行数まで番号を付けることをお勧めします。このようにして、ID 列でいつでも並べ替えて、データを元の順序に戻すことができます (それが重要な場合)。
ご覧のとおり、スプレッドシートのすべてのデータは、City 列の値に基づいて並べ替えられています。これまでのところ、非表示になっている行はありません。次に、フィルター ダイアログの下部にあるチェックボックスを見てみましょう。この例では、City 列に 3 つの一意の値しかなく、それら 3 つがリストに表示されます。
先に進み、2 つの都市のチェックを外し、1 つの都市をチェックしたままにしました。現在、表示されているデータは 8 行のみで、残りは非表示になっています。左端の行番号を確認すると、フィルタリングされたデータを見ていることが簡単にわかります。非表示の行数に応じて、いくつかの余分な水平線が表示され、数字の色が青になります.
ここで、結果の数をさらに減らすために 2 番目の列をフィルター処理するとします。列 C には、各ファミリーのメンバーの総数があり、メンバーが 2 人を超えるファミリーの結果のみを表示したいと考えています。
列 C のドロップダウン矢印をクリックすると、列の一意の値ごとに同じチェックボックスが表示されます。ただし、この場合は Number Filters をクリックします。 [より大きい] をクリックします。 .ご覧のとおり、他にもたくさんのオプションがあります。
新しいダイアログがポップアップし、ここでフィルターの値を入力できます。 AND または OR 関数を使用して複数の基準を追加することもできます。たとえば、値が 2 より大きく 5 以外の行が必要であると言えます。
これで、データは 5 行になりました。ニューオーリンズ出身で、メンバーが 3 人以上の家族のみです。簡単ですか？ドロップダウンをクリックし、[「列名」からフィルターをクリア] をクリックすると、列のフィルターを簡単にクリアできます。 リンク。
以上で、Excel の単純なフィルターについて説明します。それらは非常に使いやすく、結果は非常に簡単です。 Advanced を使用して複雑なフィルタを見てみましょう。 フィルタ ダイアログ
Excel で高度なフィルターを作成する
より高度なフィルタを作成する場合は、Advanced を使用する必要があります フィルタ ダイアログ。たとえば、ニューオーリンズに住む 2 人以上の家族がいるすべての家族を表示したいとします OR 家族が 3 人以上いるクラークスビルのすべての家族 AND .EDU を持つもののみ 末尾のメールアドレス。これは単純なフィルターではできません。
これを行うには、Excel シートを少し異なる方法でセットアップする必要があります。次に示すように、データ セットの上に数行挿入し、見出しラベルを最初の行に正確にコピーします。
高度なフィルタの仕組みは次のとおりです。最初に上部の列に基準を入力してから、[詳細] をクリックする必要があります。 [並べ替えとフィルタ] の下のボタン データ タブ
では、これらのセルには正確に何を入力できるのでしょうか?では、例から始めましょう。ニューオーリンズまたはクラークスビルのデータのみを表示したいので、それらをセル E2 と E3 に入力しましょう。
異なる行に値を入力すると、OR を意味します。ここで、メンバーが 2 人を超えるニューオーリンズの家族と、メンバーが 3 人を超えるクラークスビルの家族が必要です。これを行うには、>2 と入力します C2 と>3 C3で。
>2 と New Orleans は同じ行にあるため、AND 演算子になります。上記の 3 行目も同様です。最後に、電子メール アドレスの末尾が .EDU の家族のみが必要です。これを行うには、*.edu と入力するだけです D2とD3の両方に。 * 記号は、任意の数の文字を意味します。
それができたら、データセット内の任意の場所をクリックし、[Advanced] をクリックします。 ボタン。 リストが鳴った 詳細設定ボタンをクリックする前にフィールドをクリックしたため、フィールドは自動的にデータセットを把握します。 Criteria range の右側にある小さなボタンをクリックします。 ボタン。
A1 から E3 までのすべてを選択し、同じボタンをもう一度クリックして [高度なフィルター] ダイアログに戻ります。 [OK] をクリックすると、データがフィルタリングされます!
ご覧のとおり、これらの条件すべてに一致する結果は 3 つしかありません。これが機能するには、基準範囲のラベルがデータセットのラベルと正確に一致する必要があることに注意してください。
この方法を使用して、より複雑なクエリを作成できることは明らかなので、試してみて、目的の結果を取得してください。最後に、フィルタリングされたデータに合計関数を適用する方法について説明しましょう。
フィルタリングされたデータの要約
ここで、フィルター処理されたデータで家族の人数を合計したいとします。どうすればそれを行うことができるでしょうか? [クリア] をクリックしてフィルタをクリアしましょう。 リボンのボタン。心配する必要はありません。詳細設定ボタンをクリックしてもう一度 [OK] をクリックするだけで、高度なフィルターを簡単に適用できます。
データセットの一番下に、Total というセルを追加しましょう 次に、sum 関数を追加して、家族のメンバーの合計を合計します。この例では、=SUM(C7:C31) と入力しました。 .
すべての家族を見ると、合計 78 人のメンバーがいます。それでは、高度なフィルターを再適用して、何が起こるか見てみましょう。
おっと！正しい数 11 を表示する代わりに、合計が 78 であることが表示されます!何故ですか？ SUM 関数は非表示の行を無視しないので、まだすべての行を使用して計算を行っています。幸いなことに、非表示の行を無視するために使用できる関数がいくつかあります。
1 つ目は SUBTOTAL です .これらの特別な関数を使用する前に、フィルターをクリアして関数を入力してください。
フィルタをクリアしたら、=SUBTOTAL() と入力します。 ドロップダウン ボックスが表示され、多数のオプションが表示されます。この関数を使用すると、最初に数値を使用して使用する合計関数のタイプを選択できます。
この例では、SUM を使用します 、数字の 9 を入力するか、ドロップダウンからクリックします。次に、コンマを入力してセル範囲を選択します。
Enter キーを押すと、値 78 が以前と同じであることがわかります。ただし、ここでフィルターを再度適用すると、11 が表示されます!
優秀な！それがまさに私たちが望んでいることです。フィルターを調整すると、現在表示されている行のみが常に値に反映されます。
SUBTOTAL 関数とほとんど同じように機能する 2 つ目の関数は AGGREGATE です。 .唯一の違いは、非表示の行を無視することを指定する必要がある別のパラメーターが AGGREGATE 関数にあることです。
最初のパラメーターは使用する合計関数で、SUBTOTAL と同様に、9 は SUM 関数を表します。 2 番目のオプションは、非表示の行を無視するために 5 を入力する必要がある場所です。最後のパラメーターは同じで、セルの範囲です。
また、集計関数に関する私の記事を読んで、AGGREGATE 関数や、MODE、MEDIAN、AVERAGE などの他の関数の使用方法を詳しく学ぶこともできます。
この記事が、Excel でフィルターを作成して使用するための良い出発点になることを願っています。ご不明な点がございましたら、お気軽にコメントを投稿してください。お楽しみください!
-
Excel の列制限を増やす方法 (2 つの方法)
Excel 列の制限を増やすための解決策またはいくつかの特別なトリックを探している場合は、適切な場所にたどり着きました。この記事では、適切な図を使用してすべての手順を説明するため、目的に合わせて簡単に適用できます。記事の本編に入りましょう。 Excel の列制限とは Excel には、16,384 列あります 最新バージョンで使用可能。 Excel 2007 バージョンでも同じ数の列が見つかります そして後で . Excel 2003 では バージョンでは、256 列しかありませんでした . Excel の列制限が固定されているのはなぜですか? 行と列の制限を制限する理由はいくつかあり
-
Excel にデータ分析をインストールする方法
Microsoft Excel は、さまざまな種類のデータ分析に非常に広く使用されています。複雑な統計的または工学的分析を開発したい場合、時間と労力がかかります。しかし、Excel のデータ分析オプションを使用することで、問題を根絶することができます。 しかし、その機能を自然に使うことはできません。最初にインストールする必要があります。この記事では、Excel にデータ分析をインストールする方法を示します。 データ分析ツールパックとは データ分析ツールパック アドインです データ分析を有効にするために不可欠な Excel で . Data Analysis Toolpak をインストールする