使用筛选后的范围创建单元格内的下拉列表

Question

我正在使用Google电子表格。

为了说明我的问题，我使用范围A2:A8对D2和E2的数据验证。

但由于在单元格D2中，您只能选择一个动物，因此我想使用B2:B8过滤该范围。

我尝试过的是使用自己的公式:

=FILTER(A2:A8;IS("B2:B8";"ANIMAL"))

但是如果我使用自定义公式，这样做是行不通的，我也无法选择“下拉菜单”选项。

我还尝试在我的范围选择中使用公式，但它无效。有什么正确的公式可以使用来显示带有过滤数据的下拉菜单呢？

你有什么想法吗？

- rayfranco

Answer 1

目前在Google Sheets中，唯一可以使用的本地方法（即不使用Google Apps脚本）来填充下拉列表的方式是使用逗号分隔的列表或引用一个范围。因此，在您的情况下，您需要在电子表格中的某个地方（可以是隐藏工作表上）重现您的筛选列表：

=FILTER(A2:A8;B2:B8="ANIMAL")

然后在数据验证中引用该输出范围。

直接使用公式生成下拉列表的功能将是一个强大的功能，并已由许多人提交为功能请求（您可能希望执行相同操作：帮助菜单，报告问题）。

- AdamL

---

这是一个聪明而简单的解决方案，谢谢。@pnuts，是的，你就是 ;) - rayfranco

四年后，他们修复了这个问题吗？ - CommaToast

@CommaToast 不。 - user6767685

五年后，他们修复了这个问题吗？ - TWilly

7年，3个月过去了...仍然没有修复... - MKZ

Answer 2

使用Google Apps Scripts可以解决此问题。

通过简洁的视频讲解所有涉及的机制：

• 第一部分
• 第二部分

基本上，通过编辑任何一个下拉列表依赖的单元格（例如城市列表的国家），对于相关的“城市”单元格，它将自动重新计算验证数据范围（可能的城市列表）。

为了防止脚本不可用，这里复制/粘贴了脚本（该示例使用了汽车的品牌和型号作为依赖下拉列表）：

function onEdit() {
  var tabLists = "lists";
  var tabValidation = "Main";
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var datass = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(tabLists);

  var activeCell = ss.getActiveCell();

  if (activeCell.getColumn() == 1 && activeCell.getRow() > 1 && ss.getSheetName() == tabValidation){

    activeCell.offset(0, 1).clearContent().clearDataValidations();

    var makes = datass.getRange(1, 1, 1, datass.getLastColumn()).getValues();

    var makeIndex = makes[0].indexOf(activeCell.getValue()) + 1;

    if (makeIndex != 0){
        var validationRange = datass.getRange(3, makeIndex, datass.getLastRow());
        var validationRule = SpreadsheetApp.newDataValidation().requireValueInRange(validationRange).build();
        activeCell.offset(0, 1).setDataValidation(validationRule);
     }  
  }
}

- Maxime Pacary

Answer 3

除了上面的答案，还有其他几种方法可以实现这个目标。

• 在A:B范围内创建一个数据透视表，将“Some Values”添加到数据透视行（记得取消选中“显示总计”选项），然后将“Animal”添加到筛选器中，并编辑筛选器以仅选择动物。这将生成一个动物名称列表。最后，使用此列作为您的数据验证规则的范围。

• 另一种选择是使用查询公式创建列。例如：

  = QUERY(A:B, "SELECT A WHERE B = 'ANIMAL'", 0)
  

- Raajkumar Subramaniam

---

很不幸，这种方法是无法扩展的。随着您要过滤的类别增加，每次创建关系时都必须更新此查询。因此，如果您想要除了“动物”之外的列表，请编写全新的查询。然后，该查询的列式结果必须以某种方式返回到下拉菜单中。 - user6767685

Answer 4

=QUERY(A:B, "SELECT A WHERE B = 'ANIMAL'", 0)  

可能比之前所认为的更具可扩展性。

=QUERY(A:B, CONCATENATE("SELECT A WHERE B = '" , G3   "'", 0) )   

可能对某些用途具有可扩展性。

- Robin Hammond