如何在Google工作表/Excel中使用搜索列表过滤字符串数据?

kpbwa7wx  于 2023-01-27  发布在  Go
关注(0)|答案(3)|浏览(178)

如果地址(A列)包含城市列表(B列)中的任何城市名称,我想对其进行过滤。数据如下所示:
| 地址(A栏)|城市(B栏)|
| - ------|- ------|
| 美国加利福尼亚州比尔顿900 Rancho Diego Pkwy,邮编92028|文图拉|
| 德克萨斯州安东尼奥市托珀温路3850号,邮编:78264|圣安东尼奥|
| 美国加利福尼亚州阿塔斯卡德罗3250 El Camino Real,邮编93444|比尔顿|
| 加利福尼亚州文图拉市帕尔马大道2340号,邮编:93090|纽约|
| 加利福尼亚州圣玛丽亚Valley Rd,邮编:93420||
在上表中,A列的地址中只找到B列的2个城市(Ventura, Buellton),因此输出应为:
美国加利福尼亚州比尔顿900 Rancho Diego Pkwy,邮编92019
加利福尼亚州文图拉市帕尔马大道2340号,邮编:93003
我试了以下公式,但没有效果:

=FILTER(A2:A, REGEXMATCH(A2:A, B2:B))
=FILTER(A2:A, MATCH(A2:A, B2:B,0))
=FILTER(A2:A, COUNTIF(B2:B, A2:A))

如果地址中有城市名称(B栏),如何编制地址列表(A栏)?如有任何帮助,将不胜感激。

y3bcpkx1

y3bcpkx11#

请尝试:

更新公式:

  • 这严格地提取地址内的城市名称来与列B中的列表进行核对,以避免随机失配。

=FILTER(A2:A,REGEXMATCH(BYROW(A2:A,LAMBDA(ax,IF(ax="",,IFNA(IFNA(REGEXEXTRACT(ax,", ([^,]*), [A-Z]{2}"),REGEXEXTRACT(ax,", ([^,]*), [^,]*$")))))),"(?i)"&TEXTJOIN("|",1,B2:B)))

9w11ddsr

9w11ddsr2#

FILTER()Excel中的MMULT()一起使用

·单元格E3中使用的公式

=FILTER(B3:B7,MMULT(N(ISNUMBER(SEARCH(TRANSPOSE(C3:C6),B3:B7))),ROW(C3:C6)^0))

也可以在Google-Sheets中使用:

·单元格E3中使用的公式

=FILTER(B3:B7, MMULT(N(ISNUMBER(SEARCH(TRANSPOSE(C3:C6),B3:B7))),SEQUENCE(ROWS(C3:C6),1,1,0)))

使用BYROW()的替代方法

·单元格E3中使用的公式

=FILTER(B3:B7,BYROW(N(ISNUMBER(SEARCH(TOROW(C3:C6),B3:B7))),LAMBDA(m,SUM(m))))

对于Google-Sheets,请将TOROW()替换为TRANSPOSE()

·单元格E3中使用的公式

=FILTER(B3:B7,BYROW(N(ISNUMBER(SEARCH(TRANSPOSE(C3:C6),B3:B7))),LAMBDA(m,SUM(m))))

最好使用Table Structured References,而不是获取所有行

·单元格E3中使用的公式

=LET(_cityName,FILTER(C3:C1048576,C3:C1048576<>""),
_addresses,FILTER(B3:B1048576,B3:B1048576<>""),
FILTER(_addresses,MMULT(N(ISNUMBER(SEARCH(TRANSPOSE(_cityName),_addresses))),SEQUENCE(ROWS(_cityName)))))

带有BYROW()

·单元格E3中使用的公式

=LET(_cityName,FILTER(C3:C1048576,C3:C1048576<>""),
_addresses,FILTER(B3:B1048576,B3:B1048576<>""),
_searchArray,N(ISNUMBER(SEARCH(TOROW(_cityName),_addresses))),
FILTER(_addresses,BYROW(_searchArray,LAMBDA(m,SUM(m)))))

或者,使用LAMBDA()公式在Name Manager中创建一个Defined Name,并根据需要使用该范围。

    • 指:**
=LAMBDA(Cities,Addresses,
LET(_cityName,FILTER(Cities,Cities<>""),
_addresses,FILTER(Addresses,Addresses<>""),
_searchArray,N(ISNUMBER(SEARCH(TOROW(_cityName),_addresses))),
FILTER(_addresses,BYROW(_searchArray,LAMBDA(m,SUM(m))))))(C3:C1048576,B3:B1048576)

·单元格E3中使用的公式

=LISTDATA(C3:C1048576,B3:B1048576)

单位:Google-Sheets

=FILTER(B3:B,BYROW(N(ISNUMBER(SEARCH(TRANSPOSE(FILTER(C3:C,C3:C<>"")),B3:B))),LAMBDA(m,SUM(m))))
swvgeqrz

swvgeqrz3#

你跟这事很亲密

=FILTER(A2:A, REGEXMATCH(A2:A, B2:B))

REGEXMATCH的第二个参数不应该是一个范围,这将工作:

=FILTER(A2:A,REGEXMATCH(A2:A,TEXTJOIN("|",1,B2:B)))

相关问题