- Excel 2016函数与公式从入门到精通
- 赛贝尔资讯
- 1926字
- 2021-04-01 12:56:23
1.5 为什么公式正确却得到不正确的结果
公式设置无问题，却无法返回与期望相符的结果，日常运算中经常会遇到这种情况。出现这种情况一般是因为引用的单元格数据有问题，比如对文本型数据计算、数据中有空值等。
1.5.1 有文本数据参与运算
关键点：文本数据参与运算返回0值
操作要点：将文本数值转换为数字格式
应用场景：在下面的工作表中统计了某公司各种产品各季度的销量，需要在F列通过公式统计全年的销量，但是输入了公式：=SUM(B2:E2)，返回的答案却是0，显然返回的结果不正确，其原因是参与计算的单元格数值为文本型数值，这种情况下需要将文本数据转换为数值数据即可解决问题。
①如图1-48所示，F列中虽然使用了正确的求和公式，但返回结果却为0。
图1-48
②选中B2:E6单元格区域，单击旁边的黄色警示按钮，打开下拉菜单。在下拉菜单中选择“转换为数字”命令，如图1-49所示，即可得到正确的结果，如图1-50所示。
图1-49
图1-50
1.5.2 空白单元格不为空
关键点：引用数据源中包含空值造成公式返回错误值
操作要点：使用ISBLANK函数判断单元格中是否是真的空值
应用场景：当公式引用的数据源中是由公式计算返回的空值，或者数据源中包含特殊符号“’”，或者当数据源中设置了自定义单元格格式为“;;;”时，都会造成公式结果返回错误值，因为它们并不是真正的空单元格。
√如图1-51所示，由于使用公式在D7、D9单元格中返回了空字符串，当在E7单元格中使用公式=C7+D7进行求和计算时，出现了错误值。
图1-51
√如图1-52所示，由于B4单元格中包含一个英文单引号，在D2单元格中使用公式=B2+B4求和时出现错误值。
①将光标定位在单元格F2中，输入公式：=ISBLANK(D2)，按Enter键，即可判断是否真的为空值，如图1-53所示。
图1-52
②选中F2单元格，将鼠标指针移至该单元格的右下角，当指针变成黑色十字形时，按住鼠标左键向下拖曳至F9单元格，如图1-54所示。此时可以看到返回值都为FALSE。
图1-53
图1-54
③当找出问题所在后，即可按实际情况解决问题。
专家提醒
ISBLANK函数用来判断单元格内的值是否为真的空值，如果是真的空值则返回TRUE，假空值则返回FALSE。
知识扩展
当单元格格式被设置为“;;;”导致数据被隐藏时，需要打开“设置单元格格式”对话框，单击“自定义”，在“类型”列表中重新单击“G/通用格式”即可恢复显示，如图1-55所示。
图1-55
1.5.3 实际的值与显示的值不同
关键点：自定义数字格式转换为实际数值
操作要点：“开始”→“剪贴板”组→“剪贴板”功能按钮
应用场景：为了输入方便或让数据显示特殊的外观效果，通常会设置单元格格式，从而改变数据的显示方式，但实际数据并未改变。这会造成当使用正确公式时却不能返回正确的结果。
比如在本例的D列中输入公式，从身份证号码中提取员工的出生年份，公式并没有错，但提取出的是年份后的四位数字。
①将光标定位在单元格D2中，输入公式：=MID(C2,7,4)，按Enter键，即可根据身份证号码返回年份，向下复制公式依次得到其他员工的出生年份（可以看到返回的并非年份值），如图1-56所示。
图1-56
②打开“设置单元格格式”对话框后，可以看到C列的身份证号码设置了自定义格式“"340103"@”，如图1-57所示。
图1-57
③选中身份证号码列的C2:C8单元格区域并按Ctrl+C快捷键两次，会在左侧打开“剪贴板”任务窗格，如图1-58所示。单击第一个选项右侧的下拉按钮，在打开的下拉菜单中选择“粘贴”命令。
图1-58
④此时可以在编辑栏中看到身份证号码返回实际数值，同时D列的出生年份返回正确值，如图1-59所示。
图1-59
专家提醒
打开“剪贴板”任务窗格的默认方法，即连续按两次Ctrl+C快捷键，如果使用这种方法无法打开“剪贴板”任务窗格，用户可以在选中单元格区域后，按一次Ctrl+C快捷键进行复制，然后在“开始”选项卡“剪贴板”组中单击对话框启动器按钮，也可以打开“剪贴板”任务窗格。
技高一筹
1．将公式计算结果转换为数值
在完成公式计算后，公式所在单元格显示计算结果，但是其本质还是公式，如果公式计算的此结果移至其他位置使用或是源数据被删除等都会影响公式的显示结果。因此对于计算完毕的数据，如果不再需要改变，则可以将其转换为数值。
①选中包含公式的单元格，按Ctrl+C快捷键执行复制操作，如图1-60所示，打开“设置单元格格式”对话框。
图1-60
②再次选中包含公式的单元格区域，在“开始”选项卡的“剪贴板”组中单击“粘贴”下拉按钮，在下拉菜单中单击“值”按钮，如图1-61所示，即可实现将原本包含公式的单元格数据转换为数值，选中该区域任意单元格，在编辑栏显示为数值而不是公式，如图1-62所示。
图1-61
图1-62
2．改变运算符的优先级顺序
在实际运用中，为满足特定的运算，经常需要改变运算符的默认优先级顺序。这里可以通过先将公式中需要优先计算的部分用括号括起来，用来更改运算符的默认运算顺序。
如图1-63所示中的公式为：=A2+A3+ A4+A5+A6/5*0.2，是先进行乘除运算再进行加运算。更改优先级后为=(A2+A3+A4+ A5+A6)/5*0.2，如图1-64所示，此时会先将A2、A3、A4、A5、A6相加，再除以5，然后乘以0.2。
图1-63
图1-64
读书笔记