作者 | 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……不是你要求对经理的表不要改动太大吗？而且使用函数公式汇总的好处是不需要再去透视表刷新，数据录入后实时更新

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