求助：多条件计算极值及次极值等，谢谢！

脚根

多条件计算第2大值，第2小值，等

空格为投考
在Excel2013环境实现
函数公式越简单越好
见附件
谢谢！

博爱风雨同舟

=INDEX(A:C,SMALL(IF(MINIFS(C2:C156,C2:C156,"<>""",A2:A156,A2:A156)=C2:C156,ROW(2:156),""),SEQUENCE(7)),SEQUENCE(,3))

hugeinfo

f2=MAX(IF(($A$2:$A$156=$E2)*($C$2:$C$156<>""),$C$2:$C$156))

g2=MIN(IF(($A$2:$A$156=$E2)*($C$2:$C$156<>""),$C$2:$C$156))
h2=LARGE(IF(($A$2:$A$156=$E2)*($C$2:$C$156<>""),$C$2:$C$156),COLUMN(B1))
i2=SMALL(IF(($A$2:$A$156=$E2)*($C$2:$C$156<>""),$C$2:$C$156),COLUMN(B1))
选中h2  i2两列向右向下拉

脚根

博爱风雨同舟 发表于 2024-5-16 13:48
=INDEX(A:C,SMALL(IF(MINIFS(C2:C156,C2:C156,"""",A2:A156,A2:A156)=C2:C156,ROW(2:156),""),SEQUENCE(7)) ...

试了，不知道怎么用阿

博爱风雨同舟

=AGGREGATE(15,6,$C$2:$C$156/($A$2:$A$156=$E2)/($C$2:$C$156<>""),COLUMN(A1))

脚根

hugeinfo 发表于 2024-5-16 13:53
f2=MAX(IF(($A$2:$A$156=$E2)*($C$2:$C$156""),$C$2:$C$156))

g2=MIN(IF(($A$2:$A$156=$E2)*($C$2:$C$15 ...

向下复制公式会报错

jisijie630

=ABS(SMALL(IF(($A$2:$A$156=$E2)*$C$2:$C$156,-1^COLUMN(A1)*$C$2:$C$156),INT(COLUMN(B2)/2)))


数组三键下拉右拉。

part98

F2=LARGE(IF(($C$2:$C$156<>"")*($A$2:$A$156=$E2),$C$2:$C$156),(COLUMN(A1)+1)/2)
G2=SMALL(IF(($C$2:$C$156<>"")*($A$2:$A$156=$E2),$C$2:$C$156),COLUMN(B1)/2)
选中F2,G2,先右拉再下拖

我的城市你来过

=IFERROR(INDEX(LARGE(IF($A$2:$A$156=$E2,$C$2:$C$156,""),ROW($A$1:$A$5)),IF(MOD(COLUMN(B1),2)=0,COLUMN(B1),"")/2),INDEX(SMALL(IF($A$2:$A$156=$E2,$C$2:$C$156,""),ROW($A$1:$A$5)),IF(MOD(COLUMN(B1),2)=0,"",INT(COLUMN(B1)/2))-1))

minchar

照着5楼抄一遍
选中F2:G2
=AGGREGATE({14,15},7,$C$2:$C$156/(($A$2:$A$156=$E2)*($C$2:$C$156<>"")),COLUMN(B1)/2)
CSE
选中F2:G2填充