Excel财务管控58项实战技术（PDF56页）

Excel财务管控实战1/56品牌经典课程Excel技高一筹Excel财务管控58项实战技术解析李则见授课资深实战型Excel培训讲师Excel财务应用解决方案专家职场办公效能情境提升教练目录案例一.ERP导出的数据整理与高筛术....................................................................................2案例二.月工资与个人所得税计算...........................................................................................4案例三.资产明细汇总，透视还是函数看你怎么选...............................................................6案例四.算津贴，VLOOKUP和MATCH的配合亲密无间........................................................8案例五.企业银行对账，方法各有千秋却无万能...................................................................9案例六.净利润求和不要这样连加.........................................................................................13案例七.按科目代码前4位模糊查询，录制宏代码.............................................................14案例八.应收账款的账龄统计分析模板.................................................................................18案例九.收入成本费用利润分析，单选式动态分析图.........................................................19案例十.ERP系统导出的不规范表格，求和是个麻烦的事..................................................22案例十一.不同的报表结构设计，考验函数公式的灵活性.................................................23案例十二.多表费用数据汇总，多重透视不可错过.............................................................25案例十三.利润表滚动跟踪分析模板.....................................................................................30案例十四.两年各月分店利润表如何汇集到总表里.............................................................38案例十五.统计物料出库结存数与获取出库的首末日期.....................................................39案例十六.各部门全年费用统计分析柱形图制作全过程.....................................................40案例十七.MQ先生您好，他的魅力简直帅爆了..................................................................42案例十八.MQ先生您好：从多个相同结构的表查询结果..................................................50案例十九.折线面积组合图，OFFSET函数动态选取范围....................................................51案例二十.管理费用跟踪分析图，动态多图分析.................................................................54Excel财务管控实战2/56案例一.ERP导出的数据整理与高筛术1)ERP里导出的数据不规范，如何快速整理选中A列，数据，分列，分隔符号，下一步，空格，完成，具体见下图所示2)高级筛选技术里条件区域的设置及相关规则1.1筛出表1里，选中一个空单元格，数据，高级Excel财务管控实战3/561.2高级筛选对话框，方式是“将筛选结果复制到其他位置”，列表区域选择前面的处理表，条件区域选择当前表里的条件区域A1：C2，复制到选择当前表里的一个空单元格，确定1.3筛出2表的制作方法同上，略。1.4这里提出一个思考题，请问高级筛选里的复制到文本框，选择一个空单元格，和选择一个有内容的标题行区域，到底有什么不同？请思考并记录老师的解释。记录你的思考与老师的解释：Excel财务管控实战4/56案例二.月工资与个人所得税计算1)制作对应关系表，你有什么思考2)IF函数可以换成其他函数吗3)VLOOKUP函数的精确查询和近似查询究竟什么区别精确查找：VLOOKUP(找谁，在哪里找，结果在第几列，0)近似查找：VLOOKUP(找谁，在哪里找，结果在第几列，1)Excel财务管控实战5/56下图里的F2单元格，用的就是精确查找L2和M2单元格都用的是近似查找，具体参考下图公式4)单元格引用锁定是有心法的：在行锁行，在列锁列，在格锁格。上下行变，左右列变，自由不锁。起点锁死，终点锁死，区域固定。Excel财务管控实战6/56案例三.资产明细汇总，透视还是函数看你怎么选1)用透视表对资产明细表进行简单透视汇总1.1鼠标选择资产明细表里的任一非空单元格，插入，数据透视表1.2确定后自动生成一个新的工作表，此工作表就是数据透视表。透视表分3个部分，左边是透视表布局；右侧右上是字段列表，右下是4个区域。可以从字段列表里拖拽字段到右下或左边的布局里。但透视表布局，建议右键单击选中透视表选项，勾选显示里的经典布局。Excel财务管控实战7/561.3把成本中心字段拖拽到行标签，把本月折旧和累计折旧字段拖拽到值区域↓Excel财务管控实战8/562)也可以用SUMIF求和函数来个单条件求和单条件求和函数SUMIF语法结构：SUMIF(条件区域，条件值，求和区域)案例四.算津贴，VLOOKUP和MATCH的配合亲密无间1)MATCH函数定位位置，配角也能出彩，近似匹配要求升序怎么来理解精确定位：MATCH（定位谁，哪行或哪列，0）近似定位，升序：MATCH（定位谁，哪行或哪列，1）Excel财务管控实战9/56请记录老师讲解的函数嵌套的步骤，和你的感觉：案例五.企业银行对账，方法各有千秋却无万能1)多重合并透视技术，帮你完成一对一对账1.1ALT→D→P，透视表向导，多重，下一步，下一步1.2选定区域，分别选择公司和银行表，然后添加，下一步，当前表，完成Excel财务管控实战10/561.3然后按照下图拖拽字段到对应区域里↓。具体操作细节，请记录。2)合并计算功能比较数据差异选一空单元格，数据，合并计算，添加数据区域，勾选首行和最左列，确定Excel财务管控实战11/563)函数公式比较两表数据差异1.1先在sumifs表里，把两个标识列粘贴一起后，删除重复项，得到唯一的标识，作为H列的内容1.2然后分别用函数SUMIF或SUMIFS来求和多条件求和函数：SUMIFS(求和区域，条件区域1，条件值1，…)在countifs表里，用countif或countifs函数来统计次数单条件计数函数：COUNTIF(条件区域，条件)多条件计数函数：COUNTIFS(条件区域1，条件1，…)4)一对多的数据比较，辅助列的意义不一般1.1先制作次序辅助列，比如B2=COUNTIFS(D$2:D2,D2)，然后科目发生额和次序连接一起，A2=D2&-&B21.2M列的标识，是用A列和F列的标识粘贴一起后删除重复项得到的唯一值，Excel财务管控实战12/56然后分别用VLOOKUP来从左表里查询，=IFERROR(VLOOKUP(M2,$A:$D,4,0),0)Excel财务管控实战13/56案例六.净利润求和不要这样连加1)SUMPRODUCT函数也可条件求和多条件求和函数：SUMPRODUCT（（条件区域1=条件值1）*1，…，求和区域）2)SUMIF函数能搞定这个问题吗增加辅助列A2单元格，=LEN(B2)D123单元格里，=-SUMIF($A$2:$A$118,4,D$2:D$118)Excel财务管控实战14/56案例七.按科目代码前4位模糊查询，录制宏代码1)定义名称制作下拉框选项选中科目代码表里的I1：I15，公式，根据所选内容创建，首行勾选，确定2)录制宏工具怎么使用，你也可以制作简单的程序录制宏工具在视图，宏，见下图也可以在开发工具里找到，不过要先调出开发工具Excel财务管控实战15/562007版本：Office按钮，Excel选项，常用，开发工具打勾2010及以上其他版本：文件，选项，自定义功能区，勾选开发工具，下图示3)录制一段高级筛选的宏代码，然后执行并保存1.1准备好查询表的表头部分的内容Excel财务管控实战16/561.2A2单元格有效性，数据有效性（2013版本及以上是，数据验证），序列，=代码；B2单元格，=A2&*1.3第4行是标题行，从凭证明细里复制粘贴过来的1.4开发工具，录制宏，确定，下图1.5鼠标选择一个空单元格，数据，高级，如下图设置Excel财务管控实战17/561.6确定后，得到筛选结果，然后立即单击开发工具，停止录制宏1.7按ALT+F11，可以进入代码编辑器界面，结果如下图1.8开发工具，插入，表单控件，按钮控件，右键单击按钮，指定宏，选择刚才录制的那个宏，确定1.9以后每选择一次代码，即可立即单击上面那个控件按钮执行宏程序里，数据筛选结果立即就出现在下面。最后文件要保存为xlsm格式或者xls格式。Excel财务管控实战18/56案例八.应收账款的账龄统计分析模板1)定义名称用于查询的函数公式里1.1先准备好区间表，要注意的是B列的分区是分行的，见截图1.2发票清单表里，J2单元格公式，=VLOOKUP(I2,区间,2,1)其中，区间是定义好的一个名称，区间指的是区间表的A2：B12Excel财务管控实战19/562)账龄分析报表用这样的高级多条件求和函数就搞定了B4单元格，=SUMPRODUCT((单位名称=$A4)*1,(账龄=B$3)*1,欠款额)多条件求和函数：SUMPRODUCT(（条件区域1=条件值1）*1，…，求和区域)其中的=，可以换成其他比较运算符，比如、=、、=、等。案例九.收入成本费用利润分析，单选式动态分析图1)开发工具里插入单选控件以及控件设置1.1开发工具，插入，选项按钮，划4个单选按钮，如下图Excel财务管控实战20/561.2右键单击任一控件，设置控件格式，单元格链接是M2单元格1.3在M4单元格写公式：=CHOOSE($M$2,C4,D4,E4,F4)选择函数的语法：CHOOSE（序号，值1，值2，…）Excel财务管控实战21/561.4在O2单元格写下公式：=M3&-&N3&N2&同比分析1.5选中L3：N15，插入，图表，折线图，调整格式等。具体细节，请听课堂讲解。Excel财务管控实战22/56案例十.ERP系统导出的不规范表格，求和是个麻烦的事1)系统导出的数据不规则，要找到规律去破解，用5个函数联合嵌套解决多条件求和，挑战你的函数功底1.1报表里，C3单元格写下很长的SUMPRODUCT函数公