我是一名植物学家，我正在尝试制作一个 Excel 表，以根据位置和基本栖息地筛选出一长串物种列表。
我为每个物种创建了一个包含 200 行的主表，列中首先是位置（例如，列 b 代表英格兰、列 b 代表苏格兰等等），然后是位置之后的列，即基本栖息地（例如，列 f 代表林地、列 g 代表草地、列 h 代表水生生物）。所有单元格都包含与每个物种相关的 YES 或 NO，以显示该物种的地理位置和栖息地。
我想制作一个表格，询问我我的站点在哪里以及它支持的栖息地，并利用这些信息缩小可能存在的物种范围。例如，如果我在伦敦有一个站点，它支持林地、草地和树篱，我可以使用过滤器将物种列表从 200 多种减少到少数几种！
关于如何制作这个的任何想法都很好。我附上了我当前工作表的图片。
答案1
非常坦率的
您已将信息排列在两个并排的表格中，表格的行对齐。表 1 是您的位置，表 2 是您的栖息地。基本上，您想要生成一个列表或行号，其中匹配位置列中为是，匹配栖息地列中为是。根据该行号列表，提取相应的物种。
为了做到这一点，让我们做一些假设：
- 所有数据所在的工作表称为 SData
- 数据从 A1:R200 开始布局
- 第 1 行是标题行
- C:I 列是位置列
- J:R 列是栖息地位置
- 您要找的位置是B1
- 您正在寻找的栖息地是 D1
- 您的列表将显示在 A2:B200 中
1）确定位置列
=INDEX(SData!C2:I200,0,MATCH($B$1,SData!$C$1:$I$1,0)
2）确定栖息地栏
=INDEX(SData!J2:R200,0,MATCH($D$1,SData!$J$1:$R$1,0)
3）确定哪些行包含“是”
Habitat 和 Location 都需要包含 yes 才能成为可接受的行。如果单元格 = yes 则为 True。在数学运算中，Excel 将 True 视为 1，将 False 视为 0。因此，如果一行中的两个单元格都是 yes，则结果为 1*1=1。如果两个单元格都是 FALSE，则结果为 0*0=0。如果一个单元格为 Yes，另一个为 No，则结果为 1*0=1。
因此，为了做到这一点，将使用聚合函数。它将对将要使用的某些函数（如 14 和 15）执行类似数组的操作。还可以指示聚合忽略错误。因此，我们将设置 AGGREGATE 以将行号除以条件检查为是。结果，您要么得到行号，要么得到除以 0 的错误，聚合将转而忽略这些结果。
=INDEX(SData!A:A,AGGREGATE(14,6,ROW(SData!$A$2:$A$200)/((INDEX(SData!$C$2:$I$200,0,MATCH($B$1,SData!$C$1:$I$1,0))="yes")*(INDEX(SData!$J$2:$R$200,0,MATCH($B$1,SData!$J$1:$R$1,0))="yes")),ROW(A1)))
将上述公式放在 A2 中，然后向下复制到 B200。到目前为止，我预见到的问题是当您用完与您的数据匹配的行时。在这种情况下，或者如果您没有任何结果，则会产生错误。为了避免单元格中出现错误结果，您可以将上面的整个公式包装在 IFERROR 公式中，以便在结果为错误时显示“”。
=IFERROR(INDEX(SData!A:A,AGGREGATE(14,6,ROW(SData!$A$2:$A$200)/((INDEX(SData!$C$2:$I$200,0,MATCH($B$1,SData!$C$1:$I$1,0))="yes")*(INDEX(SData!$J$2:$R$200,0,MATCH($D$1,SData!$J$1:$R$1,0))="yes")),ROW(A1))),"")
例子：
数据表：
结果表 1：
结果表 2：
