上次发表《excel中8个鲜为人知的条件求和公式》后,不少小伙伴留言表示,对文末提及的另3个函数很感兴趣。盛情难却,今天小花就和大家一起来继续深挖条件求和函数。 09 查询求和:SUM VLOOKUP 作为SUM引导的条件求和函数组合,SUM VLOOKUP几乎很少人用过。它是运算和逻辑较为复杂的一种公式组合,有些烧脑,但是一旦掌握,对函数的融会贯通有很大帮助。 初级阶段:单一条件,多项求和 基本语句: {=SUM(VLOOKUP($A$12,$A$1:$D$9,{2,3,4},0))} 语法解析 简单的SUM VLOOKUP求和公式,通过第三个参数{2,3,4}使得VLOOKUP返回三个不同列的对应值,进而求和。此处是数组运算,按Ctrl Shift Enter执行运算。 深入学习:多重条件求和 基本语句: {=SUM(VLOOKUP(T(IF(1,A12&{'语文','数学','英语'})),IF({1,0},$A$2:$A$9&$B$2:$B$9,$C$2:$C$9),2,0))} 语法解析 通过T函数将IF引导的数组转换成文本形式赋予VLOOKUP,从而'骗过'VLOOKUP的语法检测,借由VLOOKUP查询满足两个条件的对应值并求和。 10 跨表条件求和:SUM INDIRECT 高阶函数组合的一个常规命题就是跨表求值,而跨表函数很难脱离INDIRECT,于是SUM INDIRECT的组合应运而生。 初级阶段:汇总各班总成绩,但每个班级的成绩都在各自的工作表内,求和条件是表格名称,如何求解! 语法解析: Indirect函数的唯一参数是地址,根据地址范围区域。公式中将表名作为变量,而单元格区域以“”的文本形式固定下来(因此限制了该组合函数的变化性),通过INDIRECT函数返回表格区域,进而求和。 深入学习:将处于不同工作表不同位置的数按条件汇总求和 看案例,某公司的三个销售分区三种产品的销额明细表分别填列在三个表中,但由于产品排序上的差异给汇总报表带来了麻烦! 基本语句: {=SUM(SUMIF(INDIRECT(ROW($A$1:$A$3)&'区!A2:A6'),A2,INDIRECT(ROW($A$1:$A$3)&'区!B2:B6')))} 语法解析: 通过sumif函数分别求出各表中满足条件的对应值之后,再通过SUM函数将各表之和二次求和得出结果。其中INDIRECT函数负责返回三个表格区域。 应对表格命名无规律的情况,可以用{}将表格名称一一列举,例如: 有趣的是,这个函数你无法观测它的求值过程!!!不信可以动手试试!!! 11 跨表条件求和:SUM OFFSET 在上一篇中,我们介绍过SUM IF的动态求和用法,其实SUM OFFSET和SUMPROUDCT也可以做到,三者有何区别呢? 案例分享 规划问题:SUMIF MIN 最近小花的粉丝遇到了一个问题,要在已知库存中为某一供应商的需求量制定配送计划,表格如下: 已知出货优先顺序是1号仓>2号仓>3号仓,用什么函数可以快速完成配送问题规划求解已呢?学习了求和函数后,小伙伴们不妨开动脑筋。 小花给出的答案:用SUMIF MIN,通过合理锁定单元格,一个公式拖动即可完成规划! 基本公式(以C3为例): =MIN(C$12-SUMIF($B$1:$B1,$B2,C$1:C1),SUMIF($B2,C$1,$F2)) 唠嗑花 ———————— 你工作中有什么经典的案例吗? 和小花一起探讨更实用,更有趣的函数! 学习是一种乐趣,分享是一种品质 ! |
|