09sumif的多表多列多条件求和.doc

【龙逸凡】Excel你真会了吗？ 09：深入理解SUMIF：如何多表多列多条件求和？一、基本用法翻看Excel帮助，SUMIF作用是“根据指定条件对若干单元格求和”，言简意不赅，惜字如金啊。说明白点就是“对条件区域进行判断，如果某些单元格满足指定条件，则对求和区域所对应的若干单元格进行求和”。如图一，求A列姓名为龙逸凡的金额之和，则公式为“=SUMIF(A2:A11,龙逸凡,B2：B11)”。结果为17。技巧1：SUMIF可以使用通配符，如统计龙姓员工的金额之和，则公式为=SUMIF(A2:A11,龙*,B2:B11)。技巧2：如果将SUMIF的第三个参数省略，则对条件区域中的单元格求和，如公式=SUMIF(B2:B11,3)统计B2：B11单元格区域大于3的数之和。注意：不能简写成=SUMIF(B2:B10,3,)。技巧3：SUMIF不但能对列区域求和，还能对行区域求和，大家不要被自己的固有思维限制了。如公式“=SUMIF(A1:H1,龙逸凡,A2:H2)”。上面的基本形式大家都会，下面我们逐步深入。二、简化形式一般情况，SUMIF的第一参数和第三参数的区域应该是单列，并且大小相同，但是，如果我们两参数区域不等，会怎么样呢？比如，将本文第一个公式写成下面的公式会怎么样呢？=SUMIF(A2:A11,龙逸凡,B2)=SUMIF(A2:A11,龙逸凡,B2:B3)=SUMIF(A2:A11,龙逸凡,B2:B10)=SUMIF(A2:A11,龙逸凡,B2:C1000)=SUMIF(A2:A11,龙逸凡,B2:B4:D1000)经测试，上面的五个公式等价，结果是一样的，都是17。也许你已经看出规律，第三参数真正起作用的就是第三参数单元格区域的左上角那个单元格。因而我们完全可以将公式简化成=SUMIF(A2:A5,160000,B2)。题外话：B2:B4:D1000这种奇怪的形式实际上就是此单元格区域最左最右最上最下单元格所组成的矩形区域，比如=SUM(B2:B3:B5:D5:D9:D14)，双击单元格编辑公式时Excel显示的引用范围为B2:B3、B5:D5、D9:D14，但实际上就是对B2：D14组成的矩形区域求和，而不是等同于=SUM(B2:B3，B5:D5，D9:D14)。三、定位原理既然起作用的就是第三参数单元格区域的左上角那个单元格，那其真正的原理或者定位机制是什么？我们来探索一下，将公式再变一下：=SUMIF(A2:A11,龙逸凡,B3)公式结果为21,为什么是21呢？实际上它是2+4+7+8的结果。A2：A11单元格区域为“龙逸凡”的分别为从A2单元格开始数的第1、3、6、7个，求和的单元格刚好也是由B3单元格开始数的第1、3、6、7个，即B3、B5、B8、B9单元格。同理，=SUMIF(A2:A11,龙逸凡,B4)结果为25，为B4单元格开始数的第1、3、6、7个，即B4、B6、B9、B10单元格。据此可知：第三参数单元格区域起作用的就是左上角那单元格，此单元格的作用是定位定点，只要有此定位点，SUMIF会自动以此单元格为原点，按照第一参数区域符合条件的单元格的坐标，找到同样坐标位置的单元格，并对其数值求和。此规律同样适用多列区域或矩形区域，示例参见后文。四、多条件求和在搞清楚第三参数的定位原理后，我们先来看一下如何用SUMIF进行简单的多条件求和，然后再研究多列、多表格求和。我们知道，一般情况下，SUMIF只能单条件求和，如果要多条件求和，那怎么办呢？1、多列多条件求和遇到此情况，如果不使用SUM数组公式、SUMIFS或SUMPRODUCT函数,要用SUMIF来多条件求和的话，则需要使用辅助列，将需要条件判断的字段用连接符连接起来，将多列的多条件变为单条件，然后使用类似：=SUMIF(sheet1!F1:F1000,A1&B1&C1,sheet1!D1:D1000)的公式进行求和。这不是本文讨论的话题，就不举例了。关于多条件求和，请参见Excel多条件求和 & SUMPRODUCT函数用法详解，地址/archives/2010/124086.html。2、单列多条件求和如上图一，如果统计A列龙逸凡和罗惠民的B列金额之和，则公式为：=SUM(SUMIF(A2:A11,龙逸凡,罗惠民,B2:B11)结果为25。此公式使用常量数组将两个条件逐一传递给SUMIF，然后再使用SUM来统计各条件结果之和。如果要统计龙姓员工和罗姓员工且不包含罗惠民的金额之和（36），公式为：=SUM(SUMIF(A2:A11,龙*,罗*,罗惠民,B2:B11)*1,1,-1)【龙逸凡提示】：此公式不必使用CTRL+SHIFT+ENTER键来输入，和普通的公式一样输入就行了。五、多行或多列的多条件求和SUMIF是否只能单行单列条件求和？非也，还可多行或多列条件求和，我们仍以图一为例，求A1：D11区域龙逸凡的金额之和，公式为=SUMIF(A2:D11,龙逸凡,B2)结果为121。【龙逸凡提示】：不能写成=SUMIF(A2:D11,龙逸凡,A2：D11)，至于为什么，请细读一下本文的第三点“定位原理”。我们将数据区域再变一下，以帮助大家更深入理解SUMIF，请看图二如果要统计A1：D11区域龙逸凡对应的金额之和，公式为：=SUMIF(A2:B11,龙逸凡,C2:D11)=SUMIF(A2:B11,龙逸凡,C2)结果为161。如果要统计A1:D1区域龙逸凡和罗惠民对应金额之和呢？公式为：=SUM(SUMIF(A2:B11,龙逸凡,罗惠民,C2)结果为206。六、多表单条件求和假设有三张表，分别为sheet1、sheet2、sheet3，三张表格式均如图一所示，要求三表中A列为龙逸凡的金额之和，公式为：=SUM(SUMIF(INDIRECT(sheet&1,2,3&!A2:A11),龙逸凡,INDIRECT(sheet&1,2,3&!B2:B11)结果为51（17*3）。【龙逸凡作品链接】1、逸凡对账能手V2.3，最好的对账软件2、逸凡账务系统V4.0,小企业、兼职代账专用3、逸凡账务系统V3.0正式版，永久免费使用4、逸凡工作簿合并助手,Excel表格合并不用愁5、龙逸凡Excel培训手册之潜龙在渊6、龙逸凡Excel培训手册之飞龙在天7、煤炭行业财务人员行业知识汇编8、Excel多条件求和 & SUMPRODUCT函数用法详解9、用自定义格式进行数字缩放&自定义功能及技巧介绍【龙逸凡】Excel你真会了吗？系列：08：Excel