首页 > Excel专区 > Excel函数 >

excel 使用SUMPRODUCT函数及条件格式来制作同期数据自动对比表图

Excel函数 2021-11-25 10:15:50

做电商的小伙伴们,会经常需要对当前的数据与历史数据进行对比,得出差异,分析趋势,或者从各个方面、各个渠道细化剖析数据。本篇讲的便是以原始的订单数据,制作月度同期对比分析表。

  先瞄一眼效果是酱紫的:excel数据对比    是不是感觉比较简练清晰,其中:  ①年、月、日、周,系统默认是自动获取系统时间,免去了手工设置的麻烦,当然也可以手工输入。  ②同期数据均是公式生成,免去了手工计算的繁琐,省时而且准确。  ③设计简练,嘿嘿;   废话不多说,开始正题:   STEP1 原始数据表如下(图1): SUMPRODUCT函数   STEP2 数据预处理:   小伙伴们是不是有种熟悉的感觉,接下来我们就对他进行如下处理:分别把年、月、日用日期公式扒出来(图2):excel数据对比公式    说明:日期2016/6/2,转换单元格格式为常规之后,就是上面图中的42523。   为了显示清楚,就直接显示了公式,就是简单的year(),month(),day()公式。所以实际看到的表格是这样的(图3):excel两组数据对比图    STEP3 这步是最关键的,编写获取月度同期订单量的公式:excel 2列数据对比    在“上月同期”对应的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)  这个公式的目的是在数字的前面有一个“+”,这样看起来更专业~ excel两表数据对比    STEP5 设置条件单元格格式   G6单元格与H6单元格都需要设置条件单元格格式,我们从H6单元格开始。  在“月同期环比”对应的H6单元格中,首先输入环比公式:  =F6/E6-1  再设定单元格格式为“百分比”   为了能达到“环比增加则是绿底+向上的绿色箭头”、“环比减少则是红底+向下的红色箭头”的醒目效果,接下来是设定条件单元格格式:   ①首先设置环比增加时的绿色底纹,选中H6单元格,点击“条件格式”选项卡,点击“新建规则”; “条件格式”选项卡    在弹出的“新建格式规则”对话框中,选择第二项“只为包含以下内容的单元格设置格式”,然后在“编辑规则说明”中,依次从各个下拉框中选择“单元格值”、“大于”,输入“0”。Excel2013条件格式   再点击“格式”按钮,在弹出的“设置单元格格式”对话框中,分别在“字体”、“填充”选项卡中,设置成需要的格式。(字体颜色为白色,填充的颜色为绿色)Excel设置单元格格式 Excel2013条件格式设置案例   ②环比增加的绿色底纹设置好后,再同样设置环比减少的红色底纹即可。   ③接下来设置H6单元格的红色、绿色箭头的条件格式:  同样是选中H6单元格,新建格式规则,按下图所示进行设置,即可。 Excel新建格式规则    这样H6单元格在设置好后,会有如下的三项条件规则,小伙伴们别漏了:excel数据对比工具    接下来再是设置G6单元格,其同H6类似,只是少了红色、绿色箭头,小伙伴们这下懂了吧。  至此大功告成。   备注:  其实,工作中不止是月同期对比,周对比,日对比,等都是经常需要的,笔者的常用表格其实是这个样子的,核心就是本文讲的SUMPRODUCT函数及条件格式。 SUMPRODUCT函数及条件格式

标签: SUMPRODUCT函数

office教程网 Copyright © 2016-2020 https://www.office9.cn. Some Rights Reserved.