用excel中的自定义函数办公

Excel有强大的自带函数,可以帮我们解决很多的问题。但在某些特定的问题上,Excel的自带函数就显得有那么点力不从心了。前几天我遇到的一个任务就是这样子的。虽然可以使用Excel的自带函数完成,但公式写出来却非常麻烦。后来采用了Excel的自定义函数功能,大大简化了公式,使公式变得清清爽爽。如果您也遇到类似问题,不妨也试试这个自定义函数。

图1所示的表格A列是学生的考号信息。其中前两位表示学生入学年份,也就是学生的年级。3、4位是专业代码,其中01代表种植专业;03代表机电专业;04表示微机专业;06表示财会专业;07表示商贸专业;14表示化工专业。5、6两位数字是学生的班级编号,最后三位是学生在班内的编号。我的任务是根据学生的考号信息把该名学生的年级、专业、班级写到C列相应单元格中。如图1中A2单元格对应的C2单元格就应该填写“09级种植02班”。

问题在于A列的单元格有几百个,不可能一一手工录入的。所以必须考虑使用函数或公式。

通常情况下应该在C2单元格输入公式“=IF(MID(A2,3,2)="01",LEFT(A2,2)&"级"&"种植"&MID(A2,5,2)&"班",IF(MID(A2,3,2)="03",LEFT(A2,2)&"级"&"机电"&MID(A2,5,2)&"班",IF(MID(A2,3,2)="04",LEFT(A2,2)&"级"&"微机"&MID(A2,5,2)&"班",IF(MID(A2,3,2)="06",LEFT(A2,2)&"级"&"财会"&MID(A2,5,2)&"班",IF(MID(A2,3,2)="07",LEFT(A2,2)&"级"&"商贸"&MID(A2,5,2)&"班",IF(MID(A2,3,2)="14",LEFT(A2,2)&"级"&"化工"&MID(A2,5,2)&"班"))))))”,然后向下拖动填充句柄至最后一行。

很显然这个公式太麻烦了。要想完整无误地录入公式太困难了。而且一旦出现问题想检查公式,那也几乎是不可能的事情。

用自定义函数就不一样了。

一、显示开发工具选项卡

默认情况下Excel 2007并不显示 “开发工具”选项卡。要想使它出现在功能区,可以点击左上角“Office”按钮,点击打开的对话框右下角的“Excel选项”按钮,在打开的对话框中,点击左侧的“常用”项目,并选中右侧“在功能区显示“开发工具”选项卡”复选项,这样就可以将“开发工具”选项卡添加到功能区中了,如图2所示。

二、自定义函数

点击功能区“开发工具”选项卡的“代码”功能组中的“Visual Basic”按钮,打开VBA代码编辑窗口。点击菜单命令“插入→模块”,得到“模块1”编辑窗口,在其中输入如下自定义函数的代码,如图3所示。

　　Function kaohao(kh)

　　If Mid(kh, 3, 2) = 1 Then

　　kaohao = Left(kh, 2) & "级" & "种植" & Mid(kh, 5, 2) & "班"

　　Else

　　If Mid(kh, 3, 2) = "03" Then

　　kaohao = Left(kh, 2) & "级" & "机电" & Mid(kh, 5, 2) & "班"

　　Else

　　If Mid(kh, 3, 2) = "04" Then

　　kaohao = Left(kh, 2) & "级" & "微机" & Mid(kh, 5, 2) & "班"

　　Else

　　If Mid(kh, 3, 2) = "06" Then

　　kaohao = Left(kh, 2) & "级" & "财会" & Mid(kh, 5, 2) & "班"

　　Else

　　If Mid(kh, 3, 2) = "07" Then

　　kaohao = Left(kh, 2) & "级" & "商贸" & Mid(kh, 5, 2) & "班"

　Else

　　If Mid(kh, 3, 2) = "14" Then

　　kaohao = Left(kh, 2) & "级" & "化工" & Mid(kh, 5, 2) & "班"

　　End If

　　End If

　　End If

　　End If

　　End If

　　End If

　　End Function

　　完成后,选择菜单“文件→关闭并返回到Microsoft Excel”命令,返回到Excel工作表窗口。

　　以上操作,就在当前工作簿中自定义了函数“kaohao(kh)”。其中“kh”为参数,即考号或考号所在的单元格。

　三、使用自定义函数

函数定义好了,使用就非常简单了。将鼠标定位于C2单元格,输入公式“=kaohao(A2)”,然后向下拖动填充句柄复制公式至最后一行。于是所有学生的班级就都得到了,如图4所示。您看,这公式是不是清爽、简单多了?

四、在其它工作簿中调用自定义函数

在默认的情况下自定义函数只能在当前工作薄使用。如果我们需要在其它工作薄中使用该函数,则点击“Office按钮”,在弹出的菜单中点击“另存为→其它格式”命令,打开“另存为”对话框,在“保存类型”下拉列表中选择“Excel加载宏”,然后输入一个文件名,如“考号分析”,如图5所示。单击“保存”后文件就被保存为加载宏。

再点击“Office按钮”,点击右下角的“Excel选项”按钮,打开“Excel选项”对话框,点击左侧的“加载项”项目,然后右侧“管理”下拉列表中选择“Excel加载项”,单击其右侧的“转到”按钮。打开“加载宏”对话框,勾选“可用加载宏”列表框中的“考号分析”复选框,如图6所示,单击“确定”按钮后,就可以在本机上的所有工作薄中使用该自定义函数了。

如果我们想在其它的机器中使用此自定义函数,那么也需要将加载宏文件保存到其它机器中相应的文件夹中。该文件夹默认为“C:\Documents and Settings\用户名\Application Data\Microsoft\AddIns”。如果在图6对话框中看不到“考号分析”复选项,可以点击“浏览”按钮在这个文件夹中手工查找。