Excel在财务业务核算中的应用(基础班)讲义.doc

Excel&PPT在管理中的高级应用 Excel在财务业务核算中的应用（基础版）1Part1提高工作效率11.1.必备键盘命令11.2.自定义序列：自动生成常用列表21.3.列的自由分拆21.4.列的快速合并31.5.快速模拟海量数据用于预测或预算31.6.闪电汇总跨表数据31.7.决定成败的表结构设计31.8.绝对引用和相对引用41.9.错误提示41.10.Excel常用设置41.11.其他操作技巧4Part2财务报表与表单设计42.1.国际通用财务模版解析42.2.规范表单中的数据52.3.快速消除报表中的四舍五入差异52.4.中文大写数字公式62.5.批注与浮动提示：表单填写说明62.6.保护或隐藏公式防止修改62.7.设置报表的编辑区域与保护区域62.8.让报表变得美观易懂62.9.封面，导航与报表的封装6Part3应收账款账龄动态分析73.1.应收款过期天数计算函数73.2.自定义账龄周期分析应收账款账龄73.3.制作图表直观展示应收账款账龄8Part4设计高效的进销存84.1.整体设计思路84.2.逻辑函数与判断84.3.设计进销存统计与分析表94.4.存货安全库存警戒: 条件格式的运用10Part5固定资产管理105.1.常用折旧函数105.2.固定资产查询模版105.3.利用vlookup进行查询105.4.固定资产投资分析模型11Part6薪酬计算116.1.员工信息动态查询模版116.2.薪酬计算表116.3.隐藏错误提示116.4.利用模糊查询计算奖金126.5.定义名称实现引用12Part7业务数据统计137.1.按属性归类费用并统计137.2.统计订单的分布状况137.3.对业务数据进行高级筛选147.4.对业务数据多维动态分析14v Excel在财务业务核算中的应用 袁志刚Excel在财务业务核算中的应用（基础版）Part1 提高工作效率1.1. 必备键盘命令1. 快速选中数据表的整行或者整列：ctrl + shift + 下箭头/右箭头2. 快速选中区域：ctrl + A3. 显示公式：ctrl + 4. 一个单元格内容输入为多行：alt + 回车键5. 剪切：ctrl+X6. 复制：ctrl+ C7. 粘贴：ctrl+ V8. 显示当前日期：ctrl+；9. 切换工作表：ctrl+ pagedawn,pageup10. 从下拉列表中选择：alt+ 下箭头11. 复制上方：ctrl+d12. 复制左方： ctrl+r13. 手动重算工作表：F914. 关闭当前工作簿：ctrl+ W15. 返回区域的左上角或右下角：ctrl+ home或end16. 选定菜单栏：alt17. 完成输入并选择右侧的单元格：tab18. 撤销上一次操作：ctrl+ Z19. 复制上方公式：ctrl+20. 调出单元格格式对话框：ctrl+ 121. 加粗或取消：ctrl+ B22. 向右移动一屏：alt + pagedown23. 向左移动一屏：alt + pageup24. 移动到下一个工作簿： ctrl+ F625. 移动到前一个工作簿： ctrl+shift + F626. 调出打印窗口：ctrl + P27. 插入新工作表：shift + F1128. 调出打开对话框：ctrl + F1229. 调出查找对话框：ctrl + F30. 调出替换对话框：ctrl + H31. 创建新工作簿：ctrl + N32. 调出另存为：F1233. 调出保存：ctrl + S34. 打开定位对话框：F535.1.2. 自定义序列：自动生成常用列表Office按钮-excel选项-常用-使用excel时采用的首选项-创建用于排序和填充序列的列表-编辑自定义列表1.3. 列的自由分拆1. 等长文本的分割从字符串的左边取字符：=Left (字符串，文本长度)从字符串的右边取字符：=Right (字符串，文本长度)从字符串的中间取字符：=Mid (字符串，文本起始位置，文本长度)2. 不等长文本的分割1) 选中要进行分割的字符串区域。2) 点击“数据”“分列”，在文本分列向导中选择适合的分隔符。3) 设置需要导入的列以及放置该列的位置。1.4. 列的快速合并=Concatenate(文本1，文本2，)使用连接符&：动态表头：=ABC公司&YEAR(NOW()&年&MONTH(NOW()&月报表1.5. 快速模拟海量数据用于预测或预算=最小值+（最大值-最小值）*rand()1.6. 闪电汇总跨表数据1. 对于分布在同一文件中的表格汇总首先创建格式完全一致的汇总表的表结构，在汇总表需要汇总的项目上输入：=sum(第1个表：最后1个表！需要汇总数据的单元格)，将该公式复制到其他需要汇总的单元格即可实现整张表的汇总。2. 对于分布在不同文件中的表格汇总首先创建格式完全一致的汇总表的表结构，将光标置于起始报表项目上，选择【数据】【合并计算】“引用位置”选择各个需要被合并的报表，点击“添加”，直至所有需要被合并的报表添加完成。需要删除一张表格时，只需要在合并计算界面上选中该表格，点击删除按钮。当子表格数据发生变化需要更新汇总表数据时，只需执行：【数据】【合并计算】，进入合并计算界面后直接点击确定退出该界面即可完成刷新数据的操作。1.7. 决定成败的表结构设计1 数据与维度的区分 数据：被分析的数据 维度：分析视角2 数据列表 数据库中的表结构 特性：批量分析3 行列式表格 用于观察和查看数据 特性：难以快速处理1.8. 绝对引用和相对引用绝对引用：所引用的单元格不随着公式的复制而移动的引用方式。相对引用：所引用的单元格随着公式的复制而移动的引用方式。改变引用方式的方法：1. 在公式栏中选中需要改变引用方式的单元格，按F4键2. 给需要引用的单元格定义名称，然后在公式中引用该名称1.9. 错误提示Excel中存在错误的类型，比如1/0=#DIV/0!, todas()=#NAMES!iserror是一个逻辑函数，用以判断某个单元格内的值是否是一个错误，是错误则返回TRUE,不是错误则返回FALSE.iserror有时可以和if函数嵌套进行一些较为复杂的判断。1.10. Excel常用设置1. 鼠标移动方向office按钮excel选项高级编辑选项-按Enter键后移动所选内容2. 隐藏界面要素office按钮excel选项高级此工作簿/表的显示选项包括：网格线，滚动条，工作表标签，行号列标等。改变文件保存位置3. office按钮excel选项-保存4. 改变文件用户名office按钮excel选项-常用5. 单元格自动换行右键设置单元格对齐自动换行1.11. 其他操作技巧1. 行列互换：复制需要进行行列互换的区域后，将光标放置在数据表外面位置，选择性粘贴-选中转置-选项2. 冻结窗口：选择需要进行冻结的单元格位置，选择视图-冻结窗格-3. 快速复制公式：光标悬停在被选中单元格的右下角的黑色矩形上，双击左键4. 连续使用格式刷：双击格式刷5. 不复制隐藏的行或列首先选中需要复制的被隐藏了一些行或列的表格区域；然后点击“开始”-“查找与选择”-“定位条件”，在其中选择“可见单元格”，复制表格区域，粘贴即可。Part2 财务报表与表单设计2.1. 国际通用财务模版解析构成要素：1 假设（Asumption) 通货膨胀，税率，利率，折旧方法2 项目预测 收入（Sales)，成本(Cost)，费用(Expense)，固定资产预测(FA)3 损益表（P&L）,资产负债表（BS)，现金流量表（Cash flow） 当前数（Current) 累计数（Year to date) 实际数（Actual） 预算数（Budget）4 比较（Comparation）5 仪表盘（Dash board）2.2. 规范表单中的数据1. 选中需要设置有效性的区域，点击“数据”“有效性”，在有效性条件中选择“序列”，在来源中录入或选取列表。输入信息：事前提醒。出错警告：事后提示。如果待选列表不在当前工作表中，需要首先为该列表定义名称，然后在有效性来源框中输入：“=该列表的名称”2. 有效性的其他用法不允许录入重复数据的有效性设定：countif(e:e,e12)=1输入的内容中必须包括某字符：=not(iserror(find(中国,g30)3. 二级选项首先将一级选项的每个项目定义一个名称，该名称内容包括相应的二级项目；制作一级项目的有效性；制作二级项目的有效性：内容为：=INDIRECT(g6)，其中g6为设定了有效性的一级选项所在的单元格。2.3. 快速消除报表中的四舍五入差异office按钮excel选项高级计算此工作簿时-将精度设为所显示的精度2.4. 中文大写数字公式=IF(ROUND(C25,2)0,无效数值,IF(ROUND(C25,2)=0,零,IF(ROUND(C25,2)1,TEXT(INT(ROUND(C25,2),dbnum2)&元)&IF(INT(ROUND(C25,2)*10)-INT(ROUND(C25,2)*10=0,IF(INT(ROUND(C25,2)*(INT(ROUND(C25,2)*100)-INT(ROUND(C25,2)*10)*10)=0,零),TEXT(INT(ROUND(C25,2)*10)-INT(ROUND(C25,2)*10,dbnum2)&角)&IF(INT(ROUND(C25,2)*100)-INT(ROUND(C25,2)*10)*10)=0,整,TEXT(INT(ROUND(C25,2)*100)-INT(ROUND(C25,2)*10)*10),dbnum2)&分)2.5. 批注与浮动提示：表单填写说明利用有效性设置中的“输入信息”制作2.6. 保护或隐藏公式防止修改右键菜单【设置单元格格式】【保护】选中【隐藏】选项，然后执行对工作表的保护。隐藏工作表：【开始】【格式】【可见性】隐藏工作表后需要保护工作簿。2.7. 设置报表的编辑区域与保护区域首先取消不需要保护的单元格区域的锁定状态。右键【设置单元格格式】，在【保护】标签上将锁定选项取消。如果需要隐藏单元格公式内容，可以将隐藏选项选中。选择【审阅】【保护工作表】，输入保护密码。2.8. 让报表变得美观易懂报表结构化：通过斜体测试的报表。2.9. 封面，导航与报表的封装完整的报表应该具有封面和帮助页等相关页面。导航：利用形状和超链接进行设置。1 【插入】选项卡-【形状】-选择矩形2 右键【编辑文字】，添加报表名称3 右键【超链接】，选择“本文档中的位置”，选择一个要链接的工作表Part3 应收账款账龄动态分析3.1. 应收款过期天数计算函数1. Datedif 函数：用于计算两个日期之间的年数，月数，天数。=Datedif(开始日期，结束日期，“y”)第三个参数：“y”：表示年数“m”：表示月数“d”：表示天数“ym”: 表示整年后余下的月份数“md”: 表示整月后余下的天数2. 生日提醒=DATEDIF(TODAY(),DATE(YEAR(TODAY(),MONTH(G2),DAY(G2),d)3. 某日期的星期=weekday(A2,2) 返回的值是3，则表示该日期是星期三。4. 两个日期间的工作日天数=networkdays(开始日期，结束日期，节假日列表)需要首先加载“分析工具库”才能使用此工具。3.2. 自定义账龄周期分析应收账款账龄利用透视表进行如下的操作：1【插入】选项卡-选择【数据透视表】2 在“字段列表”工作区中，将过期天数字段拖入行标签，将金额两次拖入数值区域：3 在透视表中的行标签区域内点击鼠标右键，在右键菜单中选择“组合”，并将组合对话框中的值改为如下：4 光标放在“金额2”的列上右键，选择【值字段设置】，切换为“值显示方式”，并在列表中选择“占同列数据总和的百分比”3.3. 制作图表直观展示应收账款账龄1 光标放置在刚才制作完成的透视表内，点击上方“数据透视表工具选项卡”中的“选项”，在其中选择“数据透视图”，并选择柱形图2 选中图表，点击上方“数据透视图工具”选项卡中的“布局”左上角的图表元素选择框，在其中选择“系列“求和项：金额2”3 点击图表元素选择框下方的“设置所选内容格式”按钮，将其中的系列绘制在选项由“主坐标轴”改为“次坐标轴”：4 在图表上选中“金额2”的数据系列，点击右键，选择“更改系列图表类型”，将其图表类型改为折线图。Part4 设计高效的进销存4.1. 整体设计思路整个功能分为数据录入和统计汇总两个部分，利用透视表实现。4.2. 逻辑函数与判断1. IF(条件判断，如果条件满足则返回的结果，如果条件不满足则返回的结果)根据工龄计算员工年假。年假规则：规定：公司工龄小于1年的，享受10天年假；大于1年小于10年的，工龄每增加一年，年假增加1天；增长到20天不再增加。使用IF函数嵌套实现。单元格F3为工龄=IF(F31,10,IF(F3100000,(K3-2000)*0.45-15375,IF(K3-2000)80000,(K3-2000)*0.4-10375,IF(K3-2000)60000,(K3-2000)*0.35-6375,IF(K3-2000)40000,(K3-2000)*0.3-3375,IF(K3-2000)20000,(K3-2000)*0.25-1375,IF(K3-2000)5000,(K3-2000)*0.2-375,IF(K3-2000)2000,(K3-2000)*0.15-125,IF(K3-2000)500,(K3-2000)*0.1-25,(K3-2000)*0.05)3 自动计算加班费=IF(ISERROR(VLOOKUP(D2,$L$2:$L$12,1,FALSE),IF(OR(G2=6,G2=7),周末加班,工作日加班),节假日加班)4. 自动计算请假扣除=IF(OR(D5=病假,D5=事假),E5*1,IF(D5=旷工,E5*3,0)6.3. 隐藏错误提示=iferror(vlookup()，0)=IF(ISERROR(表达式),表达式)6.4. 利用模糊查询计算奖金Vlookup函数第4个参数为1时，是模糊查询，会匹配比目标值小的最近似值。利用该属性可以利用销售员的业绩匹配其所属的提成比例。6.5. 定义名称实现引用名称可以代表一个单元格或者一个单元格区域，或者是常量，公式。1. 名称的定义：选中需要命名的单元格或区域，在界面左上角名称框中输入名称后回车；需要注意的是：名称框只能用于定义单元格和单元格区域的名称，公式的名称需要在【公式】【名称管理器】中进行定义。2. 名称的引用：需要引用某单元格时输入为该单元格定义的名称：名称步骤1：选中需要命名的某个单元格或单元格区域。步骤2：在左上角名称框输入命名后回车。3. 删除名称：选择【公式】菜单【名称管理器】；选中需要删除的名称，点击“删除”按钮。4. 名称命名的优点：1) 避免绝对引用的错误2) 对公式进行文字化表述，让公式更加容易理解3) 可以在整个工作簿中通用，引用方便Part7 业务数据统计7.1. 按属性归类费用并统计1. 条件计数countif(range，criteria)countif(A1:A100，”8”)2. 条件求和如果满足某个条件，就对该记录里的指定数值字段求和。在第一个参数所在的区域里面查找第二个参数指定的值，找到后对第三个参数指定的字段进行求和。sumif(range, criteria, sum_range)sumif(A1:A100, “?海*”, E1:E100) 对A列中第4个字为海的E列的值求和3. 模糊条件求和sumif(a1:a100, “*”&”海”&”*”, e1:e100) 对A列中包含“海”字的E列的值求和4. sumif对多个条件进行求和=SUM(SUMIF(F:F,F2:F3,E:E)=SUM(SUMIF(F:F,陈露,程静,E:E)criteria为常量，普通公式；criteria为单元格引用，需要使用数组公式。5. sum与数组公式联手计数=sum(a1:a1001000)*(a1:a1005000)6. sum与数组公式联手求和=sum(条件1)*(条件2)*.*求和区域)7