Excel小技巧:特殊问题特殊符号解决

CFan 电脑爱好者 2019-02-22 08:49应用 标签:技巧

我们经常在Excel中使用函数提高工作效率,不过大家在函数中大多使用的是各种常见参数。在函数中还有一种另类的参数就是各种标点符号,在函数中灵活用好这些标点符号可以解决Excel中的很多特殊问题。

双负号,解决求和难题

由于每个员工电脑操作水平不同,一些初学者喜欢总喜欢在单元中使用类似“1箱”、“1把”之类的计数单位。这样默认这些数据会被Excel识别为“文本数据”,而文本数据是不参与数据运算。直接导致需要对这些数据执行求和运算时,数据显示为“0”。

201803tsfh1

文本数据不参与运算

如果现在不想对原始数据进行更改而直接求和,那么借助双负号(--)就可以实现求和统计。同上在B6处输入公“=SUM(--LEFT(B2:B5,2))”,然后按下Ctrl+Shift+Enter组合键(数组公式),这样就可以正确进行求和了。

201803tsfh2

使用双负号后可以求和

小提示:因为LEFT函数取出的值是文本格式,要参与数据计算要转换成数值。这里双负号意思(-1)×(-1)×LEFT截取的文本,这样可以把结果转换成数值。因为在函数或公式中,运算过程会自动把文本转换为数值(一个隐含过程),再与数值进行运算,负值运算(-)也是一种运算,能把文本转换成数值。比如上例中-("11")=-11,-(-11)=11就完成文本到数据的转换了。

星号,模糊查询更高效

平时在工作中,我们经常需要根据某参数进行模糊查询,以便快速找出符合一类条件的数据,在Excel函数中对于模糊查询就经常使用星号来进行匹配。

比如在一份水果报表中包含很多品种,由于品种较多,在查询的时候经常记不住品名的全称,现在需要在A10中设置一个模糊查询的函数,实现只要输入品种部分名称即可实现对库存数量的查询。

201803tsfh3

模糊查询示例

对于类似模糊查询,现在就可以借助星号实现。查询函数借助Vlookup,定位到B10,输入公式“=VLOOKUP(A10&"*",A2:B7,2,0)”。这样当我们在A10输入需要查询品名的时候,只要输入品名前的部分字符即可,如输入“乔”字即可额快速查询到“乔纳金”品牌的库存数量。

201803tsfh4

模糊查询

美元符号,锁定行列不放松

在Excel函数中我们经常需要锁定引用的行列,比如上述例子,引用数据范围(A2:B7)是不会变化的,如果需要将公式下拉到B11、B12继续查找合适的数据,那么上述公式就会出现错误。比如在B11会变为“=VLOOKUP(A11&"*",A3:B8,2,0)”(查询区域变为“A3:B8”),导致查询错误,因为原来公式是相对引用单元格。

201803tsfh5

相对引用导致错误

对于这类固定数据源的引用可以使用美元符号($)进行绝对引用,在B11中将上述公式更改为“=VLOOKUP(A10&"*",$A$2:$B$7,2,0)”,这样通过$实现对原数据区域的绝对引用,现在下拉公式就不会出错了。

201803tsfh6

绝对引用

双引号,隐藏特定显示值

平时在设定函数后我们经常会下拉引用,不过由于新的单元格没有数据,这样函数在下拉的时候就会出现大量的“#N/A”的提示,造成页面不整洁。

201803tsfh7

没有数据会造成大量的“#N/A”提示

对于这类数据的隐藏可以借助双引号实现,定位到B11,输入公式“=IFERROR(VLOOKUP(A11,$A$2:$B$7,2,0),"")”,即在原来公式的前面加上“=IFERROR((原来函数),"")”这样格式即可,这样原来“#N/A”的提示就会自动被空值替代,页面干净多了。

201803tsfh8

使用IFERROR实现错误值转空

这里双引号意思时表示如果该单元格是错误值,则显示为空。同样可以用在很多函数中,比如需要将公式中的“0”值显示为空,输入类似函数“=IF(B3<27,"库存不足","")”即可。当然在实际使用函数过程中还有很多符号,比如大括号“{}”表示数组公式,问号“?”通配任意单个字符,双括号“()”表示没有参数,方括号“[]”和感叹号“!”表示对表和工作簿的引用等等。这些符号也可在一个函数中组合运用,总之灵活运用好这些符号,可以大大提高工作效率。