☰

使用表格外部儲存格中的資訊篩選表

tag-icon • tag-icon microsoft-excel
使用表格外部儲存格中的資訊篩選表

我有一個表格，可以從我的工作簿中的多個不同來源提取資訊。表格會定期篩選4個類別，供Excel不強的使用者使用。

我想設定一個過濾部分，其中包含過濾下表的清單。螢幕截圖顯示了建議的設置，其中列表值位於單元格B4、C4​​ 、中D4，E4下表是根據輸入值進行過濾的內容。

有誰知道如何完成這個或類似的事情？

在此輸入影像描述
（點擊圖片放大）

答案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）公式，將幫助您根據幾個標準提取多行。

在此輸入影像描述

怎麼運作的：

  • 我假設來源資料位於 Range 中 A2:E10。
  • 標準範圍是A16:E16。
  • 在 Cell 中輸入此公式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)),"")}

筆記：

  • 如果標準單元格為空，則此公式將傳回所有記錄。

在此輸入影像描述

  • 您可以將一種礦石放入多個條件來過濾相關記錄。

在此輸入影像描述

讓我解釋一下命令的機制。

  • MMULT function無法使用布林值，因此為了使其正常工作，公式必須將陣列乘以 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 個最小的數字。

  • 指數函數根據行號和列號從單元格範圍或陣列中傳回一個值。

相關內容