多种方法计算不规范数据中的最大值

-01-

计算各部门的最高考核分数

下图是一张录入不规范的销售考核表，A列是各销售部门，B列是各销售部的所有成绩，都录入到一个单元格中。现在求各销售部的最高考核分数，结果如C列所示。假定每个销售部的考核成绩范围在1~100之间。

第1种，公式如下，按ctrl+shift+enter三键，向下填充。

=MATCH(,-FIND(ROW($1:$100),B2))

FIND(ROW($1:$100),B2)部分，是用find在B2中查找1~100，如果找到了返回它的位置，是一个数字；如果没找到返回错误值#VALUE!。最后得到了一个由数字和错误值组成的数组。

-FIND(ROW($1:$100),B2))部分，是将上一步数组中的数字变为负数。

MATCH(,-FIND(ROW($1:$100),B2))部分，是用match第3参数为1的模糊查找方式，查找0在上一步数组中的位置。由于上一步数组中的数字都成了负数，0比负数都大，所以找到最后一个数字的位置，刚好就是最高考核成绩。

match第1参数省略，用逗号留出位置，相当于0；第3参数省略不写，逗号都省掉，相当于1。相当于MATCH(0,-FIND(ROW($1:$100),B2),1)。

如果你不怕卡，这个公式还可以写为=MATCH(,-FIND(ROW(A:A),B2))。

第2种，公式如下，不用三键。

=LOOKUP(,-FIND(ROW($1:$100),B2),ROW($1:$100))

这个公式和第1种的思路一样，只不过用lookup返回最高成绩。不再说明。

第3种，公式如下，按ctrl+shift+enter三键。

=MAX(ISNUMBER(FIND(ROW($1:$100),B2))*ROW($1:$100))

FIND(ROW($1:$100),B2)部分，还是在B2中找1~100，找到了返回位置的数字，找不到返回错误值#VALUE!。

ISNUMBER(FIND(ROW($1:$100),B2))部分，判断上一步的结果是否是数字，是数字的返回true，不是数字的返回false。简单来说，找到的返回true，未找到的返回false。

ISNUMBER(FIND(ROW($1:$100),B2))*ROW($1:$100)部分，用上一步的结果乘以ROW($1:$100)。找到的返回对应的考核成绩，未找到的返回0。

最后用max取最大值，就得到了最高考核成绩。

第4种，公式如下，按ctrl+shift+enter三键。

=MAX((SUBSTITUTE(B2,ROW($1:$100),)<>B2)*ROW($1:$100))

此公式用的是替换函数substitute，在B2中替换1~100，如果替换后的结果不等于原来的B2，说明包含1~100，相当于找到1~100；否则不包含1~100，相当于未找到1~100。

后续的逻辑和第3种方法一样，不再重复说明。

第5种，公式如下，按ctrl+shift+enter三键。

=MATCH(,-MATCH("*"&ROW($1:$100)&"*",B2,))

此公式用的是通配符查找法，之前用的是find查找1~100在B2的位置，现在是用match通配符的方式来查找。找到的返回1，未找到的返回#value!。后续的查找方法是一样的。

第6种，公式如下，按ctrl+shift+enter三键。

=MATCH(1,0/COUNTIF(B2,"*"&ROW($1:$100)&"*"))

依然是通配符的查找方式，只不过现在用的是countif函数。思路和上面的是一样的。

第7种，公式如下，按ctrl+shift+enter三键。

=MATCH(,-SEARCH("*"&ROW($1:$100)&"*",B2))

还是通配符的用法，现在又换成search函数了。只要是支持通配符的查找函数都可以解这个题目。vlookup都可以，你可以试一下。

第8种，公式如下，按ctrl+shift+enter三键。

=MAX(--TEXT(MID(B2,ROW($1:$99),{1,2,3}),"0;;0;!0"))

MID(B2,ROW($1:$99),{1,2,3})部分，用mid函数提取字符，提取的起始位置分别是1~99，提取的长度分别是1，2，3。

也就是从第1位提取1，2，3的长度，从第2位提取1，2，3的长度，···，从第99位提取1，2，3的长度。形成了一个99行3列的二维数组。

TEXT(MID(B2,ROW($1:$99),{1,2,3}),"0;;0;!0")部分，用text将上一步中的正整数显示为原来的值，负数显示为空，0显示为0，文本强制显示为0。

前面加两个负号，是减负运算，负负得正。将文本型数字转为数值，最后用max取最大值就得到了最高考核成绩。

链接：

https://pan.baidu.com/s/12APZvni4OJoPt8a_o1bATg

提取码：w1bk