Excel应用实例.doc

Excel应用实例：轻松套打带照片的准考证 2004年06月25日15:21 赛迪网-中国电脑教育报文/向德海星期一刚上班，同事小张迎上来笑嘻嘻地说：“主任让你务必于今天上午将准考证打印出来，数据已录好了，照片已加入字段”。我想用VFP的报表功能实现带照片的准考证套打，应该是很轻松的事。打开小张递过来的数据盘，原来数据全部用Excel录入，照片作为其中的一个字段已放在单元格内(如图1)，如果用VFP，还需将工作表转换成DBF格式，并将照片复制成一个个单独的图片文件，好几百人的照片，没有几天的功夫怕是完成不了任务的。还是在Excel上想想办法吧，看有没有捷径可走。通过探索，笔者终于将此问题解决了，现将方法介绍如下。图11. 设计套打模板，建立数据关联图2是空白准考证样式，先用扫描仪将准考证扫描，并保持大小尺寸不变，然后将图片保存为JPG格式。打开Sheet2工作表，将刚才保存的图片插入表中，并将图片设定为非打印对象。执行“视图工具栏绘图”命令，在准考证需填充文本的部位插入矩形框。我们用VLOOKUP函数实现Sheet1工作表中数据与Sheet2表中准考证间的关联(照片填充部分除外)。首先将A21单元格作为查询的关键数值，在A20G20单元格中分别输入序号、姓名、级别等字段。在B21单元格中输入公式“=IF(VLOOKUP(A21,Sheet1!A2:I1000,2)=0,VLOOKUP(A21,Sheet1!A2:I1000,2)”，在C21单元格中输入公式“=IF(VLOOKUP(A21,Sheet1!A2:I1000,6)=0,VLOOKUP(A21,Sheet1!A2:I1000,6)”，其他依此类推。这样就建立了准考证填充数据与Sheet1表间的关联。下一步就是要建立数据与准考证相应矩形框的关联，以准考证中姓名字段为例，鼠标单击该矩形框，在编辑栏右侧输入“=”符号，然后用鼠标点击需要填入数据的B21单元格，这样矩形框就和B21单元格中的数据建立了联系。只要单元格数据变化，则矩形框数据也跟着变化，其他依此类推。这样，只要在A21单元格输入序号值，那么Sheet1表中相应序号行的数据就会在准考证模板中自动生成。上述工作完成后，按“Shift”键选中所有矩形框，设置矩形框格式，包括字体、大小、对齐、颜色和线条等，特别是选中无填充色和无线条色。 图22. 定义名称，导入照片通过VLOOKUP函数虽然建立了工作表1和准考证的联系，但要在准考证中自动生成照片则需要用到比较复杂的函数，采取定义名称的办法 。具体步骤如下：(1)定义名称：执行“插入名称定义”命令，打开如图3所示“定义名称”对话框，将名称定义为“A”，在“引用位置”文本框中输入公式“=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)”，按下确定按钮返回。该公式建立了查询数据的动态地址。接着用同样方法，再定义另一个名叫“X”的名称，里面包括公式“X=INDIRECT(ADDRESS(MATCH(Sheet2!$A$21,A,0),9,1,Sheet1)”，该公式表示当动态地址栏为Sheet2表中A21单元格数值时，返回Sheet1表中第9列即照片所在列单元格内容。图3(2)导入图片：选取sheet2工作表，执行“视图工具栏控件工具箱”命令，打开“控制工具箱”窗口。点击图像框或文字框按钮，在准考证需要填充照片的位置画出相同大小的图片框或文字框。选取文字框(图像框)后，将编辑栏中“=EMBED(Forms.TextBox.1,”改成“=X”，如图4，这样只要A21单元格数值变化时，照片框中相应的照片就会发生变化。图43. 制作微调按钮，编写宏代码为操作更加方便，可以制作微调按钮调节序号值。执行“视图工具栏窗体”，打开窗体对话框，点击微调项标志按钮，建立微调按钮，右击按钮，打开“设置控件格式”对话框，并与B19单元格建立链接，按下确定按钮。选中A21单元格，在编辑栏中输入“= B19”，此时点击微调按钮，就可自动调节B19和A21单元格序号值，对应的数据就会在模板中自动生成，效果如图5。接下来就是打印了。 图5执行“工具宏Visul Basic编辑器”命令，在Visul Basic编辑器中选择“插入添加模块”，在代码窗口输入以下代码：Sub 打印()ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=TrueCall dyEnd SubSub dy()Dim a%, b$, c$, abc$a = Sheets(Sheet2).Cells(19, 2).Valueb = Sheets(Sheet2).Cells(19, 4).Value(说明：a即B19单元格，是图5中打印第页至第页中的起始页，b即D19单元格，是终止页。)If a Excel来套打，Excel强大的表格计算功能和自动填充功能可大大提高工作效率。在此介绍一下笔者用Excel 2003套打表格的心得。1.扫描表格 把原表格放入扫描仪，扫描分辨率设定为100dpi、缩放比设为100、模式为黑白文本。在预览图形上调整扫描区域框使它尽量靠近表格边框，你也可以在扫描后再用图形处理软件进行修剪，去除表格周围的空白，再将扫描的图像处理后保存好。如果在表格外也要填写的话，那么在扫描前最好先用铅笔在原表格上画一个边框，作为扫描后修剪和量取页边距的依据。2.设置背景 启动Excel 2003，单击菜单栏的“格式/工作表/背景”，打开工作表背景对话框，找到刚才保存的表格图像，设定为背景。单击工具栏上显示比例的下拉列表框，在框中直接输入105后回车，把显示比例设定为105%。注意：这里105%的比例是这样得来的。在纸上画一个边长10厘米的正方形，扫描后设为电子表格背景，此时比例为100%，用绘图工具栏中的“矩形”工具画一个与背景正方形完全重合的矩形，打印出来。量得打印出正方形边长为10.5厘米，除以原正方形的边长10厘米，可得比例为105%。另外，你也可以在Excel中画一个边长为10厘米的正方形，再试着慢慢调整比例，使它与背景正方形重合，亦不难得到相应的比例。3.定位填写 通过拖动行、列标题的边界调整行高或列宽，再配合使用格式工具栏中的“合并及居中”按钮，调整表格线使它与背景表格的表格线重合(也不必很准确，只要能让文字填写在所需位置就可以)。对于大量等高(宽)的行(列)，全部选中并从菜单样中的“格式/行/行高(列/列宽)”中直接输入数值会更快些。然后在相应单元格里输入数据、文字或公式引用(如图)，记住千万不要设置单元格边框，这样一张表格就做好了。4.页面设置 单击菜单栏中的“文件/页面设置/页边距”，用尺子量取原表格纸中表格边框到纸张左边和上边的距离，把量取的尺寸直接输入页面设置对话框中相应的左、上页边距中，右、下边距则要设小一点，让整张表格都可以打印出来就行了。单击“选项”按钮，进入打印机设置界面自定义纸张尺寸，直接输入纸张的尺寸，这当然也要直接用尺子测量。 部分打印机设置中可能没有自定义纸张尺寸的功能，此时我们只要从“文件/页面设置/页面/纸张大小”中选一个比实际纸张大点的纸张尺寸，一般也可以正常打印。 5.打印输出 现在可以把原表格纸放入打印机打印了，如果以上操作均无误的话，一般无须调整即可一次成功。即使出现水平或垂直移位，也只要直接量取偏移值，然后增减到相应的上、左页边距中即可。在