预算控制表,教我的3个表格设计技巧

作者: ladeng6666 分类: Excel 发布时间: 2019-04-11 14:08 阅读: 9,162

作者 | Skye

Excel训练营优秀学员投稿

唉~财务小姐姐一声又一声的叹息:

今年新增的预算控制表工作量增加的不是一点半点啊.

  • 每笔报销都要对应到项目里
  • 集团下面7家公司

周二审核报销单据以前是半天就可以了,现在给一天半都不够啊。

我探头一看……

脑壳痛!眼睛花!这表今年都填了3个月了,7个分公司每周4次报销,在下佩服!

财务小姐姐眼睛一瞪说到:

你不是在学Excel么,有什么办法解救我吗?救我出苦海一顿海底捞。

吃货听到海底捞脑子一热说道:

没问题,包我身上。

等等,有个要求,这个表格是领导设计的,我不能改变太大,但是又要输入方便能做到吗?

大概过了半个小时,呼叫财务小姐姐来看效果。

这个工作簿里我包含了3张表(参数表、记录表、汇总表)

你需要做的是在记录表内输入数据,汇总表就会自动统计。

假如记录表的行数超过了统计表,你只需要选中汇总表最后一行,下拉公式就可以了。你来试试,是不是比较简单。

这输入简单多了,汇总表也是经理想要的样子,可以给我讲讲怎么做的吗?

可以啊,看在一顿海底捞的情分上倾囊相授。

01 | 存在问题

原始表格的问题有4点:

1.表格类型错误:

原始表格属于阅读型表格,每次新增数据需要在16列中找到需要填写的单元格,很容易出错,或者为了避免出错来回对比很锻炼眼力。

2.分类不清:

摘要里包含了报销人、报销物品、报销备注3个项目

3.颜色填充不恰当:

区分了预算和实际支出,但是不能直观反映2者关系,反而会干扰输入、阅读

4.无用元素:

无用的框线,表格框线、填充颜色向下无限,这些无用的元素都会使文件过于庞大,我曾经将一个300+m的工作簿瘦身到20+m,只是删除了里面无用的填充色、表格框线

 

02 | 分析

以上问题,首先要解决是第一项方便输入,其他这些问题都属于表格细节优化、美化。

设计表格前,先对表格进行一下分析

这个表我将它分2部分文字、数字2大类,整理成下面的思维导图

03 | 设计表格

很多人认为Excel只是一张表格而已,但从数据处理的角度Excel也是系统,两者本质没什么区别.

系统数据有3大部分:配置参数、源数据、汇总表.

做Excel表就是做一张数据源表,和变N张分类汇总表,思维逻辑是和系统是相同的。

按照这个思维逻辑,需要参数表(数据关系,一二级菜单等)、数据源表(数据录入)、汇总表(方便阅读汇总数据)

参数表:

为了方便数据源表输入和对分类规范,将原始表的二级表头整理到参数表中,定义好 名称,以便进行数据有效性设置

定义名称

一级目录:参数表-公式选项卡-选择一级目录区域-名称管理器-勾选最左列-确定

二级目录:参数表-公式选项卡-选择二级目录区域-名称管理器-勾选首行-确定

快速定义二级目录:选择B2到H6-定位-常量-名称管理器-根据所选内容创建-首行-确定

录入表:

根据前面做的分析,设计了录入表(数据源)表头:

将摘要拆分为:报销人、报销项目、备注3个项目

对一级目录和二级目录进行数据有效性设置:

录入表-选择E列-数据选项卡-数据验证-设置-序列-=一级目录

录入表-选择E列-数据选项卡-数据验证-设置-序列-=INDIRECT($E1)

将录入表设置为超级表格:

选中表格-插入选项卡-表格

即可转换成超级表格。转换后,可以直接点击套用表格样式修改格式。

在超级表的单元格设置公式后,按回车就可以快速填充到最后一行,而且每扩展一行,会把公式和单元格格式自动复制下来,不用重新下拉设置公式。

汇总表:

优化表头,将摘要和录入表统一。拆分出报销人、报销项目、备注,将支付日期、支付情况合并入摘要部分

表头取消原来的底色填充改为偏商务的深蓝色

表内数据导入分为摘要和实际支出2部分,公式如下:

1.摘要部分函数公式:

日期:当录入表A3单元格不为空,单元格值等于A3,否则显示为空值(代表空值双引号输入应把输入法切换到英文状态)

=IF(录入表!A3<>””,录入表!A3,””)

报销人:同理

=IF(录入表!B3<>””,录入表!B3,””)

以此类推设置报销项目、备注
支付日期:

=IF(D6<>””,IF(录入表!H3<>””,录入表!H3,”未付款”),””)

数据源表未填写支付日期显示未付款

2-实际支出统计公式

实际支出汇总公式:

=SUMIFS(录入表!$G:$G,录入表!$E:$E,$G$2,录入表!$F:$F,G$3)

 

各单项支出数据(多条件求和):

=SUMIFS(录入表!$G:$G,录入表!$A:$A,$B6,录入表!$B:$B,$C6,录入表!$C:$C,$D6,录入表!$D:$D,$E6,录入表!$E:$E,$G$2,录入表!$F:$F,G$3)

这里公式比较长,解释一下:对录入表G列求和,需要满足的条件是录入表摘要各项和汇总表本行各项相等,同时录入表一二级目录也需要和汇总表一二级目录相同。

这些设置完成后,就实现了源数据输入汇总表立即体现。

这里再对G到X列表头预算和实际支出金额部分做条件格式设置


支出大于预算,填充红色 =G$4<=G$5

支出和预算差额小于500填充黄色 =(G$4-G$5)<500

04 | 知识点总结

表格设计完成,回顾一下我们涉及到的知识点有:

财务小姐姐以上讲解满意否?
满意是满意,为什么这里不用数据透视表汇总?
em……不是你要求对经理的表不要改动太大吗?而且使用函数公式汇总的好处是不需要再去透视表刷新,数据录入后实时更新

 

我是拉小登,好的表格是设计出来的

 

 

 

如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!

2条评论
  • 乐乐

    2019 年 4 月 11 日 下午 2:33

    老师又更文了,哈哈哈

    1. 61o

      2019 年 5 月 12 日 下午 9:14

      写的太经典了,我竟无言以对。

发表评论

电子邮件地址不会被公开。 必填项已用*标注