Google QUERY公式包含“全部"从下拉菜单 [英] Google QUERY Formula to include "All" from a dropdown

问题描述

我有3个单独的下拉菜单，可在Google表格中生成以下QUERY函数:

I have 3 separate dropdowns to generate the following QUERY function in my Google Sheet:

    =QUERY('HISOP Training'!A2:D,"select A, B, C, D where A contains '"&M5&"' and C contains '"&M6&"' and D contains '"&M7&"' order by D asc", 1)

单元格M5中的下拉列表引用列A:A(分支).单元格M6中的下拉列表引用列C:C(员工).单元格M7中的下拉列表引用了D:D列((文档).B:B列是日期，对此没有下拉列表.

The dropdown in cell M5 references to column A:A (the Branch). The dropdown in cell M6 references to column C:C (the Employee). The dropdown in cell M7 references to column D:D (the (Document). Column B:B is the date and there is no dropdown for this.

每个下拉列表都有一个引用每一列的列表.每个下拉菜单还带有全部"字样.当所有"被选中我想每行，在查询这取决于在其他2名的下拉列表中选择返回.例如，如果在M5下拉列表中选择了纽约"分支，而在M6&中选择了全部". M7下拉菜单，我希望所有员工和所有文件返回纽约.

Each dropdown has a list which references to each column. Each dropdown also has the word "All". When "All" is selected I want every row to return in the QUERY depending on what is selected in the other 2 dropdowns. For example, if the Branch "New York" is selected in the M5 dropdown and "All" is selected in the M6 & M7 dropdowns, I want all employees and all documents returned for New York.

我不知道如何在QUERY中创建IF/AND.非常感谢您的帮助.

I can't work out how to create IF/AND into the QUERY. Would really appreciate help.

推荐答案

尝试一下:

=QUERY(
  'HISOP Training'!A2:D,
    "select A, B, C, D "
  & IF(
      OR(M5 <> "All", M6 <> "All", M7 <> "All"),
          "where "
        & TEXTJOIN(
            "and ",
            True,
            IF(M5 <> "All", "A contains '" & M5 & "' ", ""),
            IF(M6 <> "All", "C contains '" & M6 & "' ", ""),
            IF(M7 <> "All", "D contains '" & M7 & "' ", "")
          ),
        ""
    )
  & "order by D asc",
  1
)

这篇关于Google QUERY公式包含“全部"从下拉菜单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！