作为一名合格的4s店售后销售员,要记住客户汽车保养的时间。今天作者就来介绍一下,excel如何计算下一次的保养时间,并设置一个提前提醒的高亮单元格来突出显示。

如下图所示,在保养记录表中,已经记录了首次保养的时间和里程数,部分客户进行过二次保养,也有所记录。

我们需要通过最后保养的时间,来确定下一次保养时间。

4S店汽车保养Excel实用功能:计算下次保养时间并设置自动提醒-天天办公网

首保后的保养周期通常以每5000公里保养一次,或者6个月保养一次。

根据保养周期的时间设定,我们可以在下次保养时间列下的单元格内输入公式:

=F3+180

4S店汽车保养Excel实用功能:计算下次保养时间并设置自动提醒-天天办公网

180表示的是6个月,用二次保养时间加上180,就得到了下一次保养时间。

但并不是每个客户都做过二次保养,没做的客户就没有二次保养的时间记录,我们填充的公式自然是错误的,如下图中红框内数据,计算结果出现了错误。

4S店汽车保养Excel实用功能:计算下次保养时间并设置自动提醒-天天办公网

所以我们的正确思路是自动判断最后保养的时间,然后在此基础上加上180天,这样的计算才是准确的。

下面作者输入公式,来查询返回一列或一行数据中的最后一个值:=LOOKUP(1,0/(E3:I3<>””),E3:I3)+180

4S店汽车保养Excel实用功能:计算下次保养时间并设置自动提醒-天天办公网

lookup函数是excel官方认定的10大常用函数之一,可见文章《Excel官方认定的10个最常用的函数,看看你都会吗?》,它是一个查询引用函数,有两种不同的表达式:

4S店汽车保养Excel实用功能:计算下次保养时间并设置自动提醒-天天办公网

在这里作者使用的是前一种语法,而且第二个参数还使用了“0除”“条件判断”的表达。

条件判断的结果要么是真,要么是假,真为1,假为0,而0是无法被除的,因为公式中0若除以0,就会返回错误值。

E3:I3<>””,它的含义是指E3:I3区域中的单元格不等于空值,也就是将如果符合这个条件,那结果就是1,不符合即为0,然后通过0除,得到了1和错误值组成的结果组合。

这个结果组合再与lookup函数的第三个参数进行匹配,忽略错误值,最后一个1对应的第三参数区域中的单元格位置,就是该区域最后一个符号条件的单元格。

我们再加180天,就计算出了下一次保养时间。

之后再来设置一个提前一月提醒的文本和颜色填充。

文本提醒比较简单,写一个if函数即可:=IF((M3-TODAY())<=30,”提醒保养”,””)

4S店汽车保养Excel实用功能:计算下次保养时间并设置自动提醒-天天办公网

公式中嵌套了一个today函数,这个函数是用来返回今天的日期,在计算两个日期的间隔时间中,使用较为频繁。

接下来给符合条件的单元格进行颜色填充高亮显示。

我们在开始工具栏中找到”条件格式“,选择”新建规则“。

4S店汽车保养Excel实用功能:计算下次保养时间并设置自动提醒-天天办公网

选择”使用公式确定要设置规则的单元格“,并在公式设置框中输入公式:=N3=”提醒保养”

4S店汽车保养Excel实用功能:计算下次保养时间并设置自动提醒-天天办公网

这里要注意单元格不能绝对引用,但输入公式时,是默认的绝对引用,因此要手动进行引用方式的调整。

之后在格式中选择”填充“,将颜色设置为比较鲜艳的颜色即可。

综上所述,今天的内容涉及了不同的公式计算,和条件格式的设置,其实在表格中的数据,如基本保养内容,保养次数等,作者都是通过公式进行设置的。