基于Google Sheets的9项定制化数据查询需求

以下是针对不同用户需求的Google Sheets查询公式，假设数据表列对应关系为：A=Title，B=Rental Date，C=Source，D=Rating，E=Elements，可根据实际列调整。

• Mike需求：单个单元格列出Title为“Where is the Sun?”且租赁日期不等于2022/9/1的标题和评分信息

  =TEXTJOIN(" | ", TRUE, FILTER({A:A, D:D}, A:A="Where is the Sun?", B:B<>DATE(2022,9,1)))
  

  说明：用FILTER筛选符合条件的记录，TEXTJOIN将结果合并到单个单元格，分隔符为|。

• Lizzy需求：单个单元格按最新租赁日期排序，列出Title为“Where is the Venus?”的所有租赁日期、评分、元素信息

  =TEXTJOIN(" ; ", TRUE, SORT(FILTER({B:B, D:D, E:E}, A:A="Where is the Venus?"), 1, FALSE))
  

  说明：先筛选目标标题的记录，再按租赁日期（第一列）降序排序，最后合并结果。

• Ed需求：单个单元格查询Elements为“A”的租赁日期、来源、标题；或评分恰好为10.00%的对应信息

  =TEXTJOIN(" | ", TRUE, FILTER({B:B, C:C, A:A}, (E:E="A")+(D:D=0.1)>0))
  

  说明：(E:E="A")+(D:D=0.1)>0实现“或”条件筛选，合并符合任一条件的记录。

• John需求：单个单元格按最早租赁日期排序，列出Elements包含“A”但不含“K”的来源、标题、评分信息

  =TEXTJOIN(" ; ", TRUE, SORTBY(FILTER({C:C, A:A, D:D}, REGEXMATCH(E:E, "A")*NOT(REGEXMATCH(E:E, "K"))), FILTER(B:B, REGEXMATCH(E:E, "A")*NOT(REGEXMATCH(E:E, "K"))), TRUE))
  

  说明：用REGEXMATCH匹配元素包含“A”且排除“K”的记录，再按租赁日期升序排序后合并。

• Mona需求：两个单元格分别查询最低、最高评分对应的租赁日期、标题和元素信息

  • 最低评分对应记录：

    =TEXTJOIN(" | ", TRUE, FILTER({B:B, A:A, E:E}, D:D=MIN(D:D)))
    

  • 最高评分对应记录：

    =TEXTJOIN(" | ", TRUE, FILTER({B:B, A:A, E:E}, D:D=MAX(D:D)))
    

  说明：若存在多条相同评分记录，TEXTJOIN会合并所有结果；如需仅显示第一条，将TEXTJOIN替换为INDEX(...,1)。

• Claire需求：查询Title为“Where is Venus”的记录中评分最高的标题、租赁日期和元素信息

  =QUERY(A:E, "SELECT A,B,E WHERE A='Where is Venus' ORDER BY D DESC LIMIT 1")
  

  说明：用QUERY语句筛选目标标题，按评分降序排序后取第一条记录。

• Frank需求：两个单元格分别查询评分最接近平均、最偏离平均的标题、租赁日期和元素信息

  • 最接近平均评分的记录：

    =INDEX(SORT(FILTER({A:A, B:B, E:E, D:D}, NOT(ISBLANK(D:D))), ABS(D:D-AVERAGE(D:D)), TRUE), 1, {1,2,3})
    

  • 最偏离平均评分的记录：

    =INDEX(SORT(FILTER({A:A, B:B, E:E, D:D}, NOT(ISBLANK(D:D))), ABS(D:D-AVERAGE(D:D)), FALSE), 1, {1,2,3})
    

  说明：通过计算评分与平均值的绝对值差，排序后取对应结果。

• Jack需求：查询Rating为最大异常值对应的租赁日期和标题信息

  =QUERY(A:E, "SELECT B,A WHERE D > "&(QUARTILE(D:D,3)+1.5*(QUARTILE(D:D,3)-QUARTILE(D:D,1)))&" ORDER BY D DESC LIMIT 1")
  

  说明：基于四分位距(IQR)判定异常值，筛选超出上限的评分，取最大值对应的记录。

• Mina需求：列出所有存在重复租赁日期的Title，按Title分组并标注其重复的租赁日期

  =QUERY(QUERY(A:B, "SELECT A, B, COUNT(B) WHERE A IS NOT NULL GROUP BY A,B HAVING COUNT(B)>1"), "SELECT A, TEXTJOIN(', ', TRUE, B) GROUP BY A ORDER BY A")
  

  说明：先统计每个标题下重复的租赁日期，再按标题分组合并重复日期。

内容的提问来源于stack exchange，提问作者HelpMePlease