Excel利用SUMPRODUCT函数及条件格式制作同期数据自动对比表
发布时间:2023-10-17 10:04:22
做电商的小伙伴们,会经常需要对当前的数据与历史数据进行对比,得出差异,分析趋势,或者从各个方面、各个渠道细化剖析数据。本篇讲的便是以原始的订单数据,制作月度同期对比分析表。
先瞄一眼效果是酱紫的:
是不是感觉比较简练清晰,其中:
①年、月、日、周,系统默认是自动获取系统时间,免去了手工设置的麻烦,当然也可以手工输入。
②同期数据均是公式生成,免去了手工计算的繁琐,省时而且准确。
③设计简练,嘿嘿;
废话不多说,开始正题:
STEP1原始数据表如下(图1):
STEP2数据预处理:
小伙伴们是不是有种熟悉的感觉,接下来我们就对他进行如下处理:分别把年、月、日用日期公式扒出来(图2):
说明:日期2016/6/2,转换单元格格式为常规之后,就是上面图中的42523。
为了显示清楚,就直接显示了公式,就是简单的year(),month(),day()公式。所以实际看到的表格是这样的(图3):
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函数及条件格式。
猜你喜欢
- word中怎么在文字行加入分数并且和文字是在同一行?很多朋友都不是很清楚,所以今天小编就为大家详细介绍一下,一起来看看吧 有时候在
- Excel怎么利用数据有效性制作多级下拉菜单?想要制作多级下拉菜单,可以实现的方法有很多,今天我们就来看看Excel利用数据有效性制作多级下
- word怎么添加超链接?office组件中的word文档,可以在其中进行超链接操作,免去我们打开多个窗口的麻烦,也可提高文档讲解的流畅性,下
- 要修改 Microsoft Office Word 2007 某次启动的方式,可以在 Microsoft Windows“运行”命令(“开始
- 对于从Office2007升级安装Office2010,或者卸载Office2007后安装Office2010的用户,如果在Word2007
- 如何在Word中添加文本框?Word是一个功能非常强大的文字处理软件,相信在座的有用户刚接触Word,对Word的一些基础功能还不是很清楚,
- 输入公式选择好要输入公式的单元格,就可以开始输入公式了。首先需要输入一个等号,告知 Excel 这是一个公式的开始。然后输入公式中的其他内容
- 我们经常会使用Win10系统的电脑进行文件共享,而有一些小伙伴进行文件共享的时候被提示要求输入网络凭据,那么遇到这种问题应该怎么办呢?下面就
- Word文档分栏显示教程。在编辑文档页面中的内容时,使用Word的分栏功能,可以展现文档内容的并列关系,并且可以整齐地规划文本。轻松为文档分
- 很多人在使用电脑的时候都会遇到很多问题,比如一个软件正在使用的过程中突然停了不能使用了,连带着电脑也一起卡机了、鼠标动不了,所以需要强制关闭
- 从win7升级到win10的用户可能会遇到升级后系统睡眠功能“消失”的问题。他们在“控制面板”或“现代设置”页中找不到睡眠。此问题的原因可能
- 1、直接在终端执行以下命令(1)删除Word最近打开文件rm-rf~/Library/Containers/com.microsoft.Wo
- 今天说点轻松的。大部分销售企业,都有统计前几名和最后几名的需求!我们通过一个案例,告诉你什么叫秒出结果!数据源:销售明细表一张!需求:求出金
- Word作为我们日常使用频率非常高的软件,便捷功能还有非常多,今天和大家分享20个Word小技巧。1、隐藏部分文本选中文本,之后选中字体打开
- 文章详细介绍excel隐藏对象内容的两种方法,通过excel选项设置和使用快捷键ctrl+6实现。图表、文本框、控件、图形等等都属于exce
- 本文的Excel基础教程主要为大家分享了Excel公式入门知识,以及Excel运算符优先级等等知识点。2.1 Excel公式简介公式是Exc
- 方法一:按住CTRL键不放,连续选定要打印的多个工作表,如图1所示,这里选定了表1、表2和表3。在文件菜单中选择“打印”,再按照一般打印的要
- 有不少用户在正常使用Win10电脑的时候,突然发下右下角任务栏中的输入法图标不见了。那么如何碰到这种问题应该怎么去解决呢?下面就和小编一起来
- Excel中的表格数字具体该如何进行操作才能够解决变成0的问题呢?下面是小编带来的关于excel表格数字变成0的解决方案,希望阅读过后对你有
- 怎么解除Word安全模式? 经常在打开word时出现mcrosoft office遇到问题需要关闭提示,并且显示word上次启时失败,以安全