我们来看个实际的例子:
系统导出的数据可能有很多列,这里我们只保留了需要的三列数据,要根据这个数据源做出下图所示的汇总表: 我不知道各位是如何完成这个汇总表的,但是我见过有人这样做:首先筛选一个客户,再筛选该客户30天以内的交易信息,选择对应的金额,从Excel右下角的状态栏看合计金额,然后填入汇总表对应的位置;再筛选该客户31~60天的交易信息……如此四次,完成一家客户的数据汇总…… 在她看来,自己已经操作得非常熟练了,完成这样一张表基本上一上午就能做完,而那些新来的人有时候一天还做不好呢。 假如懂得一些函数的运用,再会用透视表的话完成这个汇总表三分钟应该就够了,不信的话看看动画演示! 整个操作就两步: 第一步:通过公式把各笔交易欠款天数分成需要的4档。公式如下: =LOOKUP(NOW()-B2,{0,31,61,91},{'欠款0-30天','欠款31-60天','欠款61-90天','欠款90天以上'}) 第二步:利用透视表生成账龄表。 选中整个数据,插入透视表,客户简称作为行,账龄作为列,应收账款金额作为值。 下面重点为各位伙伴解释一下第一步的公式。 =LOOKUP(NOW()-B2,{0,31,61,91},{'欠款0-30天','欠款31-60天','欠款61-90天','欠款90天以上'}) (1)公式目的 公式目的只有一个,就是根据账期(当天日期与交易日期之间的天数)进行分类。实现这种分类的公式有多种,当前采用LOOKUP函数公式只是其中的一种。其他比较常见的是用IF函数来实现,但是公式会比较长,容易出现错误。 (2)LOOKUP函数公式的用法 结合本例我们来解释一下LOOKUP函数的用法。 LOOKUP函数常用格式=(查找值,查找区域,返回值)。 以本例来说,我们添加两个辅助列,F列“日期区间下限”,G列“显示内容”。F列作为查找区域,G列是要返回的值。 然后公式可以这样写:=LOOKUP(NOW()-B2,$E$2:$E$5,$F$2:$F$5) 很明显可以看到函数是由三个参数构成的。 第一参数:NOW()-B2。这部分表示当天日期与交易日期之间的间隔天数,也就是查找值。 第二参数:$E$2:$E$5,查找区间,当前区间中的数据是要查找的日期区间的下限值。比如我们需要找0-30天,下限就是0,要找31-60天,下限就是31,以此类推。第二参数必须以升序的方式存在,LOOKUP会自动判断第一参数属于哪一个对应的区间。 第三参数:$F$2:$F$5,返回值,对应查找区间要实际显示的内容。LOOKUP在判断出账期属于哪个区间后,会显示出对应的内容。 使用这个公式要注意两个地方:第二参数的区间只需要提供下限,并且是升序排列的方式。
(3)取消辅助列的公式用法 在实际使用的时候,我们可以不添加辅助列,这时候就需要把具体数据以常量数组的方式写在公式中。也就是用{0,31,61,91}取代$E$2:$E$5 ,用{'欠款0-30天','欠款31-60天','欠款61-90天','欠款90天以上'}取代$F$2:$F$5。 这里学到的LOOKUP公式属于一种自动分类套路公式。学会了它,再配合透视表的简单操作,很多原本非常麻烦的分类统计工作都可以变得非常简单。大家一定要了解、掌握这种方法! 在线咨询Excel课程 财务人的Excel速成课教学目录 你将对Excel知识体系有全面的认识,将财务常用到的Excel技巧、函数,数据透视表等知识一网打尽。 财务、会计、HR、税务、审计、销售、Excel小白等。 柳之,会计师、注册税务师、Excel爱好者,擅长用Excel解决财务工作中的各种实际问题。10年财务工作经验,注重授课内容的可操作性和实用性。 以财务工作中真实案例讲解,学完轻松应对财务工作需求,助你从零基础进阶到Excel老手。 第一章:财务人的各种花式填充 1. 发票登记表的序号填充 2. 批量添加发票填表人 3. 会计专用格式和标准财务表格零填充 4. 批量选择和补齐销售回款表数据 5. 好用到让你尖叫的金额提取妙招 6. 效率小贴士:不得不服的快手技巧 第二章:财务人规范和高效的录入技巧 1. 规范录入现金日记账 2. 效率翻倍的下拉菜单制作技巧 3. Excel数据个性化录入提示设置 4. 快速准确录入客户档案信息表 5. 效率小贴士:自动填充发票收到时间 第三章:Excel条件格式让您的报表多姿多彩 1. 通过数据条呈现销售业绩的增减 2. 通过颜色变化呈现不同数据的对比 3. 自动标识订单明细表重复数据和重点数据 4. 合同登记表录入妙招:自动为数据添加边框 5. 效率小贴士:快速核对两列数据差异 第四章:玩转数据筛选,轻松应对报表 1. 筛选常用快捷方式设置技巧 2. 筛选不可不知的断层问题解决方法 3. 按颜色筛选所需数据 4. 通过通配符筛选所需要的订单 5. 销售毛利表:渠道和毛利筛选分析 6. 高级筛选快速提取不重复数据 7. 效率小贴士:不为人知的横向筛选小妙招 第五章:应对领导的各种数据排序问题 1. 销售业绩从高到低进行排序查看 2. 同类型系列产品进行排序整理 3. 按照多条件进行排序 4. 按照产品类别分类汇总 5. 对颜色不同的数据进行排序 6. 按照自己定义的序列进行排序 7. 横向排序以及排序注意事项 8. 效率小贴士:超好用的4大跳转单元格快捷键 第六章:查找和替换的另类用法 1. 月度领料汇总表:产品数据查找替换 2. 工作表中局部数据的查找替换 3. 工资表部门精确替换技巧 4. 利用查找符号分离工资表中的姓名和手机 5. 查找单元格中值和公式的区别 6. 单元格大小写、全角半角、格式的查找应用 7. 通过查找激活表格中的公式 8. 通过查找单元格批量标注颜色 9. 通过颜色查找,对单元格求和、平均值、计数 10. 效率小贴士:巧妙使用查找进行数据转置排版 第七章:财务表格复制粘贴那些事(一) 1. 必会技巧之格式刷讲解 2. 连续标记数据颜色的方法 3. 让合并单元格既好看又好用 4. 批量隔行填充颜色,报表更美观 5. 巧妙使用格式刷隔列隐藏数据 6. 效率小贴士:迅速调整行高列宽 第八章:财务表格复制粘贴那些事(二) 1. 粘贴为数值,数据不再随公式变来变去 2. 批量调整工资的3种方法 3. 横向表格变为纵向表格 4. 选择性粘贴,让文本格式转换为数字格式 5. 让数值以万为单位显示及其原理 6. 快速核对两张表的差异 7. 快速设置和原表相同的列宽 8. 效率小贴士:快速插入随单元格大小而变动的图片 第九章:财务表格中的奇妙格式 1. 数据以万为单位显示的技巧和原理 2. 财务表格日期的各种呈现方式 3. 超快的录入产品编码、项目名称 4. 巧用Excel快速提取三级科目名称 5. 员工身份证中快速提取出生日期 6. 利用分列功能快速校正各种不规范日期 7. 解决系统导出的文本类数字格式计算问题 8. 文本和数字格式互换技巧(对应的Excel课件是空白) 9. 效率小贴士:利用SHIFT对选择区域进行调整 第十章:开启EXCEL财务应用的函数之旅 1. 认识Excel函数 2. SUM函数讲解:工资表的各项数据汇总 3. MAX和MIN函数统计当月员工最高和最低工资 4. SUBTOTAL函数及毛利表数据汇总 5. 一键对多行多列求和 6. 效率小贴士:快速对区域小计汇总金额 第十一章:财务人员必会函数之逻辑判断函数 1. 基础工资表:职工类别自动判断录入 2. IF函数多层嵌套使用 3. 利用IF函数进行区间判断调整基本工资 4. IF函数家族的另外一个函数:SUMIF函数 5. 不同的科目名称进行汇总求和 6. SUMIF函数典型的3类财务案例讲解 7. SUMIF函数的多列错位求和 8. 在SUMIF函数中使用通配符解决汇总问题 9. 效率小贴士:ROW函数自动编制行序号 第十二章:财务人员必会函数之VLOOKUP函数 1. VLOOKUP函数:查找员工对应的补贴和所属部门 2. 学函数必须掌握的绝对引用和相对引用 3. VLOOKUP函数实际应用:核对发票金额和内容 4. 导致VLOOKUP函数出错的原因及讲解 5. 固定资产折旧计算表:ROUND函数月折旧额计算 6. 把多列多行打印在一页的设置方法 7. 效率小贴士:在每页开头打印标题的方法 第十三章:EXCEL中的战斗机——数据透视表(一) 1. 插入数据透视表的两种方式 2. 数据透视表的行,列,值,筛选区域和普通表格对应关系 3. 利用字段拖拽进行分类汇总 4. 数据透视表字段的筛选 5. 字段排序的3种方法 6. 数据透视表字段的组合应用 7. 效率小贴士:批量快速新建规定名称的表格 第十四章:EXCEL中的战斗机——数据透视表(二) 1. 切片器的使用方法 2. 插入数据透视图 3. 透视表常见问题1:数字和文本格式没有区分清楚 4. 透视表常见问题2:没有及时刷新导致错误 5. 数据源更新的解决方案 6. 发送数据透视表的注意事项 7. 效率小贴士:多行多列提取不重复值 |
|