Excel在会计中的应用实验报告

精品文档山 东 * 学 院Excel在会计中的应用实验报告 学院会计学院班 级姓名学号日 期2012年9月1欢迎下载。精品文档目 录实验一 Excel基本操作1一实验目的和要求1二实验过程和步骤3三实验过程中遇到哪些问题？如何解决的？还有哪些问题？7四本次实验有何感想、体会、意见和建议？7实验二 Excel函数、公式、数据管理和分析8一实验目的和要求8二实验过程和实验步骤8三实验过程中遇到哪些问题？你是如何解决的？还存在哪些问题？14四本次实验有何感想、体会、意见和建议？14实验三 Excel在会计中应用15一实验目的和要求15二实验过程和实验步骤15三实验过程中遇到哪些问题？你是如何解决的？还存在哪些问题？18四本次实验有何感想、体会、意见和建议？18实验四 财务分析模型的设计和建立19一实验目的和要求19二实验过程和实验步骤19三实验过程中遇到哪些问题？你是如何解决的？还存在哪些问题？20四本次实验有何感想、体会、意见和建议？20 2欢迎下载。实验一 Excel基本操作一 实验目的和要求 实验目的1. 掌握Excel的基本操作，包括Excel启动的不同方法，退出Excel操作；Excel的窗口及初始屏幕各部分的功能；Excel联机帮助功能的使用方法。2. 掌握工作表的建立方法，包括建立一个新的工作薄、向工作表中输入各种类型的数据、设置数据的有效性、数据的编辑和修改、格式的编排、查找和替换单元格中的数据以及各种引用的操作；保存工作薄、工作薄密码的设置。3. 掌握工作表的编辑方法，包括工作表的各种编辑操作，有移动、复制、删除、更名、隐藏等，熟练掌握工作表的管理方法，有打开、查看多个工作薄等。4. 掌握窗口的切换、隐藏、拆分、冻结等操作。5. 掌握打印操作，包括页面设置、分页设置与调整、打印预览等。6. 会用图表的形式表示数据间的复杂关系，直观地分析统计数据，包括图标的建立步骤、图表的编辑等。7. 了解Excel的网络功能。包括创建超级链接、获取或发布数据。实验要求1“学生成绩表”的实验要求：在磁盘上建立一个文件名为“学生成绩表”的工作薄，在工作表sheet1中创建一个如表1-1所示的工作表。姓名性别高数线代英语物理语文会计学财务管理总成绩平均分张三男92807372809086梁齐男86767576828367王文女75617671868481吕梁男68806980797986王青女59738069657672岳韩男71697167746890戴卫女82816585816274杜平男73787064836592（1）练习添加记录，并用公式和函数求出每位同学的总成绩和平均分。（2）将工作表重新命名（3）将标题设置格式为黑体14号字，居中显示，颜色为红色。表格中的文字型数据居中显示。将标题调整到适当的列宽和行高。（4）将此工作表复制到同一工作簿的另一工作表中，重新命名（5）在工作表中显示平均分大于等于75且小于80的记录，然后恢复原状。（6）数据区域使用自动套用格式，最后添加一行，利用公式计算出各科成绩和全班同学总成绩的平均分。标题设为楷体，22号字，水平居中显示，并且将标题单元格所在的行高设为24，只显示姓王的成绩记录。（7）工作簿设置文件打开密码123456（8）根据每位同学的总成绩建立柱形图，并在该图中添加数据标志。（9）设置页边距页眉和页脚，并练习设置打印区域设置打印标题及其他打印选项，最后进行打印预览。2.“职工工资表”的实验要求：编号科室姓名性别基本工资职务工资奖金应发工资公积金扣款其他扣款实发工资1教务处张建兰女896300300502总务处杨审评男756200200803学生处石为国男1250400150454财务处杨芳女950150240355财务处柳丝从女645530300256学生处黄连素女1000200200847教务处王小郑男456310280258学生处华丽伟男789250270309教务处李铁牛男3543404503510学生处廖晨女11454234004511总务处赵学军男7503213002812总务处杨家辉男4503003203413学生处张德华男5602604526514总务处李楠女78025332010（1）输入职工工资表（“性别”列除外），将工作表命名为“工资表”，并计算应发工资=基本工资+职务工资+奖金。（2）利用数据的有效性，在“性别”列中只允许用户选择男或女输入每个人的性别。（3）公积金扣款计算公式为当应发工资小于1300元时，扣款为应发工资的2%，否则为5%（用IF函数），格式保留2位小数。（4）计算实发工资应发工资公积金扣款其他扣款。（5）在现有表格的下一行计算基本工资、职务工资、奖金、应发工资、公积金扣款、其他扣款和实发工资的总额。（6）将该表标题设定为“职工工资表”，合并居中，“姓名”、“性别”列水平垂直均分散对齐。（7）给表格加边框，外粗内细，外红内黑。（8）将表格内每行的高度设置为18（24像素），“编号”列宽度设为4.5（41像素）。（9）根据（5）计算的各工资项目总额，分别计算基本工资总额、职务工资总额和奖金总额占应发工资总额的比例，建立可分离的饼状图，标明具体数值。（10）按科室分类汇总实发工资、应发工资。（11）将“职工工资表”设置密码保护，整个表格的“实发工资”列以及表外的各行各列不允许鼠标到达，其它各列用户可以输入数据。（12）将“职工工资表”中的首行冻结起来，使得向下按垂直滚动条时，首行保持不动。（13）再添加若干行的数据，进行页面设置，使得浏览（或打印）时每页都显示首行标题，页脚显示页码。二 实验过程和步骤1. “学生成绩表”的实验过程和试验步骤 （1）双击Excel的图标，打开一个新的工作簿，单击保存即可。在J5中输入公式=SUM(C5：I15)，按回车键既可以得到相应的数据，再利用填充柄填充至J12，按回车键就可以得到总成绩。在K5中输入公式=AVERAGE(C5:I5)，按回车键，利用填充柄填充到K12得到所有学生的平均分。（2）双击Sheet1工作表标签，输入“学生成绩表”。（3）选择A3:K3单元格区域，单击格式工具栏相应按钮，设置字体、字号、颜色及居中。将鼠标放在行或者列之间的线上，箭头变成十字箭头时双击可以得到适合的列宽和行高。或者根据给出的数据，选中要调整的列或者行，右击选择列宽或者行高，输入相应的数据，单击“确定”就可以调整好。（4）鼠标单击“学生成绩表”标签，按住“Ctrl”键，拖动鼠标到标签的其他位置，释放鼠标；再和（2）步骤一样将其名称改为“学生成绩表2”（5）选择A4:K12单元格区域，选择【数据/筛选/自动筛选】，鼠标单击“平均分”右侧的下三角按钮，选择“自定义”，在“自定义自动筛选方式”对话框中设置相应的条件后，按【确定】按钮，显示符合条件的记录。若想恢复原状，再用鼠标单击“平均分”右侧的下三角按钮，选择“全部”即可（6）选择“2011级学生成绩表”数据所在单元格区域，单击【格式】/【自动套用格式】，出现对话框，选择相应项格式即可。选定“学生成绩表2”，选择C11单元格，输入公式“=average(C3:C10)”，在利用填充柄填充至J11即可。选中标题将它设为楷体，22号字，水平居中显示，并且将标题单元格所在的行高设为24。选择A4:K12单元格区域，选择“数据/筛选/自动筛选”，鼠标单击“姓名”右侧的下三角按钮，选择“自定义”，在【自定义自动筛选方式】”对话框中的“姓名”行中输入“王*”，按【确定】按钮，显示符合条件的记录。（7）单击【工具】/【选项】，选择“安全性”选项卡，在“打开权限密码”中输入“123456”，按【确定】按钮。或者单击【工具】/【保护】，选择“保护工作表”，输入“123456”，单击“确定”即可。（8）选定“学生成绩表2”，选择A3:A10，J3;J10单元格区域，单击常用工具栏上的【图表向导】按钮，选择“柱形图”，单击“分类轴标志”框右边的“折叠”按钮，选择A5:A12；单击【下一步】按钮，在“数据标志”选项卡中选择“值”；接下来可以进行其他设置，单击【完成】按钮即可。可以得到的以下的图表573545534541494510530525440460480500520540560580总成绩张三王文王青戴卫姓名总成绩总成绩（9）单击左上角的“文件”菜单，选择【页面设置】命令，根据相应的条件进行设置。2. “职工工资表”的实验要求和实验步骤（1） 启动Excel，在sheet1工作表中输入数据，保存工作簿，有下表工作表命名为“工资表”，选择H3，在应发工资中输入公式“=sum(E3:G3)”，并填充至H16。（2） 在M2、M3单元格中分别输入“男”和“女”，将M列隐藏（也可以部分隐藏），选择D3，单击【数据】菜单下的【有效性】命令，在“允许”框中选择“序列”，单击“来源”框右侧的“折叠”按钮，选择M2:M3，单击【确定】按钮，再利用填充柄将D3填充至D16,在右边选择相应的性别即可（3） 选择I3单元格，输入“=round(IF(H32000)”得到销售金额大于2000的数值之和在 D3中 输入公式“=SUMIF(A3:A10,A3,B3:B10)”得到对商品名称为A1的商品相对应的销售金额之和。在D5单元格中输入公式“=SUMIF(A3:A10,*A*,B3:B10)”得到商品名称包含“A”的销售金额之和。在D7单元格中输入公式“=SUMIF(A3:A10,?A2?,B3:B10)” 得到商品名称中第四五个字符为“A2”且字符总长度为6的销售金额之和。（3）COUNTIF函数某企业员工工资表如表2-6所示，根据要求计数。表2-6某企业员工工资表部门姓名基本工资提成工资实发工资财务部张景务部杨会来20005002500销售部李琴30002003200人事部原林12004001600销售部吴凤莲23006002900财务部张飞21001002200统计实发工资大于2500的人数统计财务部的人数步骤：在Excel中输入“某企业员工工资表”，在E8单元格中输入公式“=COUNTIF(E2:E7,2500)”， 统计实发工资大于2500的人数。在E9单元格中输入公式“=COUNTIF(A2:A7,A2)”， 统计财务部的人数。（4）平均函数AVERAGE函数某企业员工的工资表如表2-7所示，要求：求出所有员工总工资的平均值；表2-7某企业员工的工资表编号姓名性别职务基本工资效绩工资总工资2001李碧华女行政120040016002002林淑芬女行政120020014002003王喜育女财务150040019002004吕利萍女财务240080032002005姚启明男业务100022003200步骤：在Excel中输入“某企业员工工资表”，在G7单元格中输入公式“=AVERAGE(G2:G6)”，求出所有员工总工资的平均值。（5）DAVERAGE函数资料如上题（表2-7），要求：求出职务是“财务”的员工总工资的平均步骤：在D9和D10单元格中输入“职务”“财务”作为函数的条件项，在G8单元格中输入公式“=DAVERAGE(A1:G6,G1,D9:D10)”，求出职务是“财务”的员工总工资的平均值。（6）IF函数某企业销售提成计算表如表2-8所示，C列显示的数据是根据B列的销售额进行判断的结果，如果“销售额”大于30000，“提成额”按“销售额*0.015”计算，否则按“销售额*0.01”计算。要求：用IF函数公式计算每个人的提成额表2-8 每个人的提成额姓名销售额提成额张三25000250李四36000540王五30000300孙六45000675李七21000210步骤：在Excel中输入“某企业销售提成计算表”，在D2单元格中输入公式“=IF(B230000,B2*0.015,B2*0.001)”，然后用填充柄填充至D6，得到每个人的提成。（7）VLOOKUP函数某公司个人所得税计算表如表2-9所示，请求出每位员工应税所得额对应的税率、速算扣除数及交纳的税金。姓名应税所得额对应税率对应速算扣除数税金级数应税所得且不超过税率速算扣除数张雨370010%1052651015003%0刘平250010%10514521500450010%105李正5003%01534500900020%555王华650020%555745490003500025%10055350005500030%27556550008000035%550578000045%13505步骤：对应的税率：在Excel中输入“个人所得税计算表”，在C2单元格中输入公式“=VLOOKUP(B2,$G$2:$J$8,3)”，用填充柄填充至B5。对应的速算扣除数：在D2单元格中输入“=VLOOKUP(B2,$G$1:$J$8,4)”，用填充柄填充至D5。对应的税金： 在单元格E2中输入公式“=B2*C2-D2”，用填充柄填充E5，综上可以计算出每位员工应税所得额对应的税率、速算扣除数及交纳的税金。得到的表格如下：（8）MINVERSE和TRANSPOSE函数要求：求矩阵的逆矩阵和转置矩阵。步骤：在A1：C3单元格中输入“=2，1，3；4，1，2；2，2，3”，同时按SHIFT、CTRL和ENTER键，就可以得到相应的矩阵。 在任意处选择3行3列的区域，插入函数MINVERSE，在Array栏中选择原数组的范围，同时按SHIFT、CTRL和ENTER键就可以得到逆矩阵。 在任意处选择3行3列的区域，插入函数TRANSPOSE，在Array栏中选择原数组的范围，同时按SHIFT、CTRL和ENTER键得到转置矩阵。（9）MMULT函数要求：求矩阵和的乘积步骤：在A1：C3单元格中输入“=2，1，3；4，1，2；2，2，3”，同时按SHIFT、CTRL和ENTER键，就可以得到相应的矩阵。在D1：F3单元格中输入“=3，6，8；5，12，2；4，9，7”，同时按SHIFT、CTRL和ENTER键，就可以得到相应的矩阵。 在任意处选择3行3列的区域，插入函数MMULT, 在Array栏中选择l两个数据组的范围，同时按SHIFT、CTRL和ENTER键就可以得到乘积矩阵。 （10）函数F(X)=5X+8，利用模拟运算表的行引用和列引用两种方式分别计算X取值为-5、-4、-3、-2、-1、0、1、2、3、4、5时的F(X)的值。步骤：行引用：在Excel一行中输入x的取值-5、-4、-3、-2、-1、0、1、2、3、4、5。在行的左下角输入公式，选定包含变量和公式的上下两行的全部区域，从数据菜单下的模拟运算表，弹出模拟运算表对话框，在输入引用行的单元格中输入变量所在的第一个单元格左边的单元格，确定即可。 列引用：在Excel一列中输入x的取值-5、-4、-3、-2、-1、0、1、2、3、4、5。在列的一个变量的右上角输入公式，选定包含变量和公式的左右两列的全部区域，从数据菜单下的模拟运算表，弹出模拟运算表对话框，在输入引用列的单元格中输入变量所在的第一个单元格上面的单元格，确定即可（11）某公司2011年7月份的销售数据如表2-10所示表2-10某公司2011年7月份的销售数据日期商品单价（元）数量（台）金额（元）销售人员地区2011-7-1彩电45001045000张三济南2011-7-3空调850060510000张三济南2011-7-3彩电160080128000张三济南2011-7-8冰五青岛2011-7-10电脑68001068000王五青岛2011-7-13电脑860020172000王五青岛2011-7-18冰箱2500100250000赵六烟台2011-7-18空调230080184000赵六烟台2011-7-19空调2200100220000赵六烟台2011-7-20彩电3800120456000周七日照2011-7-24电脑860020172000李四北京2011-7-24空调260040104000李四北京2011-7-28电风扇220500110000李四北京2011-7-31冰箱280070196000李四北京要求：根据表中的数据，建立数据透视表，显示每个销售人员、各种商品的销售数量和金额；根据建立的数据透视表建立数据透视图。步骤：单击数据清单中的任一非空单元格，选择【数据】菜单中的【数据透视表和数据透视图】命令，根据提示单击下一步即可。根据题目要求拖动相应的字段，分别将销售人员、商品、销售数量、金额拖进去即可。根据向导可以做出数据透视表，得到的图表如下：三 实验过程中遇到哪些问题？你是如何解决的？还存在哪些问题？遇到的问题：函数太多，容易记混，参数不知道应该输入什么。解决办法：看课本，找笔记，自己练习。存在问题：函数太多，一个题目用多个函数时，如IF函数的嵌套，经常会出错。感觉还是应该多练习。四 本次实验有何感想、体会、意见和建议？通过这节课的学习，感觉收获很多。以前不会用这些函数，只能手工的找某些要找的东西，现在学会了这些函数，用起来已经很顺利了，感觉很好。这样可以省下很多的时间和计算量，只要函数输入和使用正确，计算就保证绝对的正确性。在工作中平时的一些查找，就会很便捷。现在才发现，知识会带来那么多的好处。正如“知识是财富”现在发现，真正地掌握一些东西，我们在玩会运用到实践中去，并不是很多人说的那样，知识学来无用。 我始终相信，只是可以改变命运，所以在校的每一天我都会好好珍惜，学好每一门课程。实验三 Excel在会计中应用一 实验目的和要求掌握建立账务处理模型的方法并学会运用该模型进行账务处理。二 实验过程和实验步骤1建立账务处理模型（1）建立账务处理工作薄（2）建立科目编码表（3）建立会计分录表（4）建立科目汇总表（5）建立科目余额表2. （1）建立账务处理工作簿第一步，启动Excel，增加一张工作表并将工作簿中的工作表Sheet1Sheet4分别命名为“科目编码表”、“会计分录表”、“科目汇总表”和“科目余额表”。第二步，保存工作簿，输入文件名为“11年01月账务处理”，单击“保存”按钮。（2）建立科目编码表第一步，A列为科目编码，B列和C列为对应的一级科目和明细科目名称。科目编码与一级科目名称按企业会计准则的要求设置。E列为现金流量代码，以现金流量表的行次为各现金流量项目的编码，F列为对应的现金流量项目。第二步，选择A3:C183，单击“插入/名称/定义”，输入名称为“编KM”，同理，将E3:F26命名为“编LL”。（3）建立会计分录表第一步，在数据汇总区E2中输入公式“=G2-H2”，目的是为了检查借贷是否相等；G2=SUBTOTAL(9,G4:G1000)；同理，在H2、J2、K2、O1设置相应的公式SUBTOTAL(9,*4:*1000)，这里的“*”代表公式所处的行号H、J、K、O。第二步，设置日期栏A5单元格的公式为：“=IF(G4+H40,IF(I4=*,A4),)”；凭证号栏B5单元格公式为：“=IF(G4+H40,IF(I4,IF(I4=*,B4+1),B4),)”；摘要栏C5单元格公式为：“=IF(G4+H4)0,IF(I4=,C4,),)”。总账科目名称栏E4单元格公式为：“=IF(ISERROR(VLOOKUP($D4,编KM,2,FALSE),VLOOKUP($D4,编KM,2,FALSE)”。明细科目名称栏F4单元格公式为：“=IF(ISERROR(VLOOKUP($D4,编KM,3,FALSE),VLOOKUP($D4,编KM,3,FALSE)”。结束标记I栏，输入“E”等非空字符表示一张凭证结束；输入“*”表示全月凭证录入完毕。设置流动标志L栏，凡属于现金流都将在此栏显示“”，L4单元格的计算公式为“=IF(MID(D4,1,4)=1001,IF(MID(D4,1,4)=1002,)”。现金流量摘要栏N4单元格公式为：“=IF(ISERROR(VLOOKUP($M4,编LL,2,FALSE),VLOOKUP($M4,编LL,2,FALSE)”。现金流量金额栏将在输入编码后自动显示金额，O4单元格公式为：“=IF(N4=,G4+H4)”第三步，将所有计算公式用填充柄复制至1000行； 第四步，将需要输入的单元格区域A4C4、D4:D1000、G4:K1000、M4:M1000设置背景色，以提示用户这些区域的数据需要人工输入。（4）科目汇总表的生成第一步， “库存现金”本月借方发生额C5单元格的公式为：“=SUMIF(会计分录表!$E$4:$E$1000,$B5,会计分录表!$G$4:$G$1000)”；D5单元格的公式为：“=SUMIF(会计分录表!$E$4:$E$1000,$B5,会计分录表!$H$4:$H$1000)”；E5单元格的公式为：“=SUMIF(会计分录表!$E$4:$E$1000,$B5,会计分录表!$K$4:$K$1000)”；F5单元格的公式为：“=SUMIF(会计分录表!$E$4:$E$1000,$B5,会计分录表!$J$4:$J$1000)”。第二步，将第5行的所有计算公式向下复制到整张表格，此时表格中会出现相应的汇总数据。（5）科目余额表的生成第一步，若存在上个月（2010年12月）的科目余额表，将其复制一份，并将其“期末余额”栏数据复制到“期初余额”栏。若不存在上个月数据，则需要先设计科目余额表的格式，并将科目编码表中的一级科目编码和总账科目名称复制到A、B列，再输入期初数据。第二步，在F6单元格中设置公式为“=SUMIF(会计分录表!$E$4:$E$1000,$B6,会计分录表!$G$4:$G$1000)”；G6单元格中设置公式为“=SUMIF(会计分录表!$E$4:$E$1000,$B6,会计分录表!$H$4:$H$1000)”；H6单元格中设置公式“=IF(C6=借,D6+F6-G6,0)”；H6单元格中设置公式“=IF(C6=贷,E6+G6-F6,0)”，并将它们分别复制到整张表格。2假设东方股份公司1月份发生以下10笔业务，利用上面建立的模型请做出相应的会计分录，并编制资产负责表利润表现金流量表。A公司1月1日，各科目的余额如表3-1所示：表3-1 东方股份公司1月1日科目余额表编码科目名称期初余额借方贷方1001库存现金4000.001002银行存款48000.001011存放同业15000.001111买入返售金融资产3000.001201应收代位追偿款62000.001301贴现资产12500.001403原材料50000.001407商品进销差价723000.001408委托加工物资55000.001431贵金属120000.001631油气资产850000.001632累计折耗120000.001701无形资产47000.001702累计摊销1703无形资产减值准备12000.002211应付职工薪酬7500.004001实收资本公积550000合 计1989500.001989500.00（1）1月3日从工商银行提取现金1000元。（2）1月10日采购100台电风扇单价100元，已入库，用工商银行存款支付。假设无增值税。（3）1月13日向长安商场销售电风扇50台，售价为120元，款项未付。假设无增值税。（4）1月15日向某客户销售电风扇30台，售价为120元，客户用工商银行支票支付款项。假设无增值税。（5）1月27日结转上两笔销售的产品销售成本，电风扇的进价为100元。（6）1月28日为各部门报销办公费用3000元，全部用工商银行的支票支付。（7）1月28日发放工资12000元，款项用工商银行支票支付。（8）1月28日向某客户销售电风扇100台售价为200元，款项未付。（9）1月28日结转产品销售成本，电风扇进价为100元。（10）1月30日收到客户欠款10000元，全部存入工行。2在会计分录表中输入以下会计分录（1）借：库存现金（1001） 1000 贷：银行存款工行（100201） 1000（2）借：库存商品（1405） 10000 贷：银行存款工行（100201） 10000（3）借：应收账款 （1122） 6000 贷：主营业务收入（6001） 6000（4）借：银行存款工行（100201） 3600 贷：主营业务收入（6001） 3600（5）借：主营业务成本（6401） 8000 贷：库存商品（1405） 8000（6）借：管理费用 （6602） 3000 贷：银行存款工行（100201） 3000（7）借：应付职工薪酬 （2211） 12000 贷：银行存款工行（100201） 12000（8）借：应收账款 （1122） 20000 贷：主营业务收入（6001） 20000（9）借：主营业务成本（6401） 10000 贷：库存商品 （1405） 10000（10）借：银行存款工行（100201） 10000 贷：应收账款 （1122） 10000三 实