☰

表の外側のセルの情報を使用して表をフィルタリングする

tag-icon • tag-icon microsoft-excel
表の外側のセルの情報を使用して表をフィルタリングする

ワークブック全体の複数の異なるソースから情報を取得するテーブルがあります。テーブルは 4 つのカテゴリで定期的にフィルターされ、Excel に詳しくないユーザーによって使用されます。

B4以下の表をフィルタリングするリストを含むフィルタリング セクションを設定したいと思います。スクリーンショットは、リストの値がセル、C4、にありD4、以下の表が入力値に基づいてフィルタリングされる設定案を示してE4います。

これを実現する方法、または同様のことを実現する方法を誰か知っていますか?

ここに画像の説明を入力してください
（画像をクリックすると拡大します）

答え1

組み込みのテーブル フィルタリングではなく、特定のセル値でフィルタリングしたいということですか?

マクロを使用してオートフィルターを呼び出し、セルの値を基準として使用することができます。

マクロをシートに配置して、関連するセルの 1 つがサブによって変更されるたびに、マクロが自動的に呼び出されるようにすることができますWorksheet_Change。

これを書く方法はたくさんありますが、フィルタリングするテーブルを指定する必要があり、またフィルタリングする行も指定する必要があります。

これは、私の例のためにシートに入力したコードです (シート タブを右クリックして [コードの表示] を選択)。

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tabl As String, i As Long, C as Range
tabl = "Table1" 'Name of the Table
If Not Application.Intersect(Range(Target.Address), Range("B4:E4")) Is Nothing And Target.Count < 5 Then
    For Each C In Target
        tCol = Range(tabl & "[" & C.Offset(-1).Value2 & "]").Column
        If C.Value2 = "" Then
            ListObjects(tabl).Range.AutoFilter Field:=tCol
        Else
            ListObjects(tabl).Range.AutoFilter Field:=tCol, Criteria1:=C.Value2
        End If
    Next C
End If
End Sub

結果：

ここに画像の説明を入力してください

編集
すべてのフィルターを一度にクリアできるようにループを追加しました。

このコードでは、入力セルとテーブル内のヘッダーが同じである必要があります。そうでない場合はエラーが発生します。

-

Worksheet_Changeワークシート上のセルが変更されたときに呼び出されます。
これは、特定の領域が対象でない限りサブが実行されないように制限するために、 と組み合わせて使用​​されることがよくあり
If Not Application.Intersect(Range(Target.Address), Range("")) Is Nothing Then
ます。

コードがクラッシュしないように、制限を設けるのTarget.Countも良い考えです。この場合のように選択をループするつもりがない限り、多くの場合、単一のターゲットに制限する必要があります。

オプション付きのドロップダウン リストが必要な場合は、次の VBA アプローチがあります。Excel でデータの検証により重複データを削除する

答え2

いくつかの条件に基づいて複数の行を抽出するのに役立つ配列 (CSE) 数式を提案します。

ここに画像の説明を入力してください

使い方：

  • ソース データは範囲内にあると想定しています A2:E10。
  • 基準範囲は ですA16:E16。
  • この数式をセルに入力しA20、 Ctrl+Shift+Enter右に塗りつぶしてから下に塗りつぶします。

{=IFERROR(INDEX($A$2:$E$10,SMALL(IF(MMULT(($A$2:$E$10=$A$16:$E$16)*1,{1;1;1;1;1;1;1})=COUNTA($A$16:$E$16),MATCH(ROW($A$2:$A$10),ROW($A$2:$A$10)),""),ROWS($A$1:A1)),COLUMNS($A$1:A1)),"")}

注記：

  • 条件セルが空白の場合、この数式はすべてのレコードを返します。

ここに画像の説明を入力してください

  • 関連するレコードをフィルタリングするために、1 つ以上の条件を設定できます。

ここに画像の説明を入力してください

コマンドの仕組みを説明します。

  • MMULT functionブール値では動作しないため、これを動作させるには、Formula で配列に 1 を掛ける必要があります。

MMULT(($A$2:$E$10=$A$16:$E$16)*1,{1;1;1;1;1;1;1})

となり、

MMULT({0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 1, 0;0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 0, 0;0, 0, 1, 0, 0, 1, 0;0, 0, 1, 0, 0, 0, 0;0, 0, 1, 0, 0, 0, 0;0, 0, 1, 0, 0, 1, 0;0, 0, 1, 0, 0, 0, 0},{1;1;1;1;1;1;1})

そして戻り、

{0;0;1;0;0;2;1;1;2;1}

そして、

MMULT(($A$2:$E$10=$A$16:$E$16)*1, {1;1;1;1;1;1;1})=COUNTA($A$16:$E$16)

となり、

{0;0;1;0;0;2;1;1;2;1}=COUNTA($A$16:$E$16)

となり、

{0;0;1;0;0;2;1;1;2;1}=2

そして戻り、

{FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}.

IF(MMULT(($A$2:$E$10=$A$16:$E$16)*1, {1;1;1;1;1;1;1})=COUNTA($A$16:$E$16), MATCH(ROW($A$2:$A$10), ROW($A$2:$A$10)), "")

となり、

IF({FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}, MATCH(ROW($A$2:$A$10), ROW($A$2:$A$10)), "")

  • 小さい関数は配列内の k 番目に小さい数値を取得します。

  • 索引関数は、行と列の番号に基づいて、セル範囲または配列から値を返します。

関連情報