英文:
How to use FILTER function to extract data from not exact matches?
问题
在这个表格中，我有事件安排在前两列（A、B），另外两列（C、D）中有事件列表。我需要我的表格能够收集列A中提供的事件的所有时间 - 我已经使用FILTER函数做到了这一点 - 它从列C中取得标题，查找它在列B中的匹配项，并提取列A中的时间。这个方法很有效，但只有当列C中的标题与列B中的完全相同时才有效。
所以我遇到的问题是，如何使它在列C中的标题与列B中的标题不完全相同时也能找到事件的时间。有时在时间表中，我需要在事件标题之前或之后添加一些额外的信息。如果有帮助的话，这些标题之前的附加信息总是以冒号结尾（就像上面的示例表格中一样），如果它们在标题之后，它们总是在括号中（就像**XYZ（特别）**这样）。
我已经找到了如何提取冒号后面的标题，但是列D中的公式必须过滤一系列单元格（超过150个），所以让它逐行查找将使公式变得非常冗长。
我不想添加额外的列来提取标题，然后让公式查找这些标题，因为文件已经包含了大量的工作表和公式，有些同事的计算机加载文件会需要一段时间，所以添加更多的公式和数据会使加载速度更慢。
英文:
In this table I've got events schedule in the first two columns (A, B) and a list of events in the other two (C, D). What I need my table to do is gather all hours of the events provided in column A - which I managed to do already with the FILTER function - it takes the title from column C, looks for it in column B and gives the hours from column A. Which works great, but only if the titles from column C are exactly the same as in column B.
| A: Time | B: Event | / | C: Event | D: Times |
|---|---|---|---|---|
| 10.00 | XYZ | / | XYZ | 10.00, 11.00 |
| 10.30 | ABC | / | ABC | 10.30, 11.30 |
| 11.00 | SPECIAL: XYZ | / | ||
| 11.30 | ABC | / |
So what I'm struggling with is how to make it find the hour of the event even if the title is not exactly the same as in column C. Sometimes in the schedule I need to put some extra information, before or after the title of the event.
If that's of any help, these adnotations before the titles would always end with a colon (as above in the sample table) and if they're after a title they'd always be in brackets (so like XYZ (special)).
I found how to extract the title after the colons, but the formula in column D has to filter a range of cells (over 150) so making it look through each and every one row would make the formula miles long.
I don't want to put an extra column to extract the title and then make the formula look through those because the file is already heavy with sheets and formulas and takes a while to load on some of my teammates computers, so adding more formulas and data would make it even slower.
答案1
得分: 0
=map(D2:D,lambda(Σ,if(Σ="",,ifna(textjoin(", ",1,filter(A:A,search(Σ,B:B)))))))
英文:
You may try:
=map(D2:D,lambda(Σ,if(Σ="",,ifna(textjoin(", ",1,filter(A:A,search(Σ,B:B)))))))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论