Как ранжировать значения на основе критерия

Question

У меня есть данные опроса 3619 человек, живущих в 1000 домах. Столбец MID — это идентификатор члена каждого человека. Например: H1M1 — 1-й член 1-го дома. H21M3 — 3-й член 21-го дома. Столбец D содержит номера домов.

Я хочу сделать 2 вещи.

1. Я хочу найти рейтинг членов каждого дома по возрасту.
2. В следующем столбце я хочу узнать, является ли член семьи старшим ребенком в доме. Ребенок — это любой человек младше 18 лет. Требуется двоичный вывод 1 или 0.

Answer 1

Ниже показан метод решения этой проблемы:

Как это работает:

• Чтобы найти РАНГ, формула в ячейкеД196:

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

• Формула массива (CSE) в ячейкеФ196дляВспомогательные данные:

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

Примечание.Закончите формулу сCtrl+Shift+Enter.

• Формула в ячейкеЕ196:

  =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) в ячейкеГ196:

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