大多数の Excel ユーザーにとって、「データ フィルタリング」という言葉が頭に浮かんだとき、タブの通常のクラシック フィルターのみが表示されます。 データ – フィルター (データ — フィルター):
このようなフィルターは間違いなくおなじみのものであり、ほとんどの場合はそれで十分です。 ただし、複数の列で一度に多数の複雑な条件でフィルタリングする必要がある場合があります。 ここの通常のフィルターはあまり便利ではなく、もっと強力なものが欲しいです。 そのようなツールは 高度なフィルター、特に少し「やすりで仕上げる」（伝統によれば）。
ベース
開始するには、データ テーブルの上にいくつかの空の行を挿入し、そこにテーブル ヘッダーをコピーします。これは条件付きの範囲になります (わかりやすくするために黄色で強調表示されています)。
黄色のセルと元のテーブルの間には、少なくとも XNUMX 行の空行が必要です。
フィルタリングが実行される基準（条件）を入力する必要があるのは、黄色のセルです。 たとえば、第 XNUMX 四半期のモスクワ「Auchan」でバナナを選択する必要がある場合、条件は次のようになります。
フィルタリングするには、ソース データを含む範囲内の任意のセルを選択し、タブを開きます 且つ そして、 さらに (データ — 詳細). 開いたウィンドウでは、データの範囲がすでに自動的に入力されているはずです。条件の範囲、つまり A1:I2 を指定するだけで済みます。
条件の範囲を「余裕を持って」割り当てることはできないことに注意してください。つまり、条件の範囲内の空のセルは Excel によって基準が存在しないと認識され、セル全体が空のセルとして認識されるため、余分な空の黄色の線を選択することはできません。すべてのデータを無差別に表示する要求として行。
スイッチ 結果を別の場所にコピーする (通常のフィルターと同様に) このシートではなくリストをフィルター処理できますが、選択した行を別の範囲にアンロードできます。これはフィールドで指定する必要があります。 結果を範囲に入れる. この場合、この関数は使用せず、そのままにしておきます 適切なフィルタ リスト をクリックし OK. 選択した行がシートに表示されます。
マクロの追加
「えっと、どこが便利なんですか？」 あなたが尋ねると、あなたは正しいでしょう。 黄色のセルに条件を手で入力するだけでなく、ダイアログボックスを開いてそこに範囲を入力し、 OK. 悲しい、私は同意します！ しかし、「© が登場するとすべてが変わる」 – マクロ！
黄色のセルを変更するなど、条件が入力されたときに高度なフィルターを自動的に実行する単純なマクロを使用すると、高度なフィルターでの作業が大幅に高速化および簡素化されます。 現在のシートのタブを右クリックし、コマンドを選択します 原文 （ソースコード）. 開いたウィンドウで、次のコードをコピーして貼り付けます。
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A2:I5")) Is Nothing Then On Error Resume Next ActiveSheet.ShowAllData Range("A7").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange :=Range("A1").CurrentRegion End If End Sub この手順は、現在のワークシートのセルが変更されると自動的に実行されます。 変更されたセルのアドレスが黄色の範囲 (A2:I5) に収まる場合、このマクロはすべてのフィルター (存在する場合) を削除し、A7 から始まるソース データ テーブルに拡張フィルターを再適用します。つまり、すべてが即座にフィルター処理されます。次の条件を入力した後:
だから、すべてがはるかに優れていますよね？ 🙂
複雑なクエリの実装
すべてがオンザフライでフィルタリングされるようになったので、ニュアンスをもう少し深く掘り下げて、高度なフィルターでより複雑なクエリのメカニズムを分解できます。 完全一致の入力に加えて、さまざまなワイルドカード文字 (* および ?) と数学的な不等号をさまざまな条件で使用して、近似検索を実装できます。 大文字と小文字は関係ありません。 わかりやすくするために、可能なすべてのオプションを表にまとめました。
| 基準 | 結果 |
| gr* または gr | で始まるすべてのセル GrIe Gr耳, Grりんご, Grアナト 等々 |
| =タマネギ | すべてのセルが単語のみで正確に 弓、つまり完全一致 |
| *liv* または *liv | 含む細胞 リヴ どのように下線を引くか、つまり Оリヴそれ, リヴep, 従ったリヴ 等々 |
| =p*v | で始まる単語 П で終わる В ie П最初のв, Пエーテルв 等々 |
| なので | で始まる単語 А さらに含む СIe Аペルсin, Аナナс, Asai 等々 |
| =*s | で終わる言葉 С |
| =???? | 4 文字のテキスト (スペースを含む文字または数字) を含むすべてのセル |
| =m??????n | で始まる 8 文字のテキストを含むすべてのセル М で終わる НIe Мアンダリн, М不安н 等々 |
| =*ん??a | で終わるすべての単語 А、末尾から 4 番目の文字はどこですか НIe ビームнikа, 従ったнozа 等々 |
| >=e | で始まるすべての単語 Э, Ю or Я |
| <>*o* | 文字を含まないすべての単語 О |
| <>*ヴィッチ | で終わる単語を除くすべての単語 HIV (たとえば、ミドル ネームで女性をフィルタリングします) |
| = | すべての空のセル |
| <> | 空でないすべてのセル |
| > = 5000 | 値が 5000 以上のすべてのセル |
| 5 または =5 | 値が 5 のすべてのセル |
| > = 3年18月2013日 | 18 年 2013 月 XNUMX 日以降の日付を持つすべてのセル (両端を含む) |
微妙な点:
- * 記号は任意の数の任意の文字を意味し、? – 任意の XNUMX 文字。
- テキスト クエリと数値クエリを処理するロジックは少し異なります。 したがって、たとえば、数字 5 の条件セルは、XNUMX で始まるすべての数字を検索することを意味するわけではありませんが、文字 B の条件セルは B* と等しくなります。つまり、文字 B で始まるすべてのテキストを検索します。
- テキスト クエリが = 記号で始まらない場合は、精神的に * を末尾に付けることができます。
- Dates must be entered in the US format month-day-year and through a fraction (even if you have Excel and regional settings).
論理接続詞 AND-OR
異なるセルに書かれているが、同じ行にある条件は、論理演算子によって相互に接続されていると見なされます И （と）:
それらの。 第XNUMX四半期に、正確にはモスクワで、同時にAuchanからバナナをフィルタリングします。
論理演算子で条件をリンクする必要がある場合 OR （OR）の場合は、別の行に入力するだけです。 たとえば、マネージャーの Volina のモスクワの桃のすべての注文と、サマラの第 XNUMX 四半期のタマネギのすべての注文を検索する必要がある場合、次のように条件の範囲で指定できます。
XNUMX つの列に XNUMX つ以上の条件を課す必要がある場合は、条件範囲の列ヘッダーを複製し、その下に XNUMX 番目、XNUMX 番目などを入力するだけです。 条項。 したがって、たとえば、XNUMX 月から XNUMX 月までのすべてのトランザクションを選択できます。
一般に、「ファイルで終了」した後、高度なフィルターは非常に適切なツールであることがわかります。場所によっては、従来のオートフィルターよりも悪くありません。
- マクロのスーパーフィルター
- マクロとは何か、Visual Basic でマクロ コードを挿入する場所と方法
- Microsoft Excel のスマート テーブル