前言
许多人在使用Excel时,常常花费大量时间做一些重复性的工作。如:针对不同表格进行格式整理;套用数据的排序与筛选;加上求和公式等。而这些功能都可以使用Excel将其转换成一个自动运行的流程,这个功能叫做Macro,中文名叫做巨集或宏。
一、宏
1.1 宏的位置
可以在视图右侧找到宏指令。
如果你经常要使用宏这个功能,可以点击左上角的箭头,选择其他命令。
然后勾选开发工具
然后就可以从这里进行宏的录制和编辑。
1.2 使用示例
假设今天我有一个表格,我需要将通过保送入学的学生标注出来,将地址和邮编分开。
我们点击开发工具中的录制宏,然后对宏进行命名,并设置快捷键。如果使用英文命名宏的名称,必须注意单词之间不能出现空格,需要使用下划线将其连接起来。
宏会录制我们所执行的所有操作。如我们在单元格中输入了什么名字,又将文字设置了什么色彩等。不过它不像一般的录屏软件会录制鼠标的移动轨迹和操作时间,因此我们可以在录制过程中慢慢思考每一个要操作的步骤。
现在我们开始执行操作。
①首先对表格标题进行设置
②要将邮编和地址分开,我们需要新建一列,分别存储邮编和地址。
首先选中C列,对列进行分割。因为邮编长度固定,所以选择固定宽度。
然后将箭头移到邮编数字后。
最后将标题进行更改,单元格宽度进行调整。
③将保送入学的标记出来。
首先设置条件格式。
然后设置筛选关键字。
④最后选中任意一单元格,点击停止录制宏。
注意:保存Excel文件时,默认的工作簿不会启用宏,因此我们得更改保存文件类型。
⑤对录制的宏进行测试。
我们打开保存的Excel文件,有的Excel会出现一个警告,点击启用。(我这里没出现)
然后在开发工具中执行宏。
执行过程中出现警告,我们点击确定。
最终效果如下:
1.3 修改VBA代码
如果我们不想出现这个警告,则可以修改VBA代码。我们点击宏菜单中的编辑。
VBA代码如下:
我们在代码最前面新增一行代码用于关闭警告。
在宏执行后我们将警告功能打开。
事实上,我们即使不懂VBA代码,我们也可以通过一些关键字来修改代码的内容。举例来说,录制过程中我们不小心将通讯地址复制为户籍地址。
我们只需在代码中找到户籍地址。
将其更改为通讯地址,就不用重新录制宏了。
点击左上角的Excel标记,回到Excel文件中。我们在另外一个表格中,按我们设置的快捷键 Ctrl+Shift+Q启动宏,发现没有出现警告提示,同时效果如下:
1.4 以相对位置录制
一般我们在录制宏时,如果我们从目前的单元格跳到隔壁单元格,则宏会将这个步骤记录为:移动到B1。
而我们启用相对位置时,则会记录为:向右移动一格。
二、学生成绩录入系统
我们准备制作一个表单界面,让我们可以输入成绩后,利用宏的功能将成绩自动录入班级的成绩表中。
2.1 外观制作
①插入背景图
效果如下:
②然后将学习logo插入。
接下来我调整单元格宽度,发现logo会随之移动。
为了使logo位置不变,我们进行相关设置。
③然后对单元格的内容、外观进行一些调整。
④ 最后将网格线隐藏,效果如下:
2.2 宏录制
我将宏命名为成绩登录。
选中四个单元格,复制。
然后在录入成绩的表格中,我们将鼠标选中左上角的姓名单元格。
再按Ctrl+↓移动到最后一行
接下来我们需要向下移动一格,为了避免宏将这个步骤记录为移动到A12,所以我们到上方启用使用相对引用。然后再将选取框移动到下一格。
接下来我们右键点击,选中选择性粘贴。
然后设置为值和转置。
最后将总分的公式复制到下一格。
这里还要做一个功能:当前同学成绩存储后,需要将输入界面的信息清空,以便下一位同学输入。
这里我们需要先关闭相对引用功能,然后将输入信息的四个单元格选中,删除信息。
然后就可以停止宏的录制了。
2.3 按钮制作
由于这个表单是给其他人提供,因此执行宏的最佳方式便是通过按钮。制作按钮有两种方式:
①在开发工具中插入表单控件.
然后拖动鼠标画一个按钮,并指定触发的宏。
按钮效果如下:
②上面那个按钮充满了上世纪90年代的风格
我们自己设计一个按钮,插入一个矩形,将棱角、颜色、大小进行相关设置。为了让文字能够在图案的中间,相关设置如下图中右侧所示。
然后右键点击形状,编辑文字,输入成绩送出。
然后将形状与宏连接。
2.4 锁定并测试
最后,我们将工作表锁定,只留下输入信息的四个栏位与成绩送出按钮。我们选中四个单元格,右键选中单元格格式。
将锁定选项取消。
最后打开锁定工作表,设置密码。
最后进行测试,输入姓名与对应成绩,点击成绩送出。
表格更新如下: