第二章 账簿管理

1、第二章 账簿管理第二章 账簿管理在“帐套13”打开“账簿管理”文件夹新建6个工作表：如下图本章要点第一节 总账13第二节 现金、银行日记账13第三节 收入、成本、费用明细账13第四节 库存商品、原材料明细账13第五节 分类明细账13第六节 固定资产明细账13重点提示：为了让大家容易理解和掌握从凭证到账簿由电脑自动登记过程，本章主要使用了的函数公式，而没有应用VBA方面的内容。虽然函数公式较多，但有规律可循。只要理解了某个公式，很多相同的应用就用复制、粘贴即可。第一节 总账13打开总账13工作表，“sheet1”表页命名为“总账”，现以“库存现金”科目为例，样式如下：（表1）打印效果如下图： 2

2、.1.1 设置（表1）表头内容：总分类账：合并单元格E1:G2；单元格格式水平对齐为居中，宋体，字号18，双下划线，上标会计科目名称：合并单元格H1:I1,水平对齐为靠右，宋体，字号11会计科目代码：合并单元格H2:I2，水平对齐为靠右，宋体，字号11单元格K1、K2：直接输入科目名称和科目代码单元格L1:L3 (总账与分类明细账核对情况)是为辅助核对而设置的，不在总账打印范围内。表格边框线为最细线2.1.2 设置（表1）表体内容： 凭证日期：格式为日期型，直接输入。凭证编号：凭证起止号。起号：如单元格B5，公式为“=H:帐套13账务管理2013年1月zw.xlsx记账凭证汇总表!$H$5”。

3、含义是：其值从1月份的记账凭证汇总表的单元格$H$5中取得。 连接符“-”：如单元格C5，直接输入符号“-” 止号：如单元格D5，公式为“=H:帐套13账务管理2013年1月zw.xlsx记账凭证汇总表!$N$5”，含义是：其值从1月份的记账凭证汇总表的单元格$N$5中取得。那么，2月份的凭证起止号就是从2月份记账凭证汇总表的“$H$5”“$N$5”取得。以此类推选中区域B5:D5,单元格右下角出现“十”时，按住鼠标左键下拉到适当位置，放开鼠标左键，进行复制，然后选中B6:D6，将公式中1月份替换为2月份。这样就使得2月的凭证起止号从2月份记账凭证汇总表的单元格H5、N5中自动取值。摘要：直接

4、输入；复制。借方：单元格F5，格式为数值型，保留千位分隔符，公式为“=H:帐套13账务管理2013年1月zw.xlsx明细科目汇总表!$D$3”，含义是：其值是从1月明细科目汇总表的一级科目“库存现金”的“借方金额”取数得到的值。参照“凭证编号”同样方法，可以得到2月“借方”单元格F6的公式。贷方：单元格H5，格式为数值型，保留千位分隔符，公式为“=H:帐套13账务管理2013年1月zw.xlsx明细科目汇总表!$E$3”，含义是：其值是从1月明 细科目汇总表的一级科目“库存现金”的“贷方金额”取数得到的值。参照“凭证编号”同样方法，可以得到2月“贷方”单元格H6的公式。借：单元格J4，公式为

5、“=IF(K4=,IF(K4=0,平,IF(K40,借,)”，含义是：如“余额”栏有不等于零的数值时，其值为“借”；如等于零，其值为“平”；如没有数值时，其值为空。然后将公式复制到适当行（12月）即可。因财务制度规定，资产类和成本类科目的余额方向必须是借方，负债类和所有者权益类科目的余额方向必须是贷方。所以，当科目为负债类或所有者权益类时，将公式“=IF(K4=,IF(K4=0,平,IF(K40,借,)”中的“借”改为“贷”即可。余额： 单元格K4，格式为数值型，保留千位分隔符,公式为“=H:帐套13账务管理2013年1月zw.xlsx明细科目汇总表!$C$3”。含义是：其值是从1月明细科目汇

6、总表的一级科目“库存现金”的“期初余额”取数得到的值。单元格K5，格式为数值型，保留千位分隔符,公式为“=IF(D5=0,ROUND(K4+F5-H5,2)”。含义是：如当月没有凭证记录时，余额栏就为空；否则就是期初余额加上本月借方金额减去本月贷方金额。这是资产类和成本类科目的余额计算公式。 而负债类和所有者权益类科目的余额计算公式为“=IF(D5=0,ROUND(K4-F5+H5,2)”， 含义是：如当月没有凭证记录时，余额栏就为空；否则就是期初余额加上本月贷方金额减去本月借方金额。“累计折旧”“坏账准备”的资产类科目的减方科目，所以其公式同负债类和所有者权益类科目计算公式。然后，复制单元格

7、K5，到需要行即可。本年累计：使用求和公式即可。“=SUM(F5:F16)”2.1.3 其他总账科目设置方法（如：银行存款）结果如下：方法：复制“库存现金”样式，设置表体内容的公式即可。在本节中，总账里每个科目的借贷方数据来源是明细科目汇总表中相对应的一级科目余额。2.1.4 总分类账的L列“总账与分类明细账核对情况”是辅助列，不用时隐藏。一般在月底，进行和分类明细账对账用。比如，1月底库存现金总账（表1）和明细账核对，单元格L5其公式格式是：“=IF(K5=H:帐套13账簿管理现金、银行日记账13.xlsx库存现金!$F$23,)”，含义是：库存现金总账余额等于明细账余额，则核对无误，出现符

8、号“”。否则为不正确，出现符号“”。2.1.5 美化账簿界面：2.1.5.1 将帐页以外的行、列不显示。 选中帐页最后列的右边的这一列，同时按Ctrl+Shift+ 组合键，这样就可以快速选中帐页以外右边的所有列，然后点击鼠标右键，选择隐藏即可。选中选中帐页最后行的下边的这一行，同时按Ctrl+Shift+ 组合键，这样就可以快速选中帐页以外下边的所有行，然后点击鼠标右键，选择隐藏即可。如下图2.1.5.2 将帐页以外的背景色显示成你喜欢的颜色 选中帐页最后列的右边的这一列，同时按Ctrl+Shift+ 组合键，这样就可以快速选中帐页以外右边的所有列，然后点鼠标右键，选择填充色即可。选中帐页最

9、后行的下边的这一行，同时按Ctrl+Shift+ 组合键，这样就可以快速选中帐页以外下边的所有行，然后点击鼠标右键，选择填充色即可2.1.5.3 帐页格式：具有零值的单元格中不显示零值 点击【office按钮】-【Excel选项】- 【高级】- 在【在具有零值的单元格中显示零】前去掉对勾。 在以后所讲的帐页格式设置中，均采用这样的方式，不在赘述。第二节 现金、银行日记账13这一节中，现金、银行日记账13的借贷方数据来源是从凭证记录表中取来。符合会计原则要求。样式如下：（2-1-1表）（2-1-2表）（这里的现金日记账银行存款日记账是会计人员的账簿，不同于第四章资金管理里的现金、银行日记账。第四

10、章是专为出纳人员进行资金管理而设置的。）打开现金、银行日记账13工作表，“sheet1”表页命名为“库存现金”，“sheet2”表页命名为“银行存款-中行”，“sheet3”表页命名为“银行存款-农行”，“sheet4”表页命名为“银行存款-建行”。 在设置（2-1-1表）和（2-1-2表）的表体内容前，首先，要在凭证记录表进行指定设置，然后在现金和银行日记账中设置公式，使得当期凭证记录表有现金或银行业务记录时，库存现金和银行日记账就会自动从凭证记录表中取数，将有关记录分别、全面反映在库存现金和银行日记账中。2.2.1 在凭证记录表进行指定设置首先，打开文件夹“账务管理”中的“2013年1月z

11、w.xlsm”工作表，进入“凭证记录表”页面。在单元格R2、S2、T2、U2、，输入科目名称（根据账簿要反映的科目名称，不论科目级次）。然后，在单元格分别输入公式R3 公式为“=IF(D3=库存现金,ROW(),)”S3 公式为“=IF(D3=银行存款-中国银行昆明市理工大支行,ROW(),)”T3 公式为“=IF(D3=银行存款-中国农业银行西坝支行,ROW(),)”U3 公式为“=IF(K3=应收票据,ROW(),)”.选中单元格R3、S3、T3、U3、，下拉复制到第600行次即可。这时，上图的单元格S5、S7、T6，分别出现5、7、6数字，表示凭证记录表中第5行和第7行，有“银行存款-中

12、国银行昆明理工大支行”业务记录；第6行有“银行存款-中国农业银行西坝支行”业务记录。为现金、银行日记账13借贷方取数提供条件。在实际应用中，如有新增开设的银行户头，就在已经设置好的科目最后增加指定设置。比如：新增开设的“建设银行”户头，那么就在凭证记录表最后科目“原材料46色带”列之后，指定设置。单元格EA3的公式为：“=IF(D3=银行存款-中国建设银行昆明建设路支行,ROW(),)”。在这里的科目指定设置不分前后次序，如有新增科目需要在帐页中单独反映，就在最后列新增指定设置即可。如下图（在设置好后进行隐藏R、S、T、 EA 各列）2.2.2 现金、银行存款13设置界面切换回现金、银行日记账

13、13工作表,对“库存现金”“银行存款”进行取数公式设置2.2.2.1 （2-1-1表）为例，“库存现金”的公式为： 凭证日期：A5 “=IF(ISERROR(SMALL(2013年1月zw.xlsx凭证记录表!R$1:R$65536,ROW(A1),0,INDEX(2013年1月zw.xlsx凭证记录表!A$1:A$65536,SMALL(2013年1月zw.xlsx凭证记录表!R$1:R$65536,ROW(A1)”其含义是：当1月份凭证记录表R列（即指定设置的库存现金科目）出现数值时，则库存现金表页的凭证日期就会自动从凭证记录表中凭证日期栏（A$1:A$65536区域）取值。且取值结果会自

14、动按顺序无空格排列显示。凭证编号：B5 “=IF(ISERROR(SMALL(2013年1月zw.xlsx凭证记录表!R$1:R$65536,ROW(A1),INDEX(2013年1月zw.xlsx凭证记录表!B$1:B$65536,SMALL(2013年1月zw.xlsx凭证记录表!R$1:R$65536,ROW(A1)”其含义是：当1月份凭证记录表R列（即指定设置的库存现金科目）出现数值时，则库存现金表页的凭证编号就会自动从凭证记录表中凭证编号栏（B$1:B$65536区域）取值。且取值结果会自动按顺序无空格排列显示。摘要：C5 “=IF(ISERROR(SMALL(2013年1月zw.x

15、lsx凭证记录表!R$1:R$65536,ROW(A1),INDEX(2013年1月zw.xlsx凭证记录表!E$1:E$65536,SMALL(2013年1月zw.xlsx凭证记录表!R$1:R$65536,ROW(A1)” 其含义是：当1月份凭证记录表R列（即指定设置的库存现金科目）出现数值时，则库存现金表页的摘要就会自动从凭证记录表中凭证摘要栏（E$1:E$65536区域）取值。且取值结果会自动按顺序无空格排列显示。借方金额：D5 “=IF(C5=本月合计,SUMPRODUCT(MONTH($A4:$A$5)=MONTH($A4)*D4:D$5),IF(C5=本年累计,SUMIF($C4

16、:$C$5,本月合计,D$5),IF(ISERROR(SMALL(2013年1月zw.xlsx凭证记录表!R$1:R$65536,ROW(A1),INDEX(2013年1月zw.xlsx凭证记录表!I$1:I$65536,SMALL(2013年1月zw.xlsx凭证记录表!R$1:R$65536,ROW(A1)” 其含义是：当库存现金表页C列（摘要栏）出现“本月合计”或“本年累计”时，借方金额等于本月借方发生额合计数(自动计算)，或等于本年借方发生额累计数（自动计算）。否则就是：当1月份凭证记录表R列（即指定设置的库存现金科目）出现数值时，则库存现金表页的借方金额就会自动从凭证记录表中借方金额

17、栏（I$1:I$65536区域）取值。且取值结果会自动按顺序无空格排列显示。贷方金额：E5 “=IF(C5=本月合计,SUMPRODUCT(MONTH($A4:$A$5)=MONTH($A4)*E4:E$5),IF(C5=本年累计,SUMIF($C4:$C$5,本月合计,E$5),IF(ISERROR(SMALL(2013年1月zw.xlsx凭证记录表!R$1:R$65536,ROW(A1),INDEX(2013年1月zw.xlsx凭证记录表!J$1:J$65536,SMALL(2013年1月zw.xlsx凭证记录表!R$1:R$65536,ROW(A1)” 其含义是：当库存现金表页C列（摘要

18、栏）出现“本月合计”或“本年累计”时，贷方金额等于本月贷方发生额合计数（自动计算），或等于本年贷方发生额累计数（自动计算）。否则就是：当1月份凭证记录表R列（即指定设置的库存现金科目）出现数值时，则库存现金表页的贷方金额就会自动从凭证记录表中贷方金额栏（J$1:J$65536区域）取值。且取值结果会自动按顺序无空格排列显示。（借方）余额：F5 “=IF(AND(D5=,E5=),IF(OR(C5=本月合计,C5=本年累计),F4,ROUND(F4+D5-E5,2)” 其含义是:当库存现金表页的D列（借方金额）和E列（贷方金额）无数值时，F列（余额）为空。当C列（摘要）是“本月合计”或“本年累计

19、”时，F列（余额）等于上期余额，否则，就是等于上期余额+本期借方金额-本期贷方金额。选中区域A5:F5,单元格右下角出现“十”时，按住鼠标左键下拉到适当位置300行（根据业务量需要），放开鼠标左键即可，这样就使得“库存现金”帐页反映出1月所有与现金业务有关的所用凭证内容。从而自动完成了凭证到账簿登记过程。结果如下：现在我们看看复制后的公式有何变化：单元格A6的公式为“=IF(ISERROR(SMALL(2013年1月zw.xlsx凭证记录表!R$1:R$65536,ROW(A2),0,INDEX(2013年1月zw.xlsx凭证记录表!A$1:A$65536,SMALL(2013年1月zw.x

20、lsx凭证记录表!R$1:R$65536,ROW(A2)”和单元格A5的公式“=IF(ISERROR(SMALL(2013年1月zw.xlsx凭证记录表!R$1:R$65536,ROW(A1),0,INDEX(2013年1月zw.xlsx凭证记录表!A$1:A$65536,SMALL(2013年1月zw.xlsx凭证记录表!R$1:R$65536,ROW(A1)”进行比较，公式中只有ROW(A1)的A1变成了A2。其他单元格在复制后的变化也同样只有ROW(A1)的A1变成了A2、A3、A4 。这一点我们在会计期间的次月，进行替换公式将应用到。次月的单元格取数公式也是从ROW(A1)开始。2.2

21、.2.2 （2-1-2表）为例，“银行存款-中行”的公式为：凭证日期：A5 “=IF(ISERROR(SMALL(2013年1月zw.xlsx凭证记录表!S$1:S$65536,ROW(A1),0,INDEX(2013年1月zw.xlsx凭证记录表!A$1:A$65536,SMALL(2013年1月zw.xlsx凭证记录表!S$1:S$65536,ROW(A1)”凭证编号：B5 “=IF(ISERROR(SMALL(2013年1月zw.xlsx凭证记录表!S$1:S$65536,ROW(A1),INDEX(2013年1月zw.xlsx凭证记录表!B$1:B$65536,SMALL(2013年1

22、月zw.xlsx凭证记录表!S$1:S$65536,ROW(A1)”摘要：C5 “=IF(ISERROR(SMALL(2013年1月zw.xlsx凭证记录表!S$1:S$65536,ROW(A1),INDEX(2013年1月zw.xlsx凭证记录表!E$1:E$65536,SMALL(2013年1月zw.xlsx凭证记录表!S$1:S$65536,ROW(A1)”借方金额：D5 “=IF(C5=本月合计,SUMPRODUCT(MONTH($A4:$A$5)=MONTH($A4)*D4:D$5),IF(C5=本年累计,SUMIF($C4:$C$5,本月合计,D$5),IF(ISERROR(SMA

23、LL(2013年1月zw.xlsx凭证记录表!S$1:S$65536,ROW(A1),INDEX(2013年1月zw.xlsx凭证记录表!I$1:I$65536,SMALL(2013年1月zw.xlsx凭证记录表!S$1:S$65536,ROW(A1)”贷方金额：E5 “=IF(C5=本月合计,SUMPRODUCT(MONTH($A4:$A$5)=MONTH($A4)*E4:E$5),IF(C5=本年累计,SUMIF($C4:$C$5,本月合计,E$5),IF(ISERROR(SMALL(2013年1月zw.xlsx凭证记录表!S$1:S$65536,ROW(A1),INDEX(2013年1月

24、zw.xlsx凭证记录表!J$1:J$65536,SMALL(2013年1月zw.xlsx凭证记录表!S$1:S$65536,ROW(A1)”（借方）余额：F5 “=IF(AND(D5=,E5=),IF(OR(C5=本月合计,C5=本年累计),F4,ROUND(F4+D5-E5),2)”选中区域A5:F5,单元格右下角出现“十”时，按住鼠标左键下拉到适当位置300行(根据业务量需要)，放开鼠标左键即可，这样就使得“银行存款-中行”帐页反映出1月所有与中行业务有关的所用凭证内容。结果如下：2.2.3 “本月合计” “本年累计”设置方法：要求：在财务日常工作中，“本月合计”“本年累计”是每月都会出

25、现，而且字体为红色，整行有红色下划线EXCEL处理后，达到如下结果：当在摘要栏输入“本月合计”或“本年累计”时，字体自动为红色，且所在整行会自动出现红色下划线，同时整行出现背景色。下面着重介绍设置方法。【借方金额】、【贷方金额】的“本月合计”“本年累计”会自动计算，其【（借方）余额】同样会自动计算。这个功能已经在本节“借方金额”“贷方金额”的取数公式中已经设置好，即公式中的前部分，如“=IF(C5=本月合计,SUMPRODUCT(MONTH($A4:$A$5)=MONTH($A4)*D4:D$5),IF(C5=本年累计,SUMIF($C4:$C$5,本月合计,D$5),”；（借方）余额 公式中

26、也已实现自动计算功能。如“=IF(AND(D5=,E5=),IF(OR(C5=本月合计,C5=本年累计),F4,ROUND(F4+D5-E5),2)”下面着重介绍“当在摘要栏输入“本月合计”或“本年累计”时，字体自动为红色，且所在整行会自动出现红色下划线，同时整行出现背景色”的设置方法，现以银行存款-建行表页为例，如图选中A列，点击工具栏的【开始】，【条件格式】在【条件格式】下来菜单中选中“新建规则”， 在【选择规则类型】中选中“使用公式确定要设置格式的单元格”，在【为符合此公式的值设置格式】下输入公式“=or(c1=本月合计,c1=本年累计)”然后，点击【格式】,边框的下线颜色选红色，实线。

27、 如下再将填充色选为淡绿色（可随个人喜好选择填充色）确定。 再确定，结果如下图选中B列，重复以上操作步骤。结果如下选择C列，重复操作步骤，同时在点击【格式】时,除边框下线颜色选红色，实线外，字体颜色也要选红色。这是因为本列中会出现文字“本月合计”“本年累计”。如下选择D列，重复前面操作A列步骤选择E列，重复操作A列步骤选择F列，重复操作A列步骤完成以上步骤后，就会得到如下结果这样一来，当你在本表中C列的任意一行，输入“本月合计”或“本年累计”时，“本月合计”和“本年累计”字体颜色就会自动为红色，同时所在行就会自动出现红色下划线，且“借方金额”、“贷方金额”、“余额”的本月合计和本年累计数值也会

28、自动计算出来。如果要查看、编辑或修改、删除以上新建的单元格条件格式，可以在【条件格式】下来菜单中选中“管理规则”进行操作，如下图2.2.4 当会计期间到次月时，将公式进行替换即可。现以现金日记账的“库存现金”为例说明，如下图（2-1-3表）从（2-1-3表）可看出第23行已经反映完1月份所有有关库存现金账务记录，当在C24单元格输入“本月合计”时，单元格D24、E24、F24就会自动计算数值，且“本月合计”字体为红色，所在行有红色下划线和背景色。从第25行起，反映的应该是2月份的库存现金账务记录。由于我们在设置本表格式时已经将各单元的公式复制到了第300行，所以，我们将第25行单元格公式进行替

29、换。首先我们看看替换前的各单元格的公式：A25 “=IF(ISERROR(SMALL(2013年1月zw.xlsx凭证记录表!R$1:R$65536,ROW(A21),0,INDEX(2013年1月zw.xlsx凭证记录表!A$1:A$65536,SMALL(2013年1月zw.xlsx凭证记录表!R$1:R$65536,ROW(A21)”B25 “=IF(ISERROR(SMALL(2013年1月zw.xlsx凭证记录表!R$1:R$65536,ROW(A21),INDEX(2013年1月zw.xlsx凭证记录表!B$1:B$65536,SMALL(2013年1月zw.xlsx凭证记录表!R

30、$1:R$65536,ROW(A21)”C25 “=IF(ISERROR(SMALL(2013年1月zw.xlsx凭证记录表!R$1:R$65536,ROW(A21),INDEX(2013年1月zw.xlsx凭证记录表!E$1:E$65536,SMALL(2013年1月zw.xlsx凭证记录表!R$1:R$65536,ROW(A21)”D25 “=IF(C25=本月合计,SUMPRODUCT(MONTH($A$5:$A24)=MONTH($A24)*D$5:D24),IF(C25=本年累计,SUMIF($C$5:$C24,本月合计,D$5),IF(ISERROR(SMALL(2013年1月zw

31、.xlsx凭证记录表!R$1:R$65536,ROW(A21),INDEX(2013年1月zw.xlsx凭证记录表!I$1:I$65536,SMALL(2013年1月zw.xlsx凭证记录表!R$1:R$65536,ROW(A21)”E25 “=IF(C25=本月合计,SUMPRODUCT(MONTH($A$5:$A24)=MONTH($A24)*E$5:E24),IF(C25=本年累计,SUMIF($C$5:$C24,本月合计,E$5),IF(ISERROR(SMALL(2013年1月zw.xlsx凭证记录表!R$1:R$65536,ROW(A21),INDEX(2013年1月zw.xlsx

32、凭证记录表!J$1:J$65536,SMALL(2013年1月zw.xlsx凭证记录表!R$1:R$65536,ROW(A21)”然后进行公式替换，方法如下：选中区域A25:E25 ,工具栏“开始”“查找和选择”“替换”，或按键Ctrl+H,出现替换界面。将公式中“1月”【全部替换】成“2月”，“A21”【全部替换】为“A1”.如下：公式中“ROW(A21)”的“21”，表示的是从1月份取数公式为开始行（即第一行）算起，到此行行数。 替换后，单元格A25:E25中的数值将是从2月份的记账凭证记录表中提取。单元格公式变为A25 “=IF(ISERROR(SMALL(2013年2月zw.xlsx凭

33、证记录表!R$1:R$65536,ROW(A1),0,INDEX(2013年2月zw.xlsx凭证记录表!A$1:A$65536,SMALL(2013年2月zw.xlsx凭证记录表!R$1:R$65536,ROW(A1)”B25 “=IF(ISERROR(SMALL(2013年2月zw.xlsx凭证记录表!R$1:R$65536,ROW(A1),INDEX(2013年2月zw.xlsx凭证记录表!B$1:B$65536,SMALL(2013年2月zw.xlsx凭证记录表!R$1:R$65536,ROW(A1)”C25 “=IF(ISERROR(SMALL(2013年2月zw.xlsx凭证记录表

34、!R$1:R$65536,ROW(A1),INDEX(2013年2月zw.xlsx凭证记录表!E$1:E$65536,SMALL(2013年2月zw.xlsx凭证记录表!R$1:R$65536,ROW(A1)”D25 “=IF(C25=本月合计,SUMPRODUCT(MONTH($A$5:$A24)=MONTH($A24)*D$5:D24),IF(C25=本年累计,SUMIF($C$5:$C24,本月合计,D$5),IF(ISERROR(SMALL(2013年2月zw.xlsx凭证记录表!R$1:R$65536,ROW(A1),INDEX(2013年2月zw.xlsx凭证记录表!I$1:I$6

35、5536,SMALL(2013年2月zw.xlsx凭证记录表!R$1:R$65536,ROW(A1)”E25 “=IF(C25=本月合计,SUMPRODUCT(MONTH($A$5:$A24)=MONTH($A24)*E$5:E24),IF(C25=本年累计,SUMIF($C$5:$C24,本月合计,E$5),IF(ISERROR(SMALL(2013年2月zw.xlsx凭证记录表!R$1:R$65536,ROW(A1),INDEX(2013年2月zw.xlsx凭证记录表!J$1:J$65536,SMALL(2013年2月zw.xlsx凭证记录表!R$1:R$65536,ROW(A1)”选中区

36、域A25:E25, 单元格右下角出现“十”时，按住鼠标左键下拉到适当位置300行.这样就覆盖掉第25行到第300行的原公式即可。从（2-1-3表）可知，第38行已经是2月份“库存现金”记录的最后一行，那么我们在单元格C39输入“本月合计”、C40输入“本年累计”后，其字体会自动为红色，所在行有背景色和红色下划线，且借方金额、贷方金额、等等会自动计算出来。从第41行起就应该是记录3月份的库存现金凭证内容了，然后利用替换功能将公式替换：选中区域A41:E41，将公式中“2月”【全部替换】成“3月”，“A17”【全部替换】为“A1”。最后再复制即可。第三节 收入、成本、费用明细账13打开收入、成本、

37、费用明细账工作表，根据账簿需要设置以下表页，如：sheet1为“主营业务收入”Sheet2为“主营业务成本”sheet3为“制造费用”. “生产成本” “管理费用” “销售费用” “财务费用” “营业税金及附加” “所得税” “其他业务收入” “其他业务支出” “营业外收入” “营业外支出” “以前年度损益调整” 等等（如果企业经营产品种类较多，也可以产品单独设置明细账，如营业收入明细账、成本明细账、生产成本明细账、费用明细账等等。）现以“主营业务收入”和“管理费用”表页为例说明2.3.1 主营业务收入“主营业务收入”表页 如下图（3-1），是以产品种类较少而设置的。（如果企业经营的产品种类较

38、多，且业务量发生频繁，可以依据每个产品单设帐页。）其中产品名称（主营业务收入的二级科目）：1、 24s乳癖清胶囊 2、 36s乳癖清胶囊 3、 48s乳癖清胶囊 4、 24s溶栓脑通胶囊 5、 36s溶栓脑通胶囊 6、 24s惠血生胶囊 7、 36s惠血生胶囊 8、 乳果糖口服液主营业务收入（3-1）接上图右边从图表的样式可以看出：“主营业务收入”是含有数量金额的多栏式明细账， 从第L列往后是“主营业务收入”的二级科目，即各个产品的销售数据。其含销售产品的数量、单价、金额，是从C列对应取值。（一般C列不用时隐藏）。当每到月底，在D列单元格输入“本月合计”或“本年累计”时，汇总数据就自动计算出来

39、，且有背景色和红色下划线。“单价”栏不将参加汇总。2.3.1.1 “主营业务收入”表页格式设置因为我们在前面已经讲了很多表样设置方法和过程，所以这里只讲“本月合计”“本年累计”在D列出现时帐表显示有关设置。（可参考第二节继续学习） 选中A列，点击工具栏的【开始】，【条件格式】 在【条件格式】下来菜单中选中“新建规则”， 在【选择规则类型】中选中“使用公式确定要设置格式的单元格”，在【为符合此公式的值设置格式】下输入公式“=or(d1=本月合计,d1=本年累计)”, 然后，点击【格式】,边框的下线颜色选红色，实线。 如下再将填充色选为淡绿色（可随个人喜好选择填充色） 确定应用后。然后用同样的方法

40、分别将除D列以外的其他列进行设置。 选择D列，重复操作步骤，同时在点击【格式】时,除边框下线颜色选红色，实线外，字体颜色也要选红色。这是因为本列中会出现文字“本月合计”“本年累计”。如下这样一来，当你在本表中D列的任意一行，输入“本月合计”或“本年累计”时，“本月合计”和“本年累计”字体颜色就会自动为红色，同时所在行就会自动出现红色下划线和背景色，且“借方金额”、“贷方金额”、“余额”以及二级明细科目（各产品）的本月合计和本年累计数值也会自动计算出来。在打印本帐页时，需自定义页眉和页脚，如下 隐藏C列。因为C列是辅助项，是为二级科目取值服务，在打印帐页时一般进行隐藏。2.3.1.2 “主营业务

41、收入”表页取数公式设置首先，在凭证记录表表页我们已经指定设置了“主营业务收入”在DH列，如单元格 DH3公式“=IF(K3=主营业务收入,ROW(),)” 然后，在“主营业务收入”表页（3-1）中，给各个单元格设置取值公式：凭证日期 A3 “=IF(ISERROR(SMALL(2013年1月zw.xlsx凭证记录表!DH$1:DH$65536,ROW(A1),0,INDEX(2013年1月zw.xlsx凭证记录表!A$1:A$65536,SMALL(2013年1月zw.xlsx凭证记录表!DH$1:DH$65536,ROW(A1)” 其含义是：当1月份凭证记录表DH列（即指定设置的主营业务收入

42、科目）出现数值时，则主营业务收入表页的凭证日期就会自动从凭证记录表中凭证日期栏（A$1:A$65536区域）取值。且取值结果会自动按顺序无空格排列显示。凭证编号B3 “=IF(ISERROR(SMALL(2013年1月zw.xlsx凭证记录表!DH$1:DH$65536,ROW(A1),INDEX(2013年1月zw.xlsx凭证记录表!B$1:B$65536,SMALL(2013年1月zw.xlsx凭证记录表!DH$1:DH$65536,ROW(A1)” 其含义是：当1月份凭证记录表DH列（即指定设置的主营业务收入科目）出现数值时，则主营业务收入表页的凭证编号就会自动从凭证记录表中凭证编号栏

43、（B$1:B$65536区域）取值。且取值结果会自动按顺序无空格排列显示。科目名称C3 “=IF(ISERROR(SMALL(2013年1月zw.xlsx凭证记录表!DH$1:DH$65536,ROW(A1),INDEX(2013年1月zw.xlsx凭证记录表!D$1:D$65536,SMALL(2013年1月zw.xlsx凭证记录表!DH$1:DH$65536,ROW(A1)” 其含义是：当1月份凭证记录表DH列（即指定设置的主营业务收入科目）出现数值时，则主营业务收入表页的科目名称就会自动从凭证记录表中凭证科目名称栏（D$1:D$65536区域）取值。且取值结果会自动按顺序无空格排列显示。

44、摘要 D3 “=IF(ISERROR(SMALL(2013年1月zw.xlsx凭证记录表!DH$1:DH$65536,ROW(A1),INDEX(2013年1月zw.xlsx凭证记录表!E$1:E$65536,SMALL(2013年1月zw.xlsx凭证记录表!DH$1:DH$65536,ROW(A1)” 其含义是：当1月份凭证记录表DH列（即指定设置的主营业务收入科目）出现数值时，则主营业务收入表页的摘要就会自动从凭证记录表中凭证摘要栏（E$1:E$65536区域）取值。且取值结果会自动按顺序无空格排列显示。数量E3 “=IF(D3=本月合计,SUMPRODUCT(MONTH($A2:$A$

45、3)=MONTH($A2)*E2:E$3),IF(D3=本年累计,SUMIF($D2:$D$3,本月合计,E$3),IF(G3=0,0,IF(ISERROR(SMALL(2013年1月zw.xlsx凭证记录表!DH$1:DH$65536,ROW(A1),INDEX(2013年1月zw.xlsx凭证记录表!G$1:G$65536,SMALL(2013年1月zw.xlsx凭证记录表!DH$1:DH$65536,ROW(A1)” 其含义是：当主营业务收入表页D列（摘要栏）出现“本月合计”或“本年累计”时，（借方）数量等于本月（借方）数量合计数(自动计算)，或等于本年（借方）数量累计数（自动计算）。否

46、则就是：当1月份凭证记录表DH列（即指定设置的主营业务收入科目）出现数值时，则主营业务收入表页的（借方）数量就会自动从凭证记录表中数量栏（G$1:G$65536区域）取值。且取值结果会自动按顺序无空格排列显示。单价F3 “=IF(OR(D3=本月合计,D3=本年累计),0,IF(G3=0,0,IF(ISERROR(SMALL(2013年1月zw.xlsx凭证记录表!DH$1:DH$65536,ROW(A1),INDEX(2013年1月zw.xlsx凭证记录表!H$1:H$65536,SMALL(2013年1月zw.xlsx凭证记录表!DH$1:DH$65536,ROW(A1)” 其含义是：当主

47、营业务收入表页D列（摘要栏）出现“本月合计”或“本年累计”时，（借方）单价等于零，即单价不汇总。否则就是：当1月份凭证记录表DH列（即指定设置的主营业务收入科目）出现数值时，则主营业务收入表页的（借方）单价就会自动从凭证记录表中单价栏（H$1:H$65536区域）取值。且取值结果会自动按顺序无空格排列显示。借方金额G3 “=IF(D3=本月合计,SUMPRODUCT(MONTH($A2:$A$3)=MONTH($A2)*G2:G$3),IF(D3=本年累计,SUMIF($D2:$D$3,本月合计,G$3),IF(ISERROR(SMALL(2013年1月zw.xlsx凭证记录表!DH$1:DH

48、$65536,ROW(A1),INDEX(2013年1月zw.xlsx凭证记录表!I$1:I$65536,SMALL(2013年1月zw.xlsx凭证记录表!DH$1:DH$65536,ROW(A1)” 其含义是：当主营业务收入表页D列（摘要栏）出现“本月合计”或“本年累计”时，借方金额等于本月借方发生额合计数(自动计算)，或等于本年借方发生额累计数（自动计算）。否则就是：当1月份凭证记录表DH列（即指定设置的主营业务收入科目）出现数值时，则主营业务收入表页的借方金额就会自动从凭证记录表中借方金额栏（I$1:I$65536区域）取值。且取值结果会自动按顺序无空格排列显示。数量H3 “=IF(D

49、3=本月合计,SUMPRODUCT(MONTH($A2:$A$3)=MONTH($A2)*H2:H$3),IF(D3=本年累计,SUMIF($D2:$D$3,本月合计,H$3),IF(J3=0,0,IF(ISERROR(SMALL(2013年1月zw.xlsx凭证记录表!DH$1:DH$65536,ROW(A1),INDEX(2013年1月zw.xlsx凭证记录表!G$1:G$65536,SMALL(2013年1月zw.xlsx凭证记录表!DH$1:DH$65536,ROW(A1)” 其含义是：当主营业务收入表页D列（摘要栏）出现“本月合计”或“本年累计”时，（贷方）数量等于本月（贷方）数量合

50、计数(自动计算)，或等于本年（贷方）数量累计数（自动计算）。否则就是：当1月份凭证记录表DH列（即指定设置的主营业务收入科目）出现数值时，则主营业务收入表页的（贷方）数量就会自动从凭证记录表中数量栏（G$1:G$65536区域）取值。且取值结果会自动按顺序无空格排列显示。 单价I3“=IF(OR(D3=本月合计,D3=本年累计),0,IF(J3=0,0,IF(ISERROR(SMALL(2013年1月zw.xlsx凭证记录表!DH$1:DH$65536,ROW(A1),INDEX(2013年1月zw.xlsx凭证记录表!H$1:H$65536,SMALL(2013年1月zw.xlsx凭证记录表

51、!DH$1:DH$65536,ROW(A1)” 其含义是：当主营业务收入表页D列（摘要栏）出现“本月合计”或“本年累计”时，（贷方）单价等于零，即单价不汇总。否则就是：当1月份凭证记录表DH列（即指定设置的主营业务收入科目）出现数值时，则主营业务收入表页的（贷方）单价就会自动从凭证记录表中单价栏（H$1:H$65536区域）取值。且取值结果会自动按顺序无空格排列显示。贷方金额J3 “=IF(D3=本月合计,SUMPRODUCT(MONTH($A2:$A$3)=MONTH($A2)*J2:J$3),IF(D3=本年累计,SUMIF($D2:$D$3,本月合计,J$3),IF(ISERROR(SM

52、ALL(2013年1月zw.xlsx凭证记录表!DH$1:DH$65536,ROW(A1),INDEX(2013年1月zw.xlsx凭证记录表!J$1:J$65536,SMALL(2013年1月zw.xlsx凭证记录表!DH$1:DH$65536,ROW(A1)” 其含义是：当主营业务收入表页D列（摘要栏）出现“本月合计”或“本年累计”时，贷方金额等于本月贷方发生额合计数(自动计算)，或等于本年贷方发生额累计数（自动计算）。否则就是：当1月份凭证记录表DH列（即指定设置的主营业务收入科目）出现数值时，则主营业务收入表页的贷方金额就会自动从凭证记录表中贷方金额栏（J$1:J$65536区域）取值

53、。且取值结果会自动按顺序无空格排列显示。余额K3 “=IF(AND(G3=,J3=),IF(OR(D3=本月合计,D3=本年累计),K2,ROUND(K2-G3+J3,2)” 其含义是:当主营业务收入表页的G列（借方金额）和J列（贷方金额）无数值时，K列（余额）为空。当D列（摘要）是“本月合计”或“本年累计”时，K列（余额）等于上期余额，否则，就是等于上期余额-本期借方金额+本期贷方金额。从L3到AE3 ，均为“主营业务收入”的明细科目，可按品名设置为数量金额式。明细科目1 如（24S乳癖清胶囊）数量 L3 公式为 “=IF(D3=本月合计,SUMPRODUCT(MONTH($A2:$A$3)=MONTH($A2)*L2:L$3),IF(D3=本年累计,SUMIF($D2:$D$3,本月合计,L$3),IF(C3=主营业务收入-24S乳癖清胶囊,H3,)” 其含义是：当主营业务收入表页D列（摘要栏）出现“本月合计”或“本年累计”时，数量等于本月（贷方）数量合计数(自动计算)，或等于本年（贷方）数量累计数（自动计算）。否则就是：当主营业务收入表页C列出现“主营业务收入-24S乳癖清胶囊”时，L3数量值等于H3数量。单价 M3公式为 “=IF(OR(D3=本月合计,