英文:
Return cells that start with string in Google Sheets?
问题
我想要一个Google表格公式，可以返回以某个字符串开头的任何单元格。
这是用于学校课程安排系统的，其语法如下：ClassCode(YearYear/SubjectSubject/ClassClassClass)，然后是教室和教师，如下所示：
08SC101 @ T201 (Valerie FRIZZLE （08年科学课程101由Valerie Frizzle教授）。
数据库位于名为CLASSES的单独工作表中，每一列代表一个课程时间段（例如，CLASSES!A:A是星期一的时间段1，CLASSES!B:B是星期一的时间段2，依此类推）。
假设星期一时间段1的数据库如下：CLASSES!A:A
07HI101 @ B107 (Ashley HARRISON
07HI102 @ B108 (Emily CAREY
08SC101 @ T201 (Valerie FRIZZLE
08SC102 @ T202 (Lisa GRAY
如果条件是以'08'开头的课程代码，我想要的输出是：
08SC101 @ T201 (Valerie FRIZZLE
08SC102 @ T202 (Lisa GRAY
我尝试过以下公式：=FILTER(CLASSES!A1:A99,ISNUMBER(SEARCH("08",CLASSES!A:A)))，但它通配符地返回了包含'08'的任何单元格，包括包含教室的单元格。输出如下：
07HI102 @ B108 (Emily CAREY
08SC101 @ T201 (Valerie FRIZZLE
08SC102 @ T202 (Lisa GRAY
还有哪些其他方法可以实现正确的结果？
非常感谢 
英文:
I would like a Google Sheet formula that returns any cell that starts with a string.
This is for a school timetabling system where the syntax is ClassCode(YearYear/SubjectSubject/ClassClassClass), room and then the teacher as follows
08SC101 @ T201 (Valerie FRIZZLE (Year 08 SCience class 101 taught by Valerie Frizzle).
The database is in a seperate sheet called CLASSES and each column is a single class period (Ie. CLASSES!A:A is Monday Period 1, CLASSES!B:B is Monday Period 2 etc.)
Let's suppose the database is as such for Monday Period 1 CLASSES!A:A
07HI101 @ B107 (Ashley HARRISON
07HI102 @ B108 (Emily CAREY
08SC101 @ T201 (Valerie FRIZZLE
08SC102 @ T202 (Lisa GRAY
I would like the output to be if the condition was class codes starting in '08' to be
08SC101 @ T201 (Valerie FRIZZLE
08SC102 @ T202 (Lisa GRAY
I have tried =FILTER(CLASSES!A1:A99,ISNUMBER(SEARCH("08",CLASSES!A:A))) for class codes that start with '08' however it is wildcard to the extent it also returns any cell that includes '08' that including rooms. The output would be
07HI102 @ B108 (Emily CAREY
08SC101 @ T201 (Valerie FRIZZLE
08SC102 @ T202 (Lisa GRAY
What other approaches are there to acheveing the correct result?
Many thanks
答案1
得分: 1
你可以使用 REGEXMATCH 进行一些处理，但由于你已经在 FILTER 内部写了一个 SEARCH 语句，你可以使用它来检查 SEARCH 的结果是否为 1（表示它在开头）：
=FILTER(CLASSES!A1:A99,SEARCH("08",CLASSES!A1:A99)=1)
英文:
You could do some approach with REGEXMATCH also, but since you already wrote a SEARCH statement inside FILTER, you could use it to check if the result of SEARCH is 1 (meaning it's at the beginning):
=FILTER(CLASSES!A1:A99,SEARCH("08",CLASSES!A1:A99)=1)
答案2
得分: 1
你也可以使用LEFT函数。
=FILTER(CLASSES!A1:A99,LEFT(CLASSES!A1:A99,2)="08")
或者使用QUERY以避免重复范围。
=QUERY(CLASSES!A1:A99,"where A starts with '08'",)
=QUERY(CLASSES!A1:A99,"where A like '08%'",)
=QUERY(CLASSES!A1:A99,"where A matches '08.*'",)
英文:
You could also use the LEFT function.
=FILTER(CLASSES!A1:A99,LEFT(CLASSES!A1:A99,2)="08")
Or with QUERY to avoid repeating the range.
=QUERY(CLASSES!A1:A99,"where A starts with '08'",)
=QUERY(CLASSES!A1:A99,"where A like '08%'",)
=QUERY(CLASSES!A1:A99,"where A matches '08.*'",)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论