我是一名植物學家，我正在嘗試製作 Excel 表格，根據位置和基本棲息地過濾一長串物種。
我為每個物種創建了一個包含200 行的主表，沿列首先是位置（例如，b 列代表英格蘭，b 列代表蘇格蘭等），然後在位置之後的列是基本棲息地（即f 列代表林地） ，g 列為草地，h 列為水生植物）。所有單元格都包含與每個物種相關的“是”或“否”，顯示該物種出現的地理位置和棲息地。
我想製作一個表格，詢問我的地點在哪裡以及它支持哪些棲息地，並使用它來縮小可能存在的物種範圍。例如，如果我在倫敦有一個支持林地、草地和樹籬的站點，我可以使用過濾器將物種清單從 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) 確定哪些行包含 Yes
Habitat 和 Location 都需要包含 yes 才能成為可接受的行。如果 cell = yes 則為 True。在數學運算中，Excel 將 True 視為 1，將 False 視為 0。如果兩者均為 FALSE，則 0*0=0。如果一個儲存格為“是”，另一個儲存格為“否”，則結果將為 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：
