谷歌电子表格查询到下拉列表 [英] google spreadsheet query to dropdown list

问题描述

我正在使用Google电子表格，因此必须根据某些条件创建一个下拉列表.例如，我有一个数据库页面:

I'm working on a google spreadsheet and have to do a dropdown list depending on some criteria. For example I have a DB page with :

|   A  |   B   |
| name | price |
| a    | 100   |
| b    | 120   |
| c    | 150   |

如果我想获得所有项目的列表，我知道我可以使用公式进行处理，转到数据>验证并说出范围 DB！A3:A999 ，如下所示: https://support.google.com/docs/answer/186103

If I want to get a list of all item I know I can do it with the formula, going to data > validation and say the range DB!A3:A999 as explained here : https://support.google.com/docs/answer/186103

现在，我想为用户添加一些可能的过滤器，例如min&最高价格.

Now I would like to add some possible filter for the user like min & max price.

我认为我需要使用查询代码，例如 = QUERY(DB！3:999，"SELECT A WHERE B> = 110 AND B< = 140")，但这返回的都是正确的在所有情况下，我将公式放在其中(1行= 1个结果).有没有办法将此查询转换为下拉列表?或任何其他将标准添加到验证范围的方法?

I think I need to use the query formula like =QUERY(DB!3:999, "SELECT A WHERE B>=110 AND B<=140") but this is returning all correct result in all case under where I put the formula (1 row = 1 result). Is there a way to turn this query into a dropdown list ? or any other way to add criterias to the validation range ?

编辑

如果您要在发布前测试解决方案，请点击此处获取电子表格链接 https://docs.google.com/spreadsheets/d/1mwwgBla7Dy?usp =分享

Here is a spreadsheet link if you want to test your solution before posting https://docs.google.com/spreadsheets/d/1mwgBla7DyIHio8eH3xr2w_EngvHcHIeTXwc43e7YwwU/edit?usp=sharing

推荐答案

是的，您可以执行以下操作:

Yes, you can do something like this:

说单元格 B1 是一个下拉列表，您可以编写查询:

Say cell B1 is a dropdown list you can write your query:

=QUERY(DB!3:999, "SELECT A WHERE B='"&B1&"' ")

但是，这仅适用于纯文本.

However this will only work with plain text.

我认为最好的选择是使用FILTER公式.

I think the better option fro you would be to use FILTER formula.

这篇关于谷歌电子表格查询到下拉列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！