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

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

excel-formula excel

Michi · 技术社区 · 9 月前

0	A.	C	E	F	H	我	K	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列的表格中看到步骤,以便更容易地按照说明进行操作)

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

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

1 回复 | 直到 9 月前

Mayukh Bhattacharya 9 月前

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

=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))

也带有标头:

=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)))