EXCEL在财务业务核算中的应用

EXCEL在财务业务核算中的应用（基础版）袁志刚技巧1：键盘快捷命令1.1.1里有35个常用键盘命令，可在7-11.1透视表数据源里面练习。例如：选中行、列、查找F5定位-定位条件-公式-确定加粗取消技巧2：快速消除四舍五入1-1.3OFFICE按钮—excel选项—高级—将精度设为所显示的精度—确定CtrlA选中区域---千分位符----可以将所有数字显示为两位。技巧3：拆分与合并1-1.4文本处理分拆：选中单元格---数据---分列---分隔符号----其他.----下一步-----不列入此列----右面一列（英文名昵称、会计科目代码分拆）合并：两列的合并用shift7&例如用英文名命名邮箱=B2&”@163.com”技巧4：汇总报表前提：各表的格式相同1-3.1北京、上海、深圳等的利润表结构统一，汇总。输入=sum（北京：深圳！B2）然后将此单元格复制到相应需要汇总的单元格。技巧5：保护工作表和工作簿、隐藏工作表1-3.1模板保护不允许删除某sheet中的行、插行、改公式等。Sheet中单元格格式默认的保护—锁定，若想只更改其中几个单元格，可以选中-----设置单元格-----保护----锁定√去掉然后审阅---保护工作表----密码---确定上述仅对某sheet进行了保护，但是别人可以删除此sheet，就需要工作簿保护，则此文档所有的sheet都无法删除更改。审阅----保护工作簿---√结构和窗口----密码---确定保护工作簿并不同时保护工作表，因此如果需要双保护，要分别设置。隐藏某sheet：右键隐藏—保护工作簿—结构和窗口—密码—确定技巧6：数据表类型1-26表总体可以分为两种：数据列表交叉表数据列表是以系统思维存储，平时存储数据最好采用此方式，例如做透视表，数据源可以用数据列表格式。技巧7：工作簿外观设计2-5.1预算模板的设计，其色彩图形的搭配，可以引起观看者的注意删除网格线：ctr-lA全选视图—网格线取消ctrl1（跳出设置单元格格式）微软雅黑，加粗2-25.1公司图标插入，隐藏标签：office—excel选项—高级—此工作簿的显示选项—显示工作表标签取消。技巧8：超链接以1-3.1为例，做封面，插入sheet，插入图标或形状—矩形—线条颜色—无—右键---编辑文字若要同时复制出三个矩形，则ctrl+shift右拖北京框—右键—超链接—本文档中的位置—北京sheet—确定返回时，可以设置封面超链接，方法同上。技巧9：报表勾稽关系公式设置2-5.3财务预测各表有公式设置，balancecheck：差额为零，则可以显示为—，数字—自定义--_(*#,##0_);_(*(#,##0);_(*-_);_(@_)技巧10：大写中文金额2-大写中文金额，里面有常用函数，现成的公式。技巧11：日期计算，绝对引用3-2日期计算：日期列的每个日期，均与基准日期相减，则需要对基准日期进行绝对引用。F4为绝对引用。=$G$1-B4将光标点在G1---按F4即可。技巧12：用透视表做账龄分析将3-2日期计算中的过期天数通过绝对引用计算出来，然后插入数据透视表—将过期天数拖入行标签—金额拖入数值两次—修改为应收账款和百分比—光标放在天数列—右键组合或数据-组合—起始1终止120步长30—光标放在百分数值上—右键—值字段设置—值显示方式—占同列数据百分比由此透视表做透视图：光标放在表里—点选项—数据透视图—柱形—点击透视图—点布局—左上角图表区—系列百分比—设置所选内容格式—系列格式—选次坐标轴—选中红色柱—更改系列图表类型—选择折线—美化图：选布局—图表表题、图例等可更改。技巧13：四组数据图7-5.10指标分析-预算实际比表选中所有数据—插入柱形图—布局—图表区分别选系列—次坐标轴—点红色—选1折线图—点绿色—选3折线图注意折线图的选择，否则效果不一样设置次纵坐标轴—最大值1.4，最小值0，主要刻度0.2，次要刻度0.04，对数取消，横轴自动。技巧14：进销存数据透视表4—简洁的进销存收入成本利润透视表：行：品种数值：进货成本、销售收入、利润（改为求和项）透视表默认是整个表区，如果需要增加行或列，则点选项—更改数据源—5000-6000如果数据源中有数据改动，则点透视表，右键刷新进出透视表：行：品种列：进出数值：数量改为求和项如果想去掉纵向总计列，右键—数据透视表选项—汇总筛选—行总计取消若要计算剩余数量，则光标放在右侧的列标签---点选项—公式—计算项(名称：库存，公式=点进插入，—，点出插入)添加确定透视表的功能：对数据进行分类、汇总、浏览另外：7-11.1按销量大小划分区间，计算占百分比（点第一列，组合设置步长等）补充：（如何将有公式的单元格隐藏公式，看上去是空格？）设置单元格格式-数字-自定义-类型里面输入；；；=MID（A2，2，4）123456789=2345公式-显示公式技巧15：图表制作一般趋势分析用折线图，结构分析用饼图。案例一：相关性分析7-5.10生产成本与产量相关性表相关系数函数：=correl（B3：B8，C3：C8）图表制作比较简单：选定数据区域—插入折线图—在布局里绘图区选择系列产量—设置所选内容格式—次坐标轴—加标题—改图例位置案例二：流动性分析7-5.10流动性分析表选中速动比率和标准比率两列数据—插入折线图1—修改纵坐标设置0-3，主要刻度0.5—删除网格线--加标题—改图例位置。袁老师特殊提醒：所加的标题需要能有针对性，能大概说明图表主旨。“公司流动性下降迅速，需要补充现金”案例三：销售费用分析7-5.10销售费用分析表根据销售费用、销售收入数据，计算出销售费用指数、销售收入指数：=B3/$B$2*$E$2（绝对引用）--选中指数数据—插入折线图—修改主要刻度为100，数字选择会计专用—调整图表大小、加标题、改图例位置。“销售费用相对于销售收入大幅增长”案例四：项目进度图7-5.10项目进度表选中数据区（4列）--插入条形图（二维条形图2）--选中纵坐标—右键设置坐标轴格式—坐标轴选项勾选逆序类别—横坐标轴交叉选择最大分类—选中横坐标轴—设置坐标轴格式—坐标轴min=39630，max=39680，主要刻度=15------将红色部分（代表结束日期）删掉---纵向的网格线删掉-----选中蓝色部分（代表开始日期）右键设置数据系列格式----填充选无---删除图例----加标题----大功告成。技巧16：同比与环比透视表应用7-11.1透视表数据同比透视表：插入透视表—改数据源范围为2000-2001年—将日期拖入行标签—将收入拖入数值区三次—三列数据分别命名为：销售收入、差异、差异百分比—光标放在日期列中，右键点组合，选月和年，确定（此时行标签由日期，变为年和月）—在右侧透视表字段列表中将年由行标签拖至列标签（此时透视表格式发生改变）--光标放在2001年差异列，右键值字段设置，值显示方式选差异，基本字段选年，基本项选2000年，确定—光标放在2001年差异百分比列，右键值字段设置，值显示方式选差异百分比，基本字段选年，基本项选2000年，确定—出现几列空值，可将其隐藏，此时列标题也被隐藏了，右键选透视表选项-布局和格式-勾选合并且居中排列带标签的单元格，完成。环比透视表：插入透视表—改数据源范围为2000年—将日期拖入行标签—将销量拖入数值区三次—三列数据分别命名为：销售量、差异、差异百分比—光标放在日期列中，右键点组合，选月和年，确定，将透视表中的日期改为月份（此时保证行标签中的年在月上方）—--光标放在差异列，右键值字段设置，值显示方式选差异，基本字段选月份，基本项选上一个，确定—光标放在差异百分比列，右键值字段设置，值显示方式选差异百分比，基本字段选月份，基本项选上一个，确定，完成。技巧17：查询问题案例一：6-3.3考勤查询表查询函数有列查询VLOOKUP(vertical)，行查询HLOOKUP。从A表向B表查询，前提是有一个共同字段。应用：公式—查找与引用—VLOOKUPLookup_value:B2姓名（共同字段）Table_array:$Q$2:$R$13（绝对引用F4）查询区域（从共有字段开始，共有字段作为第一列）Col_index_num：2（查询区域的第几列）Range_lookup:0（精确查询）考勤表中查询后，无缺勤的单元格出现#N/A错误，可通过iferror函数设置解决。Iferror(value,value_if_error)=IFERROR(VLOOKUP(B2,$Q$2:$R$13,2,0),0)案例二：查询模板6-3.2共同字段为姓名，可在数据源表中命名此列，选中第一列，在左上角名称框里输入姓名在查询模板表里，去掉网格线，加标题，然后输入姓名，光标放在右边单元格，点数据—数据有效性—有效性条件允许，选序列—来源：=姓名—确定接下来通过VLOOKUP函数设置部门、职级、手机号等。首先将数据源的所有数据选定（ctrl+shift+↓），在名称框命名为data。部门：=VLOOKUP(D6,data,7,0)手机：=VLOOKUP(D6,data,3,0)其他项目同理。生日的单元格格式为日期。技巧18：财务函数固定资产折旧函数5-8.1（一般公司有专用的软件，函数在预算预测中应用）首先右键命名单元格：原值、残值、使用年限直线法：=SLN(cost,salvage,life)累计折旧=SUM(B$6:B8)双倍余额递减法：=DDB(cost,salvage,life,period)年数总和法：=SYD（cost,salvage,life,per）固定资产投资模型：5-10.4首先设置折旧方法下拉菜单：数据—数据有效性—允许序列，来源：直线法，双倍余额递减法，年数总和法（英文逗号，直线法前面没有=，在查询模板时=姓名）改变折旧方法，第21行的值会改变，用IF函数嵌套=IF($C$7=直线法,SLN(cost,salvage,life),IF($C$7=双倍余额递减法,DDB(cost,salvage,life,I11),SYD(cost,salvage,life,I11)))另外：6-3.3个人所得税的函数，也是用IF函数嵌套NPV=（折现率，现金流量值….）=(rate,values())IRR=(values,guess)guess为函数计算结果的估计值。（6-12.3人力资源仪表盘待以后学习）技巧19：统计函数SUMSUMIFSUMIFSCOUNTCOUNTIFCOUNTIFSAVERAGEAVERAGEIFAVERAGEIFSMAXMIN条件求和：=SUMIFS(求和区域，条件区域1，条件1，条件区域2，条件2，…..)举例：7-4.3统计函数-销售统计表1.求USB线在上海，MODEL卡在浙江的金额1095=SUM(SUMIFS(D2:D100,B2:B100,{USB线,MODEL卡},C2:C100,{上海,浙江}))2.求USB线和MODEL卡，在上海和浙江的金额4495=SUM(SUMIFS(D2:D100,B2:B100,{USB线,MODEL卡},C2:C100,{上海;浙江}))注：上海和浙江之间的逗号改为分号，则为“和”。各标点符号均为英文状态下的。个人认为用透视表可更快捷求和：行标签：产品列标签：地区数值：求和项金额