☰

單一搜尋條件多個結果

tag-icon • tag-icon microsoft-excel-2007
單一搜尋條件多個結果

我需要僅使用單一搜尋條件從一列排序資料中輸出結果。有時同一標準會多次出現。 LOOKUP 只尋找第一個出現的位置。我需要在一個cell儲存格中輸入匹配is in J8:J581，對應顯示的資料是N8:N581

J   K L M N
bob       RED
bob       BLUE
Bob       Green
Sue       yellow
Sue       white
fred      grey
pete      brown

。

input=bob
output=    bob  RED
                BLUE
                Green

答案1

如果您只是想傳回與上面的範例類似的行，請使用 Excel 表格。

  1. 選擇您的數據
  2. 建立表Insert>Tables>Table（選取我的表有標題框）
  3. 建立表格後，只需選擇過濾器按鈕（表標題行中的向下箭頭）並選擇過濾器值（例如，在範例中選擇 Bob），這將只傳回“Bob”行。

這是之前的圖片：

桌子

之後：

在此輸入影像描述

編輯： 根據您的附加信息，我會考慮根據您的資料表添加資料透視表。這將允許您建立資訊的「概述」視圖，以便更好地了解哪些露營地只有一個 ID，哪些露營地有多個 ID。它看起來像這樣：

在此輸入影像描述

另外，@pnuts 請注意，Excel 的下拉（即篩選器）清單中的項目數限制為 10,000 個，但對錶成員沒有記錄的限制。我的表有 10,000 行。

編輯2：如果您只想輕鬆找到重複值，那麼資料透視表絕對是最佳選擇。

  1. 取得初始資料表並新增“計數器”行。 =IF([@Name]=D1,F1+1,1)這將傳回具有相同名稱的行數。
  2. Insert>Tables>Pivot Table根據您的資料建立資料透視表。
  3. 像這樣設定資料透視表的格式：
    • 行標籤 = Name、Code、Counter，並關閉所有值的小計和總計。
    • 過濾計數器 = 清除 1，這將只顯示具有多個值的名稱。
    • 過濾器名稱 = 您感興趣的任何特定露營地名稱。

過濾樞軸

答案2

假設您的輸入單元格是 A1，輸出單元格是 A3（名稱）和 B3:B..（輸出範圍）（B 範圍的末尾向下直至您期望看到的最大結果數） 。

對於A3，輸入公式=A1。

對於B3，輸入公式

  =IFERROR(INDEX($N$1:$N$99,SMALL(IF($J$1:$J$99=$A$3,ROW($N$1:$N$99)-ROW($N$1)+1),ROWS($N$1:$N1))),"")

這是一個陣列公式，需要使用Control- Shift-Enter組合鍵輸入。然後您可以將其複製到輸出範圍的底部。

請注意，該公式針對最多 99 行的輸入清單進行了硬編碼。您可以將其變更為您需要的任何長度。雖然可以引用整個列（J:J 和 N:N），但您可能希望避免效能下降。

公式如何運作

公式從內到外工作，首先將要執行查找的名稱（儲存格 A3）與完整名稱清單（J1:J99 範圍內最多 99 個名稱）進行比較。此比較顯示在下面所示的函數分解的第 6 行。

此比較的結果是一個數組，其中包含用於匹配的 True 值和用於不匹配的 False 值，例如，{False、False、False、False、True、True、False、...等}。

然後，將該陣列與可被視為名稱清單的「行號」的陣列進行比較：{1, 2, 3, 4, 5, 6, ... 99}。此比較是透過公式圖表第 6-8 行中的 IF 語句完成的。

比較是逐個元素進行的。如果名稱比較數組的某個元素等於 True，則 IF 會傳回其對應的行號；如果元素等於 False，則 IF 傳回 FALSE。使用上面的兩個範例數組，IF 語句的結果將是 {False, False, False, False, 4, 5, False, ...}。

匹配函數的元素

繼續，SMALL 函數（從函數大綱的第 8 行開始）用於從 IF 取得這個新陣列的第 k 個最小元素。本例中的「k」由表達式ROWS($N$1:$N1) 提供，當整個公式從第1 行向下複製到第99 行時，該表達式將簡單地從1 向上計數到99 ( ROWS($N$1 :$N1) = 1，ROWS($N$1:$N2) = 2，依此類推）。

所以，SMALL首先會找到IF產生的陣列中最小的元素，忽略為 False 的元素。換句話說，它將傳回正在比較的名稱與名稱查找清單中的名稱相符的第一行號。在我們的範例中，即數字 4，如下表第 6 列所示。

總結步驟在尋找值上使用 INDEX 來取得與已計算的行號相對應的元素。在本例中，問題的顏色清單範例中的第 4 項是「黃色」。 （IFERROR 確保當公式找不到匹配項時顯示空白。）

這是完整公式的第一個副本產生的結果。當它被複製到下一個單元格時，計算中唯一改變的是 SMALL 函數的“k”值，該值前進到 2。

在此輸入影像描述

相關內容