查询问题

yangqianming

我想查询日期列中的两天对应餐谱，想着应该是循环查询但写不出来，
在查询表已经做好了结果数据，请大神相助，谢谢！

查询.rar (24.8 KB, 下载次数: 24)

2025-4-13 19:25 上传

点击文件名下载附件

quqiyuan

1. =FILTER(餐谱!$A$2:$H$241,MMULT(1*(餐谱!$A$2:$A$241=TEXT(TRANSPOSE(A2:A3),"aaaa")),{1;1}))

复制代码

lyj344600

=REDUCE({"星期","餐次","餐别","食谱名称","具体食材","数量（克）","单价（元/克）","总价（元）"},TRIMRANGE(A2:A1000),LAMBDA(r,t,IF(TEXT(t,"aaaa")="星期六",r,VSTACK(r,FILTER(餐谱!$A$2:$H$241,餐谱!$A$2:$A$241=TEXT(t,"aaaa"))))))

gwjkkkkk

1. =REDUCE(餐谱!A1:H1,A2:A3,LAMBDA(x,y,VSTACK(x,FILTER(餐谱!A1:H241,餐谱!A1:A241=TEXT(y,"aaaa")))))

复制代码

xtmyd5156

yangqianming

gwjkkkkk 发表于 2025-4-13 19:45

厉害！非常感谢！

家有囡囡

=IFNA(REDUCE(餐谱!A1:H1,TRIMRANGE(A2:A31),LAMBDA(x,y,VSTACK(x,FILTER(餐谱!A:H,餐谱!A:A=TEXT(y,"aaaa"),"")))),"")
你的清单里没有星期六的菜单，公式生成的数组里碰到周六会空一行

mykitami

循环能不用就不用
=FILTER(餐谱!A2:.H999,ISNUMBER(MATCH(餐谱!A2:.A999,TEXT(A2:.A99,"aaaa"),)))

mykitami

如果就是想用循环，参考如下
=DROP(REDUCE("",UNIQUE(TEXT(A2:.A99,"aaaa")),LAMBDA(x,y,LET(_a,FILTER(餐谱!A2:.H999,餐谱!A2:.A999=y,""),IF(@_a="",x,VSTACK(x,_a))))),1)

mjm04

=DROP(REDUCE("",WEEKDAY(A2:A3,2),LAMBDA(a,b,VSTACK(a,FILTER(餐谱!A2:H241,b=XLOOKUP(RIGHT(餐谱!A2:A241),{"一";"二";"三";"四";"五";"六";"日"},{1;2;3;4;5;6;7}))))),1)