车船号作等号多条件计数的复杂实例

今天作者来讲一个关于多条件计数的复杂实例场景，我们先配合下面这张图来介绍一下任务需求。

下图是两个工作表的组合图，上面是总数据表表1，下面是要填充公式结果的表2，题干是这样的：

计算每个车船号在当天指定时间内不重复的出车次数，并将结果填充到另一工作表的指定区域。

再来看看两个工作表的数据情况：

表1为总数据表，包含了出车确认时间、车船号等基础信息。

表2为结果填充的表格，其中标题行是0-23小时，也是要作为条件的时间区间，A列则是提取出来的不重复的车船号。所以标题行和A列的数据实际是作为两个不同的条件，比如计算车船号海L90571在当天1点的不重复总出车次数！

这里的1点，它不仅仅是包含1点整，凡是时间在1点多的，也属于要统计的范围，；

此外还要注意不重复出车的问题，我们看下面这张图，如箭头红框所示，车船号海L90571实际在21:35只发出了一趟，但由于车上搭载的货物是不同合同号的，因此重复显示了4次出车记录。

所以我们在后续写公式时，如何剔除掉那些重复的出车记录，是其中一个难点和关键点。

看到这里，不知道童鞋们有没有解题思路。

如果单从效率出发，那么利用数据透视表和公式的组合应用，可能解题的时间更快；

而通过建立辅助列，再使用公式来解题，也是一个不错的选择。

但如果要不变动表格数据，也不想创建数据透视表，而完全通过一个公式来完成结果的填充，那么请继续往下看。

首先讲讲作者的公式解题思路。

既然是关于多条件计数的案例，那么前提就是设置条件，而设置条件又要看设置什么样的条件，和怎么设置条件。

这两步就如同创建一个框架，然后给框架进行填充。

设置什么条件，那很明显，一个是指定的车船号，另一个是指定的时间内。

怎么设置条件，其实就是函数的选择，如果选择countif函数，那么就要思考我们要设置的条件如何嵌入countif函数的参数中。

那在这个场景中，由于是多条件计数，因此至少也要使用countifs函数，但countifs函数的参数条件区域的限制太多，它通常只能选定一个单元格区域作为条件区域，而不能通过一个表达式运算得到的区域来作为它的条件区域，因此如果使用countifs函数会遇到一些麻烦。

所以作者选择sum函数来建立公式。

sum函数当然不止用来求和，也可以用于计数，而它计数的逻辑，其实作者在专栏《excel100个常见场景可套公式》的首节就进行了介绍，也写了它的固定公式表达。

那闲言少叙，下面作者就通过两个条件的公式设置，来逐步组合出最后完整的解题公式。

第一个条件——指定的车船号

正常的思路，我们只要将总数据表的车船号列与表2的车船号作等号运算，即Sheet1!$G$2:$G$369=A2。

这个表达式的结果是一个包含众多逻辑值的数组，等号成立的结果就为true，即等于1。但由于总数据表中的出车记录是有重复的，因此上面表达式得出的结果中，也包含了一些重复的结果。

所以我们需要创建一个不重复的车船号列表，并将这个列表区域与A2进行等号运算，这样得到的结果才是唯一不重复的。

那么我们先写公式：

{=RIGHT(UNIQUE(TEXT(Sheet1!$F$2:$F$369,"hhmm")&Sheet1!$G$2:$G$369),7)=$A2}

我们先来看下这个公式包含了哪些函数，有right、unique、text三个函数，然后使用了”&“连接符和“=”号运算。

那么text函数在这里是将Sheet1!$F$2:$F$369单元格区域的时间转换成”hhmm“的格式，也就是小时和分钟。如2022-07-2521:35，使用text函数转换，则为2135.

unique函数的作用是取一个列表中的唯一值，也可以叫做取列表中的不重复值，都是一个意思。在这里，它的参数是两个单元格列表的组合，在下面表达式可以看到，F列出车确认时间和G列车船号通过连接符号相连组成一个新的文本，然后再提取出这个新文本列表中的不重复值。

UNIQUE(TEXT(Sheet1!$F$2:$F$369,"hhmm")&Sheet1!$G$2:$G$369)

之后再使用right函数来提取unique表达式结果的右侧指定长度的字符串，比如这里rigth函数的第2参数是7，也就是提取文本右侧的7个字符。

右侧7个字符，实际就是连接后的新文本中的车船号，所以最后再等于A2，就会得到一个不重复的逻辑值数组结果。

第二个条件——指定时间区间

指定时间区间，这又是一个难点，因为表2标题行作为时间条件，但它显示为0-23的数字，与总数据表中的时间格式是不一致的。

如果我们通过hour函数来提取时间列表中的小时，如hour（2022-07-2521:35），等于21，这个结果是可以直接和标题行的条件值划等号的。

但还是由于时间列表中的重复值问题，如果不先取唯一值，最后的公式结果仍会出现错误。

因此我们再借鉴第1个条件中使用的公式表达，那么公式为：

{=LEFT(UNIQUE(TEXT(Sheet1!$F$2:$F$369,"hhmm")&Sheet1!$G$2:$G$369),2)=TEXT(B$1,"00")}

这个公式中出现了left、unique、text三个函数，与第1个条件公式不同的地方在外层嵌套的文本提取函数不同，left函数是从左侧提取指定长度的字符，而right函数是从右侧，这就是两者的区别。

那么这个表达式的逻辑其实是一样的，通过text函数转换时间列表为指定的小时分钟格式，然后连接车船号列表，再通过unique函数提取组合后的新列表中的不重复值，然后利用left函数来提取左侧2个字符，其实恰好就是小时那两个字符，最后再与"TEXT(B$1,"00")"的结果进行等号运算。

而得到一个包含众多逻辑值的数组结果。

之后我们将两个条件套入sum函数中，来看看完整的公式情况：

{=SUM((LEFT(UNIQUE(TEXT(Sheet1!$F$2:$F$369,"hhmm")&Sheet1!$G$2:$G$369),2)=TEXT(B$1,"00"))

*(RIGHT(UNIQUE(TEXT(Sheet1!$F$2:$F$369,"hhmm")&Sheet1!$G$2:$G$369),7)=$A2))}

如上图所示，通过对条件1和条件2相乘，得到一个1和0的数组结果，再通过sum函数来汇总得到的数组结果，而最终得到符合条件的出车总次数。

由于公式填充的结果出现了0值，因此可以通过操作将0值不显示，于是得到下图的结果。

由于数据量较多，我们在写完公式后，也需要进行结果测试，比如抽取其中一两组结果到数据表中去核对，或者统计所有车船号的不重复的总出车次数，是不是与数据表的不重复值个数一致。

那最后，作者再总结一下，由于篇幅问题，公式的所有内容并没有完全细致地解析，但我们只要弄懂每个函数在公式中的作用，然后多使用F9解析公式结果，也能一步步揭开这个组合公式的“面纱”。

比如sum函数在这里仍然是汇总求和，但它更关键的点是将其参数设置为两组逻辑值相乘，从而得到数值结果。而left函数提取指定文本字符，unique函数提取列表唯一值，text函数转换单元格为指定格式文本。

我们一个一个函数表达式去分析，相信没有那个公式是不能被理解透彻的！

当然，我们也要打好函数基础，了解常见函数的语法和用法，才能更好地领会和应用函数公式。

举报/反馈