基準に基づいて値をランク付けするにはどうすればいいですか

Question

1000 軒の家に住む 3619 人の調査データがあります。MID 列は各個人のメンバー ID です。たとえば、H1M1 は第 1 ハウスの第 1 メンバーです。H21M3 は第 21 ハウスの 3 番目のメンバーです。列 D にはハウス番号があります。

やりたいことが2つあります。

1. 年齢に基づいて、各家のメンバーの順位を調べたい。
2. 次の列では、メンバーが家の長男かどうかを調べます。長男とは 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次のように、列がエントリの最初の 2 文字でソートされていると仮定します。

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))