excel公式中的“流氓”，可以解决很多场景问题

今天继续学习一下关于公式一对多引用和延伸应用的讲解。

首先回顾一下一对多引用公式，是关于index+n+if函数的特性组合，那么在这个基础上，我们将进一步深化该知识点，并来解答一个相关实际案例！

也就是上期结尾作者预告的问题。

在下面数据表中，是某企业物料采购的记录，通过不同日期来记录该物料采购的要货数量和采购回复数量。

我们需要通过公式将物料不同日期的要货和采购数据全部合并到一个单元格中显示！

1、有多个空格的列不在公式的结果中显示，即没有任何采购数据的列将跳过不显示；

2、公式结果最终在合并单元格中显示；

3、每组数据换行显示；

4、公式结果保持原日期格式。

具体问题介绍和解题要求如图中文本所示：

我们通过动图来看看公式运行的效果，当在后续日期输入任意采购数据时，单元格都会实时显示新录入的数据信息。

如何达到这样一个效果，作者先来介绍一下自己的思路：

首先它最终要合并所有多组数据到一个单元格中，因此必须使用文本合并函数，这是外层的一个大框架；而根据显示情况可见，它使用了分隔符进行分隔不同采购要货数量和回复数量，因此可以利用基础合并方式将同一日期下的采购数据合并到一起；

现在重点就是要得到一个多组日期的合并数据，并将这些数据组成一个数组，且能够通过函数来提取其中符合条件要求的数据组。所以这一点既是重点也是难点！

下面作者还是一步步来完成公式的创建！

第一步：small+if+row函数固定搭配

small+if+row函数是什么固定搭配？有什么用？

这个搭配对于很多新手都比较陌生，但它其实属于excel公式中的“流氓”，可以解决很多场景问题。至于为什么“流氓”能解决很多问题，实则是脸皮厚能耍赖……

从下图中可见，作者输入了一个较长的组合公式：

{=SMALL(IF(TRANSPOSE(LEN(TEXT($H$3:$AI$3,"m/d")&":"&H4:AI4&";"&H5:AI5)>7),ROW($1:$28),""),ROW($1:$28))}}

我们先简单认识一下其中的“TEXT($H$3:$AI$3,"m/d")&":"&H4:AI4&";"&H5:AI5)”部分。

其中使用了“&”连接符，所以这个表达式就是文本的基础合并，是将日期行和下方的采购要货数量和回复数量行的数据进行连接。

由于是单元格区域与其他单元格区域的连接，因此会得到一个数组结果。

然后在公式中首个单元格区域套上了text函数，是为了给这组数据套上固定的日期格式，因此在4个条件中有一条为“公式结果保持原日期格式”。如果不使用text函数，那么公式运行时会将日期转换为一串数值。

我们来看下这个公式运行得到的结果，如下图所示，虽然在单元格显示为1，实际是一个包含数字排序和错误值的数组结果。

第二步：index+n+if函数的固定搭配

这个搭配童鞋们可能有点印象，是作者上个内容讲到的一对多公式套路。

那么它的作用是什么呢？

是利用index函数的引用功能，来引用合并文本的数组中指定位置的数据组合，也就是指定行序号来引用区域中的数据，这是index函数的基本作用。

但高级的是，index函数在这个公式中起到了引用出一个数组结果的效果，这在常规的index公式中是无法实现的。

下面先上公式：

{=INDEX(TRANSPOSE(TEXT($H$3:$AI$3,"m/d")&":"&H4:AI4&";"&H5:AI5),N(IF(1,SMALL(IF(TRANSPOSE(LEN(TEXT($H$3:$AI$3,"m/d")&":"&H4:AI4&";"&H5:AI5)>7),RO($1:$28),""),ROW($1:$28)))))}

初看这个公式感觉有点复杂，应用了很多函数，但不管添加多少函数进去，我们解析公式的步骤是一层一层剥开来，去解析每个函数在另一个函数中作为参数，起到了什么样的作用！

那这里，len函数是为了创建一个逻辑判断表达式；transpose是为了将行数据转为列数据；small函数是将数组结果进行排序；n+if函数是为了搭配index函数，而固定添加的一个组合，为了能够引用到一个数组的结果；

关于上面这段话的详细含义，大家可以去琢磨，也可以记住这个固定套路，因为到index函数这一步，基本可以应用到不同的一对多引用场景中。

下面我们还是来看一下这个公式运算的结果：

第三步：textjoin+iferror函数跳过空值组合文本

这一步将输出最后的结果，其中iferror函数是为了将index函数得到的错误值转换为空值，使textjoin利用其特性跳过这些空值，来组合其他的数据组合，并在单元格中换行显示。

所以完整公式为：

{=TEXTJOIN(CHAR(10),TRUE,IFERROR(INDEX(TRANSPOSE(TEXT($H$3:$AI$3,"m/d")&":"&H4:AI4&";"&H5:AI5),N(IF(1,SMALL(IF(TRANSPOSE(LEN(TEXT($H$3:$AI$3,"m/d")&":"&H4:AI4&";"&H5:AI5)>7),ROW($1:$28),""),ROW($1:$28))))),""))}

通过公式得到了正确的结果后，其实已经完成了最主要的工作，但我们还需要填充公式，还要将同一组单元格合并到一起，使公式结果在合并单元格中显示。

那其实操作也不复杂，作者做一遍，其中应用了多行填充和格式刷功能，大家可以收藏练习。

以上就是今天的全部内容，欢迎继续关注作者，持续更文中！

举报/反馈