在指定日期区间内查询判断是否有相近的值

coee · 发表于 2024-6-30 08:23

沈默00 发表于 2024-6-30 07:12
=IFERROR(TOROW(FILTER($H$3:$H$32,(ABS($H$3:$H$32-$A3)

谢谢，2019测试不到

hxj119 · 发表于 2024-6-30 08:53

本帖最后由 hxj119 于 2024-6-30 08:58 编辑

OFFICE 365:
=LET(x,FILTER($H$3:$I$32,($H$3:$H$32>(A3-7))*($H$3:$H$32<(A3+7))),a,CHOOSECOLS(x,1),b,CHOOSECOLS(x,2),c,TOROW(FILTER(a,ABS(b-B3)<100)),IFERROR(TEXTJOIN(",",,TEXT(c,"e/m/d")),"无"))

hxj119 · 发表于 2024-6-30 09:05

低版本：
=CONCAT(IF(($H$3:$H$32>A3-7)*($H$3:$H$32<A3+7)*(ABS($I$3:$I$32-B3)<100),TEXT($H$3:$H$32,"e/m/d,"),""))

mjm04 · 发表于 2024-6-30 09:45

=TEXTJOIN(",",,FILTER($H$3:$H$32,($A3>=($H$3:$H$32-7))*($A3<=($H$3:$H$32+7))*($B3>=($I$3:$I$32-100))*($B3<=($I$3:$I$32+100)),"无"))

quqiyuan · 发表于 2024-6-30 12:03

coee 发表于 2024-6-30 08:21
结果最后边的逗号怎么去除好些？

=IFERROR(LEFT(CONCAT(TEXT(IF((ABS($H$3:$H$32-A3)<7)*(ABS($I$3:$I$32-B3)<100),$H$3:$H$32,""),"e/m/d,")),LEN(CONCAT(TEXT(IF((ABS($H$3:$H$32-A3)<7)*(ABS($I$3:$I$32-B3)<100),$H$3:$H$32,""),"e/m/d,")))-1),"无")

象山海鲜 · 发表于 2024-6-30 16:07

=SUBSTITUTE("无"&CONCAT(TEXT(H$3:H$32*(ABS(H$3:H$32-A3)<7)*(ABS(I$3:I$32-B3)%<1),",e/m/d;;")),"无,",,1)

复制代码

剑指E · 发表于 2024-6-30 18:01

=MID(TEXT(0&TEXTJOIN("、",,IF(MMULT(--(ABS(H$3:I$32-A3:B3)<{7,100}),{1;1})=2,TEXT(H$3:H$32,"e/m/d"),"")),";; 无;@"),2,99)

复制代码

		自动登录	找回密码
密码			免费注册

[求助] 在指定日期区间内查询判断是否有相近的值