从Excel 2007开始,它就陆续有了各种”IFS“类型的函数,比如Sumifs,Countifs,Averageifs,Maxifs,Minifs,Ifs。这些函数允许同时设定多个条件,用起来非常方便。 有些人可能还不知道有这样的函数,今天就来给大家说道说道。 题外话,其实有些时候,并不是函数或公式有多复杂、多么地难理解,而是你知不知道。这就和生活中的各种信息不对称是一样一样地。 在有这些“Ifs”类型的公式之前,比如我们要实现多条件求和,我们通常要用到Sum+If的数组公式,或者使用Sumproduct函数。 有的同学可能还不会用数组公式,在处理实际问题的时候也会非常不方便。 Maxifs、Minifs、Ifs是在Office 365中增加的函数。Excel中没有Maxif和Minif,直接就增加了Maxifs和Minifs。 这些”Ifs“们,简单地来理解就是一种可以设置多个条件的统计函数。下面我们通过实例来看看怎么使用这些函数。 先看看Maxifs和Minifs函数 MAXIFS 函数返回一组给定条件或标准指定的单元格中的最大值。 语法:MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) 注意:max_range 和 criteria_rangeN 参数的大小和形状必须相同,否则这些函数会返回 #VALUE! 错误。 类似的,MINIFS用来返回最小值,其他跟MAXIFS一样。 第一个参数max_range就是你要对哪个区域求最大值。 后面的参数中,每两个一组,分别代表条件区域和条件表达式。 我们来看一个例子,下图是一个计划排产表,我们想用公式来显示每个型号的产品生产的开始日期和结束日期。 开始日期,在J2单元格输入公式 =MINIFS($B$1:$H$1,B2:H2,'>0') 结束日期,在K2单元格输入公式 =MAXIFS($B$1:$H$1,B2:H2,'>0') 公式的第一个参数是计划排产的日期范围。 大家知道,日期也是数值型数据的一种,可以直接转换成数字,可以直接比较大小。 第二个参数是计划数量的区域,计划数量大于0,就表示这一天安排生产了。 所以用公式找到计划数量大于0的日期中的最小值和最大值,就是我们排产的开始日期和结束日期。 假如我们想要增加多个条件,就按照条件区域和条件作为一组来增加到公式里就可以了。 下面我们来说说IFS函数 语法:IFS([Something is True1, Value if True1, [Something is True2, Value if True2],…[Something is True127, Value if True127]) 这个函数也是从公式的字面意思我们不难看出,如果第一个条件的结果是True的话,就返回后面跟着的这个值;否则就开始检查第二个条件,如果第二个条件的结果是True的话,就返回第二个条件后面跟着的值;依此类推...如果到最后一个条件都检查完了还是没有返回结果的话,就返回错误值#N/A。 为了避免出现公式结果出现#N/A错误值,我们可以把最后一个条件直接设成True (划重点),后面接着显示所有条件之外的返回结果。 如下图示例,我们需要根据学生分数划分等级,在C2单元格输入以下公式: =IFS(B2>=90,'优秀',B2>=80,'良',B2>=60,'及格',TRUE,'不及格') 大家看,倒数第二个参数直接就是True,就是用来表示,如果前面的所有条件都不满足,就显示”不及格“这个结果。 最后是Sumifs、Countifs、Averageifs Sumifs语法: SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) Countifs语法: COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2],…) Averageifs语法: AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) 从语法上来看,Sumifs和Averageifs跟前面提到的Maxifs和Minifs相似,第一个参数都是统计的数值区域,后面的参数就是每两个一组,分别代表条件区域和条件表达式。 而Countifs就不一样了,直接就是每两个参数作为一组,分别代表条件区域和条件表达式。 如下图所示的数据,我们需要统计“啤酒”“已开票”的数量、行数、平均值。 数量:在H2中输入公式 =SUMIFS(C2:C11,A2:A11,E2,B2:B11,F2) 行数:在H5中输入公式 =COUNTIFS(A2:A11,E5,B2:B11,F5) 平均值:在H8中输入公式 =AVERAGEIFS(C2:C11,A2:A11,E8,B2:B11,F8) 在Sumifs中C2:C11是统计的数值区域,A2:A11是第一个条件区域,E2是第一个条件区域对应的筛选值;B2:B11是第二个条件区域,F2是第二个条件区域的对应的筛选值。Averageifs跟Sumifs类似。 在Countifs中没有统计的数值区域,A2:A11是第一个条件区域,E5是第一个条件区域对应的筛选值;B2:B11是第二个条件区域,F5是第二个条件区域的对应的筛选值。 好了,“Ifs”类型的函数就介绍完了。 总的来说,这几个函数都不难,比较容易理解,大家练习几遍应该就可以完全掌握。关键点还是在于,首先大家要知道有这样的函数并掌握,在遇到实际问题时才能灵活运用,少走弯路。
本文由公众号 Excel轻松学 友情推荐 |
|