在Google Sheets中进行搜索/筛选/查找

huangapple go评论61阅读模式
英文:

Searching/Filtering/Finding in Google Sheets

问题

我正在尝试为我的教堂的排班表创建一个搜索功能。我希望最终的效果如下,但还没有弄清楚哪些公式适用于它... 在Google Sheets中进行搜索/筛选/查找

我的教堂有三种不同的礼拜仪式,分别是上午10:30、赞美和不定期的仪式。我已经将每种仪式的活动分开,因为我每种仪式都有一个工作表,而不是都在同一个工作表上。因此,我认为这种分开的方式会比像这样做更容易: 在Google Sheets中进行搜索/筛选/查找。不过,如果这实际上比我上面设想的更容易,欢迎告诉我。

这个问题稍微比原来的计划复杂一点,因为我想要搜索的数据在三个不同的工作表中: 在Google Sheets中进行搜索/筛选/查找

现在,我认为FILTER不适用的原因是,这个排班表的形状类似于传统的表格,顶部和左侧都有标题,数据位于中间。据我所知,FILTER用于当您想要搜索标题并返回与标题相关联的数据时,而我想要做的是相反的。我想要搜索数据并返回与该数据相关联的标题。 在Google Sheets中进行搜索/筛选/查找

是否有人知道有什么可以帮助我的方法?

我已经进行了多次谷歌搜索并阅读了关于FILTER、SEARCH、FIND和MATCH函数的多篇文章,但认为它们都不适用于这种情况。SEARCH、FIND和MATCH我都排除在外,因为它们似乎返回一个表示在字符串中找到项的位置的数字,而FILTER我排除在外,因为虽然它可以从匹配周围的单元格中返回数据,并且能够返回多个匹配项,但您只能搜索第一行或列。

英文:

So, I'm trying to create a search feature for my church's rota spreadsheet. I want it to eventually look as below, but haven't worked out what formulas would work for it...在Google Sheets中进行搜索/筛选/查找

My church has three different services it operates, 10.30am, Praise, and then irregular services. I have separated out the activities for each service because I have one sheet for each service, instead of them all being on the same sheet. Therefore I figured it would be easier to split it out this way instead of doing something like this: 在Google Sheets中进行搜索/筛选/查找. However, if this would actually be easier than what I envisioned above, feel free to tell me.

The reason this is slightly more complicated than it was already going to be is that I have the data I want to search in three separate worksheets: 在Google Sheets中进行搜索/筛选/查找.

Now, the reason I don't believe FILTER would work is that this rota is shaped like a traditional table, with headers along the top and left hand side of the table, and the data in the middle. As far as my understanding reaches, FILTER works when you want to search the headers and bring back the data attached to the header, whereas I want to do it the other way around. I want to search the data and bring back the headers attached to that data. 在Google Sheets中进行搜索/筛选/查找

Does anyone know anything that could help me with this?

I have done multiple Google searches and read numerous articles on the FILTER, SEARCH, FIND and MATCH functions respectively, but believe none of them to be applicable in this situation. SEARCH, FIND and MATCH I have discarded because they seem to return a number representing where in a string the item was found, and FILTER I have discarded because, while it does bring back data from the cells around matches, and is capable of bringing back multiple matches, you can only search the first row or column.

答案1

得分: 0

你可以尝试:

=let(Σ,reduce(wraprows(,3,),{"10.30am";"Praise";"Irregular"},lambda(a,c,{a; index(iferror(split(tocol(if(indirect("'"&c&"'!B2:CZ5")=I4,indirect("'"&c&"'!B1:CZ1")&"|"&c&"|"&indirect("'"&c&"'!A2:A5"),),1),"|"),wraprows(,3,)))})), query(Σ,"Where Col3!='' order by Col1 format Col1 'ddd, dd mmmm yyy'"))

  • 首先 为了使此公式正常工作,请确保所有3个标签中的列分配相同,即直到 CZ(当前 10.30am 扩展到 CFIrregular 扩展到 Z...)
  • 您的 A2:A5 值似乎只在 Irregular 标签中是正确的,而其他两个标签中的 A2:A5 有些不同,您可能需要检查一下。
英文:

You may try:

=let(Σ,reduce(wraprows(,3,),{"10.30am";"Praise";"Irregular"},lambda(a,c,{a;
       index(iferror(split(tocol(if(indirect("'"&c&"'!B2:CZ5")=I4,indirect("'"&c&"'!B1:CZ1")&"|"&c&"|"&indirect("'"&c&"'!A2:A5"),),1),"|"),wraprows(,3,)))})),
     query(Σ,"Where Col3!='' order by Col1 format Col1 'ddd, dd mmmm yyy'"))
  • first and foremost for this formula to work w/out issues, please allocate equal dose of columns in all 3 tabs i.e., till CZ (right now 10.30am extends till CF; Irregular till Z...)
  • Your A2:A5 values seem to be right only in Irregular tab while the other 2 tabs A2:A5 is slightly varying; which you may need to check upon

在Google Sheets中进行搜索/筛选/查找

huangapple
  • 本文由 发表于 2023年6月5日 20:08:15
  • 转载请务必保留本文链接:https://go.coder-hub.com/76406266.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定