玩转Excel函数之旅（四）


2000年 第44期 

　　前面介绍过求和函数（SUM），它能对选取区域内的数据进行按行（或列）求和，但在实际应用中，经常需要进行有条件的数据汇总。本期介绍的总计函数（SUMIF），将为你解决这个问题；另外介绍的分类汇总函数（SUBTOTAL），可以很容易地计算分类汇总。这两个函数的作用都十分强大，利用它可以简化条件汇总和分类汇总。
　　如^44050201a^1的样表，是某百货公司下属的四个门市部，都销售同样的商品；假设其中的四个售货员，轮流在四个门市部工作，样表是他们在一段时间内的营业销售情况，（其中的“金额”可以用公式计算，方法是在F2中输入公式“=D2＊E2”进行计算，然后按照前面讲述过的拖动进行复制的方法，即可求出各门市部各种商品的销售金额）。下面将举例说明用总计函数（SUMIF），分别按门市部、售货员、某一商品、某一范围进行有关的汇总；用分类汇总函数（SUBTOTAL），分别计算某门市部商品数量的平均值、某门市部的商品数量之和、某门市部货物类别的数目、某门市部最大销售量和最便宜的单价。
#1　　一、对固定的单元格区域进行命名
　　上期已介绍过对一定的单元格区域进行命名的方法，应该值得注意和学会运用，因为命名单元格区域对灵活运用函数是十分重要的，它将为运用函数带来极大的方便。这里先按上期介绍过的方法，利用菜单中的“插入/名称/定义”命令，在“定义名称”的对话框中，分别添加对如下区域的命名：把“A2～A17”命名为“门市部”，把“B2～B17”命名为“售货员”，把“C2～C17”命名为“类别”，下面的几项依次命名为“数量”、“单价”、“金额”。
#1　　二、用总计函数（SUMIF）分别按门市部、售货员、商品类别、某一条件进行有关的汇总
　　1．按门市部进行汇总
　　以“门市部3”进行金额的汇总为例：按我们都已熟悉的方法，首先选取存放数据的单元格，然后选取函数“SUMIF”，在弹出的“粘贴函数”对话框中，设置如^44050201b^2，其中的“Range”为选取的区域，这个区域（前面已定义为“门市部”）是下面条件判断的依据、“Criteria”为计算条件，它将判断选取区域中哪些单元格（即“门市部3”）符合计算要求、“Sum_range”为求和区域，这个区域中的数值（“金额”）用于真正的求和。
　　2．按售货员进行汇总
　　以“刘芳”的销售数量为例，与上述方法一样，只要在“粘贴函数”对话框图中，在“Range”项输入“售货员”，在“Criteria”项中输入“″刘芳″”，在“Sum_range”项中输入“数量”。
　　3．按商品类别进行汇总
　　以“茶叶”的销售金额为例，只要在“粘贴函数”对话框图中，在“Range”项输入“类别”，在“Criteria”项中输入“″茶叶″”，在“Sum_range”项中输入“金额”。
　　4．按一定条件进行汇总
　　如计算除“矿泉水”之外的货款：在“粘贴函数”对话框图中，在“Range”项输入“类别”，在“Criteria”项中输入“″<>矿泉水″”、在“Sum_range”项中输入“金额”。
　　以上按不同的条件进行了“数量”或“金额”的汇总，如果双击存放“门市部3”金额的单元格，其中的计算公式为：“=SUMIF（门市部，″门市部3″，金额）”，如果需要计算其它门市部的汇总金额，只须把计算公式利用“复制”和“粘贴”命令，复制至相对应的单元格后，把“门市部3”修改为其它门市部即可汇总出其它门市部的金额了。同样方法，可完成对各“售货员”的汇总、各类商品的汇总。^44050201c^3是以上各项汇总数据的样表，剩余的各项（如各门市部、各种商品的销售数量）的汇总，大家不妨一试。不难看出，利用函数进行计算时，相对固定的单元格区域命名后，减少了频繁选取单元格区域的次数，给我们操作带来了极大的方便。
#1　　三、分类汇总函数（SUBTOTAL）的应用
　　分类汇总函数（SUBTOTAL）不仅仅是一个求和函数，还能够对给定区域内的数值进行其它计算（见^44050201d^4），它的语法结构为：
　　SUBTOTAL（function_num，ref1）
　　其中的“function_num”是计算类型编号，为一个1到11的数字，它规定所要进行的计算类型，图4为计算类型编号及具体含义；“ref1”为进行汇总数据的单元格区域。
　　应用举例如下：
　　1．计算“门市部2”的“数量”平均值
　　选定函数后，弹出“粘贴函数”对话框中，在“Function_num”项中输入计算类型“1”，单击“Ref1”右侧的按钮，选取“门市部2”对应的“数量”单元格区域（D6～D9），如^44050201e^5，按“确定”按钮完成。
　　2．计算“门市部4”中的数量之和
　　与计算“门市部2”的“数量”平均值相似，计算类型（“Function_num”）为“9”，汇总单元格区域（“Ref1”）为“D14:D17”。
　　3．计算“门市部2”的商品“类别”数目
　　与上例相似的操作，计算类型（“Function_num”）为“2”，汇总单元格区域为“C6:C9”。
　　4．计算最大的销售数量
　　计算类型（“Function_num”）为“4”，汇总单元格区域（“Ref1”）为“D2:D17”。
　　5．计算 “门市部2”中的最便宜的单价
　　计算类型（“Function_num”）为“5”，汇总单元格区域（“Ref1”）为“E6:E9”。
　　分类汇总函数的应用是十分灵活的，这是它与使用菜单中“数据/分类汇总”命令的最大差别，如果应用菜单中的分类汇总命令，往往是对于有标题的某个区域而进行的分类汇总，这样的应用有时缺乏必要的灵活性，不利于实际中的灵活运用，所以应该学会利用分类汇总函数进行分类汇总，这在我们的实际工作中是非常有用的。
　　总结：对指定条件的区域进行汇总和分类汇总，这是我们在实际工作中经常遇到的，利用总计函数（SUMIF）和分类汇总函数（SUBTOTAL）一般就能够处理这些工作，而且具有比较强的灵活性。

 