Excel服务器认证工程师培训教材2

图2-1填报时出现下拉列表1.1设置主键，保证录入数据的唯一性。“主键”是一个数据库的概念。针对一个数据表，我们可以把其中某个或某几个字段设成主键。在输入数据的时候，数据库会自动检查主键字段的内容是否唯一。如果发生重复，则拒绝保存。下面，我们把“客户登记表”中的“客户名称”设置为主键，以此来保证所有输入的客户没有重名。定义主键1）用Admin账户登录，打开《客户登记表》模板，进入设计状态。2）选择菜单“模版管理数据表”，调出“管理数据表”窗口，点击“字段定义”按钮，弹出“字段定义”窗口。3）在“字段定义”窗口中“客户名称”左侧的“主键”栏单击，出现“√”，然后单击【修改字段】按钮。图2-2设置主键4）点【返回】，回到“管理数据表”窗口，再次点【关闭】，回到模板设计状态。5）关闭模板主键的作用回忆5.4节，我们输入的第一份《客户登记表》的客户名称是“甲公司”，现在把“甲公司”的客户信息再录入一遍，看会出现什么情况。1）新填一份《客户登记表》，名称输入“甲公司”。2）保存，出现以下的提示：图2-3主键的作用这就是主键的作用，通过设置“主键”，我们不需要靠人为的小心谨慎来保证数据的唯一，这个任务由系统自动完成了，可以大大提高数据的准确性。1.2本章练习请自行完成以下练习。本练习中建立的模板在下面的章节中会用到。1）建立《物料表》模板，表样如下：图2-4《物料表》模板的表样设置该模板“Admin”可以录入，其他用户可以查看。2）定义《物料表》模板上的数据项，组成数据表“物料表”主键区域字段名数据类型必填填写规范其它C2物料类别文字(100字以内)不清空C3物料编码文字(20字以内)√E3物料名称文字(100字以内)√C4规格型号文字(100字以内)E4计量单位文字(20字以内)E6当前库存量小数3）定义自动编号，让“物料编码”能够自动生成。4）让“计量单位”可以下拉选择。5）让“物料类别”可以下拉选择。6）输入若干物料信息第2章、管理业务数据本章我们通过建立一个业务单据模板----《订单》，继续学习模板的设计，同时会接触到Excel服务器的最精彩功能之一----表间公式的定义和应用。本章具体内容包括：定义重复数据项使用动态取值的下拉列表学习表间公式的概念，定义并应用一个简单的表间公式使用列表选择数据规范2.1定义重复数据项单一数据项和重复数据项图2-1订单表样上图是我们要建立的《订单》模板的表样。如前所述，模板上可以填写内容的那些空白单元格称为“数据项”。在订单的表样上，有表头----2~4行的客户信息、表尾---第12行的审批信息，也有明细----7~10行的订货产品信息。我们说在《订单》模板上，有两种不同性质的数据项。第一种，即表头和表尾处的空白单元格，为“单一数据项”。它们的特点是在一份表单上只出现一次。比如C3:F3处的“客户名称”，一份订单上只能有一个“客户名称”。第二种，即明细中的空白区域，为“重复数据项”，它们的特点是在一份表单上会填多次，比如区域C7:C10处的“产品名称”，一份订单上会包括多种产品。该区域的每一行的单元格的内容都不一样，但它们都是“产品名称”。建立《订单》模板1）建立订单模板，表样如图6-1所示2）权限设置为：填报权限：Administrators、销售员查阅权限：Administrators、总经理、销售部长、销售员。其中销售员只能查看本人填报的，其他角色能够查看全部。3）输入若干Excel公式单元格/区域公式作用H7:H10=F7*G7金额＝数量×单价H11=SUM(H7:H10)计算合计金额4）把表头、表尾定义成单一数据项，组成数据表“订单_主表”主键区域字段名数据类型必填填写规范其它√C2订单编号文字(20字以内)√不清空E2:F2订单日期日期√当前日期H2录入人文字(20字以内)√当前用户姓名C3:F3客户名称名称或地址√H3客户编号文字(20字以内)√C4:F4地址名称或地址H4电话文字(100字以内)C12审批日期日期E12:F12审批人文字(20字以内)H12审批意见文字(20字以内)5）定义自动编号“订单编号”，设为“订单编号”字段的填写规范。定义重复数据项，组成明细表现在来把订单的明细部分定义成重复数据项。1）在订单模板的设计状态下，选中区域B7:H10，右击鼠标，选择弹出菜单“定义重复数据项”图2-2选中要定义为重复数据项的区域2）弹出如下图所示的“定义重复数据项”对话框，点击带图标的大按钮【按行】，上方标题行数1，选中“数据区域可向下扩展”，按【下一步】。图2-33）选择“组成新数据表”，命名为“订单_明细”如图，按【下一步】图2-44）选择各字段的数据类型，设置所有字段必填，点【下一步】。图2-55）在下一屏上直接点【完成】，窗口关闭，回到订单模板的设计状态。6）关闭并保存模板。填报时扩充明细表的行数在订单模板的表样上，我们只预留了4行明细的位置，但实际的订单可能不只4条明细。因为我们在定义“订单_明细”的重复数据项时，指定了“可向下扩展”，填报时，工作表的上方会出现“报表操作工具栏”。当输入焦点进入“订单_明细”表所在区域时，工具栏中的【增加行】和【删除行】按钮可选，如下图所示。通过这两个按钮，可以向明细表增加新行或删除多余的空行。图2-6报表操作工具栏关于“报表操作工具栏”上其它按钮的作用，我们会在后续的章节中陆续介绍。2.2动态取值的下拉列表在上一章，我们学习过“下拉列表”这种数据规范，现在我们为订单上的“客户名称”定义下拉列表。1）用Admin帐户登录到管理台。2）新建数据规范。输入内容如下图：点此增加明细行数图2-7动态取值的下拉列表注意在这里，我们对数据源选择的是“来自数据表”，意思就是，下拉列表中的可选项不是事先写定的，而是根据“客户登记表”中的内容动态决定。3）按【确定】4）回到Excel，在《订单》模板的设计状态下，选择菜单“模板管理数据表”，设置“订单_主表”中“客户名称”字段的填写规范是“客户名称列表”。5）关闭模板。现在用小宋的帐户登录，来填报一份订单，我们看到，“客户名称”处出现了下拉列表，其中的内容是我们已经输入的两张客户登记表中的客户名称。图2-8练习1）复习上一章，在订单模板上设置如下数据填写规范：a)订单编号自动生成b)录入人默认为当前用户姓名c)订单日期默认为当前日期2）多输入一些客户登记表信息2.3用表间公式提取数据在上一节，我们对订单模板上的客户名称字段设定了填写规范，填报时可以从下拉列表中选择，但地址、电话等依然还需要手工输入。设想一下填写订单的过程：填好客户名称“甲公司”--地址在哪里？…去翻一下客户登记表…找到了！--把找到的地址“抄写”到收货地址处--接着填…这个过程中的“翻查”和“抄写”既耽误时间又容易出错。实际上，因为所有客户的信息都已经通过《客户登记表》模板录入计算机了，所以“翻查/抄写”的工作完全可以由计算机完成，只需要告诉计算机：按照“我所选定的客户名称”到客户表中查一下地址，填充到“地址”栏。这句话实际上表达了四个方面的含义：1）从哪里找数据？----到“客户登记表”中。2）找符合什么条件的数据？----按照“我所选定的客户名称”3）找什么数据？----地址4）找到数据做什么用？----填充到“地址”栏这就是表间公式的最原始表达。用稍微规范一些的方式描述一下，教给计算机，就是这样：2.3.1定义表间公式1）打开《订单》模板，进入设计状态2）选择菜单“模板定义表间公式”，弹出“表间公式”对话框3）按【新增】按钮，弹出如下图所示的对话框，选中第一个单选按钮，按【确定】图2-9选择表间公式类型4）弹出定义表间公式对话框，在公式名称处输入“查询客户信息”，应用方式选择“筛选条件改变后自动执行”如下图。图2-10输入表间公式名称5）按【来源数据】，弹出如下对话框。所谓“数据源”指的是现有模板上那些已创建的数据表。因为我们输入的所有信息都存储在这些数据表中。对话框左上部的列表中列出了所有已创建的数据表的名称。在左侧列表中选中“客户表”，点击【】，客户表移到右侧列表，按【确定】图2-11选择表间公式数据源6）回到“定义表间公式”对话框，点击【筛选条件】按钮图2-127）弹出“输入表达式”对话框，做以下操作：a)单击左侧分类列表中的“客户表”，右侧选项列表中出现客户表中的左右字段b)双击选项列表中的“客户表.客户名称”，使之出现于下方的表达式文本框c)单击常用操作符中的【=】d)单击分类列表中的“本报表”e)双击选项列表中的“本报表.Sheet：客户名称”构造好的表达式为“客户表.客户名称=本报表.Sheet1:客户名称”，如下图图2-13输入表达式图2-14输入表达式表间公式在模板的设计状态下定义，在填报时实际起作用。其中的“本报表.Sheet1:客户名称”指的是“当前正在填报的这张表单上所输入的客户名称”。它是一个变量，只有在填报的时刻才能确定取值。a)单击客户登记表b)双击“客户登记表.客户名称”c)单击[=]d)单击本报表e)双击“本报表.Sheet1:客户名称”这里是构造好的表达式8）按【确定】回到定义表间公式对话框，点击【填充方式】按钮。图2-15准备输入填充方式9）出现“定义填充方式”对话框，填充数据表选择“订单_主表”，双击“地址”所在行的第一列图2-16双击输入填充方式10）再次弹出“输入表达式”对话框，参照前述方法输入表达式内容为“客户登记表.地址”，按【确定】。双击此处图2-17输入填充表达式11）回到“定义填充方式”窗口，结果如图。图2-1812）继续输入“客户编号”和“电话”的填充方式。图2-19请注意：在每个填充目的字段的右侧，都有一个“锁定”复选框，勾选此复选框，表明通过表间公式填充到表单上的内容会被锁定，不允许手工修改，反之，则允许修改。13）按【确定】。回到“定义表间公式”窗口，显示完整的表间公式如下：图2-20定义好的表间公式14）按【确定】回到最初的“表间公式”窗口，按【返回】15）关闭模板。2.3.2应用表间公式以小宋的账户登录，填写一份订单，您会发现两个现象：1）选择客户名称后，客户编号、地址和电话自动显示。2）输入焦点无法进入客户编号、地址和电话所在的单元格，也就是说，它们被锁定了！前者意味着输入变得简便快捷，而后者意味着出错的可能性大大降低。2.4列表选择数据规范通过前面两个小节的工作，我们已经尽可能简化了“订单_主表”的输入――只需要选择“客户名称”，其它内容全部是自动填充的，或者通过数据规范，或者通过筛选条件改变后自动执行的表间公式。在这一小节，我们来解决“订单_明细”的输入问题，达到如下图所示的效果――当输入焦点放到“产品名称”位置上时，自动弹出一个选择列表，列举所有产品的编码、名称、规格等，可以从中选择一条或几条，自动填充到订单明细上。图2-21填报时自动弹出选择列表2.4.1定义列表1）以Admin帐户登录到管理控制台2）在管理台主窗口的左侧选中“数据规范列表选择”，点击鼠标右键，选择弹出菜单【新建】，弹出【定义列表数据规范】窗口，输入名称为“产品列表”。图2-22新建列表3）点击【1:数据源】，弹出“选择数据表”窗口，选择数据表为“物料表”，点击【确定】图2-23选择列表数据源4）回到“定义列表数据规范”窗口，点击【3:定义数据项】，弹出“定义列表数据项”窗口。如下图。图2-245）双击第一行的“表达式”栏。弹出“输入表达式”窗口，输入表达式“物料表.物料编码”，点击【确定】，回到“定义数据项”窗口。6）继续定义其他列表数据项，结果如图。在“物料名称”对应的“默认返回值”栏打勾。图2-25我们在这里定义的列表将成为模板上的某个数据项的填写规范。列表选择和下拉列表不同，下拉列表只有一列，而列表选择有多列。所谓“默认返回值”，指的就是，填报时选中了弹出列表的某一行之后，要把这一行信息中的哪一列的内容，返回到该数据项所在的位置双击这里上。7）点击【确定】，回到“定义列表数据规范”窗口，再次点击【确定】，完成列表的定义。2.4