请求修正Google Sheet的QUERY与下拉筛选公式（听障用户求助）

原公式存在的问题 #

• 第一个IF嵌套未完成闭合，缺少对应括号
• 两个独立的IF公式直接拼接，语法错误
• 拼写错误：FEE NOT PAIND应为FEE NOT PAID
• 重复的全量筛选逻辑（ALL WARD LICENSE APPLICATIONS和ALL APPLICATIONS功能重叠）
• QUERY语句中部分条件匹配逻辑可优化

修正后的组合筛选公式 #

若需同时结合A2（区域筛选）和A3（类型筛选）的条件，可使用以下公式（建议放在C2单元格）：

=QUERY(ALL!B3:K, 
  "SELECT * 
   WHERE " & 
   IF(A2="ALL WARD LICENSE APPLICATIONS", "1=1", "D CONTAINS '" & MID(A2,6,2) & "/'") & 
   " AND " & 
   SWITCH(A3,
     "ALL APPLICATIONS", "1=1",
     "ALL NEW LICENSE APPLICATIONS", "H CONTAINS 'NEW LICENSE APPLICATION'",
     "ALL RENEWAL LICENSE APPLICATIONS", "H CONTAINS 'RENEWAL LICENSE APPLICATION'",
     "FEE NOT PAID", "K CONTAINS 'FEE PENDING'",
     "1=1"
   )
)

若需保持A2和A3分别独立筛选（两个筛选器互不影响），可分开设置：

A2区域筛选公式（放在B2单元格） #

=IFS(
  A2="ALL WARD LICENSE APPLICATIONS", QUERY(ALL!B3:K,"SELECT *"),
  A2="WARD 1 LICENSE APPLICATIONS", QUERY(ALL!B3:K,"SELECT * WHERE D CONTAINS 'MP 01/'"),
  A2="WARD 2 LICENSE APPLICATIONS", QUERY(ALL!B3:K,"SELECT * WHERE D CONTAINS 'MP 02/'"),
  A2="WARD 3 LICENSE APPLICATIONS", QUERY(ALL!B3:K,"SELECT * WHERE D CONTAINS 'MP 03/'"),
  A2="WARD 4 LICENSE APPLICATIONS", QUERY(ALL!B3:K,"SELECT * WHERE D CONTAINS 'MP 04/'"),
  A2="WARD 5 LICENSE APPLICATIONS", QUERY(ALL!B3:K,"SELECT * WHERE D CONTAINS 'MP 05/'"),
  A2="WARD 6 LICENSE APPLICATIONS", QUERY(ALL!B3:K,"SELECT * WHERE D CONTAINS 'MP 06/'"),
  TRUE, QUERY(ALL!B3:K,"SELECT *")
)

A3类型筛选公式（放在B3单元格） #

=IFS(
  A3="ALL APPLICATIONS", QUERY(ALL!B3:K,"SELECT *"),
  A3="ALL NEW LICENSE APPLICATIONS", QUERY(ALL!B3:K,"SELECT * WHERE H CONTAINS 'NEW LICENSE APPLICATION'"),
  A3="ALL RENEWAL LICENSE APPLICATIONS", QUERY(ALL!B3:K,"SELECT * WHERE H CONTAINS 'RENEWAL LICENSE APPLICATION'"),
  A3="FEE NOT PAID", QUERY(ALL!B3:K,"SELECT * WHERE K CONTAINS 'FEE PENDING'"),
  TRUE, QUERY(ALL!B3:K,"SELECT *")
)

下拉筛选设置步骤 #

• 选中A2单元格，点击菜单栏「数据」→「数据验证」
• 在弹出窗口中，「条件」选择「列表项」，输入下拉选项：ALL WARD LICENSE APPLICATIONS,WARD 1 LICENSE APPLICATIONS,WARD 2 LICENSE APPLICATIONS,WARD 3 LICENSE APPLICATIONS,WARD 4 LICENSE APPLICATIONS,WARD 5 LICENSE APPLICATIONS,WARD 6 LICENSE APPLICATIONS
• 勾选「显示下拉箭头」，点击「保存」
• 重复上述步骤，为A3单元格设置下拉选项：ALL APPLICATIONS,ALL NEW LICENSE APPLICATIONS,ALL RENEWAL LICENSE APPLICATIONS,FEE NOT PAID

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