化繁为简——用Excel简化数据输入

办公族

陈桂鑫 · 2005年8月8日

在使用Excel时经常需要输入大量数据，当列数太多时直接输入很容易看花眼。可别以为使用记录单就可以简化输入，在记录单中不能使用下拉列表，也无法及时显示条件格式，其实并不方便。此时不妨通过编辑少量宏代码自制一个输入界面（以为“合同记录”工作表设置输入界面为例，图1）。

构架：搭建录入框架

打开保存“合同记录”工作表的文件，新建一个工作表，命名为“合同输入”，按“合同记录”中的标题，在其中设计表格（图1）。选中不允许填写的区域设置填充色，以便查看。选中允许输入的区域C2:C7、E2:E7，接着选择“设置单元格格式”，在“保护”选项卡中取消对“锁定”的选择。

转到“合同记录”工作表，在首行标题前插入一行，在各单元格内分别输入公式让它等于“合同输入”工作表中的相应单元格，如合同编号的A1=合同输入!C2、建设单位的B1=合同输入!C3、工程名称的C1=合同输入!C4（图2），然后右键点击第一行行标选择“隐藏”。

选中B列在名称框中输入“建设单位”回车确认把B列命名为“建设单位”。同样把C列命名为“工程名称”、D列命名为“项目经理”。

提示：条件格式和数据有效性都不直接引用其他工作表中的单元格，因此必须先给区域命名后再通过名称来引用。

全部设置完成后，再返回到“合同输入”工作表。

工程类别只须输入框架结构、砖混结构或安装工程，应该设置下拉列表输入。

选中E3单元格，点击“数据→有效性”，打开“编辑有效性”窗口，在“设置”选项卡下的“允许”列表中选择“序列”，在“来源”中输入“框架结构,砖混结构,安装工程”（不含引号且分隔的逗号为半角格式）即可（图3）。

建设单位和项目经理名经常重复，一般要先在输入信息中寻找，找不到才直接输入，这可用有效性设置实现。

选中C3，打开“编辑有效性”窗口，在“设置”标签的“允许”中选择“序列”，在“来源”中输入=INDIRECT("建设单位")。切换到“出错警告”选项卡，取消“输入无效数据时显示出错警告”复选项前的√。

工程名称一般是不允许重复的，一旦重复就要及时提示，这要用条件格式实现。

选中C4，单击菜单中的“格式→条件格式”，在弹出窗口的“条件1”列表中选择“公式”，在后面输入公式=COUNTIF(工程名称,$C$4)>=1（图4），单击“格式”按钮，设置字体颜色为红色。

画一个按钮，选中并输入“保存”二字。右键点击该按钮选择“指定宏”，在弹出的“指定宏”窗口输入宏名“保存”，单击“新建”按钮，编辑如下代码：

Sub 保存()

Sheets("合同记录").Rows("1:1").Copy

'复制合同记录工作表第1行

Sheets("合同记录").Rows("3:3").Insert Shift:=xlDown

'在第三行前插入一行

Sheets("合同记录").Range("A3").PasteSpecial Paste:=xlPasteValues

'以数值方式在记录工作表A3粘贴

Range("C2:C7,E2:E7").ClearContents

Range("C2").Select

'清除C2:C7,E2:E7中的数据，选中C2以便输入。

End Sub

在宏列表内选中“保存”宏，单击“选项”按钮，在弹出窗口中将它设置为“Ctrl+M”组合键。

进入“保护工作表”，在弹出对话框的列表中，只勾选“允许选定未锁定的单元格”选项，其他选项全部清除（图5）。设置保护工作表后，再回车或按方向键，都只能在原先取消锁定的C2:C7,E2:E7区域内移动，填完C7时回车或按↓键就会自动跳到E2，而不是C8。在E7时按↓键也会直接转到C2。

现在，在“合同输入”工作表中输入一项数据，回车后会自动跳到下一项，对于设置了列表的单元格可单击后面的下拉箭头或按“Alt+↓”组合键打开下拉列表进行选择。填完全部数据后，单击保存按钮或按“Ctrl+M”组合键即可保存这条记录，并自动清空定位到C2以便填写下一条。

在此宏代码中使用的是插入行的方法添加记录，所以添加的新记录总是排在最前面，当然这也正是我们最需要的。