SUMIF函数 – Excel22

Excel 应用SUMIF函数按给定条件对指定单元格求和

SUMIF函数的功能是按照给定条件对指定的单元格进行求和。其语法如下：

SUMIF(range,criteria,sum_range)

参数range是要根据条件计算的单元格区域，每个区域中的单元格都必须是数字和名称、数组和包含数字的引用，空值和文本值将被忽略。参数criteria为确定对哪些单元格相加的条件，其形式可以为数字、表达式或文本。参数sum_range为要相加的实际单元格（如果区域内的相关单元格符合条件）。如果省略参数sum_range，则当区域中的单元格符合条件时，它们既按条件计算，也执行相加。

【背景知识】参数sum_range与区域的大小和形状可以不同。相加的实际单元格通过以下方法确定：使用sum_range中左上角的单元格作为起始单元格，然后包括与区域大小和形状相对应的单元格，如表13-3所示。

表13-3　确定相加的实际单元格

【典型案例】某班级六名男同学分成两组，进行一分钟定点投篮比赛。A组成员有张辉、徐鑫和郑明涛，B组成员有王明、毛志强和李卫卫。比赛结束后，又来两名同学，分别是李波和王赐，也进行了定点一分钟投篮。现在要计算A组和B组的进球总数及其他人员的进球总数。本例的原始数据如图13-79所示。

步骤1：在D2单元格中输入公式“=SUMIF（A2：A9，”A*”，B2：B9）”，计算A组同学的进球总数。

步骤2：在D3单元格中输入公式“=SUMIF（A2：A9，”B*”，B2：B9）”，计算B组同学的进球总数。

步骤3：在D4单元格中输入公式“=SUM（B2：B9）-SUMIF（A2：A9，”a*”，B2：B9）-SUMIF（A2：A9，”b*”，B2：B9）”，计算其他同学的进球总数。计算结果如图13-80所示。

图13-79　原始数据

图13-80　计算结果

【使用指南】SUMIF函数的主要进行有条件的求和，可以在条件中使用通配符问号（？）和星号（*）。问号匹配任意单个字符；星号匹配任意一串字符。如果要查找实际的问号或星号，请在该字符前键入波形符（~）。

Excel 库存统计相关函数及制作库存统计表

库存统计是对商品的出入库情况进行的综合统计，它包括期初库存、本期入库、本期出库和期末库存等信息。

制作库存统计表

步骤01：打开“进销存管理.xlsx”工作簿，复制“出库单”工作表，将复制后的工作表重命名为“库存统计”，并将其移到工作表的最后位置。选中单元格区域“B1:I11”，然后单击鼠标右键，在弹出的快捷菜单中选择“删除”选项，如图5-92所示。

图5-92　选择“删除”选项

步骤02：弹出“删除”对话框，选中“下方单元格上移”单选按钮，然后单击“确定”按钮，如图5-93所示。

图5-93　设置删除选项

步骤03：此时即可删除单元格区域“B1:I11”中的所有单元格，下方单元格依次上移，如图5-94所示。

步骤04：在“库存统计”工作表中输入表格标题和相应的列标题，然后进行单元格格式设置，并适当地调整各列的列宽，如图5-95所示。

步骤05：在“商品代码”列输入文本型数值代码，然后在C4单元格中输入公式“=IF（ISNA（VLOOKUP（$B4,商品代码表!$B:$G,COLUMN（）,0））,””，VLOOKUP（$B4,商品代码表!$B:$G,COLUMN（）,0））”，按Enter键完成输入，随即返回引用结果。然后将该单元格的公式向右和向下填充（不带格式），如图5-96所示。

图5-94　删除区域中所有单元格

图5-95　输入相关数据

图5-96　设置商品代码公式并填充

步骤06：在F4:F10单元格中输入期初库存数量，然后在单元格G4中输入公式“=IF（ISNA（VLOOKUP（$B4,商品代码表!$B:$G,6,0））,0,VLOOKUP（$B4,商品代码表!$B:$G,6,0）*F4）”，按Enter键完成输入，随即返回计算结果。然后将该单元格的公式填充到该列的其他单元格中，如图5-97所示。

步骤07：导入本期商品入库的数量。在单元格H4中输入公式“=SUMIF（入库明细单!D:D，库存统计!B4,入库明细单!J:J）”，按Enter键完成输入，随即返回计算结果。然后将该单元格的公式填充到该列其他单元格中，如图5-98所示。

步骤08：导入本期商品入库的成本金额。在I4单元格中输入公式“=SUMIF（入库明细单!D:D，库存统计!B4,入库明细单!K:K）”，按Enter键完成输入，随即返回计算结果。然后将该单元格的公式填充到该列其他单元格中，如图5-99所示。

图5-97　计算期初库存成本金额

图5-98　导入本期商品入库的数量

图5-99　导入本期商品入库的成本金额

步骤09：导入本期商品出库的数量。在J4单元格中输入公式“=SUMIF（出库明细单!D:D，库存统计!B4，出库明细单!K:K）”，按Enter键完成输入，随即返回计算结果。然后将该单元格的公式填充到该列其他单元格中，如图5-100所示。

图5-100　导入本期商品出库的数量

步骤10：导入本期商品出库的成本金额。在K4单元格中输入公式“=SUMIF（出库明细单!D:D，库存统计!B4，出库明细单!L:L）”，按Enter键完成输入，随即返回计算结果，然后将该单元格的公式填充到该列其他单元格中，如图5-101所示。

图5-101　导入本期商品出库的成本金额

步骤11：计算期末库存的数量。在L4单元格中输入公式“=F4+H4-J4”，按Enter键完成输入，随即返回计算结果。然后将该单元格的公式填充到该列其他单元格中，如图5-102所示。

步骤12：计算期末库存的成本金额。在M4单元格中输入公式“=G4+I4-K4”，按Enter键完成输入，随即返回计算结果。然后将该单元格的公式填充到该列其他单元格中，如图5-103所示。

步骤13：计算期初库存总成本。将单元格区域D11:F11合并为1个单元格，输入“期初库存总成本”，然后在G11单元格中输入公式“=SUM（G4:G10）”，按Enter键完成输入，随即返回计算结果，如图5-104所示。

图5-102　计算期末库存的数量

图5-103　计算期末库存的成本金额

图5-104　计算期初库存总成本

步骤14：计算期末库存总成本。将单元格区域K11:L11合并为一个单元格，输入“期末库存总成本”。然后在M11单元格中输入公式“=SUM（M4:M10）”，按Enter键完成输入，随即返回计算结果，如图5-105所示。

图5-105　计算期末库存总成本

步骤15：根据前面我们所学过的知识，为库存统计表添加合适的边框并去除网格线，最终效果如图5-106所示。

图5-106　最终效果图

Excel 指定单元格求和：SUMIF函数

SUMIF函数的功能是按照给定条件对指定的单元格进行求和。其语法如下：

SUMIF(range,criteria,sum_range)

range参数是要根据条件计算的单元格区域，每个区域中的单元格都必须是数字和名称、数组和包含数字的引用，空值和文本值将被忽略。criteria参数为确定对哪些单元格相加的条件，其形式可以为数字、表达式或文本。sum_range参数为要相加的实际单元格（如果区域内的相关单元格符合条件），如果省略sum_range参数，则当区域中的单元格符合条件时，它们既按条件计算，也执行相加。

sum_range参数与区域的大小和形状可以不同。相加的实际单元格通过以下方法确定：使用sum_range中左上角的单元格作为起始单元格，然后包括与区域大小和形状相对应的单元格，如表14-1所示。

表14-1　确定相加的实际单元格

下面通过实例详细讲解该函数的使用方法与技巧。

在体育课上，高一一班的6名男同学被分成两组，进行一分钟定点投篮比赛。A组成员有张辉、徐鑫和郑明涛，B组成员有王明、毛志强和李卫卫。比赛结束后，又来两名同学，分别是李波和王赐，也进行了定点一分钟投篮。现在要计算A组和B组的进球总数及其他人员的进球总数。具体操作步骤如下。

STEP01：新建一个空白工作簿，重命名为“SUMIF函数”，在“Shee1”工作表中输入本例中的原始数据，如图14-6所示。

STEP02：选中D2单元格，在编辑栏中输入公式“=SUMIF(A2:A9,”A*”,B2:B9)”，然后按“Enter”键返回，即可计算出A组同学的进球总数，如图14-7所示。

图14-6　原始数据

图14-7　计算A组同学进球总数

STEP03：选中D3单元格，在编辑栏中输入公式“=SUMIF(A2:A9,”B*”,B2:B9)”，然后按“Enter”键返回，即可计算出B组同学的进球总数，如图14-8所示。

STEP04：选中D4单元格，在编辑栏中输入公式“=SUM(B2:B9)-SUMIF(A2:A9,”a*”,B2:B9)-SUMIF(A2:A9,”b*”,B2:B9)”，然后按“Enter”键返回，即可计算出其他同学的进球总数，如图14-9所示。

图14-8　计算B组同学进球总数

图14-9　计算其他同学的进球总数

SUMIF函数主要用于有条件的求和，可以在条件中使用通配符问号（?）和星号（*）。问号匹配任意单个字符；星号匹配任意一串字符。如果要查找实际的问号或星号，可在该字符前键入波形符（~）。

Excel用SUMIF函数统计多个条件的方法

追加带有统计条件的“工作列”

SUMIF 函数和 COUNTIF 函数，都是用于计算符合条件的单元格的总和，以及单元格个数的函数。如果想使用这两种函数计算出2个条件以上的统计结果的话，我们需要稍微动一下脑筋。

比如下表，仅在单元格 H4中为 A 列负责人“吉田”、B 列商品代码为“A001”这个条件下，在 D 列中显示销售额数值。

SUMIF 函数第一参数只能指定1列。但在此表中，元数据中无法在1列中同时判定负责人和商品代码这2个条件。A 列只能判定负责人，B 列只能判定商品代码。

这时候，就需要“在元数据中追加作为新的统计条件的数据列”。这样的做法，通常被称为追加“工作列”或“计算单元格”。

我们来尝试添加结合负责人姓名和商品代码的数据列。具体操作如下。

➊ 在单元格 E4输入下列公式，并一直复制粘贴到数据最后一行。

=A4&B4

➋ 在单元格 H4输入下列公式：

=SUMIF($E:$E,$G4&H$3,$D:$D)

➌ 将单元格 H4中的公式复制至全表。

在这里，设置绝对引用也十分重要。利用指定 SUMIF 函数的参数指定各个单元格时，按几次F4 键就会像上面这样出现符号“$”。

然后，将最开始在 H4中输入的公式一直向右复制至 M 列，向下复制至第8行。这里，为使引用单元格不偏离正确的列和行，设定了绝对引用。

要重视简单易懂

在2007版本之后的 Excel 中，追加了复数条件下也能统计数据总和的 SUMIFS 函数和 COUNTIFS 函数。甚至像前文中的例子一样，不需要追加工作列也可以求和。但是，如果统计条件增多，参数的指定就会变得复杂，因此，需要追加工作列，分成几个步骤来处理。

另外，数组公式和 SUMPRODUCT 函数也可以用同样的方式处理，但就从简单易懂这点上来看，我还是推荐大家采用追加工作列这种方法来处理。

SUMIF函数：不能只导出实数一览表

决不能仅限于导出实数一览表。将得出的数字进行比较，才有其计算的意义。让我们再算一下“结构比率”吧。

➊ 单元格 I2中，销售额输入计算吉田的销售额占整体的比例的公式。

➋ 按下回车键，显示出吉田的销售额在整体中的所占比例。

➌ 拖拽复制到单元格 I7，即可显示每一名负责人所占的比例。

如此一来，即可显示每一名负责人的销售额所占比例，每一名负责人对公司的贡献程度等情况也会一目了然。

另外，比起“A 君的业绩比谁都高啊”这种模糊的说明，通过使用“A 君的销售额占全员的43%”这样带有具体数据表达，就会让说明变得更加详细、更有说服力。

按照负责人分别计算销售情况——SUMIF函数的基础

如下所示，A 列为负责人，D 列为销售额数据。

A 列：负责人，D 列：销售额

以这个数据表格为基础，从 G 列开始，计算每一个负责人的销售额的总和。

在做这项工作时，我看到很多人发生了以下“惨剧”。

使用电子计算器，手动计算数据。
输入“=SUM(D2,D7,D12,D17,D18)”，统计每一名负责人的销售额总数时，不断重复这一操作。

那么，怎样做才正确的呢？

这时我们可以使用 SUMIF 函数，我们看一下具体的操作步骤吧。

➊ 在单元格 H2内输入以下公式

=SUMIF(A:A,G2,D:D)

➋ 按下回车键后，单元格 H2内显示“吉田”负责的销售额。

➌ 将单元格 H2中的公式拖拽复制至 H6，则会显示相应的负责人的销售额。

➍ 想要得出所有负责人的销售额总和时，则需要双击单元格 H7，再按下 AUTOSUM 快捷键Alt += 。

➎ 按下回车键，可得出全员销售额的总和。

SUMIF 函数有三个参数。

第一参数：用于条件判断的单元格区域
第二参数：在第一参数指定的范围里，需要计算总和的行的判定条件
第三参数：实际求和的区域

按照步骤1输入“=SUMIF(A:A,G2,D:D)”这一公式，Excel 会自动识别，做出以下的处理：

需要计算总和的区域为 D 列数值。但并不是要算出 D 列中全部数值的总和。
在 A 列中，只计算与 G2的值相同的行的 D 列数值的总和。

标签：SUMIF函数