报表公式定义

报表公式定义第1页，共60页，2023年，2月20日，星期五报表公式分为三类。第一类是报表计算公式(也称单元公式)，它是反映报表单元数据来源的表达式；第二类是审核公式，它是判断报表编制是否正确的表达式；第三类是舍位平衡公式，它是确保报表在舍位后有关项目之间的平衡关系能够得以维持的表达式。第2页，共60页，2023年，2月20日，星期五一、计算公式定义(一)取数公式概述报表基本格式设置完毕之后，就需要对报表上各项目的取数规则进行定义。由于报表上绝大部分项目的数据都来源于账簿，因此我们把这种数据来源的表达式称为账务取数函数；有些报表项目的数据来源于报表本身或其他报表的项目，我们把这种数据来源表达式称为报表取数函数。第3页，共60页，2023年，2月20日，星期五1.账务取数函数账务取数函数表示报表数据来源于账簿，它要解决以下方面的问题：⑴取哪一个账套的数据。⑵取哪一年、哪一个月的数据。⑶取哪一个账户的数据⑷取这个账户的什么数据(一个账户可以提供期初、期末、发生额、累计发生额等数据)。第4页，共60页，2023年，2月20日，星期五账务取数函数的基本格式为：函数名(“科目编码”，会计期间，[“方向”]，[“账套号”]，[会计年度]，[“编码1”]，[“编码2”])。第5页，共60页，2023年，2月20日，星期五函数说明函数内部带中括号的项目为可选项目，可采用缺省方式。而科目编码或会计期间则是必选项目。第6页，共60页，2023年，2月20日，星期五⑴函数名:即函数关键字,账务取数函数主要函数名，如表4-3所示。第7页，共60页，2023年，2月20日，星期五函数名称表示方法金额式数量式外币式期初余额函数QCsQCwQC期末余额函数QMsQMwQM发生额函数FSsFSwFS累计发生额函数LFSsLFSwLFS条件发生额函数TFSsTFSwTFS对方科目发生额函数DFSsDFSwDFS净额函数JEsJEwJE第8页，共60页，2023年，2月20日，星期五⑵科目编码：表示取哪一个科目的数据。第9页，共60页，2023年，2月20日，星期五⑶会计期间：表示取某一年哪一个月的数据，可以是全年、季、月等变量，也可以用具体数字表示年、季、月。第10页，共60页，2023年，2月20日，星期五⑷方向：即借或贷，可以省略。第11页，共60页，2023年，2月20日，星期五⑸账套号：表示取哪一套账的数据，为数字型字符串，缺省时默认为第一套账(默认的账套号可通过“数据”菜单下的“账套初始”程序设置)。第12页，共60页，2023年，2月20日，星期五⑹会计年度：即数据取数的年度，可以省略，省略时取默认的会计年度，该值也可通过上述“账套初始”程序设置。第13页，共60页，2023年，2月20日，星期五⑺编码1与编码2：与科目编码的核算账类有关，可以取科目的辅助账，如无辅助核算可以不填。第14页，共60页，2023年，2月20日，星期五2.报表取数函数报表取数函数是指数据来源于报表某一单元、某一行或某一列的函数。按照数据来源，它又可以分为取某一单元的函数、取某一行的函数、取一列的函数、取本表它页的函数、到其他表取数的函数。第15页，共60页，2023年，2月20日，星期五⑴取某一单元的函数取某一单元的函数是指数据来源于本表某一单元的数值。这种函数没有特殊的函数名，只需要输入数据所在单元的名称即可。D6单元的值等于A8单元的值，则在“数据”菜单中指向“编辑公式”，然后单击其下拉项“单元公式”，直接输入“A8”即可。第16页，共60页，2023年，2月20日，星期五⑵取某一行的函数取某一行的函数是指反映报表中某一行的值等于本表中另一行的数据的函数。这种函数没有特殊的函数名，只需要输入数据所在行的名称即可。某表中第六行(C6:H6)的数据等于本表中第十四行(C14:H14)的值，则在输入运算公式时，可以首先选中C6:H6区域，按“=”号后直接输入“C14:H14”即可。第17页，共60页，2023年，2月20日，星期五⑶取某一列的函数取某一列的函数是指反映报表中某一列的值等于本表中另一列的数据的函数。这种函数没有特殊的函数名，只需要输入数据所在列的名称即可。某表中第六列的数据等于本表中第八列的值，则在“数据状态”下指向“文件”菜单，然后单击其下拉项“命令窗”，输入“LET

F=H”即可，回车后计算机自动编制报表。前文“取某一行的函数”，也可以在“命令窗”中输入，表达方法为“LET

#6=#14”。第18页，共60页，2023年，2月20日，星期五⑷取本表它页的函数取本表它页的函数是指反映数据来源于本表其他月份的数据的函数。例如二月份利润表中的本年累计数应根据一月份利润表中的本年累计数与二月份报表中的本月数合计填列，这时就可以用取本表它页的函数设置运算公式。这类公式有二种，一种是运用关联条件取数，另一种是运用选择函数取数。第19页，共60页，2023年，2月20日，星期五①运用关联条件取数。从本表他页取数的关联条件的格式为：RELATION<单元｜关键字｜变量｜常量>WITH“<当前表表名>”—><单元｜关键字｜变量｜常量>WITH前的参数<单元｜关键字｜变量｜常量>表示目标表页的特征，WITH后的参数<单元｜关键字｜变量｜常量>表示数据源所在表页的特征，关联条件的意义在于目标页与数据源所在的表页使WITH前后的参数相等。第20页，共60页，2023年，2月20日，星期五这些参数中比较常用的参数为“年”、“月”，如利润表中的本年累计数应根据一月份利润表中的本年累计数与二月份报表中的本月数合计填列，这里的关联参数就是“月”，表达如下：RELATION月WITH“利润表”—>月＋1假如当前表页是2月，即左边的参数“月”为2，WITH右边的参数“月”的值为1时才能使WITH前后相等，这就形成一个关联，达到了取上月表页数据的目的。以此类推，取3个月前的数据就应该描述为：RELATION月WITH“利润表”—>月＋3。第21页，共60页，2023年，2月20日，星期五注意：所有引用本表他页相同地址单元的计算公式只能在命令窗和批命令中使用，不能在单元公式中使用。因此利润表中的本年累计数不能用关联条件函数。当报表中有两个或更多的关键字，且用一个关键字不能唯一表示一个表页时，需要用两个关键字进行关联。第22页，共60页，2023年，2月20日，星期五例如当前页关键字为2006年5月，需要取2005年4月的值，就不能用“RELATION月WITH月＋1”这样的关联关系了，这时，我们可以使用两个关联条件。下面的计算公式令表“ABC”本页A列取本表上年上月A列数值。LET

A=“ABC”

—>A

RELATION年WITH“ABC”

—>年＋1，月WITH“ABC”—>月＋1注意：使用关联条件取本表他页数据时，等号右边数据源区域必须有当前表表名。第23页，共60页，2023年，2月20日，星期五②运用选择函数取数选择函数是指取数时计算机将自动搜寻满足条件的数据。其基本格式为SELECT()，括号内为取数的条件。例如，“ABC”报表中D列的值为本年上月本表C列的数据，则运算公式可以表达为：D=SELECT(C,年＠＝年and月＠＝月＋１)SELECT()函数常用在损益表中求累计值。第24页，共60页，2023年，2月20日，星期五⑸用关联条件从他表取数当从他表取数时，已知条件并不是页号，而是希望按照年、月、日等关键字的对应关系来取他表数据，这时就必须用到关联条件。表页关联条件的意义是建立本表与他表之间以关键字或某个单元为联系的默契关系。从他表取数的关联条件的格式为：RELATION<单元｜关键字｜变量｜常量>WITH“<他表表名>”—><单元｜关键字｜变量｜常量>第25页，共60页，2023年，2月20日，星期五具体用法我们以例子说明。①A1=“FYB”—>A1

RELATION月WITH“FYB”

—>月取FYB表的，与当前表页月相同月的A1单元的值。如果当前表页为9月，则取FYB表9月表页A1的值。第26页，共60页，2023年，2月20日，星期五②下面单元公式令本表各页A列取表“LRB”上月各页B列数值。A=“LRB”—>B

RELATION月WITH“LRB”

—>月＋1第27页，共60页，2023年，2月20日，星期五③下面公式令当前各页A列取表“LRB”同年上月表页B例数值及表“ZJB”上一年相同月份表页C列数值之和。A=“LRB”—>B＋“ZJB”

—>C

RELATION年WITH“LRB”

—>年，月WITH“LRB”

—>月＋1，年WITH“ZJB”

—>年＋1，月WITH“ZJB”

—>月第28页，共60页，2023年，2月20日，星期五3．凭证取数函数凭证取数函数是指到记账凭证中搜寻数据的函数。在编制对内会计报表时，表上的有些数据无法从账簿中取得，只能从记账凭证中取数。例如，本年度管理费用中用货币资金支付的费用就需要从凭证中取得。第29页，共60页，2023年，2月20日，星期五凭证取数函数的基本格式为：函数名(<科目编码>，<对方科目编码>，<会计期间>，<方向>，〔<摘要>〕，〔<摘要匹配方式>〕，〔<账套号>〕，〔<会计年度>〕，〔<编码１>〕，〔<编码２>〕第30页，共60页，2023年，2月20日，星期五函数说明⑴函数名，函数名有三种：金额式的函数名为“对方科目发生”或“DFS”；数量式的函数名为“数量对方科目发生”或“sDFS”；外币式的函数名为“外币对方科目发生”或“wDFS”。⑵方向，合法的方向为“借”、“贷”、“j”“d”。第31页，共60页，2023年，2月20日，星期五⑶摘要匹配方式，摘要匹配方式是指取数时，摘要内容的精确程度。模糊匹配用“＝”，精确匹配用“＝＝”。模糊匹配是指经济业务中的摘要内容包含函数中的摘要内容。精确匹配是指经济业务中的摘要内容与函数中的摘要内容完全一致。①DFS(“550201”,“1001”,2,“借”,,,“001”,2006)的含义就是返回001账套“550201”科目2006年2月份且其对方科目为“1001”的业务。②DFS(“550201”，2，“1001”，“借”，“部门一报订阅资料”，“==”，“001”，2006)的含义就是返回001套账“550201”科目2006年2月份的发生业务中，摘要等于“部门一报订阅资料”且其对方科目为“1001”的业务。第32页，共60页，2023年，2月20日，星期五4.运算公式的输入在UFO中，运算公式的输入渠道主要是二种。第一种是在格式状态下输入，有三种方式启动“定义分式”对话框：选择“数据”菜单中的“编辑公式”项，点击〖fx〗按钮，在需编辑公式的单元格按“＝”键；第二种是在数据状态下输入：选择“文件”菜单中的“命令窗”项。在“命令窗”输入时，每一条公式的前面应加“LET”，例如：LET

D=B，本公式的含义为本表中第４列的值等于第２列相对应的单元的数据。第33页，共60页，2023年，2月20日，星期五如果采用第一种方式输入公式，则数据在输入计算机时，计算机将自动编制报表；如果采用第二种方式输入公式，计算机不能完全自动编制报表，需要打入回车键才能编制报表。第34页，共60页，2023年，2月20日，星期五函数使用要点：1.函数中的第一个时间是指报表中的数据来源于某一年的某个期间，如月或季度。其表达方法有两种：一种是直接以“月”或“季”表示如qm(4010201,月,001,年,,)，这时公式中的月份将随着关键字中的月份的变化而变化，从而确保不同月份的报表有不同的数据。资产负债表期末数栏和损益表运算公式中的“时间1”一般用“月”表示；第35页，共60页，2023年，2月20日，星期五资产负债表年初数栏运算公式中的“时间1”一般用“全年”表示(表示全年的期初数)，如QC(101,全年,001,年,,)即表示取年初的现金账余额。另一种表示方法是用阿拉伯数字1-12表示如QC(101,2,001,年,,)即表示取2月初的现金账余额。这种公式的特点在于报表固定地取某一个月的数据，关键字中月份的变化对本公式没有影响。因此资产负债表年初数栏运算公式中的“时间1”也可以用“1”表示。第36页，共60页，2023年，2月20日，星期五在实际工作中，报表各项目运算公式中的“时间1”一般用“月”表示比较多。函数中的第二个时间是指年份。其表达方法也有两种，一种是“年”，采用这种表达方法时，报表数据所属年份将随着关键字中年份的变化而变化；另一种是直接输入代表年份的数字，如2005。采用这种表达方法时，报表数据所属年份是固定的，并不随着关键字中年份的变化而变化。第37页，共60页，2023年，2月20日，星期五2.关于运算公式中的方向问题。账务取数函数对方向的设置有两类对余额函数有三种：默认、借、贷；对发生额函数有两种：借、贷。对余额函数而言，方向采用“默认”(即省略)时，表示取该账户的期末余额；方向采用“借”时，表示取该科目所属明细账的借方余额；方向采用“贷”时，表示取该科目所属明细账的贷方余额。这里的“借”或“贷”也可以用“J”或“D”代替。第38页，共60页，2023年，2月20日，星期五例子：QM(113,月,001,年,,)：表示取应收账款的月末余额，其值为40000。QM(113,月,J，001,年,,)：表示取应收账款所属明细账中余额方向为借方的月末余额，其值为70000。QM(113,月,D，001,年,,)：表示取应收账款所属明细账中余额方向为贷方的月末余额，其值为30000。第39页，共60页，2023年，2月20日，星期五当所属明细账的余额方向都一致时，期初期末函数中的方向取“默认”或“借”、“贷”，其结果是相同的，否则就不一样。根据《企业会计制度》的规定，资产负债表中的应收账款、预付账款、应付账款、预收账款、待摊费用、预提费用等项目要根据明细账的余额分析填列，因此上述项目的运算公式中都要加上方向“借”或“贷”，其他项目的运算公式的方向可以采用“默认”。但是在运用发生额函数时必须输入方向“借”或“贷”。第40页，共60页，2023年，2月20日，星期五3.输入单元公式时，可以直接输入，也可以采用引导式输入。直接输入是指“选取需要定义公式的单元，按“=”即可定义，或者在“数据”菜单中指向“编辑公式”，然后单击其下拉项“单元公式”，直接输入相应公式”；引导式输入是指根据报表系统的提示相应输入报表计算公式，在定义公式对话框中点击〖函数向导〗按钮，进入函数向导对话框，如图4-13所示。第41页，共60页，2023年，2月20日，星期五选择适用的函数并点击〖下一步〗进入下一函数向导界面，例如选择期初余额函数进入账务函数对话框，如图4-14所示，在相应栏目进行选择后点击〖确定〗按钮即可将公式返回到定义公式对话框中。需要注意的是，如果在设置报表公式时，只要将图4-14中的“包含未记账凭证”勾上，就可以将未记账的数据取数到报表处理系统中，真正实现报表数据的实时反映。第42页，共60页，2023年，2月20日，星期五4.对方科目发生额函数又称凭证取数函数，是指将某一类记账凭证的发生额汇总后反映在报表项目中。这里需要注意的是，这里所指的凭证必须是一借一贷的记账凭证，多借多贷的凭证不能进行取数。第43页，共60页，2023年，2月20日，星期五5.账务取数函数中的账套号和年份都可以省略，也可以直接将账套号和年份输入在报表计算公式中。如果采用省略方法，在编制报表时就必须通过“数据”菜单下的“账套初始”程序设置相应的账套号和年份，否则编制报表时可能出错。在实际工作中一般不把账套号和年份直接输入在报表计算公式中，否则过了这一年或换了账套，该报表计算公式就需要进行修改。对于套用系统预置的报表模板生成的报表格式，虽然有的报表模板本身已经设置定义了有关报表项目的单元公式，但是这些单元的计算公式并不一定正确，需要用户进行必要的检查。第44页，共60页，2023年，2月20日，星期五(二)资产负债表主要取数公式资产负债表是反映企业在某一特定日期财务状况的会计报表。本表通过提供“年初数”和“年末数”两组数据，可以反映本年度发生的经济业务对资产、负债、所有者权益的影响结果。第45页，共60页，2023年，2月20日，星期五年初数运算公式中“会计期间”有两种表达方法，以货币资金为例：第一种：QC(1001,全年,,,年,,)+QC(1002,全年,,,年,,)+QC(1009,全年,,,年,,)，本公式的含义是取全年“现金”账户、“银行存款”账户、“其他货币资金”账户的期初数，即取本年度1月初的数据。第二种：QC(1001,1,,,年,,)+QC(1002,1,,,年,,)+QC(1009,1,,,年,,)，本公式的含义是取本年1月份“现金”账户、“银行存款”账户、“其他货币资金”账户的期初数。第46页，共60页，2023年，2月20日，星期五年末数运算公式的“会计期间”只有一种表示方法，即以“月”表示。仍以“货币资金”项目为例，期末数的运算公式为：QM(1001,月,,,年,,)+QM(1002,月,,,年,,)+QM(1009,月,,,年,,)。第47页，共60页，2023年，2月20日，星期五表4-5：资产负债表部分项目期末数运算公式为了便于计算机编制报表，各单位最好在“长期债权投资”、“长期借款”等科目下面设置“一年内到期的长期债权投资”和“一年内到期的长期负债”二级科目，月末对有关的投资业务和借款业务进行分析，将满足条件的项目转入“一年内到期的长期债权投资”或“一年内到期的长期负债”科目，在这样的情况下，报表中的“长期债权投资”、“长期借款”等项目的运算公式就不能根据有关账户的总账余额来取数，而应该根据有关账户的总账余额减去有关明细科目余额后的差额确定。第48页，共60页，2023年，2月20日，星期五举例说明如下：假定“一年内到期的长期债权投资”的科目代码为140299，则报表中“长期债权投资”项目的运算公式为QM(1402,月)-QM(140299,月)。第49页，共60页，2023年，2月20日，星期五(三)利润表主要取数公式利润表是反映企业一定会计期间经营成果的报表，该表是按照该期间各项收入、费用以及构成利润的各个项目分类分项编制而成的，因此本表所提供的数据都是本期收入或费用的发生额。利润表“本月数”栏的“主营业务收入”和“主营业务成本”运算公式可设置如下：1.主营业务收入：DFS(“5101”,“3131”,月,“借”,,,“001”,年)2.主营业务成本：DFS(“5102”，“3131”,月,“贷”,,,“001”,年)第50页，共60页，2023年，2月20日，星期五利润表“本月数”栏其他项目的运算公式可以依此类推，“本年累计数”栏的运算公式采用本表它页取数函数，假定“主营业务收入”栏为第6行，“净利润”栏为第21行，“本年累计”为第四列，则D6:D21区域的运算公式可以作如下表达：D6:D21=C6:C21+SELECT(D6:D21,月@=月+1)需要提醒的是，“本月数”栏采用对方科目取数函数(也称凭证取数函数)时，结转收入、费用的凭证只能是一借一贷的凭证，不能多借多贷或一借多贷和多借一贷。第51页，共60页，2023年，2月20日，星期五二、审核公式定义目前，国内财务软件公司开发的报表系统都提供了数据审核功能，用户只要将报表上的勾稽关系用公式表达出来(在会计电算化中称为审核公式)，计算机就可以根据审核公式自动判断报表编制的正确性。审核公式应该在格式状态下输入。第52页，共60页，2023年，2月20日，星期五现以资产负债表为例，说明审核公式的设置问题。假定资产期末数的单元为D39，负债及所有者权益期末合计数的单元为H39。在格式状态下单击“数据”菜单中的“编辑公式/审核公式”，系统弹出审核公式编辑窗口，如图4-15所示。第53页，共60页，2023年，2月20日，星期五第54页，共60页，2023年，2月20日，星期五在公式编辑框中输入：“D39=H39MESS期末资产总计不等于期末负债及所有者权益总计!”，点击〖确定〗按钮即完成D39单元格审核公式定义。公式的含义是：报表D39单元的数据必须等于H39单元的数据，否则，在屏幕上显示“期末资产总计不等于期末负债及所有者权益总计”的提示信息。第55页，共60页，20