资讯

Excel答疑小课堂:关于事件宏、条件格式、CHAR的问题

本文将介绍如何利用事件宏禁用拖放功能;如何使用条件格式隔行填充颜色;以及如何使用CHAR在公式中插入特殊字符。

Question 1:

我有一张需要发给用户输入数据的电子表格。为了确保用户只能在指定区域输入,已使用了保护工作表功能,但用户仍然可以在输入区域进行拖放操作,从而导致“#REF!”错误。请问有什么办法可以禁用拖放功能?

在Excel中可以通过更改设置来禁用拖放功能,但缺点是会影响所有Excel文件。利用事件宏可以禁用某个文件的拖放功能。你可使用事件宏来更改设置,事件发生后会触发宏。Excel中有多类事件可以触发宏。

下面两段宏命令将会在激活文件时禁用拖放功能,转为非活动状态时启用该功能。

Private SubWorkbook_Activate()

Application.CellDragAndDrop = False

End Sub

Private SubWorkbook_Deactivate()

Application.CellDragAndDrop = True

End Sub

创建

事件宏的创建方式与标准宏不同。

按Alt + F11将打开Visual Basic for Applications (VBA) 窗口。在屏幕左侧可以看到列出的工作表,如图1所示。

Excel答疑小课堂:关于事件宏、条件格式、CHAR的问题

图1

1、按Alt + F11将打开Visual Basic for Applications (VBA) 窗口。在屏幕左侧可以看到列出的工作表,如图1所示。

2、双击Microsoft Excel Objects列表下方的ThisWorkbook。

3、将上面两段宏命令复制并粘贴到VBA窗口右侧的代码窗口中,这样就可以执行宏了。

4、当文件激活或转为非活动状态时,宏命令会自动执行。切换到另一个文件时会使原文件转为非活动状态。打开一个空白Excel文件,然后切换到原文件,这时会发现无法使用拖放功能。

上述宏命令不会禁用剪切和粘贴功能。Excel中的剪切和粘贴操作也可能导致“#REF!”错误,不过禁用剪切和粘贴功能相比禁用拖放功能更加复杂。

文件类型

2005年时,Excel只有一种文件类型,即.xls。随着Excel 2007的发布,出现了三种新的文件类型。现在的标准Excel文件类型是.xlsx,但无法保存宏,其他两种文件类型可以保存宏。

若在标准Excel文件中创建了上述代码,保存文件时会出现警告信息,如图2所示。

Excel答疑小课堂:关于事件宏、条件格式、CHAR的问题

图2

警告信息指出,标准Excel文件类型不支持宏功能。确保点击“否”(No),若点击“是”(Yes)则会丢失宏。点击“否”后会出现“另存为”(Save As)对话框。

从“保存类型”(Save as type)下拉菜单中选择“Excel启用宏的工作簿”(Excel Macro-Enabled Workbook;.xlsm),或者“Excel二进制工作簿”(Excel Binary Workbook;.xlsb),如图3所示。

Excel答疑小课堂:关于事件宏、条件格式、CHAR的问题

图3

请注意:必须确保启用宏之后才能使用上述方法。

Question 2

为了便于查阅,有些电子表格会隔行填充颜色,请问有没有简单的方法,这样就不需要一行行手动调整格式?

在Excel 2007及之后的版本中,主功能区的“套用表格格式”(Format as Table)功能添加了隔行填充颜色格式,作为Excel表格的默认设置。如果想要自行设置某个正常区域的隔行填充颜色,可以使用条件格式,具体步骤如下。

选中想要设置格式的区域。

Excel答疑小课堂:关于事件宏、条件格式、CHAR的问题

图4

点击主功能区的“条件格式”(nditional Formatting)图标下拉菜单并选择“新建规则”(New Rule),如图4所示。

Excel答疑小课堂:关于事件宏、条件格式、CHAR的问题

图5

选择顶部区域的最后一项“使用公式...”(Use a formula to …)。在下方的公式输入框中输入以下公式,如图5所示。

=MOD(ROW(),2)=1

Excel答疑小课堂:关于事件宏、条件格式、CHAR的问题

图5

点击格式(Format)按钮,点击填充(Fill)选项卡,选择一种颜色,然后点击“确定”(OK)。

对话框各项设置如图5所示。

Excel答疑小课堂:关于事件宏、条件格式、CHAR的问题

图6

点击“确定”(OK)应用格式,结果如图6所示。

公式说明

创建条件格式的公式时,公式必须返回TRUE才能应用格式。上述公式包含两个函数。

ROW函数可返回行号,若括号里面省略引用,则会返回当前行的行号。

MOD函数可返回两数相除的余数。

ROW函数用来确定所选区域各行的行号,MOD函数将该行号除以2。这意味着每隔一行会得到余数1。

通过判断MOD函数返回的结果是否为1,最终只有奇数行会被填充颜色。如果想要给偶数行填充颜色,判断MOD返回的结果是否为0即可。

Question 3

当一个标题占用三个单元格时,我会希望将这三个单元格合并为一个单元格并分行显示,在单元格进入输入状态时使用Alt + Enter可以实现这一效果。请问可以用公式来达到同样的效果吗?

你可使用公式在单元格中插入换行符,方法不止一种,具体取决于所用的Excel版本。

Excel中的CHAR函数可用于在公式中插入特殊字符,CHAR(10) 即可返回换行符。

Excel答疑小课堂:关于事件宏、条件格式、CHAR的问题

图7

将该单元格设置为“自动换行”(Wrap Text)格式后即可分行显示,如图7所示。

上述方法不具备可扩展性,需要在公式中引用每一个单元格。若使用订阅版Excel,则可使用另一种方法。

最新版本的Excel新增了TEXTJOIN函数,它可以基于单元格区域生成文本字符串,具有一定的可扩展性。订阅版可使用以下公式:

=TEXTJOIN(CHAR(10),1,A1:A3)

在TEXTJOIN函数中,必须先指定分隔符。分隔符是用来分隔单元格区域内各文本值的符号。函数中间的参数1表示忽略空白单元格,最后的参数用于指定想要组合的单元格区域。接下来还需要设置自动换行格式,以便正确显示。

Excel答疑小课堂:关于事件宏、条件格式、CHAR的问题

图8

Excel答疑小课堂:关于事件宏、条件格式、CHAR的问题

图9

图9显示了CHAR函数可返回的其他字符及对应的数字代码。

点击这里,阅读微信原文。

点击这里,阅读英文原文。