EXCEL|月报看板,用数据建模进行自动化数据分析和展示2
- 2026-04-09 19:21:21
EXCEL|月报看板,用数据建模进行自动化数据分析和展示2接上章，月报的核心指标包括所展示的时间段是全年第几月，该月份的计划完成率，截至该月份的本年累计计划完成率，本月的起始日期，截止日期，本月总销售额，本月总订单数，销售额及订单数的环比分析与极值（最大值，最小值），各渠道及各商品销售对比，区域销售百分比，各业务员的销售业绩对比及排名等。指标比较多，所以月报的计算过程，就需要把指标需要用到的相关数据全部用公式计算好。 以下为月报计算过程中，需要呈现出来的相关数据。这个里面，除了2月这个是手动收入的，其他日期和相关数据，全部由公式得来。这样可以实现在修改原始数据的时候，所有统计数据都能随之更新。因为内容比较多，所以要完整显示，字体就会放的小一点。接下来，我们分别介绍每个模块的制作过程。 
首先，我们需要在原始数据里增加一列统计月份的函数。 
然后我们在月度计算过程这个插页里来完成以下内容的制作。 
1）在 C2 单元格输入代表月份的数字（如 
上月的起始日期，截止日期及对应星期几是一样的公式，公式如下： 
上月的总金额和总订单数计算方法一样，公式如下： 3）接下来来进行环比对比计算，即如果这月比上月订单总金额和订单数是增长的，则显示为▲，且显示为红色；如果这月比上月订单总金额和订单数是下降的，则显示为▼，且显示为绿色。这些在之前的日报，周报里都有介绍。这里不再赘述。 
4）下面来看当月单天最高最低订单金额和数量的统计方式。即下面这部分内容的生成。 
我们可以使用 Excel 函数公式自动生成天数序号和本月日期，无论是天数序号还是本月日期都要根据所选的月份自动更新（如 2 月的天数序号是 1 至 28）。在 B13 单元格输入以下公式，生成从 1 开始的天数序号。由于月份最大天数是 31 天，所以将公式向下填充至 B43 单元格，如下图所示。 
这里解释下：Day函数是提取日期里的日，见如下： 
ROW函数的用法如下： 
5) 根据日期计算当天的销售金额，在D13单元格输入以下公式，再将公式向下填充。 =IF(ROW(1:1)>DAY(DATE(2019,$C$2+1,1)-1),NA, SUMIFS(月报原始数据!$F:$F,月报原始数据!$B:$B,C13)) 
6）计算当日订单数 在 E13 单元格输入以下公式，再将公式向下填充： =IF(ROW(1:1)>DAY(DATE(2019,$C$2+1,1)-1),NA,COUNTIF(月报原始数据!$B:$B,C13)) 
7)有了这些数据，再来计算该月份中的单天最高金额，单天最低金额，单天最多订单，单天最低订单。单天最高金额的计算公式如下： 
我们来分解下这个公式： 所以整个公式的意思是： 从 D13 开始，取本月所有天数的销售金额，然后找出其中的单日最高金额。 单日最低金额 ：=MIN(OFFSET(D13,,,DAY(C4)))； 单日最多订单 ：=MAX(OFFSET(E13,,,DAY(C4))) 
单日最少订单 ：=MIN(OFFSET(E13,,,DAY(C4))) 月报的内容比较多，今天就先学习到这里吧。明天我们继续。 内容来自李锐的书《跟李锐学习Excel数据分析》
2），通过设置单元格格式将其显示为 “2 月”，操作步骤如下，后续在月报看板中会将 C2 单元格设置为与控件按钮关联，用于根据用户选择快捷指定目标月份。其他所有核心指标数据都按此单元格确定日期时段进行对应计算，如：本月起始日期，本月截止日期，上月起始日期，上月截止日期。2）. 输入相关计算公式如下：
- 本月起始日期
： =DATE(2019,C2,1)
- 本月截止日期
： =DATE(2019,C2+1,1)-1
- 上月起始日期
： =DATE(2019,C2-1,1) - 上月截止日期
： =DATE(2019,C2,1)-1 - 本月起始日期对应星期几
： =TEXT(C3,"aaaa")
- 本月总金额
:=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)
MAX(OFFSET(D13,,,DAY(C4)))
-DAY(C4)提取这个日期的 “日” 部分，也就是本月总天数（28）；
-OFFSET(起点, 行偏移, 列偏移, 高度, 宽度)这里的参数是：
起点： D13（本月第一天金额所在单元格）行偏移：（留空 = 0，不上下移动） 列偏移：（留空 = 0，不左右移动） 高度： 28（从 D13 开始，向下取 28 行）宽度：（留空 = 1，只取 1 列）
所以 OFFSET(D13,,,28) 等价于：
D13:D40：从 D13 开始，向下 28 行的区域（D13, D14, ..., D40）
这个区域正好是本月每天的销售金额。
-MAX(区域) 会计算这个区域里的最大值。
本文来自网友投稿或网络内容，如有侵犯您的权益请联系我们删除，联系邮箱：wyl860211@qq.com 。
