论文部分内容阅读
摘要：VLOOKUP函数在会计实务中被广泛应用。本文通过分析VLOOKUP函数在会计应用中的常见#N/A错误，可以让财务人员在对VLOOKUP函数有进一步的了解，并能在以后的会计实务中，采用本文提供的处理办法，轻松应对类似的错误。
关键词：EXCEL 单元格
EXCEL作为一个优秀的数据处理软件，经常被财务人员用在会计实务中，其中VLOOKUP函数以其强大的功能被广泛应用。然而在实际应用时，稍有不慎，就会产生错误，返回一些奇怪的错误代码，其中最常见的是#N/A错误。本文通过实例就一些常见的#N/A错误作一个探讨，并提出相应的解决方案。
实例：某公司2007年12月1日从银行提取现金300元。在图一中，左边是简化的总账科目表，右边是简化的会计凭证表。要求在填制会计凭证表总账科目一栏时，根据科目代号，从总账科目表中找出相应的总账科目。 正确的做法是，在J3单元格中输入公式“=VLOOKUP($I$3，$A$3:$B$4，2，0)”，公式计算结果是“库存现金”。在J4单元格中输入公式“=VLOOKUP($I$4，$A$3:$B$4，2，0)”，公式计算结果是“银行存款”。 在实际工作中，计算J3和J4单元格的值时，经常会产生一些#N/A错误。
1 精确匹配查找时出现#N/A错误
1.1 查询表相关单元格中的字符或者查询字符存在空格或者其它字符 在图二中，在J3单元格中输入公式“=VLOOKUP($I$3，$A$3:$B$4，2，0)”， 在J4单元格中输入公式“=VLOOKUP($I$4，$A$3:$B$4，2，0)”，结果出现了#N/A错误。
我们用LEN函数和CODE函数来测试A4和I3。LEN(A4)=5，CODE(RIGHT(A4))=32；LEN(I3)=5，CODE(RIGHT(I3))=32。通过测试，我们知道，J3的#N/A错误是因为I3单元格包含5个字符，最后一个不可见字符的ASCII码是32，也就是空格，导致A3和I3不匹配。J4的#N/A错误是因为A4单元格包含5个字符，最后一个不可见字符的ASCII码是32，也就是空格，导致A4和I4不匹配。
对于J3的#N/A错误，有两种办法处理，一是删除或者替换I3单元格尾部空格，二是使用TRIM函数清除空格，相应的公式为“=VLOOKUP(TRIM($I$3)，$A$3:$B$4，2，0)”。
对于J4的#N/A错误，是函数的第二个参数中包含空格，同样也有两种办法处理，一是删除或者替换A4单元格尾部空格，二是使用TRIM函数清除空格，在使用第二种方法时，第二个参数已不再是直接引用单元格区域，而是引用对$A$3:$B$4运算TRIM后的内存数组，因此公式就应该应用数组公式，需要按CTRL+SHIFT+ENTER（请注意：此处不用用户输入“{}”）。相应的公式为“{=VLOOKUP($I$4，TRIM($A$3:$B$4)，2，0)}”。
如果我们测试CODE(RIGHT(I3))≠32，也就是说I3单元格的最后一个字符是非空格的不可见字符（由网页复制、其他程序转出的表格，经常会出现这类字符），可以删除该字符或者先复制该字符，然后替换该字符为空白。
1.2 查询表相关单元格中的字符与查询字符的格式不一致 在图三中，在J3单元格中输入公式“=VLOOKUP($I$3，$A$3:$B$4，2，0)”， 在J4单元格中输入公式“=VLOOKUP($I$4，$A$3:$B$4，2，0)”，结果出现了#N/A错误。
J3和J4单元格的#N/A错误都是由于查询表相关单元格中的字符与查询字符的格式不一致造成的。A3是文本格式，I3是常规格式，J3是按数值查文本。A4是常规格式，I4是文本格式，J4是按文本查数值。
对于此类错误，有两种处理方法。 ①可以修改查询字符或者查询表相关单元格的格式，使之统一 修改单元格的格式很简单，但是要注意以下几点：
a仅仅将单元格格式更改还不行。例如对于包含数值的常规单元格将格式改为文本后，单元格中的值仍是数值形式，需要激活（双击）才会真正转为文本。
b需要更改的单元格很多时，可以采用分列或选择性粘贴的方法。 采用分列的方法是，点击菜单栏上的“数据”，选择“分列”，然后根据提示进行操作。 由文本格式转为数值格式也可以采用选择性粘贴的方法，是复制一格式为常规的空白单元格或者在其中输入“0”，然后选定需转换的数据区，进行选择性粘贴，在“运算”区域下选择“加”。例如要把A3单元格转为数值格式，可以在格式为常规的单元格A5中输入数字“0”，接着将其复制，然后选定A3进行选择性粘贴，在“运算”区域下选择“加”即可。 ②用公式处理 在J3单元格中输入公式“=VLOOKUP($I$3&""，$A$3:$B$4，2，0)”，这个公式将数值1001用&连接空字符串""，强制转为文本。 在J4单元格中输入公式“=VLOOKUP($I$4*1，$A$3:$B$4，2， 0)”，这个公式将文本1002用乘1进行运算，强制转为数值。
1.3 查询字符不在查询表的第一列 我们在图一所示的表中最前面插入一列，如图四所示。在K3单元格中输入公式“=VLOOKUP($J$3，$A$3:$C$4，2，0)”，在K4单元格中输入公式“=VLOOKUP($J$4，$A$3:$C$4，2，0)”，结果出现了#N/A错误。 K3和K4单元格出现#N/A错误都是由于查询字符不在查询表的第一列，而是在第二列。VLOOKUP一般要求查询字符在查询表的第一列，如果区域选择错误不在第一列，则查找错误，解决方法为重新选择数据区域。在这里，K3和K4单元格公式中的$A$3:$C$4更改为$B$3:$C$4即可。
1.4 查询字符在查询表中不存在 在图五中，在J3单元格中输入公式“=VLOOKUP($I$3，$A$3:$B$4，2，0)”，结果出现了#N/A错误。 错误的原因是因为查询字符“1000”在查询表中不存在。
2 近似匹配查找时出现#N/A错误 近似匹配查找时出现#N/A错误，除了前述的格式等原因，还有以下两种情况。
2.1 查询表的第一列没有升序排列
我们在图一所示的表中，把B3:C3与B4:C4的位置互换一下，如图六所示。在J3单元格中输入公式“=VLOOKUP($I$3，$A$3:$B$4，2，1)”， 在J4单元格中输入公式“=VLOOKUP($I$4，$A$3:$B$4，2，1)”，结果在J3单元格出现了#N/A错误。
在这里，我们一般使用精确匹配查找，也就是把上述两个公式的第四个参数由“1”更改为“0”。当然也可以使用近似匹配查找，这里出现的#N/A错误是因为查询表的第一列没有升序排列。
如果公式中第四个参数省略，或为true，或为非0数值时，查询表的第一列必须升序排列，否则不会返回期望的结果（不一定报错误）。
2.2 查询字符小于查询表第一列数据的最小值
我们返回去看看图五，如果在J3单元格中用近似匹配查找，输入公式“=VLOOKUP($I$3，$A$3:$B$4，2，1)”，结果同样出现#N/A错误。这是因为查询字符小于查询表第一列数据的最小值。对于此类错误，只要建立可能的最小值，使数据区设计合理就可避免。
3 错误处理
在图五中，在J3单元格中输入公式“=VLOOKUP($I$3，$A$3:$B$4，2，0)”，结果出现了#N/A错误。为了使表格更美观而不要显示#N/A错误，可以使用条件格式或直接使用公式处理。
3.1 使用条件格式
在图五所示的工作表中，选定J3单元格，选择菜单中的“格式－条件格式”，输入公式“=ISNA(J3)”，设定字体颜色与底色（一般是白色）相同。使用这种方式进行错误处理，仍然会显示代表错误的绿色三角。操作结果如图七所示。
3.2 使用公式处理 ①综合应用IF、ISNA和VLOOKUP函数。在图七所示的工作表中，选定J3单元格，输入公式“=IF(ISNA(VLOOKUP($I$3，$A$3:$B$4，2，0))，""， VLOOKUP($I$3，$A$3:$B$4，2，0))”。操作结果如图八所示。 ②综合应用IF、TYPE和VLOOKUP函数。在图七所示的工作表中，选定J3单元格，输入公式“=IF(TYPE(VLOOKUP($I$3，$A$3:$B$4，2，0))=16，""，VLOOKUP($I$3，$A$3:$B$4，2，0))”。操作结果如图八所示。 ③综合应用COUNTIF和VLOOKUP函数。在图七所示的工作表中，选定J3单元格，输入公式“=IF(COUNTIF($A$3:$B$4，$I$3)，VLOOKUP($I$3，$A$3:$B$4，2，0)，"")”。操作结果如图八所示。 ④综合应用IF、ISERROR和VLOOKUP函数。在图七所示的工作表中，选定J3单元格，输入公式“=IF(ISERROR(VLOOKUP($I$3，$A$3:$B$4，2，0))，""，VLOOKUP($I$3，$A$3:$B$4，2，0))”。操作结果如图八所示。 需要说明的是，在上述四种公式中的空字符，也可以根据需要改写成“查无此人”等类似的提示。在这个示例中，可以改写成“无此科目”之类的提示。
通过以上分析，相信对VLOOKUP函数有了进一步的了解，并能在以后的工作中，轻松应对类似的错误。
参考文献：
[1]伊娜.Excel在会计中的应用.高等教育出版社.
[2](美)John Walkenbach.中文版Excel2007高级VBA编程宝典.清华大学出版社.
注：本文中所涉及到的图表、注解、公式等内容请以PDF格式阅读原文
关键词：EXCEL 单元格
EXCEL作为一个优秀的数据处理软件，经常被财务人员用在会计实务中，其中VLOOKUP函数以其强大的功能被广泛应用。然而在实际应用时，稍有不慎，就会产生错误，返回一些奇怪的错误代码，其中最常见的是#N/A错误。本文通过实例就一些常见的#N/A错误作一个探讨，并提出相应的解决方案。
实例：某公司2007年12月1日从银行提取现金300元。在图一中，左边是简化的总账科目表，右边是简化的会计凭证表。要求在填制会计凭证表总账科目一栏时，根据科目代号，从总账科目表中找出相应的总账科目。 正确的做法是，在J3单元格中输入公式“=VLOOKUP($I$3，$A$3:$B$4，2，0)”，公式计算结果是“库存现金”。在J4单元格中输入公式“=VLOOKUP($I$4，$A$3:$B$4，2，0)”，公式计算结果是“银行存款”。 在实际工作中，计算J3和J4单元格的值时，经常会产生一些#N/A错误。
1 精确匹配查找时出现#N/A错误
1.1 查询表相关单元格中的字符或者查询字符存在空格或者其它字符 在图二中，在J3单元格中输入公式“=VLOOKUP($I$3，$A$3:$B$4，2，0)”， 在J4单元格中输入公式“=VLOOKUP($I$4，$A$3:$B$4，2，0)”，结果出现了#N/A错误。
我们用LEN函数和CODE函数来测试A4和I3。LEN(A4)=5，CODE(RIGHT(A4))=32；LEN(I3)=5，CODE(RIGHT(I3))=32。通过测试，我们知道，J3的#N/A错误是因为I3单元格包含5个字符，最后一个不可见字符的ASCII码是32，也就是空格，导致A3和I3不匹配。J4的#N/A错误是因为A4单元格包含5个字符，最后一个不可见字符的ASCII码是32，也就是空格，导致A4和I4不匹配。
对于J3的#N/A错误，有两种办法处理，一是删除或者替换I3单元格尾部空格，二是使用TRIM函数清除空格，相应的公式为“=VLOOKUP(TRIM($I$3)，$A$3:$B$4，2，0)”。
对于J4的#N/A错误，是函数的第二个参数中包含空格，同样也有两种办法处理，一是删除或者替换A4单元格尾部空格，二是使用TRIM函数清除空格，在使用第二种方法时，第二个参数已不再是直接引用单元格区域，而是引用对$A$3:$B$4运算TRIM后的内存数组，因此公式就应该应用数组公式，需要按CTRL+SHIFT+ENTER（请注意：此处不用用户输入“{}”）。相应的公式为“{=VLOOKUP($I$4，TRIM($A$3:$B$4)，2，0)}”。
如果我们测试CODE(RIGHT(I3))≠32，也就是说I3单元格的最后一个字符是非空格的不可见字符（由网页复制、其他程序转出的表格，经常会出现这类字符），可以删除该字符或者先复制该字符，然后替换该字符为空白。
1.2 查询表相关单元格中的字符与查询字符的格式不一致 在图三中，在J3单元格中输入公式“=VLOOKUP($I$3，$A$3:$B$4，2，0)”， 在J4单元格中输入公式“=VLOOKUP($I$4，$A$3:$B$4，2，0)”，结果出现了#N/A错误。
J3和J4单元格的#N/A错误都是由于查询表相关单元格中的字符与查询字符的格式不一致造成的。A3是文本格式，I3是常规格式，J3是按数值查文本。A4是常规格式，I4是文本格式，J4是按文本查数值。
对于此类错误，有两种处理方法。 ①可以修改查询字符或者查询表相关单元格的格式，使之统一 修改单元格的格式很简单，但是要注意以下几点：
a仅仅将单元格格式更改还不行。例如对于包含数值的常规单元格将格式改为文本后，单元格中的值仍是数值形式，需要激活（双击）才会真正转为文本。
b需要更改的单元格很多时，可以采用分列或选择性粘贴的方法。 采用分列的方法是，点击菜单栏上的“数据”，选择“分列”，然后根据提示进行操作。 由文本格式转为数值格式也可以采用选择性粘贴的方法，是复制一格式为常规的空白单元格或者在其中输入“0”，然后选定需转换的数据区，进行选择性粘贴，在“运算”区域下选择“加”。例如要把A3单元格转为数值格式，可以在格式为常规的单元格A5中输入数字“0”，接着将其复制，然后选定A3进行选择性粘贴，在“运算”区域下选择“加”即可。 ②用公式处理 在J3单元格中输入公式“=VLOOKUP($I$3&""，$A$3:$B$4，2，0)”，这个公式将数值1001用&连接空字符串""，强制转为文本。 在J4单元格中输入公式“=VLOOKUP($I$4*1，$A$3:$B$4，2， 0)”，这个公式将文本1002用乘1进行运算，强制转为数值。
1.3 查询字符不在查询表的第一列 我们在图一所示的表中最前面插入一列，如图四所示。在K3单元格中输入公式“=VLOOKUP($J$3，$A$3:$C$4，2，0)”，在K4单元格中输入公式“=VLOOKUP($J$4，$A$3:$C$4，2，0)”，结果出现了#N/A错误。 K3和K4单元格出现#N/A错误都是由于查询字符不在查询表的第一列，而是在第二列。VLOOKUP一般要求查询字符在查询表的第一列，如果区域选择错误不在第一列，则查找错误，解决方法为重新选择数据区域。在这里，K3和K4单元格公式中的$A$3:$C$4更改为$B$3:$C$4即可。
1.4 查询字符在查询表中不存在 在图五中，在J3单元格中输入公式“=VLOOKUP($I$3，$A$3:$B$4，2，0)”，结果出现了#N/A错误。 错误的原因是因为查询字符“1000”在查询表中不存在。
2 近似匹配查找时出现#N/A错误 近似匹配查找时出现#N/A错误，除了前述的格式等原因，还有以下两种情况。
2.1 查询表的第一列没有升序排列
我们在图一所示的表中，把B3:C3与B4:C4的位置互换一下，如图六所示。在J3单元格中输入公式“=VLOOKUP($I$3，$A$3:$B$4，2，1)”， 在J4单元格中输入公式“=VLOOKUP($I$4，$A$3:$B$4，2，1)”，结果在J3单元格出现了#N/A错误。
在这里，我们一般使用精确匹配查找，也就是把上述两个公式的第四个参数由“1”更改为“0”。当然也可以使用近似匹配查找，这里出现的#N/A错误是因为查询表的第一列没有升序排列。
如果公式中第四个参数省略，或为true，或为非0数值时，查询表的第一列必须升序排列，否则不会返回期望的结果（不一定报错误）。
2.2 查询字符小于查询表第一列数据的最小值
我们返回去看看图五，如果在J3单元格中用近似匹配查找，输入公式“=VLOOKUP($I$3，$A$3:$B$4，2，1)”，结果同样出现#N/A错误。这是因为查询字符小于查询表第一列数据的最小值。对于此类错误，只要建立可能的最小值，使数据区设计合理就可避免。
3 错误处理
在图五中，在J3单元格中输入公式“=VLOOKUP($I$3，$A$3:$B$4，2，0)”，结果出现了#N/A错误。为了使表格更美观而不要显示#N/A错误，可以使用条件格式或直接使用公式处理。
3.1 使用条件格式
在图五所示的工作表中，选定J3单元格，选择菜单中的“格式－条件格式”，输入公式“=ISNA(J3)”，设定字体颜色与底色（一般是白色）相同。使用这种方式进行错误处理，仍然会显示代表错误的绿色三角。操作结果如图七所示。
3.2 使用公式处理 ①综合应用IF、ISNA和VLOOKUP函数。在图七所示的工作表中，选定J3单元格，输入公式“=IF(ISNA(VLOOKUP($I$3，$A$3:$B$4，2，0))，""， VLOOKUP($I$3，$A$3:$B$4，2，0))”。操作结果如图八所示。 ②综合应用IF、TYPE和VLOOKUP函数。在图七所示的工作表中，选定J3单元格，输入公式“=IF(TYPE(VLOOKUP($I$3，$A$3:$B$4，2，0))=16，""，VLOOKUP($I$3，$A$3:$B$4，2，0))”。操作结果如图八所示。 ③综合应用COUNTIF和VLOOKUP函数。在图七所示的工作表中，选定J3单元格，输入公式“=IF(COUNTIF($A$3:$B$4，$I$3)，VLOOKUP($I$3，$A$3:$B$4，2，0)，"")”。操作结果如图八所示。 ④综合应用IF、ISERROR和VLOOKUP函数。在图七所示的工作表中，选定J3单元格，输入公式“=IF(ISERROR(VLOOKUP($I$3，$A$3:$B$4，2，0))，""，VLOOKUP($I$3，$A$3:$B$4，2，0))”。操作结果如图八所示。 需要说明的是，在上述四种公式中的空字符，也可以根据需要改写成“查无此人”等类似的提示。在这个示例中，可以改写成“无此科目”之类的提示。
通过以上分析，相信对VLOOKUP函数有了进一步的了解，并能在以后的工作中，轻松应对类似的错误。
参考文献：
[1]伊娜.Excel在会计中的应用.高等教育出版社.
[2](美)John Walkenbach.中文版Excel2007高级VBA编程宝典.清华大学出版社.
注：本文中所涉及到的图表、注解、公式等内容请以PDF格式阅读原文