☰

仅使用公式按部分字符串过滤具有重复条目的多列列表

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 条件为真（请注意如果没有 '别的' 值，它将只是错误的条件不成立）

    • 逻辑测试：ISNUMBER(搜索($D$3&"/*";$B$4:$B$14))返回 True 和 False 的数组，具体取决于 SEARCH 是否为范围 $B$4:$B$15 中给定的每个单元格产生数值。
      上例的结果是：
      1. 搜索：1、2、1、#值、2、#值、2、1、1、#值、1
      2. 编号：真，真，真，假，真，假，真，真，真，真，假，真
      3. 如果为真，则值：ROW($B$4:$B$14)) 返回由数组 $B$4:$B$14 的行号填充的数组，
        上例的结果为：4、5、6、7、8、9、10、11、12、13、14

    如果将结合逻辑测试#1 和如果为真则为值#2。当数组 #2 中相同位置的 #2 为 FALSE 时，#1 中的所有值都将被忽略。最后，我们有一个数组，指示在 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));
          "")}

以下是它的作用：

  • COUNTIF(参数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));
        "")}

以下是它的作用：

  • COUNTIF(参数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 列（但我今天不会这么做）。

相关内容