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

将值与包括多列条件的动态列表匹配并列出

excel-formula excel

Michi · 技术社区 · 11 月前

0	A.	C	E	F	H	我	K	L	N
1.	产品	商店	2023-S	2023万	2024-S	2024-M
2.
3.	产品A	车间3	80	2.	500	22%	ColCrit1	ColCrit2	查找数组
4.	产品B	车间2	320	23%	180	60%	产品D	车间1	2024-s
5.	产品B	车间1	90	8.	300	36%	产品G	车间3	return_array
6.	产品C	车间3	500	15%	657	16%			产品
7.	产品D	车间1	160	17%	500	15%
8.	产品D	车间1	500	30%	600	8.	列表	火柴
9	产品D	车间1	130	4.	300	4.	830	产品G
10	产品E	车间2	75	10%	450	10%	600	产品D
11	产品F	车间4	60	8.	370	4.	600	产品G
12	产品F	车间1	500	11%	850	4.	500	产品D
13	产品G	车间3	350	8.	150	15%	300	产品D
14	产品G	车间3	60	47%	600	7.	300	产品G
15	产品G	车间3	90	25%	830	35%	300	产品G
16	产品G	车间2	390	9%	325	13%	150	产品G
17	产品G	车间3	170	30%	300	9%
18	产品G	车间3	320	5.	300	12%
19	产品H	车间2	935	27%	230	16%
20	产品I	车间3	134	18%	600	42%

在里面 Range K9:K16 所有值来自 Column H 其完全填充了中的列标准 Range K4:K6 和 Range L4:L6 根据以下公式列出:

=SORT(
    LET(
    a;COUNTIF(K4:K6;A1:A20)+AND(K4:K6="");
    b;COUNTIF(L4:L6;C1:C20)+AND(L4:L6="");
    FILTER(FILTER(A1:J20;(A1:J1=N4);"");a*b;""));;-1)

在里面 Range L9:L16 我想:

将相应的值与中的结果进行匹配 范围K9:K16 和
作为变量输入的列 return_array 在里面 Cell N6 .

来自的公式 this question 已经接近结果:

选项1

=CHOOSECOLS(SORT(FILTER(CHOOSECOLS(A3:I20;XMATCH(N4;A1:I1);XMATCH(N6;A1:I1));COUNTIFS(K4:K6;A3:A20;L4:L6;C3:C20));;-1);2)

选项2

=LET(
     a, K9:K16,
     b, A1:I1,
     c, A3:I20,
     d, XLOOKUP(N4,b,c,""),
     MAP(a,LAMBDA(Î±, @DROP(TOCOL(FILTER(IFS((d=Î±)*
                    (1-ISNA(XMATCH(A3:A20,K4:K6)+XMATCH(C3:C20,L4:L6))),
                    IF(c=0,x,c)),N6=b,""),3),
     COUNTIF(K9:Î±,Î±)-1))))

然而,例如 量程K4:K6 或 范围L4:L6 为空,并且范围中的列表 K9:K16 调整为两个公式 范围L9:L16 返回错误 #CALC! ?

我需要如何修改它们以使它们根据中的列表进行调整 量程K4:K6 ?

2 回复 | 直到 11 月前

P.b 11 月前

我想你的意思是,如果空的比列出所有? 那样的话 =SORT(FILTER(CHOOSECOLS(A3:I20,XMATCH(N4,A1:I1),XMATCH(N6,A1:I1)),(AND(K4:K6="")+COUNTIF(K4:K6,A3:A20))*(AND(L4:L6="")+COUNTIF(L4:L6,C3:C20)),""),,-1)

或

=LET(x,LAMBDA(a,b,AND(a="")+COUNTIF(a,b)),
SORT(FILTER(CHOOSECOLS(A3:I20,XMATCH(N4,A1:I1),XMATCH(N6,A1:I1)),x(K4:K6,A3:A20)*x(L4:L6,C3:C20)),,-1))

Mayukh Bhattacharya 11 月前

也许我还没有弄清楚,如果两者 ColCrits 为空,那么为什么不返回基于 lookup_array 和 return_array 同时匹配 list 因此,在不偏离其他条件的情况下,返回整列H如何帮助?:

=LET(
     a, K9:K16,
     b, A1:I1,
     c, A3:I20,
     d, XLOOKUP(N4,b,c,""),
     e, (1-ISNA(XMATCH(A3:A20,K4:K6)*XMATCH(C3:C20,L4:L6))),
     MAP(a,LAMBDA(Î±, @DROP(TOCOL(FILTER(IFS(IF(SUM(e)=0,(d=Î±),(d=Î±)*e),
                                            IF(c=0,x,c)),N6=b,""),3),
     COUNTIF(K9:Î±,Î±)-1))))

使现代化

=LET(
     a, K9:K16,
     b, A1:I1,
     c, A3:I20,
     d, XLOOKUP(N4,b,c,""),
     e, (1-ISNA(XMATCH(A3:A20&"|"&C3:C20,K4:K6&"|"&L4:L6))),
     IFERROR(MAP(a,LAMBDA(Î±, @DROP(TOCOL(FILTER(IFS(IF(SUM(e)=0,(d=Î±),(d=Î±)*e),
                                                    IF(c=0,x,c)),N6=b,""),3),
     COUNTIF(K9:Î±,Î±)-1))),""))