限制输入长度并显示提示和错误信息

财经数据通常对某些单元格的数据有一定的限制，如金额只能保留两位数、日期或时间在某个范围、字符的个数等。我们在录入数据时通常希望对不符合要求的数据给予提示或显示错误信息，以加强审核，防止错误数据的录入。如果是由别人录入的数据，则有必要给予录入者一些提示或说明。而Excel提供了“数据有效性”这个工具，利用它用户可以有针对性地输入数据。在输入错误数据时，系统会提示错误信息，警示用户给予修改。

如图2-29所示为重庆××汽车公司中层管理干部信息表，该数据表有如下限制：

图2-29　用数据有效性录入人员信息表

①干部编号下面的内容都以GB开头；

②姓名下边都应该是汉字，所以需要中文输入法自动切换；

③性别以选择方式输入“男”“女”；

④身份证号码长度为18位；

⑤入职日期应在公司成立日期（2006年1月1日）之后；

⑥基本工资按公司的薪酬制度，中层干部基本薪酬应在2500至8000之间；

⑦入职誓言不得重复。

完成上述工作表的初始设计过程的具体操作如下。

Step 1：利用数据有效确保必须输入特定内容。本例中的编号必须以GB开头，该有效性条件只要按照图2-30的样式设置即可。

图2-30中的公式“=COUNTIF（A3,"GB*"）=1”中，*为通配符，可以代表任意长度的字符或字符串。如果单元格中的录入内容以“GB”开头，则该公式的结果为真，允许数据录入，否则不允许保留数据。

Step 2：利用有效性设置汉字字段自动切换汉字输入法。在数据表中，既有英文字符和数字字符，也有汉字文本字段，在其中输入时，通常需要在中英文输入状态之间切换。利用Excel的数据有效性可以设置对汉字字段自动切换为中文输入状态的有效性设置。本例如图2-31所示，将设置输入法模式为“打开”状态。

Step 3：参照上节下拉列表输入方法录入性别。将允许设置为序列，在序列里输入“男,女”或者在非数据区的相邻的两个单元格分别输入“男”“女”，然后在序列下边的来源里选中这两个单元格即设置完成（参照下拉列表输入方法）。

图2-30　数据有效性录入特定内容设置

图2-31　自动切换中文输入状态(www.xing528.com)

Step 4：利用有效性限制文本长度。限制录入文本的长度就是限定单元格中录入数字或文本长度不满足相应条件时，能够阻止其录入。图2-29中的身份证号码要求是18位，只须按图2-32中的样式设置即可。

Step 5：利用数据有效性限制录入数据的格式。限制数据录入的格式就是限定单元格录入数据为特定格式，如日期、整数或文本等。如此限定有助于提高录入正确率。本例中的入职日期为日期型数值，且在公司成立日期2006年1月1日之后，按图2-33中的样式设置即可。

图2-32　身份证号码长度限制设置

图2-33　入职日期格式设置

Step 6：利用数据有效性限制录入数值范围。在图2-29中的工资标准，对于提拔时间、正副职岗位虽然有差别，但基本工资有其限定范围。为了防止错误地输入基本工资数据，需要为其设置有效范围。其操作为：选定数据区域→【数据】→【有效性】，然后如图2-34所示，箭头所示方向为操作顺序。

图2-34中的①是为了设立工资数据的取值范围；②是为了在输入数据时有提示信息；③是在输入不符合要求的情况下出错警告，在样式下拉框里有“停止”“警告”和“信息”三个选项，本例中选择“停止”（审核条件最为严格），在标题文本框里输入“错误信息”，在“错误信息”文本框中输入“工资数据不符合公司规定范围，请仔细核对后输入！”；④是在“输入法模式”选项卡下，在“模式”下拉列中选择“关闭（英文模式）”,意在输入数字时自动关闭中文输入方法，切换到英文输入状态，以免在录入小数点时输入汉字的句号。

图2-34　对录入数据范围进行限制的设置

经过以上操作，指定单元格区域的数据有效性设置完毕。单击该区域任意单元格，就会在其旁边显示一条输入提示信息（图2-35），根据提示信息录入正确数据，如超过规定范围则会弹错误警告信息（图2-36）。当我们不再需要有效性设置时，则选定相关单元格→【数据】→【有效性】→【全部清除】。

图2-35　显示提示信息

图2-36　显示错误信息

Step 7：利用数据有效性防止重复数据的录入。在录入财经信息中，通常有些字段不能重复录入。本例中的入职誓言也要求每个人都不能与其他任何人重复，其有效性方式设置如图2-37所示。

图2-37中的有效性公式“=Countif（$G$3:$G$888,G3）=1”中，Countif函数可以统计输入的单元格在需要输入的整个区域中的个数，如果等于1就表示符合每个誓词唯一性的条件，允许录入。反之，则表示数据重复，不允许录入。注意单元格地址的格式差异，$G$3:$G$888是绝对地址格式，表示整个数据不改变；而G3则是相对地址，在执行后面的单元格时分别会变成G4、G5、G6……，这样就达到了对每个单元格数据进行相同性检查的目的。

图2-37　限制重复数据录入的有效性设置