Excel-运营周报

此时想看7月第2周 &全部外卖平台的综合数据，只需要直接修改excel表格中最上方时间，即可自动生成~

如何利用excel函数使得运营周报可自动生成，解放周复一周的制作时间，如何看运营周报呢，后续补充~

在本章前四节的内容中，我们通过案例详细展示了四套不同用途的财务工作表的设计方法，不过这四个工作表都属于不需要编制者之外的其他人参与填写的独立编制模式。而在实际工作中，我们经常会遇到这种情况：先设计好表格分发给其他部门或下属公司，其他部门或下属公司填好后传回，然后进行汇总统计。有些表格是需要定期填报汇总的，这些表格如果设计不好，既不方便下属公司填报，也不方便集团公司汇总。那怎么设计表格才能让集团公司可以快速合并汇总数据，下属公司又能填报方便，且尽可能不重复输入数据呢？

本节我们就以逸凡公司“营运周报表”为例，对此类分发后又收回汇总的工作表进行介绍，主要介绍此类表格的设计思路。这一节是第二章第三节理念和方法的具体应用，通过本示例你将看到，原来集团数据汇总可以如此简单而快速。

一、工作需求

【案例5-5】逸凡公司为集团公司，下辖矿井十余个（为便于展示，本示例只考虑四个），公司领导每周五需要了解下属矿井本周（上周六到本周五）的营运数据：进尺、产量、销量、回款情况、煤炭发热量等指标情况及当月和年度预算完成进度。逸凡公司财务人员收集下属矿井的上述指标后统一汇总呈报给公司领导，报送的表格既要有总体汇总数，也要有各公司当期数据。

二、设计思路及应实现的功能

设计总体要求：作为集团的财务人员一定要有服务的理念，在设计表格时，除了要便于集团公司汇总，还要考虑下属矿井填列数据是否方便。汇总表格要使用公式实现下属公司填报的数据自动汇总，不必手工填列、不重复抄填数据。

1.表格布局

（1）下属公司的上报表格。

各矿井上报表格的列分别列示本月/本年实际数、本月/本年预算数、预算完成进度等，表格的行分别列示各项营运指标：开拓进尺、生产进尺、产量、销量、含税销售收入、销售回款、平均售价、平均发热量。各公司上报表格的布局参见图5-105表格的格式（本案例的示例文件请参见“营运周报”文件夹）。

考虑到数据是按周填报，并且还需要填报本月累计和本年累计数据，如果手工填列需要手工将本期数据加到上期累计数上去，填写很麻烦且容易出错。为避免手工填列这些数据，各矿井还应该增加一个台账表格，使用求和公式根据台账数据自动统计本月累计和本年累计数据。“每周台账”表格格式如图5-106所示。

在台账表格填列每周数据，台账的列就是各项营运指标。为了提高填写效率，上报表格的本周完成数直接采用查找引用公式从台账表格取数，上报表格的本月累计和本年累计数据使用求和公式自动计算台账数据生成。

（2）集团公司的汇总表格。

集团公司的汇总表格有两种选择。

方案1：所有矿井的所有指标在一张表格列示，某一指标下每个公司的数据在不同行展示，然后用一汇总行列示本指标的汇总数据。具体格式如图5-107所示。

缺点：不方便查看整体汇总数据，也不方便查看每个矿井的各项数据。

方案2：所有数据在同一工作簿列示，每个公司使用一个工作表，各公司表格结构一致，集团整体情况使用汇总表列示。

缺点：不方便对比查看某一指标各公司的情况。

两种方案各有优缺点，但是第一种方案的缺点可以使用Excel的分组、筛选功能来克服。使用分组的分级显示功能可以很方便地查看汇总数据，使用筛选功能可以很方便地查看某一矿井的各项指标。因而，第一种方案更能满足工作需求，我们采用第一种方案。

2.表格的公式

各矿井上报的表格应该实现全自动取数，上报表格只需填列周数或日期，表格自动从台账表格取数：本周数使用VLOOKUP函数进行查找引用，本月和本年累计数使用SUMIF函数进行统计。

集团公司的汇总表格应该能使用公式从各矿井上报的表格中链接取数。

3.报表的后续使用

由于营运周报是定期报表，汇总报表应能实现自动更新或方便批量翻新。即：

如果上报的表格名称是固定不变的，每周当下属公司报表上报后，将数据放入指定的文件夹，汇总表格应能自动更新。

如果上报的表格名称是按周数或日期命名，比如类似“甲公司营运周报（5/17- 5/23）”的格式，那么上报表格的名称应规范，要方便使用查找替换实现批量翻新（报表翻新的技术请参阅第二章第三节）。

三、知识点装备

在阅读本节下面的内容前，请各位读者朋友首先确认大脑中是否已经基本装备了图5-109中的相关知识点。

四、主要信息的公式设计

1.建立表格框架

根据前文所述的表格设计思路，首先要建立表格整体框架，各矿井表格的结构应该完全一致（一致性原则）。具体格式如图5-105所示。

图5-105是各矿井上报表格的样式，也是集团公司汇总表格的雏形。集团公司的汇总表由于需要在各指标行下列示各矿井的数据，故还需在各指标行下都插入四行。插入后表格样式如图5-107所示。在插入行后，表格由原来的12行变成了44行，很不方便查看整体数据，这个问题可以使用分组的分级显示功能来解决。

选定集团汇总表格的第6～9行，点击【数据】选项卡的“分级显示”组中的“创建组”按钮，将6～9行组合在一起。然后分别选定11～14行、16～19行……41～44行，重复以上步骤，分别创建组。创建组后，表格如图5-107所示，在表格行号的左侧增加了“分级显示”的按钮。如果点击“1”，表示显示第一级，则表格如图5-110所示。

如果要显示某一指标的明细数据，则点击该指标前的“+”号即可。

至此，集团汇总表格的框架和下属矿井上报表格的格式已经确定。下面我们来确定下属矿井每周数据的台账。简单来说，台账表格应将各项指标分别列示在各列，同时将第一列增加一个本周日期区间的字段即可，如前文图5-106所示。

2.编制计算公式

我们先来看下属矿井报表的公式设置。

（1）每周起止日期的自动计算。

如果每周的起止天数手工录入的话，由于每年有52周左右，周数较多，费时费力且容易出错，故应该考虑用公式来实现。用公式自动计算出起止日期，生成类似“1/1-1/3”起止日期的字符串。

由于每周的天数都是固定的七天，如果知道每周起始日期是每周的第几天就可推算出每周结束的具体日期，具体公式如下：

=每周起始日+（7-每周起始日在本周的第几天）

计算日期在本周的第几天可用WEEKDAY函数，此函数返回某日期为星期几。当使用不同的参数，当参数为2时，返回数字1（星期一）到数字7（星期日）；当参数为16时，返回数字1（星期六）到数字7（星期五）。下面以“2014-1-1”为例简单介绍此函数两种常用参数的使用。

如果每周起止日期从周一到周日，那么用函数公式“=WEEKDAY("2014/1/1",2)”可计算出它是本周的第三天；

如果每周起止日期从周六到周五，那么用函数公式“=WEEKDAY("2014/1/1",16)”可计算出它是本周的第五天。

关于此函数的参数及详细使用请参阅Excel帮助。

如前所述，逸凡公司周报统计日期是从上周六到本周五，为简化计算，同时也是为方便统计本月和本年数据，故计算起止日期增加两个辅助列（辅助列化繁为简的思想，请参见第二章第六节）。第一列为每周起始日期，第二列为每周截止日期。每年第一周的起始日期为固定的1月1日，即在A2单元格直接输入“2014-1-1”。第一周的截止日期可以用公式计算，B2单元格的公式为：

=A2+7-WEEKDAY(A2,16)

但是此公式计算周截止日期有点小问题：当周报遇到月底时应该以最后一天为截止日期，而不是以周自然天数来计算。故第一周结束日期（B2单元格）的公式应该用IF函数来进行逻辑判断：当使用上述公式计算出的日期跨月时，应该采用最后一天，否则才能使用上述公式。故此公式完善为：

=IF(MONTH(A2+7-WEEKDAY(A2,16))<>MONTH(A2),EOMONTH(A2,0), A2+7-WEEKDAY(A2,16))

然后执行列填充至本年度最后一天即可（EOMONTH函数的用法请参见第四章相关内容）。

第二周的起始日期为第一周截止日期的第二天，由于日期型数据可以用简单的加减法，故A3单元格公式为：=B2+1

然后执行列填充至本年度最后一周即可。

增加C列为每周的序号列，增加D列为起止日期列，D2公式为：

=TEXT(A2,"m/d")&"-"&TEXT(B2,"m/d")

然后拖动填充柄下拉填充。

公式解释：

“=TEXT(A2,"m/d")”是将A2单元格的日期按“月/日”格式显示，如“1/1”；如果要显示为“01/01”，则使用公式“=TEXT(A2,"mm/dd")”。

增加辅助列后每周台账表格的结构如图5-111所示。

（2）“本周完成”的公式。

如前面设计思路所述，各矿井上报表格的数据都是从“每周台账”表格取数计算生成的。“本周完成”数可使用VLOOKUP函数来查找引用。为了能直接使用VLOOKUP函数，我们应该直接在C3单元格直接输入每周的起止日期。如“5/17-5/23”，但此日期不能作为表格的列标题，表格的列标题应该为“本周完成(5/17-5/23)”的类似格式。

为同时满足两种要求，我们可以将C3单元格设置为自定义格式“"本周完成("@)”，将“5/17-5/23”直接显示为“本周完成(5/17-5/23)”。这样即可直接使用VLOOKUP查找引用，也可满足作为列标题的需要。

通过以上设置后，“上报表格”C5单元格的公式可以直接使用VLOOKUP函数：

=VLOOKUP($C$3,每周台账!$D$2:$L$62,2,0)

此公式有点小问题，不能直接下拉填充至C6:C12单元格区域。

C6和C7单元格的公式为：

=VLOOKUP($C$3,每周台账!$D$2:$L$62,3,0)

=VLOOKUP($C$3,每周台账!$D$2:$L$62,4,0)

通过观察可以发现A5:A12各项指标的顺序与“每周台账”表格各项指标的顺序完全一致，C6:C12单元格区域的公式只是引用的列不同，故为了增加公式的可拓展性（可扩展性原则），可以将C5单元格的公式修改完善为：

=VLOOKUP($C$3,每周台账!$D$2:$L$62,ROW()-3,0)

然后直接拖动填充柄下拉填充即可。

在“上报表格”C3单元格手工输入每周的起止日期容易出错，一旦录入错误，“本周完成、本月达成、累计达成”将无法计算出正确值。为避免录入错误，应该使用数据有效性来限制录入的值，并且数据有效性还提供下拉列表，不必通过键盘录入，直接用鼠标点击选取下拉值还可提高工作效率。设置数据有效性的方法：

点击【数据】选项卡→点击“数据工具”组中的“数据有效性”按钮→在d出的“数据有效性”对话框按图5-112进行设置。

（3）“本月达成”的公式。

我们知道，要计算“本月达成”可以使用SUMIF来进行条件求和。那如何来构架这个求和的条件呢？我们观察“上报表格”C3单元格和“每周台账”表格D列可以发现，直接提取起始日期的月份作为模糊求和的条件即可，即从“5/17-5/23”提取出“5/”，然后对所有以“5/”开头的记录进行求和的结果，即是5月达成数。即求和条件为：LEFT($C$3,FIND("/",$C$3))&"*"。

因而，上报表格D5单元格公式为：

=SUMIF(每周台账!$D$2:$D$62,LEFT($C$3,FIND("/",$C$3))&"*",每周台账!$E$2:$E$62)

当然，也可以使用此公式：

=SUMPRODUCT((MONTH(每周明细!A2:A62)=--LEFT(C3,FIND("/",C3)-1))*每周明细!E2:E62)

D6:D10单元格区域的公式使用以上公式类推即可。

计算平均发热量时不能简单求和，而是应该对当月发热量进行算术平均，故D12单元格的公式为：

=SUMIF(每周台账!$D$2:$D$62,LEFT($C$3,FIND("/",$C$3))&"*",每周台账!$L$2:$L$62)/SUMPRODUCT((MONTH(每周台账!A2:A62)=(--LEFT($C$3,FIND("/",$C$3)-1)))*(每周台账!L2:L62<>""))

（4）“累计达成”的公式。

要计算累计达成也是要使用SUMIF函数来求和。要统计累计达成肯定是要统计小于等于截止日期指标之和，因而，其条件应该为：

"<="&--RIGHT($C$3,LEN($C$3)-FIND("-",$C$3))

公式解释：RIGHT函数即截取C3单元格的截止日期，即“5/17-5/23”中的“5/23”，截取出来“5/23”为文本数据，不能进行运算，故还需使用两个负号将其转化为日期数据（乘以负壹，再乘以负壹，负负得正。类似于使用“选择性粘贴-运算”将文本数值转换为数字型）。

G5单元格累计达成的公式为：

=SUMIF(每周台账!$A$2:$A$62,"<="&--RIGHT($C$3,LEN($C$3)-FIND("-",$C$3)),每周台账!$E$2:$E$62)

G6:G10单元格区域的公式以此类推，不详述。平均发热量G12单元格的公式为：

=SUMIF(每周台账!$A$2:$A$62,"<="&--RIGHT($C$3,LEN($C$3)-FIND("-",$C$3)),每周台账!$L$2:$L$62)/COUNTIF(每周台账!$A$2:$A$62,"<="&--RIGHT($C$3,LEN($C$3)-FIND("-",$C$3)))

各项指标的完成进度使用类似F5单元格的公式：

=IFERROR(D5/E5,"")

（5）集团公司汇总表格的公式。

如果下属矿井每月上报的表格名称固定不变，均为“营运周报（甲公司）”这种格式，那么，集团公司汇总表格中各矿井的各项指标可以使用简单的单元格链接。如：D6单元格甲公司开拓进尺指标“本周完成”的公式为：

='[营运周报（甲公司）.xlsx]上报'!C5

D7单元格乙公司开拓进尺指标“本周完成”的公式为：

='[营运周报（乙公司）.xlsx]上报'!C5

其他公司其他指标类推。

以后每月只需将旧表格替换成最新的表格即可。但为了保证数据的正确引用，需确保以下事项：

1）各公司上报表格的工作簿名称和工作表名称保持不变，“营运周报（甲公司）”不能改成“营运周报[甲公司]”等，否则将出现链接错误，无法引用数据。

2）各公司上报表格布局不能改变，否则将出现数据引用错位。

为了防范上述事项的发生，在将上报表格分发给各矿井之前，应对工作表和工作簿加密码保护，防止下属矿井修改表格的名称和结构（安全性原则）。 *** 作方法：

先选定需要录入数据的单元格→点击右键——设置单元格格式→“保护”选项卡→将“锁定”选项前的勾去掉→点击“确定”退出（见图5-113）。

然后点击【审阅】选项卡下的“保护工作表”按钮→在d出的“保护工作表”对话框输入密码，然后确定退出（见图5-114）。

保护好各工作表后，还可设置保护工作簿， *** 作方法：点击【审阅】选项卡下的“保护工作簿”按钮→在d出的“保护结构和窗口”对话框输入密码，然后确定退出（见图5-115）。

当勾选保护“结构”选项后，将不能进行以下 *** 作：查看已隐藏的工作表；移动、删除、隐藏或更改工作表的名称；插入新工作表或图表工作表；将工作表移动或复制到另一工作簿中。

当勾选保护“窗口”选项后，将不能进行以下 *** 作：打开工作簿时，更改工作簿窗口的大小和位置；移动窗口、调整窗口大小或关闭窗口。

3.报表的后续使用

如果上报的表格名称是按周数或日期命名，比如类似“甲公司营运周报（5/17-5/23）”的格式，仍可使用简单的单元格链接。但为了能使用查找替换将汇总表表格的公式对上周表格的引用更改为对本周表格的引用。要做到以下两点：

（1）上报表格的名称应规范，同类型工作簿、工作表应使用统一格式的文件名（一致性原则）。如“甲公司营运周报（5/17-5/23）”“丁公司营运周报（5/24-5/30）”。

（2）各公司表格应放在同一文件夹下（整体性原则）。

在满足以上条件后，收集齐各矿井下一周最新报表时，将上周各公司的报表删除，将本周各公司上报的表格放入营运周报文件夹，然后打开“集团公司汇总”表格，使用查找替换，比如：查找“（5/17-5/23）”替换为“（5/24-5/30）”，即可实现报表的快速翻新，具体 *** 作请参阅第二章第三节报表翻新的技术。