写本篇文章的初心在于方便以后复习,记录了本人在工作学习中对Excel使用的总结,内容涵盖常用函数、数据透视表、数据透视图等,其中许多知识来源于网络,若有错误,欢迎大家批评指正,谢谢!
主要是文本、格式以及脏数据的清洗和转换。
1、trim:清除掉字符串两边的空格。
instance_num用来指定新字符串替代第几次出现的原字符串。
其中:text表示要截取的字符串,num_chars表示要截取的字符数
其中:text表示要截取的字符串,start_num表示从第几位字符串开始截取,num_chars表示要截取的字符数。
是按列查找,最终返回该列所需查询列序所对应的值;与之对应的HLOOKUP是按行查找的。
=vlookup(查找的值,查找的范围,值在查找范围的第几列,是否精准匹配)
精确匹配,输入“FALSE"或者“0”;
模糊匹配,输入“TRUE"或者“1”。
例:查找下表中“小富”的职务。
COUNTIF:单个条件的计数。
COUNTIFS:既能解决多个条件的计数,也能解决单个条件的计数。
=COUNTIFS(条件匹配查询区域1,条件1,条件匹配查询区域2,条件2,以此类推......)
SUMIF:单条件求和
=SUMIFS(求和的区域,条件区域一,条件一,条件区域二,条件二….)
VAR.P:用于计算基于整个样本总体的方差(忽略样本总体中的逻辑值和文本)。
VAR.S:用于估算基于样本的方差(忽略样本中的逻辑值和文本)。
附:数据透视表/数据透视图展示
多条件求和,一听就好厉害的样子,用起来嘛,也的确挺厉害的的,可还是有不少小伙伴对这个函数表示不懂,那么,我们今天就用一个例子,来实际讲解,看能否把这个函数稍讲明白些。
话不多说,先上语法,这个有个印象就好,等会例子讲解会详细解释
SUMIFS(求和区域,指定条件区域1,条件1,【指定条件区域2,条件2】,...)
其实它的本质,我们可以理解为,对各条件区域,按指定的条件来筛选,筛选出来之后,再对求和区域进行求和,就是这么简单。
什么,还是有点迷糊?正常!我们用例子来讲解,实践出真理。
如图,是猪爸爸数据捏造局捏造的数据,有商品、销售人员、销售数量、销售额4列内容,此次,我们想通过指定销售人员,销售数量,来统计符合条件的商品的销售额。
那么,我们的求和区域,就是销售额列了,在此例中,就是指D1:D19区域,那么,公式的第一个参数就出来了,公式就是
之后,就是指定条件区域1了,我们想看销售人员宋江的销售额,那么指定条件区域1就是销售额这个区域了,销售额在哪列?没错,B列中的B1:B19了,而条件就是“宋江”了,公式就变成了SUMIFS(D1:D19,B1:B19,"宋江",
注意这里,因为宋江是文字,所以我们需要用英文状态的双引号包起来,不然就会出错了。
接着,除了是宋江还不行,我们还需要销售数量是大于等于71的,那么销售数量是哪列呢?对了,C列,所以指定条件区域2就是C1:C19,而条件就是“>=71“了,公式就变成了
同样的,判断条件,也需要用双引号包起来。
可以看动图了,这次求和,其实就是筛选宋江出来,然后销售数量大于等于71,有2个结果是符合条件的,1个销售额为800,1个为1600,所以求和结果就是2400了。
2、通过单元格指定条件
当然,这样子写公式,比较麻烦,那么,其实我们也可以把条件放到单元格里边,这样子,公式就能够简单些了
如图,在F11单元格,输入指定人名,在G11单元格,输入数量条件,这样子,当你想更改条件的时候,就只需要在单元格更改就可以了。
例如,我们想找宋开头的销售人员,则条件为【宋*】,销售数量为71的,则条件为【71】,这样子就可以快速更改条件了。
我们可以看到,符合条件的销售人员,有宋江,宋河,然后销售数量为71的,一个销售额为800,一个销售额为860,则最终求和结果为1660了。
好了,今天的分享就到此了,希望看完这篇文章,你能够理解sumifs函数的用法,祝好运~~
手:你说啥?鼠标要点哪里?要怎么点击?
光看不练,上阵头晕,大家多多练习下,才能真正掌握哦~~
在看~~点赞~~谢谢大家~~
我的财宝吗?想要的话就给你吧,去找吧!我把世界的一切都放在那里~~
今天的分享就到这里,大家生活愉快~~