Excel函数实例

应用过程：

1、启动Excel2003（其他版本请仿照操作），打开“员工基本情况登记表”工作簿（如图1）。

2、切换到Sheet2工作表中，仿照图2的样式制作好简历表的框架。

3、分别选中需要填写内容的单元格（除B2单元格外，如D2、F2

等），输入公式：=IF(ISERROR(VLOOKUP(B2,简

历!A3:K12,*,FALSE)),\"\简历!A3:K12,*,FALSE))。 注意：①上述公式中的“*”号，表示“简历”表中，相应的数据所对应的列数：如“出生时间”在第“5”列（参见图1），在J2

1

单元格中输入公式时，将“*”修改为“5”，即：=IF(ISERROR(VLOOKUP(B2,

历!A3:K12,5,FALSE)),\"\简历!A3:K12,5,FALSE))。 ②公式的含义是：如果公式“VLOOKUP(B2,简历!A3:K12,5,FALSE)”返回错误（ISERROR），则有显示“空”（\"\"），反之显示公式的返回结果。

4、选中J2单元格，再按住Ctrl键，点击C3单元格，同时选中J2和C3单元格，执行“格式→单元格”命令，打开“单元格格式”对话框（如图3），在“数字”标签中，选中“分类”下面的“日期”选项，然后在右侧选定一种日期类型，确定返回。

简

5、选中B5单元格，再次打开“单元格格式”对话框，切换到“对齐”标签下（参见图4），将“水平对齐”、“垂直对齐”分别设置成“靠左”、“靠上”，并选中“自动换行”选项，确定返回。

2

6、设置好表格的字体、字号等格式。

以后，需要打印某位员工简历表时，只要在B2单元格中输入员工的姓名，确认后，该员工的简历情况，自动填入表格相应的单元格中（参见图2），打印即可。

注意：如果需要在大量数据源表格中浏览某条具体数据时，采取此方法也是非常有效的！

用Excel统计各分数段学生数

文章导读：

方法一：用COUNTIF函数统计

这是最常用、最容易理解的一种方法，我们用它来统计“语文”学科各分数段学生数。

如果某些学科（如体育），其成绩是不具体数值，而是字符等级

3

（如“优秀、良好”等），我们也可以用COUNTIF函数来统计各等级的学生人数。

方法二：用DCOUNT函数统计

这个函数不太常用，但用来统计分数段学生数效果很不错。我们用它统计“数学”学科各分数段学生数。 方法三：用FREQUENCY函数统计

这是一个专门用于统计某个区域中数据的频率分布函数，我们用它来统计“英语”学科各分数段学生数。 方法四：用SUM函数统计

我们知道SUM函数通常是用来求和的，其实，他也可以用来进行多条件计数，我们用它来统计“政治”学科各分数段的学生数。

方法一：用COUNTIF函数统计

这是最常用、最容易理解的一种方法，我们用它来统计“语文”学科各分数段学生数。函数功能及用法介绍

①分别选中C63、C67单元格，输入公式：=COUNTIF(C3:C62,\"<60\")和=COUNTIF(C3:C62,\">=90\")，即可统计出“语文”成绩“低于60分”和“大于等于90”的学生人数。

4

②分别选中C、C65和C66单元格，输入公式：=COUNTIF(C3:C62,\">=60\")-COUNTIF(C3:C62,\">=70\")=COUNTIF(C3:C62,\">=70\")-COUNTIF(C3:C62,\">=80\")

、和

=COUNTIF(C3:C62,\">=80\")-COUNTIF(C3:C62,\">=90\")，即可统计出成绩在60-69分、70-79分、80-分区间段的学生人数。 注意：同时选中C63至C67单元格，将鼠标移至C67单元格右下角，成细十字线状时，按住左键向右拖拉至I列，就可以统计出其它学科各分数段的学生数。

如果某些学科（如体育），其成绩是不具体数值，而是字符等级（如“优秀、良好”等），我们可以用COUNTIF函数来统计各等级的学生人数。

如果某些学科（如体育），其成绩是不具体数值，而是字符等级（如“优秀、良好”等），我们可以用COUNTIF函数来统计各等级的学生人数。

①在K至K67单元格中，分别输入成绩等级字符（参见图2）。

5

②选中L单元格，输入公式：=COUNTIF($L$3:$L$62,K)，统计出“优秀”的学生人数。

③再次选中L单元格，用“填充柄”将上述公式复制到L65至L67单元格中，统计出其它等级的学生人数。 上述全部统计结果参见图1。

方法二：用DCOUNT函数统计

这个函数不太常用，但用来统计分数段学生数效果很不错。我们用它统计“数学”学科各分数段学生数。

①分别选中M63至N72单元格区域（不一定非得不这个区域），

6

输入学科名称（与统计学科名称一致，如“数学”等）及相应的分数段（如图2）。

②分别选中D63、D„„D67单元格，输入公式：=DCOUNT(D2:D62,\"数学\、=DCOUNT(D2:D62,\"数学\

、

=DCOUNT(D2:D62,\"

数

学

\

、

=DCOUNT(D2:D62,\"数学\、=DCOUNT($D$2:$D$62,\"数学\，确认即可。

注意：将上述公式中的“DCOUNT”函数换成“DCOUNTA”函数，同样可以实现各分数段学生人数的统计。 方法三：用FREQUENCY函数统计

这是一个专门用于统计某个区域中数据的频率分布函数，我们用它来统计“英语”学科各分数段学生数。 函数功能及用法介绍 ①分别选中O至O67单元格，输入分数段的分隔数值（参见图2）。

7

②同时选中E63至E67单元格区域，在“编辑栏”中输入公式：=FREQUENCY(E3:E62,$O$:$O$67)，输入完成后，按下“Ctrl+Shift+Enter”组合键进行确认，即可一次性统计出“英语”学科各分数段的学生人数。

注意：①实际上此处输入的是一个数组公式，数组公式输入完成后，不能按“Enter”键进行确认，而是要按“Ctrl+Shift+Enter”组合键进行确认。确认完成后，在公式两端出现一个数组公式的标志“{}”（该标志不能用键盘直接输入）。②数组公式也支持用“填充柄”拖拉填充：同时选中E63至E67单元格区域，将鼠标移至E67单元格右下角，成细十字线状时，按住左键向右拖拉，就可以统计出其它学科各分数段的学生数。 方法四：用SUM函数统计

我们知道SUM函数通常是用来求和的，其实，他也可以用来进行多条件计数，我们用它来统计“政治”学科各分数段的学生数。 函数功能及用法介绍

8

①分别选中P至P69单元格，输入分数段的分隔数值（参见图2）。

②选中

F63

单元格，输入公式：

=SUM(($F$3:$F$62>=P)*($F$3:$F$62 ③再次选中F63单元格，用“填充柄”将上述公式复制到F至F67单元格中，统计出其它各分数段的学生人数。

注意：用此法统计时，可以不引用单元格，而直接采用分数值。例

如

，

在

F

单

元

格

中

输

入

公

式

：

=SUM(($F$3:$F$62>=60)*($F$3:$F$62<70))，也可以统计出成绩在60-69分之间的学生人数。

注意：①为了表格整体的美观，我们将M至P列隐藏起来：同时选中M至P列，右击鼠标，在随后出现的快捷菜单中，选“隐藏”选项。

9

Excel自动统计学生成绩

每到学期结束时，教师的一项重要工作就是要统计学生的成绩，在电脑逐步普及的今天，我们就把这项繁杂的工作交给Excel去自动完成吧。

涉及函数：AVERAGE、COUNTIF 、IF、ISERROR、MAX、MIN、RANK、SUM

1、启动Excel，同时选中A1至K1单元格，按“格式”工具条上的“合并及居中”按钮，将其合并成一个单元格，然后输入统计表的标题（参见图1）。

2、根据统计表的格式，将有关列标题及相关内容输入到相应的单元格中（参见图1）。

3、选中J3单元格，输入公式：=SUM(C3:I3)，用于计算第一位学生的总分。

4、选中K3单元格，输入公式：=RANK(J3,$J$3:$J$62)，计算出第一位学生总分成绩的名次（此处，假定共有60位学生）。 注意：如果将上述公式修改为：=COUNTIF($J$3:$J$62,\">=\"&J3)，

10

同样可以计算出第一位学生的名次。

5、同时选中J3和K3单元格，将鼠标移至K3单元格右下角的成“细十字”状时（通常称这种状态为“填充柄”状态），按住左键向下拖拉至K62单元格，完成其他学生的总分及名次的统计处理工作。 6、分别选中C63、C单元格，输入公式：=MAX(C3:C62)和=MIN(C3:C62)，用于统计“语文”学科的最高分和最低分。

7、选中

C65

单元格，输入公式：

=IF(ISERROR(AVERAGE(C3:C62)),\"\VERAGE(C3:C62))，用于统计“语文”学科的平均分。

注意：直接用“=AVERAGE(C3:C)”公式也可以计算平均分，但是，如果成绩表中没有输入成绩时，这一公式将显示出一个错误的值，因此，我们用“ISERROR”函数来排除这一错误。

8、选中C66单元格，输入公式：=SUM(C3:C62)，用于统计“语文”学科的总分。

9、同时选中C63至C66单元格，用“填充柄”将上述公式复制到D63至J66单元格中，完成其它学科及总分的最高分、最低分、平均分和总分的统计工作。

至此，一个基本的成绩统计表制作完成（参见xls文件下载），下面我们来进一步处理一下。

10、选中C3单元格，执行“格式、条件格式”命令，打开“条件格式”对话框（如图2），按最左边一个方框右侧的下拉按钮，在随后出现的下拉列表中，选“公式”选项，并在右侧的方框中输入公

11

式：=C3>=AVERAGE(C3:C62)，然后按“格式”按钮，打开“单元格格式”对话框，将字体颜色设置为“蓝色”。

再按“添加”按钮，仿照上面的操作，添加一个公式为“=C3注意：经过这样的设置后，当学生的“语文”成绩大于或等于平均分时，显示蓝色，反之显示红色。

11、再次选中C3单元格，按“格式”工具条上的“格式刷”按钮，然后在D3至J3单元格区域上拖拉一遍，将上述条件格式复制到相应的区域中，完成其他学科及总分的条件格式设置工作。

12、再一次选中C3单元格，打开“条件格式”对话框，将其中的两项公式修改为：=C3>=AVERAGE($C$3:$C$62)和=C312

依照上面的操作，将D3至J3单元格的条件格式中的公式修改为绝对引用（添加“$”符号）。

注意：这样做的目的，是为了将其条件格式，通过“格式刷”复制到下面的各单元格中。

13、同时选中C3至J3单元格，按“格式”工具条上的“格式刷”按钮，然后在C4至J62单元格区域上拖拉一遍，将上述条件格式复制到相应的区域中，完成其他学生各科成绩及总分的条件格式设置工作。

把学生的成绩填入到表格中试试看，效果不错吧。

如果你经常要统计学生的成绩，我们将其保存为模板，方便随时调用。

14、将工作表中的学生成绩等内容删除，执行“文件、保存（另存为）”命令，打开“另存为”对话框（如图4），按“保存类型”右侧的下拉按钮，在随后出现的下拉列表中，选“模板（*.xlt）”选项，然后给定一个名称（如“成绩统计”），按下保存按钮。

13

15、以后需要统计成绩时，启动Excel，执行“文件、新建”命令，展开“新建工作簿”任务窗格（如图5），点击其中的“本机上的模板”选项，打开“模板”对话框，双击“成绩统计”模板，即可新建一份新的成绩统计表。

16、将学生的成绩填入相应的单元格中，取名保存即可快速完成成绩统计处理工作。

14

用Excel制作自动评分计算表

一、评委评分表的制作

1、启动Excel2003，新建一空白工作簿。

2、在Sheet1工作表中，仿照如图1所示的样式，制作一份空白表格。

3、执行“文件→保存”命令（或按“Ctrl+S”组合键），打开“另存为”对话框（如图2）。

15

4、单击工具条上“工具”按钮，在随后出现的下拉列表（参见图2）中，选择“常规选项”，打开“保存选项”对话框（如图3），设置好“打开权限密码”后，确定返回。

注意：①密码需要重新确认输入一次。②此处只需要设置“打开权限密码”，如果设置了“修改权限密码”，则评委在保存评分时，必须提供密码，反而造成不须要的麻烦。 5、然后取名（如1.xls）保存。

6、再执行“文件→另存为”命令，再次打开“另存为”对话框，然后仿照上面的操作重新设置一个密码后，另取一个名称（如2.xls）

16

保存一下。

7、重复第6步的操作，按照评委数目，制作好多份工作表（此处为9份）。 二、汇总表的制作

1、新建一工作簿，仿照如图4所示的样式，制作一张空白表格。

2、分别选中B3至J3单元格，依次输入公式：=[1.xls]sheet1!B3、=[2.xls]sheet1!B3„„=[3.xls]sheet1!B3，用于调用各评委给第一位选手的评分。

注意：请将评委评分表和汇总表保存在同一文件夹内。

3、选中

K3

单元格，输入公式：

=(SUM(B3:J3)-MAX(B3:J3)-MIN(B3:J3))/7，用于计算选手的最后平均得分。

4、选中L3单元格，输入公式：=RANK(K3,$K$3:$K$22)，用于确定选手的名次。

5、同时选中B3至L3单元格区域，用“填充柄”将上将上述公式复制到下面的单元格区域，完成其它选手的成绩统计和名次的排定。

6、取名（如hz.xls）保存该工作簿。

17

注意：在保存汇总表的时候，最好设置“打开权限密码”和“修改权限密码”。 三、电子评分表的使用

1、将上述工作簿文件放在局域网上某台电脑的一个共享文件夹中，供各们评委调用。

注意：当我们移动整个工作簿所在的文件夹时，系统会自动调整公式相应的路径，不影响表格的正常使用。

2、比赛开始前，将工作簿名称和对应的打开权限密码分别告知不同的评委，然后通过局域网，让每位评委打开各自相应的工作簿（如1.xls、2.xls„„）文档。

注意：评委在打开文档中，系统会弹出一个如图5所示的对话框，输入“打开权限密码”，确定即可。

2、某位选手比赛完成后，评位将其成绩输入到相应的单元格中，并要求评委执行一下保存操作。

注意：每次要求评委评分成执行一次保存操作，其目的是为了防止出现意外情况而造成数据丢失。

3、整个比赛结束后，主持人只要打开“hz.xls”工作簿，即可公布比赛结果了（如图6）。

18

注意：主持人在打开“hz.xls”工作簿时，系统会弹出如图7所示的对话框，请单击其中的“更新”按钮。

用EXCEL制作一份万年历

1、启动EXCEL2003，新建一个工作表，取名保存（如万年历.xls），并在相应的单元格中，输入如图1所示的文本。

19

2、同时选中B1、C1、D1单元格，按“格式”工具栏上的“合并及居中”按钮，将其合并成一个单元格，并输入公式：=TODAY（）。 选中B1（合并后的）单元格，执行“格式→单元格”命令，打开“单元格格式”对话框（如图2），在“数字”标签中的“分类”下面选中“日期”选项，再在右侧“类型”下面选中“二○○一年三月十四日”选项，“确定”退出，将日期设置成中文形式。

注意：TODAY（）函数用于提取当前系统日期，请将系统日期一定要调整准确哟。

3、选中F1单元格，输入公式：=IF(WEEKDAY(B1,2)=7,\"日\；选中H1单元格，输入公式：=NOW（）。 选中F1单元格，打开“单元格格式”对话框，在“数字”标签中的“分类”下面选中“特殊”选项，再在右侧“类型”下面选中“中文小写数字”选项，“确定”退出，将“星期数”设置成中文小写形

20

式；选中H1单元格，打开“单元格格式”对话框，在“数字”标签中的“分类”下面选中“时间”选项，再在右侧“类型”下面选中一款时间格式，“确定”退出。

注意：①上述前面一个公式的含义是：如果（IF）当前日期（B1）是星期“7”（WEEKDAY(B1,2)=7），则在F1单元格中显示“日”，否则，直接显示出星期的数值（WEEKDAY(B1,2)）。

②上述第二个函数（NOW（））用于提取当前系统日期和时间，也请将系统日期和时间调整准确。

4、在I1、I2单元格分别输入1900、1901，然后同时选中I1、I2单元格，用“填充柄”向下拖拉至I151单元格，输入1900—2050年份序列。

同样的方法，在J1至J12单元格中输入1—12月份序列。 5、选中D13单元格，执行“数据→有效性”命令，打开“数据有效性”对话框（如图3），按“允许”右侧的下拉按钮，选中“序列”选项，在“来源”下面的方框输入：=$I$1:$I$151，“确定”退出。

21

同样的操作，将F15单元格数据有效性设置为“=$J$1:$J$12”序列。

注意：经过这样的设置以后，当我们选中D15（或F15）单元格时，在单元格右侧出现一个下拉按钮，按此下拉按钮，即可选择年份（或月份）数值，快速输入需要查询的年、月值。

6、选中A2单元格（不一定非得是A2哟），输入公式：=IF(F13=2,IF(OR(D13/400=INT(D13/400),AND(D13/4=INT(D13/4),D13/100<>INT(D13/100))),29,28),IF(OR(F13=4,F13=6,F13=9,F13=11),30,31))，用于获取查询“月份”所对应的天数（28、29、30、31）。 注意：上述函数的含义是：如果查询“月份”为“2月”（F13=2）时，并且“年份”数能被400整除[D13/400=INT(D13/400)]，或者（OR）“年份”能被

4

整除，但不能被

100

整除

[AND(D13/4=INT(D13/4),D13/100<>INT(D13/100))]，则该月为29天（也就是我们通常所说的“闰年”），否则为28天。如果“月份”不

22

是2月，但是“4、6、9、11”月，则该月为30天。其他月份天数为31天。

7、选中

B2

单元格，输入公式：

=IF(WEEKDAY(DATE($D$13,$F$13,1),2)=B3,1,0)。再次选中B2单元格，用“填充柄”将上述公式复制到C2—H2单元格中。

注意：①上述B2公式的含义是：如果“查询年月”的第1天是星期“7”（WEEKDAY(DATE)($D$13,$F$13,1),2)=B3）时，在该单元格显示“1”，反之显示“0”），为“查询年月”获取一个对照值，为下面制作月历做准备。

②上述C2—H2单元条中公式的含义与B2相似。

③在用拖拉法复制公式时，公式“绝对引用”的单元格（加了“$”号的，如“$D$13”等）不会发生改变，而“相对引用”的单元格（没有加“$”号的，如“B3”等），则会智能化地发生变化，例如在E2单元格中，“B3”变成了“E3”，整个公式成为：=IF(WEEKDAY(DATE($D$13,$F$13,1),2)=E3,1,0)。

8、选中B6单元格，输入公式：=IF(B2=1,1,0)。选中B7单元格，输入公式：=H6+1。用“填充柄”将B7单元格中的公式复制到B8、B9单元格中。

分别选中B10、B11单元格，输入公式：=IF(H9>=A2,0,H9+1)和=IF(H10>=A2,0,IF(H10>0,H10+1,0))。

选中C6单元格，输入公式：=IF(B6>0,B6+1,IF(C2=1,1,0))。用“填充柄”将C6单元格中的公式复制到D6—H6单元格中。

23

选中C7单元格，输入公式：=B7+1。用“填充柄”将C7单元格中的公式复制到C8、C9单元格中。同时选中C7—C9单元格，用“填充柄”将其中的公式复制到D7—H9单元格中。

选

中

C10

单

元

格

，

输

入

公

式

：

=IF(B11>=$A$2,0,IF(B11>0,B11+1,IF(C6=1,1,0)))。用“填充柄”将C10单元格中的公式复制到D10—H10单元格和C11单元格中。 至此，整个万年历（其实没有万年，只有从1900—2050的151年）制作完成。

下面，我们一起来将其装饰一下。

9、选中相应的单元格，利用工具栏上的相应按钮，设置好字体、字号、字符颜色等。

选中相应的单元格，打开“单元格格式”对话框，在“对齐”标签下，设置好单元格中文本的对齐方式（通常情况下，垂直对齐可以一次性设置为“居中”，水平“对齐”根据具体情况设置）。 同时选中I列和J列，右击鼠标，选“隐藏”选项，将相应的列隐藏起来，使得界面更加友好。用同样的方法，将第2和第3行也隐藏起来。

10、选中B5—H11单元格区域，打开“单元格格式”对话框，进入“边框”标签，选择好“颜色、样式”，并“预置”好边框范围，然后“确定”退出，为月历加上边框。

11、执行“工具→选项”命令，打开“选项”对话框（如图4），在“视图”标签下（通常是默认标签），清除“零值”和“网格线”

24

复选框中的“∨”号，“确定”退出，让“零值”和“网格线”不显示出来。

12、将B14—H14和B15—H15单元格分别合并成一个单元格，并在

B14

和

B15

单元格中输入公式：

=IF(AND(MONTH(D1)=1,DAY(D1)=1),\"新的新气象！加油呀！\向女同胞们致敬！\

劳

动

最

光

荣

\青年是祖国的栋梁\原天下所有的儿童永远快乐\和=IF(AND(MONTH(D1)=7,DAY(D1)=1),\"党的恩情永不忘\提高警惕，保卫祖国！\老师，您辛苦了！\祝我们伟大的祖国繁荣富

25

强\。

设置好B14和B15单元格的字体、字号、字符颜色。

注意：上述公式的含义是：如果当前日期逢到相关的节日（如“元旦”等），则在B14或B15单元格显示出相应的祝福语言（如“新的新气象！加油呀！”，参见图5）。

由于IF函数只能嵌套7层，而节日数量超过7个（我们这里给出了9个），因此，我们用两个单元格来显示。 13、执行“格式→工作表→背景”命令，打开“工作表背景”对话框（如图6），选择一张合适的图片后，按“插入”按钮，将其衬于工作表文字下面。

26

14、在按住“Ctrl”键的同时，单击D13和F13单元格，同时选中两个单元格，开“单元格格式”对话框，切换“保护”标签（如图7），清除“锁定”前面复选框中的“∨”号，“确定”退出。

15、执行“工具→保护→保护工作表”命令，打开“保护工作表”

27

对话框（如图8），两次输入密码后，确定退出。

注意：经过这样的设置后，整个工作表中除了D13和F13单元格中的内容可以改变外，其它单元格中的内容均不能改变，保证了万年历的使用可靠性。

Excel制作奖金计算表

某公司规定：一个月奖金基数为300元，病假1天扣15元，事假一天扣30元，旷工一天扣60元，扣完为止。使用这个奖金计算表时，只要将员工的出勤情况记录在表中，该员工的奖金将自动计算出来，兼有考勤和计算奖金两种功能。自动统计表做好以后还可以保存成模板，以便以后使用。

本文所涉及到的Excel函数有： 1、COUNTIF 2、MONTH

28

3、TODAY()

注意：图中符号的含义是：B表示病假，S表示事假，G表示旷工，Q表示出勤，J表示法定休息日。

1、启动Excel2003，按照图1所示的样式，制作一张二维表格，并填入相关文本。

2、将A1至BM1单元格合并成一个单元格后，输入表格标题（如“晓风公司考勤及奖金表”），并设置好字体、字号等。

3、将C2和D2单元格合并后，输入公式：=MONTH(TODAY())&\"月\"，用于显示出当前的月份。

注意：在上述单元格中直接输入月份数值也是可行的。

4、选中

BM4

单元格，输入公式：

=IF(300-COUNTIF(C4:BL4,\"B\")*7.5-COUNTIF(C4:BL4,\"S\")*15-COUNTIF(C4:BL4,\"G\")*30>0,300-COUNTIF(C4:BL4,\"B\")*7.5-COUNTIF(C4:BL4,\"S\")*15-COUNTIF(C4:BL4,\"G\")*30,0)，确认，计算出第一位员工的当月奖金额。

注意：上述公式的含义是：用“COUNTIF(C4:BL4,\"B\")、COUNTIF(C4:BL4,\"S\")、COUNTIF(C4:BL4,\"G\")”分别用于统计每位

29

员工的病假、事假、旷工的次数，再分别乘以“7.5、15、30”（每“半天”为一次）计算出所要扣去的奖励额，再用基数“300”减去上述扣去的金额。如果（IF）差大于“0”，则显示差（即员工的奖金额），否则显示“0”（扣完为止）。

5、再次选中BM4单元格，用“填充柄”将上述公式复制到下面的单元格中，用于计算其它员工的当月奖励额。

注意：具体复制的数量，请根据员工的实际数目确定。 6、清空表格中所填写的数据，执行“文件→保存”命令，打开“另存为”对话框（如图2），按“保存类型”右边的下拉按钮，选中“模板（*.xlt）”选项，在文件名方框中输入一名称（如“奖金表.xlt”），单击“保存”按钮，将制作好的表格文档保存为模板。

7、以后，每个月第一天，启动Excel，执行“文件→新建”命令，展开“新建工作簿”任务窗格（如图3）。

30

点击其中的“本机上的模板”选项，打开“模板”对话框（如图4），选中“奖金表”模板文件，单击“确定”按钮即可新建一个空的“奖金表1”工作簿文档。

选中C2单元格（即保存月份的单元格），将鼠标移到编辑栏中，然后按一下F9功能键，将公式（=MONTH(TODAY())&\"月\"）转换为具体的值（如“12月”），最后将文档保存一下。

8、以后就可以用这个计算表逐日对员工进行考勤，到了月底就可以自动计算出奖金数据了。

31

Excel中自定义函数实例剖析

稍有Excel使用经验的朋友，都知道Excel内置函数的快捷与方便，它大大增强了Excel数据计算与分析的能力。不过内置的函数并不一定总是能满足我们的需求，这时，就可以通过定义自己的函数来解决问题。文章末尾提供.xls文件供大家下载参考。 一、认识VBA

在介绍自定义函数的具体使用之前，不得不先介绍一下VBA，原因很简单，自定义函数就是用它创建的。VBA的全称是Visual Basic for Application，它是微软最好的通用应用程序脚本编程语言，它的特点是容易上手，而且功能非常强大。

在微软所有的Office组件中，如Word、Access、Powerpoint等等都包含VBA，如果你能在一种Office组件中熟练使用VBA，那么在其它组件中使用VBA的原理是相通的。

Excel中VBA主要有两个用途，一是使电子表格的任务自动化；二是可以用它创建用于工作表公式的自定义函数。

由此可见，使用Excel自定义函数的一个前提条件是对VBA基础知识有所了解，如果读者朋友有使用Visual Basic编程语言的经验，那么使用VBA时会感觉有很多相似之处。如果读者朋友完全是一个新手，也不必太担心，因为实际的操作和运用是很简单的。 二、什么时候使用自定义函数？

有些初学Excel的朋友可能有这样疑问：Excel已经内置了这么多函数，我还有必要创建自己的函数吗？

32

回答是肯定的。原因有两个，它们也正好可以解释什么时候使用Excel自定义函数的问题。

第一，自定义函数可以简化我们的工作。

有些工作，我们的确可以在公式中组合使用Excel内置的函数来完成任务，但是这样做的一个明显缺点是，我们的公式可能太冗长、繁琐，可读性很差，不易于管理，除了自己之外别人可能很难理解。这时，我们可以通过使用自定义函数来简化自己的工作。 第二，自定义函数可以满足我们个性化的需要，可以使我们的公式具有更强大和灵活的功能。

实际工作的要求千变万化，仅使用Excel内置函数常常不能地解决问题，这时，我们就可以使用自定义函数来满足实际工作中的个性化需求。

上面的讲述比较抽象，我们还是把重点放在实际例子的剖析上，请大家在实际例子中进一步体会，进而学会在Excel中创建和使用自定义函数。

三、自定义函数实例剖析

下面我们通过两个典型实例，学习自定义函数使用的全过程。这里实际上假设读者朋友都有一定的VBA基础。

假如你完全没有VBA基础也不要紧，当学习完实例后，若觉得自定义函数在自己以后的工作中可能用到，那么再去补充相应的VBA基础也不迟。

(一) 计算个人调节税的自定义函数

33

任务

假设个人调节税的收缴标准是：工资小于等于800元的免征调节税，工资800元以上至1500元的超过部分按5％的税率征收，1500元以上至2000元的超过部分按8％的税率征收，高于2000元的超过部分按20％的税率征收。 分析

假设Sheet1工作表的A、B、C、D列中分别存放“姓名”、“总工资”、“调节税”、“税后工资”字段数据，如图1所示。

图 1

平时使用较多的方法是借助嵌套使用IF函数计算，比如在C2单

元

格

输

入

公

式

“=IF(B2<=800,0,IF(B2<=1500,(B2-800)*0.05,IF(B2<=2000,700*0.05+(B2-1500)*0.08,700*0.05+500*0.08+(B2-2000)*0.2)))”，然后通过填充柄复制公式到C列的其余单元格。

既然公式能够解决问题，为什么还要使用自定义函数的方法呢？ 正如前面提到的两个方面的原因：一是公式看起来太繁琐，不便于理解和管理；二是公式的处理能力在面对稍微复杂一些的问题时便失去效用，比如假设调节税的税率标准会根据年龄的不同而改变，那

34

么公式可能就为力了。 使用自定义函数

下面就通过此例介绍使用自定义函数的全过程，即使是初学Excel的朋友，也会感觉其操作实际上是非常简单的。

1. 为了便于测试自定义函数的计算效果，可以先把上面采用公式计算的结果删去。然后选择菜单“工具→宏→Visual Basic编辑器”命令(或按下键盘Alt+F11组合键)，打开Visual Basic窗口，我们将在这里自定义函数。

2. 进入Visual Basic窗口后，选择菜单“插入→模块”命令，于是得到“模块1”，在其中输入如下自定义函数的代码(图2)： Function TAX(salary) Const r1 As Double = 0.05 Const r2 As Double = 0.08 Const r3 As Double = 0.2 Select Case salary Case Is <= 800 TAX = 0 Case Is <= 1500 TAX = (salary - 800) * r1 Case Is <= 2000

TAX = (1500 - 800) * r1 + (salary - 1500) * r2 Case Is > 2000

35

TAX = (1500 - 800) * r1 + (2000 - 1500) * r2 + (salary - 2000) * r3 End Select End Function

图 2

3. 函数自定义完成后，选择菜单“文件→关闭并返回到Microsoft Excel”命令，返回到Excel工作表窗口，在C2单元格中输入公式“=TAX(B2)”回车后就计算出了第一个员工应付的个人调节税，然后用公式填充柄复制公式到其它后面的单元格，这样就利用自定义函数完成了个人调节税的计算(图3)。

图 3

4. 从自定义函数的代码中可以看出，用这种方式，自定义函数的功能非常易于理解，同时如果税率改变，相应地变化r1、r2、r3

36

的值即可。

通常，自定义的函数只能在当前工作薄使用，如果该函数需要在其它工作薄中使用，则选择菜单“文件→另存为”命令，打开“另存为”对话框，选择保存类型为“Mircosoft Excel加载宏”，然后输入一个文件名，如“TAX”单击“确定”后文件就被保存为加载宏(图4)。然后选择菜单“工具→加载宏”命令，打开“加载宏”对话框，勾选“可用加载宏”列表框中的“Tax”复选框即可，单击“确定”按钮后(图5)，就可以在本机上的所有工作薄中使用该自定义函数了。

图 4

37

图 5

如果想要在其它机器上使用该自定义函数，只要把上面的加载宏文件复制到其它电脑上加载宏的默认保存位置即可。

说明：Windows XP系统下加载宏文件的默认保存位置为：C:\\Documents

and

Settings\\zunyue(用户帐户)\\Application

Data\\Microsoft\\AddIns文件夹。 (二) 计算奖金的自定义函数 任务

为了促进销售人员的工作积极性，销售部门经理制定了销售业绩奖金制度，奖金发放的标准奖金率如下：月销售额小于等于2800元的奖金率为4％，月销售额为2800元至7900元的奖金率为7％，月销售额为7900元至15000元的奖金率为10％，月销售额为15000元至30000元的奖金率为13％，月销售额为30000元至50000元的奖

38

金率为16％，月销售额大于50000元的奖金率为19％。同时，为了鼓励员工持续地为公司工作，工龄越长对奖金越有利，具体规定为：参与计算的奖金率等于标准奖金率加上工龄一半的百分数。比如一个工龄为5年的员工，标准奖金率为7％时，参与计算的奖金率则为9.5%=7%+(5/2)%。 分析

首先，我们在Excel2003中制作好如图6的Sheet1工作表，开始分析计算的方法。

图 6

如果不考虑工龄对奖金率的影响，那么可以利用嵌套使用IF函数

，

在

D2

单

元

格

输

入

公

式

“=IF(B2<=2800,B2*4%,IF(B2<=7900,B2*7%,IF(B2<=15000,B2*10%,IF(B2<=30000,B2*13%,IF(B2<=50000,B2*16%,B2*19%)))))”可以进行计算。

但是，该公式的一些弊端很明显：一是公式看起来太繁琐、不容易理解，而且IF函数最多只能嵌套7层，万一奖金率超过7个，那么这个方法就为力了。

另一方面，由于没有考虑工龄，所以该方法不能算是解决问题了，

39

如果我们把工龄融入到上述公式中，这样公式就会显得更加冗长繁琐，以后的管理与调整都很不方便。 使用自定义函数

下面我们看看利用Excel自定义函数进行计算的全过程，有了实例一的基础，相信大家理解起来更容易了。不过这里与实例一有一个明显的差别是，该自定义函数使用了2个参数，请大家注意体会。 1. 在上述Excel工作表中，选择菜单“工具→宏→Visual Basic编辑器”命令，打开Visual Basic窗口，然后选择菜单“插入→模块”命令，插入一个名为“模块1”的模块。

2. 接着在模块编辑窗口中输入自定义函数的代码如下(图 7)： Function REWARD(sales, years) As Double Const r1 As Double = 0.04 Const r2 As Double = 0.07 Const r3 As Double = 0.1 Const r4 As Double = 0.13 Const r5 As Double = 0.16 Const r6 As Double = 0.19 Select Case sales Case Is <= 2800

REWARD = sales * (r1 + years / 200) Case Is <= 7900

REWARD = sales * (r2 + years / 200)

40

Case Is <= 15000

REWARD = sales * (r3 + years / 200) Case Is <= 30000

REWARD = sales * (r4 + years / 200) Case Is <= 50000

REWARD = sales * (r5 + years / 200) Case Is > 50000

REWARD = sales * (r6 + years / 200) End Select End Function

图 7

3. 从代码可以看出，我们自定义了一个名为REWARD的函数，它包含两个参数：销售额sales和工龄years。常量r1至r6分别存放

41

着各个等级的奖金率，这样处理的好处是当奖金率调整时，修改非常方便。同时，函数的层次结构比前面的公式清晰，让人容易理解函数的功能。此外，当奖金率超过7个时，用自定义函数的方法仍然可以轻松处理。

4. 接下来用该自定义函数进行具体的计算。选择菜单“文件→关闭并返回到Microsoft Excel”命令，关闭Visual Basic窗口，返回Excel工作表。选中D2单元格，在其中输入“=reward(B2,C2)”，回车后就算出了第一个员工的奖金，然后利用公式填充柄复制该公式到后面的单元格，即可完成对其它员工奖金的计算(图 8)。

图 8

如果该自定义函数需要在其它工作薄或其它机器上使用，仿照实例一的操作方法进行即可。 四、 总结

我们通过两个典型的实例讲述了Excel中自定义函数使用的全过程，相信大家都已经会到，其操作过程还是相当简单的。 如果你觉得自己的工作可能需要自定义函数，想进一步学好提高使用自定义函数的水平，笔者想给出如下几点建议。

42

第一点、尽力全面熟练地掌握Excel内置的函数。能用内置函数妥善解决的问题，就不必使用自定义函数。实际上，自定义函数的执行效率当然是比Excel内置函数的执行效率慢的。

第二点、认真掌握好VBA的基础知识。这点很容易理解，如果连VBA的基本规则都不甚清楚，那么别说是写出精致的自定义函数，就是写出能解决问题的自定义函数也还大有疑问。

第三点、具体写自定义函数代码之前，应该认真分析自己要处理的实际问题，如果这个问题有实际的数学函数模型，那么最好列出这个函数的解析式。

以上只是笔者的一些浅薄认识，希望能为大家使用好Excel自定义函数带来帮助，也希望大家能够通过使用自定义函数提高自己的工作效率。

43