设计分录表并进行Excel数据验证

1.Excel数据验证的内容

数据验证在Excel 2010及以前版中也称数据有效性，用于定义可以在单元格中输入或应该在单元格中输入哪些数据，控制输入单元格的数据或数值的类型，防止输入无效数据，或在输入了错误数据时提示修改等。

设置允许输入值的主要内容有：

（1）将数据限制为列表中的预定义项（即“序列”）；

（2）将数字限制为整数、小数；

（3）将日期、时间限制在某一时间范围之外或之内；

（4）限制文本字符数；

（5）根据其他单元格中的公式或值验证数据有效性等。

有的验证项还可进一步限定最大值、最小值、不等于、介于等条件。

2.Excel单元区域名称

（1）单元区域名称的作用。Excel对单元区域的引用，可使用单元坐标，如“B2：G2”“＄B＄3：＄G＄3”等方式进行单元格的相对引用或绝对引用，也可通过插入单元区域名称的方式进行单元区域的引用。

定义名称后，可以直接在公式中键入相应的单元名称进行单元区域的引用。

例：若将B2至B50单元区域定义为“A公司销售额”这一名称，则公式“＝SUM（B2：B50）”与“＝SUM（A公司销售额）”这两个公式等效；将C2至G80单元区域定义为“3年级成绩”，则公式“＝AVERAGE（C2：G80）”与“＝AVERAGE（3年级成绩）”这两个公式也是等效的。

（2）单元名称中的字符可以是字母、数字、句号和下划线；名称不能与单元坐标名称相同；名称可使用多个单词；名称中不能有空格；名称中不区分大小写；名称使用绝对单元格引用；同一单元区域可以定义多重名称。

（3）名称运用于同一工作簿的所有工作表，即在本工作簿的不同工作表之间，可直接键入单元名称而实现不同工作表之间的引用。所以，在同一工作簿中不要定义重复的单元名称，否则后定义的名称将替代之前的名称。

（4）名称的管理。定义名称后，若选定已定义名称的单元区域，则名称框中将显示其名称；部分选定或超范围选定不会显示名称。也可以在“名称管理器”或“定义名称”对话框中，查看本工作簿所有已定义的名称列表，以及具体的名称所涉及的单元区域。

若需修改、删除已定义的名称，应在“名称管理器”或“定义名称”对话框中，选定该名称，单击“编辑”或“删除”按钮。

需注意的是，若该名称已被引用，则不要随意删除，否则引用的单元格将会因为引用的名称不存在而提示错误“＃NAME？”。

设计星科制造公司Excel会计分录表。要求：录入了错误的日期数据时提示修改，或使用了会计科目表之外的会计科目名称时无法保存；摘要不能超过15个字符等。

Excel中完成工作任务后的会计分录表如图7-7所示。当选定A2、B2、C2、F2、G2或H2单元格时，将显示下拉箭头；当单击该下拉箭头时，将弹出带单引号前缀的年、月、日序列或会计科目名称序列供选择。当单击D2单元格时，将提示编号规则。在E2单元格录入的摘要超过15个字符时将提示错误。

图7-7　会计凭证表（Excel 2016公式选项卡）

（1）新建Excel工作表，将其表标签名称修改为“会计分录”。在第一行中录入该表的相关文字（即字段名）。

（2）设置月的序列值。由于本工作簿只能在2022年使用，共12个月，每月最多为31天。所以，可以进行数据验证，当输入的数据有错时，提示修改。Excel 2013—Excel 2019设置月值验证的方法如下：

a.单击B列（第二列）列头，以选定该列；单击“数据/数据工具/数据验证”命令，进入“数据验证”对话框，如图7-8（a）所示。

b.单击该对话框的“设置”卡片，在“允许”中选择“序列”；在“来源”框中输入带单引号（半角状态）前缀的“′01，′02，′03，…，′12”，并勾选“忽略空值、提供下拉箭头”项。

c.单击“出错警告”卡片，选择“样式”为“警告”；在错误信息框中录入“请输入：文本格式两字符的月份值”，如图7-8（b）所示。

(www.xing528.com)

图7-8　数据验证设置（a）与出错警告（b）卡片

d.单击“确定”按钮。回到工作表后，选定B列的任意单元格时，将显示下拉箭头。

（3）类似地，设置年、日的序列值。其中，A列（年）的序列值为带单引号（半角状态）前缀的“2022”，出错警告中选择样式为“停止”。

C列（日）的序列值为带单引号（半角状态）前缀的“′01，′02，′03，…，′31”；出错警告中选择样式为“信息”。

（4）设置凭证号的输入提示信息。选定D列，进入“数据验证”对话框，单击“输入信息”卡片，在输入信息框中录入“记-XXX”，如图7-9（a）所示，单击“确定”按钮。

图7-9　数据验证输入信息（a）与设置（b）卡片

（5）设置摘要的限制字符。选定E列，进入“数据验证”对话框并单击“设置”卡片，选择允许中的“文本长度”小于或等于“15”；单击“出错警告”卡片，选择样式为“警告”，在错误信息框中录入“限15字符”；单击“确定”按钮。

（6）设置一级科目的序列值，用表间取数设置序列值的方法如下：

a.选定F列（一级科目），进入“数据验证”对话框并单击“设置”卡片，选择允许中的“序列”项。

b.单击该对话框来源下的编辑框，然后选定“会计科目”表标签并单击该表的B列列头，此时编辑框中将显示“＝会计科目！＄B：＄B”，如图7-9（b）所示。

c.单击“出错警告”卡片，在样式中选择“停止”，在错误信息框中输入“请选择正确的一级科目”，单击“确定”按钮。

（7）类似地，设置G列、H列的序列值。其中，G列（二级科目）的来源为“＝会计科目！＄C：＄C”；出错警告样式中选择“停止”。

H列（三级科目）的来源为“＝会计科目！＄D：＄D”；出错警告样式中选择“停止”。

（8）选定I列（借方发生额）、J列（贷方发生额），单击“千位分隔符”按钮。

（1）数据验证。在Excel 2010及其以前版本中设置“序列”值，应选择“数据/有效性”命令，进入“数据有效性”对话框进行设置。

（2）Excel 2007及其以前版本数据有效性的“序列”值的来源，不能引用其他工作表或其他工作簿的数据。所以，设置一级科目、二级科目与三级科目的序列值的方法是：先定义单元区域名称，再在“序列”值中粘贴名称。实际上，Excel 2010—Excel 2019也可按这种方法进行设置（但它不是最优方法）。方法如下：

a.单击“会计科目”表使之成为当前工作表，选定B列（一级科目）。

b.Excel 2003及其以前版本选择“插入/名称/定义”菜单命令，Excel 2007选择“公式/定义名称”功能命令，进入“定义名称”对话框，如图7-10（a）所示；在该界面上部名称框录入“一级科目”，下部的引用位置自动显示为“＝会计科目！＄B：＄B”（若不正确，可以修改，即删除原有数据，通过键盘录入正确的字符）；单击“添加”按钮。

c.再在定义名称上部输入“二级科目”；单击下部引用位置框（先删除原有内容），再单击会计科目表C列的列头，此时编辑框将显示“＝会计科目！＄C：＄C”；单击“添加”按钮。

d.类似地，添加“三级科目”名称，引用位置为“＝会计科目！＄D：＄D”。

e.单击“定义名称”对话框的“确定”按钮。

（3）Excel 2007及其以前版本在“会计分录”表中粘贴名称，方法如下：

a.单击“会计分录”表使之成为当前工作表，选定F列（一级科目），选择“数据/有效性”命令进入“数据有效性”对话框。

b.在“设置”卡片中选择允许为“序列”；单击来源框，Excel 2003及其以前版本选择“插入/名称/粘贴”菜单命令，Excel 2007选择“公式/定义的名称/用于公式/粘贴名称”命令，弹出“粘贴名称”对话框，如图7-10（b）所示；选择“一级科目”并单击“确定”按钮，数据有效性的来源框中将显示“＝一级科目”（注意名称引用没有“！”与单元区域，与表间取数有区别）。

图7-10　定义名称与（a）粘贴名称（b）对话框

c.单击“出错警告”卡片，在样式中选择“停止”；在错误信息框中输入“请选择正确的一级科目”，再单击“确定”按钮。

d.类似地，将“会计分录”表的G列、H列的有效性设置为“序列”，来源分别粘贴为“二级科目”“三级科目”名称（不是表间取数）。

（4）Excel 2010—Excel 2019的名称定义在“公式”选项卡的“定义名称”组（本例不用定义）；在该选项卡的“名称管理器”中可进行名称的删除或修改。