代码之家  ›  专栏  ›  技术社区  ›  Michi

使用多个列和行条件对数据进行筛选和排序,并使用灵活的return_array将相应的数据与其匹配

  •  2
  • Michi  · 技术社区  · 1 周前
    0 A. B C D E F G H J K L M N O P
    1. 产品 商店 2023-S 2023万 2024-S 2024-M 类型
    2.
    3. 500
    4. P-线路_A 步骤1
    5. 产品_A 车间3 80 2. 500 22% t1 ColCrit1 ColCrit2 ColCrit3
    6. 产品_B 车间2 320 23% 180 60% t1 产品_D 车间1 t3
    7. 产品_B 车间1 90 8. 300 36% t2 产品_G 车间3 t4
    8. 产品_C 车间3 500 15% 657 16% t1 车间4
    9 产品_D 车间1 160 17% 500 15% t3
    10 产品_D 车间1 500 30% 600 8. t3 rowCrit
    11 产品_D 车间1 130 4. 300 4. t3 2024-S
    12 全部的 1810 3037
    13
    14 600 步骤二
    15 300 _数组 _阵列1 _阵列2 _阵列3
    16 P线_B 2024-S 产品 类型 商店
    17 产品_E 车间2 75 10% 450 10% t1
    18 产品_F 车间4 60 8. 370 4. t2
    19 产品_F 车间1 500 11% 850 4. t2 步骤三
    20 产品_G 车间3 350 8. 150 15% t3 开始
    21 产品_G 车间3 60 47% 600 7. t4 10 2.
    22 产品_G 车间4 90 25% 830 35% t4
    23 产品_G 车间2 390 9% 325 13% t1
    24 产品_G 车间3 90 30% 300 9% t3 步骤四
    25 产品_G 车间4 90 5. 300 12% t3 产品 类型 商店 2024-S
    26 产品_G 车间4 120 24% 300 24% t4 产品_D t3 车间1 600
    27 产品_G 车间4 135 35% 480 17% t4 产品_G t4 车间3 600
    28 产品_H 车间2 935 27% 230 16% t1 产品_D t3 车间1 500
    29 产品_I 车间3 134 18% 600 42% t2 产品_G t4 车间4 480
    30 全部的 3029 5785 产品_D t3 车间1 300
    31 产品_G t3 车间3 300
    32 产品_G t3 车间4 300
    33 产品_G t4 车间4 300
    34 产品_G t3 车间3 150
    35
    36

    目标

    最终输出为 Range M26:P34 它是基于的降序列表 lookup_array 在里面 Cell M16 以及多个列和行筛选器( Range M6:M8, Range N6:O8 , Range O6:O8 Cell M11 )以及基于 Cell M21 Cell N21 .

    要获得此列表,需要执行以下步骤:
    (您也可以在M列的表格中看到步骤,以便更容易地按照说明进行操作)

    1. 在中定义多列条件 Range M6:O6 和中的一行标准 单元格M11 。这些标准都是 AND 筛选列表后,应按降序排列。
    2. 基于步骤1,您将有一个满足列和行条件的降序列表。对于此列表,相应的值基于 ret_array1 在里面 Cell N16 , ret_array2 在里面 Cell O16 ret_array3 在里面 Cell P16 应添加。
      笔记 查找数组 = rowCrit --> 单元格M16 = 单元格M11 )
    3. 从第2步中的列表中,仅根据位置进行排名的值 单元格M21 单元格N21 应显示。
      在这种情况下 10 positions 之后 1st position .
    4. 在步骤1-3之后的步骤4中,最终列表应该出现。

    与中的答案相比 this question 我添加了一个额外的 return_array3 在里面 P16细胞 .
    我尝试将此附加数组实现为这样的公式:

    =LET(
        _a; COUNTIF(M6:M8;A1:A31)+AND(M6:M8="");
        _b; COUNTIF(N6:N8;C1:C31)+AND(N6:N8="");
        _c; COUNTIF(O6:O8;K1:K31)+AND(O6:O8="");
        _d; CHOOSECOLS(A1:K31;XMATCH(HSTACK(N16;O16;P16;M16);A1:K1));
        _e; SORT(FILTER(HSTACK(_d;FILTER(A1:K31;A1:K1=M11;""));_a*_b*_c;"");3;-1);
        WRAPROWS(TOCOL(INDEX(_e;SEQUENCE(M21;;N21);SEQUENCE(;3));2);3))
    

    然而,现在2024-S的值不再出现,排序也有所不同:

    enter image description here

    我需要如何修改公式才能使其工作?

    1 回复  |  直到 1 周前
        1
  •  0
  •   Mayukh Bhattacharya    1 周前

    尝试使用以下内容,这是动态的:

    enter image description here


    =LET(
     _a, COUNTIF(M6:M8,A1:A31)+AND(M6:M8=""),
     _b, COUNTIF(N6:N8,C1:C31)+AND(N6:N8=""),
     _c, COUNTIF(O6:O8,K1:K31)+AND(O6:O8=""),
     _d, TOROW(HSTACK(N16,O16,P16,M16),1),
     _e, CHOOSECOLS(A1:K31,XMATCH(_d,A1:K1)),
     _f, COLUMNS(_d),
     _g, SORT(FILTER(HSTACK(_e,FILTER(A1:K31,A1:K1=M11,"")),_a*_b*_c,""),_f+1,-1),
     WRAPROWS(TOCOL(INDEX(_g,SEQUENCE(M21,,N21),SEQUENCE(,_f)),2),_f))
    

    也带有标头:

    enter image description here


    =LET(
        _a, COUNTIF(M6:M8,A1:A31)+AND(M6:M8=""),
        _b, COUNTIF(N6:N8,C1:C31)+AND(N6:N8=""),
        _c, COUNTIF(O6:O8,K1:K31)+AND(O6:O8=""),
        _d, TOROW(HSTACK(N16,O16,P16,M16),1),
        _e, CHOOSECOLS(A1:K31,XMATCH(_d,A1:K1)),
        _f, COLUMNS(_d),
        _g, SORT(FILTER(HSTACK(_e,FILTER(A1:K31,A1:K1=M11,"")),_a*_b*_c,""),_f+1,-1),
        VSTACK(_d, WRAPROWS(TOCOL(INDEX(_g,SEQUENCE(M21,,N21),SEQUENCE(,_f)),2),_f)))