Excel基于历史月份数据的条件格式预警

在市场研究或产品监测中,通常会遇到对同一个数据连续多期的表现的预警监测。如,连续两期低于某个值、连续三期低于某个值等等。

仍以”单一目标值的条件格式预警“中的区域和产品为例。假设我们连续监测了8个区域5个产品在1-3月的销售情况,对于各个产品,如果某区域的连续2个月销量低于各区域当月平均值则用黄色预警,如果连续3个月均低于当月平均值则用红色预警。最终结果如下:

操作方法:

1、选中3月的数据区域C21:G26,点击开始→条件格式→新建规则→使用公式确定要设置格式的单元格

2、在”为符合此公式的值设置格式”中,输入:=SUM(IF(C3<C$9,1,0),IF(C12<C$18,5,0),IF(C21<C$27,10,0))=15,点击格式,设置填充为黄色。用以设置在2月和3月连续两个月均低于平均值的数据预警

3、上述步骤确定完成后,再重复步骤1,在设置公式时输入:=SUM(IF(C3<C$9,1,0),IF(C12<C$18,5,0),IF(C21<C$27,10,0))=16,点击格式,设置填充为红色。用以设置在1-3月均低于平均值的数据预警

至此我们可以观察验证公式设置是否正确。以上问题的解决思路主要为:

我们判断某个月的,某区域的产品销售量是否小于所有区域的均值,如果小于则返回一个特定值,否则返回0。我们通过对不同月份的判断结果返回不同的值,然后对这些值相加即可知道某个区域是在哪几个月份销售额小于均值。

如我们设置某区域一月销售额小于均值则返回1,二月小于均值则返回5,三月则返回10,将三个月返回值相加,得到最终结果。通过对最终结果的判断,我们知道15则表示二三月份同时低于均值,16则表示一二三月份同时低于均值。

文件下载,百度网盘:https://pan.baidu.com/s/1NGSqr663kRUCvOUuCb8JtQ 提取码:umk6