![如何根据标准对值进行排序](https://linux22.com/image/1628598/%E5%A6%82%E4%BD%95%E6%A0%B9%E6%8D%AE%E6%A0%87%E5%87%86%E5%AF%B9%E5%80%BC%E8%BF%9B%E8%A1%8C%E6%8E%92%E5%BA%8F.png)
I have survey data for 3619 individuals living in 1000 houses. The MID column is the member ID of each individual. For example: H1M1 is 1st Member of 1st House. H21M3 is 3rd member of 21st house. Column D has House numbers.
I want to do 2 things.
- Based on age, I want to find the ranking of members in every house.
- 在下一列中,我想查找该成员是否是家中最大的孩子。孩子是指 18 岁以下的任何人。需要二进制输出 1 或 0。
答案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 中的公式。
为了整洁你可以隐藏辅助数据。
根据需要调整公式中的单元格引用。
答案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))