如何根據標準對數值進行排名

Question

我有 1000 間房子裡 3619 人的調查數據。 MID 欄位是每個人的會員 ID。例如：H1M1 是第一宮的第一位成員。 H21M3是第21宮的第3位成員。 D 列包含門牌號碼。

我想做兩件事。

1. 根據年齡，我想找到每個房子的成員排名。
2. 在下一欄中，我想找出該成員是否是家裡最大的孩子。 Kid 是 18 歲以下的任何人。

Answer 1

下面顯示的方法解決了這個問題：

怎麼運作的：

• 尋找儲存格中的排名、公式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))