搜索多列的 Google 表格查询 [英] Google Sheets query that searches multiple columns

问题描述

我目前有一个从教程中获得的搜索功能，我想知道如何修改它，以便当我在 B1 中输入关键词时，它会在研讨会选项卡上的 A 到 G 列中搜索关键词仅列 D.我将当前拥有的代码留在下面:

=QUERY(Workshop!$A$1:$G,"SELECT * WHERE LOWER(D) LIKE LOWER(""%" &JOIN("%"") AND LOWER(D) LIKE LOWER(""%", SPLIT($B$1," "))&"%"")",1)

这是示例电子表格的链接:

ofc 有 20 多个级别，您希望搜索的严格程度或智能程度...

I currently have a search function I got from a tutorial and I'm wondering how I can modify it so that when I type key words into B1 it will search for the key words in columns A thru G on the workshop tab instead of just column D. I left the code I currently have below:

=QUERY(Workshop!$A$1:$G,"SELECT * WHERE LOWER(D) LIKE LOWER(""%" &JOIN("%"") AND LOWER(D) LIKE LOWER(""%", SPLIT($B$1," "))&"%"")",1)

Here is a link to the sample spreadsheet:

https://docs.google.com/spreadsheets/d/1nCNMpe2peJUWerUP8fgjMHxTiIwgUyZoM5GAW1PnoQo/edit?usp=sharing

I tried replacing the "D" with "A:G" and I also tried replacing it with "A,B,C,D,E,F,G" and neither worked. I don't know what else to try, I am still so new to this! I couldn't find anything on Google about it either!

解决方案

instead of searching through all columns you can collapse them into one and search it there and then not include it into the output:

=ARRAY_CONSTRAIN(IFERROR(QUERY({Workshop!A:G, 
 TRANSPOSE(QUERY(TRANSPOSE(Workshop!A:G),,99^99))}, 
 "where lower(Col8) contains '"&LOWER(B1)&"'", 1)), 99^99, COLUMNS(A:G))

ofc there are like 20+ levels of how strict or smart you want that search to be...

这篇关于搜索多列的 Google 表格查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！