课程设计EXCEL在会计中的应用

课程设计EXCEL在会计中的应用一、实验目的1、熟练掌握工作簿及工作表的建立。2、熟练掌握基本表格的制作。3、熟练掌握企业日常应用模型的建立。最终达到熟练应用EXCEL进行日常管理数据分析的处理。二、使用设备及软件计算机及OFFICE2003三、课程设计内容注意：1、以下内容均建立在一个工作簿中，文件名为学生的名字，以不同工作表体现。2、以下内容中的不需要计算的表格及内容需要学生按照图示在EXCEL中进行设计。3、注意数字的录入练习，主要是小键盘盲打1、已经知道12个月的销售量和产品单价，如下图，请计算每个月的销售额及月平均销售额。（第4行及第5行中的数据区要利用公式计算）（1）选取单元格区域B4:M4。（2）输入公式“=B2:M2*B3:M3”。（3）按“Crtl+Shift+Enter”组合键。（4）在单元格B5中输入公式“=AVERAGE(B2:M2*B3:M3)”，然后按“Crtl+Shift+Enter”组合键即得出月平均销售额，结果如上图。2、某企业2005年销售的3种产品的有关资料如下图所示，请计算该企业2005年的月销售总额。（第8行的数据区需要利用公式计算）（1）选取单元格区域C8:N8。（2）输入公式“=C2:N2*C3:N3+C4:N4*C5:N5+C6:N6*C7:N7”。（3）按“Crtl+Shift+Enter”组合键。3、某公司对现有三种商品实施降价销售，产品原价如下图所示，降价幅度为20%，请计算产品降价后的价格。（G3至I8用公式计算）（1）选取单元格区域G3:I8。（2）输入公式“=B3:D8*(1-20%)”。（3）按Crtl+Shift+Enter组合键。4、某商场2月份销售的家电流水记录如下图所示，利用公式计算I3、I4、I5、I6四个单元，求出分类销售额汇总。（1）单元格I3中输入公式“=SUMIF(C3:C10,211,F3:F10)”，（2）单元格I4中输入公式“=SUMIF(C3:C10,215,F3:F10)”，（3）单元格I5中输入公式“=SUMIF(C3:C10,212,F3:F10)”，（4）单元格I6中输入公式“=SUMIF(C3:C10,220,F3:F10)”5、某企业对各个销售部门的销售业绩进行评价，评价标准及各个销售部门在2005年的销售业绩汇总如下图所示，评价计算C3到C12单元。（1）选定单元格区域C3:C12。（2）直接输入以下公式：“=IF(B3:B12100000,差,IF(B3:B12200000,一般,IF(B3:B12300000,好,IF(B3:B12400000,较好,很好))))”。（3）按“Crtl+Shift+Enter”组合键。6、某企业根据各销售部门的销售额及销售费用确定奖金提成比例及提取额，若销售额大于300000元且销售费用占销售额的比例不超过1%，则奖金提取比例为15%，否则为10%，请计算D3至D10、E3至E10单元。（1）在单元格D3中输入公式“=IF(AND(B3300000,C3/B31%),15%,10%)”，将其向下填充复制到D4～C10单元格中。（2）选取单元格区域E3:E10，输入公式“=B3:B10*D3:D10”，按“Crtl+Shift+Enter”组合键。8、某企业2002年12个月的销售量与销售费用的有关数据如下图所示，请绘制各月销售额与销售费用之间关系的图表：（1）选取单元格区域A2：M3，单击工具栏上的【图表向导】按钮，或单击【插入】菜单，选择【图表】项，出现【图表向导－4步骤之1－图表类型】对话框，如图2-20所示，在【图表类型】中选【折线图】，在【子图表类型】中选【数据点折线图】，单击【下一步】按钮，出现【图表向导－4步骤之2－图表数据源】对话框，如图所示。【图表向导－4步骤之1－图表类型】对话框【图表向导－4步骤之2－图表源数据】对话框（2）在【图表向导－4步骤之2－图表源数据】对话框中，单击【系列】，在【分类（X）轴标志】栏中填入“=Sheet1!$B$1:$M$1”，单击【下一步】按钮，出现【图表向导－4步骤之3－图表选项】对话框，如图所示。【图表向导-4步骤之3-图表选项】对话框（3）在【图表向导－4步骤之3－图表选项】对话框中，对【标题】的各项进行输入，即在【图表标题】栏中填入“销售额和销售费用的变化图”，在【数值X轴】栏中填入“月份”，在【数值Y轴】栏中填入“金额（千元）”，单击【下一步】按钮，出现【图表向导－4步骤之4－图表位置】如图所示。【图表向导-4步骤之4-图表位置】对话框（4）在【图表向导－4步骤之4－图表位置】对话框中，若要建立嵌入式图表，即图表嵌入在本工作表中，则选择“作为其中的对象插入”；若要建立工作表图表，则选择“作为新工作表插入”。这里选择“作为其中的对象插入”；然后单击【完成】按钮，即得到需要的图表，如图所示。销售额与销售费用变化图9、某企业在某些省份的销售数据如下图所示，请建立数据地图。（1）选中数据区域A2:B15。（2）单击【插入】菜单，执行【对象】命令，弹出【对象】对话框，如图9－1所示，选中“Microsoft地图”，则Excel就会根据所选的工作表数据建立如图9－2所示的数据地图；在数据地图中，数据越多的区域（省份），颜色就越深。（3）在图9－2中所示的地图中没有标明省份名称，但可以通过下述方法加入省份名称：双击地图，出现地图的菜单，如图9－3所示，单击地图菜单上的【工具】，选择【标志】项，出现【地图标志】对话框，如图9－4所示；（4）在【地图标志】对话框中，【需要设置标志的地图项】中选择“中国”，【创建标志】中选择“地图项名称”，然后单击【确定】按钮。图9－1【对象】对话框图9－2某企业的销售地区分布图9－3地图菜单项图9－4【地图标志】对话框（5）在地图上移动鼠标，在鼠标移动过程中，Excel会显示该区域所对应的省份名称，单击左键，该省份名称就会标注在对应的省份区域上（图表太小，此处省略）。（6）如果需要，还可以对各省份添加数量标志，方法是：双击地图，出现【Microsoft地图控件】对话框，如图9－5所示，根据需要选择数据类型格式，用鼠标把需要的格式拖放在对话框右边区域中的格式上，然后把要设置这种格式的数据列（对话框中的【第B列】）拖放在图中的“列”字框上即可。【Microsoft地图控件】对话框提供了6种不同的数据类型格式。图9－5【Microsoft地图控件】对话框10、某企业的8个销售部门一年内各月的销售量数据如下图所示，请建立各部门的动态图表。（1）设计动态图表数据区域，如上图所示。（2）在单元格A13中输入公式“=INDIRECT(ADDRESS(CELL(row),COLUMN(A3)))”，并把该公式向右填充复制到M13中，这里COLUMN的意思是返回参数所在的列标，CELL(row)的意思是返回当前光标所在的行号，ADDRESS（行号，列标）的意思是返回由行号和列标确定的单元格，INDIRECT的意思是返回参数所确定的单元格内容；（3）选中区域A12:M13，插入“折线图”，并进行相应的格式设置，则动态图表就建立起来了。若鼠标单击A3单元格，再按F9键（即对工作表数据重新计算），就会显示部门A的销售图；若鼠标单击A5单元格，再按F9键（即对工作表数据重新计算），就会显示部门C的销售图。这样，就可以很方便地对各个销售部门的销售量进行直观的观察和分析。11、某企业四个季度的销售量统计如图所示，它们分别存放在不同的工作簿——销售统计-1.xls，销售统计-2.xls，销售统计-3.xls和销售统计-4.xls中，请将它们汇总合并到另外一张名为销售统计-年度.xls）的工作簿中。图2-58某企业四个季度的销售量统计（1）在“销售统计-年度.xls”的工作簿中选取单元格区域B3:B9。（2）单击【数据】菜单，选取【合并计算】项，则弹出【合并计算】对话框，如图所示。【合并计算】对话框（3）在【函数】项中选“求和”。（4）在【引用位置】中输入第一季度销售统计工作簿“销售统计-1.xls”的数据区域B3:B9，最好用鼠标选取，即：激活工作簿“销售统计-1.xls”，然后选取该工作表的B3:B9单元格区域，输入完毕后，单击【添加】按钮，则该单元格区域自动加入【所有引用位置】中去，并在【引用位置】的文字呈反黑显示。（5）仿照上述方法输入其他三个季度的资料，最后的结果如上图所示。（6）选中【创建连至数据源的连接】，这是为了能够查看最终汇总数据的来源，若没有这个要求，可不选此项。（7）单击【确定】按钮，则最后的合并计算结果如图所示。合并计算结果在上图中，左上角的按钮1为总的合并数据的显示按钮，按钮2为所有合并数据的明细显示按钮。在每个合并数据的左边有一个滑动按钮，单击此按钮，则将在此合并数据的上方显示隐藏的合并明细数据，同时变为显示明细按钮，再单击此按钮，则合并明细数据再度隐藏起来，同时变为。若不选中【创建连至数据源的连接】，则合并计算结果如下图所示。合并计算结果12、某企业1～9月份的总成本与人工小时及机器工时的数据如图12-1所示。假设总成本与人工小时之间存在着线性关系，请建立总成本与人工小时间的一元线性回归方程。（计算图12－1中的B13、B14、B15单元的数值）图12-1一元线性回归分析（1）单元格B13中插入公式“=INDEX(LINEST(B2:B10,D2:D10),2)”，（2）单元格B14插入公式“=INDEX(LINEST(B2:B10,D2:D10),1)”（3）单元格B15插入公式“=INDEX(LINEST(B2:B10,D2:D10,TRUE,TRUE),3,1)”，即得总成本与人工小时的一元线性回归分析方程为：Y=562.72756+4.41444X1，相关系数为R2=0.99801，如图12-1所示。说明：LINEST函数可用于一元线性回归分析，也可以用于多元线性回归分析，以及时间数列的自回归分析。当只有一个自变量x（即一元线性回归分析）时，可直接利用下面的公式得到斜率和y轴的截距值以及相关系数：斜率：INDEX（LINEST（known_y's，known_x's）,1,1）；或INDEX（LINEST（known_y's,known_x's）,1）截距：INDEX（LINEST（known_y's，known_x's）,1,2）；或INDEX（LINEST（known_y's,known_x's）,2）相关系数：INDEX（LINEST（known_y's,known_x's,true,true）,3,1）