Excel公式：获取列表中最大数值和对应的条目

excelperfect

引言：本文的练习整理自chandoo.org。多一些练习，想想自己会怎么解决这个问题，看看别人又是怎样解决的，这样能够快速提高Excel公式编写水平。

本次的练习是：给定一个包含数字和文本条目的单列列表，查找数字总和最大值对应的条目。示例数据如下图1所示，公式应该返回“c”，因为其对应的数字总和9是最大的。

图1

公式应该是仅涉及Excel函数的单个公式，该公式引用一个包含值列表的命名区域x（示例中，该命名区域代表单元格区域A1:A12）。

同时，假设数字都是正的。

请写下你的公式。

解决方案

公式1：数组公式。

=INDEX(x,MATCH(MAX(MMULT(TRANSPOSE(ROW(x)^0),IFERROR((LOOKUP(ROW(x),1/ISERR(-x)*ROW(x),x)=TRANSPOSE(x))*x,0))),MMULT(TRANSPOSE(ROW(x)^0),IFERROR((LOOKUP(ROW(x),1/ISERR(-x)*ROW(x),x)=TRANSPOSE(x))*x,0)),0))

公式2：数组公式。

=INDEX(x,ROUND(MOD(MAX(MMULT(IFERROR((TRANSPOSE(LOOKUP(ROW(x),1/ISERR(-x)*ROW(x),x))=x)*TRANSPOSE(x),0),ROW(x)^0)+MATCH(ROW(x),ROW(x),0)%),1)*100,0))

公式3：数组公式。

=INDEX(x,INDEX(SMALL(IF(ISTEXT(x),ROW(x)),ROW(INDIRECT("1:"&SUM(--ISTEXT(x))))),MATCH(MAX(SUMIF(OFFSET(x,SMALL(IF(ISTEXT(x),ROW(x)),ROW(INDIRECT("1:"&SUM(--ISTEXT(x))))),,IFERROR(SMALL(IF(ISTEXT(OFFSET(x,1,)),ROW(OFFSET(x,1,))),ROW(INDIRECT("1:"&SUM(--ISTEXT(x)))))-1,ROWS(x))-SMALL(IF(ISTEXT(x),ROW(x)),ROW(INDIRECT("1:"&SUM(--ISTEXT(x))))),),"<>0")),SUMIF(OFFSET(x,SMALL(IF(ISTEXT(x),ROW(x)),ROW(INDIRECT("1:"&SUM(--ISTEXT(x))))),,IFERROR(SMALL(IF(ISTEXT(OFFSET(x,1,)),ROW(OFFSET(x,1,))),ROW(INDIRECT("1:"&SUM(--ISTEXT(x)))))-1,ROWS(x))-SMALL(IF(ISTEXT(x),ROW(x)),ROW(INDIRECT("1:"&SUM(--ISTEXT(x))))),),"<>0"),)))

公式4：数组公式。

=INDEX(x,MATCH(1,FREQUENCY(-9999,-MMULT(IFERROR((TRANSPOSE(LOOKUP(ROW(x),1/ISERR(-x)*ROW(x),x))=x)*TRANSPOSE(x),0),ROW(x)^0)),0))

公式5：数组公式。

=LOOKUP(1,1/FREQUENCY(-9999,-MMULT(IFERROR((TRANSPOSE(LOOKUP(ROW(x),1/ISERR(-x)*ROW(x),x))=x)*TRANSPOSE(x),0),ROW(x)^0)),x)

或者：

=LOOKUP(1,1/FREQUENCY(-9^9,-MMULT((TRANSPOSE(LOOKUP(ROW(x),ROW(x)/ISERR(-x),x))=x)*N(TRANSPOSE(x)),ROW(x)^0)),x)

或者：

=LOOKUP(1,1/FREQUENCY(-9^9,MMULT(N(TRANSPOSE(LOOKUP(ROW(x),ROW(x)/ISERR(-x),x))=x),IFERROR(-x,0))),x)

公式6：数组公式。

=LOOKUP(1,1/FREQUENCY(-9^9,MMULT(N(TRANSPOSE(LOOKUP(ROW(x),ROW(x)/ISERR(-x)))=ROW(x)),IFERROR(-x,0))),x)

公式7：数组公式。

=LOOKUP(1,1/FREQUENCY(0,IFERROR(1/SUBTOTAL(9,OFFSET(x,ROW(x)-MIN(ROW(x)),,FREQUENCY(-ROW(x),-ISTEXT(x)*ROW(x)))),-1)),x)

或者：

=LOOKUP(1,1/FREQUENCY(-9^9,-IFERROR(SUMIF(OFFSET(x,ROW(x)-ROW(INDEX(x,1)),,FREQUENCY(-ROW(x),-ISTEXT(x)*ROW(x)),1),"<>"),0)),x)

……

公式并没有给出详细的解析，有兴趣的朋友可以参照前面推送的有关分析公式的文章对这些公式进行解析，相信对理解Excel函数，编写公式解决问题会有很大的帮助。

也许有人会问，为什么要编写这么复杂的公式？其实，在这里的主要目的，是通过编写公式解决复杂的Excel问题来磨练公式与函数应用技能，也让研究Excel的大脑时刻保持良好的状态。

欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。