哪一类职位最离不开 Excel?财务称第二,估计其他人不敢称第二!
每一个财务老司机,估计都是 Excel 的骨灰级玩家。没办法啊,搞错一个符号,一个数字,就可能给你发错工资,多扣几千的税。
今天分享财务人员常会碰到的 2 个难题,以及相应的解决方案。
小樱是个刚入职 1 年的财务新手。公司业务量非常的大,每天都有处理不完的账务追踪数据。
这个表格超级大,从左到右共计 44 列,这就是财务表妹经常要面对的表格……
小樱的第一难题就是:
列数太多,查看浏览超级不方便!
头痛医头,脚痛医脚的办法,当然是将暂时不想看到的数据列隐藏起来。为了能够快速隐藏和重新展开一部分数据列,表哥都知道有一个好办法:分级显示,创建组,随时可以折叠和展开。
可是,用这个表格,小樱还有一个更大的难题:
统计数据超级麻烦,每次都搞到很晚很晚才能下班。
比如,老板常常要她按月统计收款金额,还有待还款的合同有哪些,她每次都要一个个筛选,完全没有办法批量筛选。痛苦不堪。
为什么会这样呢?
还得先看一看,她的表格到底是怎么做的。前面说了,小樱的表格超级宽,为什么会这么宽呢?
原来,她的表格分成了几个部分,在标题行上用不同的颜色做了区分。
第一部分,黑色背景的合同信息,记录了每一份合同的具体金额、收款比例、已收款金额,以及未收款信息等等。
第二部分,是蓝色背景和橙色背景的发票信息,记录了每一次收款的发票开具情况。
第三部分:是收款信息,记录了每一次收款的具体日期、方式和金额。每一次收款都要三列,而现实情况是,一份合同要完整收回所有款项,通常不止一次收款,可能会分 3 次、5 次甚至更多……。这就导致,需要横跨很多列,才能把所有收款记录登记完整。
第四部分:是月度统计信息,按月分列,统计每份合同在各个月的收款情况。
能够在一份表格里,把老板需要的信息全部登记完并统计出结果,小樱是不是很厉害?
然而并不是。
这个表格看起来很有条理,很省事。但是违背了 Excel 高效应用的一个基本原则:
按用途分离!
她错误地将汇总统计表徒手设计出来,还和数据记录表混在一起,导致结构过于复杂。满足了阅读需求,却没有考虑到数据统计的便利性。这也是大部分人制作表格时,容易犯下的致命失误!
致命失误
首先想一想,这个财务表格的目的是什么?数据的大致流转过程是怎样的?
【目的】针对厂商的货款进行追踪
【数据流】如下图所示
现在主要的难题在于第 3 个环节。财务追账的过程中,有可能需要跟进很多次,才能完成把所有的款项都追回来。相应的形成多笔的开票、收款记录。
在这个过程中,有两个数据信息流:合同和收款。大事化小,小事化了。先分别看看这两个数据流应该如何进行记录。
合同数据:
每个合同代表一个订单,我们可以根据合同的编号,建立一个清单,记录所有厂商的订单信息,这些信息包括:订单日期、合同金额、商品名称、商品数量等信息。
这一点在原表格中,是没有问题的。
收款数据:
收款对应着合同中的金额,但是针对金额较大的合同,厂商可能无法一次付清,这样同一个合同,可能会有多笔的收款记录。
而小樱设计的表格结构,有 2 个致命的失误:
❶ 每1笔收款记录设计成了横向的列数据,导致无法针对收款记录进行筛选。(Excel 只能纵向筛选,无法横向筛选)
❷ 一个合同对应多笔收款记录,这种1对多的多级数据,被设计到了同 1 行中。数据的统计方向发生交叉,合同是纵向延展,而收款记录是横向延展,给数据统计造成了极大的麻烦。
上面这两个失误,究其原因,都可以归结为一点:
用阅读的思维,把统计数据设计成了,一个汇总表格。
怎么办呢?要想少加班,学更复杂的函数公式治标不治本。得从根源上解决问题,才能真正事半功倍。
追根溯源
正确的做法应该是,对这些多笔收款记录,建立一个清单。记录收款的信息包括:收款日期、收款金额、收款对应的发票、收款方式等等。
它的结构,与合同清单应该是一样的,一行登记一笔收款记录,逐行登记。每一列只记录一种信息。保证不重不漏。
你要记住一点,未来要进一步做统计分析的数据表,都这样这样的清单式表格,我们称为记录表。这类表格,有两个打死都不变的特色:
如果你学息过信息管理或数据库相关的知识,就应该对这类表格结构不陌生。
化繁为简
找到问题的根源和思路。我们从需求和数据流出发,就能找到事半功倍的解决方案。
分析数据流
根据数据流,创建记录表(统计用)
依据记录表,输出汇总表(阅读用)
分析数据层级
每一份合同有一笔完整的合同信息;
每一份合同会有多笔收款记录,而开票记录和收款记录一一对应。
这个账务追踪过程,实际上产生了 2 类数据:合同记录和收款记录。所以我们需要分别为合同、和收款,建立单独的表格。
创建记录表
我们把所有对应的信息,都横向的放在数据标题中,设计出下面的两个表格。
合同记录表如下:
收款记录表如下:
需要特别注意的是,两份记录表中都必须有合同编号。这样才能将收款记录和具体的合同对应起来,通过查询获得相应的信息。
这样的清单式表格,一行一条完整的数据记录,纵向包含所有的记录合同记录或收款记录,就可以轻松的使用筛选、函数公式,或者透视表来统计和分析数据。
输出汇总表
老板可能每个月、甚至任何时候都提出各种汇总统计:
快速筛选出,待还款的合同,以及对应的单位
按发票统计收款状况
根据时段,输出统计每个月、或者每年的收款状况
……
有了结构清晰的记录表,要做各种统计分析,就能真正事半功倍。
事半功倍
我们只需要利用表妹们日常用到的 SUMIFS 函数,透视表技巧,就可以快速输出各种统计结果。例如:
汇总每份合同的收款状态
使用SUMIFS函数,以【收款明细】表为数据源,将每个合同的收款状态汇总到合同记录表中。还可以添加条件格式,当收款达到100%时,自动标记为绿色。
统计每一份合同的应收、预收状态
使用透视表,以【收款明细】为数据源,只要折叠或展开“单位名称”字段,就可以轻松的,按单位、或者按合同统计,当前应收款,或预收款的状态。
图中,红色表示预收款,黑色表示待收款额度。
按时段统计收款状况
同样的,基于【收款明细】表创建数据透视表,使用“创建组”功能,可以轻松的是实现,按月、按年统计收款金额。
有数据透视表这个统计分析大杀器,老板想要怎样的统计报表,你都可以分分钟,甚至秒秒钟,通过点击拖拽鼠标完成。再也不用加班熬夜啦。
记录表:清单式的数据库表
汇总表:透视表自动生成的统计报表
设计一个好用的表格结构,省下半年的工作量,真的一点不夸张。把握表格的核心思想:
本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请
点击举报。