저는 식물학자이고 위치와 기본 서식지를 기반으로 매우 긴 종 목록을 필터링하는 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) Yes가 포함된 행을 확인합니다.
허용되는 행이 되려면 Habitat 및 Location 모두 yes를 포함해야 합니다. cell = yes이면 True가 됩니다. 수학 연산에서 Excel은 True를 1로, False를 0으로 처리합니다. 따라서 행의 두 셀이 모두 '예'이면 1*1=1이 됩니다. 둘 다 FALSE이면 0*0=0이 됩니다. 한 셀이 예이고 다른 셀이 아니오이면 결과는 1*0=1이 됩니다.
따라서 이를 수행하기 위해 집계 함수가 사용됩니다. 사용될 14 및 15와 같은 특정 기능에 대해 배열과 같은 작업을 수행합니다. 오류를 무시하도록 Aggregate에 지시할 수도 있습니다. 따라서 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:
