기준에 따라 값의 순위를 지정하는 방법

Question

저는 1000가구에 거주하는 3619명의 개인에 대한 설문조사 데이터를 가지고 있습니다. MID 열은 각 개인의 회원ID입니다. 예: H1M1은 1하우스의 1번째 구성원입니다. H21M3은 21하우스의 3번째 멤버입니다. D 열에는 집 번호가 있습니다.

나는 2가지 일을 하고 싶다.

1. 나이를 기준으로 집집마다 회원들의 순위를 알아보고 싶습니다.
2. 다음 칼럼에서는 그 구성원이 집의 큰 아이인지 알아보고 싶습니다. Kid는 18세 미만의 모든 사람입니다. 1 또는 0의 이진 출력이 필요합니다.

Answer 1

아래 표시된 방법으로 문제가 해결됩니다.

작동 방식:

• RANK를 찾으려면 셀의 수식D196:

  =SUMPRODUCT((--(LEFT(A196,2)=LEFT($A$196:$A$207,2))),(--(B196<$B$196:$B$207)))+1
  

• 셀의 배열( CSE ) 수식F196~을 위한도우미 데이터:

  {=IFERROR(INDEX(LEFT(A$196:A$207,2),MATCH(0,COUNTIF($F$195:F195,LEFT(A$196:A$207,2)),0)),"")}
  

주의수식 마무리Ctrl+Shift+Enter.

• 셀의 수식E196:

  =IF(AGGREGATE(14,4,(LEFT($A$196:$A$207,2)=F196)*$B$196:$B$207,1)=0,"",(AGGREGATE(14,4,(LEFT($A$196:$A$207,2)=F196)*$B$196:$B$207,1)))
  

• 셀의 배열(CSE) 수식G196:

   {=IF(MIN(IF(LEFT(A$196:A$207,2)=F196,B$196:B$207,""))=0,"",MIN(IF(LEFT(A$196:A$207,2)=F196,B$196:B$207,"")))}
  

주의수식 마무리Ctrl+Shift+Enter.

• 셀의 수식H196:

  =IFERROR(INDEX(A$196:A$207,MATCH(G196,B$196:B$207,0)),"")
  

주의

• 당신은 또한 찾을 수 있습니다가장 나이 많은 회원의 이름, H196 셀의 수식을 사용합니다.

• 깔끔함을 위해 숨길 수도 있습니다.도우미 데이터.

• 필요에 따라 수식의 셀 참조를 조정합니다.

Answer 2

MID다음과 같이 열이 항목의 처음 두 글자를 기준으로 정렬되었다고 가정합니다 .

AgeRank:    =RANK(G2,INDEX(Age,MATCH(LEFT(F2,2) & "*",MID_,0)):INDEX(Age,-1+MATCH(LEFT(F2,2)&"*",MID_,0)+COUNTIF(MID_,LEFT(F2,2)&"*")))

EldestKid:  =N(G2=AGGREGATE(14,6,1/(INDEX(Age,MATCH(LEFT(F2,2)&"*",MID_,0)):INDEX(Age,-1+MATCH(LEFT(F2,2)&"*",MID_,0)+COUNTIF(MID_,LEFT(F2,2)&"*"))<18)*INDEX(Age,MATCH(LEFT(F2,2)&"*",MID_,0)):INDEX(Age,-1+MATCH(LEFT(F2,2)&"*",MID_,0)+COUNTIF(MID_,LEFT(F2,2)&"*")),1))