返回目录:excel表格制作
如果说VBA是Excel里面的大炮,那么函数可以说就是Excel里面的尖刀了。
举个很简单的例子,不懂函数,还在写着=A1+A2+...A10。懂函数的,一个=sum(A1:A10)。
不要笑,还真有这种事,这个事就是前几个月发生在我身边的实习生身上,当时都把我震惊了,这种函数居然也有人不知道?
可能你还是觉得没啥,无所谓。那我在举个例子。
曾经,我在做班级成绩单的时候,需要做一个按学号排序,然后显示每个同学的成绩排名。那个时候,想到的最快方法,就是先按成绩排序一下,然后把名次停进去,然后再按学号重新排序一次。
可是,后来我发现,只需要一个rank函数,即可获取所有人的排名,简单快捷。
比如下面这样:
结果如下:
当然,为了让下拉填充不变形,可以给C2,C5加上固定符号“$”
这里分享一个小技巧,鼠标选中公式里面的C2:C5,按下快捷键“F4”,就可以直接加入固定符号“$”了
比如这样
然后就会变成这样:
之后填充一下就好了。
上面以rank函数为例,只是说明函数的重要性。当然,rank有个bug,就是同等分数下,它会自动分成一大一小排名,而不是并排第几(有空再说如何解决)
好了,下面直接说正题,几个常用的Excel函数。
1.left、right、mid、len、&
为啥把这几个函数一起说。因为经常可以一起用。
left函数,截取最左边的n个字符。
right,截取最右边的n个字符。
mid,自定义开始位置,截取n个字符。
len函数,计算字符长度。
&,它不算函数,只是一个字符连接符号。用于连接自定义内容。
举个例子,假设现在有下面一个目录。
如果后期某个章节发生了变化,如何第二章,变成了第10页,而我希望后面的页码可以同步更新,继续间隔相同的页码,该如何做呢?
我们需要干两件事情,
第一件事情,提取中间的数字。
第二件事情,给左右两侧加入自定义字符。
先说第一件事情,提取中间的数字,我们并不知道中间有几个数字,但是可以用len函数,统计总字符数,然后减去左边和右边的字符,剩下的就是中间的数字了。
公式如下:
注意excel中,中文和数字都是算一个字符。
再说第二件事情,加入自定义字符,我们可以通过“&”符号实现,把需要自定义的东西用英文双引号括起来。
那下一个“第11页”,是不是可以在中间加10即可。
这样的话,后期上面的页码1动了,后面的都可以跟着调,动态调整excel目录,是不是很方便呢。
2.vlookup函数
这个函数不用我说,也是一个经常用到的函数,用于条件查找返回。
比如这样。
其中列序数,是相对于引用数据表的第几列。
false是精确查找,true是模糊查找。
但是注意,vlookup只能查找一个返回值,如果有多个返回值,也只返回一个,所以后期还会讲返回多个返回值的方法。
3.sumif函数
用于条件汇总。就是找到你想查找的所有符合条件的汇总量。
比如我想找所有叫“张三”的人的工资汇总
4.iserro,isna
有时候函数在使用的时候,会因为查找不到,或者其它非逻辑错误而带来的报错,这个时候,我们可以用上面两个函数来消灭这些错误。
比如:
我们在查找“何五”的时候,没有查找到,返回了NA错误。
我们可以用isna来给判断如果是na报错,那么将返回的默认值。
若na报错,就返回空值,这样整个表格就显得比较美观。
同样,如果erro报错,那就用iserro来给报错后的返回值赋值。
比较常用的函数就以上几个,其它的large,small,index,type等用的也比较少,以后有空再说。
最后,我们在分享一个查找的小技能。
我们假设要找“刘三”这个人,但是不想找“刘三x”
我们只需要点击选项,再勾选单元格匹配,这样,就不会多查了。
同样,如果我们想找刘姓家族的人,我们可以输入“刘*”即可找到所有刘开头的单元格(建议勾选单元格匹配)
再同样,如果我们想找刘开头的,两个字名字的人,可以输入“刘?”
但是注意,这个问号,一定要是英文半角的噢。
最后总结一下,在勾选“单元格匹配后”,将对表格的内容进行一对一匹配。
* 代表n个任意字符,可能是1,也可能是100个
? 代表1个任意字符。
# 代表一个任意数字。
基本常用的就这三个了。记住他们,可以让你查找更加得心应手。快去拿你的excel试试吧。
今天的分享就到这里了,我们下期再见。