费用发放单的Excel自动编制，本文主要内容关键词为：费用论文,Excel论文，此文献不代表本站观点，内容供学术参考，文章仅供参考阅读下载。
目前，财会或部门管理人员在实际工作中，常需根据工资总表或其他费用汇总表，制作工资条或部门费用发放单。传统的编制方法通常是，手工将总表中的若干记录，复制粘贴到指定规格的费用发放单模板中，再打印输出，多次重复这一过程，直到全部数据打印结束。当总表中的数据增加、修改或部分删除后，再重复上述过程。因此，这种手工方式的工作效率极低，且极易出现差错。根据财务数据总表，编制自动更新的、格式规范的费用发放单，不仅可以大大减轻财务人员的劳动强度，还可以有效地避免发生错误。本文分析研究几种格式的费用发放单的实现方法，并对实现方法进行了比较，以便财会人员或部门管理人员具体应用时选择。
一、问题描述
根据提供的格式统一的费用发放总表及费用发放单，各部门按照表的格式，编制部门费用发放总表及部门费用发放单并上报。计财处根据上报的费用发放总表发放现金到各部门，各部门根据费用发放单发放现金到个人。费用发放总表及费用发放单的格式如表1和表2所示。财务要求是，根据表1的费用发放总表数据，自动绘制生成表2形式的部门费用发放单，并且表2中的人民币大写形式也要能够自动生成。当表1的数据插入或删除时，表2的数据应自动变化。主要存在的问题有两个：一个是在表2的单元格中，不能采用公式“=[总表]！A4”等引用数据，因为在总表1中插入或删除行时，表2单元格的公式将产生形式为“#REF！”的出错信息；另一个是在表2中，如何将人民币小数形式的金额转换为大写形式。
二、解决方案
一是自动生成人民币大写形式。在Excel中，选定单元格，打开“单元格格式”对话框，将“数字”选项卡上“分类”中的自定义格式类型“G/通用格式”改为“[dbnum2]G/通用格式元”可显示大写金额，但格式并不完全符合会计金额格式(例如：壹拾贰.叁伍元)。如果自定义格式类型改为“[dbnum2]0角0分”，则数字“12”显示为“壹角贰分”也不正确。如果将小写数字的金额分为整数和小数两部分，采用函数TEXT按上述两种不同格式，分别进行转换，则可自动实现将人民币小写格式转换成大写金额格式，以A1单元格的金额为例，进行转换分析。
(1)整数部分的转换。第一步，将A1小写数字的金额，四舍五入到小数点右边的2位小数(分)；格式为：FIXED(A1)；第二步，将结果截掉小数部分，保留整数部分；格式为：TRUNC(FIXED(A1))；第三步，将非零的整数部分进行格式转换，按“[dbnum2]G/通用格式元；负[dbnum2]G/通用格式元；"”格式进行，负数前自动加入“负”字，格式如下：TEXT(TRUNC(FIXED(A1))"[dbnum2]G/通用格式元；负[dbnum2]G/通用格式元"；问题：如果整数部分为零，转换后将会出错，例如：-0.56和+0.56转换后都为“零元”，显然不正确，应按下一步方法进行解决。第四步，如果整数部分为零，正数则不填，负数则填充“负”字，当负数太小，小于-0.005时，也不填充，格式如下：TEXT(TRUNC(FIXED(A1))，"[dbnum2]G/通用格式元；负[dbnum2]G/通用格式元；"&IF(A1>-0.5％，，"负"))。
(2)小数部分的转换(以A1单元格的金额为例)。第一步，将A1小写数字的金额，四舍五入到小数点右边的2位小数(分)；格式为：FIXED(A1)；第二步，提取小写金额的两位小数部分；格式为：RIGHT(FIXED(A1)，2)；第三步，讲两位小数部分进行格式转换为角分；格式为：TEXT(RIGHT(FIXED(A1)，2)，"[dbnum2]0角0分；；")。问题1：数字0.06或1.06的小数部分转换结果均为“零角陆分”，不符合习惯。数字0.06的转换结果“零角陆分”，应去掉“零角”转换为“陆分”；数字1.06的转换结果“零角陆分”，应将“零角”转换为“零”，即“零陆分”。问题2：数字0.60转换的结果为“陆角零分”，不符合习惯。应将“陆角零分”的“零分”转换为“整”，即数字0.60转换为“陆角整”。问题3：如果金额中不含小数部分，或小数部分太小(小于0.005)，应加上“整”字。第四步，“零角”转换为“零”或舍弃如果金额的绝对值小于1，将字符串中的“零角”直接去掉，否则转换为“零”。=Substitute(Text(Right(Fixed(A1)，2)，"[dbnum2]0角0分；；")，"零角"，IF(Abs(A1)<1，，"零"))。第五步，"零分"转换为"整"格式为：=Substitute(Substitute(Text(Right(Fixed(A1)，2)，"[dbnum2]0角0分；；")，"零角"，IF(ABS(A1)<1，，"零"))，"零分"，"整")。第六步，处理“整”元，格式为：=TEXT(RIGHT(FIXED(A20)，2)，"[dbnum2]0角0分；；"&IF(ABS(A20)>1％，"整"))。
(3)整数与小数部分的处理结果相结合。例如：将单元格P98的人民币小数形式的金额转换为大写形式，可在目标单元格中填充如下的计算公式：=SUBSTITUTE(SUBSTITUTE(TEXT(TRUNC(FIXED(P98))，"[dbnum2]G/通用格式元；负[dbnum2]G/通用格式元；"&IF(P98>-0.5％，，"负"))&TEXT(RIGHT(FIXED(P98)，2)，"[dbnum2]0角0分；；"&IF(ABS(P98)>1％，"整"，))，"零角"，IF(ABS(P98)<1，，"零"))，"零分"，"整")
(4)公式中的函数简介。FIXED函数，用于将数字按指定的小数位数进行取整，并返回文本形式的结果，语法的格式如下：FIXED(Numbers,decimals,no_commas)。其中，参数Number为要进行四舍五入转换的数字值；参数Decimals用来指定小数点右边的小数位数，如果省略则默认2位小数；参数No_commas为逻辑值FALSE或省略，则返回的文本中包含逗号，为TRUE，则返回的文本中无逗号。例如：公式“=FIXED(1234.567)”的结果为1,230.57。TRUNC函数，用于将数字的小数部分截去，返回整数。函数TRUNC的格式如下：；TRUNC(number,num_digits)，其中，参数Number为需要截尾取整的数字，参数Num_digits为用于指定取整精度的数字(默认值0)。例如：公式“=TRUNC(-8.9)”的整数部分为-8。注意：类似的函数INT可将给定数的小数部分四舍五入到最接近的整数，含义不同。TEXT函数，将数值转换为按指定数字格式表示的文本，函数格式如下：TEXT(value，format_text)，其中，参数Value为数值、计算结果为数字值的公式，或对包含数字值的单元格的引用。Format_text为“单元格格式”对话框中，“数字”选项卡上，“分类”框中的文本形式的数字格式。RIGHT函数，函数RIGHT根据指定的字符数，返回文本字符串中最后一个或多个字符。函数的格式如下：RIGHT(text,num_chars)，其中，参数Text是包含要提取字符的文本字符串，参数Num_chars指定希望RIGHT提取的字符数，Num_chars必须大于或等于0。如果num_chars大于文本长度，则RIGHT返回所有文本，如果忽略num_chars，则假定其为1。SUBSTITUTE函数，用文本替换某一文本字符串中的指定文本，函数的格式如下：SUBSTITUTE(text,old_text,new text,instance num)。其中，参数Text为文本字符串或含有文本字符串的单元格的引用；参数Old_text为需要替换的旧文本；参数New_text用于替换old_text的新文本。数值参数Instance_num指定替换方式，省略将用new_text替换Text中出现的所有old_text。IF函数，可以使用函数IF对数值和公式进行逻辑条件检测，根据计算的真假值，返回不同结果。例如：=IF(A3>60，"及格"，"不及格")，假定单元格A3中的值为数值75，表达式A3>60为TRUE，函数的结果为“及格”，否则为“不及格”。ABS函数，使用函数ABS(number)计算number的绝对值。例如，“=abs(-12.3)”计算结果为12.3。
二是总表插删数据行时，自动更新子表数据。如果表2的A8单元格数据来源于表1“汇总表”的A8单元格，可在表2的A8单元格中使用公式"=INDIRECT("汇总表！A8")"。当“汇总表”的A8以上的数据行插删时，表2的A8单元格的公式可不变。完整的公式如下：=IF(INDIRECT("汇总表！A8")=0,"",INDIRECT("汇总表！A8"))。其中，函数INDIRECT返回由文本字符串指定的引用。此函数立即对引用进行计算，并显示其内容。当更改公式中单元格的引用时，无需更改公式本身。函数的语法如下：INDIRECT(ref_text,al)，参数Ref_text为对单元格的引用，此单元格可以包含A1样式的引用、R1C1样式的引用、定义为引用的名称或对文本字符串单元格的引用。参数A1为一逻辑值，指明包含在单元格ref_text中的引用的类型。如果a1为TRUE或省略，ref_text被解释为A1样式的引用。如果a1为FALSE，ref_text被解释为R1C1样式的引用。
三、具体操作步骤
第一步，建立工作簿文件，在工作簿内建立“汇总表”、“费用发放表”2张工作表。“汇总表”存放基础数据，“费用发放表”可以有多种固定格式，用于打印输出，例如单栏单行(如工资发放单)、单栏多行、双栏多行等形式。“费用发放表”的数据来源于“汇总表”，由于部门“汇总表”的最多行数一般根据部门人员数量确定，可先将表样做好，数据初始化为空。第二步，对于双栏多行的费用发放子表，为了便于修改和维护数据，从M列到T列，建立地址对应表，确定子表各单元格的数据来源(例如，子表A5的数据来源于M5提供的总表A4单元格数据)，如图1所示。第三步，在子表的A5∶D8，F5∶I8等区域中填入公式。例如，A5单元格的公式为：=IF(INDIRECT("汇总表！"&M5)=0,"",INDIRECT("汇总表！"&M5))。第四步，在合计I9、I20等单元格填写公式，统计子表的总金额，以I5为例，公式如下：=SUM(D5∶D8,I5∶I8)。第五步，在C9(C20)等单元格中，建立公式，将I9(I20)的小写金额转换为大写：=SUBSTITUTE(SUBSTITUTE(TEXT(TRUNC(FIXED(I9))，"[dbnum2]G/通用格式元；负[dbnum2]G/通用格式元；"&IF(I9>-0.5％,,"负"))&TEXT(RIGHT(FIXED(I9)，2)，"[dbnum2]0角0分；；"&IF(ABS(I9)>1％，"整"，))，"零角"，IF(ABS(I9)<1，，"零"))，"零分"，"整")。第六步，用公式填充其他单元格，以实现填表日期、制表人等数据的自动填写。注意，本实例是以格式固定的子表为研究对象实现，如果打印的格式为单栏、行数允许变化，则无需制作子表，只需利用总表进行筛选，在汇总单元格中使用函数SUBTOTAL，将第一个参数设为109，可实现不包括隐藏区域的数据统计功能。
图1 子表与总表的地址对应关系示意图
四、测试效果
将费用发放子表的A列到K列设为打印区域，在“汇总表中”增加职工或删除职工数据，子表无需作任何修改，可实现数据的自动更新。至此，实现了用户的需求，并已在实践中大量运用，显著提高了工作效率和工作质量。
五、结论
本文利用Excel的公式和函数功能，用简单的方式实现了随总表增删而自动变化的子表编制功能。该方法对用户的要求低，财会人员或部门管理人员可根据工作实际情况，按照具体子表的格式要求，进一步修改公式，以便灵活运用。
标签：text函数论文; 文本分类论文; 形式参数论文; 数字转换论文; 单元格引用论文; 文本分析论文; excel公式论文;