哪位Excel高人琢磨出的这4个会计提效公式，太牛掰了！

点击关注【秋叶 Excel】

发送【6】

领取秋叶 Excel 6 年精选文章合集

作者：小花

编辑：卫星酱

经常有人问小花：

你是怎么记住这么多函数以及他们的变形用法的？

同样的，也有人问：

为什么你的文章总喜欢就一个问题反复探讨不同的解法？

其实，这两个问题刚好互为应和，他们是彼此的答案。

论语有云：

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

今天，小花就以朋友近期的提问，再次印证这古老的东方智慧。

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

怎么计算主要账龄？

比如：A 公司应付余额中，账龄在 1 个月以内的绝对额最高，所以，A 公司的主要账龄是 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 引导的逆向查询公式：当有多个最大值时，该公式返回满足条件的首个查询结果。

文本连接法

当主要账龄唯一时，我们还可以使用 IF 函数进行判断，将不满足条件的文本转换为空，再将所有文本联和起来，就能得出主要账龄。

具体公式如下

CONCAT 条件文本连接公式：

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

公式说明：

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

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

CONCAT 函数将唯一的账龄分类和其余空值连接起来，其结果就是主要账龄。

该公式不适用多个相等最大值的情况。

频率分布法

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 两分法公式，原理此处不再赘述。

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

条件排序法

2021 以上版本 OFFICE 或 WPS 的使用者，还可以用新函数 SORTBY 来解决这一问题。

此外，我们还需要 INDEX 来索引排序后的首个值，即主要账龄。

SORTBY 排序索引公式：

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

公式说明：

SORTBY 函数以 C2:G2 为排序依据，以降序排列方式，将 C1:G1 单元格进行重新排列，此时，应付款最大值对应的主要账龄排到第 1 位，再使用 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，增强职场竞争力~

欢迎你加入《秋叶 Excel 3 天集训营》，和 500 强企业培训讲师拉登 Dony 一起学习更多高效技巧！

不限年龄！不限岗位！不限基础！都可以学！

《秋叶 Excel 3 天集训营》

原价 99 元

新课上线

现在免费报名！

视频+图文笔记+作业实操+助教答疑

Excel 和 WPS 双软件教学

抢占学习名额吧！

关注【秋叶 Excel】 ，发送【6】

领取秋叶 Excel 6 年精选文章合集