large函数表达式排序公式写法和含义

从这个案例来讲，它包含了三大内容，一是常规排序，二是条件排序，三是多个关键字查找引用。

excel的排序问题，大多数情况下都是通过数据工具栏下的排序功能来操作的。

本文也会在最后通过排序操作动图来对比公式排序的结果。

但如果在一些特定情境下，需要使用公式来进行数据排序，并设置了指定的排序条件，那么这样的情况下，该使用什么函数来写这个公式！？

通过最后公式的介绍，也能了解关于其中函数的一些特性和组合应用。

下面作者以某加工企业的实例来讲解。

如下图所示，现在需要先对数据表中的线材规格列表进行从大到小的排序，然后再对同一线材规格下的标准线材长度进行从大到小的排序，最后再将对应的线材总支数引用过来。

关于线材规格的排序，属于常规排序应用，而后续对线材长的排序，则属于条件排序，即指定线材规格，按照大小进行不重复排序。

从这个案例来讲，它包含了三大内容，一是常规排序，二是条件排序，三是多个关键字引用。

根据重点，作者将介绍条件排序的公式写法和含义。

首先，我们还是来看看解题的第一步。

一、对线材规格进行排序

由于是从大到小来排序，所以作者选择了large函数。

large函数的作用是返回列表中指定第K大的值。

其语法图如下所示：

表达式为：=large（单元格区域，第K大）

根据其语法表达，我们可以输入一个公式：

=LARGE($A$2:$A$38,ROW(A1))

这个公式重点在于第2参数的设置，它嵌套了row函数，而row函数最大的特点是通过下拉填充能够获得动态结果。比如row（A1）等于1，下拉到A2单元格，即为row（A2），结果等于2.

所以输入公式后，下拉填充就能得到从第1大的值一直到最后一个最小的值。

二、对指定线材规格的线材长度进行排序

得到了排序后的线材规格列表，我们需要根据相同的线材规格，来进行线材长度从大到小的排序。

既然还是大小排序的场景，那么我们再利用large函数来写一个公式：

=LARGE(IF($A$2:$A$38=E2,$B$2:$B$38),COUNTIF($E$2:E2,E2))

这个公式组合了large、if、countif函数，其中if函数起到了什么作用呢？countif函数又有什么特殊效果。

首先if函数是一个逻辑判断函数，根据公式中的if函数表达式“IF($A$2:$A$38=E2,$B$2:$B$38)”。

它的含义是当A列的数据等于E2到的值，则返回B列的数据。

而这个地方我们需要看到if函数的一个特性，即当A列数据不等于E2的值的时候，它会返回一个逻辑值false。

从数据表可以看出，if函数表达式的结果肯定是包含了两组不同的结果值，一组是false，另一组则是符合条件返回的数据值。

我们按下快捷键来解析if函数表达式的预览结果：

通过if函数我们得到了一个包含逻辑值和数值的数组区域。

此时我们便可以利用large函数忽略逻辑值的特性，来对这个数组区域内的数值进行大小排序。

那我们就要来看看第2参数countif函数表达式的含义了：COUNTIF($E$2:E2,E2)

这是条件计数函数countif的常规表达，表示列表区域$E$2:E2包含E2的值的单元格个数。

大家注意一个要点，在countif函数的第1参数列表区域中，使用了单元格区域的混合引用，即首个单元格为绝对引用，结尾单元格则是相对引用。

这个写法，与row函数的作用有点类似，它会通过下拉填充公式时，得到一个动态扩展的区域结果。也就是将，当填充公式到E6单元格时，其表达式中的列表区域则等于“$E$2:E6”。

所以当countif函数的第1参数列表区域发生了变化，其计数结果也会相应变动。

最后，需要注意三键运行公式，才能得到正确结果。

我们填充一下公式，看看最后的排序效果：

三、多个关键字的查找引用

关于多个关键字的查询引用，则可以利用“vlookup+if{1,0}”的固定套路来写公式。

完整公式为：=VLOOKUP(E2&F2,IF({1,0},$A$2:$A$38&$B$2:$B$38,$C$2:$C$38),2,0)

这里作者不再详述该公式的含义，大家可以参照作者专栏《excel100个常见场景可套公式》中关于该段内容的讲解。

最后，我们再来看下使用排序功能，进行自定义条件排序的操作，如下动图所示：

从动图可见，公式排序的结果与功能排序的结果是一致的！

总结一下，今天主要介绍了large函数公式的两种案例介绍，一个是嵌套row函数的用法，另一个则是large组合if和countif函数的数组公式应用。

我们需要理解当中if函数和countif函数表达式的具体含义，以及在large函数中起到的作用！

举报/反馈