|
在你的表格中是不是有时会出现”乱码“，比如#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME？或 #NULL!。你可能会有点疑惑这些是什么鬼，甚至有点担心。不过没关系，学完今天的知识你就再也不会疑惑和担心了，而且有时还会利用它们，不相信？那就请看完这篇文章。你眼中的这些”乱码“在excel中叫错误值，是由于计算出错导致的。 -01- 错误值 在excel中，错误值主要有7种，如下图所示。对其产生的结果进行说明。 #VALUE!是由错误的运算对象类型造成的，比如=1+"e"，将数字和文本进行数学运算，明显是行不通的。 #DIV/0!是由除数是0造成的，比如=2/0，学过数学都知道，这是不行的。 #NAME?是由不能识别的名称造成的，比如=1+e，注意这里的e没有双引号，不是文本，和上面的"e"不一样。在这里将其当作定义名称，也就是说你没有对e进行定义名称，所以识别不到。 #N/A是由函数或公式中没有可用数值造成的，比如=VLOOKUP(8,A2:A8,1,)，在A2:A8这个区域中找8，并且是精确查找，肯定是找不到的，返回#N/A。 #REF!是由删除了引用的单元格造成的，比如当你在一个单元格中输入=row(a1)，紧接着你就把A1单元格删除了，那么就回返回#REF!。 #NUM!是由函数中某个数字有问题时造成的，比如=SMALL(ROW(1:2),4)，small是在一个数组中，取第n个最小值。这里就是在{1;2}这个数组中取第4个最小值。一共就2个值，第1个最小值1，第2个最小值2，肯定取不到4个最小值，返回#NUM!。 #NULL!是由2个不相交的区域产生交点时造成的，意思就是空。比如=C11:C15 D13:E13，2个区域中间加个空格，就相当于它们的交集。这2个区域是没有交集的，返回#NULL!。这种错误值出现的概率比较低。 这就是7种错误值，如果你知道它们是怎么产生的，很好，你可以很快地发现公式出错的原因，快速地进行修改。如果你说记不住，或者没明白，也没关系，因为我们对它处理时是不分它是哪种错误的，是用一个函数统一处理的，那么这个处理错误值的函数就是iferror。 -02- iferror函数 IFERROR 函数可以捕获和处理公式中的错误。 如果公式的计算结果为错误值, 则 IFERROR 返回您指定的值；否则, 它将返回公式的结果。 函数语法结构如下，具有2个参数： IFERROR(value, value_if_error)
备注
-03- iferror示例说明 如下图所示，A列是一些数据，有数字和错误值。在B列和C列分别处理错误值。在B11单元格中输入公式=IFERROR(A11,"有错")，向下填充。判断A11是否是错误值，如果是错误值，返回我们指定的内容"有错"，如果不是错误值，返回它的第一参数。从下图我们可以看到，只有A12单元格的错误值返回"有错"，其他的都是返回原来的值。 我们指定的内容可以是任意值，比如C列，我将其指定为0，公式为=IFERROR(A11,0)。这样有错误值的话返回0，没有的话返回第1参数。 下面要对A1:A14这个区域的数据求和，在E10单元格中输入公式=SUM(A11:A14)，结果返回错误值，因为sum函数不能忽略错误值。先用iferror对错误值处理一下，再求和。 在E11单元格中输入公式=SUM(IFERROR(A11:A14,0))，按ctrl+shift+enter三键。IFERROR(A11:A14,0)这部分是对一组数据处理错误值，是错误值的返回0，不是错误值的返回原来的值。结果为{1;0;2;3}，最后用sum求和。 由于iferror是数组公式要按ctrl+shift+enter三键。哪怕是常量数组，也要按ctrl+shift+enter三键，比如在任一单元格输入公式=SUM(IFERROR({1,2,3},0))，不按三键，结果为1；按三键结果为6。 -04- iferror具体应用 1.提取数字 如下图所示，A列是一些数据，现要求将数字提取出来。在D17单元格中输入公式=MAX(IFERROR(--LEFT(A17,ROW($1:$9)),))，按ctrl+shift+enter三键，向下填充。 LEFT(A17,ROW($1:$9))这部分就是用left从左边提取1位，2位···9位，形成一个数组{"1";"12";"125";"1254";"1254d";"1254da";"1254dad";"1254dad";"1254dad"}，我们想要的是1254这个数字，怎么得到呢？ 在数组前面用负负运算，这样文本型数字转为真正的数字，文本字符转为错误值，形成一个数组{1;12;125;1254;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}。用max取最大值就可以了，但max也不能忽略错误值，所以要用iferror处理错误值。 IFERROR(--LEFT(A17,ROW($1:$9)),)可以看到iferror的第2参数什么也没写，只是留出位置，就相当于是0，形成的数组为{1;12;125;1254;0;0;0;0;0}，最后用max取最大值就完成了。 iferror在处理错误值上非常好用，是不是也挺简单的。在文章的开始说过，有时候处理数据的时候要特意产生错误值，下面来一个简单的利用错误值的例子。 -05- 利用错误值处理数据 1.求大于80分的个数 其实这个题之前就说过了，是个条件计数的问题。计算的方法有很多，今天主要说说利用错误值处理数据。在E22单元格中输入公式=COUNT(0/(A22:A27>80))，按ctrl+shift+enter三键。 A22:A27>80这部分是将A列数据和80比较，大于80的返回true，否则返回false，形成一个数组{TRUE;TRUE;FALSE;FALSE;FALSE;TRUE}。true相当于1，false相当于0，后面会说的。 0/(A22:A27>80)形成的数组为{0;0;#DIV/0!;#DIV/0!;#DIV/0!;0}，可以看到之前的true变为0，false变为错误值#DIV/0!。这一步故意把false变成错误值。 最后用count函数统计数字的个数，这里也就是0的个数，就是我们要求的个数。count会忽略错误值。所以如果要故意利用错误值，那么这个函数必须能处理错误值，或者能忽略错误值，比如lookup函数的查询套路就会利用错误值。 |
|
|
