☰

僅使用公式按部分字串過濾具有重複條目的多列列表

tag-icon • tag-icon microsoft-excel worksheet-function
僅使用公式按部分字串過濾具有重複條目的多列列表

是否可以透過部分字串過濾數組，並使用單一公式僅列出一次多個條目（無需自動過濾器、VBA 或附加列）例如，我有以下電子表格：

A | B  | C        | D       | F  
-----------------------------------  
01| ID | Class    | Value   | Filter
02| 1  | A/as     | V1      |
03| 1  | B/as     | V2      |
04| 2  | A/ab     | V3      |
05| 3  | B/ab     | V4      |
06| 3  | B/as     | V5      |
07| 2  | B/ab     | V5      |
08| 1  | A/as     | V5      |
09| 3  | A/ab     | V5      |

如果我過濾 B 列班級「A/*」但只顯示其值一次，結果應該是：

A | B  | C        | D       | F    
-----------------------------------  
01| ID | Class    | Value   | Filter
02| 1  | A/as     | V1      | V1
03| 1  | B/as     | V2      | V3
04| 2  | A/ab     | V3      | V2
05| 3  | A/ab     | V1      | 
06| 3  | B/as     | V4      |
07| 2  | B/ab     | V5      |
08| 1  | A/as     | V2      |
09| 3  | A/ab     | V2      |

代替

A | B  | C        | D       | F   
-----------------------------------  
01| ID | Class    | Value   | Filter
02| 1  | A/as     | V1      | V1
03| 1  | B/as     | V2      | V3
04| 2  | A/ab     | V3      | V1
05| 3  | A/ab     | V1      | V2
06| 3  | B/as     | V4      | V2
07| 2  | B/ab     | V5      |
08| 1  | A/as     | V2      |
09| 3  | A/ab     | V6      |

按部分字串作品過濾列（改編那描述）以某種方式像這樣：

...
    IF(
                    ISNUMBER(Search("A/*"; $B$2:$B$9))  
...

這會產生一個包含列表 $B$2:$B$9 索引的陣列：

ISNUMBER(Search("A/*"; $B$2:$B$9): {1;0;1;1;0;0;1;1}

所以

IF(IS...): {1;3;4;8;9}

到目前為止，我還沒有找到任何方法將其與“唯一名單”方法結合起來

`MATCH(0;INDEX(COUNTIF(`

如上所述這裡

我所擁有的一點是，但它工作得不太好，並且導致相當大的CPU負載，例如單元C8

{=IFERROR(INDEX(
               INDEX($D$2:$D$9;
                     IF(ISNUMBER(SEARCH("A/*";$C$2:$C$9));
                        ROW($D$2:$D$9)-ROW($D$2)+1)));
               MATCH(0;
                     INDEX(COUNTIF($C$2:C7;
                                   INDEX($D$2:$D$9;
                                         IF(ISNUMBER(SEARCH("A/*";$C$2:$C$9));
                                            ROW($D$2:$D$9)-ROW($D$2)+1)));
                     0;0);0));
          "error")

答案1

我解決了這個問題，雖然不是很完美（它需要 3 列），但它的作用就像一個魅力。

A | B     | C     | D      | E       | F       | G      
-------------------------------------------------------  
01| Array |Array  | Text  | search   | search  | ordered
02| Source|Source | sought| results  | results |
03|   #1  |  #2   | *a*   |    #1    |    #2   |
04| aa    | c12   |       | c12      | c12     | c02
05| ca    | c13   |       | c13      | c13     | c06    
06| ad    | c06   |       | c06      | c06     | c12
07| ee    | c11   |       | c02      | c02     | c13
08| fa    | c02   |       | c06      | c25     | c25      
09| gg    | c12   |       | c13      |         |
10| ba    | c06   |       | c06      |         |
11| aa    | c13   |       | c25      |         |
12| ad    | c06   |       | #NUM!    |         |
13| gt    | c12   |       | #NUM!    |         |
14| aa    | c25   |       | #NUM!    |         |

E欄如果 B 列中的對應儲存格包含 D3 的表達式，則列出 C 列的所有項目。單元格 E4 中的公式複製到 E5-E14：

{=INDEX(C:C;
        SMALL(IF(ISNUMBER(SEARCH($D$3&"/*";
                                 $B$4:$B$14));
                 ROW($B$4:$B$14));
              ROWS($E$4:E4)))}

您必須按 ctrl-shift-enter 才能將公式作為數組輸入，但要小心，當您要查找大表時，這可能需要相當長的時間。我有1300個單元格，花了一分鐘多的時間，但只是輸入公式，將其複製到其他單元格沒有任何延遲。

這就是它的作用：

  • 指數 （精氨酸1,精胺酸2）將輸出項目/儲存格 n 的值（精胺酸2）C 列（精氨酸1）。 N 的計算公式為小的。
  • 小的（精氨酸1,精胺酸2）應該回第 k 個（精胺酸2）資料集中的最小值（精氨酸1）。
    此函數傳回資料集中具有特定相對位置的值。這正是正確使用所需要的如果和行列式，嵌套更深。

  • 如果 （邏輯測試,如果為真則值）是這個技巧的主要部分：它建立一個 IF 條件為 true 的行號數組（請注意如果沒有 '別的' 值，它將只是錯誤的如果條件不成立）

    • 邏輯測試：ISNUMBER(搜尋($D$3&"/*";$B$4:$B$14))傳回 True 和 False 數組，取決於 SEARCH 是否為 $B$4:$B$15 範圍內給定的每個單元格產生數值。
      上例的結果是：
      1. 搜尋:1, 2, 1, #VALUE, 2, #VALUE, 2, 1, 1, #VALUE, 1
      2. 編號：真，真，真，假，真，假，真，真，真，假，真
      3. value if true: ROW($B$4:$B$14)) 傳回由陣列 $B$4:$B$14 的行號填入的陣列
        上例的結果是： 4, 5, 6, 7, 8 , 9, 10, 11, 12, 13, 14

    如果將結合邏輯測試#1 和如果為真則值#2. #1 中的所有值都將被忽略，而 #2 在陣列 #2 中的相同位置處為 FALSE。最後我們有一個數組，指示在 B 列的哪一行，在給定的範圍內排（...）找到D3的表達。上例的結果為：4,5,6,8,10,11,12,14

  • 行($E$4:E4)只是一個技巧，它會給你一個遞增的數字（即 F2 中的 1，F3 中的 2...）。用於什麼小的作為精胺酸2。第一個單元格中的結果 (行（...）=1) 將是 4（最低值），第二個是 5，依此類推。最後，每個後續儲存格將顯示 B 列中找到 D3 表達式的行號/位置。

F欄過濾重複項，這是最困難的部分。 F 列將「僅」列出 E 列中列出的所有項目一次。
這就是F5輸入的公式！ （F4 與 E4 相同）使用 ctrl-shift-enter：

{=IFERROR(INDEX($C$2:$C$14;
                MATCH(0;
                      COUNTIF($E$4:E4;
                              $C$2:$C$14);
                      0));
          "")}

這就是它的作用：

  • 計數（精氨酸1,精胺酸2）結果是範圍長度的數組精氨酸1，用 1 表示其中條目的符合項目在哪裡精胺酸2。
    上例的結果為：1, 1, 0, 0, 0, 1, 0, 0, 0, 0, 0
  • 匹配（查找值,大批,比賽類型）在結果陣列中尋找第一次出現的 0計數 (...)上例的第三個位置是什麼
    • 查找值:0，第一個新值/非重複
    • 大批:結果數組計數 (...)
    • 比賽類型:0 = 完全正確
  • 指數（精胺酸2COUNTIF、Match(...))最終將顯示新的/不重複的值，該值位於範圍的第三個位置精胺酸2，上例的c06 是什麼。

G欄最後，所有項目將按字母順序排序。這是使用 ctrl-shift-enter 在 G4 中輸入的公式：

{=IFERROR(INDEX($F$4:$F$14;
                        MATCH(ROWS($G$4:$G4);
                              COUNTIF($F$4:$F$14;
                                      "<="&$F$4:$F$14);
                              0));
        "")}

這就是它的作用：

  • 計數（精氨酸1,精胺酸2）是技巧的主要部分：它比較中給出的文本值精胺酸2與給出的所有其他文本值精氨酸1並返回其相對排名（按字母順序）。
    上例的結果是：
    3, 4, 2, 1, 5
  • 行($E$2:E2)只是一個技巧，它會給你一個遞增的數字（即G2中的1，G3中的2......）。用於什麼匹配作為查找值。
  • 匹配（查找值,大批,比賽類型）尋找第一次出現的行（...）在結果數組中計數 (...)。
    上例的結果是：
    4, 3, 1, 2, 5
    1. 細胞：行（...）=1 => 4
    2. 細胞：行（...）=2 => 3
    3. 細胞：行（...）=3 => 1
    4. 細胞：行（...）=4 => 2
    5. 細胞：行（...）=5 => 5
  • INDEX(COUNTIF 的 arg1, Match(...))最終將顯示與其對應的排序條目行（...）結果。上例的結果是：
    1. 細胞：行（...）=1 => 4 英寸計數值數組 => c02
    2. 細胞：行（...）=2 => 3 英寸計數值數組 => c06
    3. 細胞：行（...）=3 => 1 英寸計數值數組 => c12
    4. 細胞：行（...）=4 => 2 英寸計數值數組 => c13
    5. 細胞：行（...）=5 => 5 英寸計數值數組=> c25

到目前為止，最後一步是將所有內容合併在一列中。至少找到了一些幫助合併列 F&G（但我今天不會這樣做）。

相關內容