（视频）表格错误值系列课程：错误值#N/A 常见出错原因与解决方法
<b>视频地址1：</b>点击观看<br><br><b>视频地址2：</b>点击观看<br><br>
<b>教程难度：</b>初级<br><br>
<b>图文教程：</b><br>
<p><span style=";font-family:微软雅黑;font-size:16px"><br></span></p><p><span style=";font-family:微软雅黑;font-size:16px">#N/A是一个常见的表格错误值。</span></p><p><span style=";font-family:微软雅黑;font-size:16px"><br></span></p><p style="margin-top:0;margin-right:0;margin-bottom:0;margin-left:0;text-align:justify;text-justify:inter-ideograph"><span style=";font-family:微软雅黑;font-size:16px"><span style="font-family:微软雅黑">常见的错误原因有查找区域不存在查找值、</span></span><span style=";font-family:微软雅黑;font-size:16px"><span style="font-family:微软雅黑">数据类型不匹配、</span></span><span style=";font-family:微软雅黑;font-size:16px"><span style="font-family:微软雅黑">查找数据源引用错误</span></span><span style=";font-family:微软雅黑;font-size:16px"><span style="font-family:微软雅黑">、引用了返回值为</span>#N/A的函数或公式等</span><span style=";font-family:微软雅黑;font-size:16px"><span style="font-family:微软雅黑">。</span></span></p><p style="margin-top:0;margin-right:0;margin-bottom:0;margin-left:0;text-align:justify;text-justify:inter-ideograph"><span style=";font-family:微软雅黑;font-size:16px"><span style="font-family:微软雅黑"><br></span></span></p><p style="margin-top:0;margin-right:0;margin-bottom:0;margin-left:0;text-align:justify;text-justify:inter-ideograph"><span style=";font-family:微软雅黑;font-size:16px"><span style="font-family:微软雅黑"><img src="//res1.wpsacdm.cache.wpscdn.cn/images/a99f246a55d4462202ecc7b87ad9bc36.png" title="中间页_05.png" alt="中间页_05.png" width="800" height="450" style="width: 800px; height: 450px;"></span></span></p><p style="margin-top:0;margin-right:0;margin-bottom:0;margin-left:0;text-align:justify;text-justify:inter-ideograph"><span style=";font-family:微软雅黑;font-size:16px"><span style="font-family:微软雅黑"><br></span></span></p><p style="line-height:150%"><span style="font-family: 微软雅黑;line-height: 150%;color: rgb(30, 30, 30);letter-spacing: 0;font-size: 16px;background: rgb(255, 255, 255)"><span style="font-family:微软雅黑">本期将给大家讲解此错误值主要的出现原因和解决方法。</span></span></p><p style="line-height:150%"><br></p><p style="line-height:150%"><img src="https://res1.wpsacdm.cache.wpscdn.cn/images/bccf8f501a092ee62d67745e209abab1.png" title="教程分界线.png" alt="教程分界线.png"></p><p style="line-height:150%"><br></p><p><strong><span style="font-family: 微软雅黑;color: rgb(34, 34, 38);letter-spacing: 0;font-size: 16px;background: rgb(255, 255, 255)"><span style="font-family:微软雅黑">原因一：</span></span></strong><strong><span style="font-family: 微软雅黑;color: rgb(34, 34, 38);letter-spacing: 0;font-size: 16px;background: rgb(255, 255, 255)"><span style="font-family:微软雅黑">查找区域不存在查找值</span></span></strong><strong><span style="font-family: 微软雅黑;color: rgb(34, 34, 38);letter-spacing: 0;font-size: 16px;background: rgb(255, 255, 255)"><span style="font-family:微软雅黑">会导致</span>#N/A错误</span></strong></p><p><strong><span style="font-family: 微软雅黑;color: rgb(34, 34, 38);letter-spacing: 0;font-size: 16px;background: rgb(255, 255, 255)"><br></span></strong></p><p style="margin-top:0;margin-right:0;margin-bottom:0;margin-left:0;text-indent:0;padding:0 0 0 0 ;background:rgb(255,255,255)"><span style="font-family: 微软雅黑; color: rgb(34, 34, 38); letter-spacing: 0px; font-size: 16px;">▪#N/A错误通常表示公式找不到要求查找的内容</span><span style="font-family: 微软雅黑; color: rgb(34, 34, 38); letter-spacing: 0px; font-size: 16px;">。</span></p><p style="margin-top:0;margin-right:0;margin-bottom:0;margin-left:0;text-indent:0;padding:0 0 0 0 ;background:rgb(255,255,255)"><span style="font-family: 微软雅黑;color: rgb(34, 34, 38);letter-spacing: 0;font-size: 16px"><span style="font-family:微软雅黑">比如查询</span>“计算机”的数量</span><span style="font-family: 微软雅黑;color: rgb(34, 34, 38);letter-spacing: 0;font-size: 16px"><span style="font-family:微软雅黑">，</span></span><span style="font-family: 微软雅黑;color: rgb(34, 34, 38);letter-spacing: 0;font-size: 16px"><span style="font-family:微软雅黑">输入公式</span>=VLOOKUP(G4,$B$1:$D$6,3,0)，</span><span style="font-family: 微软雅黑; color: rgb(34, 34, 38); letter-spacing: 0px; font-size: 16px;">但数据源中不存在“计算机”</span><span style="font-family: 微软雅黑; color: rgb(34, 34, 38); letter-spacing: 0px; font-size: 16px;">，</span><span style="font-family: 微软雅黑; color: rgb(34, 34, 38); letter-spacing: 0px; font-size: 16px;">故返回#N/A</span><span style="font-family: 微软雅黑; color: rgb(34, 34, 38); letter-spacing: 0px; font-size: 16px;">。</span></p><p style="margin-top:0;margin-right:0;margin-bottom:0;margin-left:0;text-indent:0;padding:0 0 0 0 ;background:rgb(255,255,255)"><span style="font-family: 微软雅黑;color: rgb(34, 34, 38);letter-spacing: 0;font-size: 16px"><span style="font-family:微软雅黑">当我们计算求和时，公式中包含了错误返回值</span>#N/A，此时会导致最后计算结果错误。</span></p><p style="margin-top:0;margin-right:0;margin-bottom:0;margin-left:0;text-indent:0;padding:0 0 0 0 ;background:rgb(255,255,255)"><span style="font-family: 微软雅黑;color: rgb(34, 34, 38);letter-spacing: 0;font-size: 16px"><span style="font-family:微软雅黑">为了避免这种情况，可以借助</span>IFERROR函数，将错误值替换为文字或数值信息。</span></p><p style="margin-top:0;margin-right:0;margin-bottom:0;margin-left:0;text-indent:0;padding:0 0 0 0 ;background:rgb(255,255,255)"><span style="font-family: 微软雅黑;color: rgb(34, 34, 38);letter-spacing: 0;font-size: 16px"><span style="font-family:微软雅黑">输入公式</span>=IFERROR(VLOOKUP(G4,$B$1:$D$6,3,0),0)。</span></p><p style="margin-top:0;margin-right:0;margin-bottom:0;margin-left:0;text-indent:0;padding:0 0 0 0 ;background:rgb(255,255,255)"><span style="font-family: 微软雅黑;color: rgb(34, 34, 38);letter-spacing: 0;font-size: 16px">它的意思是当公式VLOOKUP(G4,$B$1:$D$6,3,0)计算结果为错误值时，将返回输入的指定值0。</span></p><p style="margin-top:0;margin-right:0;margin-bottom:0;margin-left:0;text-indent:0;padding:0 0 0 0 ;background:rgb(255,255,255)"><span style="font-family: 微软雅黑;color: rgb(34, 34, 38);letter-spacing: 0;font-size: 16px"><span style="font-family:微软雅黑">这样就可以将</span>H4的错误值转为数值0，避免后续计算错误了。</span></p><p style="margin-top:0;margin-right:0;margin-bottom:0;margin-left:0;text-indent:0;padding:0 0 0 0 ;background:rgb(255,255,255)"><span style="font-family: 微软雅黑;color: rgb(34, 34, 38);letter-spacing: 0;font-size: 16px"><br></span></p><p style="margin-top:0;margin-right:0;margin-bottom:0;margin-left:0;text-indent:0;padding:0 0 0 0 ;background:rgb(255,255,255)"><span style="font-family: 微软雅黑;color: rgb(34, 34, 38);letter-spacing: 0;font-size: 16px"><img src="//res1.wpsacdm.cache.wpscdn.cn/images/2e62d6eece9de4e2cb5ac4e9b745cdb6.gif" title="1.gif" alt="1.gif" width="800" height="411" style="width: 800px; height: 411px;"></span></p><p style="margin-top:0;margin-right:0;margin-bottom:0;margin-left:0;text-indent:0;padding:0 0 0 0 ;background:rgb(255,255,255)"><br></p><p style="white-space: normal;"><strong><span style="color: rgb(34, 34, 38); letter-spacing: 0px; font-size: 16px; font-family: 微软雅黑;">原因二：</span></strong><span style="font-size: 16px;"><strong>查找数据源引用错误</strong></span></p><p style="white-space: normal;"><br></p><p style="margin-top: 0px; margin-bottom: 0px; white-space: normal; padding: 0px; background: rgb(255, 255, 255);"><span style="font-family: 微软雅黑; color: rgb(34, 34, 38); letter-spacing: 0px; font-size: 16px;">▪</span><span style="font-family: 微软雅黑; color: rgb(34, 34, 38); letter-spacing: 0px; font-size: 16px;">以此表格为例，我们可见表格前几项已经完成查找，下拉填充单元格时出现错误值#N/A。</span></p><p style="margin-top: 0px; margin-bottom: 0px; white-space: normal; padding: 0px; background: rgb(255, 255, 255);"><span style="font-family: 微软雅黑; color: rgb(34, 34, 38); letter-spacing: 0px; font-size: 16px;">这是因为</span><span style="font-family: 微软雅黑; color: rgb(34, 34, 38); letter-spacing: 0px; font-size: 16px;">查询“冰箱”时数据源选择为B1:D6</span><span style="font-family: 微软雅黑; color: rgb(34, 34, 38); letter-spacing: 0px; font-size: 16px;">，</span><span style="font-family: 微软雅黑; color: rgb(34, 34, 38); letter-spacing: 0px; font-size: 16px;">公式</span><span style="font-family: 微软雅黑; color: rgb(34, 34, 38); letter-spacing: 0px; font-size: 16px;">填充</span><span style="font-family: 微软雅黑; color: rgb(34, 34, 38); letter-spacing: 0px; font-size: 16px;">复制后</span><span style="font-family: 微软雅黑; color: rgb(34, 34, 38); letter-spacing: 0px; font-size: 16px;">，</span><span style="font-family: 微软雅黑; color: rgb(34, 34, 38); letter-spacing: 0px; font-size: 16px;">数据源由于相对引用变成了B4:D9</span><span style="font-family: 微软雅黑; color: rgb(34, 34, 38); letter-spacing: 0px; font-size: 16px;">，</span><span style="font-family: 微软雅黑; color: rgb(34, 34, 38); letter-spacing: 0px; font-size: 16px;">导致“桌子”查询不到结果</span><span style="font-family: 微软雅黑; color: rgb(34, 34, 38); letter-spacing: 0px; font-size: 16px;">。</span></p><p style="margin-top: 0px; margin-bottom: 0px; white-space: normal; padding: 0px; background: rgb(255, 255, 255);"><span style="font-family: 微软雅黑; color: rgb(34, 34, 38); letter-spacing: 0px; font-size: 16px;"><br></span></p><p style="margin-top: 0px; margin-bottom: 0px; white-space: normal; padding: 0px; background: rgb(255, 255, 255);"><span style="font-family: 微软雅黑; color: rgb(34, 34, 38); letter-spacing: 0px; font-size: 16px;"><img src="http://res1.wpsacdm.cache.wpscdn.cn/images/fc9f94fc6274df7ac79955a6ae9b12e7.gif" title="6.gif" alt="6.gif" width="800" height="411" style="width: 800px; height: 411px;"></span></p><p style="margin-top: 0px; margin-bottom: 0px; white-space: normal; padding: 0px; background: rgb(255, 255, 255);"><span style="font-family: 微软雅黑; color: rgb(34, 34, 38); letter-spacing: 0px; font-size: 16px;"><br></span></p><p style="margin-top: 0px; margin-bottom: 0px; white-space: normal; padding: 0px; background: rgb(255, 255, 255);"><span style="font-family: 微软雅黑; color: rgb(34, 34, 38); letter-spacing: 0px; font-size: 16px;">▪</span><span style="font-family: 微软雅黑; color: rgb(34, 34, 38); letter-spacing: 0px; font-size: 16px;">遇到这种情况，需要将数据范围进行绝对引用。</span></p><p style="margin-top: 0px; margin-bottom: 0px; white-space: normal; padding: 0px; background: rgb(255, 255, 255);"><span style="font-family: 微软雅黑; color: rgb(34, 34, 38); letter-spacing: 0px; font-size: 16px;">选中数据区域，使用F4键快速添加此区域的绝对引用，这样再次下拉填充公式时，就可修复#N/A错误值。</span></p><p style="margin-top: 0px; margin-bottom: 0px; white-space: normal; padding: 0px; background: rgb(255, 255, 255);"><span style="font-family: 微软雅黑; color: rgb(34, 34, 38); letter-spacing: 0px; font-size: 16px;"><br></span></p><p style="margin-top: 0px; margin-bottom: 0px; white-space: normal; padding: 0px; background: rgb(255, 255, 255);"><span style="font-family: 微软雅黑; color: rgb(34, 34, 38); letter-spacing: 0px; font-size: 16px;"><img src="http://res1.wpsacdm.cache.wpscdn.cn/images/aed1bd505b29dee56816f45f8861a908.gif" title="7.gif" alt="7.gif" width="800" height="411" style="width: 800px; height: 411px;"></span></p><p style="margin-top:0;margin-right:0;margin-bottom:0;margin-left:0;text-indent:0;padding:0 0 0 0 ;background:rgb(255,255,255)"><br></p><p style="margin-top:0;margin-right:0;margin-bottom:0;margin-left:0;text-indent:0;padding:0 0 0 0 ;background:rgb(255,255,255)"><br></p><p style="margin-top:0;margin-right:0;margin-bottom:0;margin-left:0;text-indent:0;padding:0 0 0 0 ;background:rgb(255,255,255)"><br></p><p style="margin-top:0;margin-right:0;margin-bottom:0;margin-left:0;text-indent:0;padding:0 0 0 0 ;background:rgb(255,255,255)"><strong><span style="font-family: 微软雅黑;color: rgb(34, 34, 38);letter-spacing: 0;font-size: 16px"><span style="font-family:微软雅黑">原因三：</span></span></strong><strong><span style="font-family: 微软雅黑;color: rgb(34, 34, 38);letter-spacing: 0;font-size: 16px"><span style="font-family:微软雅黑">数据类型不匹配导致</span>#N/A错误</span></strong></p><p style="margin-top:0;margin-right:0;margin-bottom:0;margin-left:0;text-indent:0;padding:0 0 0 0 ;background:rgb(255,255,255)"><strong><span style="font-family: 微软雅黑;color: rgb(34, 34, 38);letter-spacing: 0;font-size: 16px"><br></span></strong></p><p style="margin-top:0;margin-right:0;margin-bottom:0;margin-left:0;text-indent:0;padding:0 0 0 0 ;background:rgb(255,255,255)"><span style="font-family: 微软雅黑;color: rgb(34, 34, 38);letter-spacing: 0;font-size: 16px">▪</span><span style="font-family: 微软雅黑;color: rgb(34, 34, 38);letter-spacing: 0;font-size: 16px"><span style="font-family:微软雅黑">这有可能是因为</span></span><span style="font-family: 微软雅黑;color: rgb(34, 34, 38);letter-spacing: 0;font-size: 16px"><span style="font-family:微软雅黑">单元格格式不同导致的</span></span><span style="font-family: 微软雅黑;color: rgb(34, 34, 38);letter-spacing: 0;font-size: 16px"><span style="font-family:微软雅黑">。</span></span></p><p style="margin-top:0;margin-right:0;margin-bottom:0;margin-left:0;text-indent:0;padding:0 0 0 0 ;background:rgb(255,255,255)"><span style="font-family: 微软雅黑;color: rgb(34, 34, 38);letter-spacing: 0;font-size: 16px">A列</span><span style="font-family: 微软雅黑;color: rgb(34, 34, 38);letter-spacing: 0;font-size: 16px"><span style="font-family:微软雅黑">是文本格式的，</span>G列的序号是数值形式</span><span style="font-family: 微软雅黑;color: rgb(34, 34, 38);letter-spacing: 0;font-size: 16px"><span style="font-family:微软雅黑">，</span></span><span style="font-family: 微软雅黑;color: rgb(34, 34, 38);letter-spacing: 0;font-size: 16px"><span style="font-family:微软雅黑">进行查找时，要求查找值与数据源对象数据类型必须完全一致</span></span><span style="font-family: 微软雅黑;color: rgb(34, 34, 38);letter-spacing: 0;font-size: 16px"><span style="font-family:微软雅黑">。</span></span></p><p style="margin-top:0;margin-right:0;margin-bottom:0;margin-left:0;text-indent:0;padding:0 0 0 0 ;background:rgb(255,255,255)"><span style="font-family: 微软雅黑;color: rgb(34, 34, 38);letter-spacing: 0;font-size: 16px"><span style="font-family:微软雅黑"><br></span></span></p><p style="margin-top:0;margin-right:0;margin-bottom:0;margin-left:0;text-indent:0;padding:0 0 0 0 ;background:rgb(255,255,255)"><span style="font-family: 微软雅黑;color: rgb(34, 34, 38);letter-spacing: 0;font-size: 16px"><span style="font-family:微软雅黑"><img src="//res1.wpsacdm.cache.wpscdn.cn/images/70ccd61f10d8fdd8ff6e0acd925e78c4.gif" title="2.gif" alt="2.gif" width="800" height="411" style="width: 800px; height: 411px;"></span></span></p><p style="margin-top:0;margin-right:0;margin-bottom:0;margin-left:0;text-indent:0;padding:0 0 0 0 ;background:rgb(255,255,255)"><span style="font-family: 微软雅黑;color: rgb(34, 34, 38);letter-spacing: 0;font-size: 16px"><span style="font-family:微软雅黑"><br></span></span></p><p style="margin-top:0;margin-right:0;margin-bottom:0;margin-left:0;text-indent:0;padding:0 0 0 0 ;background:rgb(255,255,255)"><span style="font-family: 微软雅黑;color: rgb(34, 34, 38);letter-spacing: 0;font-size: 16px">▪</span><span style="font-family: 微软雅黑;color: rgb(34, 34, 38);letter-spacing: 0;font-size: 16px"><span style="font-family:微软雅黑">遇到这种情况，统一将文本格式的单元格转为数字就可以了。</span></span></p><p style="margin-top:0;margin-right:0;margin-bottom:0;margin-left:0;text-indent:0;padding:0 0 0 0 ;background:rgb(255,255,255)"><span style="font-family: 微软雅黑;color: rgb(34, 34, 38);letter-spacing: 0;font-size: 16px"> </span></p><p style="margin-top:0;margin-right:0;margin-bottom:0;margin-left:0;text-indent:0;padding:0 0 0 0 ;background:rgb(255,255,255)"><img src="//res1.wpsacdm.cache.wpscdn.cn/images/40b88d7c05a76bd82205b4705e6ef5c5.gif" title="3.gif" alt="3.gif" width="800" height="411" style="width: 800px; height: 411px;"></p><p style="margin-top:0;margin-right:0;margin-bottom:0;margin-left:0;text-indent:0;padding:0 0 0 0 ;background:rgb(255,255,255)"><br></p><p style="margin-left: 0"><strong><span style="font-family: 微软雅黑;font-size: 16px"><span style="font-family:微软雅黑">原因四：</span></span></strong><strong><span style="font-family: 微软雅黑;font-size: 16px"><span style="font-family:微软雅黑">引用返回值为</span></span></strong><span style=";font-family:微软雅黑;font-size:16px">#N/A</span><strong><span style="font-family: 微软雅黑;font-size: 16px"><span style="font-family:微软雅黑">错误的函数或公式</span></span></strong></p><p style="margin-left: 0"><strong><span style="font-family: 微软雅黑;font-size: 16px"><span style="font-family:微软雅黑"><br></span></span></strong></p><p><span style="font-family: 微软雅黑;color: rgb(34, 34, 38);letter-spacing: 0;font-size: 16px;background: rgb(255, 255, 255)">▪</span><span style=";font-family:微软雅黑;font-size:16px"><span style="font-family:微软雅黑">以此数据表格为例，我们可见单元格中包含了返回</span>#N/A错误值的公式函数。</span></p><p><span style=";font-family:微软雅黑;font-size:16px"><span style="font-family:微软雅黑">若我们想计算第</span>6行数值总和，此时我们可见计算结果为#N/A错误值。</span></p><p><span style=";font-family:微软雅黑;font-size:16px"><span style="font-family:微软雅黑">这是因为在运算中使用了返回值为</span>#N/A错误值公式。</span></p><p><span style=";font-family:微软雅黑;font-size:16px"><br></span></p><p><span style=";font-family:微软雅黑;font-size:16px"><img src="//res1.wpsacdm.cache.wpscdn.cn/images/c7abc3d6838df283d458a01f84d17971.gif" title="9.gif" alt="9.gif" width="800" height="411" style="width: 800px; height: 411px;"></span></p><p><span style=";font-family:微软雅黑;font-size:16px"><br></span></p><p><span style="font-family: 微软雅黑;color: rgb(34, 34, 38);letter-spacing: 0;font-size: 16px;background: rgb(255, 255, 255)">▪</span><span style=";font-family:微软雅黑;font-size:16px"><span style="font-family:微软雅黑">遇到这种情况，我们可以使用</span>IFERROR函数。</span></p><p><span style=";font-family:微软雅黑;font-size:16px"><span style="font-family:微软雅黑">通过修改返回值为</span> #N/A的单元格，从而更正最终计算结果。</span></p><p><span style=";font-family:微软雅黑;font-size:16px"><br></span></p><p><span style=";font-family:微软雅黑;font-size:16px"><img src="//res1.wpsacdm.cache.wpscdn.cn/images/b9f3cfe9a28f607a596f1d704c425ff6.gif" title="10.gif" alt="10.gif" width="800" height="411" style="width: 800px; height: 411px;"></span></p><p><span style=";font-family:微软雅黑;font-size:16px"><br></span></p><p style="line-height: 150%;"><span style="font-family: 微软雅黑;line-height: 150%;color: rgb(30, 30, 30);letter-spacing: 0;font-size: 16px;background: rgb(255, 255, 255)"><span style="font-family:微软雅黑">以上就是错误值</span></span><span style=";font-family:微软雅黑;line-height:150%;font-size:16px">#N/A</span><span style=";font-family:微软雅黑;line-height:150%;font-size:16px"><span style="font-family:微软雅黑">常见的出错原因，你都了解了吗</span></span><span style=";font-family:微软雅黑;line-height:150%;font-size:16px"> ？</span></p>
頁:
[1]