EXCEL学习之函数思路解析--“报税倒计时提醒”的设计思路

上一期中，我们利用“报税倒计时”作为例子，讲解了多个函数组合使用的方法以及组合的思路。通过大家的反馈可以看出，对于复杂函数组合的使用并不是很容易就能掌握，因此在这一期的前半部分，我们再来整理一下函数组合的思路。

以上一期的”报税倒计时“为例：报税倒计时提醒

B17单元格中的公式为：=VALUE(INDEX(C3:C14,MATCH(IF(TODAY()<=C3,C2,LOOKUP(,0/(TODAY()>C3:C14),C3:C14)),C2:C14,0))-TODAY())+1

接下来我们整理一下解题的思路：

第一步、对于“倒计时”这种应用，关键在于如何得到用于计算的两个日期，即【①下一个报税截止日期】与【②当前日期】。对于【②当前日期】，我们可以很容易的利用TODAY函数直接得到结果，但是对于【①下一个报税截止日期】，很难一下子想到直接可以得到该结果的函数，故考虑利用组合函数。

基于上述分析，可以得到如下公式结构：=VALUE(【①下一个报税截止日期】 - TODAY()) + 1。

第二步、针对第一步中未能得出的【①下一个报税截止日期】，需要进一步将问题分解。首先，这个日期一定出现在C3到C14单元格区域内，也就是说需要在此单元格区域内进行查找；其次，需要解决如何在这个区域内取得该日期。对于本例，我们可以通过将当前日期与C3到C14单元格区域内的全部日期进行比较，得到比当前日期大的最近的一个截止日期。

基于上述分析，针对于“在一个区域内查找比一个固定的值大的值”这种问题，我们可以利用MATCH函数定位出大于等于当前日期的最小截止日期所在的位置（即在数组中排在第几位），并将该定位位置交给INDEX函数，从而将该位置上的内容取出。因此所得公式如下：=VALUE(INDEX(C3:C14,MATCH(TODAY(),C3:C14,-1)) - TODAY()) + 1。

第三步、上一步的公式中，虽然我们将MATCH函数中的“检索方式”参数设定为“-1”（即定位出大于等于检索值的最小值），此参数的设置方式可得到距当前日期最近的下一个截止日期。但由于MATCH函数要求当使用“-1”定位时，检索区域应保持倒序排列（即12月，11月……1月）。因此在不改变原始数据的情况下，我们需要改变思路，将该参数改为“1”从而得到距离当前日期最近的上一个截止日期。

故首先应将上述公式调整为：=VALUE(INDEX(C3:C14,MATCH(TODAY(),C3:C14,1)) - TODAY()) + 1。

其次，由于我们利用MATCH函数得到的是距离当前日期最近的上一个截止日期，但实际我们需要使用的是下一个截止日期，因此，可以将INDEX函数的“待取值区域”参数由原来的C3:C14调整为C4:C14，即当MATCH返回定位值为1时（C3单元格），对应INDEX的取值区域中的第一个位置上的内容应该是C4单元格内容。

基于上述分析，可以得到如下公式：=VALUE(INDEX(C4:C14,MATCH(TODAY(),C3:C14,1)) - TODAY()) + 1。

第四步、上述的结果对于绝大多数日期有效，但是当出现一些特殊的日期时会出现异常，这些日期是“在第一个报税截止日期之前的日期”。因为在这种情况下，MATCH函数找不到比当前日期小的日期。因此需要将MATCH函数中的“待检索值”参数进行调整，以便能够为INDEX函数提供正确的位置。

调整的思路是，将当前日期分两种情况处理，当该日期比第一个报税截止日期大时，取出【③上一个截止日期】（相当于是取得下一个截止日期所在单元格的上一个单元格）；否则，人为指定一个值C2（由于下一个截止日期即为第一个截止日期，故应该指定C3单元格的上一个单元格）。由于上述原因，MATCH函数与INDEX函数中使用的区域应当相应向上扩大一个单元格。即：=VALUE(INDEX(C3:C14,MATCH(IF(TODAY()<=C3,C2,【③上一个截止日期】),C2:C14,1)) - TODAY()) + 1。

第五步、对于上一步中的【③上一个截止日期】，我们可以继续套用MATCH函数，以达到取得上一个截止日期的目的。也可以使用例子中使用的LOOKUP函数。由于上述两种方式都介绍过，就不多说了。

通过上述五个步骤，完成了多个函数的组合，并得到了正确结果。

下面我们就来看看如何利用“辅助列方式”来直接取得”距离当前日期最近的下一个截止日期“。

首先说什么是“辅助列方式”。所谓的“辅助列方式”就是，为了可以利用简单函数直接得到所需的结果，人为的添加一列或几列单元格，利用函数或其他手段对数据源中的某些内容进行调整并将调整的结果反映到添加的单元格内，或者将函数中将要使用的条件直接放入单元格中以便函数可以直接使用的一种方法。关于“辅助列方式”的应用，可以参考“填充”这一期中的关于“隔行提取数据”的应用。EXCEL中排序的应用汇总-日期填充、混合填充与自动填充

这次我们将利用后一种“将条件写入某辅助单元格”的方式解决公式复杂的问题。先来看一下结果：

在上图中，共有三部分内容：

第一部分、数据源。B2至C14单元格区域为数据源区域，与上一期中的例子相同，为2017年各次报税的起止日期。

第二部分、筛选条件。B16至B17单元格区域为辅助列部分，输入的是我们针对数据源区域中哪一个项目进行何种筛选。在B17单元格中的公式为：=">"&TEXT(TODAY(),"yyyy/m/d")。该公式的目的在于得到一个由大于号和当前日期组成的每天都会变化的文字列，为后面的获得”距离当前日期最近的下一个截止日期“的公式提供筛选条件，从而起到辅助列的作用。其中的TEXT函数是一个常用的转换数字表示格式的函数，通过对数字内容使用格式代码，进而更改数字的显示方式。该函数的效果类似于【设置单元格格式】中【数字】标签中的各种自定义格式。如下图：

上图中的结果可以用以下TEXT函数完成：=TEXT(TODAY(),"yyyy/m/d")。该函数由两个参数组成，第一个参数value（如例子中的TODAY函数所得到的当前日期的序列），代表了“待转换的数字或单元格引用”；第二个参数format_text（如例子中的"yyyy/m/md"），代表了“需要显示的文字格式”。对于第二个参数，格式代码的写法基本与【设置单元格格式】类似，需要注意的是格式代码的两端必须用双引号括起来。

第三部分、倒计时结果。B19至B20单元格区域为结果部分，其中B19单元格中的公式与上一期完全相同，为取得当前日期的TODAY函数，而在B20单元格中的公式与上一期有所不同，注意公式中的红字部分：=VALUE(DMIN(B2:C14,2,B16:B17)-TODAY())+1。下面对红字部分进行说明：

函数说明：返回列表或数据库中满足指定条件的列或记录字段中的最小数字。

函数模型：DMIN(Database, Field, Criteria)

参数说明：

Database：必需输入参数，代表了“待筛选的数据区域”。该参数构成列表或数据库的单元格区域。这里所说的数据库是指包含一组相关数据的单元格区域。构成该区域的行数据称为“记录”；构成该区域的列数据称为“项目”，其中每列的第一行必须为“列标签”（即记录中的每一个项目的名称）。

Field：必需输入参数，代表了“待求值的项目列”。该参数有两种形式。文本形式：输入数据列第一行中的“列标签”，两端用双引号括起，如“起始日期”或“终止日期”；数字形式：输入构成数据库的单元格区域中所使用的数据列的列号（不带引号），此列号相对于数据库而不是对工作表。例如：1 表示第一列，2 表示第二列，依此类推。

Criteria：必需输入参数，代表了“筛选条件”。可以为该参数指定任意区域，此区域包含至少一个列标签，并且列标签下至少有一个做为筛选条件的单元格且不能与数据库区域交叉重叠。

通过对参数的说明，可以知道该函数的原理为：在由“记录”和“项目”构成的成为“数据库列表”中（即参数1），利用“筛选条件”（即参数3），将符合条件的结果从“待求值的项目列”（即参数2）中筛选出来。将上述原理套入本例，即：在由起始和终止日期组成的数据源区域（B2至C14）中，筛选“终止日期”列（第二列）中满足终止日期大于当前日期（B16至B17单元格区域）的下一个报税截止日期。

有三点需要注意：

第一、第一个参数所在的数据源区域中必须包含“列标签”行；

第二、第二个参数除了可以输入数字2以外，还可以用“列标签”代替，如“终止日期”；

第三、第三个参数中的“列标签”必须包含于第一个参数中的”列标签“。

至此，我们通过使用一个简单的辅助列，将上一期中的复杂的【INDEX+MATCH+LOOKUP】公式组合变为一个简单的【DMIN】函数，并得到了相同的结果。