日常工作中,我们会经常对数据进行求和,出于不同的需求会按不同的条件去汇总。为此,Excel为我们提供了多种求和方式,例如数据透视表,再例如分类汇总,当然也少不了函数。 今天小花就带大家一起来盘点,Excel中有哪些鲜为人知但是非常牛逼的条件求和函数? 01 错位求和:SUMIF 作为科班出身,SUMIF身上流淌着最正宗的条件求和之血。 =SUMIF(条件区域,条件,求和区域) SUMIF函数的基本用法想必小伙伴们都已经是信手拈来。 这函数烂大街了?? 是时候来点花活了!! 看例子,如此排列的一张表怎么条件求和? SUMIF,专治各种错位求和,不服来辩! 用法延伸: ① 当遭遇隔行求和难题,用SUMIF可破逐一相加的困境。 ② SUMIF函数在通配符的协助下,还可以实现模糊查找 ' * ':任意个字符 “ ? ”:单个字符 02 多条件求和:SUMIFS 作为SUMIF函数的加强版,07及以上版本Excel为我们提供了多条件求和函数SUMIFS。 基本用法: =SUMIFS(求和区域,条件区域1,条件1,条件区域2,条件2……) 如图,求解不同月份不同部门的预算之和: SUMIFS与SUMIF非常接近,不再赘述。 03 BUG函数:SUMPRODUCT 要论最BUG最不讲道理的EXCEL函数,SUMPRODUCT绝对是排得上号。 在条件求和方面,它也能露一手。 万能求和公式: =SUMPRODUCT((条件区域1=条件1)*(条件区域N=条件N)*(求和区域)) 这个函数比较难掌握。不怕不怕,小花曾用三个篇幅精讲过这个函数(文末有链接),大家可以去复习下哦。 04 可见单元格求和:SUBTOTAL 如果你对数据进行了筛选,要如何计算筛选后的单元格之和呢?这时你需要用SUBTOTAL来完成(SUBTOTAL可以忽略因筛选而隐藏的值)。 =SUBTOTAL(功能代码,求和区域1,求和区域2......) 其中,求和用到的功能代码是9和109。 (区别:当有隐藏单元格时,9包含隐藏值求和,109忽略隐藏值求和) 05 颜色求和:GET.CELL Excel对颜色处理的能力一直很薄弱,好在老版本还遗留的一个宏表函数--GET.CELL可以用来做颜色条件求和。 GET.CELL可以返回应用单元格的信息,其基本用法如下: =GET.CELL(信息类型,引用单元格或区域) 其中,信息类型用数字表示,范围为1-66,我们做颜色求和会用到两个数字: 24,首字符的字体颜色; 63,单元格填充颜色 遗憾的是,这个函数只能在定义名称中使用,无法直接在单元格中使用、 以按字体颜色(信息类型24)求和为例 Step1: 定义名称,构建提取颜色对应的值的工具 =GET.CELL(24,B2)+0/NOW() 其中, GET.CELL用来提取颜色 B2表示引用当前单元格C2左边的单元格,因此必须选中C2后做定义名称 0/NOW()保证宏表函数即时更新。 Step2:利用定义好的名称,提取颜色条件区域对应的颜色值,形成辅助列 Step3:运用SUMIF函数做条件求和 06 大道至朴:SUM 所谓万变不离其宗,SUM作为求和的本家,在一些高阶函数或数组中,有时比SUMIF更受欢迎。 比如同样是条件求和,使用SUM函数也可以实现: {=SUM((条件区域N=条件N)*(求和区域))} (数组公式,需要按Ctrl+Shift+Enter三建输入) 07 点睛之笔:SUM+IF 相比于SUM的数组应用,SUM+IF组成的数组公式具有更多变化,应用面更广,更为强大! 例如,我们在预测销额时,采用这样的原则:如果实际已发生,取实际发生额,否则取预算发生额。 如何做这样的条件求和? {=SUM(IF($B$3:$G$3>0,$B$3:$G$3,$B$2:$G$2))} 08 最值求和:SUM+LARGE/SMALL 求最大或最小的几个数之和,怎么破? LARGE(最大)或SMALL(最小)函数显神功。 |
|