夜雨聆风
EXCEL|周报看板1,自动按选定期间进行数据分析和展示
周报相比日报而言，除了要展示时点静态数据的对比分析，还要兼顾时段动态数据的趋势分析，下面结合具体案例展开讲解。
某企业要求每周一 9:00 晨会时以周报形式展示上周企业的整体销售情况，数据源是从系统导出的销售明细记录，每周递增，2019 年全年销售记录如下图所示。
领导要求除了查看本周总销售额、总订单数以及与上周对比的环比增长情况，还要查看本周单天的销售额和订单数极值、本周销售走势图、各渠道销售比例分布图及对比图，这些需求都可以使用 Excel 周报看板及时、准确地满足。
先预览做好的 Excel 周报看板的效果，如下图所示。
在这张 Excel 数据看板中，不但可以直观查看各种静态指标的具体数值，而且可以动态查看本周的销售趋势图，整个看板的所有数据可以跟随控件选择的第几周动态更新，如当用户在顶部控件按钮调整至下一周时，Excel 周报看板则会自动更新，效果如下图所示。
本案例的周报看板，除了要像日报看板展示核心指标的静态数据外，还要对本周 7 天的销售数据趋势进行展示，这可以使用折线图来实现，如果想在折线图的基础上进一步突出显示数据趋势的变动效果，可以使用面积图和折线图的组合图表。
对于领导要求的各渠道销售比例分布图及对比图，可以分别使用饼图和柱形图。对于这些需求，都要在动手之前在心中理解透彻并想好对策，这是十分重要的，这一步做不好，很可能会导致最后要把整个数据看板推翻重做。
把大的需求拆分成多个小需求逐一满足，包括核心指标的准确提炼。
本案例中的核心指标包括所展示的时间段是全年第几周、本周的起始日期和截止日期、本周总销售额、本周总订单数、销售额及订单数的环比分析与极值（最大值、最小值）、渠道销售百分比等。
为了方便大家看清周报的计算过程，我们简化了原始数据，只保留了周报看板分析时用到的一些日期的数据。所以我们整个周报的生成，参考的都是下面这个表格的数据，截取的是原始表格从7/29~8/19一段时间的数据。
大家可能注意到了，最后一列周数的计算是用WEEKNUM这个函数实现的。它的语法结构如下：
WEEKNUM(日期，周起始参数)
-
-如果第二参数为 1 或省略，说明将星期日作为一周的第一天； -
-如果第二参数为 2，说明将星期一作为一周的第一天。
本案例中按照大多数企业常用规则，将星期一作为一周的第一天，所以公式中第二参数为2。
这样即可根据日期计算出其在该年的第几周，方便后续按照周统计各种核心指标。
基于这个原始数据，接下来，我们要重点讲周报的幕后功臣-周报计算过程的一些展示数据的生成。
先上成品。这个分析计算过程的算法中，除了选定第几周的这个数字32是手动输入的，其他所有的显示，都有公式生成。而这些公式，是随着原始数据的变动而变动的。因为周报所展示的效果，数据全部来源于这个表格，所以最终周报的显示效果是通过这个计算过程作为中转，也跟随着原始表格的数据变动而变动的。
1. 我们先来看日期部分的生成：
以下红框的这部分日期，最复杂的是C3这个日期，见以下公式，这里就用到了我们前面讲的INDEX+MATCH的经典组合。然后下面几个日期的公式比较简单，如下：
-
输入本周截止日期的计算公式： =C3+6 -
输入上周起始日期的计算公式： =C3-7 -
输入上周截止日期的计算公式： =C4-7 -
输入本周起始日期对应星期几的计算公式： =TEXT(C3,"aaaa") -
输入本周截止日期对应星期几的计算公式： =TEXT(C4,"aaaa")
2. 计算本周总金额、本周总订单数、上周总金额、上周总订单数。
输入本周总金额的计算公式，如下图：
=SUMIFS(原始数据!$F:$F,原始记录!$B:$B,”>=”&$C$3,原始数据!$B:$B,”<=”&$C$4)
需要特别注意下：整列的引用， 一定要用绝对引用，这样在原始数据有增减的时候，公式会自动计算增减后的结果。
输入本周总订单数的计算公式：
=COUNTIFS(原始数据!$B:$B,”>=”&$C$3,原始数据!$B:$B,”<=”&$C$4)
输入上周总金额的计算公式：
=SUMIFS(原始数据!$F:$F,原始记录!$B:$B,”>=”&$H$3,原始数据!$B:$B,”<=”&$H$4)
输入上周总订单数的计算公式：
=COUNTIFS(原始数据!$B:$B,”>=”&$H$3,原始数据!$B:$B,”<=”&$H$4)
有了这些核心指标之后，为了计算本周金额及订单数极值和后续生成销售趋势图，继续计算本周每天的日期以及对应的金额和订单数。
3. 可以使用 Excel 函数公式自动生成本周日期，在 C13 单元格输入公式，再将公式向下填充，如下图所示。
=$C$3+ROW(1:1)-1
ROW(1:1)向下填充时会自动变为ROW(2:2)、ROW(3:3)…
，实现序列递增。 -
若要生成从 0开始的序列，可使用ROW(1:1)-1。 -
若要生成从 n开始的序列，可使用ROW(1:1)+(n-1)。
4.根据日期汇总计算当天的销售金额，当天的订单数。
单日金额公式，在D13单元格输入公式，再将公式向下填充。见下图：
=SUMIFS(原始记录!$F:$F,原始记录!$B:$B,$C13)
当天的订单数，在E13单元格输入公式，再将公式向下填充。见下图：
5. 有了这些数据，再来计算单天最高金额、单天最低金额、单天最多订单、单天最少订单。输入单天最高金额的计算公式，如下图所示。
-
输入单天最低金额的计算公式： =MIN(D13:D19) -
输入单天最多订单的计算公式： =MAX(E13:E19) -
输入单天最少订单的计算公式： =MIN(E13:E19)
至此，计算过程中的基本要素添加完毕。下面就进入根据需要插入动态图表 ，数据可视化元素的过程，这个，咱们下一节分享。内容来自李锐的书《跟李锐学Excel数据分析》