首先应自动生成或新建工作表、工作表更名、设置单元格格式等;然后设计账簿基本外观格式;再根据会计核算资料,进行表间取数、表内运算,自动生成总分类账、日记账或明细分类账。
会计账簿的数据主要来自会计科目表、年初余额表、会计分录表、数据透视表等,所以需要进行表间取数。取数的方法有:单元格直接引用、名称粘贴、函数取数、数据透视表自动生成等。
会计账簿需要反映余额及发生额,所以,必须使用Excel函数、数据排序、数据筛选、单元区域技术等,分别处理相关账簿年初有无余额、余额的方向,以及本期发生额等各种较为复杂的情况。
2.高级筛选
Excel的数据筛选有自动筛选和高级筛选两项功能。与自动筛选相比,高级筛选可以用更多、更复杂的条件,并且可以不使用逻辑运算符而将多个筛选条件加以逻辑运算;高级筛选还可将筛选结果从数据区域中抽取出来,复制到当前工作表的指定位置。高级筛选需要有3个区域:数据区域、条件区域、筛选结果区域。
(1)数据区域,即需要进行筛选的数据区域或整个工作表(筛选总体)。
(2)条件区域,即用来指定筛选数据时必须满足的条件。
(3)筛选结果区域,即用于显示存放满足筛选条件结果的区域,可以是原有区域,也可以是其他指定的区域。
3.指定位置取值函数说明
数组取值LOOKUP函数与行取值HLOOKUP函数、列取值VLOOKUP函数相似。区别在于:HLOOKUP在第一行中搜索待查找的值,VLOOKUP在第一列中搜索待查找的值,而LOOKUP根据数组维度进行搜索待查找的值。
使用高级筛选与指定位置取值LOOKUP函数,编制星科制造公司管理费用总分类账。
完成工作任务后,星科制造公司管理费用总账如图8-18所示。
图8-18 管理费用总分类账及高级筛选对话框
(1)在现有工作表标签上右击,选择“插入”命令,以新建工作表;将该表的标签修改为“管理费用”。
(2)合并A1至I1单元区域;录入第1、2行的相关文字;在D3单元格录入带半角单引号的管理费用科目编码“6602”。
(3)用数组(即多行多列)查找值LOOKUP函数,根据科目编码自动生成总账科目名称,方法如下:
a.选定E3单元格,单击编辑框前的插入函数按钮,在查找与引用类中选择查找值LOOKUP函数,由于该函数有向量形式(即单行单列)、数组形式(即多行多列)两种参数组合方式,将进入“选定参数”对话框,如图8-19(a)所示。
b.选择数组参数(第2条)后进入LOOKUP函数参数对话框,如图8-19(b)所示。
图8-19 LOOKUP函数组合方式(a)与LOOKUP数组函数(b)
c.在待查找对象框中录入“D3”;在查找总体中录入“会计科目!A:B”。
d.单击“确定”按钮,编辑框将显示公式“=LOOKUP(D3,会计科目!A:B)”。
公式含义:在会计科目表的A列中查找与本表D3单元格相同的值,并取该值对应于B列相同位置的值。
(4)高级筛选管理费用的本期记录,方法如下:
a.任意选取一个单元格,单击“数据/排序与筛选/高级”命令;在弹出的警告对话框中单击“确定”按钮,进入“高级筛选”对话框,如图8-18(b)所示。
b.选择方式为“复制到其他位置”;单击“列表区域”框,再单击“会计分录”表后选择A1至J112单元区域;单击“条件区域”框,再选择本表的E2至E3单元区域;单击“复制到”框,再单击本表的A4单元格。
c.单击“确定”按钮,工作表第4行将显示“会计分录”表第1行的所有字段,第5~9行将显示筛选的符合条件的记录。
(5)修改表格外观格式。选定F4至H9单元区域(即一、二、三级科目字段);右击鼠标选择“删除”命令;在弹出的对话框中选择“右侧单元格左移”命令。
在H4与I4单元格分别录入“方向、期末余额”文字。
(6)录入第1条记录公式,方法如下:
a.在H5单元格录入公式“=IF(F5>G5,"借","贷")”。公式含义:如果F5大于G5单元格的值,则显示为“借”;否则显示为“贷”。
b.在I5单元格录入公式“=ABS(F5-G5)”。公式含义:对两单元格之差取绝对值。
(7)录入其他各行记录的公式,方法如下:
a.在H6单元格录入借贷方向判断公式“=IF((IF(H5="借",I5,-I5)+F6-G6)>0,"借",IF((IF(H5="借",I5,-I5)+F6-G6)<0,"贷","平"))”。
公式含义:若“±I5+F6-G6>0”,则为“借”;若“±I5+F6-G6<0”,则为“贷”;否则为“平”。其中, “±I5”的取值方法是:若H5单元格为“借”,则取“I5”;否则取“-I5”。
b.在I6单元格录入期末余额公式“=ABS(IF(H5="借",I5,-I5)+F6-G6)”。
公式含义:若H5单元格为“借”,则取I5单元格的值作为被加数,否则取I5单元格的负值作被加数,然后加F6单元格的值减G6单元格的值,并对计算结果取绝对值。
c.选定H6至I6单元区域,通过I6单元格的填充柄拖动至I9单元格,以自动填充H7至I9单元区域公式。
使用高级筛选与指定位置取值LOOKUP函数,编制星科制造公司“应交税费/应交增值税/销项税额”明细账,其中的表标题应使用条件IF函数自动生成。
完成工作任务后,“应交税费/应交增值税/销项税额”明细账如图8-20所示。
图8-20 应交税费/应交增值税/销项税额明细账
(1)在现有工作表标签上右击选择“插入”命令,以新建工作表;将该表的标签修改为“销项税额”。
(2)合并A1至I1单元区域;录入第2行相关文字(不录入第1行表标题文字);在D3单元格录入带半角单引号前缀的“22210102”(应交税费/应交增值税/销项税额的科目编码)。
(3)用向量(单行或单列)取值LOOKUP函数,根据科目编码生成一、二、三级科目的名称,方法如下:
a.选定E3单元格,单击“插入函数”按钮,在查找与引用类中选择取值LOOKUP函数,进入“选定参数”对话框(图8-19(a));选择向量参数(第1条)后进入LOOKUP函数参数对话框。
b.在待查找对象框中录入“D3”;在查找总体中录入“会计科目!A:D”;在取值的行或列中录入“会计科目!B:B”。(www.xing528.com)
c.单击“确定”按钮,编辑框将显示“=LOOKUP(D3,会计科目!A:D,会计科目!B:B)”。
公式含义:在会计科目表的A至D列中查找与本表D3单元格相同的值,并取会计科目表B列中相同位置的值。
类似地,F3=LOOKUP(D3,会计科目!A:D,会计科目!C:C);G3=LOOKUP(D3,会计科目!A:D,会计科目!D:D)。
拓展:也可用取值LOOKUP数组(多行多列)函数,根据科目编码自动生成上述三个科目名称,公式分别为:E4=LOOKUP(D3,会计科目!A:B);F4=LOOKUP(D3,会计科目!A:C);G4=LOOKUP(D3,会计科目!A:D)。
(4)文本运算自动生成第1行表标题的文字,方法为:在A1单元格录入“=”号;单击E3单元格,键入“&”与“"/"”符,键入“&”符;单击F3单元格,键入“&”与“"/"”符,键入“&”符;单击G3单元格,键入“&”与“"明细账"”。
输入完成后,Excel将自动生成该明细账的表标题。
(5)高级筛选销项税额的本期记录。方法如下:
a.选择任意单元格,单击“数据/排序与筛选/高级”命令;在弹出的警告对话框中单击“确定”按钮进入“高级筛选”对话框。
b.在对话框的列表区域框中录入“会计分录!$A$1:$J$112”,在条件区域中录入“$E$2:$G$3”,勾选“将筛选结果复制到其他位置”,并在复制到框中录入“$A$5”;单击“确定”按钮。
(6)修改整理为“销项税额明细账”,方法如下。
a.按前述方法删除多余的字段;录入“方向、期末余额”文字。
b.录入与填充单元格运算公式,主要有:
H6=IF(F6>G6,"借","贷");
I6=ABS(F6-G6);
H7=IF((IF(H6="借",I6,-I6)+F7-G7)>0,"借",IF((IF(H6="借",I6,-I6)+F7-G7)<0,"贷","平"));
I7=ABS(IF(H6="借",I6,-I6)+F7-G7)。
根据会计科目表、年初余额表、数据透视表生成工行存款日记账。
完成工作任务后,工行存款日记账(有年初余额)如图8-21所示。
图8-21 有年初余额的日记账(根据多表透视数据生成)
(1)根据数据透视表生成工行存款本期记录,方法如下:
a.在“多字段透视”表中双击工行存款后部的借方或贷方金额(或右击“金额”,选择“显示详细信息”与“组及分类显示/显示明细数据”等命令),Excel将自动新建工作表,并在该表中生成该科目的本期借、贷方记录。
b.将自动新建的工作表标签修改为“工行存款”。
(2)修改表格外观,方法如下:
a.选择任意单元格,单击“数据/排序和筛选/筛选”命令,以取消筛选器(Excel 2003及其以前版本没有筛选器)。右击该表的全选按钮,选择“复制”命令;再右击该表的全选按钮,选择“选择性粘贴”命令,再选择“数值”并单击“确定”按钮。
b.选择多余的字段(一、二、三级科目),右击选择“删除”命令,并选择“右侧单元格左移”。
c.在第一行之前插入3行;合并A1至I1单元区域;录入第1行文字;在D3单元格录入半角前缀单引号的“100201”(工行存款科目编码)。
d.选定“日”字段,即C4单元格(Excel 2003及其以前版本应选择C4至I19单元区域),单击“数据/排序和筛选/升序”命令,以使该表的记录按日期由小到大的顺序进行排列。
(3)根据科目编码生成科目名称,方法如下:
a.在E3单元格录入公式“=LOOKUP(D3,会计科目!A:D,会计科目!B:B)”。
b.在F3单元格录入公式“=LOOKUP(D3,会计科目!A:D,会计科目!C:C)”。
(4)插入“上年结转”数据行,方法如下:
a.选定第5行,右击选择“插入”命令,并在E5单元格录入文字。
b.在H5单元格录入公式“=IF(LOOKUP(D3,年初余额!A:F,年初余额!E:E)<>"","借",IF(LOOKUP(D3,年初余额!A:F,年初余额!F:F)<>"","贷","平"))”。
c.在I5单元格录入公式“=LOOKUP(D3,年初余额!A:F,IF(H5="借",年初余额!E:E,年初余额!F:F))”。
(5)计算各记录行的余额,方法如下:
a.在H6单元格录入公式“=IF((IF(H5="借",I5,-I5)+F6-G6)>0,"借",IF((IF(H5="借",I5,-I5)+F6-G6)<0,"贷","平"))”。
b.在I6单元格录入公式“=ABS(IF(H5="借",I5,-I5)+F6-G6)”。
c.自动填充H7至I19单元区域公式。
根据会计科目表、年初余额表、数据透视表生成应付账款明细账,并使用名称粘贴法进行函数取数。
完成工作任务后,应付账款明细账(有年初余额),如图8-22所示。
图8-22 有年初余额的明细账(透视表生成与定义名称)
(1)自动生成记录并设计表格,方法如下:
a.在“多字段透视”表中双击“湘洪实业公司”后的借方或贷方金额,将自动生成的明细记录表标签修改为“应付湘洪”,再对该表进行选择性粘贴“数值”。
b.删除多余的字段;插入空白行;录入第1行文字;在D3单元格录入带半角单引号前缀的“220202”(应付账款/湘洪实业公司科目编码);对日期按升序排序。
(2)定义与粘贴名称,Excel 2007—Excel 2019方法如下(Excel 2003及其以前版本见“学习情境7”):
a.选定“会计科目”表的A列至D列,单击“公式/定义的名称/定义名称”命令进入“新建名称”对话框;录入名称为“会计科目”,引用位置为“=会计科目!$A:$D”,单击“确定”按钮。
b.在E3单元格录入公式“=LOOKUP(D3,会计科目,会计科目!B:B)”;在F3单元格录入公式“=LOOKUP(D3,会计科目,会计科目!C:C)”。
注意:函数公式中,第1个“会计科目”表示名称取数,第2个“会计科目!”表示表间取数(多“!”)。
(3)计算余额公式:复制“工行存款”表的H5至I7单元格,粘贴于“应付湘洪”表的H5至I7单元格(只粘贴了单元公式)。
免责声明:以上内容源自网络,版权归原作者所有,如有侵犯您的原创版权请告知,我们将尽快删除相关内容。