Excel教程：利用SUMPRODUCT函数及条件格式制作同期数据自动对比表
做电商的小伙伴们，经常需要去对比当前的数据与历史数据，从而得出差异，分析趋势，或者从各个方面、各个渠道细化剖析数据。本篇讲的便是以原始的订单数据，制作月度同期对比分析表。
先瞄一眼效果是酱紫的：
是不是感觉比较简练清晰，其中：
①年、月、日、周，默认是自动获取系统时间，免去了手工设置的麻烦，当然也可以手工输入。
②同期数据均是公式生成，免去了手工计算的繁琐，省时而且准确。
③设计简练，嘿嘿；
废话不多说，开始正题：
STEP1 原始数据表如下：
STEP2 数据预处理：
小伙伴们是不是有种熟悉的感觉，接下来我们就对他进行如下处理：分别将年、月、日用日期公式扒出来：
说明：将日期2016/6/2的单元格格式，转换为常规之后，就变成了上面图中的42523。
提取年、月、日我们用了最简单的year(),month(),day()公式。得到以下结果：
STEP3 这步是最关键的，编写获取月度同期订单量的公式：
在“上月同期”对应的E6单元格中，我们输入：
=SUMPRODUCT((M2:M100=销售!B2)*(N2:N100=C2-1)*(O2:O100<=D2)*(L2:L100))
其中：“(M2:M100=销售!B2)” 是指年份为2016年；“(N2:N100=C2-1)”当前月为7月，所以要减去1，即6月；“(O2:O100<=D2)”则是指选择小于当前所在月第几天，表中是07.27日，所以实际上是选择小于27的天数；“(L2:L100)”是指要计算的订单数据。
同样，在“本月同期”对应的F6单元格中，我们输入：
=SUMPRODUCT((M2:M100=销售!B2)*(N2:N100=C2)*(O2:O100<=D2)*(L2:L100))
同理，只是月份要选择当前月，即(N2:N100=C2)
STEP4 编写对比结果公式：
月度同期的数据出来了，接着就是对比结果了。我们通过采用“+/-”、醒目颜色、单元格底纹颜色等条件格式的设置，直观表现数据的变化：
在“增加/减少”对应的G6单元格中，我们输入：
=IF(F6-E6<0,F6-E6,"+"&F6-E6)
这个公式的目的是在数字的前面有一个“+”，这样看起来更专业~
STEP5 设置条件单元格格式
G6单元格与H6单元格都需要设置条件单元格格式，我们从H6单元格开始。
在“月同期环比”对应的H6单元格中，首先输入环比公式：
=F6/E6-1
再设定单元格格式为“百分比”
为了达到“环比增加是向上的绿色箭头”、“环比减少是向下的红色箭头”的效果，接下来是设定条件单元格格式：
①首先设置环比增加时的绿色底纹，选中H6单元格，点击“条件格式”选项卡，点击“新建规则”；
在弹出的“新建格式规则”对话框中，选择第二项“只为包含以下内容的单元格设置格式”，然后在“编辑规则说明”中，依次从各个下拉框中选择“单元格值”、“大于”，输入“0”。
再点击“格式”按钮，在弹出的“设置单元格格式”对话框中，分别在“字体”、“填充”选项卡中，设置成需要的格式。（字体颜色为白色，填充颜色为绿色）
②环比增加的绿色底纹设置好后，再同样设置环比减少的红色底纹即可。
③接下来设置H6单元格的红色、绿色箭头的条件格式：
同样是选中H6单元格，新建格式规则，按下图所示进行设置，即可。
这样H6单元格在设置好后，会有如下的三项条件规则，小伙伴们别漏了：
接下来再是设置G6单元格，其同H6类似，只是少了红色、绿色箭头，小伙伴们这下懂了吧。
至此大功告成。
备注：
其实，工作中不止是月同期对比，周对比，日对比，等都是经常需要的。笔者的常用表格其实是这个样子的，核心就是本文讲的SUMPRODUCT函数及条件格式。
扫一扫，添加老师微信咨询Excel课程
Excel教程相关推荐
想要跟随滴答老师全面系统学习Excel，不妨关注《一周Excel直通车》视频课或者《Excel极速贯通班》。
《一周Excel直通车》视频课
包含Excel技巧、函数公式、
数据透视表、图表。
一次购买，永久学习。
最实用接地气的Excel视频课
《一周Excel直通车》
风趣易懂，快速高效，带您7天学会Excel
38 节视频大课
（已更新完毕，可永久学习）
理论+实操一应俱全
主讲老师： 滴答
Excel技术大神，资深培训师；
课程粉丝100万+；
开发有《Excel小白脱白系列课》
《Excel极速贯通班》。
原价299元
限时特价 99 元，随时涨价
少喝两杯咖啡，少吃两袋零食
就能习得受用一生的Excel职场技能！