Excel ISNA函数




Excel ISNA函数

MS Excel 是一款强大的电子表格软件,允许用户记录大量的数据并使用许多内置函数执行各种操作。在使用Excel表格中的函数和公式时,各种情况可能会导致我们遇到一些错误代码,而不是所期望的结果。然而,当处理大型数据集时,直接通过肉眼来识别或检测此类错误将会很困难。这就是现有的ISNA函数发挥作用的地方,它帮助我们在工作表中识别或检测特定类型的错误。

本文讨论了Excel ISNA函数的简要介绍,以及它的语法、用途和在Excel工作表中的使用方法。我们还讨论了一些相关示例,以帮助我们更好地理解如何在不同的常见情况下在Excel中使用ISNA函数。

Excel中的ISNA函数是什么

ISNA函数是一种内置错误处理Excel函数,属于信息函数的一类。该函数特别有助于找到或处理工作表中的#N/A错误。简而言之,Excel中的ISNA函数帮助我们确定给定的数据集、函数或公式是否存在#N/A错误。该函数使得进行平滑比较和数据分析更加容易,并有助于修复错误。



Excel ISNA函数

此外,ISNA函数可以与其他相关函数(如IF或VLOOKUP函数)结合使用,对工作表中记录的数据集执行其他操作。

Excel ISNA函数的语法

Excel中ISNA函数的语法定义如下:

‘值’是ISNA函数的唯一必需参数。

Excel ISNA函数的参数

如上述语法所示,Excel中的ISNA函数只需要一个参数,例如:

  • :它是一个灵活的、必需的参数,代表要测试#N/A错误的数据。它可以是另一个函数、公式、单元格、范围或直接值。通常,’值’以单元格地址/引用或范围的形式给出。

Excel ISNA函数返回什么

Excel中的ISNA函数返回一个逻辑值,要么是TRUE,要么是FALSE:

  • TRUE :如果给定的’值’参数存在#N/A错误,则函数返回TRUE。
  • FALSE :如果给定的’值’参数中没有找到#N/A错误,则函数返回FALSE。在这种情况下,给定参数可能包含其他值或错误类型。

例如,看一下下面的表格,确定ISNA Excel函数对不同数据集(参数)的行为:

数据 公式 返回值 描述
# N/A =ISNA(#N/A) TRUE 因为给定的值是#N/A错误,ISNA函数返回TRUE。
# DIV/0! =ISNA(#DIV/0!) FALSE 虽然提供的值是Excel错误,但它不是#N/A错误。因此,ISNA函数返回FALSE。
# NAME? =ISNA(#NAME?) FALSE 因为给定的值不是#N/A错误,ISNA函数返回FALSE。
文本 =ISNA(文本) FALSE ISNA函数不将文本值视为有效的参数,返回FALSE。

如何在Excel中使用ISNA函数

最好通过使用样本数据集在Excel工作表中使用ISNA函数来完全理解该函数的概念。让我们考虑以下示例,并在不同情况下使用ISNA Excel函数:

示例1:常规ISNA函数

如上表所示,我们在它的纯粹形式下使用了ISNA函数。然而,ISNA函数更常用的是与其他必要的函数结合使用,以帮助评估我们工作表中相应公式的结果。在这种情况下,我们通常按以下方式应用ISNA函数:

=ISNA(另一个公式())

假设我们有一个示例数据集,其中有两个学生姓名列表分别在A列和D列。A列中的列表包含所有学生的姓名,而D列仅包含考试不及格的学生。

Excel ISNA函数



我们需要比较这两个列表,并确定特定的名称是仅存在于第一个列表中还是同时存在于两个列表中。随后,我们需要使用ISNA函数来确定A列中的学生是否通过或不通过。

为了将第一个学生(单元格A2)的名称与D列中相应的每个名称进行比较,我们可以采用以下方式应用MATCH公式:

=MATCH(A2, D3:D8, 0)

这个公式将匹配单元格A2的值与D列(从D3到D8单元格)进行比较,并返回其相对位置,如果值存在的话。如果值不匹配,该公式将返回#N/A错误。因此,我们可以将上述公式与ISNA函数结合或嵌套在一起,ISNA函数将在匹配函数针对学生名称返回#N/A错误时返回TRUE,意味着相应的名称不在不及格学生列表中。

我们可以按以下方式在ISNA函数中嵌套MATCH公式:

=ISNA(MATCH(A2, D3:D8, 0))

我们将此公式应用于单元格B2,并将其复制到其他单元格直到B16。

Excel ISNA函数

该图像显示ISNA函数返回了布尔值TRUE和FALSE。我们可以看到该函数对于未在B列中列出的学生返回TRUE。让我们了解ISNA函数在返回TRUE和FALSE时的工作原理:

  • TRUE: 学生的姓名在D列中找不到 > MATCH函数返回#N/A错误 > ISNA返回TRUE。
  • FALSE: 学生的姓名在D列中找到 > MATCH函数不返回错误 > ISNA返回FALSE。

示例2:ISNA函数与IF函数结合使用

正如我们上面所说,Excel的ISNA函数只返回布尔值TRUE和FALSE。然而,我们可以将ISNA函数与IF函数结合使用来返回或显示自定义消息。这在某种程度上使ISNA函数的使用更加有效。在这种情况下,通常我们以以下方式应用ISNA函数:

=IF(ISNA(…),”如果存在错误,则显示的消息”,”如果不存在错误,则显示的消息”)

让我们再次使用之前的例子数据集。然而,这一次我们不想显示布尔值TRUE和FALSE的结果,而是需要显示自定义消息。假设我们想要为未失败的学生(即未在D列中列出姓名的学生)显示消息“通过”。我们希望为其他学生显示消息“失败”。

Excel ISNA函数

首先,我们应用MATCH函数,然后将其嵌套在ISNA函数中,就像前面的例子一样。之后,我们将ISNA MATCH公式嵌入到IF函数中,如下所示:

=IF(ISNA(MATCH(A2, D3:D8, 0)), "Passed", "Failed")



和前一个例子一样,我们将该公式应用于单元格B2,并将其复制或拖动到列中的其他剩余单元格,直到B16。

Excel ISNA函数

与前一个示例相比,这次的结果看起来更好、更直观。

示例3:ISNA函数与VLOOKUP函数的结合使用

在Excel中,VLOOKUP函数是最常用的内置函数之一,它帮助我们从一个列表中提取数据到另一个工作表中。然而,在VLOOKUP公式中出现#N/A错误是非常常见的。这个错误通常发生在函数在给定的数据集中找不到查找值时。

将结果单元格中显示#N/A错误改为显示一些有意义的信息或空白格更好。这将使我们的可视化表现更加有效和专业。在这种情况下,ISNA函数非常有用。将ISNA函数与VLOOKUP函数相结合后,它可以帮助确定给定数据集中是否存在#N/A错误。之后,我们可以将ISNA和VLOOKUP函数相结合的公式嵌套在IF函数中,以在#N/A错误的位置返回或检索一个自定义的消息。

ISNA函数与VLOOKUP的语法可以定义如下:

=IF(ISNA(VLOOKUP(…),"custom_message", VLOOKUP(…))

当给定数据集中出现#N/A错误时,这个公式将返回“自定义消息”,否则将返回由VLOOKUP函数获得的结果。

在我们的例子数据集中,假设我们想要显示那些考试未通过的科目的名称。对于其他通过了考试的人,我们需要显示自定义消息“全部通过”。

Excel ISNA函数

首先,根据以下语法构建经典的VLOOKUP公式:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

  • lookup_value :需要查找的值,即学生的姓名或相应的单元格引用。
  • table_array :由查找值组成的区域,例如D3:E8。我们使用绝对引用(D3:E8),这样复制到其他单元格时引用不会改变。
  • col_index_num :从给定的table_array(或区域)中的一列编号,需要检索匹配值。
  • range_lookup :我们想要进行查找的方式。对于近似匹配,我们必须使用TRUE(或1),对于精确匹配,我们必须使用FALSE(或0)。

因此,我们使用以下VLOOKUP公式查找所需的科目:

=VLOOKUP(A2, D3:E8, 2, FALSE)

之后,我们必须将这个VLOOKUP公式与前面示例中讨论的通用IF-ISNA公式结合起来。因此,我们的整个公式变成:

=IF(ISNA(VLOOKUP(A2, D3:E8, 2, FALSE)), "All Passed", VLOOKUP(A2, D3:E8, 2, FALSE))

我们将这个公式应用于我们的第一个结果单元格B2,并将其复制到剩余的单元格直到B16。

Excel ISNA函数

上图显示了那些考试不及格学生的科目名称。对于其余学生,我们看到了一个自定义信息“全部及格”。现在将“全部及格”自定义信息替换为短横线(-)。如下所示更加有效地突出了我们期望的结果:

Excel ISNA函数

示例4:ISNA函数与SUMPRODUCT函数一起使用以计算#N/A错误的次数

ISNA函数可以与SUMPRODUCT函数结合使用,以确定在我们的工作表中某个范围内出现了多少次#N/A错误。在这种情况下,我们可以将相应的函数组合如下:

=SUMPRODUCT(--ISNA(range))

正如前面所说,ISNA函数只返回布尔结果TRUE和FALSE。然而,这里的双重否定(–)将逻辑值评估为1和0,而SUMPRODUCT函数相应地将结果相加。

例如,让我们再次重用样本数据集。

Excel ISNA函数

假设我们想要找出考试通过的学生人数。所以,我们首先在B列(单元格B2到B16)应用MATCH和ISNA函数的组合公式,类似于第一个示例。

Excel ISNA函数

在通过上述公式得到的TRUE和FALSE结果后,我们将匹配公式修改为对查找值范围(A2:A16)进行如下操作:

=MATCH(A2:A16, D3:D8, 0)

上述公式将比较A列和D列中的学生姓名,如果对应姓名出现在A列但不在D列中,则该函数将返回#N/A错误。

接下来,我们将该公式嵌套在ISNA函数和SUMPRODUCT函数中,然后应用到结果单元格B18中,写成如下形式:

=SUMPRODUCT(--ISNA(MATCH(A2:A16, D3:D8, 0)))

该公式返回9,表示有9名学生通过了考试。

Excel ISNA函数

在这里,MATCH函数返回了 9 个 #N/A 错误,因为在列 D 的失败学生列表中找不到这 9 个学生的名字。ISNA函数识别出 #N/A 错误,并对相应值返回 TRUE,然后将其强制转换为 1。最后,SUMPRODUCT函数将所有的 1 相加并返回 9。

重要要点

  • ISNA函数也包括给定数据集中的空白(空)单元格,并相应返回 FALSE,因为空单元格中没有找到 #N/A 错误。
  • 必须注意的是,Excel 中的 ISNA 函数不能帮助我们定位 #N/A 错误在表格中的位置。它只告诉我们给定字段中是否存在 #N/A 错误。
  • 除了使用带有 VLOOKUP 的 IF-ISNA 组合之外,我们还可以使用 Excel 中的 IFNA 函数与 VLOOKUP 结合,以捕捉和处理 #N/A 错误,并显示自定义消息。这可以使公式更短、更易理解。但是,IFNA 函数仅适用于 Excel 2013 及更高版本。
  • 在 Excel 365、2021 和未来的版本中,我们可以利用 XLOOKUP 函数来代替 IF-ISNA-VLOOKUP 的组合。XLOOKUP 函数可以本地处理 #N/A 错误,并根据需要显示自定义消息。



Excel 精选教程
Excel 如何合并单元格而不丢失数据Excel 如何防止列中重复数据Excel 如何防止文本溢出Excel 如何删除第一个字符Excel 如何使用和实现IPMT函数计算贷款付款的利息部分Excel 插入多行的快速方法Excel COUNTIF 和 COUNTIFS 函数Excel 如何将行分组以进行折叠和展开Excel 如何将文本转换为日期和将数字转换为日期Excel 如何编辑、评估和调试公式Excel 随机样本Excel 如何删除文本并保留数字或者相反Excel 如何复制和移动Excel表格Excel 如何在另一个工作表中添加超链接Excel 计算唯一值Excel 如何复制公式,向下复制一列而不改变引用Excel 如何在单个单元格中输入多行Excel 如何实现左侧查找Excel 如何使用Linest函数Excel if match公式用于检查两个或多个单元格是否相等Excel 地址函数与公式Excel 如何更改和自动调整行高Excel SUBTOTAL函数及其公式示例Excel UNIQUE函数:快速找到唯一值的方法Excel 如何将表格转换为范围或反之Excel 如何计算包含特定文本的单元格Excel 如何对列进行分组Excel 如何交换列Excel XLOOKUP函数Excel MAX IF公式:按条件获取最大值Excel 如何比较两列的匹配和差异Excel如何对工作表进行分组和取消分组Excel 如何运行宏并创建宏按钮Excel 复制和移动工作表Excel 图表的技巧、技术和窍门Excel 查找和删除外部链接Excel 如何轻松创建图表并保存为模板Excel 如何插入页码Excel 如何表格的每一页上重复行和列标题Excel 乘法公式Excel 如何添加或创建下拉列表Excel 如何查找重复项Excel 如何添加图表标题Excel 如何处理和处理#DIV/0!错误Excel 如何确定你使用的Excel版本Excel 插入日期Excel 合并两列而不丢失数据Excel 如何将图像文件转换为表格Excel 如何进行数据分析Excel 如何轻松将CSV文件转换为ExcelExcel 如何掌握微软ExcelExcel 公式列表Excel 如何处理#DIV/0!错误Excel 如何进行筛选Excel 计算机的Excel是什么意思Excel 年金函数Excel 电子表格Excel 仪表盘Excel 如何使用SUM函数Excel 个人所得税计算器Excel 发票格式Excel 报价格式Excel 工资表Excel 行和列Excel 如何格式化表格Excel 百分比公式Excel 合并Excel 输入数据Excel VLOOKUP函数Excel 什么是数据透视表Excel ISNA函数Excel 如何查找和删除重复项Excel 如何创建下拉列表Excel 如何合并单元格Excel 如何启用宏Excel 如何计算年龄Excel 如何创建透视表Excel 如何启用开发者选项卡Excel 如何计算标准差Excel 如何计算时间差Excel 如何冻结单元格Excel 什么是假设分析Excel 如何插入复选框Excel 如何插入PDFExcel 如何更改日期格式Excel 如何比较两个表格Excel 如何应用筛选器Excel 如何移动列Excel 如何添加或删除超链接Excel 如何计算平均值Excel 如何分隔文本Excel 如何恢复宏Excel 如何恢复损坏的文件Excel 如何恢复Excel文件Excel 如何删除逗号Excel 如何求和一列Excel 如何删除筛选器Excel 如何删除网格线Excel 如何将Excel转换为CSVExcel 如何创建表格Excel 如何计算IRRExcel 如何计算字符数Excel 如何制作表格Excel 如何替换单词Excel 如何按日期排序Excel 如何隐藏公式Excel 如何进行减法运算Excel 如何使用IF函数Excel 如何创建图表Excel 如何使用CONCATENATE函数Excel 如何进行拼写检查Excel 如何将小写字母转换为大写字母Excel 如何创建仪表板Excel 如何删除重复行Excel 如何使用SUMIFS公式Excel 如何添加分页符