如下图,在录入数据时不规范导致所有数据录入在同一单元格中,现在想要快速统计出各单元格中最大数字。
在C2单元格输入公式=MAX((SUBSTITUTE(B2,ROW($1:$100),)<>B2)*ROW($1:$100)),并同时按下CTRL+SHIFT+ENTER三键结束。将C2单元格公式下拉填充至低,即可获得所有单元格最大数值。
下面跟大家简单介绍一下这个公式,首先是row函数,他是返回参数的行号,公式中=ROW($1:$100)的返回值是1-100组成的数组{1;2;3;4;5;6;7……98;99;100}
接着是SUBSTITUTE函数,它主要用来对指定字符串进行替换,SUBSTITUTE函数基本语法结构为:SUBSTITUTE(要替换的文本,旧文本,新文本,[替换第几个]),其中替换第几个为可选, 如果指定了替换第几个,则只有满足要求的旧文本被替换。 否则,文本中出现的所有旧文本都会更改为新文本。
【SUBSTITUTE(B2,ROW($1:$100),)】的作用是将B2单元格中所有1~100的数字替换掉,否则则返回B2单元格。
SUBSTITUTE(B2,ROW($1:$100),)<>B2),是将删除了数字后的文本与B2单元格相对比,如果不等于B2返回TURE,如果等于B2返回FALSE。相当于判断B2单元格是否含有1~100这些数字。它的返回值是一组TURE与FALSE组成的100个逻辑值数组
【(SUBSTITUTE(B2,ROW($1:$100),)<>B2)*ROW($1:$100)】是100个逻辑数组与1~100数值相乘,得到的结果是一个数组,该数组由100个数值组成,分别是B2单元格中包含的所有数字和0。(在Excel中TURE相当于1,FALSE相当于0)
最后是max函数,max函数返回上一步数组中最大数值。即提取B2单元格中最大值。(ps:本方法不适用于含有小数且数字返回在1~100之间。)