私は植物学者で、場所と基本的な生息地に基づいて非常に長い種のリストをフィルタリングする Excel テーブルを作成しようとしています。
私は、それぞれの種について 200 行のマスター テーブルを作成しました。列には最初に場所 (たとえば、列 b はイングランド、列 b はスコットランドなど) が示され、場所の次の列には基本的な生息地 (つまり、列 f は森林、列 g は草原、列 h は水生) が示されます。すべてのセルには、それぞれの種に関連する YES または NO が示され、その種がどの地理的位置とどの生息地に生息しているかが示されます。
私は、自分のサイトがどこにあり、どのような生息地がサポートされているかを尋ね、これを使用して、存在する可能性のある種を絞り込むテーブルを作成したいと考えています。たとえば、ロンドンに森林、草地、生け垣がサポートされているサイトがある場合、フィルターを使用して、種のリストを 200 種以上から数種に減らすことができます。
これを作成する方法についてのアイデアがあれば、ぜひ教えてください。現在のワークシートの写真を添付しました。
答え1
かなり単純明快
基本的に、行が一列に並んだ 2 つの表に情報をレイアウトしました。表 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 になります。また、1 つのセルが Yes で、もう 1 つのセルが No の場合、結果は 1*0=1 になります。
そのため、これを実行するには、集計関数を使用します。集計関数は、使用される 14 や 15 などの特定の関数に対して配列のような操作を実行します。集計関数は、エラーを無視するように指示することもできます。そのため、行番号を条件チェックで yes と判定された数で割るように 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:
