英文:
Searching/Filtering/Finding in Google Sheets
问题
我正在尝试为我的教堂的排班表创建一个搜索功能。我希望最终的效果如下,但还没有弄清楚哪些公式适用于它...
我的教堂有三种不同的礼拜仪式,分别是上午10:30、赞美和不定期的仪式。我已经将每种仪式的活动分开,因为我每种仪式都有一个工作表,而不是都在同一个工作表上。因此,我认为这种分开的方式会比像这样做更容易: 。不过,如果这实际上比我上面设想的更容易,欢迎告诉我。
这个问题稍微比原来的计划复杂一点,因为我想要搜索的数据在三个不同的工作表中: 。
现在,我认为FILTER不适用的原因是,这个排班表的形状类似于传统的表格,顶部和左侧都有标题,数据位于中间。据我所知,FILTER用于当您想要搜索标题并返回与标题相关联的数据时,而我想要做的是相反的。我想要搜索数据并返回与该数据相关联的标题。
是否有人知道有什么可以帮助我的方法?
我已经进行了多次谷歌搜索并阅读了关于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...
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: . 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: .
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.
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
扩展到CF
;Irregular
扩展到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., tillCZ
(right now10.30am
extends tillCF
;Irregular
tillZ
...)- Your
A2:A5
values seem to be right only inIrregular
tab while the other 2 tabsA2:A5
is slightly varying; which you may need to check upon
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论