1、 天津财经大学天津财经大学 商学院会计系商学院会计系 第3章 Excel应用案例 【本章重点本章重点】 自动折旧模型自动折旧模型 工资管理模型工资管理模型 销售管理模型销售管理模型 天津财经大学天津财经大学 商学院会计系商学院会计系 3.1 自动折旧模型 3.2 工资管理模型 3.3 销售管理模型 本章目录 【课后习题】 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 3.1 自动折旧模型 3.1.13.1.1模型功能模型功能 利用该模型，只需输入固定资产原值、使用年限、预计净利用该模型，只需输入固定资产原值、使用年限、预计净 残值并选择相应的折旧方法，便会自动显示各年
2、折旧和累计折残值并选择相应的折旧方法，便会自动显示各年折旧和累计折 旧。模型会对数据输入区进行相应的有效性设置，以保证初始旧。模型会对数据输入区进行相应的有效性设置，以保证初始 数据正确。模型会使用到表单工具，其中使用年限将通过滚动数据正确。模型会使用到表单工具，其中使用年限将通过滚动 条进行设置，折旧方法通过下拉框选择，包括年限平均法、年条进行设置，折旧方法通过下拉框选择，包括年限平均法、年 数总和法和双倍余额递减法。模型会对工作表进行必要的保护，数总和法和双倍余额递减法。模型会对工作表进行必要的保护， 除基本数据输入区域之外的其他区域不允许进行任何编辑操作。除基本数据输入区域之外的其他区域
3、不允许进行任何编辑操作。 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 3.1 自动折旧模型 3.1.23.1.2模型设计模型设计 1 1设置单元格的数据有效性设置单元格的数据有效性 固定资产原值。假定固定资产原值必须是大于等于固定资产原值。假定固定资产原值必须是大于等于10001000 的小数，设置其数据有效性为“大于等于的小数，设置其数据有效性为“大于等于20002000的小数”。的小数”。 使用年限。假定使用年限必须是大于等于使用年限。假定使用年限必须是大于等于2 2小于等于小于等于3030 的整数，设置其数据有效性为“介于的整数，设置其数据有效性为“介于2 2
4、和和3030之间的整数”。之间的整数”。 预计净残值。假定预计净残值必须是大于等于预计净残值。假定预计净残值必须是大于等于0 0且小于且小于 等于原值等于原值20%20%的小数，可在数据有效性对话框中作如下设置：的小数，可在数据有效性对话框中作如下设置： 将“允许”设置为“小数”，将“数据”设置为“介于”，将“允许”设置为“小数”，将“数据”设置为“介于”， 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 3.1 自动折旧模型 将最小值设置为将最小值设置为0 0，将最大值设置为公式“，将最大值设置为公式“=B1*20%”=B1*20%”。也可以。也可以 将“允许”设置为
5、自定义，将公式设置为“将“允许”设置为自定义，将公式设置为“=AND(B2=0,B2=0,B2 $D$1,”“,A4+1)=IF(A4=”“,”“,IF(A4+1 $D$1,”“,A4+1) 本年折旧公式。本年折旧公式。 在“在“B4”B4”单元格中定义以下公式并复制到以下各单元格：单元格中定义以下公式并复制到以下各单元格： 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 3.1 自动折旧模型 =IF(A4=”“,”“,IF($D$2=1,SLN($B$1,$B$2,$D$1),IF($D$2=IF(A4=”“,”“,IF($D$2=1,SLN($B$1,$B$2,$D
6、$1),IF($D$2 =2,SYD($B$1,$B$2,$D$1,A4),VDB($B$1,$B$2,$D$1,A4=2,SYD($B$1,$B$2,$D$1,A4),VDB($B$1,$B$2,$D$1,A4- -1, 1, A4)A4) 累计折旧公式累计折旧公式 在“在“C4”C4”单元格中定义以下公式并复制到以下各单元格：单元格中定义以下公式并复制到以下各单元格： =IF(A4=“,“,IF(A4=1,B4,B4+C3)=IF(A4=“,“,IF(A4=1,B4,B4+C3) 5.5.分割窗体并冻结窗格分割窗体并冻结窗格 当使用年限过大，而折旧表无法在一屏中显示时，可以将当使用年限过大
7、，而折旧表无法在一屏中显示时，可以将 窗口在第窗口在第3 3行处进行拆分，然后在功能区“视图”选项卡中行处进行拆分，然后在功能区“视图”选项卡中 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 3.1 自动折旧模型 单击“冻结窗格”出现下拉框，再选择其中的“冻结首行”即单击“冻结窗格”出现下拉框，再选择其中的“冻结首行”即 可。可。 6.6.保护工作表保护工作表 在该模型中，只有“在该模型中，只有“B1”B1”、“、“B2”B2”、“、“D1”D1”、“、“D2”D2”等四等四 个单元格允许输入值，所以应该将这四个单元格以外的整个工个单元格允许输入值，所以应该将这四个单
8、元格以外的整个工 作表进行保护，方法是：选中这四个单元格，然后在设置单元作表进行保护，方法是：选中这四个单元格，然后在设置单元 格格式对话框的“保护”页单击“锁定”复选框取消其选中状格格式对话框的“保护”页单击“锁定”复选框取消其选中状 态，然后单击“确定”按钮。最后，对工作表进行保护即可。态，然后单击“确定”按钮。最后，对工作表进行保护即可。 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 3.2 工资管理模型 3.2.13.2.1模型功能模型功能 工资管理模型可对职工工资数据进行简单的维护、管理、工资管理模型可对职工工资数据进行简单的维护、管理、 查询和分析。该模型
9、用到的工作表包括税率表、基础资料表、查询和分析。该模型用到的工作表包括税率表、基础资料表、 工资表、工资查询表、部门分析表、人员类别分析表、数据透工资表、工资查询表、部门分析表、人员类别分析表、数据透 视表、工资条等工作表。税率表用于设置个人所得税税率表，视表、工资条等工作表。税率表用于设置个人所得税税率表， 以便自动计算代扣个人所得税。基础资料表，用于设置工资表以便自动计算代扣个人所得税。基础资料表，用于设置工资表 需要用到的部门和职工类别。工资表用于录入各职工数据，并需要用到的部门和职工类别。工资表用于录入各职工数据，并 能够自动计算应代扣的个人所得税。部门分析表用于统计分析能够自动计算应
10、代扣的个人所得税。部门分析表用于统计分析 各部门职工数据，并可制作动态图表进行图形化分析。各部门职工数据，并可制作动态图表进行图形化分析。 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 3.2 工资管理模型 人员类别分析表用于统计分析各人员类别职工数据，并可人员类别分析表用于统计分析各人员类别职工数据，并可 制作动态图表进行图形化分析。数据透视工作表用于对工资数制作动态图表进行图形化分析。数据透视工作表用于对工资数 据进行多角度的透视分析。此外，该模型利用了基本的据进行多角度的透视分析。此外，该模型利用了基本的VBAVBA知知 识自动生成工资条。识自动生成工资条。 3
11、.2.23.2.2模型设计模型设计 1.1.税率表的设置税率表的设置 输入起征点、应纳税所得额上限和税率输入起征点、应纳税所得额上限和税率 设置应纳税所得额下限设置应纳税所得额下限 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 3.2 工资管理模型 设置速算扣除数设置速算扣除数 设置数据有效性设置数据有效性 起征点，设置为大于等于起征点，设置为大于等于0 0的整数。的整数。 应纳税所得额上限应纳税所得额上限 第一级的应纳税所得额上限应该大于第一级的应纳税所得额上限应该大于0 0，以后各级应纳税，以后各级应纳税 所得额上限应该大于上一级的应纳税所得额上限。所得额上限应该
12、大于上一级的应纳税所得额上限。 税率税率 第一级的税率应该是介于第一级的税率应该是介于0 0和和1 1之间的小数，以后各级税率之间的小数，以后各级税率 应该介于上一级税率和应该介于上一级税率和1 1之间。之间。 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 3.2 工资管理模型 定义名字定义名字 起征点，将单元格起征点，将单元格C2C2表示的起征点命名为“表示的起征点命名为“qzdqzd”。 税率表，将区域“税率表，将区域“C4C4：F10”F10”命名为“命名为“slbslb”。 保护工作表保护工作表 在此工作表中，可以输入的内容有起征点、应纳税所得额在此工作表中，
13、可以输入的内容有起征点、应纳税所得额 上限和税率（应纳税所得额上限和速算扣除数根据公式自动计上限和税率（应纳税所得额上限和速算扣除数根据公式自动计 算），因此可以将这些单元格之外的整个工作表进行保护。算），因此可以将这些单元格之外的整个工作表进行保护。 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 3.2 工资管理模型 2.2.基础资料设置基础资料设置 假定工资表中包括部门和人员类别等基础信息，所以需要假定工资表中包括部门和人员类别等基础信息，所以需要 事先在工作表中输入部门和人员类别信息，以便工资表引用。事先在工作表中输入部门和人员类别信息，以便工资表引用。 将区域
14、“将区域“B2B2：B7”B7”和“和“F2F2：F4”F4”分别命名为“分别命名为“BM”BM”和“和“ZGLB”ZGLB”。 同样，可以对部门和人员类别外的单元格进行保护。同样，可以对部门和人员类别外的单元格进行保护。 3.3.工资表的设置工资表的设置 设置标题设置标题 设置数据的有效性设置数据的有效性 职工编号。在此，限定职工编号长度为职工编号。在此，限定职工编号长度为4 4位。位。 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 3.2 工资管理模型 职工姓名。在此，限定职工姓名长度介于职工姓名。在此，限定职工姓名长度介于2424之间。之间。 职工类别。设置为序
15、列，数据来源为“职工类别。设置为序列，数据来源为“= =zglbzglb”。”。 所属部门。设置为序列，数据来源为“所属部门。设置为序列，数据来源为“= =bmbm”。”。 基本工资、岗位津贴、社会保险费和住房公积金。可以基本工资、岗位津贴、社会保险费和住房公积金。可以 将这四个项目的数据限定为大于等于将这四个项目的数据限定为大于等于0 0的小数。的小数。 设置公式设置公式 应发工资。应发工资等于基本工资、岗位津贴和奖金之应发工资。应发工资等于基本工资、岗位津贴和奖金之 和，所以在“和，所以在“H2”H2”单元格中输入公式“单元格中输入公式“=E2+F2+G2”=E2+F2+G2”，然后将，然
16、后将 该公式复制到同列的以下单元格。该公式复制到同列的以下单元格。 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 3.2 工资管理模型 应纳税所得额。假定应纳税所得额等于应发工资减社会应纳税所得额。假定应纳税所得额等于应发工资减社会 保险费、住房公积金以及税法规定可扣除费用，当然应纳税所保险费、住房公积金以及税法规定可扣除费用，当然应纳税所 得额应大于等于得额应大于等于0 0。所以，在“。所以，在“K2”K2”单元格中输入公式单元格中输入公式 “=IF(H2=IF(H2- -I2I2- -J2J2- -mze=“&DATE(J2,1,1)”、 “=”=”&DATE($
17、J$20,$J$22,1)， 用于构造大于等于起始日期表达式。在用于构造大于等于起始日期表达式。在D3D3、D4D4单元格设置公式单元格设置公式 “&DATE($J$24,$J$26,1)”“&DATE($J$24,$J$26,1)”，用于构造小于下月，用于构造小于下月1 1日的日期表日的日期表 达式。达式。 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 3.3 销售管理模型 3.3.模拟运算模拟运算 (1)(1)在在A8A8单元格输入公式“单元格输入公式“=$J$18”=$J$18”，即将第一个日期设，即将第一个日期设 置为起始日期，在置为起始日期，在A9A9单元格
18、输入下个月份，然后利用序列填充单元格输入下个月份，然后利用序列填充 销售记录表中涉及的各年月，注意将涉及单元格格式设置为年销售记录表中涉及的各年月，注意将涉及单元格格式设置为年 月形式。在月形式。在B7B7单元格输入公式“单元格输入公式“=DSUM(xsjl,B6,$A$2:$D$4)”=DSUM(xsjl,B6,$A$2:$D$4)”， 该公式用于在销售记录中汇总符合筛选条件的销售收入，然后该公式用于在销售记录中汇总符合筛选条件的销售收入，然后 将该公式向右复制到将该公式向右复制到C7C7、D7D7单元格，分别计算销售成本和毛利。单元格，分别计算销售成本和毛利。 天津财经大学天津财经大学 商
19、学院会计系商学院会计系 目录目录 本节本节 3.3 销售管理模型 (2)(2)设置完毕，选中区域“设置完毕，选中区域“A7A7：D31”D31”，通过数据表（模拟，通过数据表（模拟 运算表）功能，将引用列的单元格设置为“运算表）功能，将引用列的单元格设置为“J18”J18”，J18J18单元格单元格 是以上设置的模拟运算起始年月单元格。此时，各月的销售收是以上设置的模拟运算起始年月单元格。此时，各月的销售收 入、销售成本和毛利都会被自动计算出来。入、销售成本和毛利都会被自动计算出来。 4.4.设置表单工具设置表单工具 分别设置商品列表框和销售类型单选钮，并将其单元格链分别设置商品列表框和销售类
20、型单选钮，并将其单元格链 接分别设置为接分别设置为J4J4和和J2J2。 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 3.3 销售管理模型 5.5.制作图表制作图表 根据模拟运算表结果，制作折线图并进行必要的格式设置，根据模拟运算表结果，制作折线图并进行必要的格式设置， 同时调整表单工具的位置至适合的位置。同时调整表单工具的位置至适合的位置。 最后，可将不必要显示的行列，如筛选条件行、基础数据最后，可将不必要显示的行列，如筛选条件行、基础数据 列隐藏。列隐藏。 3.3.63.3.6销售额销售额paretopareto图形分析图形分析 天津财经大学天津财经大学 商学院
21、会计系商学院会计系 目录目录 本节本节 3.3 销售管理模型 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 3.3 销售管理模型 3.3.63.3.6销售额销售额paretopareto图形分析图形分析 1.1.数据源设置数据源设置 在销售记录工作表中空白列，计算各销售次数百分比和销在销售记录工作表中空白列，计算各销售次数百分比和销 售收入百分比，如图售收入百分比，如图3 3- -2424所示。在所示。在N2N2单元格输入公式单元格输入公式 “=COUNT($F$2:F2)/COUNT($F$2:$F$65536)”=COUNT($F$2:F2)/COUNT($F$2
22、:$F$65536)”，即截至当前行，即截至当前行 的销售次数合计占销售总次数的百分比。在的销售次数合计占销售总次数的百分比。在O2O2单元格输入公式单元格输入公式 “=SUM($F$2:F2)/SUM($F$2:$F$65536)”=SUM($F$2:F2)/SUM($F$2:$F$65536)”，即截至当前行的销，即截至当前行的销 售收入合计占销售收入总额的百分比。售收入合计占销售收入总额的百分比。 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 3.3 销售管理模型 2.2.设置表单工具设置表单工具 画一个微调项，并设置其数据范围为画一个微调项，并设置其数据范围为
23、1010- -9090（即可观察的（即可观察的 销售次数百分比将介于销售次数百分比将介于10%10%和和90%90%之间），将其链接单元格设置之间），将其链接单元格设置 为任意空白单元格如为任意空白单元格如B1B1。在另一单元格如。在另一单元格如C1C1定义公式定义公式 “=B1/100”=B1/100”，并将其格式设置为百分比格式。在另一单元格，并将其格式设置为百分比格式。在另一单元格 如如D1D1设置公式“设置公式“=INDEX(=INDEX(销售记录销售记录!O2:O145,MATCH(!O2:O145,MATCH(销售额销售额 paretopareto图形图形!C1,!C1,销售记录销
24、售记录!N2:N145)”!N2:N145)”，该公式用于根据销售，该公式用于根据销售 次数百分比查找对应的销售收入百分比。次数百分比查找对应的销售收入百分比。C1C1、D1D1将作为一个系将作为一个系 列出现在图表中。另可设置一个文本框并将其与列出现在图表中。另可设置一个文本框并将其与C1C1单元格链接。单元格链接。 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 3.3 销售管理模型 3.3.制作制作XYXY散点图散点图 根据销售记录工作表中计算出的各销售次数百分比和销售根据销售记录工作表中计算出的各销售次数百分比和销售 收入百分比制作收入百分比制作XYXY散点图。
25、此时，只有一个系列。在图表上单散点图。此时，只有一个系列。在图表上单 击右键，在快捷菜单中选择“源数据”，出现对话框，在该对击右键，在快捷菜单中选择“源数据”，出现对话框，在该对 话框切换至系列选项卡，在此添加两个系列。单击“增加”按话框切换至系列选项卡，在此添加两个系列。单击“增加”按 钮，将其钮，将其X X值设置为“值设置为“=(=(销售额销售额paretopareto图形图形!$C$1,!$C$1,销售额销售额 paretopareto图形图形!$C$1)”!$C$1)”，将其，将其Y Y值设置为“值设置为“=0,1”=0,1”。继续增加。继续增加 第三个系列，并将其第三个系列，并将其X
26、 X值和值和Y Y值分别设置为“值分别设置为“= =销售额销售额paretopareto图图 形形!$C$1”!$C$1”和“和“= =销售额销售额paretopareto图形图形!$D$1”!$D$1”。 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 3.3 销售管理模型 3.3.73.3.7应收账款分析应收账款分析 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 3.3 销售管理模型 1.1.统计应收账款和坏账准备统计应收账款和坏账准备 先输入各客户名称，然后在先输入各客户名称，然后在B2B2单元格输入公式单元格输入公式 “=SUMIF(=
27、SUMIF(销售记录销售记录!$I$2:$I$65536,!$I$2:$I$65536,应收账款分析应收账款分析!$A2,!$A2,销售销售 记录记录!$J$2:$J$65536)”!$J$2:$J$65536)”，用于汇总长江商厦应收账款余额，用于汇总长江商厦应收账款余额， 在在C2C2单元格输入公式“单元格输入公式“=SUMIF(=SUMIF(销售记录销售记录!$I$2:$I$65536,!$I$2:$I$65536,应收应收 账款分析账款分析!$A2,!$A2,销售记录销售记录!$L$2:$L$65536)”!$L$2:$L$65536)”，用于汇总长江商，用于汇总长江商 厦坏账准备。可
28、将这两个公式向下复制到其他客户行即可计算厦坏账准备。可将这两个公式向下复制到其他客户行即可计算 其他客户的应收账款和坏账准备。最后，根据“其他客户的应收账款和坏账准备。最后，根据“A1A1：B7”B7”制作制作 柱形图，并进行必要的格式设置。柱形图，并进行必要的格式设置。 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 3.3 销售管理模型 2.2.账龄分析账龄分析 要分客户统计应收账款在各账龄区间的分布情况，可利用要分客户统计应收账款在各账龄区间的分布情况，可利用DSUMDSUM 函数。为此，需要先设置筛选条件函数。为此，需要先设置筛选条件, ,如下图所示：如下图所示
29、： 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 3.3 销售管理模型 然后，设置各计算公式，如图然后，设置各计算公式，如图3 3- -2727所示。以第一个公式为所示。以第一个公式为 例，其公式为“例，其公式为“=DSUM(=DSUM(xsjlxsjl,“,“应收账款应收账款“,P1:Q2)”“,P1:Q2)”，即根据，即根据 “P1:Q2”P1:Q2”筛选条件，在“筛选条件，在“xsjlxsjl”区域汇总“应收账款”列的区域汇总“应收账款”列的 合计，即可得到第一个客户合计，即可得到第一个客户3030天内的应收账款余额，其他公式天内的应收账款余额，其他公式 类似。
30、最后，根据各账龄区间的应收账款合计行制作饼图，并类似。最后，根据各账龄区间的应收账款合计行制作饼图，并 进行必要的格式设置。进行必要的格式设置。 【思考思考】如何利用模拟运算表实现对其他客户各账龄区间如何利用模拟运算表实现对其他客户各账龄区间 的应收账款？的应收账款？ 天津财经大学天津财经大学 商学院会计系商学院会计系 设计一个材料管理模型，实现对材料出入库的管理，具体设计一个材料管理模型，实现对材料出入库的管理，具体 要求如下：要求如下： 材料明细账表头项目包括材料名称和数量单位；表体包材料明细账表头项目包括材料名称和数量单位；表体包 括日期、摘要、入库数量、入库单价、入库金额、发出数量、括
31、日期、摘要、入库数量、入库单价、入库金额、发出数量、 发出单价、发出金额、结存数量、结存单价、结存金额等列。发出单价、发出金额、结存数量、结存单价、结存金额等列。 日期的录入值限定在日期的录入值限定在 系统日期当月系统日期当月1 1日至日至 当前系统日当前系统日 期之间；摘要应从下拉框中选择输入，包括采购入库、盘盈入期之间；摘要应从下拉框中选择输入，包括采购入库、盘盈入 库、生产领用、维修领用、盘亏出库；入库单价根据入库金额库、生产领用、维修领用、盘亏出库；入库单价根据入库金额 和入库数量自动计算；发出单价和发出金额根据移动加权平均和入库数量自动计算；发出单价和发出金额根据移动加权平均 目录目录 本节本节 课后习题 天津财经大学天津财经大学 商学院会计系商学院会计系 法自动计算；结存数量、结存单价和结存金额自动计算，采用法自动计算；结存数量、结存单价和结存金额自动计算，采用 移动加权平均法。移动加权平均法。 根据材料明细账记录，利用函数汇总各类出库数量，并根据材料明细账记录，利用函数汇总各类出库数量，并 据此制作饼图。据此制作饼图。 目录目录 本节本节 课后习题
