《报表公式定义》PPT课件.ppt

报表公式定义,4.3,报表公式分为三类。 第一类是报表计算公式(也称单元公式)，它是反映报表单元数据来源的表达式； 第二类是审核公式，它是判断报表编制是否正确的表达式； 第三类是舍位平衡公式，它是确保报表在舍位后有关项目之间的平衡关系能够得以维持的表达式。,一、计算公式定义,(一)取数公式概述 报表基本格式设置完毕之后，就需要对报表上各项目的取数规则进行定义。 由于报表上绝大部分项目的数据都来源于账簿，因此我们把这种数据来源的表达式称为账务取数函数； 有些报表项目的数据来源于报表本身或其他报表的项目，我们把这种数据来源表达式称为报表取数函数。,1.账务取数函数,账务取数函数表示报表数据来源于账簿，它要解决以下方面的问题： 取哪一个账套的数据。 取哪一年、哪一个月的数据。 取哪一个账户的数据 取这个账户的什么数据(一个账户可以提供期初、期末、发生额、累计发生额等数据)。,账务取数函数的基本格式为：,函数名(“科目编码”，会计期间，“方向”，“账套号”，会计年度，“编码1”，“编码2”)。,函数说明,函数内部带中括号的项目为可选项目，可采用缺省方式。而科目编码或会计期间则是必选项目。,函数名:,即函数关键字,账务取数函数主要函数名，如表4-3所示。,科目编码：,表示取哪一个科目的数据。,会计期间：,表示取某一年哪一个月的数据，可以是全年、季、月等变量，也可以用具体数字表示年、季、月。,方向：,即借或贷，可以省略。,账套号：,表示取哪一套账的数据，为数字型字符串，缺省时默认为第一套账(默认的账套号可通过“数据”菜单下的“账套初始”程序设置)。,会计年度：,即数据取数的年度，可以省略，省略时取默认的会计年度，该值也可通过上述“账套初始”程序设置。,编码1与编码2：,与科目编码的核算账类有关，可以取科目的辅助账，如无辅助核算可以不填。,2.报表取数函数,报表取数函数是指数据来源于报表某一单元、某一行或某一列的函数。 按照数据来源，它又可以分为取某一单元的函数、取某一行的函数、取一列的函数、取本表它页的函数、到其他表取数的函数。,取某一单元的函数,取某一单元的函数是指数据来源于本表某一单元的数值。这种函数没有特殊的函数名，只需要输入数据所在单元的名称即可。 D6单元的值等于A8单元的值，则在“数据”菜单中指向“编辑公式”，然后单击其下拉项“单元公式”，直接输入“A8”即可。,取某一行的函数,取某一行的函数是指反映报表中某一行的值等于本表中另一行的数据的函数。这种函数没有特殊的函数名，只需要输入数据所在行的名称即可。 某表中第六行(C6:H6)的数据等于本表中第十四行(C14:H14)的值，则在输入运算公式时，可以首先选中C6:H6区域，按“=”号后直接输入“C14:H14”即可。,取某一列的函数,取某一列的函数是指反映报表中某一列的值等于本表中另一列的数据的函数。这种函数没有特殊的函数名，只需要输入数据所在列的名称即可。 某表中第六列的数据等于本表中第八列的值，则在“数据状态”下指向“文件”菜单，然后单击其下拉项“命令窗”，输入“LET F=H”即可，回车后计算机自动编制报表。 前文“取某一行的函数”，也可以在“命令窗”中输入，表达方法为“LET #6=#14”。,取本表它页的函数,取本表它页的函数是指反映数据来源于本表其他月份的数据的函数。 例如二月份利润表中的本年累计数应根据一月份利润表中的本年累计数与二月份报表中的本月数合计填列，这时就可以用取本表它页的函数设置运算公式。 这类公式有二种，一种是运用关联条件取数，另一种是运用选择函数取数。,运用关联条件取数。,从本表他页取数的关联条件的格式为： RELATIONWITH “” WITH前的参数表示目标表页的特征，WITH后的参数表示数据源所在表页的特征，关联条件的意义在于目标页与数据源所在的表页使WITH前后的参数相等。,这些参数中比较常用的参数为“年”、“月”， 如利润表中的本年累计数应根据一月份利润表中的本年累计数与二月份报表中的本月数合计填列，这里的关联参数就是“月”，表达如下： RELATION 月 WITH “利润表”月1 假如当前表页是2月，即左边的参数“月”为2，WITH右边的参数“月”的值为1时才能使WITH前后相等，这就形成一个关联，达到了取上月表页数据的目的。 以此类推，取3个月前的数据就应该描述为：RELATION 月 WITH “利润表”月3。,注意：,所有引用本表他页相同地址单元的计算公式只能在命令窗和批命令中使用，不能在单元公式中使用。因此利润表中的本年累计数不能用关联条件函数。 当报表中有两个或更多的关键字，且用一个关键字不能唯一表示一个表页时，需要用两个关键字进行关联。,例如当前页关键字为2006年5月，需要取2005年4月的值，就不能用“RELATION 月 WITH 月1”这样的关联关系了，这时，我们可以使用两个关联条件。 下面的计算公式令表“ABC”本页A列取本表上年上月A列数值。 LET A=“ABC” A RELATION 年 WITH “ABC” 年1，月 WITH “ABC”月1 注意： 使用关联条件取本表他页数据时，等号右边数据源区域必须有当前表表名。,运用选择函数取数,选择函数是指取数时计算机将自动搜寻满足条件的数据。其基本格式为SELECT()，括号内为取数的条件。 例如，“ABC”报表中D列的值为本年上月本表C列的数据，则运算公式可以表达为： D=SELECT(C,年年 and 月月) SELECT()函数常用在损益表中求累计值。,用关联条件从他表取数,当从他表取数时，已知条件并不是页号，而是希望按照年、月、日等关键字的对应关系来取他表数据，这时就必须用到关联条件。 表页关联条件的意义是建立本表与他表之间以关键字或某个单元为联系的默契关系。 从他表取数的关联条件的格式为： RELATIONWITH “”,具体用法我们以例子说明。 A1=“FYB”A1 RELATION 月 WITH “FYB” 月 取FYB表的，与当前表页月相同月的A1单元的值。 如果当前表页为9月，则取FYB表9月表页A1的值。,下面单元公式令本表各页A列取表“LRB”上月各页B列数值。 A=“LRB”B RELATION 月 WITH “LRB” 月1,下面公式令当前各页A列取表“LRB”同年上月表页B例数值及表“ZJB”上一年相同月份表页C列数值之和。 A=“LRB”B“ZJB” C RELATION 年 WITH “LRB” 年，月 WITH “LRB” 月1，年 WITH “ZJB” 年1，月 WITH “ZJB” 月,3凭证取数函数,凭证取数函数是指到记账凭证中搜寻数据的函数。 在编制对内会计报表时，表上的有些数据无法从账簿中取得，只能从记账凭证中取数。 例如，本年度管理费用中用货币资金支付的费用就需要从凭证中取得。,凭证取数函数的基本格式为：,函数名(，,函数说明,函数名，函数名有三种：金额式的函数名为“对方科目发生”或“DFS”；数量式的函数名为“数量对方科目发生”或“sDFS”；外币式的函数名为“外币对方科目发生”或“wDFS”。 方向，合法的方向为“借”、“贷”、“j”“d”。,摘要匹配方式，摘要匹配方式是指取数时，摘要内容的精确程度。模糊匹配用“”，精确匹配用“”。模糊匹配是指经济业务中的摘要内容包含函数中的摘要内容。精确匹配是指经济业务中的摘要内容与函数中的摘要内容完全一致。 DFS(“550201”,“1001”,2,“借”,“001”,2006)的含义就是返回001账套“550201”科目2006年2月份且其对方科目为“1001”的业务。 DFS(“550201”，2，“1001”，“借”，“部门一报订阅资料”，“=”，“001”，2006)的含义就是返回001套账“550201”科目2006年2月份的发生业务中，摘要等于“部门一报订阅资料”且其对方科目为“1001”的业务。,4.运算公式的输入,在UFO中，运算公式的输入渠道主要是二种。 第一种是在格式状态下输入，有三种方式启动“定义分式”对话框：选择“数据”菜单中的“编辑公式”项，点击fx按钮，在需编辑公式的单元格按“”键； 第二种是在数据状态下输入：选择“文件”菜单中的“命令窗”项。在“命令窗”输入时，每一条公式的前面应加“LET”，例如：LET D=B，本公式的含义为本表中第列的值等于第列相对应的单元的数据。,如果采用第一种方式输入公式，则数据在输入计算机时，计算机将自动编制报表；如果采用第二种方式输入公式，计算机不能完全自动编制报表，需要打入回车键才能编制报表。,函数使用要点：,1.函数中的第一个时间是指报表中的数据来源于某一年的某个期间，如月或季度。 其表达方法有两种： 一种是直接以“月”或“季”表示 如qm(4010201,月,001,年,)，这时公式中的月份将随着关键字中的月份的变化而变化，从而确保不同月份的报表有不同的数据。 资产负债表期末数栏和损益表运算公式中的“时间1”一般用“月”表示；,资产负债表年初数栏运算公式中的“时间1”一般用“全年”表示(表示全年的期初数)，如QC(101,全年,001,年,)即表示取年初的现金账余额。 另一种表示方法是用阿拉伯数字1-12表示 如QC(101,2,001,年,)即表示取2月初的现金账余额。这种公式的特点在于报表固定地取某一个月的数据，关键字中月份的变化对本公式没有影响。因此资产负债表年初数栏运算公式中的“时间1”也可以用“1”表示。,在实际工作中，报表各项目运算公式中的“时间1”一般用“月”表示比较多。函数中的第二个时间是指年份。其表达方法也有两种，一种是“年”，采用这种表达方法时，报表数据所属年份将随着关键字中年份的变化而变化； 另一种是直接输入代表年份的数字，如2005。采用这种表达方法时，报表数据所属年份是固定的，并不随着关键字中年份的变化而变化。,2.关于运算公式中的方向问题。,账务取数函数对方向的设置有两类 对余额函数有三种：默认、借、贷； 对发生额函数有两种：借、贷。 对余额函数而言，方向采用“默认”(即省略)时，表示取该账户的期末余额；方向采用“借”时，表示取该科目所属明细账的借方余额；方向采用“贷”时，表示取该科目所属明细账的贷方余额。这里的“借”或“贷”也可以用“J”或“D”代替。,例子：,QM(113,月,001,年,)：表示取应收账款的月末余额，其值为40000。 QM(113,月,J，001,年,)：表示取应收账款所属明细账中余额方向为借方的月末余额，其值为70000。 QM(113,月,D，001,年,)：表示取应收账款所属明细账中余额方向为贷方的月末余额，其值为30000。,当所属明细账的余额方向都一致时，期初期末函数中的方向取“默认”或“借”、“贷”，其结果是相同的，否则就不一样。 根据企业会计制度的规定，资产负债表中的应收账款、预付账款、应付账款、预收账款、待摊费用、预提费用等项目要根据明细账的余额分析填列，因此上述项目的运算公式中都要加上方向“借”或“贷”，其他项目的运算公式的方向可以采用“默认”。但是在运用发生额函数时必须输入方向“借”或“贷”。,3.输入单元公式时，可以直接输入，也可以采用引导式输入。,直接输入是指“选取需要定义公式的单元，按“=”即可定义，或者在“数据”菜单中指向“编辑公式”，然后单击其下拉项“单元公式”，直接输入相应公式”； 引导式输入是指根据报表系统的提示相应输入报表计算公式，在定义公式对话框中点击函数向导按钮，进入函数向导对话框，如图4-13所示。,选择适用的函数并点击下一步进入下一函数向导界面，例如选择期初余额函数进入账务函数对话框，如图4-14所示，在相应栏目进行选择后点击确定按钮即可将公式返回到定义公式对话框中。 需要注意的是，如果在设置报表公式时，只要将图4-14中的“包含未记账凭证”勾上，就可以将未记账的数据取数到报表处理系统中，真正实现报表数据的实时反映。,4.对方科目发生额函数又称凭证取数函数，是指将某一类记账凭证的发生额汇总后反映在报表项目中。 这里需要注意的是，这里所指的凭证必须是一借一贷的记账凭证，多借多贷的凭证不能进行取数。,5.账务取数函数中的账套号和年份都可以省略，也可以直接将账套号和年份输入在报表计算公式中。如果采用省略方法，在编制报表时就必须通过“数据”菜单下的“账套初始”程序设置相应的账套号和年份，否则编制报表时可能出错。 在实际工作中一般不把账套号和年份直接输入在报表计算公式中，否则过了这一年或换了账套，该报表计算公式就需要进行修改。 对于套用系统预置的报表模板生成的报表格式，虽然有的报表模板本身已经设置定义了有关报表项目的单元公式，但是这些单元的计算公式并不一定正确，需要用户进行必要的检查。,(二)资产负债表主要取数公式,资产负债表是反映企业在某一特定日期财务状况的会计报表。 本表通过提供“年初数”和“年末数”两组数据，可以反映本年度发生的经济业务对资产、负债、所有者权益的影响结果。,年初数运算公式中“会计期间”有两种表达方法， 以货币资金为例： 第一种：QC(1001,全年,年,)+QC(1002,全年,年,)+QC(1009,全年,年,)，本公式的含义是取全年“现金”账户、“银行存款”账户、“其他货币资金”账户的期初数，即取本年度1月初的数据。 第二种：QC(1001,1,年,)+QC(1002,1,年,)+QC(1009,1,年,)，本公式的含义是取本年1月份“现金”账户、“银行存款”账户、“其他货币资金”账户的期初数。,年末数运算公式的“会计期间”只有一种表示方法，即以“月”表示。 仍以“货币资金”项目为例，期末数的运算公式为：QM(1001,月,年,)+QM(1002,月,年,)+QM(1009,月,年,)。,表4-5：资产负债表部分项目期末数运算公式,为了便于计算机编制报表，各单位最好在“长期债权投资”、“长期借款”等科目下面设置“一年内到期的长期债权投资”和“一年内到期的长期负债”二级科目，月末对有关的投资业务和借款业务进行分析，将满足条件的项目转入“一年内到期的长期债权投资”或“一年内到期的长期负债”科目，在这样的情况下，报表中的“长期债权投资”、“长期借款”等项目的运算公式就不能根据有关账户的总账余额来取数，而应该根据有关账户的总账余额减去有关明细科目余额后的差额确定。,举例说明如下： 假定“一年内到期的长期债权投资”的科目代码为140299，则报表中“长期债权投资”项目的运算公式为QM(1402,月)-QM(140299,月)。,(三)利润表主要取数公式,利润表是反映企业一定会计期间经营成果的报表，该表是按照该期间各项收入、费用以及构成利润的各个项目分类分项编制而成的，因此本表所提供的数据都是本期收入或费用的发生额。 利润表“本月数”栏的“主营业务收入”和“主营业务成本”运算公式可设置如下： 1.主营业务收入：DFS(“5101”,“3131”,月,“借”,“001”,年) 2.主营业务成本：DFS(“5102”，“3131”,月,“贷”,“001”,年),利润表“本月数”栏其他项目的运算公式可以依此类推， “本年累计数”栏的运算公式采用本表它页取数函数，假定“主营业务收入”栏为第6行，“净利润”栏为第21行，“本年累计”为第四列，则D6:D21区域的运算公式可以作如下表达： D6:D21=C6:C21+SELECT(D6:D21,月=月+1) 需要提醒的是，“本月数”栏采用对方科目取数函数(也称凭证取数函数)时，结转收入、费用的凭证只能是一借一贷的凭证，不能多借多贷或一借多贷和多借一贷。,二、审核公式定义,目前，国内财务软件公司开发的报表系统都提供了数据审核功能，用户只要将报表上的勾稽关系用公式表达出来(在会计电算化中称为审核公式)，计算机就可以根据审核公式自动判断报表编制的正确性。 审核公式应该在格式状态下输入。,现以资产负债表为例，说明审核公式的设置问题。假定资产期末数的单元为D39，负债及所有者权益期末合计数的单元为H39。在格式状态下单击“数据”菜单中的“编辑公式/审核公式”，系统弹出审核公式编辑窗口，如图4-15所示。,在公式编辑框中输入：“D39=H39 MESS 期末资产总计不等于期末负债及所有者权益总计!”，点击确定按钮即完成D39单元格审核公式定义。 公式的含义是：报表D39单元的数据必须等于H39单元的数据，否则，在屏幕上显示“期末资产总计不等于期末负债及所有者权益总计”的提示信息。,三、报表舍位平衡公式定义,目前我国对外报表中的金额单位都是“元”。因此在上规模的单位，会计报表上的数据都比较大，阅读报表时就显得比较麻烦，故常常希望将报表中的数据金额单位由“元”改为“千元”或“万元”，以方便阅读。 这时报表上原来的数据平衡关系就可能被破坏，因此需要重新进行调整，使之保持数据间的平衡关系。,例如：原始报表数据平衡关系为D5+E5=F5，在未转换之前，D5的值为15237.23，E5的值为1543.31，F5的值为16780.54，此时该表页是符合平衡关系的。但在转换为“万元”表之后，D5的值变为1.52，E5的值变为0.15，F5的值变为1.68，这时原先的平衡关系被打破，即1.52+0.15=1.671.68。 报表经舍位后，重新调整平衡关系的公式称为舍位平衡公式。其中，进行进位的操作叫做舍位，舍位后调整平衡关系的操作叫做平衡调整。当报表数据生成之后，应对报表进行审核，以检查报表各项数据勾稽关系的准确性。,在报表处于格式设计状态时，调用“数据”菜单下的“编辑公式/舍位公式”功能进入到“舍位平衡公式”对话窗口，如图4-16所示。,其中， 舍位表名是舍位后报表的名称，和当前报表文件名不能相同，存放地点默认在当前目录下； 舍位范围，是指需要进行舍位的区域；舍位位数可输入1-8位，舍位位数为1时，区域中的数据除以10；舍位位数为2，区域中的数据除以100，依此类推；,平衡公式，是指舍位后有关项目间的平衡关系，按如下规则书写： 倒顺序写，首写写最终运算结果，然后一步一步向前推； 每个公式一行，各公式之间用逗号隔开，最后一条公式后不用写逗号； 公式中只能使用“+”或“-”符号，不能使用其他运算符号及函数； 等号左边只能为一个单元(不带页号和表名)； 一个单元只允许在等号右边出现一次。 舍位平衡公式编辑完毕，检查无误后单击完成按钮，系统将保存此次舍位平衡公式的设置。按“ESC”键或选择取消将放弃此次操作。,