最近大量のデータを簡単に要約するためにExcelで要約関数を使用する方法 という記事を書きましたが、その記事ではワークシートのすべてのデータを考慮しました。
Excelでは、フィルタに一致しない行を非表示にする列に対してフィルタを作成できます。また、Excelで特別な関数を使用して、フィルタリングされたデータのみを使用してデータを集計することもできます。
この記事では、Excelでフィルタを作成する手順と、
Excelで簡単なフィルタを作成する
Excelでは、単純なフィルタと複雑なフィルタを作成できます。簡単なフィルタから始めましょう。フィルタを使用する場合は、ラベルに使用する行が常に1つ上に表示されている必要があります。この行を持つ必要はありませんが、フィルタを使って作業するのが少し楽になります。
市区町村]列にフィルタを作成する必要があります。 Excelでは、これは本当に簡単です。リボンの[データ]タブをクリックし、[フィルタ]ボタンをクリックします。
「フィルター」をクリックすると、各列のデータを選択するか、
次に、最初の行のドロップダウン矢印をクリックします。都市の列。
上部には、すべての行を簡単に並べ替えることができます。 City列の値。データを並べ替えると、[City]列の値だけでなく、行全体が移動されます。
IDの最前面に列を追加し、1から数えて列に番号を付けることをお勧めしますワークシート。この方法では、ID列で並べ替えることができ、重要であれば元の順序と同じ順序でデータを取得できます。
ご覧のとおり、スプレッドシートのすべてのデータは、[City]列の値に基づいてソートされます。今のところ、行は隠されていません。次に、フィルタダイアログの下部にあるチェックボックスを見てみましょう。私の例では、都市の列には3つのユニークな値しかなく、リストには3つの値が表示されます。
2つの都市と1つはチェックした。今私は8行のデータしか表示せず、残りは隠されています。左端の行番号を確認すると、フィルタリングされたデータを見ていることを簡単に知ることができます。
次に、2番目の列をフィルタリングして、さらに数を減らしたいとします結果のC列では、各家族の総会員数があり、2人以上の会員の家族の結果のみを表示したいとします。
列Cのドロップダウン矢印をクリックすると、列内の一意の値ごとに同じチェックボックスが表示されます。ただし、この場合はNumber FiltersをクリックしてからGreater Thanをクリックします。
新しいダイアログがポップアップし、ここに値を入力することができますフィルターのために。また、ANDまたはOR機能を持つ複数の条件を追加することもできます。
9
これで、値が2より大きく5以外の行が必要になると言うことができます。わずか5行のデータ：ニューオーリンズの家族と3人以上のメンバー。十分に簡単ですか？ドロップダウンをクリックし、[フィルタを列名から削除]をクリックすると、列のフィルタを簡単に消去できます。
これは、Excelの簡単なフィルタの場合です。彼らは非常に使いやすく、結果はかなり簡単です。次に、詳細フィルタダイアログを使用して複雑なフィルタを見てみましょう。
Excelで詳細フィルタを作成する
より高度なフィルタを作成するには、 [詳細]フィルタダイアログを使用する必要があります。たとえば、ニューオーリンズに住むすべての家族と家族の中に3人以上のメンバーがいるOR.EDUで終了するメールアドレスを持つもののみ。
これを行うには、Excelシートを少し異なる設定にする必要があります。次にデータセットの上に2行を挿入し、下に示すように見出しラベルを最初の行に正確にコピーします。
ここでは、高度なフィルタの仕組みを紹介します。まず上部の列に条件を入力してから、[ソート＆amp;アンサー]の[詳細]ボタンをクリックする必要があります。
12
これらのセルに正確に何を入力できますか？はい、この例から始めましょう。ニューオーリンズやクラークスビルのデータだけを見たいので、それらをE2とE3のセルに入力しましょう。
それはORを意味します。今では2人以上のメンバーを持つニューオリンズ家族と3人以上のメンバーを持つClarksville家族が欲しいです。これを行うには、C2に＆gt; 2、C3には＆gt; 3を入力します。 >
＆gt; 2とNew Orleansは同じ行にあるため、AND演算子になります。上記の3行目についても同様です。最後に、.EDUの電子メールアドレスを持つファミリだけが必要です。これを行うには、D2とD3の両方に*。eduと入力します。 *記号は任意の数の文字を意味します。
これを済ませたら、データセット内の任意の場所をクリックし、[]ボタンをクリックします。 List Rangフィールドでは、クリックしてから[Advanced]ボタンをクリックする前にデータセットが自動的に把握されます。 Criteria rangeボタンの右にある小さな小さなボタンをクリックします。
A1からE3までのすべてを選択し、同じボタンをもう一度クリックすると、詳細フィルタダイアログに戻ります。
ご覧のとおり、すべての条件に一致する結果が3つしかありません。
このメソッドを使用すると、はるかに複雑なクエリを作成することができますので、それを使って遊んでくださいあなたの望む結果を得る。最後に、フィルタリングされたデータに集計関数を適用する方法について説明します。
フィルタリングされたデータの集計
フィルタリングされたデータの家族の数を合計したいとします。それについてやっている？さて、リボンの[クリア]ボタンをクリックしてフィルタをクリアしてみましょう。
[詳細設定]ボタンをクリックして、 合計というセルを追加してから、合計関数を追加して家族の合計を集計しましょう。私の例では、= SUM（C7：C31）と入力しただけです。
私は78人のメンバーを集めました。
これで、詳細フィルタを再適用して何が起こるかを見てみましょう。正しい数字、11を表示する代わりに、私はまだ合計が78であることを見る！何故ですか？さて、SUM関数は隠れた行を無視しないので、まだすべての行を使って計算を行っています。幸いにも、非表示の行を無視するために使用できる関数がいくつかあります。
最初はサブタイトルです。
フィルタがクリアされたら、= SUBTOTAL（）を入力します。 >この関数を使用すると、使用したい集計関数のタイプを最初に選択し、数値を使用してドロップダウンボックスが表示されるはずです。
この例では、 SUMので、番号9を入力するか、ドロップダウンリストからクリックします。次にコンマを入力してセルの範囲を選択します。
Enterを押すと78の値が以前と同じになるはずですが、フィルタを再度適用すると、11が表示されます！
これはまさに私たちが望むものです。フィルタを調整することができ、値は常に現在表示されている行のみを反映します。
SUBTOTAL関数とほぼ同じように動作する2番目の関数は集約です。あなたが隠れた行を無視するように指定する必要がある別のパラメータがAGGREGATE関数にあるということです。
23
あなたが使用したいと思う合計関数であり、SUBTOTALと同様に、9はSUM関数を表します。 2番目のオプションは、非表示の行を無視するために5を入力する必要がある場所です。最後のパラメータは同じで、セルの範囲です。
また、AGGREGATE関数を使用する やMODE、MEDIAN、AVERAGEなどの他の関数の詳細については、サマリー機能に関する記事を読むこともできます。
記事は、Excelでフィルタを作成して使用するための出発点となります。ご不明な点がございましたら、お気軽にコメントを投稿してください。お楽しみください！