|
编按： 大家习惯性的认为SUMIF就是1列条件区域1列求和区域。实际上并非如此，SUMIF条件求和的规则或原理：可以有多列条件区域，它按条件区域的大小与求和区域进行成对的条件求和。利用这点可以很简单地搞定多表汇多表查找、并列表汇总、错位求和等等。 在《按条件进行多列求和》的教程中，小窝曾提到SUMIF的奇怪规则，由下面的一个故意为难人的要求引起。小窝的解决方法就是增加两列姓名,让条件区域和求和区域大小一致：以条件区域大小为基础，将条件列（或行）和求和列（或行）按相同列序（或行序）成对，然后累加每对中符合条件的数据。小窝把条件区域各列中“王静”的排序调整一下，大家看得更明白：首先在第1列条件中查找“王静”，返回第1列求和区域中的“142”；接着在第2列条件中查找，返回第2列求和区域中的“109”；再在第3列条件中查找，返回第3列求和区域中的“60”；最后三者相加就等于311。由于是以条件区域的大小为基础进行成对条件求和，所以求和区域可以简写，只引用区域中第一个单元格，SUMIF在实际运算中会自动补齐，让求和区域与条件区域大小相等。了解了SUMIF按照条件区域大小成对进行条件求和的规则，那神秘的错位求和、多表查找就很简单了。所谓的错位求和，就是指条件区域和求和区域有重叠，或者位置有错行或者错列。1）多表汇总、并列表汇总——条件区域和求和区域重叠譬如求下方王文、刘新的总分，可以认为是按条件汇总英语、语文、数学三个并列表中的数据。简单，公式=SUMIF($A$21:$E$26,H20,$B$21:$F$26)，向下填充即可。把姓名到姓名列A21:E26作为条件区域，分数到分数列B21:F26作为求和区域，然后进行成对条件求和，实质如下图。在第1列条件中查“王文”，返回第1列求和区域中的69；在第2列条件中查找，没有符合条件的，返回0……以此类推，完成5对条件求和并累加69+0+99+0+61=229。也很简单，公式=SUMIF(B41:F47,"",B40:F46)将第二次报价到最后一次报价下一行B41:F47作为条件区域，将第一次报价到最后一次报价B40:F46作为求和区域，条件是空。此处既有重叠——条件区域和求和区域存在重叠，又有位置错位——条件区域位与求和区域错开了一行（但是大小一致）。第1列条件中等于空的，对应第1列求和区域中的90和0（空）；第2列条件中等于空的，对应第2列求和区域中的94……以此类推得到各列最后一次报价并累加。如果表格是横向的，也是一样的用SUMIF求和搞定。譬如从下方并列的多个表中查找林菲的成绩和学号。小窝曾分享过用INDIRECT进行多表格查找，但太复杂了。今天借助SUMIF来查找。因为并不知道林菲位于那一列，所以不管是VLOOKUP，还是XLOOKUP，都不好使。公式=SUMIF($A$72:$D$80,$H$72,B72:E80)，然后向右填充即可。由于没有重名，成绩和学号都是数字，所以用SUMIF求和可以完美的搞定多表查找。把姓名到姓名区域$A$72:$D$80作为条件区域，把成绩到成绩区域B72:E80作为求和区域，就可以获得成绩。把姓名到姓名区域$A$72:$D$80作为条件区域，把学号到学号区域C72:F80作为求和区域，就可以获得学号。
|
