Excel 2019利用数据有效性制作下拉列表框/菜单

如果某个高校在招生时只面向有限的几个省市,那么在建立学生报表时,这几个省市的出现概率肯定很高,如图5-29所示。

用户除了使用“复制”和“粘贴”命令外,只能手工输入了。接下来使用数据有效性制作下拉列表框,让用户在输入时,从下拉列表框中选择即可,这样既提高了数据的输入速度,还保证了输入数据的准确性。

制作数据有效性下拉列表框的具体操作步骤如下。

STEP01:打开“完善学生籍贯.xlsx”工作簿,选择E2:E22单元格区域,切换至“数据”选项卡,在“数据工具”组中单击“数据验证”下三角按钮,在展开的下拉列表中选择“数据验证”选项,打开“数据验证”对话框,如图5-30所示。

图5-29 目标数据

选择“数据验证”选项

图5-30 选择“数据验证”选项

STEP02:打开的“数据验证”对话框如图5-31所示。单击“允许”选择框右侧的下拉按钮,在展开的下拉列表中选择“序列”选项。

图5-31 选择序列选项

STEP03:在“来源”文本框中输入数据来源为“山东,北京,黑龙江,河北,江苏,安徽”,然后单击“确定”按钮返回工作表,如图5-32所示。输入数据来源时,一定要用英文逗号隔开。

STEP04:选择E2单元格,单击单元格右侧的控件按钮,在展开的下拉列表中选择相应的省份即可,如图5-33所示。

图5-32 输入数据来源

使用下拉列表框

图5-33 使用下拉列表框

Excel 2019复制数据有效性

在对某一个单元格设置了数据有效性后,如果还想对其他的单元格进行设置,这时应该怎么办?很多用户会选择对其他的单元格重复设置数据有效性。其实,只需要复制粘贴一下就实现了!

复制数据有效性设置的具体操作步骤如下。

打开“有效性特定条件.xlsx”工作簿,这里以复制A1单元格中设置的序列有效性为例进行具体讲解。

STEP01:选择A1单元格,单击鼠标右键,在弹出的隐藏菜单中选择“复制”选项,如图5-25所示。

STEP02:选择粘贴的目标单元格或单元格区域,例如这里选择C2单元格,单击鼠标右键,在弹出的隐藏菜单中选择“选择性粘贴”选项,在打开的级联列表中选择“选择性粘贴命令”,打开“选择性粘贴”对话框,如图5-26所示。

图5-25 选择复制

选择“选择性粘贴”选项

图5-26 选择“选择性粘贴”选项

STEP03:在打开的对话框中单击“粘贴”列表框下的“验证”单选按钮,然后单击“确定”按钮返回工作表即可,如图5-27所示。最终复制数据有效性的效果如图5-28所示。

图5-27 单击“验证”单选按钮

复制序列有效性

图5-28 复制序列有效性

Excel 2019设置唯一数据有效性

Excel工作表给用户的工作带来了极大的方便,但在输入数据的过程中难免会出错,例如输入一些重复的身份证号码或超出范围的有效数据。此类错误可以用设置数据有效性的方法来确定输入数据的唯一性。具体操作步骤如下。

STEP01:在B列列标处单击选中B列,切换至“数据”选项卡,在“数据工具”组中单击“数据验证”下三角按钮,在展开的下拉列表中选择“数据验证”选项,打开“数据验证”对话框,如图5-20所示。

STEP02:在“验证条件”窗格中单击“允许”选择框右侧的下拉按钮,在展开的下拉列表中选择“自定义”选项,如图5-21所示。

STEP03:在“公式”文本框中输入条件公式“=COUNTIF($B:$B,B1)=1”,$B:$B为所选定的列,B1为数据起始单元格,如图5-22所示。

图5-20 选择“数据验证”选项

选择“自定义”选项

图5-21 选择“自定义”选项

STEP04:切换至“出错警告”选项卡,勾选“输入无效数据时显示出错警告”复选框,在“样式”列表框中选择“警告”项,并在“标题”文本框中输入文本“输入错误”,在“错误信息”文本框中输入文本“重复的数据输入,请确认后再输入!”,最后单击“确定”按钮完成设置,如图5-23所示。

图5-22 输入条件公式

设置出错警告

图5-23 设置出错警告

STEP05:设置完成后,用户在B列中输入数据时,如果输入了重复的数据,则会弹出如图5-24所示的“输入错误”警告框。

“输入错误”警告框

图5-24 “输入错误”警告框

Excel 2019设置选定单元格数据有效性信息

用户如果事先设置了在选定单元格时出现的数据有效性信息,会避免很多错误数据的输入,同时还会提醒用户输入什么样的数据。设置选定单元格时出现的数据有效性信息的具体操作步骤如下。

STEP01:选择A6:D6单元格区域,切换至“数据”选项卡,在“数据工具”组中单击“数据验证”下三角按钮,在展开的下拉列表中选择“数据验证”选项,打开“数据验证”对话框,如图5-17所示。

选择“数据验证”选项

图5-17 选择“数据验证”选项

STEP02:切换至“输入信息”选项卡,勾选“选定单元格时显示输入信息”复选框,在“标题”文本框中输入“输入正确的信息!”,在“输入信息”文本框中输入“输入11位的手机号码,例如15000001234”,然后单击“确定”按钮完成设置,如图5-18所示。

STEP03:返回工作表后,单击选中A6:D6单元格区域中的任意单元格,都会显示输入信息框,如图5-19所示。

图5-18 设置“输入信息”

选定单元格时显示输入信息

图5-19 选定单元格时显示输入信息

使用“数据有效性”的2个好处

想要提高工作效率和生产率有一点很重要,那就是建立零失误的结构。如果出现错误,就需要花费不必要的时间与精力去恢复数据,这样就会使工作的生产率下降。所以说,努力降低失误的发生概率,与提高生产率有直接联系。

Excel 中有一个重要功能——数据有效性。使用这个功能,有2个好处:

  • 更高效地输入数据
  • 避免输入错误

例如,需要从几个选项中多次输入相同数据时,在需要输入的单元格范围内将有效性条件设定为允许“序列”输入,这样就能从下拉菜单中选择想输入的数据。

使用“序列”输入有一个好处,就是能够确保每次都用相同字符串输入相同的数据。例如,要输入相同公司名称的时候,有的地方是“××股份有限公司”,有的是“××(股份公司)”,这些数据虽然都代表同一家公司,但也会出现不同的字符串(这种情况叫作“标示不统一”)。在这种情况下,在统计和处理数据时,Excel 无法将这两种公司名称数据自动识别为同一家,因此会出现各种各样的错误。

另外,限制单元格中的数值,也能防止输入错误。