Excel制表技巧批量制作.docx
《Excel制表技巧批量制作.docx》由会员分享,可在线阅读,更多相关《Excel制表技巧批量制作.docx(14页珍藏版)》请在三一办公上搜索。
1、Excel制表技巧(13 )批量制作批量制作利息催收通知单在银行信贷部门的日常工作中,经常要利用会计部门提供的贷款欠息情况表,向每个贷 款欠息户发放贷款利息催收通知单催收利息。由于贷款欠息户数量较多,采用常规的逐户编 辑打印利息催收通知单的方法势必耗费大量的工作时间,经过一段时间的摸索,通过对 EXCEL 2000和WORD 2000灵活运用,找到了一条快速、准确完成大批量制作贷款利息 催收通知单工作的捷径。现将制作过程介绍如下,供有类似要求的读者朋友参考。一、利用EXCEL 2000实现利息小写金额向中文大写金额的转换1、数据导入。运行EXCEL 2000,点击工具栏上“打开文件”按钮,找到
2、会计部门提供 的贷款欠息情况表文件后,点击“打开”,EXCEL 2000自动弹出“文本导入向导”3步骤之1:如设置无误,点击“下一步”按钮,进入“文本导入向导”3步骤之2,在这里你可以选择合 适的分隔符号,在预览窗中可预览分列效果,满意后点击“下一步”按钮,进入“文本导入向导”3 步骤之3,在这里你可以对每列数据格式进行简单设置(当然可以在EXCEL 2000中进行高 级设置),如果我们不是导入全部数据列,还可以选择不导入某列,当所有设置完成后,点 击“完成”按钮,将数据导入EXCEL 2000中,并将文件另存为EXCEL文件格式(本文设存 盘文件名为“应交利息.XLS)。2、格式转换。由于在
3、EXCEL 2000中系统提供的中文大写数字格式与我们日常使用的中文 大写金额格式相差较远,如小写金额“138.59元在EXCEL 2000中只能转换成“壹佰叁拾捌. 伍玖”,因此我们必须想办法实现正确的格式转换。经过实验,发现通过设置中间字段,利 用IF判断函数、TRUNC截尾取整函数及TEXT函数可以很好地解决这个问题。1) 、打开应交利息.XLS”文件,在D1至G1单元格按顺序增加“元位”、“角位”、“分位”、 “应交利息(大写)”四个字段。2) 、为了能将“应交利息”C列中小写金额转换成中文大写金额,我们需将小写金额按“元 位”、“角位”、“分位”进行分解转换,并在相应位置添加元”、“
4、角”、“分”符号,以备将来合并 之用。具体分解通过下列公式实现:D2=IF(TRUNC(C2)=0,TEXT(TRUNC(C2),DBNum2G通用格式)&元)中国15:43:04|m|m|HE2=IF(TRUNC(C2*10)-TRUNC(C2*10,-1)=0,TEXT(TRUNC(C2*10)-TRUNC(C2 *10,-1),DBNum2G/通用格式)&角)F2=IF(TRUNC(C2*100)-TRUNC(C2*100,-1)=0,TEXT(TRUNC(C2*100)- TRUNC(C2*100,-1),DBNum2G/通用 格式)&分)在 G2 单元格使用公 G2=IF(AND(D
5、2,F2,E2=),D2&零&F2,IF(AND(D2, E2=,F2=),D2&整 ,D2&E2&F2)通过对元、角、分位是否为零的判断,将元、角、分按 照中文大写金额的书写规律进行合并,得到所需规范的中文大写金额。使用自动填充柄功能将D3至G11单元格公式设置好,得到如下效果图。至此我们完成了应收利息由小写金额向大写金额的转换工作,将文件存盘后退出,进 入一步工作二、利用WORD 2000实现快速批量打印利息催收通知单为了实现快速批量打印利息催收通知单,我们必须利用WORD 2000中的邮件合并功 能来帮助完成工作。1、建立文档。启动WORD 2000新建一空白文档,按规定格式编辑好利息催
6、收通知单 中的固定内容,设置好页面格式。2、邮件合并。选择工具菜单中“邮件合并”选项,进入“邮件合并帮助器”窗口;点击“主文档”中“创建”下拉菜单中的“分类”按钮,在弹出窗口中选择“活动窗口”;然后点 击“数据源”中“获取数据”下拉菜单中的“打开数据源”按钮,弹出“打开数据源”窗口,找到刚才 我们在EXCEL 2000中保存的“应交利息.XLS”文件,点击“打开”;在随后弹出的窗口中点击“确定”,在接着弹出的窗口中点击“编辑主文档”按钮来进行给 主文件添加合并域的工作。将光标移至需要添加合并域的位置,点击“插入合并域”按钮,从下拉列表中选择需要插入的 合并域;重复这一步骤,在所有需要插入合并域
7、的地方插入相应的合并域。然后点击工具栏 上“查看合并数据”按钮进行检查核对。检查无误后,即可进行批量打印利息催收通知单。点击工具栏上合并选项”按钮,弹出 “合并”窗口,在“合并到”下拉列表中选择“打印机”,开启打印机后,点击确定”按钮,直接将 合并的每一份利息催收通知单输出到打印机。用Excel批量计算年龄一位做保险业务的朋友接到一笔团队大订单,有近千条记录。她花了半天的时间用Word 录入了客户的档案后,根据要求需要补充填写每个客户的年龄。如果一个个用笔算,又是需 要花上大半天的时间,还好客户档案中有每个人的身份证号,于是我用Excel很快帮她计算 出了每位客户的年龄。1.首先把Word中的
8、客户档案全部复制到Excel中生成一个数据库文件。然后在数据库 文件中新建一列,并命名为出生年月”。因为身份证号有15位和18位,为了计算方便,先 对身份证号进行排序。2.用MID函数计算出第一个客户的出生年月。函数表达方式如下:MID(E2, 7, 2), 表示第一个客户的身份证号在E列第二行中,要从这个位置中的第7个文本始返回2个长 度的字符。回车确认后,“J2”中的值变为“62”,表示该职工62年出生。接着下拉J2公式复制单元格, 快速求出每个职工的出生年月(若身份证为18位,则公式变为MID(E2, 9, 2)再把J 列的格式改为数值型。3.在D2中输入计算机公式“=103-J2”就可
9、求出该职工的实际年龄。下拉D2中的公式再次复制,近千个职工的年龄就一键敲定。小编注:以上的方法中的出生年月只有在2000年以前才有效,如果客户中有2000年以 后出生的人,则计算时还要做些变通。Excel自动求和”批处理功能在表格中,许多时候大家需要对行或列进行求和运算,如果同时有多行或多列需要求和, 该怎样实现求和运算的简化操作呢?以下,我们以图1所示表格为例,使用最为常见的软 件,为大家讲解如何实现“自动求和”的批处理。、瑚绩 姓名语文张三李四90王五100总成绩.幻、工作表选中图1所示表格中“总成绩”下的3个单元格,然后单击常用工具栏中的“自动求和”按 钮。怎么样,Excel准确判断并计
10、算出三人的总成绩了,够智能吧!二、wps表格下面以“金山文字2003”为例说明操作过程:1. 用鼠标单击表格使其处于编辑状态。2. 在表格中选定多个连续的单元格,即矩形区域。对图1所示表格而言,请选中表格“语 文”、“数学”和“总成绩”下面的9个单元格。3. 按鼠标右键,在右键菜单中,指向“单元格计算”,然后单击“横向求和”由以上操作我们可以看出,wps表间计算与Excel的不同之处是选择的单元格区域的不 同,大家可一定得注意。三、word表格与Excel、wps相比,word的计算功能最弱,没有提供批量求和的功能,但以下的操 作仍能够减轻大家的工作量:1. 单击“总成绩”下的第一个单元格,然
11、后在表格”菜单中,单击“公式”,弹出“公式”对话 框。如果“公式”下的框中出现的不是“=SUM(LEFT)”,请将其中内容改为=SUM(LEFT)”(如 图3),单击“确定”按钮,则计算结果即“167”出现在“总成绩”下的第一个单元格。2. 选中“张三”的“总成绩”即“167”,单击常用工具栏上的“复制”按钮。3. 单击“总成绩”下的第二个单元格,再单击常用工具栏上的“粘贴”按钮,把求总成绩公 式复制到该单元格。同理,也把公式复制到“总成绩”下的第三个单元格。4. 选中“总成绩”下的所有单元格,按下F9键。实际上,word是通过“域”功能实现数值运算的,“域”不能够根据变化的数值自动计算, 只
12、有选中“域”后,按下F9键方可重新计算。愿笔者所述对你能够有所启发和帮助。用公式批量提取数据信息假设在一个Excel工作簿中有N个企业基本情况登记表,不同工作表中分别保存了各 个企业的基本情况数据,工作表格式都一致,名称分别为Sheetl、Sheet2直到SheetN。 图1为Sheetl表中企业基本情况表,从该表中可以看到,企业名称、负责人姓名和联系电 话等数据分散在B2、B4及G4单元格中。如何提取各表中企业名称、负责人姓名和联系电 话等内容,并将提取的内容制成如图2所示的表格呢?AB dDEFG1企业基本情况登记表时企业名称市一建公司成立肘间19兀1年3月3专业类别土建资质等级批准时间如
13、口年B月4负责人利一技术职称工程师联系电活23456785 .技术负责丁 一技术职称高缄工程师联系电话2333335&荻奖工程一览7序号工程名称.获奖时间荻奖内容AD1全市建筑企业负责人联系电活.2序号企业名称负责人联系电话31 ;市一建公司;刘_234567842市二建公司弗三5.专市工程建监理公司王武23456706市路桥公司电前2356899一、新建工作表在工作簿中插入一个新工作表,并将该工作表命名为“通讯录”,合并A1-D1单元格, 在合并后的单元格中输入“全市建筑企业负责人联系电话”。分别在A2、B2、C2、D2单元 格中输入“序号”、“企业名称”、“负责人”、“联系电话”等字段。二
14、、输入公式在序号列中填充如图2所示的序号,在B3单元格中输入公式:“=INDIRECT(Sheet&ROW()-2&!$B$2)”。公式中 INDIRECT 函数返回由文字串指定的 引用。ROW()返回所在单元格的行号,Sheet&ROW()-2&!$B$2为要引用的单元格,该 公式含义为返回Sheet1表中B2单元格的值。同样道理在C3及D3单元格中分别输入 “=INDIRECT(Sheet&ROW()-2&!$B$4)、=INDIRECT(Sheet&ROW()-2&!$G$4)。 上述公式中单元格引用要使用绝对引用,否则会导致错误的结果。公式输入完成后,选中 A3:D3单元格区域,将鼠标
15、指向D3单元格右下角的填充柄,将公式向下拖动复制到相应数 据区域。这样,一份精致的表格就呈现在你面前了。三、批量命名工作表如果各工作表名称无规律,如“一建公司表”、“路桥公司表”等,使用公式前还需将所有 表名称统一为默认的表名,当然表数量不多,用手工命名也可以,如果工作表数量很多,批 量命名则要使用VBA代码了。具体实现方法如下:执行“工具一宏一Visul Basic编辑器”命 令或按下Alt+F11组合键,在Visul Basic编辑器中选择“插入一添加模块”(图3),在代码 窗口输入以下代码:Sub工作表命名()Dim Sh As WorksheetFor Each Sh In Works
16、heetsSh.Name = Sh.CodeNameNextEnd Sub输入完成后,关闭VBA窗口,返回到工作表编辑窗口,在Excel文档中执行宏即可。 将工作表名称统一后,再按照步骤一和二介绍的方法,提取表格中的数据。怎么样,是不是 很简单?批量修改数据比如我们做好一个Excel表格,填好了数据,现在想修改其中的一列(行),例如:想 在A列原来的数据的基础上加8,有没有这样的公式?是不是非得手工的一个一个数据地住 上加?对于这个问题我们自然想到了利用公式,当你利用工式输入A1=A1+8时,你会得到 Excel的一个警告:“MICROSOFT Excel不能计算该公式.”只有我们自己想办法了
17、, 这里介绍一种简单的方法:第一步:在想要修改的列(假设为A列)的旁边,插入一个临时的新列(为B歹U),并在B列的第一个单元格(B1)里输入8。第二步:把鼠标放在B1的或下角,待其变成十字形后住下拉直到所需的数据长度,此时B列所 有的数据都为8。第二步:在B列上单击鼠标右键,“复制” B列。第四步:在A列单击鼠标的右键,在弹出的对话框中单击“选择性粘贴”,在弹出的对话框中选择 “运算”中的你所需要的运算符,在此我们选择“加”,这是本方法的关键所在。第五步:将B列删除。怎么样? A列中的每个数据是不是都加上了8呢?同样的办法可以实现对一列(行)的 乘,除,减等其它的运算操作。原表格的格式也没有改
18、变。此时整个工作结束,使用熟练后,将花费不到十秒钟。Excel批量修改标准化试卷笔者从事信息技术教学多年,每次都为考试后的批卷感到头痛。虽然采用标准化试题, 但近300名学生的试卷,一份份地改完,要花费大量的时间。为提高自己的工作效率和阅 卷的准确率,笔者利用Excel强大的统计和判断功能,制作了一个电子答题卡,来实现自动 的快速阅卷。1. 考前准备工作本校微机教室有学生机96台,微机考试分为上机考试和理论考试,理论考试共计60道 选择题(每题1分,占60%)。理论考试的试题要事先存放在教师机上,共计四套试卷, 每批考试的试卷是随机抽取的(此方法也适用于其他课程的标准化考试)。在教师机上抽取
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Excel 制表 技巧 批量 制作
链接地址:https://www.31ppt.com/p-4884415.html