Excel 中关于快速判断的四种不同解题思路分享

优化标题：《哪位Excel高手研发的这4个会计提效公式，惊才绝艳！》

经常有人向小花询问：

你是如何记住这么多函数及其不同应用方式的？

同样地，也有人质疑：

为什么你总是在不同解决方案中反复讨论一个问题？

实际上，这两个问题互为因果，它们是相互呼应的答案。

《论语》云：

学而不思则罔，思而不学则殆。

今天，小花将通过近期朋友的提问，再次验证这古老的东方智慧。

这位朋友是一名会计，他需根据「应付账款账龄明细表」来辨别每笔应付款的主要账龄。

如何计算主要账龄？

例如：A公司应付余额中，账龄在1个月以内的绝对额最高，因此，A公司的主要账龄是1个月以内。

接下来将分享四种不同解题方法。

1、常规查询法

作为VLOOKUP函数的衍生，HLOOKUP专门用于按列查询。

唯一需要解决的问题是结果列位于查询列上方，而非下方。

只有当比较值位于数据表的第一行，并且要查找下面指定行中的数据时，HLOOKUP函数才能正确计算。

所以，我们需要使用IF({1;0},,）结构虚拟查询数据表。

HLOOKUP精确查询公式：

=HLOOKUP(MAX(C2:G2)IF({1;0}C2:G2$C$1$G$1)2,0)

公式解释：

1代表TRUE，0代表FALSE，分号代表分行。

IF {1;0}将C2:G2与C1:G1重新组合为一个虚拟、以C2:G2为首行的新数据表。

HLOOKUP查询MAX最大值在C2:G2出现的位置，并返回C1:G1对应位置的值。

这是一个经典的IF引导的逆向查询公式：对于多个最大值，该公式将返回满足条件的首个查询结果。

2、文本连接法

当主要账龄唯一时，我们可以使用IF函数判断，将不符合条件的文本转换为空，然后连接所有文本，即可得出主要账龄。

具体公式如下👇

CONCAT条件文本连接公式：

{=CONCAT(IF(C2:G2=MAX(C2:G2)$C$1$G$1""))}

公式解释：

这是一个数组公式，必须使用【Ctrl+Shift+Enter】来精确运算。

通过将C2:G2的每个值与最大值进行比较，相等则返回C1:G1对应账龄分类，不等则返回空。

使用CONCAT函数连接了唯一的账龄分类和其他空值，并得到主要账龄。

此公式不适用于存在多个相等最大值的情况。

3、频率分布法

FREQUENCY函数在计算单值的频率分布时，仅在目标值对应的频率为1时返回，其他情况为0。

通过利用FREQUENCY的这一特性，我们可轻松将其与LOOKUP的两分法联系起来，从而构建公式。

FREQUENCY频率分布查询公式：

=LOOKUP(1,0/FREQUENCY(MAX(C2:G2)C2:G2)$C$1$G$1)

公式解释：

FREQUENCY(MAX(C2:G2)C2:G2),$C$1:$G$1)仅在C2:G2的最大值位置返回1，其他位置为0。

剩余部分构成了LOOKUP两分法公式，这里不再详述原理。

对于多个最大值，该公式将返回最后一个最大值对应的查询结果。

4、条件排序法

使用2021以上版本的Office或WPS，可采用新函数SORTBY来解决此问题。

除此之外，我们还需利用INDEX来索引排序后的第一个值，即主要账龄。

SORTBY排序索引公式：

=INDEX(SORTBY($C$1:$G$1,C2:G2-1),1)

公式解释：

SORTBY函数以C2:G2作为排序依据，按降序排列，再重排C1:G1单元格，此时，应付款的最大值对应的主要账龄排在第一个，再使用INDEX索引即可。

对于多个最大值，该公式将返回第一个最大值对应的账龄。

以上即是判断主要账龄的四种不同思路：

❶使用IF({1;0},,）结构和HLOOKUP函数进行逆向查询。

❷应用IF进行逐步筛选后使用CONCAT连接。

❸运用LOOKUP对FREQUENCY的概率分布进行查询。

❹通过SORTBY对降序排列后运用INDEX进行索引。

通过对不同解法思路这一简单问题的探讨，我们既学会了8种函数（MAX，IF，HLOOKUP，CONCAT，LOOKUP，FREQUENCY，INDEX，SORTBY）又掌握了4种高效用法（IF重构数据表，数组公式，LOOKUP两分法，FREQUENCY单值频率分布）。

若我们能对工作和学习中的每一小问题进行开放性探讨和实践，Excel水平必定大幅提升，对吧？

本文来自微信公众号：秋叶Excel（ID：excel100），作者：小花

广告声明：文内包含对外跳转链接（包括但不限于超链接、二维码、口令等形式），旨在传递更多信息，为节省挑选时间，结果仅供参考。