EXCEL在财务工作中的应用讲义.doc

EXCEL在财务工作中的应用第一章Excel2007的基本操作1.1认识Excel2007Excel2007是Microsoft公司新推出的Office2007办公软件中的核心部分，是Excel2003之后的升级版本。系统配置要求选择安装方式选择安装组件设置安装路径安装完成后重启系统启动Excel2007Excel2007的操作界面设置Excel2007的操作环境快速访问工具栏隐藏/显示功能区设置“最近使用的文档”数目改变Excel窗口外观颜色去除屏幕提示说明另存为低版本的Excel文件1.2工作簿的基本操作创建工作簿保护工作簿保护Excel文件工作簿的自动保存工作簿的修复1.3工作表的基本操作新建工作簿时的默认状态更改网格线冻结和拆分工作表1.4单元格的基本操作数据的整理单元格的数据分段单元格的数据分列删除重复项单元格格式设置数据录入的主要方式通过单元格手动录入通过数据编辑栏输入通过控制柄填充数据通过对话框填充序列数据有效性单元格的其他录入形式选择性粘贴插入图片插入超链接插入批注单元格的常用技巧 输入分数输入“0”开头的数字输入具有自动设置小数点的数字同时在多个单元格中输入相同的数据（注意ctrl键的配合使用）同时在多张工作表中输入相同的数据光标的快速移动快速选择区域自动设置行高与列宽单元格的保护1.5Excel 常用快捷键综合练习员工编号必须是三位，按序列填充部门必须为办公室、财务部、销售部职务必须为总经理、主管、职员学历大多数为硕士、本科联系电话批注为：必须为移动电话号码员工编号列链接该员工的简历将文件加密第二章表格美化与图表分析格式化工作表2.2图表工具2.3利用图表合理展现数据2.1格式化工作表2.1.1设置单元格格式2.1.2设置条件格式2.1.3整体设计表格外观2.1.4使用样式2.1.5自动套用表格格式2.1.1设置单元格格式1.在“开始”功能选项卡中设置2.使用“设置单元格格式”对话框进行设置3.利用浮动工具栏进行设置小技巧：添加斜线表头2.1.2设置条件格式2.1.3整体设计表格外观整体设计表格设置工作表标签颜色设置工作表背景注意：打印时底色不会被打印上。2.1.4使用样式使用样式应用内置样式新建样式2.1.5自动套用表格格式2.2图表工具2.2.1认识Excel图表图表图表是Excel重要的数据分析工具，可以直观地表现枯燥的数据信息，并反映各种财务数据的走向、趋势以及数据之间的差异，便于用户进行分析和处理。2.2.2创建和编辑图表2.3利用图表合理展现数据2.3.1制作分析图的重要原则1.数量：以量来表现大小和多少2.趋势：表达时间与数量的变化关系3.比较：将同一观点下收集的数据相互比较2.3.2表现数量随时间变化的柱形图柱形图的分类1.簇状柱形图：表现数量上的变化趋势2.堆积柱形图：通过堆积的方式体现数量上的变化趋势3.百分比堆积柱形图：将数量上的变化趋势换算成占总类别的百分比后进行比较无间隔簇状柱形图堆积柱形图百分比堆积柱形图2.3.3表现数量与时间无关的条形图条形图的分类1.簇状条形图：将数量由上而下（或相反）排列时使用2.堆积条形图：与簇状条形图相同，但要加注内容说明时使用3.百分比堆积条形图：表现时间的变迁、地区间的差别时使用簇状条形图堆积条形图百分比堆积柱形图2.3.4以曲线和转折点表现变化趋势的折线图折线图与柱形图的区别1.柱形图：重点表现某固定点的数值 2.折线图：重点表现两点间数值的变化趋势 带数据标记的折线图总结与提示1.经常使用自带的可以直接套用的表格样式或格式;2.日常工作中常用的图形：柱状图、条形图、折线图、饼图、XY散点图（注意不同图形的结合，达到更好的分析效果）；2.3利用图表合理展现数据2.3.1制作分析图的重要原则2.3.2表现数量随时间变化的柱形图2.3.3表现数量与时间无关的条形图2.3.4以曲线和转折点表现变化趋势的折线图2.3.5柱形图与折线图并用2.3.6图表中应用控件、函数2.3.5柱形图与折线图并用应用情形性质相同的数据：累计与增值率、实际业绩与平均值等数值上有差异的数据：总体和详细说明等存在相关性的资料：气温与销售额等性质相同的数据数值上有差异的数据存在相关性的资料2.3.6图表中应用控件、函数应用控件的图表应用控件、函数的图表第三章管理和分析表格中的数据3.1数据的排序3.1.1单列排序小技巧：制作工资条3.1.2多列排序3.1.3 自定义排序3.2数据的筛选3.2.1 自动筛选3.2.2 自定义筛选3.2.3 高级筛选3.3数据的分类汇总3.3.1 创建分类汇总3.3.2 清除分类汇总分类汇总分类简单分类汇总嵌套分类汇总合并计算3.4数据透视表3.4.1 创建数据透视表3.4.2 设置数据透视表的字段布局3.4.3 设置数据透视表的数值格式3.4.4 编辑数据透视表3.4.5 设置数据透视表的外观和格式3.4.6 在数据透视表中分析和处理数据3.4.7 创建和编辑数据透视图3.4.1 创建数据透视表3.4.2 设置数据透视表的字段布局3.4.3 设置数据透视表的数值格式设置数据格式设置汇总结果和显示方式3.4.4 编辑数据透视表数据透视表的选择、移动和清除操作设置数据透视表选项更改数据透视表的数据源查看数据透视表明细数据对数据透视表中的项进行组合3.4.5 设置数据透视表的外观和格式3.4.6 在数据透视表中分析和处理数据排序数据 筛选数据3.4.7 创建和编辑数据透视图综合练习 第四章文件的打印与输出4.1页面布局设置 4.1.1打印方式设置 要求：纸张方向为横向、缩放比例为90%、 纸张大小为A5、打印质量为600点/英寸 4.1.2页边距设置 4.1.3 页眉页脚设置 4.1.4 分页设置 4.2打印预览4.3 打印表格 4.3.1打印属性设置4.3.2打印区域设置 4.3.3 快速打印小技巧分页设置列标题 第五章Excel2007常用函数的使用5.1 认识公式各种运算符的含义及示例类别运算符及含义含义示例算术+（加号）加1+2（减号）减21（负号）负数1*（星号）乘2*3/（斜杠）除4/2%（百分比）百分比10%?（乘方）乘幂3?2比较=（等号）等于A1=A2（大于号）大于A1A2（小于号）小于A1=（大于等于号）大于等于A1=A2=（小于等于号）小于等于A1=A2（不等号）不等于A1A2文本&（连字符）将两个文本连接起来产生连续的文本“2009年”&“”（结果为“”）引用:（冒号）区域运算符，对两个引用之间包括这两个引用在内的所有单元格进行引用A1:D4（引用A1到D4范围内的所有单元格）,（逗号）联合运算符，将多个引用合并为一个引用SUM（A1:D1,A2:C2）将A1:D2和A2:C2两个区域合并为一个（空格）交集运算符，生成对两个引用中共有的单元格的引用A1:D 1:B4（引用A1:D1和A1:B4两个区域的交集即A1:B1）各种运算符的优先级运算符（优先级从高到低）说明：（冒号）（单个空格），（逗号）（负号）%（百分号）?（乘方）*和/+和&=、=、=、区域运算符交集运算符联合运算符负数百分比乘幂乘和除加和减连接两个文本字符串（串连）比较运算符5.2 使用公式操作方法： 控制柄 CTRL+ENTER 5.3 单元格的引用相对引用和绝对引用单元格的引用通常是为了使用某个单元格的公式，而对单元格进行标识的方式。 引用单元格能够通过所标识的单元格来快速获得公式对数据的运算。跨工作表引用 跨工作簿引用5.4 认识函数函数格式函数名（参数1，参数2，）序号分类功能简介1数据库工作表函数分析数据清单中的数值是否符合特定条件2日期与时间函数在公式中分析和处理日期值和时间值3工程函数用于工作分析4信息函数确定存储在单元格中数据的类型5财务函数进行一般的财务计算6逻辑函数进行逻辑判断或者进行复合检验7统计函数对数据区域进行统计分析8查找和引用函数在数据清单中查找特定数据或者查找一个单元格的引用9文本函数在公式中处理字符串10数学和三角函数进行数学计算5.5 使用函数函数定义 插入函数5.6 常用函数的应用 SUM函数“sum”在英语中表示“总数、总和、求和”的意思，SUM函数是用来计算某一个或多个单元格区域中所有数字的总和的求和函数。 AVERAGE函数“average”在英语中表示“平均、平均数”的意思，AVERAGE函数是用来计算指定数据集合中所有数值平均值的函数。 其语法结构为： AVERAGE（number1,number2,）MAX和MIN函数“max”/“min”是“maximum”和“minimum”的缩写。在英语中分别表示“最大量”和“最小量”的意思，分别用来返回指定区域内所有数值的最大值和最小值。 其语法结构为： MAX（number1,number2,）MIN（number1,number2,）LARGE和SMALL函数“large”/“small”是分别表示“大的”和“小的”的意思，分别用来返回指定区域内所有数值的第k个最大值和最小值。 其语法结构为： LARGE（array，k） （kn） SMALL（array，k） （kn） 注意：LARGE和MAX的区别LARGE函数的统计范围比MAX函数广，可以返回第k个最大值，而MAX函数只能返回指定数据集合中的最大值。 同理，SMALL和MIN函数也是如此。 RANK函数“rank”在英语中表示“等级、排列”的意思，用来返回指定区域内所有数值的大小排序的情况。 其语法结构为： RANK（number，ref，order）number 需要进行排位的数值 ref 数字列表数组 order 表示对ref进行排序的方式 为0或省略时表示降序，为1表示升序 IF函数“if”在英语中表示“如果，假设”的意思，用来判断真假值，再根据逻辑判断的真假值返回不同结果的函数。 其语法结构为： IF（logical_test,value_if_true,value_if_false）logical_test 判断的方式，返回值或表达式 value_if_true 表示判断结果为真时返回的值 value_if_false 表示判断结果为假时返回的值 COUNTIF函数COUNTIF函数是计算数据区域中满足给定条件的单元格数量。 其语法结构为： COUNTIF（range，criteria）range 需要计算满足条件的单元格数目的单元格区域； criteria 确定哪些单元格将被计算在内的条件。 嵌套函数 将市场部各小组按照第1季度销售合计情况进行排名，但是只显示前三名的名次。 VLOOKUP函数VLOOKUP函数是在表格数据的首列查找指定的值，并由此返回表格数据当前行其他列的值。其语法结构为： VLOOKUP（lookup_value,table_array,col_index_num,range_lookup）lookup_value 需要在表格数组第一列查找的数值； table_array 需要进行查询转置的数组或单元格区域； ol_index_num 待返回的匹配值的列序号； range_lookup 逻辑值，希望查找匹配的是精确值还是近似值，如果为true或省略，则为模糊查询；false为精确匹配。 INDEX函数 返回数组中指定单元格或单元格数组的数值。 INDEX（array,row_num,column_num） Array：单元格区域或数组常数； Row_num为数组中某行的行序号，函数从该行返回数值； Column_num为数组中某列的列序号，函数从该列返回数值。 OFFSET函数 以指定引用为参考，通过给定偏移量返回新的引用 OFFSET（reference,rows,cols） Reference：参考系的引用区域，其左上角是偏移量的起始位置； Rows：相对于参考系的起始位置上（下）偏移的行数； Cols：相对于参考系的起始位置左（右）偏移的列数； 5.7 财务管理常用函数的应用 5.7.1 折旧计算函数的使用折旧函数 直线法（SLN）年数总和法（SYD）双倍余额递减法（VDB）工作量法 5.7.2 投资预算函数的使用EFFECT函数 利用给定的名义利率和一年中复利的期数，计算实际利率 EFFECT（nominal_rate,npery） Nominal_rate：名义利率； Npery：每年的复利期数； NOMINAL函数 利用给定的实际利率和一年中复利的期数，计算名义利率 NAMINAL（effect_rate,npery） Effect_rate：实际利率； Npery：每年的复利期数； FV函数 基于固定利率和年金返回某项投资的未来值FV（rate,nper,pmt,pv,type） Rate：各期利率； Nper：表示总投资期； Pmt：年金； Pv：开始计算时已经入账的款项； Type：0或者忽略表示期末年金，1表示期初年金； PV函数 基于固定利率和年金返回某项投资的未来值PV（rate,nper,pmt,fv,type） Rate：各期利率； Nper：表示总投资期； Pmt：年金； Fv：终值； Type：0或者忽略表示期末年金，1表示期初年金； NPER函数 基于固定利率和年金返回某项投资的总投资期NPER（rate, pmt,pv,fv，type） Rate：各期利率； Pmt：年金； Pv：现值； Fv:终值； Type：0或者忽略表示期末年金，1表示期初年金； NPV函数 通过使用贴现率以及一系列未来支出和收入，返回一项投资的净现值 NPV（rate,value1,value2,） Rate：贴现率； Value1,value2,：在时间上必须具有相等的间隔，并且都是发生在期末； 5.7.3 本利函数的使用IPMT函数 基于固定利率或等额分期付款方式，用于返回某项投资在给定期次内的投资回报或贷款偿还利息部分 IPMT（rate,per,nper,pv,fv,type） Rate：各期利率； Per：用于计算其利息数额的期数； Nper：总投资期； Pv：现值； Fv：终值； Type：0或者忽略表示期末，1表示期初；PPMT函数 基于固定利率或等额分期付款方式，用于返回某项投资在某一给定期间内的本金偿还额 PPMT（rate,per,nper,pv,fv） Rate：各期利率； Per：用于计算其利息数额的期数； Nper：总投资期； Pv：现值； Fv：终值；5.7.4 报酬率函数的使用IRR函数 返回由数值代表的一组现金流的内部收益率 IRR（values,guess） Values：为数组或单元格的引用，包含用来返回的内部收益率的数字； Guess：对函数IRR计算结果的估计值； 第六章日常财务表格的制作6.1 建立企业工资管理系统制作思路：前期准备思路1.查看公司各的人事信息2.查看考勤情况3.了解当地个人所得税征收标准.起征点及个人所得税的计算方法Excel中的制作思路1.使用数据有效性建立职工人事信息表2.使用Vlookup函数建立工资基本信息3.使用Count等函数统计职工考勤信息4.使用If函数计算个人所得税，计算工资5.使用Sumif函数汇总部门工资数据6.使用数据透视表建立工资查询7.制作工资单8.生成系统首页6.1.1 创建工资统计表需要引用的基本工作表 人事信息表注意：应用“数据有效性”职工工资基本信息表注意：应用“定义名称”本月考勤表注意：应用“COUNT.COUNTIF.COUNTBLANK”函数6.1.2 创建工资统计表 工资统计表注意：应用“IF”函数6.1.3 按部门汇总工资数据部门工资统计表SUMIF函数对满足条件的单元格求和 SUMIF（rang,criteria,sum_rang） Rang：要进行计算的单元格区域； Criteria：求和的条件； Sum_rang:用于求和计算的实际单元格区域 6.1.4 工资表查询工资表查询注意：应用数据透视表6.1.5 创建工资单工资单注意：应用“VLOOKUP”函数6.1.6 创建工资管理系统首页工资管理系统首页注意：应用“超链接”6.2 建立出入库管理系统 制作思路：前期准备思路1.准备公司的物流编码信息2.了解出入库信息表的构成3.明确汇总分析的目标Excel中的制作思路1.使用数据有效性建立职工人事信息表物料编码表、出入库录入表2.使用Sumproduct函数按月汇总数据3.使用筛选进行出入库明细查询4.使用Sumproduct函数汇总库存数据5.使用数据透视表分析数据6.2.1 建立材料（产品）编码表注意：应用“数据有效性”6.2.2 建立出入库记录表注意：应用“数据有效性”6.2.3 按月汇总出入库数据SUMPRODUCT函数 返回相应的数组乘积的和 SUMPRODUCT（Array1, Array1,） Rrray：要求和的数组； *：单元格区域的交集 6.2.4 材料（产品）出入库明细查询2009年第1季度出入库明细录入表月日供应商物料编码物料名称品牌规格型号计量单位计划单价入库数量入库金额出库数量出库金额13国美电器1-1b服务器HPdr400台11000888,000 - 13苏宁电器1-1b服务器HPdr400台11000888,000 - 21软件园1-1b服务器HPdr400台11000- 111,000 22国美电器1-1b服务器HPdr400台11000- 555,000 31同方电子1-1b服务器HPdr400台11000999,000 - 33软件园1-1b服务器HPdr400台11000999,000 - 注意：应用“筛选” 6.2.5 出入库累计汇总注意：应用“SUMPRODUCT”函数 6.2.6 用数据透视表分析出入库数据注意：应用数据透视表和数据透视图 6.3成本及利润规划管理6.3.1 盈亏平衡点的预测与分析 单一产品保本点计算：本量利法：利润=销售单价*销售量- 单位变动成本*销售量- 固定成本边际贡献法：单位边际贡献=销售单价- 单位变动成本边际贡献率=单位边际贡献/销售单价保本销售量=固定成本/单位边际贡献保本销售额=固定成本/边际贡献率多产品盈亏平衡点预测：加权平均法：预计全部产品总销售额= （各产品的销售单价*各产品预计销售量）各产品的销售比重=各产品的销售额/全部产品的销售额各产品的加权边际贡献率= （各产品的边际贡献率*各产品的销售比重）综合保本点销售额=固定成本/各产品的加权边际贡献率各产品保本点销售额=综合保本点销售额*各产品的销售比重6.3.2 对影响利润的因素进行分析注意：应用“数值调节钮”窗体控件6.3.3 生产成本预测GROWTH函数给定的数据预测指数增长值。其语法结构为：GROUWTH（Known_y,nown_x,new_x,const）Known_y 满足回归曲线y=b*mx的一组已知Y值Known_x 满足回归曲线的一组已知x值new_x 一组新的X值Const 省略或true表示b参与正常计算，false表示b为1TREND函数给定的数据预测指数增长值（线性回归）。其语法结构为：TREND（Known_y,nown_x,new_x,const）Known_y 满足回归曲线y=b*mx的一组已知Y值Known_x 满足回归曲线的一组已知x值new_x 一组新的X值Const